Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6726d8d

Browse files
committed
Move plpgsql error-trapping tests to a new module-specific test file.
The test for statement timeout has a 2-second timeout, which was onlymoderately annoying when it was written, but nowadays it contributesa pretty significant chunk of the elapsed time needed to run the coreregression tests on a fast machine. We can improve this situation bypushing the test into a plpgsql-specific test file instead of havingit in a core regression test. That's a clean win when consideringjust the core tests. Even when considering check-world or a buildfarmtest run, we should come out ahead because the core tests get runmore times in those sequences.Furthermore, since the plpgsql tests aren't currently parallelized,it seems likely that the timing problems reflected in commitf1e671a(which increased that timeout from 1 sec to 2) will be much less severein this context. Hence, let's try cutting the timeout back to 1 secondin hopes of a further win for check-world. We can undo that ifbuildfarm experience proves it to be a bad idea.To give the new test file some modicum of intellectual coherency,I moved the surrounding tests related to error-trapping along withthe statement timeout test proper. Those other tests don't run longenough to have any particular bearing on test-runtime considerations.The tests are the same as before, except with minor adjustments tonot depend on an externally-created table.Discussion:https://postgr.es/m/735.1554935715@sss.pgh.pa.us
1 parented16ba3 commit6726d8d

File tree

5 files changed

+432
-427
lines changed

5 files changed

+432
-427
lines changed

‎src/pl/plpgsql/src/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,8 @@ DATA = plpgsql.control plpgsql--1.0.sql plpgsql--unpackaged--1.0.sql
2727
REGRESS_OPTS = --dbname=$(PL_TESTDB)
2828

2929
REGRESS = plpgsql_call plpgsql_control plpgsql_domain plpgsql_record\
30-
plpgsql_cache plpgsql_transaction plpgsql_trigger plpgsql_varprops
30+
plpgsql_cache plpgsql_transaction plpgsql_trap\
31+
plpgsql_trigger plpgsql_varprops
3132

3233
# where to find gen_keywordlist.pl and subsidiary files
3334
TOOLSDIR =$(top_srcdir)/src/tools
Lines changed: 255 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,255 @@
1+
--
2+
-- Test error trapping
3+
--
4+
create function trap_zero_divide(int) returns int as $$
5+
declare x int;
6+
sx smallint;
7+
begin
8+
begin-- start a subtransaction
9+
raise notice 'should see this';
10+
x := 100 / $1;
11+
raise notice 'should see this only if % <> 0', $1;
12+
sx := $1;
13+
raise notice 'should see this only if % fits in smallint', $1;
14+
if $1 < 0 then
15+
raise exception '% is less than zero', $1;
16+
end if;
17+
exception
18+
when division_by_zero then
19+
raise notice 'caught division_by_zero';
20+
x := -1;
21+
when NUMERIC_VALUE_OUT_OF_RANGE then
22+
raise notice 'caught numeric_value_out_of_range';
23+
x := -2;
24+
end;
25+
return x;
26+
end$$ language plpgsql;
27+
select trap_zero_divide(50);
28+
NOTICE: should see this
29+
NOTICE: should see this only if 50 <> 0
30+
NOTICE: should see this only if 50 fits in smallint
31+
trap_zero_divide
32+
------------------
33+
2
34+
(1 row)
35+
36+
select trap_zero_divide(0);
37+
NOTICE: should see this
38+
NOTICE: caught division_by_zero
39+
trap_zero_divide
40+
------------------
41+
-1
42+
(1 row)
43+
44+
select trap_zero_divide(100000);
45+
NOTICE: should see this
46+
NOTICE: should see this only if 100000 <> 0
47+
NOTICE: caught numeric_value_out_of_range
48+
trap_zero_divide
49+
------------------
50+
-2
51+
(1 row)
52+
53+
select trap_zero_divide(-100);
54+
NOTICE: should see this
55+
NOTICE: should see this only if -100 <> 0
56+
NOTICE: should see this only if -100 fits in smallint
57+
ERROR: -100 is less than zero
58+
CONTEXT: PL/pgSQL function trap_zero_divide(integer) line 12 at RAISE
59+
create table match_source as
60+
select x as id, x*10 as data, x/10 as ten from generate_series(1,100) x;
61+
create function trap_matching_test(int) returns int as $$
62+
declare x int;
63+
sx smallint;
64+
y int;
65+
begin
66+
begin-- start a subtransaction
67+
x := 100 / $1;
68+
sx := $1;
69+
select into y data from match_source where id =
70+
(select id from match_source b where ten = $1);
71+
exception
72+
when data_exception then -- category match
73+
raise notice 'caught data_exception';
74+
x := -1;
75+
when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then
76+
raise notice 'caught numeric_value_out_of_range or cardinality_violation';
77+
x := -2;
78+
end;
79+
return x;
80+
end$$ language plpgsql;
81+
select trap_matching_test(50);
82+
trap_matching_test
83+
--------------------
84+
2
85+
(1 row)
86+
87+
select trap_matching_test(0);
88+
NOTICE: caught data_exception
89+
trap_matching_test
90+
--------------------
91+
-1
92+
(1 row)
93+
94+
select trap_matching_test(100000);
95+
NOTICE: caught data_exception
96+
trap_matching_test
97+
--------------------
98+
-1
99+
(1 row)
100+
101+
select trap_matching_test(1);
102+
NOTICE: caught numeric_value_out_of_range or cardinality_violation
103+
trap_matching_test
104+
--------------------
105+
-2
106+
(1 row)
107+
108+
create temp table foo (f1 int);
109+
create function subxact_rollback_semantics() returns int as $$
110+
declare x int;
111+
begin
112+
x := 1;
113+
insert into foo values(x);
114+
begin
115+
x := x + 1;
116+
insert into foo values(x);
117+
raise exception 'inner';
118+
exception
119+
when others then
120+
x := x * 10;
121+
end;
122+
insert into foo values(x);
123+
return x;
124+
end$$ language plpgsql;
125+
select subxact_rollback_semantics();
126+
subxact_rollback_semantics
127+
----------------------------
128+
20
129+
(1 row)
130+
131+
select * from foo;
132+
f1
133+
----
134+
1
135+
20
136+
(2 rows)
137+
138+
drop table foo;
139+
create function trap_timeout() returns void as $$
140+
begin
141+
declare x int;
142+
begin
143+
-- we assume this will take longer than 1 second:
144+
select count(*) into x from generate_series(1, 1000000000000);
145+
exception
146+
when others then
147+
raise notice 'caught others?';
148+
when query_canceled then
149+
raise notice 'nyeah nyeah, can''t stop me';
150+
end;
151+
-- Abort transaction to abandon the statement_timeout setting. Otherwise,
152+
-- the next top-level statement would be vulnerable to the timeout.
153+
raise exception 'end of function';
154+
end$$ language plpgsql;
155+
begin;
156+
set statement_timeout to 1000;
157+
select trap_timeout();
158+
NOTICE: nyeah nyeah, can't stop me
159+
ERROR: end of function
160+
CONTEXT: PL/pgSQL function trap_timeout() line 15 at RAISE
161+
rollback;
162+
-- Test for pass-by-ref values being stored in proper context
163+
create function test_variable_storage() returns text as $$
164+
declare x text;
165+
begin
166+
x := '1234';
167+
begin
168+
x := x || '5678';
169+
-- force error inside subtransaction SPI context
170+
perform trap_zero_divide(-100);
171+
exception
172+
when others then
173+
x := x || '9012';
174+
end;
175+
return x;
176+
end$$ language plpgsql;
177+
select test_variable_storage();
178+
NOTICE: should see this
179+
NOTICE: should see this only if -100 <> 0
180+
NOTICE: should see this only if -100 fits in smallint
181+
test_variable_storage
182+
-----------------------
183+
123456789012
184+
(1 row)
185+
186+
--
187+
-- test foreign key error trapping
188+
--
189+
create temp table master(f1 int primary key);
190+
create temp table slave(f1 int references master deferrable);
191+
insert into master values(1);
192+
insert into slave values(1);
193+
insert into slave values(2);-- fails
194+
ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
195+
DETAIL: Key (f1)=(2) is not present in table "master".
196+
create function trap_foreign_key(int) returns int as $$
197+
begin
198+
begin-- start a subtransaction
199+
insert into slave values($1);
200+
exception
201+
when foreign_key_violation then
202+
raise notice 'caught foreign_key_violation';
203+
return 0;
204+
end;
205+
return 1;
206+
end$$ language plpgsql;
207+
create function trap_foreign_key_2() returns int as $$
208+
begin
209+
begin-- start a subtransaction
210+
set constraints all immediate;
211+
exception
212+
when foreign_key_violation then
213+
raise notice 'caught foreign_key_violation';
214+
return 0;
215+
end;
216+
return 1;
217+
end$$ language plpgsql;
218+
select trap_foreign_key(1);
219+
trap_foreign_key
220+
------------------
221+
1
222+
(1 row)
223+
224+
select trap_foreign_key(2);-- detects FK violation
225+
NOTICE: caught foreign_key_violation
226+
trap_foreign_key
227+
------------------
228+
0
229+
(1 row)
230+
231+
begin;
232+
set constraints all deferred;
233+
select trap_foreign_key(2);-- should not detect FK violation
234+
trap_foreign_key
235+
------------------
236+
1
237+
(1 row)
238+
239+
savepoint x;
240+
set constraints all immediate; -- fails
241+
ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
242+
DETAIL: Key (f1)=(2) is not present in table "master".
243+
rollback to x;
244+
select trap_foreign_key_2(); -- detects FK violation
245+
NOTICE: caught foreign_key_violation
246+
trap_foreign_key_2
247+
--------------------
248+
0
249+
(1 row)
250+
251+
commit;-- still fails
252+
ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
253+
DETAIL: Key (f1)=(2) is not present in table "master".
254+
drop function trap_foreign_key(int);
255+
drop function trap_foreign_key_2();

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp