diff -U3 /tmp/cirrus-ci-build/src/pl/plperl/expected/plperl_trigger.out /tmp/cirrus-ci-build/build-32/testrun/plperl/regress/results/plperl_trigger.out --- /tmp/cirrus-ci-build/src/pl/plperl/expected/plperl_trigger.out 2024-03-28 13:35:02.751437097 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plperl/regress/results/plperl_trigger.out 2024-03-28 13:41:54.475155656 +0000 @@ -119,274 +119,7 @@ NOTICE: $_TD->{table_name} = 'trigger_test_generated' NOTICE: $_TD->{table_schema} = 'public' NOTICE: $_TD->{when} = 'BEFORE' -NOTICE: $_TD->{argc} = '0' -NOTICE: $_TD->{event} = 'INSERT' -NOTICE: $_TD->{level} = 'ROW' -NOTICE: $_TD->{name} = 'show_trigger_data_trig_after' -NOTICE: $_TD->{new} = {'i' => '1', 'j' => '2'} -NOTICE: $_TD->{relid} = 'bogus:12345' -NOTICE: $_TD->{relname} = 'trigger_test_generated' -NOTICE: $_TD->{table_name} = 'trigger_test_generated' -NOTICE: $_TD->{table_schema} = 'public' -NOTICE: $_TD->{when} = 'AFTER' -update trigger_test_generated set i = 11 where i = 1; -NOTICE: $_TD->{argc} = '0' -NOTICE: $_TD->{event} = 'UPDATE' -NOTICE: $_TD->{level} = 'ROW' -NOTICE: $_TD->{name} = 'show_trigger_data_trig_before' -NOTICE: $_TD->{new} = {'i' => '11'} -NOTICE: $_TD->{old} = {'i' => '1', 'j' => '2'} -NOTICE: $_TD->{relid} = 'bogus:12345' -NOTICE: $_TD->{relname} = 'trigger_test_generated' -NOTICE: $_TD->{table_name} = 'trigger_test_generated' -NOTICE: $_TD->{table_schema} = 'public' -NOTICE: $_TD->{when} = 'BEFORE' -NOTICE: $_TD->{argc} = '0' -NOTICE: $_TD->{event} = 'UPDATE' -NOTICE: $_TD->{level} = 'ROW' -NOTICE: $_TD->{name} = 'show_trigger_data_trig_after' -NOTICE: $_TD->{new} = {'i' => '11', 'j' => '22'} -NOTICE: $_TD->{old} = {'i' => '1', 'j' => '2'} -NOTICE: $_TD->{relid} = 'bogus:12345' -NOTICE: $_TD->{relname} = 'trigger_test_generated' -NOTICE: $_TD->{table_name} = 'trigger_test_generated' -NOTICE: $_TD->{table_schema} = 'public' -NOTICE: $_TD->{when} = 'AFTER' -delete from trigger_test_generated; -NOTICE: $_TD->{argc} = '0' -NOTICE: $_TD->{event} = 'DELETE' -NOTICE: $_TD->{level} = 'ROW' -NOTICE: $_TD->{name} = 'show_trigger_data_trig_before' -NOTICE: $_TD->{old} = {'i' => '11', 'j' => '22'} -NOTICE: $_TD->{relid} = 'bogus:12345' -NOTICE: $_TD->{relname} = 'trigger_test_generated' -NOTICE: $_TD->{table_name} = 'trigger_test_generated' -NOTICE: $_TD->{table_schema} = 'public' -NOTICE: $_TD->{when} = 'BEFORE' -NOTICE: $_TD->{argc} = '0' -NOTICE: $_TD->{event} = 'DELETE' -NOTICE: $_TD->{level} = 'ROW' -NOTICE: $_TD->{name} = 'show_trigger_data_trig_after' -NOTICE: $_TD->{old} = {'i' => '11', 'j' => '22'} -NOTICE: $_TD->{relid} = 'bogus:12345' -NOTICE: $_TD->{relname} = 'trigger_test_generated' -NOTICE: $_TD->{table_name} = 'trigger_test_generated' -NOTICE: $_TD->{table_schema} = 'public' -NOTICE: $_TD->{when} = 'AFTER' -DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated; -DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated; -insert into trigger_test values(1,'insert', '("(1)")'); -CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; -CREATE TRIGGER show_trigger_data_trig -INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view -FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); -insert into trigger_test_view values(2,'insert', '("(2)")'); -NOTICE: $_TD->{argc} = '2' -NOTICE: $_TD->{args} = ['24', 'skidoo view'] -NOTICE: $_TD->{event} = 'INSERT' -NOTICE: $_TD->{level} = 'ROW' -NOTICE: $_TD->{name} = 'show_trigger_data_trig' -NOTICE: $_TD->{new} = {'foo' => {'rfoo' => {'i' => '2'}}, 'i' => '2', 'v' => 'insert'} -NOTICE: $_TD->{relid} = 'bogus:12345' -NOTICE: $_TD->{relname} = 'trigger_test_view' -NOTICE: $_TD->{table_name} = 'trigger_test_view' -NOTICE: $_TD->{table_schema} = 'public' -NOTICE: $_TD->{when} = 'INSTEAD OF' -update trigger_test_view set v = 'update', foo = '("(3)")' where i = 1; -NOTICE: $_TD->{argc} = '2' -NOTICE: $_TD->{args} = ['24', 'skidoo view'] -NOTICE: $_TD->{event} = 'UPDATE' -NOTICE: $_TD->{level} = 'ROW' -NOTICE: $_TD->{name} = 'show_trigger_data_trig' -NOTICE: $_TD->{new} = {'foo' => {'rfoo' => {'i' => '3'}}, 'i' => '1', 'v' => 'update'} -NOTICE: $_TD->{old} = {'foo' => {'rfoo' => {'i' => '1'}}, 'i' => '1', 'v' => 'insert'} -NOTICE: $_TD->{relid} = 'bogus:12345' -NOTICE: $_TD->{relname} = 'trigger_test_view' -NOTICE: $_TD->{table_name} = 'trigger_test_view' -NOTICE: $_TD->{table_schema} = 'public' -NOTICE: $_TD->{when} = 'INSTEAD OF' -delete from trigger_test_view; -NOTICE: $_TD->{argc} = '2' -NOTICE: $_TD->{args} = ['24', 'skidoo view'] -NOTICE: $_TD->{event} = 'DELETE' -NOTICE: $_TD->{level} = 'ROW' -NOTICE: $_TD->{name} = 'show_trigger_data_trig' -NOTICE: $_TD->{old} = {'foo' => {'rfoo' => {'i' => '1'}}, 'i' => '1', 'v' => 'insert'} -NOTICE: $_TD->{relid} = 'bogus:12345' -NOTICE: $_TD->{relname} = 'trigger_test_view' -NOTICE: $_TD->{table_name} = 'trigger_test_view' -NOTICE: $_TD->{table_schema} = 'public' -NOTICE: $_TD->{when} = 'INSTEAD OF' -DROP VIEW trigger_test_view; -delete from trigger_test; -DROP FUNCTION trigger_data(); -CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$ - - if (($_TD->{new}{i}>=100) || ($_TD->{new}{i}<=0)) - { - return "SKIP"; # Skip INSERT/UPDATE command - } - elsif ($_TD->{new}{v} ne "immortal") - { - $_TD->{new}{v} .= "(modified by trigger)"; - $_TD->{new}{foo}{rfoo}{i}++; - return "MODIFY"; # Modify tuple and proceed INSERT/UPDATE command - } - else - { - return; # Proceed INSERT/UPDATE command - } -$$ LANGUAGE plperl; -CREATE TRIGGER "test_valid_id_trig" BEFORE INSERT OR UPDATE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE "valid_id"(); -INSERT INTO trigger_test (i, v, foo) VALUES (1,'first line', '("(1)")'); -INSERT INTO trigger_test (i, v, foo) VALUES (2,'second line', '("(2)")'); -INSERT INTO trigger_test (i, v, foo) VALUES (3,'third line', '("(3)")'); -INSERT INTO trigger_test (i, v, foo) VALUES (4,'immortal', '("(4)")'); -INSERT INTO trigger_test (i, v) VALUES (101,'bad id'); -SELECT * FROM trigger_test; - i | v | foo ----+----------------------------------+--------- - 1 | first line(modified by trigger) | ("(2)") - 2 | second line(modified by trigger) | ("(3)") - 3 | third line(modified by trigger) | ("(4)") - 4 | immortal | ("(4)") -(4 rows) - -UPDATE trigger_test SET i = 5 where i=3; -UPDATE trigger_test SET i = 100 where i=1; -SELECT * FROM trigger_test; - i | v | foo ----+------------------------------------------------------+--------- - 1 | first line(modified by trigger) | ("(2)") - 2 | second line(modified by trigger) | ("(3)") - 4 | immortal | ("(4)") - 5 | third line(modified by trigger)(modified by trigger) | ("(5)") -(4 rows) - -DROP TRIGGER "test_valid_id_trig" ON trigger_test; -CREATE OR REPLACE FUNCTION trigger_recurse() RETURNS trigger AS $$ - use strict; - - if ($_TD->{new}{i} == 10000) - { - spi_exec_query("insert into trigger_test (i, v) values (20000, 'child');"); - - if ($_TD->{new}{i} != 10000) - { - die "recursive trigger modified: ". $_TD->{new}{i}; - } - } - return; -$$ LANGUAGE plperl; -CREATE TRIGGER "test_trigger_recurse" BEFORE INSERT ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE "trigger_recurse"(); -INSERT INTO trigger_test (i, v) values (10000, 'top'); -SELECT * FROM trigger_test; - i | v | foo --------+------------------------------------------------------+--------- - 1 | first line(modified by trigger) | ("(2)") - 2 | second line(modified by trigger) | ("(3)") - 4 | immortal | ("(4)") - 5 | third line(modified by trigger)(modified by trigger) | ("(5)") - 20000 | child | - 10000 | top | -(6 rows) - -CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$ - if ($_TD->{old}{v} eq $_TD->{args}[0]) - { - return "SKIP"; # Skip DELETE command - } - else - { - return; # Proceed DELETE command - }; -$$ LANGUAGE plperl; -CREATE TRIGGER "immortal_trig" BEFORE DELETE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE immortal('immortal'); -DELETE FROM trigger_test; -SELECT * FROM trigger_test; - i | v | foo ----+----------+--------- - 4 | immortal | ("(4)") -(1 row) - -CREATE FUNCTION direct_trigger() RETURNS trigger AS $$ - return; -$$ LANGUAGE plperl; -SELECT direct_trigger(); -ERROR: trigger functions can only be called as triggers -CONTEXT: compilation of PL/Perl function "direct_trigger" --- check that SQL run in trigger code can see transition tables -CREATE TABLE transition_table_test (id int, name text); -INSERT INTO transition_table_test VALUES (1, 'a'); -CREATE FUNCTION transition_table_test_f() RETURNS trigger LANGUAGE plperl AS -$$ - my $cursor = spi_query("SELECT * FROM old_table"); - my $row = spi_fetchrow($cursor); - defined($row) || die "expected a row"; - elog(INFO, "old: " . $row->{id} . " -> " . $row->{name}); - my $row = spi_fetchrow($cursor); - !defined($row) || die "expected no more rows"; - - my $cursor = spi_query("SELECT * FROM new_table"); - my $row = spi_fetchrow($cursor); - defined($row) || die "expected a row"; - elog(INFO, "new: " . $row->{id} . " -> " . $row->{name}); - my $row = spi_fetchrow($cursor); - !defined($row) || die "expected no more rows"; - - return undef; -$$; -CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test - REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table - FOR EACH STATEMENT EXECUTE PROCEDURE transition_table_test_f(); -UPDATE transition_table_test SET name = 'b'; -INFO: old: 1 -> a -INFO: new: 1 -> b -DROP TABLE transition_table_test; -DROP FUNCTION transition_table_test_f(); --- test plperl command triggers -create or replace function perlsnitch() returns event_trigger language plperl as $$ - elog(NOTICE, "perlsnitch: " . $_TD->{event} . " " . $_TD->{tag} . " "); -$$; -create event trigger perl_a_snitch on ddl_command_start - execute procedure perlsnitch(); -create event trigger perl_b_snitch on ddl_command_end - execute procedure perlsnitch(); -create or replace function foobar() returns int language sql as $$select 1;$$; -NOTICE: perlsnitch: ddl_command_start CREATE FUNCTION -NOTICE: perlsnitch: ddl_command_end CREATE FUNCTION -alter function foobar() cost 77; -NOTICE: perlsnitch: ddl_command_start ALTER FUNCTION -NOTICE: perlsnitch: ddl_command_end ALTER FUNCTION -drop function foobar(); -NOTICE: perlsnitch: ddl_command_start DROP FUNCTION -NOTICE: perlsnitch: ddl_command_end DROP FUNCTION -create table foo(); -NOTICE: perlsnitch: ddl_command_start CREATE TABLE -NOTICE: perlsnitch: ddl_command_end CREATE TABLE -drop table foo; -NOTICE: perlsnitch: ddl_command_start DROP TABLE -NOTICE: perlsnitch: ddl_command_end DROP TABLE -drop event trigger perl_a_snitch; -drop event trigger perl_b_snitch; --- dealing with generated columns -CREATE FUNCTION generated_test_func1() RETURNS trigger -LANGUAGE plperl -AS $$ -$_TD->{new}{j} = 5; # not allowed -return 'MODIFY'; -$$; -CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated -FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); -TRUNCATE trigger_test_generated; -INSERT INTO trigger_test_generated (i) VALUES (1); -ERROR: cannot set generated column "j" -CONTEXT: PL/Perl function "generated_test_func1" -SELECT * FROM trigger_test_generated; - i | j ----+--- -(0 rows) - +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/plperl/expected/plperl_transaction.out /tmp/cirrus-ci-build/build-32/testrun/plperl/regress/results/plperl_transaction.out --- /tmp/cirrus-ci-build/src/pl/plperl/expected/plperl_transaction.out 2024-03-28 13:35:02.751437097 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plperl/regress/results/plperl_transaction.out 2024-03-28 13:41:55.059154932 +0000 @@ -201,44 +201,7 @@ spi_commit(); elog(WARNING, 'should not get here'); $$; -ERROR: insert or update on table "testfk" violates foreign key constraint "testfk_f1_fkey" at line 4. -CONTEXT: PL/Perl anonymous code block -SELECT * FROM testpk; - id ----- -(0 rows) - -SELECT * FROM testfk; - f1 ----- -(0 rows) - -DO LANGUAGE plperl $$ -# this insert will fail during commit: -spi_exec_query("INSERT INTO testfk VALUES (0)"); -eval { - spi_commit(); -}; -if ($@) { - elog(INFO, $@); -} -# these inserts should work: -spi_exec_query("INSERT INTO testpk VALUES (1)"); -spi_exec_query("INSERT INTO testfk VALUES (1)"); -$$; -INFO: insert or update on table "testfk" violates foreign key constraint "testfk_f1_fkey" at line 5. - -SELECT * FROM testpk; - id ----- - 1 -(1 row) - -SELECT * FROM testfk; - f1 ----- - 1 -(1 row) - -DROP TABLE test1; -DROP TABLE test2; +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost