diff -U3 /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_populate.out /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_populate.out --- /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_populate.out 2024-03-29 10:06:51.843270926 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_populate.out 2024-03-29 10:13:33.243272679 +0000 @@ -8,15 +8,7 @@ INSERT INTO taxonomy (name) VALUES ('HIV II') ; INSERT INTO taxonomy (name) VALUES ('HCV') ; INSERT INTO entry (accession, txid) VALUES ('A00001', '1') ; -INSERT INTO entry (accession, txid) VALUES ('A00002', '1') ; -INSERT INTO entry (accession, txid) VALUES ('A00003', '1') ; -INSERT INTO entry (accession, txid) VALUES ('A00004', '2') ; -INSERT INTO entry (accession, txid) VALUES ('A00005', '2') ; -INSERT INTO entry (accession, txid) VALUES ('A00006', '3') ; -INSERT INTO sequences (sequence, eid, product, multipart) VALUES ('ABCDEF', 1, 'env', 'true') ; -INSERT INTO xsequences (sequence, pid) VALUES ('GHIJKL', 1) ; -INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 2, 'env') ; -INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 3, 'env') ; -INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 4, 'gag') ; -INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 5, 'env') ; -INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 6, 'ns1') ; +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/plpython/expected/plpython_spi.out /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_spi.out --- /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_spi.out 2024-03-29 10:06:51.843270926 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_spi.out 2024-03-29 10:13:33.491272632 +0000 @@ -108,25 +108,13 @@ SELECT join_sequences(sequences) FROM sequences; join_sequences ---------------- - ABCDEFGHIJKL - ABCDEF - ABCDEF - ABCDEF - ABCDEF - ABCDEF -(6 rows) +(0 rows) SELECT join_sequences(sequences) FROM sequences WHERE join_sequences(sequences) ~* '^A'; join_sequences ---------------- - ABCDEFGHIJKL - ABCDEF - ABCDEF - ABCDEF - ABCDEF - ABCDEF -(6 rows) +(0 rows) SELECT join_sequences(sequences) FROM sequences WHERE join_sequences(sequences) ~* '^B'; diff -U3 /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_trigger.out /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_trigger.out --- /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_trigger.out 2024-03-29 10:06:51.843270926 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_trigger.out 2024-03-29 10:13:33.959272542 +0000 @@ -121,500 +121,7 @@ NOTICE: TD[table_name] => trigger_test NOTICE: TD[table_schema] => public NOTICE: TD[when] => BEFORE -NOTICE: TD[args] => ['23', 'skidoo'] -NOTICE: TD[event] => INSERT -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig_after -NOTICE: TD[new] => {'i': 1, 'v': 'insert'} -NOTICE: TD[old] => None -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => AFTER -update trigger_test set v = 'update' where i = 1; -NOTICE: TD[args] => ['23', 'skidoo'] -NOTICE: TD[event] => UPDATE -NOTICE: TD[level] => STATEMENT -NOTICE: TD[name] => show_trigger_data_trig_stmt -NOTICE: TD[new] => None -NOTICE: TD[old] => None -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => BEFORE -NOTICE: TD[args] => ['23', 'skidoo'] -NOTICE: TD[event] => UPDATE -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig_before -NOTICE: TD[new] => {'i': 1, 'v': 'update'} -NOTICE: TD[old] => {'i': 1, 'v': 'insert'} -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => BEFORE -NOTICE: TD[args] => ['23', 'skidoo'] -NOTICE: TD[event] => UPDATE -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig_after -NOTICE: TD[new] => {'i': 1, 'v': 'update'} -NOTICE: TD[old] => {'i': 1, 'v': 'insert'} -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => AFTER -delete from trigger_test; -NOTICE: TD[args] => ['23', 'skidoo'] -NOTICE: TD[event] => DELETE -NOTICE: TD[level] => STATEMENT -NOTICE: TD[name] => show_trigger_data_trig_stmt -NOTICE: TD[new] => None -NOTICE: TD[old] => None -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => BEFORE -NOTICE: TD[args] => ['23', 'skidoo'] -NOTICE: TD[event] => DELETE -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig_before -NOTICE: TD[new] => None -NOTICE: TD[old] => {'i': 1, 'v': 'update'} -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => BEFORE -NOTICE: TD[args] => ['23', 'skidoo'] -NOTICE: TD[event] => DELETE -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig_after -NOTICE: TD[new] => None -NOTICE: TD[old] => {'i': 1, 'v': 'update'} -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => AFTER -truncate table trigger_test; -NOTICE: TD[args] => ['23', 'skidoo'] -NOTICE: TD[event] => TRUNCATE -NOTICE: TD[level] => STATEMENT -NOTICE: TD[name] => show_trigger_data_trig_stmt -NOTICE: TD[new] => None -NOTICE: TD[old] => None -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => BEFORE -DROP TRIGGER show_trigger_data_trig_stmt on trigger_test; -DROP TRIGGER show_trigger_data_trig_before on trigger_test; -DROP TRIGGER show_trigger_data_trig_after on trigger_test; -CREATE TRIGGER show_trigger_data_trig_before -BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated -FOR EACH ROW EXECUTE PROCEDURE trigger_data(); -CREATE TRIGGER show_trigger_data_trig_after -AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated -FOR EACH ROW EXECUTE PROCEDURE trigger_data(); -insert into trigger_test_generated (i) values (1); -NOTICE: TD[args] => None -NOTICE: TD[event] => INSERT -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig_before -NOTICE: TD[new] => {'i': 1} -NOTICE: TD[old] => None -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test_generated -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => BEFORE -NOTICE: TD[args] => None -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[old] => None -NOTICE: TD[relid] => bogus:12345 -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[args] => None -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[table_name] => trigger_test_generated -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => BEFORE -NOTICE: TD[args] => None -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[table_name] => trigger_test_generated -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => AFTER -delete from trigger_test_generated; -NOTICE: TD[args] => None -NOTICE: TD[event] => DELETE -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig_before -NOTICE: TD[new] => None -NOTICE: TD[old] => {'i': 11, 'j': 22} -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test_generated -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => BEFORE -NOTICE: TD[args] => None -NOTICE: TD[event] => DELETE -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig_after -NOTICE: TD[new] => None -NOTICE: TD[old] => {'i': 11, 'j': 22} -NOTICE: TD[relid] => bogus:12345 -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'); -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'); -NOTICE: TD[args] => ['24', 'skidoo view'] -NOTICE: TD[event] => INSERT -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig -NOTICE: TD[new] => {'i': 2, 'v': 'insert'} -NOTICE: TD[old] => None -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test_view -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => INSTEAD OF -update trigger_test_view set v = 'update' where i = 1; -NOTICE: TD[args] => ['24', 'skidoo view'] -NOTICE: TD[event] => UPDATE -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig -NOTICE: TD[new] => {'i': 1, 'v': 'update'} -NOTICE: TD[old] => {'i': 1, 'v': 'insert'} -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test_view -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => INSTEAD OF -delete from trigger_test_view; -NOTICE: TD[args] => ['24', 'skidoo view'] -NOTICE: TD[event] => DELETE -NOTICE: TD[level] => ROW -NOTICE: TD[name] => show_trigger_data_trig -NOTICE: TD[new] => None -NOTICE: TD[old] => {'i': 1, 'v': 'insert'} -NOTICE: TD[relid] => bogus:12345 -NOTICE: TD[table_name] => trigger_test_view -NOTICE: TD[table_schema] => public -NOTICE: TD[when] => INSTEAD OF -DROP FUNCTION trigger_data() CASCADE; -NOTICE: drop cascades to trigger show_trigger_data_trig on view trigger_test_view -DROP VIEW trigger_test_view; -delete from trigger_test; --- --- trigger error handling --- -INSERT INTO trigger_test VALUES (0, 'zero'); --- returning non-string from trigger function -CREATE FUNCTION stupid1() RETURNS trigger -AS $$ - return 37 -$$ LANGUAGE plpython3u; -CREATE TRIGGER stupid_trigger1 -BEFORE INSERT ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE stupid1(); -INSERT INTO trigger_test VALUES (1, 'one'); -ERROR: unexpected return value from trigger procedure -DETAIL: Expected None or a string. -CONTEXT: PL/Python function "stupid1" -DROP TRIGGER stupid_trigger1 ON trigger_test; --- returning MODIFY from DELETE trigger -CREATE FUNCTION stupid2() RETURNS trigger -AS $$ - return "MODIFY" -$$ LANGUAGE plpython3u; -CREATE TRIGGER stupid_trigger2 -BEFORE DELETE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE stupid2(); -DELETE FROM trigger_test WHERE i = 0; -WARNING: PL/Python trigger function returned "MODIFY" in a DELETE trigger -- ignored -DROP TRIGGER stupid_trigger2 ON trigger_test; -INSERT INTO trigger_test VALUES (0, 'zero'); --- returning unrecognized string from trigger function -CREATE FUNCTION stupid3() RETURNS trigger -AS $$ - return "foo" -$$ LANGUAGE plpython3u; -CREATE TRIGGER stupid_trigger3 -BEFORE UPDATE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE stupid3(); -UPDATE trigger_test SET v = 'null' WHERE i = 0; -ERROR: unexpected return value from trigger procedure -DETAIL: Expected None, "OK", "SKIP", or "MODIFY". -CONTEXT: PL/Python function "stupid3" -DROP TRIGGER stupid_trigger3 ON trigger_test; --- Unicode variant -CREATE FUNCTION stupid3u() RETURNS trigger -AS $$ - return "foo" -$$ LANGUAGE plpython3u; -CREATE TRIGGER stupid_trigger3 -BEFORE UPDATE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE stupid3u(); -UPDATE trigger_test SET v = 'null' WHERE i = 0; -ERROR: unexpected return value from trigger procedure -DETAIL: Expected None, "OK", "SKIP", or "MODIFY". -CONTEXT: PL/Python function "stupid3u" -DROP TRIGGER stupid_trigger3 ON trigger_test; --- deleting the TD dictionary -CREATE FUNCTION stupid4() RETURNS trigger -AS $$ - del TD["new"] - return "MODIFY"; -$$ LANGUAGE plpython3u; -CREATE TRIGGER stupid_trigger4 -BEFORE UPDATE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE stupid4(); -UPDATE trigger_test SET v = 'null' WHERE i = 0; -ERROR: TD["new"] deleted, cannot modify row -CONTEXT: while modifying trigger row -PL/Python function "stupid4" -DROP TRIGGER stupid_trigger4 ON trigger_test; --- TD not a dictionary -CREATE FUNCTION stupid5() RETURNS trigger -AS $$ - TD["new"] = ['foo', 'bar'] - return "MODIFY"; -$$ LANGUAGE plpython3u; -CREATE TRIGGER stupid_trigger5 -BEFORE UPDATE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE stupid5(); -UPDATE trigger_test SET v = 'null' WHERE i = 0; -ERROR: TD["new"] is not a dictionary -CONTEXT: while modifying trigger row -PL/Python function "stupid5" -DROP TRIGGER stupid_trigger5 ON trigger_test; --- TD not having string keys -CREATE FUNCTION stupid6() RETURNS trigger -AS $$ - TD["new"] = {1: 'foo', 2: 'bar'} - return "MODIFY"; -$$ LANGUAGE plpython3u; -CREATE TRIGGER stupid_trigger6 -BEFORE UPDATE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE stupid6(); -UPDATE trigger_test SET v = 'null' WHERE i = 0; -ERROR: TD["new"] dictionary key at ordinal position 0 is not a string -CONTEXT: while modifying trigger row -PL/Python function "stupid6" -DROP TRIGGER stupid_trigger6 ON trigger_test; --- TD keys not corresponding to row columns -CREATE FUNCTION stupid7() RETURNS trigger -AS $$ - TD["new"] = {'v': 'foo', 'a': 'bar'} - return "MODIFY"; -$$ LANGUAGE plpython3u; -CREATE TRIGGER stupid_trigger7 -BEFORE UPDATE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE stupid7(); -UPDATE trigger_test SET v = 'null' WHERE i = 0; -ERROR: key "a" found in TD["new"] does not exist as a column in the triggering row -CONTEXT: while modifying trigger row -PL/Python function "stupid7" -DROP TRIGGER stupid_trigger7 ON trigger_test; --- Unicode variant -CREATE FUNCTION stupid7u() RETURNS trigger -AS $$ - TD["new"] = {'v': 'foo', 'a': 'bar'} - return "MODIFY" -$$ LANGUAGE plpython3u; -CREATE TRIGGER stupid_trigger7 -BEFORE UPDATE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE stupid7u(); -UPDATE trigger_test SET v = 'null' WHERE i = 0; -ERROR: key "a" found in TD["new"] does not exist as a column in the triggering row -CONTEXT: while modifying trigger row -PL/Python function "stupid7u" -DROP TRIGGER stupid_trigger7 ON trigger_test; --- calling a trigger function directly -SELECT stupid7(); -ERROR: trigger functions can only be called as triggers --- --- Null values --- -SELECT * FROM trigger_test; - i | v ----+------ - 0 | zero -(1 row) - -CREATE FUNCTION test_null() RETURNS trigger -AS $$ - TD["new"]['v'] = None - return "MODIFY" -$$ LANGUAGE plpython3u; -CREATE TRIGGER test_null_trigger -BEFORE UPDATE ON trigger_test -FOR EACH ROW EXECUTE PROCEDURE test_null(); -UPDATE trigger_test SET v = 'null' WHERE i = 0; -DROP TRIGGER test_null_trigger ON trigger_test; -SELECT * FROM trigger_test; - i | v ----+--- - 0 | -(1 row) - --- --- Test that triggers honor typmod when assigning to tuple fields, --- as per an early 9.0 bug report --- -SET DateStyle = 'ISO'; -CREATE FUNCTION set_modif_time() RETURNS trigger AS $$ - TD['new']['modif_time'] = '2010-10-13 21:57:28.930486' - return 'MODIFY' -$$ LANGUAGE plpython3u; -CREATE TABLE pb (a TEXT, modif_time TIMESTAMP(0) WITHOUT TIME ZONE); -CREATE TRIGGER set_modif_time BEFORE UPDATE ON pb - FOR EACH ROW EXECUTE PROCEDURE set_modif_time(); -INSERT INTO pb VALUES ('a', '2010-10-09 21:57:33.930486'); -SELECT * FROM pb; - a | modif_time ----+--------------------- - a | 2010-10-09 21:57:34 -(1 row) - -UPDATE pb SET a = 'b'; -SELECT * FROM pb; - a | modif_time ----+--------------------- - b | 2010-10-13 21:57:29 -(1 row) - --- triggers for tables with composite types -CREATE TABLE comp1 (i integer, j boolean); -CREATE TYPE comp2 AS (k integer, l boolean); -CREATE TABLE composite_trigger_test (f1 comp1, f2 comp2); -CREATE FUNCTION composite_trigger_f() RETURNS trigger AS $$ - TD['new']['f1'] = (3, False) - TD['new']['f2'] = {'k': 7, 'l': 'yes', 'ignored': 10} - return 'MODIFY' -$$ LANGUAGE plpython3u; -CREATE TRIGGER composite_trigger BEFORE INSERT ON composite_trigger_test - FOR EACH ROW EXECUTE PROCEDURE composite_trigger_f(); -INSERT INTO composite_trigger_test VALUES (NULL, NULL); -SELECT * FROM composite_trigger_test; - f1 | f2 --------+------- - (3,f) | (7,t) -(1 row) - --- triggers with composite type columns (bug #6559) -CREATE TABLE composite_trigger_noop_test (f1 comp1, f2 comp2); -CREATE FUNCTION composite_trigger_noop_f() RETURNS trigger AS $$ - return 'MODIFY' -$$ LANGUAGE plpython3u; -CREATE TRIGGER composite_trigger_noop BEFORE INSERT ON composite_trigger_noop_test - FOR EACH ROW EXECUTE PROCEDURE composite_trigger_noop_f(); -INSERT INTO composite_trigger_noop_test VALUES (NULL, NULL); -INSERT INTO composite_trigger_noop_test VALUES (ROW(1, 'f'), NULL); -INSERT INTO composite_trigger_noop_test VALUES (ROW(NULL, 't'), ROW(1, 'f')); -SELECT * FROM composite_trigger_noop_test; - f1 | f2 --------+------- - | - (1,f) | - (,t) | (1,f) -(3 rows) - --- nested composite types -CREATE TYPE comp3 AS (c1 comp1, c2 comp2, m integer); -CREATE TABLE composite_trigger_nested_test(c comp3); -CREATE FUNCTION composite_trigger_nested_f() RETURNS trigger AS $$ - return 'MODIFY' -$$ LANGUAGE plpython3u; -CREATE TRIGGER composite_trigger_nested BEFORE INSERT ON composite_trigger_nested_test - FOR EACH ROW EXECUTE PROCEDURE composite_trigger_nested_f(); -INSERT INTO composite_trigger_nested_test VALUES (NULL); -INSERT INTO composite_trigger_nested_test VALUES (ROW(ROW(1, 'f'), NULL, 3)); -INSERT INTO composite_trigger_nested_test VALUES (ROW(ROW(NULL, 't'), ROW(1, 'f'), NULL)); -SELECT * FROM composite_trigger_nested_test; - c -------------------- - - ("(1,f)",,3) - ("(,t)","(1,f)",) -(3 rows) - --- check that using a function as a trigger over two tables works correctly -CREATE FUNCTION trig1234() RETURNS trigger LANGUAGE plpython3u AS $$ - TD["new"]["data"] = '1234' - return 'MODIFY' -$$; -CREATE TABLE a(data text); -CREATE TABLE b(data int); -- different type conversion -CREATE TRIGGER a_t BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE trig1234(); -CREATE TRIGGER b_t BEFORE INSERT ON b FOR EACH ROW EXECUTE PROCEDURE trig1234(); -INSERT INTO a DEFAULT VALUES; -SELECT * FROM a; - data ------- - 1234 -(1 row) - -DROP TABLE a; -INSERT INTO b DEFAULT VALUES; -SELECT * FROM b; - data ------- - 1234 -(1 row) - --- 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 plpython3u AS -$$ - rv = plpy.execute("SELECT * FROM old_table") - assert(rv.nrows() == 1) - plpy.info("old: " + str(rv[0]["id"]) + " -> " + rv[0]["name"]) - rv = plpy.execute("SELECT * FROM new_table") - assert(rv.nrows() == 1) - plpy.info("new: " + str(rv[0]["id"]) + " -> " + rv[0]["name"]) - return None -$$; -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(); --- dealing with generated columns -CREATE FUNCTION generated_test_func1() RETURNS trigger -LANGUAGE plpython3u -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: while modifying trigger row -PL/Python 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/plpython/expected/plpython_quote.out /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_quote.out --- /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_quote.out 2024-03-29 10:06:51.843270926 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_quote.out 2024-03-29 10:13:34.211272493 +0000 @@ -9,48 +9,10 @@ else: raise plpy.Error("unrecognized quote type %s" % how) $$ LANGUAGE plpython3u; -SELECT quote(t, 'literal') FROM (VALUES - ('abc'), - ('a''bc'), - ('''abc'''), - (''), - (''''), - ('xyzv')) AS v(t); - quote ------------ - 'abc' - 'a''bc' - '''abc''' - '' - '''' - 'xyzv' -(6 rows) - -SELECT quote(t, 'nullable') FROM (VALUES - ('abc'), - ('a''bc'), - ('''abc'''), - (''), - (''''), - (NULL)) AS v(t); - quote ------------ - 'abc' - 'a''bc' - '''abc''' - '' - '''' - NULL -(6 rows) - -SELECT quote(t, 'ident') FROM (VALUES - ('abc'), - ('a b c'), - ('a " ''abc''')) AS v(t); - quote --------------- - abc - "a b c" - "a "" 'abc'" -(3 rows) - +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/plpython/expected/plpython_composite.out /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_composite.out --- /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_composite.out 2024-03-29 10:06:51.843270926 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_composite.out 2024-03-29 10:13:34.223272491 +0000 @@ -1,594 +1 @@ -CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$ -return (1, 2) -$$ LANGUAGE plpython3u; -SELECT multiout_simple(); - multiout_simple ------------------ - (1,2) -(1 row) - -SELECT * FROM multiout_simple(); - i | j ----+--- - 1 | 2 -(1 row) - -SELECT i, j + 2 FROM multiout_simple(); - i | ?column? ----+---------- - 1 | 4 -(1 row) - -SELECT (multiout_simple()).j + 3; - ?column? ----------- - 5 -(1 row) - -CREATE FUNCTION multiout_simple_setof(n integer = 1, OUT integer, OUT integer) RETURNS SETOF record AS $$ -return [(1, 2)] * n -$$ LANGUAGE plpython3u; -SELECT multiout_simple_setof(); - multiout_simple_setof ------------------------ - (1,2) -(1 row) - -SELECT * FROM multiout_simple_setof(); - column1 | column2 ----------+--------- - 1 | 2 -(1 row) - -SELECT * FROM multiout_simple_setof(3); - column1 | column2 ----------+--------- - 1 | 2 - 1 | 2 - 1 | 2 -(3 rows) - -CREATE FUNCTION multiout_record_as(typ text, - first text, OUT first text, - second integer, OUT second integer, - retnull boolean) RETURNS record AS $$ -if retnull: - return None -if typ == 'dict': - return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' } -elif typ == 'tuple': - return ( first, second ) -elif typ == 'list': - return [ first, second ] -elif typ == 'obj': - class type_record: pass - type_record.first = first - type_record.second = second - return type_record -elif typ == 'str': - return "('%s',%r)" % (first, second) -$$ LANGUAGE plpython3u; -SELECT * FROM multiout_record_as('dict', 'foo', 1, 'f'); - first | second --------+-------- - foo | 1 -(1 row) - -SELECT multiout_record_as('dict', 'foo', 1, 'f'); - multiout_record_as --------------------- - (foo,1) -(1 row) - -SELECT * FROM multiout_record_as('dict', null, null, false); - first | second --------+-------- - | -(1 row) - -SELECT * FROM multiout_record_as('dict', 'one', null, false); - first | second --------+-------- - one | -(1 row) - -SELECT * FROM multiout_record_as('dict', null, 2, false); - first | second --------+-------- - | 2 -(1 row) - -SELECT * FROM multiout_record_as('dict', 'three', 3, false); - first | second --------+-------- - three | 3 -(1 row) - -SELECT * FROM multiout_record_as('dict', null, null, true); - first | second --------+-------- - | -(1 row) - -SELECT * FROM multiout_record_as('tuple', null, null, false); - first | second --------+-------- - | -(1 row) - -SELECT * FROM multiout_record_as('tuple', 'one', null, false); - first | second --------+-------- - one | -(1 row) - -SELECT * FROM multiout_record_as('tuple', null, 2, false); - first | second --------+-------- - | 2 -(1 row) - -SELECT * FROM multiout_record_as('tuple', 'three', 3, false); - first | second --------+-------- - three | 3 -(1 row) - -SELECT * FROM multiout_record_as('tuple', null, null, true); - first | second --------+-------- - | -(1 row) - -SELECT * FROM multiout_record_as('list', null, null, false); - first | second --------+-------- - | -(1 row) - -SELECT * FROM multiout_record_as('list', 'one', null, false); - first | second --------+-------- - one | -(1 row) - -SELECT * FROM multiout_record_as('list', null, 2, false); - first | second --------+-------- - | 2 -(1 row) - -SELECT * FROM multiout_record_as('list', 'three', 3, false); - first | second --------+-------- - three | 3 -(1 row) - -SELECT * FROM multiout_record_as('list', null, null, true); - first | second --------+-------- - | -(1 row) - -SELECT * FROM multiout_record_as('obj', null, null, false); - first | second --------+-------- - | -(1 row) - -SELECT * FROM multiout_record_as('obj', 'one', null, false); - first | second --------+-------- - one | -(1 row) - -SELECT * FROM multiout_record_as('obj', null, 2, false); - first | second --------+-------- - | 2 -(1 row) - -SELECT * FROM multiout_record_as('obj', 'three', 3, false); - first | second --------+-------- - three | 3 -(1 row) - -SELECT * FROM multiout_record_as('obj', null, null, true); - first | second --------+-------- - | -(1 row) - -SELECT * FROM multiout_record_as('str', 'one', 1, false); - first | second --------+-------- - 'one' | 1 -(1 row) - -SELECT * FROM multiout_record_as('str', 'one', 2, false); - first | second --------+-------- - 'one' | 2 -(1 row) - -SELECT *, s IS NULL AS snull FROM multiout_record_as('tuple', 'xxx', NULL, 'f') AS f(f, s); - f | s | snull ------+---+------- - xxx | | t -(1 row) - -SELECT *, f IS NULL AS fnull, s IS NULL AS snull FROM multiout_record_as('tuple', 'xxx', 1, 't') AS f(f, s); - f | s | fnull | snull ----+---+-------+------- - | | t | t -(1 row) - -SELECT * FROM multiout_record_as('obj', NULL, 10, 'f'); - first | second --------+-------- - | 10 -(1 row) - -CREATE FUNCTION multiout_setof(n integer, - OUT power_of_2 integer, - OUT length integer) RETURNS SETOF record AS $$ -for i in range(n): - power = 2 ** i - length = plpy.execute("select length('%d')" % power)[0]['length'] - yield power, length -$$ LANGUAGE plpython3u; -SELECT * FROM multiout_setof(3); - power_of_2 | length -------------+-------- - 1 | 1 - 2 | 1 - 4 | 1 -(3 rows) - -SELECT multiout_setof(5); - multiout_setof ----------------- - (1,1) - (2,1) - (4,1) - (8,1) - (16,2) -(5 rows) - -CREATE FUNCTION multiout_return_table() RETURNS TABLE (x integer, y text) AS $$ -return [{'x': 4, 'y' :'four'}, - {'x': 7, 'y' :'seven'}, - {'x': 0, 'y' :'zero'}] -$$ LANGUAGE plpython3u; -SELECT * FROM multiout_return_table(); - x | y ----+------- - 4 | four - 7 | seven - 0 | zero -(3 rows) - -CREATE FUNCTION multiout_array(OUT integer[], OUT text) RETURNS SETOF record AS $$ -yield [[1], 'a'] -yield [[1,2], 'b'] -yield [[1,2,3], None] -$$ LANGUAGE plpython3u; -SELECT * FROM multiout_array(); - column1 | column2 ----------+--------- - {1} | a - {1,2} | b - {1,2,3} | -(3 rows) - -CREATE FUNCTION singleout_composite(OUT type_record) AS $$ -return {'first': 1, 'second': 2} -$$ LANGUAGE plpython3u; -CREATE FUNCTION multiout_composite(OUT type_record) RETURNS SETOF type_record AS $$ -return [{'first': 1, 'second': 2}, - {'first': 3, 'second': 4 }] -$$ LANGUAGE plpython3u; -SELECT * FROM singleout_composite(); - first | second --------+-------- - 1 | 2 -(1 row) - -SELECT * FROM multiout_composite(); - first | second --------+-------- - 1 | 2 - 3 | 4 -(2 rows) - --- composite OUT parameters in functions returning RECORD not supported yet -CREATE FUNCTION multiout_composite(INOUT n integer, OUT type_record) AS $$ -return (n, (n * 2, n * 3)) -$$ LANGUAGE plpython3u; -CREATE FUNCTION multiout_table_type_setof(typ text, returnnull boolean, INOUT n integer, OUT table_record) RETURNS SETOF record AS $$ -if returnnull: - d = None -elif typ == 'dict': - d = {'first': n * 2, 'second': n * 3, 'extra': 'not important'} -elif typ == 'tuple': - d = (n * 2, n * 3) -elif typ == 'list': - d = [ n * 2, n * 3 ] -elif typ == 'obj': - class d: pass - d.first = n * 2 - d.second = n * 3 -elif typ == 'str': - d = "(%r,%r)" % (n * 2, n * 3) -for i in range(n): - yield (i, d) -$$ LANGUAGE plpython3u; -SELECT * FROM multiout_composite(2); - n | column2 ----+--------- - 2 | (4,6) -(1 row) - -SELECT * FROM multiout_table_type_setof('dict', 'f', 3); - n | column2 ----+--------- - 0 | (6,9) - 1 | (6,9) - 2 | (6,9) -(3 rows) - -SELECT * FROM multiout_table_type_setof('dict', 'f', 7); - n | column2 ----+--------- - 0 | (14,21) - 1 | (14,21) - 2 | (14,21) - 3 | (14,21) - 4 | (14,21) - 5 | (14,21) - 6 | (14,21) -(7 rows) - -SELECT * FROM multiout_table_type_setof('tuple', 'f', 2); - n | column2 ----+--------- - 0 | (4,6) - 1 | (4,6) -(2 rows) - -SELECT * FROM multiout_table_type_setof('tuple', 'f', 3); - n | column2 ----+--------- - 0 | (6,9) - 1 | (6,9) - 2 | (6,9) -(3 rows) - -SELECT * FROM multiout_table_type_setof('list', 'f', 2); - n | column2 ----+--------- - 0 | (4,6) - 1 | (4,6) -(2 rows) - -SELECT * FROM multiout_table_type_setof('list', 'f', 3); - n | column2 ----+--------- - 0 | (6,9) - 1 | (6,9) - 2 | (6,9) -(3 rows) - -SELECT * FROM multiout_table_type_setof('obj', 'f', 4); - n | column2 ----+--------- - 0 | (8,12) - 1 | (8,12) - 2 | (8,12) - 3 | (8,12) -(4 rows) - -SELECT * FROM multiout_table_type_setof('obj', 'f', 5); - n | column2 ----+--------- - 0 | (10,15) - 1 | (10,15) - 2 | (10,15) - 3 | (10,15) - 4 | (10,15) -(5 rows) - -SELECT * FROM multiout_table_type_setof('str', 'f', 6); - n | column2 ----+--------- - 0 | (12,18) - 1 | (12,18) - 2 | (12,18) - 3 | (12,18) - 4 | (12,18) - 5 | (12,18) -(6 rows) - -SELECT * FROM multiout_table_type_setof('str', 'f', 7); - n | column2 ----+--------- - 0 | (14,21) - 1 | (14,21) - 2 | (14,21) - 3 | (14,21) - 4 | (14,21) - 5 | (14,21) - 6 | (14,21) -(7 rows) - -SELECT * FROM multiout_table_type_setof('dict', 't', 3); - n | column2 ----+--------- - 0 | - 1 | - 2 | -(3 rows) - --- check what happens if a type changes under us -CREATE TABLE changing ( - i integer, - j integer -); -CREATE FUNCTION changing_test(OUT n integer, OUT changing) RETURNS SETOF record AS $$ -return [(1, {'i': 1, 'j': 2}), - (1, (3, 4))] -$$ LANGUAGE plpython3u; -SELECT * FROM changing_test(); - n | column2 ----+--------- - 1 | (1,2) - 1 | (3,4) -(2 rows) - -ALTER TABLE changing DROP COLUMN j; -SELECT * FROM changing_test(); -ERROR: length of returned sequence did not match number of columns in row -CONTEXT: while creating return value -PL/Python function "changing_test" -SELECT * FROM changing_test(); -ERROR: length of returned sequence did not match number of columns in row -CONTEXT: while creating return value -PL/Python function "changing_test" -ALTER TABLE changing ADD COLUMN j integer; -SELECT * FROM changing_test(); - n | column2 ----+--------- - 1 | (1,2) - 1 | (3,4) -(2 rows) - --- tables of composite types -CREATE FUNCTION composite_types_table(OUT tab table_record[], OUT typ type_record[] ) RETURNS SETOF record AS $$ -yield {'tab': [('first', 1), ('second', 2)], - 'typ': [{'first': 'third', 'second': 3}, - {'first': 'fourth', 'second': 4}]} -yield {'tab': [('first', 1), ('second', 2)], - 'typ': [{'first': 'third', 'second': 3}, - {'first': 'fourth', 'second': 4}]} -yield {'tab': [('first', 1), ('second', 2)], - 'typ': [{'first': 'third', 'second': 3}, - {'first': 'fourth', 'second': 4}]} -$$ LANGUAGE plpython3u; -SELECT * FROM composite_types_table(); - tab | typ -----------------------------+---------------------------- - {"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"} - {"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"} - {"(first,1)","(second,2)"} | {"(third,3)","(fourth,4)"} -(3 rows) - --- check what happens if the output record descriptor changes -CREATE FUNCTION return_record(t text) RETURNS record AS $$ -return {'t': t, 'val': 10} -$$ LANGUAGE plpython3u; -SELECT * FROM return_record('abc') AS r(t text, val integer); - t | val ------+----- - abc | 10 -(1 row) - -SELECT * FROM return_record('abc') AS r(t text, val bigint); - t | val ------+----- - abc | 10 -(1 row) - -SELECT * FROM return_record('abc') AS r(t text, val integer); - t | val ------+----- - abc | 10 -(1 row) - -SELECT * FROM return_record('abc') AS r(t varchar(30), val integer); - t | val ------+----- - abc | 10 -(1 row) - -SELECT * FROM return_record('abc') AS r(t varchar(100), val integer); - t | val ------+----- - abc | 10 -(1 row) - -SELECT * FROM return_record('999') AS r(val text, t integer); - val | t ------+----- - 10 | 999 -(1 row) - -CREATE FUNCTION return_record_2(t text) RETURNS record AS $$ -return {'v1':1,'v2':2,t:3} -$$ LANGUAGE plpython3u; -SELECT * FROM return_record_2('v3') AS (v3 int, v2 int, v1 int); - v3 | v2 | v1 -----+----+---- - 3 | 2 | 1 -(1 row) - -SELECT * FROM return_record_2('v3') AS (v2 int, v3 int, v1 int); - v2 | v3 | v1 -----+----+---- - 2 | 3 | 1 -(1 row) - -SELECT * FROM return_record_2('v4') AS (v1 int, v4 int, v2 int); - v1 | v4 | v2 -----+----+---- - 1 | 3 | 2 -(1 row) - -SELECT * FROM return_record_2('v4') AS (v1 int, v4 int, v2 int); - v1 | v4 | v2 -----+----+---- - 1 | 3 | 2 -(1 row) - --- error -SELECT * FROM return_record_2('v4') AS (v1 int, v3 int, v2 int); -ERROR: key "v3" not found in mapping -HINT: To return null in a column, add the value None to the mapping with the key named after the column. -CONTEXT: while creating return value -PL/Python function "return_record_2" --- works -SELECT * FROM return_record_2('v3') AS (v1 int, v3 int, v2 int); - v1 | v3 | v2 -----+----+---- - 1 | 3 | 2 -(1 row) - -SELECT * FROM return_record_2('v3') AS (v1 int, v2 int, v3 int); - v1 | v2 | v3 -----+----+---- - 1 | 2 | 3 -(1 row) - --- multi-dimensional array of composite types. -CREATE FUNCTION composite_type_as_list() RETURNS type_record[] AS $$ - return [[('first', 1), ('second', 1)], [('first', 2), ('second', 2)], [('first', 3), ('second', 3)]]; -$$ LANGUAGE plpython3u; -SELECT * FROM composite_type_as_list(); - composite_type_as_list ------------------------------------------------------------------------------------- - {{"(first,1)","(second,1)"},{"(first,2)","(second,2)"},{"(first,3)","(second,3)"}} -(1 row) - --- Starting with PostgreSQL 10, a composite type in an array cannot be --- represented as a Python list, because it's ambiguous with multi-dimensional --- arrays. So this throws an error now. The error should contain a useful hint --- on the issue. -CREATE FUNCTION composite_type_as_list_broken() RETURNS type_record[] AS $$ - return [['first', 1]]; -$$ LANGUAGE plpython3u; -SELECT * FROM composite_type_as_list_broken(); -ERROR: malformed record literal: "first" -DETAIL: Missing left parenthesis. -HINT: To return a composite type in an array, return the composite type as a Python tuple, e.g., "[('foo',)]". -CONTEXT: while creating return value -PL/Python function "composite_type_as_list_broken" +psql: error: connection to server on socket "/tmp/pg_regress-U7uKiV/.s.PGSQL.40002" failed: FATAL: the database system is in recovery mode diff -U3 /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_subtransaction.out /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_subtransaction.out --- /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_subtransaction.out 2024-03-29 10:06:51.843270926 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_subtransaction.out 2024-03-29 10:13:34.263272483 +0000 @@ -1,401 +1 @@ --- --- Test explicit subtransactions --- --- Test table to see if transactions get properly rolled back -CREATE TABLE subtransaction_tbl ( - i integer -); -CREATE FUNCTION subtransaction_ctx_test(what_error text = NULL) RETURNS text -AS $$ -with plpy.subtransaction(): - plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") - plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)") - if what_error == "SPI": - plpy.execute("INSERT INTO subtransaction_tbl VALUES ('oops')") - elif what_error == "Python": - raise Exception("Python exception") -$$ LANGUAGE plpython3u; -SELECT subtransaction_ctx_test(); - subtransaction_ctx_test -------------------------- - -(1 row) - -SELECT * FROM subtransaction_tbl; - i ---- - 1 - 2 -(2 rows) - -TRUNCATE subtransaction_tbl; -SELECT subtransaction_ctx_test('SPI'); -ERROR: spiexceptions.InvalidTextRepresentation: invalid input syntax for type integer: "oops" -LINE 1: INSERT INTO subtransaction_tbl VALUES ('oops') - ^ -QUERY: INSERT INTO subtransaction_tbl VALUES ('oops') -CONTEXT: Traceback (most recent call last): - PL/Python function "subtransaction_ctx_test", line 6, in - plpy.execute("INSERT INTO subtransaction_tbl VALUES ('oops')") -PL/Python function "subtransaction_ctx_test" -SELECT * FROM subtransaction_tbl; - i ---- -(0 rows) - -TRUNCATE subtransaction_tbl; -SELECT subtransaction_ctx_test('Python'); -ERROR: Exception: Python exception -CONTEXT: Traceback (most recent call last): - PL/Python function "subtransaction_ctx_test", line 8, in - raise Exception("Python exception") -PL/Python function "subtransaction_ctx_test" -SELECT * FROM subtransaction_tbl; - i ---- -(0 rows) - -TRUNCATE subtransaction_tbl; --- Nested subtransactions -CREATE FUNCTION subtransaction_nested_test(swallow boolean = 'f') RETURNS text -AS $$ -plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") -with plpy.subtransaction(): - plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)") - try: - with plpy.subtransaction(): - plpy.execute("INSERT INTO subtransaction_tbl VALUES (3)") - plpy.execute("error") - except plpy.SPIError as e: - if not swallow: - raise - plpy.notice("Swallowed %s(%r)" % (e.__class__.__name__, e.args[0])) -return "ok" -$$ LANGUAGE plpython3u; -SELECT subtransaction_nested_test(); -ERROR: spiexceptions.SyntaxError: syntax error at or near "error" -LINE 1: error - ^ -QUERY: error -CONTEXT: Traceback (most recent call last): - PL/Python function "subtransaction_nested_test", line 8, in - plpy.execute("error") -PL/Python function "subtransaction_nested_test" -SELECT * FROM subtransaction_tbl; - i ---- -(0 rows) - -TRUNCATE subtransaction_tbl; -SELECT subtransaction_nested_test('t'); -NOTICE: Swallowed SyntaxError('syntax error at or near "error"') - subtransaction_nested_test ----------------------------- - ok -(1 row) - -SELECT * FROM subtransaction_tbl; - i ---- - 1 - 2 -(2 rows) - -TRUNCATE subtransaction_tbl; --- Nested subtransactions that recursively call code dealing with --- subtransactions -CREATE FUNCTION subtransaction_deeply_nested_test() RETURNS text -AS $$ -plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") -with plpy.subtransaction(): - plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)") - plpy.execute("SELECT subtransaction_nested_test('t')") -return "ok" -$$ LANGUAGE plpython3u; -SELECT subtransaction_deeply_nested_test(); -NOTICE: Swallowed SyntaxError('syntax error at or near "error"') - subtransaction_deeply_nested_test ------------------------------------ - ok -(1 row) - -SELECT * FROM subtransaction_tbl; - i ---- - 1 - 2 - 1 - 2 -(4 rows) - -TRUNCATE subtransaction_tbl; --- Error conditions from not opening/closing subtransactions -CREATE FUNCTION subtransaction_exit_without_enter() RETURNS void -AS $$ -plpy.subtransaction().__exit__(None, None, None) -$$ LANGUAGE plpython3u; -CREATE FUNCTION subtransaction_enter_without_exit() RETURNS void -AS $$ -plpy.subtransaction().__enter__() -$$ LANGUAGE plpython3u; -CREATE FUNCTION subtransaction_exit_twice() RETURNS void -AS $$ -plpy.subtransaction().__enter__() -plpy.subtransaction().__exit__(None, None, None) -plpy.subtransaction().__exit__(None, None, None) -$$ LANGUAGE plpython3u; -CREATE FUNCTION subtransaction_enter_twice() RETURNS void -AS $$ -plpy.subtransaction().__enter__() -plpy.subtransaction().__enter__() -$$ LANGUAGE plpython3u; -CREATE FUNCTION subtransaction_exit_same_subtransaction_twice() RETURNS void -AS $$ -s = plpy.subtransaction() -s.__enter__() -s.__exit__(None, None, None) -s.__exit__(None, None, None) -$$ LANGUAGE plpython3u; -CREATE FUNCTION subtransaction_enter_same_subtransaction_twice() RETURNS void -AS $$ -s = plpy.subtransaction() -s.__enter__() -s.__enter__() -s.__exit__(None, None, None) -$$ LANGUAGE plpython3u; --- No warnings here, as the subtransaction gets indeed closed -CREATE FUNCTION subtransaction_enter_subtransaction_in_with() RETURNS void -AS $$ -with plpy.subtransaction() as s: - s.__enter__() -$$ LANGUAGE plpython3u; -CREATE FUNCTION subtransaction_exit_subtransaction_in_with() RETURNS void -AS $$ -try: - with plpy.subtransaction() as s: - s.__exit__(None, None, None) -except ValueError as e: - raise ValueError(e) -$$ LANGUAGE plpython3u; -SELECT subtransaction_exit_without_enter(); -ERROR: ValueError: this subtransaction has not been entered -CONTEXT: Traceback (most recent call last): - PL/Python function "subtransaction_exit_without_enter", line 2, in - plpy.subtransaction().__exit__(None, None, None) -PL/Python function "subtransaction_exit_without_enter" -SELECT subtransaction_enter_without_exit(); -WARNING: forcibly aborting a subtransaction that has not been exited - subtransaction_enter_without_exit ------------------------------------ - -(1 row) - -SELECT subtransaction_exit_twice(); -WARNING: forcibly aborting a subtransaction that has not been exited -ERROR: ValueError: this subtransaction has not been entered -CONTEXT: Traceback (most recent call last): - PL/Python function "subtransaction_exit_twice", line 3, in - plpy.subtransaction().__exit__(None, None, None) -PL/Python function "subtransaction_exit_twice" -SELECT subtransaction_enter_twice(); -WARNING: forcibly aborting a subtransaction that has not been exited -WARNING: forcibly aborting a subtransaction that has not been exited - subtransaction_enter_twice ----------------------------- - -(1 row) - -SELECT subtransaction_exit_same_subtransaction_twice(); -ERROR: ValueError: this subtransaction has already been exited -CONTEXT: Traceback (most recent call last): - PL/Python function "subtransaction_exit_same_subtransaction_twice", line 5, in - s.__exit__(None, None, None) -PL/Python function "subtransaction_exit_same_subtransaction_twice" -SELECT subtransaction_enter_same_subtransaction_twice(); -WARNING: forcibly aborting a subtransaction that has not been exited -ERROR: ValueError: this subtransaction has already been entered -CONTEXT: Traceback (most recent call last): - PL/Python function "subtransaction_enter_same_subtransaction_twice", line 4, in - s.__enter__() -PL/Python function "subtransaction_enter_same_subtransaction_twice" -SELECT subtransaction_enter_subtransaction_in_with(); -ERROR: ValueError: this subtransaction has already been entered -CONTEXT: Traceback (most recent call last): - PL/Python function "subtransaction_enter_subtransaction_in_with", line 3, in - s.__enter__() -PL/Python function "subtransaction_enter_subtransaction_in_with" -SELECT subtransaction_exit_subtransaction_in_with(); -ERROR: ValueError: this subtransaction has already been exited -CONTEXT: Traceback (most recent call last): - PL/Python function "subtransaction_exit_subtransaction_in_with", line 6, in - raise ValueError(e) -PL/Python function "subtransaction_exit_subtransaction_in_with" --- Make sure we don't get a "current transaction is aborted" error -SELECT 1 as test; - test ------- - 1 -(1 row) - --- Mix explicit subtransactions and normal SPI calls -CREATE FUNCTION subtransaction_mix_explicit_and_implicit() RETURNS void -AS $$ -p = plpy.prepare("INSERT INTO subtransaction_tbl VALUES ($1)", ["integer"]) -try: - with plpy.subtransaction(): - plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") - plpy.execute(p, [2]) - plpy.execute(p, ["wrong"]) -except plpy.SPIError: - plpy.warning("Caught a SPI error from an explicit subtransaction") - -try: - plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") - plpy.execute(p, [2]) - plpy.execute(p, ["wrong"]) -except plpy.SPIError: - plpy.warning("Caught a SPI error") -$$ LANGUAGE plpython3u; -SELECT subtransaction_mix_explicit_and_implicit(); -WARNING: Caught a SPI error from an explicit subtransaction -WARNING: Caught a SPI error - subtransaction_mix_explicit_and_implicit ------------------------------------------- - -(1 row) - -SELECT * FROM subtransaction_tbl; - i ---- - 1 - 2 -(2 rows) - -TRUNCATE subtransaction_tbl; --- Alternative method names for Python <2.6 -CREATE FUNCTION subtransaction_alternative_names() RETURNS void -AS $$ -s = plpy.subtransaction() -s.enter() -s.exit(None, None, None) -$$ LANGUAGE plpython3u; -SELECT subtransaction_alternative_names(); - subtransaction_alternative_names ----------------------------------- - -(1 row) - --- try/catch inside a subtransaction block -CREATE FUNCTION try_catch_inside_subtransaction() RETURNS void -AS $$ -with plpy.subtransaction(): - plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") - try: - plpy.execute("INSERT INTO subtransaction_tbl VALUES ('a')") - except plpy.SPIError: - plpy.notice("caught") -$$ LANGUAGE plpython3u; -SELECT try_catch_inside_subtransaction(); -NOTICE: caught - try_catch_inside_subtransaction ---------------------------------- - -(1 row) - -SELECT * FROM subtransaction_tbl; - i ---- - 1 -(1 row) - -TRUNCATE subtransaction_tbl; -ALTER TABLE subtransaction_tbl ADD PRIMARY KEY (i); -CREATE FUNCTION pk_violation_inside_subtransaction() RETURNS void -AS $$ -with plpy.subtransaction(): - plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") - try: - plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") - except plpy.SPIError: - plpy.notice("caught") -$$ LANGUAGE plpython3u; -SELECT pk_violation_inside_subtransaction(); -NOTICE: caught - pk_violation_inside_subtransaction ------------------------------------- - -(1 row) - -SELECT * FROM subtransaction_tbl; - i ---- - 1 -(1 row) - -DROP TABLE subtransaction_tbl; --- cursor/subtransactions interactions -CREATE FUNCTION cursor_in_subxact() RETURNS int AS $$ -with plpy.subtransaction(): - cur = plpy.cursor("select * from generate_series(1, 20) as gen(i)") - cur.fetch(10) -fetched = cur.fetch(10); -return int(fetched[5]["i"]) -$$ LANGUAGE plpython3u; -CREATE FUNCTION cursor_aborted_subxact() RETURNS int AS $$ -try: - with plpy.subtransaction(): - cur = plpy.cursor("select * from generate_series(1, 20) as gen(i)") - cur.fetch(10); - plpy.execute("select no_such_function()") -except plpy.SPIError: - fetched = cur.fetch(10) - return int(fetched[5]["i"]) -return 0 # not reached -$$ LANGUAGE plpython3u; -CREATE FUNCTION cursor_plan_aborted_subxact() RETURNS int AS $$ -try: - with plpy.subtransaction(): - plpy.execute('create temporary table tmp(i) ' - 'as select generate_series(1, 10)') - plan = plpy.prepare("select i from tmp") - cur = plpy.cursor(plan) - plpy.execute("select no_such_function()") -except plpy.SPIError: - fetched = cur.fetch(5) - return fetched[2]["i"] -return 0 # not reached -$$ LANGUAGE plpython3u; -CREATE FUNCTION cursor_close_aborted_subxact() RETURNS boolean AS $$ -try: - with plpy.subtransaction(): - cur = plpy.cursor('select 1') - plpy.execute("select no_such_function()") -except plpy.SPIError: - cur.close() - return True -return False # not reached -$$ LANGUAGE plpython3u; -SELECT cursor_in_subxact(); - cursor_in_subxact -------------------- - 16 -(1 row) - -SELECT cursor_aborted_subxact(); -ERROR: ValueError: iterating a cursor in an aborted subtransaction -CONTEXT: Traceback (most recent call last): - PL/Python function "cursor_aborted_subxact", line 8, in - fetched = cur.fetch(10) -PL/Python function "cursor_aborted_subxact" -SELECT cursor_plan_aborted_subxact(); -ERROR: ValueError: iterating a cursor in an aborted subtransaction -CONTEXT: Traceback (most recent call last): - PL/Python function "cursor_plan_aborted_subxact", line 10, in - fetched = cur.fetch(5) -PL/Python function "cursor_plan_aborted_subxact" -SELECT cursor_close_aborted_subxact(); -ERROR: ValueError: closing a cursor in an aborted subtransaction -CONTEXT: Traceback (most recent call last): - PL/Python function "cursor_close_aborted_subxact", line 7, in - cur.close() -PL/Python function "cursor_close_aborted_subxact" +psql: error: connection to server on socket "/tmp/pg_regress-U7uKiV/.s.PGSQL.40002" failed: FATAL: the database system is in recovery mode diff -U3 /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_transaction.out /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_transaction.out --- /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_transaction.out 2024-03-29 10:06:51.843270926 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_transaction.out 2024-03-29 10:13:34.283272480 +0000 @@ -1,250 +1 @@ -CREATE TABLE test1 (a int, b text); -CREATE PROCEDURE transaction_test1() -LANGUAGE plpython3u -AS $$ -for i in range(0, 10): - plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i) - if i % 2 == 0: - plpy.commit() - else: - plpy.rollback() -$$; -CALL transaction_test1(); -SELECT * FROM test1; - a | b ----+--- - 0 | - 2 | - 4 | - 6 | - 8 | -(5 rows) - -TRUNCATE test1; -DO -LANGUAGE plpython3u -$$ -for i in range(0, 10): - plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i) - if i % 2 == 0: - plpy.commit() - else: - plpy.rollback() -$$; -SELECT * FROM test1; - a | b ----+--- - 0 | - 2 | - 4 | - 6 | - 8 | -(5 rows) - -TRUNCATE test1; --- not allowed in a function -CREATE FUNCTION transaction_test2() RETURNS int -LANGUAGE plpython3u -AS $$ -for i in range(0, 10): - plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i) - if i % 2 == 0: - plpy.commit() - else: - plpy.rollback() -return 1 -$$; -SELECT transaction_test2(); -ERROR: spiexceptions.InvalidTransactionTermination: invalid transaction termination -CONTEXT: Traceback (most recent call last): - PL/Python function "transaction_test2", line 5, in - plpy.commit() -PL/Python function "transaction_test2" -SELECT * FROM test1; - a | b ----+--- -(0 rows) - --- also not allowed if procedure is called from a function -CREATE FUNCTION transaction_test3() RETURNS int -LANGUAGE plpython3u -AS $$ -plpy.execute("CALL transaction_test1()") -return 1 -$$; -SELECT transaction_test3(); -ERROR: spiexceptions.InvalidTransactionTermination: spiexceptions.InvalidTransactionTermination: invalid transaction termination -CONTEXT: Traceback (most recent call last): - PL/Python function "transaction_test3", line 2, in - plpy.execute("CALL transaction_test1()") -PL/Python function "transaction_test3" -SELECT * FROM test1; - a | b ----+--- -(0 rows) - --- DO block inside function -CREATE FUNCTION transaction_test4() RETURNS int -LANGUAGE plpython3u -AS $$ -plpy.execute("DO LANGUAGE plpython3u $x$ plpy.commit() $x$") -return 1 -$$; -SELECT transaction_test4(); -ERROR: spiexceptions.InvalidTransactionTermination: spiexceptions.InvalidTransactionTermination: invalid transaction termination -CONTEXT: Traceback (most recent call last): - PL/Python function "transaction_test4", line 2, in - plpy.execute("DO LANGUAGE plpython3u $x$ plpy.commit() $x$") -PL/Python function "transaction_test4" --- commit inside subtransaction (prohibited) -DO LANGUAGE plpython3u $$ -s = plpy.subtransaction() -s.enter() -plpy.commit() -$$; -WARNING: forcibly aborting a subtransaction that has not been exited -ERROR: spiexceptions.InvalidTransactionTermination: cannot commit while a subtransaction is active -CONTEXT: Traceback (most recent call last): - PL/Python anonymous code block, line 4, in - plpy.commit() -PL/Python anonymous code block --- commit inside cursor loop -CREATE TABLE test2 (x int); -INSERT INTO test2 VALUES (0), (1), (2), (3), (4); -TRUNCATE test1; -DO LANGUAGE plpython3u $$ -for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): - plpy.execute("INSERT INTO test1 (a) VALUES (%s)" % row['x']) - plpy.commit() -$$; -SELECT * FROM test1; - a | b ----+--- - 0 | - 1 | - 2 | - 3 | - 4 | -(5 rows) - --- check that this doesn't leak a holdable portal -SELECT * FROM pg_cursors; - name | statement | is_holdable | is_binary | is_scrollable | creation_time -------+-----------+-------------+-----------+---------------+--------------- -(0 rows) - --- error in cursor loop with commit -TRUNCATE test1; -DO LANGUAGE plpython3u $$ -for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): - plpy.execute("INSERT INTO test1 (a) VALUES (12/(%s-2))" % row['x']) - plpy.commit() -$$; -ERROR: spiexceptions.DivisionByZero: division by zero -CONTEXT: Traceback (most recent call last): - PL/Python anonymous code block, line 3, in - plpy.execute("INSERT INTO test1 (a) VALUES (12/(%s-2))" % row['x']) -PL/Python anonymous code block -SELECT * FROM test1; - a | b ------+--- - -6 | - -12 | -(2 rows) - -SELECT * FROM pg_cursors; - name | statement | is_holdable | is_binary | is_scrollable | creation_time -------+-----------+-------------+-----------+---------------+--------------- -(0 rows) - --- rollback inside cursor loop -TRUNCATE test1; -DO LANGUAGE plpython3u $$ -for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): - plpy.execute("INSERT INTO test1 (a) VALUES (%s)" % row['x']) - plpy.rollback() -$$; -SELECT * FROM test1; - a | b ----+--- -(0 rows) - -SELECT * FROM pg_cursors; - name | statement | is_holdable | is_binary | is_scrollable | creation_time -------+-----------+-------------+-----------+---------------+--------------- -(0 rows) - --- first commit then rollback inside cursor loop -TRUNCATE test1; -DO LANGUAGE plpython3u $$ -for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): - plpy.execute("INSERT INTO test1 (a) VALUES (%s)" % row['x']) - if row['x'] % 2 == 0: - plpy.commit() - else: - plpy.rollback() -$$; -SELECT * FROM test1; - a | b ----+--- - 0 | - 2 | - 4 | -(3 rows) - -SELECT * FROM pg_cursors; - name | statement | is_holdable | is_binary | is_scrollable | creation_time -------+-----------+-------------+-----------+---------------+--------------- -(0 rows) - --- check handling of an error during COMMIT -CREATE TABLE testpk (id int PRIMARY KEY); -CREATE TABLE testfk(f1 int REFERENCES testpk DEFERRABLE INITIALLY DEFERRED); -DO LANGUAGE plpython3u $$ -# this insert will fail during commit: -plpy.execute("INSERT INTO testfk VALUES (0)") -plpy.commit() -plpy.warning('should not get here') -$$; -ERROR: spiexceptions.ForeignKeyViolation: insert or update on table "testfk" violates foreign key constraint "testfk_f1_fkey" -DETAIL: Key (f1)=(0) is not present in table "testpk". -CONTEXT: Traceback (most recent call last): - PL/Python anonymous code block, line 4, in - plpy.commit() -PL/Python anonymous code block -SELECT * FROM testpk; - id ----- -(0 rows) - -SELECT * FROM testfk; - f1 ----- -(0 rows) - -DO LANGUAGE plpython3u $$ -# this insert will fail during commit: -plpy.execute("INSERT INTO testfk VALUES (0)") -try: - plpy.commit() -except Exception as e: - plpy.info('sqlstate: %s' % (e.sqlstate)) -# these inserts should work: -plpy.execute("INSERT INTO testpk VALUES (1)") -plpy.execute("INSERT INTO testfk VALUES (1)") -$$; -INFO: sqlstate: 23503 -SELECT * FROM testpk; - id ----- - 1 -(1 row) - -SELECT * FROM testfk; - f1 ----- - 1 -(1 row) - -DROP TABLE test1; -DROP TABLE test2; +psql: error: connection to server on socket "/tmp/pg_regress-U7uKiV/.s.PGSQL.40002" failed: FATAL: the database system is in recovery mode diff -U3 /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_drop.out /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_drop.out --- /tmp/cirrus-ci-build/src/pl/plpython/expected/plpython_drop.out 2024-03-29 10:06:51.843270926 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/plpython/regress/results/plpython_drop.out 2024-03-29 10:13:34.291272478 +0000 @@ -1,5 +1 @@ --- --- For paranoia's sake, don't leave an untrusted language sitting around --- -SET client_min_messages = WARNING; -DROP EXTENSION plpython3u CASCADE; +psql: error: connection to server on socket "/tmp/pg_regress-U7uKiV/.s.PGSQL.40002" failed: FATAL: the database system is in recovery mode