drop table perform_test;
--
--- Test error trapping
---
-create function trap_zero_divide(int) returns int as $$
-declare x int;
- sx smallint;
-begin
- begin -- start a subtransaction
- raise notice 'should see this';
- x := 100 / $1;
- raise notice 'should see this only if % <> 0', $1;
- sx := $1;
- raise notice 'should see this only if % fits in smallint', $1;
- if $1 < 0 then
- raise exception '% is less than zero', $1;
- end if;
- exception
- when division_by_zero then
- raise notice 'caught division_by_zero';
- x := -1;
- when NUMERIC_VALUE_OUT_OF_RANGE then
- raise notice 'caught numeric_value_out_of_range';
- x := -2;
- end;
- return x;
-end$$ language plpgsql;
-select trap_zero_divide(50);
-NOTICE: should see this
-NOTICE: should see this only if 50 <> 0
-NOTICE: should see this only if 50 fits in smallint
- trap_zero_divide
-------------------
- 2
-(1 row)
-
-select trap_zero_divide(0);
-NOTICE: should see this
-NOTICE: caught division_by_zero
- trap_zero_divide
-------------------
- -1
-(1 row)
-
-select trap_zero_divide(100000);
-NOTICE: should see this
-NOTICE: should see this only if 100000 <> 0
-NOTICE: caught numeric_value_out_of_range
- trap_zero_divide
-------------------
- -2
-(1 row)
-
-select trap_zero_divide(-100);
-NOTICE: should see this
-NOTICE: should see this only if -100 <> 0
-NOTICE: should see this only if -100 fits in smallint
-ERROR: -100 is less than zero
-CONTEXT: PL/pgSQL function trap_zero_divide(integer) line 12 at RAISE
-create function trap_matching_test(int) returns int as $$
-declare x int;
- sx smallint;
- y int;
-begin
- begin -- start a subtransaction
- x := 100 / $1;
- sx := $1;
- select into y unique1 from tenk1 where unique2 =
- (select unique2 from tenk1 b where ten = $1);
- exception
- when data_exception then -- category match
- raise notice 'caught data_exception';
- x := -1;
- when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then
- raise notice 'caught numeric_value_out_of_range or cardinality_violation';
- x := -2;
- end;
- return x;
-end$$ language plpgsql;
-select trap_matching_test(50);
- trap_matching_test
---------------------
- 2
-(1 row)
-
-select trap_matching_test(0);
-NOTICE: caught data_exception
- trap_matching_test
---------------------
- -1
-(1 row)
-
-select trap_matching_test(100000);
-NOTICE: caught data_exception
- trap_matching_test
---------------------
- -1
-(1 row)
-
-select trap_matching_test(1);
-NOTICE: caught numeric_value_out_of_range or cardinality_violation
- trap_matching_test
---------------------
- -2
-(1 row)
-
-create temp table foo (f1 int);
-create function subxact_rollback_semantics() returns int as $$
-declare x int;
-begin
- x := 1;
- insert into foo values(x);
- begin
- x := x + 1;
- insert into foo values(x);
- raise exception 'inner';
- exception
- when others then
- x := x * 10;
- end;
- insert into foo values(x);
- return x;
-end$$ language plpgsql;
-select subxact_rollback_semantics();
- subxact_rollback_semantics
-----------------------------
- 20
-(1 row)
-
-select * from foo;
- f1
-----
- 1
- 20
-(2 rows)
-
-drop table foo;
-create function trap_timeout() returns void as $$
-begin
- declare x int;
- begin
- -- we assume this will take longer than 2 seconds:
- select count(*) into x from tenk1 a, tenk1 b, tenk1 c;
- exception
- when others then
- raise notice 'caught others?';
- when query_canceled then
- raise notice 'nyeah nyeah, can''t stop me';
- end;
- -- Abort transaction to abandon the statement_timeout setting. Otherwise,
- -- the next top-level statement would be vulnerable to the timeout.
- raise exception 'end of function';
-end$$ language plpgsql;
-begin;
-set statement_timeout to 2000;
-select trap_timeout();
-NOTICE: nyeah nyeah, can't stop me
-ERROR: end of function
-CONTEXT: PL/pgSQL function trap_timeout() line 15 at RAISE
-rollback;
--- Test for pass-by-ref values being stored in proper context
-create function test_variable_storage() returns text as $$
-declare x text;
-begin
- x := '1234';
- begin
- x := x || '5678';
- -- force error inside subtransaction SPI context
- perform trap_zero_divide(-100);
- exception
- when others then
- x := x || '9012';
- end;
- return x;
-end$$ language plpgsql;
-select test_variable_storage();
-NOTICE: should see this
-NOTICE: should see this only if -100 <> 0
-NOTICE: should see this only if -100 fits in smallint
- test_variable_storage
------------------------
- 123456789012
-(1 row)
-
---
--- test foreign key error trapping
---
-create temp table master(f1 int primary key);
-create temp table slave(f1 int references master deferrable);
-insert into master values(1);
-insert into slave values(1);
-insert into slave values(2); -- fails
-ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
-DETAIL: Key (f1)=(2) is not present in table "master".
-create function trap_foreign_key(int) returns int as $$
-begin
- begin -- start a subtransaction
- insert into slave values($1);
- exception
- when foreign_key_violation then
- raise notice 'caught foreign_key_violation';
- return 0;
- end;
- return 1;
-end$$ language plpgsql;
-create function trap_foreign_key_2() returns int as $$
-begin
- begin -- start a subtransaction
- set constraints all immediate;
- exception
- when foreign_key_violation then
- raise notice 'caught foreign_key_violation';
- return 0;
- end;
- return 1;
-end$$ language plpgsql;
-select trap_foreign_key(1);
- trap_foreign_key
-------------------
- 1
-(1 row)
-
-select trap_foreign_key(2); -- detects FK violation
-NOTICE: caught foreign_key_violation
- trap_foreign_key
-------------------
- 0
-(1 row)
-
-begin;
- set constraints all deferred;
- select trap_foreign_key(2); -- should not detect FK violation
- trap_foreign_key
-------------------
- 1
-(1 row)
-
- savepoint x;
- set constraints all immediate; -- fails
-ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
-DETAIL: Key (f1)=(2) is not present in table "master".
- rollback to x;
- select trap_foreign_key_2(); -- detects FK violation
-NOTICE: caught foreign_key_violation
- trap_foreign_key_2
---------------------
- 0
-(1 row)
-
-commit; -- still fails
-ERROR: insert or update on table "slave" violates foreign key constraint "slave_f1_fkey"
-DETAIL: Key (f1)=(2) is not present in table "master".
-drop function trap_foreign_key(int);
-drop function trap_foreign_key_2();
---
-- Test proper snapshot handling in simple expressions
--
create temp table users(login text, id serial);
projects / postgresql / blobdiff