-- Regression tests for bugs found by the 2026-11-19 audit (fix_core). -- Each case names its audit finding (A). SET client_min_messages = warning; CREATE EXTENSION IF NOT EXISTS plx; CREATE SCHEMA fc; SET search_path = fc, public; -- The CREATE-time errors below come from plpgsql's compiler, whose context -- lines differ between PostgreSQL majors, so show only the message. \set VERBOSITY terse -- A0: CREATE FUNCTION runs plpgsql's signature checks and compiles the body -- a pseudo-type result is rejected CREATE FUNCTION a0_cstring(x int) RETURNS cstring LANGUAGE plxruby AS $$ return nil $$; ERROR: plxruby functions cannot return type cstring -- a pseudo-type argument is rejected CREATE FUNCTION a0_internal(x internal) RETURNS int LANGUAGE plxjs AS $$ return 1; $$; ERROR: plxjs functions cannot accept type internal -- a body that plpgsql cannot parse is rejected CREATE FUNCTION a0_syntax() RETURNS int LANGUAGE plxruby AS $$ return 1 2 $$; ERROR: syntax error at or near "2" at character 46 -- a trigger function cannot declare arguments CREATE FUNCTION a0_trg_args(a int) RETURNS trigger LANGUAGE plxpython3 AS $$ return None $$; ERROR: trigger functions cannot have declared arguments -- none of the rejected functions was stored SELECT count(*) FROM pg_proc WHERE proname LIKE 'a0\_%'; count ------- 0 (1 row) -- the pseudo-types plpgsql allows are still accepted CREATE FUNCTION a0_trg() RETURNS trigger LANGUAGE plxruby AS $$ return nil $$; CREATE FUNCTION a0_poly(x anyelement) RETURNS anyelement LANGUAGE plxruby AS $$ return x $$; CREATE FUNCTION a0_void() RETURNS void LANGUAGE plxphp AS $$ return; $$; SELECT a0_poly(41 + 1), a0_void(); a0_poly | a0_void ---------+--------- 42 | (1 row) -- with check_function_bodies off (as pg_restore runs), the body is not -- compiled, so a bad body and trigger arguments pass as they do in plpgsql; -- the signature checks still run SET check_function_bodies = off; CREATE FUNCTION a0_syntax() RETURNS int LANGUAGE plxruby AS $$ return 1 2 $$; CREATE FUNCTION a0_trg_args(a int) RETURNS trigger LANGUAGE plxpython3 AS $$ return None $$; CREATE FUNCTION a0_cstring(x int) RETURNS cstring LANGUAGE plxruby AS $$ return nil $$; ERROR: plxruby functions cannot return type cstring RESET check_function_bodies; -- fc_err(q) runs q and returns 'SQLSTATE: message' if it fails. The message -- leaves out the error position, which differs between PostgreSQL 13 -- (plpgsql prefixes each expression with SELECT) and later versions. CREATE FUNCTION fc_err(q text) RETURNS text LANGUAGE plpgsql AS $h$ BEGIN EXECUTE q; RETURN 'no error'; EXCEPTION WHEN OTHERS THEN RETURN SQLSTATE || ': ' || SQLERRM; END $h$; SELECT fc_err($q$SELECT a0_syntax()$q$); fc_err ------------------------------------ 42601: syntax error at or near "2" (1 row) \set VERBOSITY default -- A4: plx_strbuild has binary I/O and a collation, like text CREATE TABLE a4_sb(v plx_strbuild); INSERT INTO a4_sb VALUES ('b'), ('a'), ('c'), ('a'); SELECT DISTINCT v FROM a4_sb ORDER BY v; v --- a b c (3 rows) SELECT v, count(*) FROM a4_sb GROUP BY v ORDER BY v; v | count ---+------- a | 2 b | 1 c | 1 (3 rows) SELECT 'B'::plx_strbuild < 'a'::plx_strbuild COLLATE "C" AS c_order; c_order --------- t (1 row) -- binary COPY writes and reads the type SELECT '/tmp/plx_fix_core_a4_' || pg_backend_pid() || '.bin' AS a4_file \gset COPY a4_sb TO :'a4_file' (FORMAT binary); CREATE TABLE a4_sb2(v plx_strbuild); COPY a4_sb2 FROM :'a4_file' (FORMAT binary); SELECT v FROM a4_sb2 ORDER BY v; v --- a a b c (4 rows) -- A3: appending a NULL makes the string NULL, as || does (except plxcobol) CREATE FUNCTION a3_rb(x text) RETURNS text LANGUAGE plxruby AS $$ s = "a" #:: text s << x s << "b" return s $$; CREATE FUNCTION a3_rb_nullacc(x text) RETURNS text LANGUAGE plxruby AS $$ s = x #:: text s << "b" return s $$; CREATE FUNCTION a3_rb_coalesce(x text) RETURNS text LANGUAGE plxruby AS $$ s = "a" #:: text s << coalesce(x, '') s << "b" return s $$; SELECT a3_rb(NULL) IS NULL AS rb_null, a3_rb('X') AS rb, a3_rb_nullacc(NULL) IS NULL AS rb_nullacc_null, a3_rb_nullacc('X') AS rb_nullacc, a3_rb_coalesce(NULL) AS rb_coalesce; rb_null | rb | rb_nullacc_null | rb_nullacc | rb_coalesce ---------+-----+-----------------+------------+------------- t | aXb | t | Xb | ab (1 row) CREATE FUNCTION a3_php(x text) RETURNS text LANGUAGE plxphp AS $$ $s = "a" /*:: text */; $s .= $x; $s .= "b"; return $s; $$; -- with interpolation, the whole string becomes NULL, not just one segment CREATE FUNCTION a3_php_seg(x text) RETURNS text LANGUAGE plxphp AS $$ $s = "" /*:: text */; $s .= "1:{$x} "; $s .= "2:b "; return $s; $$; -- a variable whose first use is .= starts as an empty string, as in PHP CREATE FUNCTION a3_php_undecl(x text) RETURNS text LANGUAGE plxphp AS $$ $s .= "a"; $s .= $x; return $s; $$; CREATE FUNCTION a3_php_loop(n int) RETURNS text LANGUAGE plxphp AS $$ for ($i = 0; $i < $n; $i++) { $s .= "x"; } return $s; $$; SELECT a3_php(NULL) IS NULL AS php_null, a3_php('X') AS php, a3_php_seg(NULL) IS NULL AS seg_null, a3_php_seg('a') AS seg, a3_php_undecl(NULL) IS NULL AS undecl_null, a3_php_undecl('X') AS undecl, a3_php_loop(3) AS php_loop; php_null | php | seg_null | seg | undecl_null | undecl | php_loop ----------+-----+----------+----------+-------------+--------+---------- t | aXb | t | 1:a 2:b | t | aX | xxx (1 row) CREATE FUNCTION a3_js(x text) RETURNS text LANGUAGE plxjs AS $$ let s = "a" /*:: text */; s += x; s += "b"; return s; $$; CREATE FUNCTION a3_ts(x text) RETURNS text LANGUAGE plxts AS $$ let s: string = "a"; s += x; s += "b"; return s; $$; CREATE FUNCTION a3_py(x text) RETURNS text LANGUAGE plxpython3 AS $$ s = "a" #:: text s += x s += "b" return s $$; CREATE FUNCTION a3_py_nullacc(x text) RETURNS text LANGUAGE plxpython3 AS $$ s = x #:: text s += "b" return s $$; SELECT a3_js(NULL) IS NULL AS js_null, a3_js('X') AS js, a3_ts(NULL) IS NULL AS ts_null, a3_ts('X') AS ts, a3_py(NULL) IS NULL AS py_null, a3_py('X') AS py, a3_py_nullacc(NULL) IS NULL AS py_nullacc_null, a3_py_nullacc('X') AS py_nullacc; js_null | js | ts_null | ts | py_null | py | py_nullacc_null | py_nullacc ---------+-----+---------+-----+---------+-----+-----------------+------------ t | aXb | t | aXb | t | aXb | t | Xb (1 row) -- plxcobol STRING-APPEND still treats a NULL as nothing to append CREATE FUNCTION a3_cob(x text) RETURNS text LANGUAGE plxcobol AS $$ WORKING-STORAGE SECTION. 01 WS-S PIC X(1) VALUE "a". PROCEDURE DIVISION. STRING-APPEND X TO WS-S STRING-APPEND "b" TO WS-S GOBACK RETURNING WS-S. $$; SELECT a3_cob(NULL) AS cob_null, a3_cob('X') AS cob; cob_null | cob ----------+----- ab | aXb (1 row) -- the generated code calls the strict append, except in plxcobol SELECT proname, prosrc LIKE '%plx_sb_append_strict(%' AS strict_append, prosrc LIKE '%plx_sb_append(%' AS plain_append, prosrc LIKE '%plx_strbuild := '''';%' AS starts_empty FROM pg_proc WHERE proname IN ('a3_rb', 'a3_cob', 'a3_php_undecl') ORDER BY proname; proname | strict_append | plain_append | starts_empty ---------------+---------------+--------------+-------------- a3_cob | f | t | f a3_php_undecl | t | f | t a3_rb | t | f | f (3 rows) -- the strict append itself SELECT plx_sb_append_strict('a'::text::plx_strbuild, NULL)::text IS NULL AS null_suffix, plx_sb_append_strict(NULL, 'b')::text IS NULL AS null_builder, plx_sb_append_strict('a'::text::plx_strbuild, 'b')::text AS both; null_suffix | null_builder | both -------------+--------------+------ t | t | ab (1 row) -- a message built with appends and then raised shows if a part was NULL CREATE FUNCTION a3_rb_raise(x text) RETURNS text LANGUAGE plxruby AS $$ s = "bad: " #:: text s << x raise s $$; \set VERBOSITY terse SELECT a3_rb_raise(NULL); ERROR: SELECT a3_rb_raise('X'); ERROR: bad: X \set VERBOSITY default -- A4: ALTER EXTENSION plx UPDATE from 2.0.0 gives the same objects as -- CREATE EXTENSION, and a plx_strbuild column made under 2.0.0 gets the -- collation. This drops and re-creates the extension, so it stays last. CREATE TEMP VIEW plx_objects AS SELECT pg_describe_object(d.classid, d.objid, 0) AS object, CASE d.classid WHEN 'pg_proc'::regclass THEN (SELECT row(pg_get_function_result(p.oid), p.prosrc, p.probin, p.prokind, p.provolatile, p.proisstrict, p.prosupport::regproc, p.proparallel, p.proacl)::text FROM pg_proc p WHERE p.oid = d.objid) WHEN 'pg_type'::regclass THEN (SELECT row(t.typlen, t.typbyval, t.typtype, t.typcategory, t.typinput::regproc, t.typoutput::regproc, t.typreceive::regproc, t.typsend::regproc, t.typcollation, t.typstorage, t.typalign, a.typcollation, a.typreceive::regproc, a.typsend::regproc)::text FROM pg_type t LEFT JOIN pg_type a ON a.oid = t.typarray WHERE t.oid = d.objid) WHEN 'pg_language'::regclass THEN (SELECT row(l.lanpltrusted, l.lanplcallfoid::regproc, l.laninline::regproc, l.lanvalidator::regproc, l.lanacl)::text FROM pg_language l WHERE l.oid = d.objid) WHEN 'pg_cast'::regclass THEN (SELECT row(c.castfunc, c.castcontext, c.castmethod)::text FROM pg_cast c WHERE c.oid = d.objid) END AS detail, obj_description(d.objid, d.classid::regclass::name) AS comment FROM pg_depend d WHERE d.refclassid = 'pg_extension'::regclass AND d.deptype = 'e' AND d.refobjid = (SELECT oid FROM pg_extension WHERE extname = 'plx'); CREATE TEMP TABLE plx_fresh AS SELECT * FROM plx_objects; SELECT count(*) > 30 AS has_objects FROM plx_fresh; has_objects ------------- t (1 row) -- A4: nested domains, arrays of domains, range types and composite fields -- over them get the same collations from the update as from a fresh -- install. a4s_make builds the same objects under either version. CREATE PROCEDURE a4s_make() LANGUAGE plpgsql AS $p$ BEGIN CREATE DOMAIN a4s_d1 AS plx_strbuild; CREATE DOMAIN a4s_d2 AS a4s_d1; CREATE DOMAIN a4s_d3 AS a4s_d2; CREATE DOMAIN a4s_da AS plx_strbuild[]; CREATE DOMAIN a4s_dd1a AS a4s_d1[]; CREATE TYPE a4s_r2 AS RANGE (subtype = a4s_d2); CREATE TYPE a4s_comp AS (f_d2 a4s_d2, f_d1arr a4s_d1[], f_d3arr a4s_d3[], f_da a4s_da, f_sbarr plx_strbuild[]); CREATE TABLE a4s_t (c_d2 a4s_d2, c_d3 a4s_d3, c_d1arr a4s_d1[], c_d2arr a4s_d2[], c_dd1a a4s_dd1a, c_comp a4s_comp, c_r2 a4s_r2); INSERT INTO a4s_t (c_d2, c_d3, c_d1arr, c_dd1a) VALUES ('b', 'b', '{b}', '{b}'), ('a', 'a', '{a}', '{a}'); -- Indexes and partition keys. Their tables stay empty here, because -- 2.0.0 cannot hash or compare these values without a collation. CREATE TABLE a4s_h (h plx_strbuild, hd a4s_d2, x int); CREATE INDEX a4s_h_h ON a4s_h USING hash (h); CREATE INDEX a4s_h_hd ON a4s_h USING hash (hd); CREATE INDEX a4s_h_e ON a4s_h USING hash ((hd::plx_strbuild)); CREATE INDEX a4s_h_p ON a4s_h (x, h text_pattern_ops) INCLUDE (hd); CREATE TABLE a4s_ph (k a4s_d2) PARTITION BY HASH (k); CREATE TABLE a4s_ph0 PARTITION OF a4s_ph FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE a4s_ph1 PARTITION OF a4s_ph FOR VALUES WITH (MODULUS 2, REMAINDER 1); CREATE TABLE a4s_pl (n int, k plx_strbuild) PARTITION BY LIST (k); CREATE TABLE a4s_pr (n int, k a4s_d3) PARTITION BY RANGE (n, k); CREATE TABLE a4s_pe (n int, k a4s_d2) PARTITION BY RANGE ((n + 1), (k::plx_strbuild)); CREATE TABLE a4s_pf (n int) PARTITION BY HASH ((n::text::plx_strbuild)); CREATE TABLE a4s_pf0 PARTITION OF a4s_pf FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE a4s_pf1 PARTITION OF a4s_pf FOR VALUES WITH (MODULUS 2, REMAINDER 1); END $p$; CREATE TEMP VIEW a4_scolls AS SELECT 'type ' || t.typname AS object, t.typcollation AS coll FROM pg_type t WHERE t.typname LIKE 'a4s\_%' OR t.typname LIKE '\_a4s\_%' UNION ALL SELECT 'column ' || c.relname || '.' || a.attname, a.attcollation FROM pg_attribute a JOIN pg_class c ON c.oid = a.attrelid WHERE c.relname LIKE 'a4s\_%' AND a.attnum > 0 AND NOT a.attisdropped UNION ALL SELECT 'range ' || t.typname, r.rngcollation FROM pg_range r JOIN pg_type t ON t.oid = r.rngtypid WHERE t.typname LIKE 'a4s\_%' UNION ALL SELECT 'index ' || c.relname || ' key ' || k, i.indcollation[k - 1] FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid, generate_series(1, i.indnkeyatts) k WHERE c.relname LIKE 'a4s\_%' UNION ALL SELECT 'partkey ' || c.relname || ' key ' || k, p.partcollation[k - 1] FROM pg_partitioned_table p JOIN pg_class c ON c.oid = p.partrelid, generate_series(1, p.partnatts) k WHERE c.relname LIKE 'a4s\_%'; CALL a4s_make(); CREATE TEMP TABLE a4_sfresh AS SELECT * FROM a4_scolls; -- the objects that have a collation on a fresh install SELECT object, coll FROM a4_sfresh WHERE coll <> 0 ORDER BY object COLLATE "C"; object | coll -------------------------+------ column a4s_comp.f_d1arr | 100 column a4s_comp.f_d2 | 100 column a4s_comp.f_d3arr | 100 column a4s_comp.f_da | 100 column a4s_comp.f_sbarr | 100 column a4s_h.h | 100 column a4s_h.hd | 100 column a4s_h_e.hd | 100 column a4s_h_h.h | 100 column a4s_h_hd.hd | 100 column a4s_h_p.h | 100 column a4s_pe.k | 100 column a4s_ph.k | 100 column a4s_ph0.k | 100 column a4s_ph1.k | 100 column a4s_pl.k | 100 column a4s_pr.k | 100 column a4s_t.c_d1arr | 100 column a4s_t.c_d2 | 100 column a4s_t.c_d2arr | 100 column a4s_t.c_d3 | 100 column a4s_t.c_dd1a | 100 index a4s_h_e key 1 | 100 index a4s_h_h key 1 | 100 index a4s_h_hd key 1 | 100 index a4s_h_p key 2 | 100 partkey a4s_pe key 2 | 100 partkey a4s_pf key 1 | 100 partkey a4s_ph key 1 | 100 partkey a4s_pl key 1 | 100 partkey a4s_pr key 2 | 100 range a4s_r2 | 100 type _a4s_d1 | 100 type _a4s_d2 | 100 type _a4s_d3 | 100 type _a4s_da | 100 type _a4s_dd1a | 100 type a4s_d1 | 100 type a4s_d2 | 100 type a4s_d3 | 100 type a4s_da | 100 type a4s_dd1a | 100 (42 rows) -- DROP EXTENSION ... CASCADE would keep the table and the composite type -- and drop only their columns, so drop them first. DROP TABLE a4s_t, a4s_h, a4s_ph, a4s_pl, a4s_pr, a4s_pe, a4s_pf; DROP TYPE a4s_comp; DROP EXTENSION plx CASCADE; CREATE EXTENSION plx VERSION '2.0.0'; CALL a4s_make(); CREATE TABLE a4_old(v plx_strbuild); INSERT INTO a4_old VALUES ('b'), ('a'), ('b'); -- A4: so do a domain column and a composite-type field made under 2.0.0 CREATE DOMAIN a4_odom AS plx_strbuild; CREATE TABLE a4_old_dom(v a4_odom); INSERT INTO a4_old_dom VALUES ('b'), ('a'), ('b'); CREATE TYPE a4_comp AS (v plx_strbuild); CREATE TABLE a4_old_comp(c a4_comp); INSERT INTO a4_old_comp VALUES (ROW('b')), (ROW('a')), (ROW('b')); -- A4: range types over plx_strbuild made under 2.0.0 get the collation too CREATE TYPE a4_rng AS RANGE (subtype = plx_strbuild); CREATE DOMAIN a4_rdom AS plx_strbuild; CREATE TYPE a4_drng AS RANGE (subtype = a4_rdom); ALTER EXTENSION plx UPDATE TO '3.0.0'; -- objects only a fresh install has, then objects only the update has SELECT * FROM plx_fresh EXCEPT SELECT * FROM plx_objects; object | detail | comment --------+--------+--------- (0 rows) SELECT * FROM plx_objects EXCEPT SELECT * FROM plx_fresh; object | detail | comment --------+--------+--------- (0 rows) SELECT DISTINCT v FROM a4_old ORDER BY v; v --- a b (2 rows) SELECT DISTINCT v FROM a4_old_dom ORDER BY v; v --- a b (2 rows) SELECT DISTINCT (c).v FROM a4_old_comp ORDER BY 1; v --- a b (2 rows) SELECT rngtypid::regtype, rngcollation FROM pg_range WHERE rngtypid IN ('a4_rng'::regtype, 'a4_drng'::regtype) ORDER BY rngtypid::regtype::text; rngtypid | rngcollation ----------+-------------- a4_drng | 100 a4_rng | 100 (2 rows) SELECT a4_rng('a', 'c') @> 'b'::plx_strbuild AS rng_has, a4_drng('a', 'c') @> 'b'::a4_rdom AS drng_has; rng_has | drng_has ---------+---------- t | t (1 row) -- collations only a fresh install has, then collations only the update has SELECT * FROM a4_sfresh EXCEPT SELECT * FROM a4_scolls; object | coll --------+------ (0 rows) SELECT * FROM a4_scolls EXCEPT SELECT * FROM a4_sfresh; object | coll --------+------ (0 rows) SELECT c_d2 FROM a4s_t ORDER BY c_d2; c_d2 ------ a b (2 rows) SELECT c_d3 FROM a4s_t ORDER BY c_d3; c_d3 ------ a b (2 rows) SELECT c_d1arr FROM a4s_t ORDER BY c_d1arr; c_d1arr --------- {a} {b} (2 rows) SELECT c_dd1a FROM a4s_t ORDER BY c_dd1a; c_dd1a -------- {a} {b} (2 rows) -- A4: hash indexes and partition keys made under 2.0.0 work after the -- update: values can be hashed for the index and the hash partitions, and -- list and range partitions can compare their bounds. INSERT INTO a4s_h VALUES ('a', 'a', 1); INSERT INTO a4s_ph VALUES ('a'), ('b'); CREATE TABLE a4s_pl1 PARTITION OF a4s_pl FOR VALUES IN ('a', 'b'); CREATE TABLE a4s_pr1 PARTITION OF a4s_pr FOR VALUES FROM (1, 'a') TO (1, 'm'); INSERT INTO a4s_pl VALUES (1, 'a'); INSERT INTO a4s_pr VALUES (1, 'b'); CREATE TABLE a4s_pe1 PARTITION OF a4s_pe FOR VALUES FROM (2, 'a') TO (2, 'm'); INSERT INTO a4s_pe VALUES (1, 'b'); INSERT INTO a4s_pf VALUES (1), (2); SELECT (SELECT count(*) FROM a4s_h) AS h, (SELECT count(*) FROM a4s_ph) AS ph, (SELECT count(*) FROM a4s_pl1) AS pl1, (SELECT count(*) FROM a4s_pr1) AS pr1, (SELECT count(*) FROM a4s_pe1) AS pe1, (SELECT count(*) FROM a4s_pf) AS pf; h | ph | pl1 | pr1 | pe1 | pf ---+----+-----+-----+-----+---- 1 | 2 | 1 | 1 | 1 | 2 (1 row) -- The A4 test re-created the extension with search_path = fc, so it now -- lives in schema fc. Put it back in public, where the test files that run -- after this one expect it. DROP EXTENSION plx CASCADE; RESET search_path; CREATE EXTENSION plx SCHEMA public;