diff -U3 /tmp/cirrus-ci-build/src/pl/plpgsql/src/expected/plpgsql_transaction.out /tmp/cirrus-ci-build/build-32/testrun/plpgsql/regress/results/plpgsql_transaction.out --- /tmp/cirrus-ci-build/src/pl/plpgsql/src/expected/plpgsql_transaction.out 2024-03-26 23:35:06.498574769 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpgsql/regress/results/plpgsql_transaction.out 2024-03-26 23:42:33.830235263 +0000 @@ -513,229 +513,7 @@ INSERT INTO test3 (y) VALUES (3); -- won't get here END; $$; -ERROR: duplicate key value violates unique constraint "test3_y_key" -DETAIL: Key (y)=(1) already exists. -CONTEXT: PL/pgSQL function inline_code_block line 9 at COMMIT -SELECT * FROM test3; - y ---- - 1 -(1 row) - --- failure while trying to persist a cursor across a transaction (bug #15703) -CREATE PROCEDURE cursor_fail_during_commit() - LANGUAGE plpgsql -AS $$ - DECLARE id int; - BEGIN - FOR id IN SELECT 1/(x-1000) FROM generate_series(1,1000) x LOOP - INSERT INTO test1 VALUES(id); - COMMIT; - END LOOP; - END; -$$; -TRUNCATE test1; -CALL cursor_fail_during_commit(); -ERROR: division by zero -CONTEXT: PL/pgSQL function cursor_fail_during_commit() line 6 at COMMIT --- note that error occurs during first COMMIT, hence nothing is in test1 -SELECT count(*) FROM test1; - count -------- - 0 -(1 row) - -CREATE PROCEDURE cursor_fail_during_rollback() - LANGUAGE plpgsql -AS $$ - DECLARE id int; - BEGIN - FOR id IN SELECT 1/(x-1000) FROM generate_series(1,1000) x LOOP - INSERT INTO test1 VALUES(id); - ROLLBACK; - END LOOP; - END; -$$; -TRUNCATE test1; -CALL cursor_fail_during_rollback(); -ERROR: division by zero -CONTEXT: PL/pgSQL function cursor_fail_during_rollback() line 6 at ROLLBACK -SELECT count(*) FROM test1; - count -------- - 0 -(1 row) - --- SET TRANSACTION -DO LANGUAGE plpgsql $$ -BEGIN - PERFORM 1; - RAISE INFO '%', current_setting('transaction_isolation'); - COMMIT; - SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; - PERFORM 1; - RAISE INFO '%', current_setting('transaction_isolation'); - COMMIT; - SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; - PERFORM 1; - RAISE INFO '%', current_setting('transaction_isolation'); - COMMIT; -END; -$$; -INFO: read committed -INFO: repeatable read -INFO: serializable --- error cases -DO LANGUAGE plpgsql $$ -BEGIN - SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -END; -$$; -ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query -CONTEXT: SQL statement "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ" -PL/pgSQL function inline_code_block line 3 at SQL statement -DO LANGUAGE plpgsql $$ -BEGIN - SAVEPOINT foo; -END; -$$; -ERROR: unsupported transaction command in PL/pgSQL -CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement -DO LANGUAGE plpgsql $$ -BEGIN - EXECUTE 'COMMIT'; -END; -$$; -ERROR: EXECUTE of transaction commands is not implemented -CONTEXT: PL/pgSQL function inline_code_block line 3 at EXECUTE --- snapshot handling test -TRUNCATE test2; -CREATE PROCEDURE transaction_test9() -LANGUAGE SQL -AS $$ -INSERT INTO test2 VALUES (42); -$$; -DO LANGUAGE plpgsql $$ -BEGIN - ROLLBACK; - CALL transaction_test9(); -END -$$; -SELECT * FROM test2; - x ----- - 42 -(1 row) - --- another snapshot handling case: argument expressions of a CALL need --- to be evaluated with an up-to-date snapshot -CREATE FUNCTION report_count() RETURNS int -STABLE LANGUAGE sql -AS $$ SELECT COUNT(*) FROM test2 $$; -CREATE PROCEDURE transaction_test9b(cnt int) -LANGUAGE plpgsql -AS $$ -BEGIN - RAISE NOTICE 'count = %', cnt; -END -$$; -DO $$ -BEGIN - CALL transaction_test9b(report_count()); - INSERT INTO test2 VALUES(43); - CALL transaction_test9b(report_count()); -END -$$; -NOTICE: count = 1 -NOTICE: count = 2 --- Test transaction in procedure with output parameters. This uses a --- different portal strategy and different code paths in pquery.c. -CREATE PROCEDURE transaction_test10a(INOUT x int) -LANGUAGE plpgsql -AS $$ -BEGIN - x := x + 1; - COMMIT; -END; -$$; -CALL transaction_test10a(10); - x ----- - 11 -(1 row) - -CREATE PROCEDURE transaction_test10b(INOUT x int) -LANGUAGE plpgsql -AS $$ -BEGIN - x := x - 1; - ROLLBACK; -END; -$$; -CALL transaction_test10b(10); - x ---- - 9 -(1 row) - --- transaction timestamp vs. statement timestamp -CREATE PROCEDURE transaction_test11() -LANGUAGE plpgsql -AS $$ -DECLARE - s1 timestamp with time zone; - s2 timestamp with time zone; - s3 timestamp with time zone; - t1 timestamp with time zone; - t2 timestamp with time zone; - t3 timestamp with time zone; -BEGIN - s1 := statement_timestamp(); - t1 := transaction_timestamp(); - ASSERT s1 = t1; - PERFORM pg_sleep(0.001); - COMMIT; - s2 := statement_timestamp(); - t2 := transaction_timestamp(); - ASSERT s2 = s1; - ASSERT t2 > t1; - PERFORM pg_sleep(0.001); - ROLLBACK; - s3 := statement_timestamp(); - t3 := transaction_timestamp(); - ASSERT s3 = s1; - ASSERT t3 > t2; -END; -$$; -CALL transaction_test11(); --- transaction chain -TRUNCATE test1; -DO LANGUAGE plpgsql $$ -BEGIN - ROLLBACK; - SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; - FOR i IN 0..3 LOOP - RAISE INFO 'transaction_isolation = %', current_setting('transaction_isolation'); - INSERT INTO test1 (a) VALUES (i); - IF i % 2 = 0 THEN - COMMIT AND CHAIN; - ELSE - ROLLBACK AND CHAIN; - END IF; - END LOOP; -END -$$; -INFO: transaction_isolation = repeatable read -INFO: transaction_isolation = repeatable read -INFO: transaction_isolation = repeatable read -INFO: transaction_isolation = repeatable read -SELECT * FROM test1; - a | b ----+--- - 0 | - 2 | -(2 rows) - -DROP TABLE test1; -DROP TABLE test2; -DROP TABLE test3; +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost diff -U3 /tmp/cirrus-ci-build/src/pl/plpgsql/src/expected/plpgsql_trap.out /tmp/cirrus-ci-build/build-32/testrun/plpgsql/regress/results/plpgsql_trap.out --- /tmp/cirrus-ci-build/src/pl/plpgsql/src/expected/plpgsql_trap.out 2024-03-26 23:35:06.498574769 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpgsql/regress/results/plpgsql_trap.out 2024-03-26 23:42:34.482234770 +0000 @@ -155,101 +155,10 @@ begin; set statement_timeout to 1000; 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 root(f1 int primary key); -create temp table leaf(f1 int references root deferrable); -insert into root values(1); -insert into leaf values(1); -insert into leaf values(2); -- fails -ERROR: insert or update on table "leaf" violates foreign key constraint "leaf_f1_fkey" -DETAIL: Key (f1)=(2) is not present in table "root". -create function trap_foreign_key(int) returns int as $$ -begin - begin -- start a subtransaction - insert into leaf 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 "leaf" violates foreign key constraint "leaf_f1_fkey" -DETAIL: Key (f1)=(2) is not present in table "root". - 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 "leaf" violates foreign key constraint "leaf_f1_fkey" -DETAIL: Key (f1)=(2) is not present in table "root". -drop function trap_foreign_key(int); -drop function trap_foreign_key_2(); +WARNING: terminating connection because of crash of another server process +DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. +HINT: In a moment you should be able to reconnect to the database and repeat your command. +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost diff -U3 /tmp/cirrus-ci-build/src/pl/plpgsql/src/expected/plpgsql_trigger.out /tmp/cirrus-ci-build/build-32/testrun/plpgsql/regress/results/plpgsql_trigger.out --- /tmp/cirrus-ci-build/src/pl/plpgsql/src/expected/plpgsql_trigger.out 2024-03-26 23:35:06.498574769 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpgsql/regress/results/plpgsql_trigger.out 2024-03-26 23:42:34.514234746 +0000 @@ -1,36 +1 @@ --- Simple test to verify accessibility of the OLD and NEW trigger variables -create table testtr (a int, b text); -create function testtr_trigger() returns trigger language plpgsql as -$$begin - raise notice 'tg_op = %', tg_op; - raise notice 'old(%) = %', old.a, row(old.*); - raise notice 'new(%) = %', new.a, row(new.*); - if (tg_op = 'DELETE') then - return old; - else - return new; - end if; -end$$; -create trigger testtr_trigger before insert or delete or update on testtr - for each row execute function testtr_trigger(); -insert into testtr values (1, 'one'), (2, 'two'); -NOTICE: tg_op = INSERT -NOTICE: old() = (,) -NOTICE: new(1) = (1,one) -NOTICE: tg_op = INSERT -NOTICE: old() = (,) -NOTICE: new(2) = (2,two) -update testtr set a = a + 1; -NOTICE: tg_op = UPDATE -NOTICE: old(1) = (1,one) -NOTICE: new(2) = (2,one) -NOTICE: tg_op = UPDATE -NOTICE: old(2) = (2,two) -NOTICE: new(3) = (3,two) -delete from testtr; -NOTICE: tg_op = DELETE -NOTICE: old(2) = (2,one) -NOTICE: new() = (,) -NOTICE: tg_op = DELETE -NOTICE: old(3) = (3,two) -NOTICE: new() = (,) +psql: error: connection to server on socket "/tmp/pg_regress-fjdmtd/.s.PGSQL.40000" failed: FATAL: the database system is in recovery mode diff -U3 /tmp/cirrus-ci-build/src/pl/plpgsql/src/expected/plpgsql_varprops.out /tmp/cirrus-ci-build/build-32/testrun/plpgsql/regress/results/plpgsql_varprops.out --- /tmp/cirrus-ci-build/src/pl/plpgsql/src/expected/plpgsql_varprops.out 2024-03-26 23:35:06.498574769 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpgsql/regress/results/plpgsql_varprops.out 2024-03-26 23:42:34.526234738 +0000 @@ -1,298 +1,2 @@ --- --- Tests for PL/pgSQL variable properties: CONSTANT, NOT NULL, initializers --- -create type var_record as (f1 int4, f2 int4); -create domain int_nn as int not null; -create domain var_record_nn as var_record not null; -create domain var_record_colnn as var_record check((value).f2 is not null); --- CONSTANT -do $$ -declare x constant int := 42; -begin - raise notice 'x = %', x; -end$$; -NOTICE: x = 42 -do $$ -declare x constant int; -begin - x := 42; -- fail -end$$; -ERROR: variable "x" is declared CONSTANT -LINE 4: x := 42; -- fail - ^ -do $$ -declare x constant int; y int; -begin - for x, y in select 1, 2 loop -- fail - end loop; -end$$; -ERROR: variable "x" is declared CONSTANT -LINE 4: for x, y in select 1, 2 loop -- fail - ^ -do $$ -declare x constant int[]; -begin - x[1] := 42; -- fail -end$$; -ERROR: variable "x" is declared CONSTANT -LINE 4: x[1] := 42; -- fail - ^ -do $$ -declare x constant int[]; y int; -begin - for x[1], y in select 1, 2 loop -- fail (currently, unsupported syntax) - end loop; -end$$; -ERROR: syntax error at or near "[" -LINE 4: for x[1], y in select 1, 2 loop -- fail (currently, unsup... - ^ -do $$ -declare x constant var_record; -begin - x.f1 := 42; -- fail -end$$; -ERROR: variable "x" is declared CONSTANT -LINE 4: x.f1 := 42; -- fail - ^ -do $$ -declare x constant var_record; y int; -begin - for x.f1, y in select 1, 2 loop -- fail - end loop; -end$$; -ERROR: variable "x" is declared CONSTANT -LINE 4: for x.f1, y in select 1, 2 loop -- fail - ^ --- initializer expressions -do $$ -declare x int := sin(0); -begin - raise notice 'x = %', x; -end$$; -NOTICE: x = 0 -do $$ -declare x int := 1/0; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: division by zero -CONTEXT: SQL expression "1/0" -PL/pgSQL function inline_code_block line 2 during statement block local variable initialization -do $$ -declare x bigint[] := array[1,3,5]; -begin - raise notice 'x = %', x; -end$$; -NOTICE: x = {1,3,5} -do $$ -declare x record := row(1,2,3); -begin - raise notice 'x = %', x; -end$$; -NOTICE: x = (1,2,3) -do $$ -declare x var_record := row(1,2); -begin - raise notice 'x = %', x; -end$$; -NOTICE: x = (1,2) --- NOT NULL -do $$ -declare x int not null; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: variable "x" must have a default value, since it's declared NOT NULL -LINE 2: declare x int not null; -- fail - ^ -do $$ -declare x int not null := 42; -begin - raise notice 'x = %', x; - x := null; -- fail -end$$; -NOTICE: x = 42 -ERROR: null value cannot be assigned to variable "x" declared NOT NULL -CONTEXT: PL/pgSQL function inline_code_block line 5 at assignment -do $$ -declare x int not null := null; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: null value cannot be assigned to variable "x" declared NOT NULL -CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization -do $$ -declare x record not null; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: variable "x" must have a default value, since it's declared NOT NULL -LINE 2: declare x record not null; -- fail - ^ -do $$ -declare x record not null := row(42); -begin - raise notice 'x = %', x; - x := row(null); -- ok - raise notice 'x = %', x; - x := null; -- fail -end$$; -NOTICE: x = (42) -NOTICE: x = () -ERROR: null value cannot be assigned to variable "x" declared NOT NULL -CONTEXT: PL/pgSQL function inline_code_block line 7 at assignment -do $$ -declare x record not null := null; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: null value cannot be assigned to variable "x" declared NOT NULL -CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization -do $$ -declare x var_record not null; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: variable "x" must have a default value, since it's declared NOT NULL -LINE 2: declare x var_record not null; -- fail - ^ -do $$ -declare x var_record not null := row(41,42); -begin - raise notice 'x = %', x; - x := row(null,null); -- ok - raise notice 'x = %', x; - x := null; -- fail -end$$; -NOTICE: x = (41,42) -NOTICE: x = (,) -ERROR: null value cannot be assigned to variable "x" declared NOT NULL -CONTEXT: PL/pgSQL function inline_code_block line 7 at assignment -do $$ -declare x var_record not null := null; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: null value cannot be assigned to variable "x" declared NOT NULL -CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization --- Check that variables are reinitialized on block re-entry. -do $$ -begin - for i in 1..3 loop - declare - x int; - y int := i; - r record; - c var_record; - begin - if i = 1 then - x := 42; - r := row(i, i+1); - c := row(i, i+1); - end if; - raise notice 'x = %', x; - raise notice 'y = %', y; - raise notice 'r = %', r; - raise notice 'c = %', c; - end; - end loop; -end$$; -NOTICE: x = 42 -NOTICE: y = 1 -NOTICE: r = (1,2) -NOTICE: c = (1,2) -NOTICE: x = -NOTICE: y = 2 -NOTICE: r = -NOTICE: c = -NOTICE: x = -NOTICE: y = 3 -NOTICE: r = -NOTICE: c = --- Check enforcement of domain constraints during initialization -do $$ -declare x int_nn; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: domain int_nn does not allow null values -CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization -do $$ -declare x int_nn := null; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: domain int_nn does not allow null values -CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization -do $$ -declare x int_nn := 42; -begin - raise notice 'x = %', x; - x := null; -- fail -end$$; -NOTICE: x = 42 -ERROR: domain int_nn does not allow null values -CONTEXT: PL/pgSQL function inline_code_block line 5 at assignment -do $$ -declare x var_record_nn; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: domain var_record_nn does not allow null values -CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization -do $$ -declare x var_record_nn := null; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: domain var_record_nn does not allow null values -CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization -do $$ -declare x var_record_nn := row(1,2); -begin - raise notice 'x = %', x; - x := row(null,null); -- ok - x := null; -- fail -end$$; -NOTICE: x = (1,2) -ERROR: domain var_record_nn does not allow null values -CONTEXT: PL/pgSQL function inline_code_block line 6 at assignment -do $$ -declare x var_record_colnn; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: value for domain var_record_colnn violates check constraint "var_record_colnn_check" -CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization -do $$ -declare x var_record_colnn := null; -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: value for domain var_record_colnn violates check constraint "var_record_colnn_check" -CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization -do $$ -declare x var_record_colnn := row(1,null); -- fail -begin - raise notice 'x = %', x; -end$$; -ERROR: value for domain var_record_colnn violates check constraint "var_record_colnn_check" -CONTEXT: PL/pgSQL function inline_code_block line 2 during statement block local variable initialization -do $$ -declare x var_record_colnn := row(1,2); -begin - raise notice 'x = %', x; - x := null; -- fail -end$$; -NOTICE: x = (1,2) -ERROR: value for domain var_record_colnn violates check constraint "var_record_colnn_check" -CONTEXT: PL/pgSQL function inline_code_block line 5 at assignment -do $$ -declare x var_record_colnn := row(1,2); -begin - raise notice 'x = %', x; - x := row(null,null); -- fail -end$$; -NOTICE: x = (1,2) -ERROR: value for domain var_record_colnn violates check constraint "var_record_colnn_check" -CONTEXT: PL/pgSQL function inline_code_block line 5 at assignment +psql: error: connection to server on socket "/tmp/pg_regress-fjdmtd/.s.PGSQL.40000" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached.