From b4b8efbd3826ac0af2d1c2e7c40fcf80a4bfba45 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:18:03 +0200 Subject: Adding upstream version 15.6. Signed-off-by: Daniel Baumann --- src/test/regress/sql/fast_default.sql | 11 ++++++ src/test/regress/sql/generated.sql | 3 ++ src/test/regress/sql/horology.sql | 2 ++ src/test/regress/sql/indexing.sql | 20 +++++++++++ src/test/regress/sql/inherit.sql | 9 +++++ src/test/regress/sql/int8.sql | 1 + src/test/regress/sql/interval.sql | 8 +++++ src/test/regress/sql/join.sql | 12 +++++++ src/test/regress/sql/matview.sql | 17 +++++++++ src/test/regress/sql/merge.sql | 64 +++++++++++++++++++++++++++++++++ src/test/regress/sql/opr_sanity.sql | 2 +- src/test/regress/sql/partition_join.sql | 48 +++++++++++++++++++++++++ src/test/regress/sql/triggers.sql | 36 +++++++++++++++++++ src/test/regress/sql/tsdicts.sql | 30 ++++++++++++++++ 14 files changed, 262 insertions(+), 1 deletion(-) (limited to 'src/test/regress/sql') diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql index 16a3b7c..dc9df78 100644 --- a/src/test/regress/sql/fast_default.sql +++ b/src/test/regress/sql/fast_default.sql @@ -256,7 +256,18 @@ ALTER TABLE T ADD COLUMN c2 TIMESTAMP DEFAULT clock_timestamp(); SELECT comp(); +-- check that we notice insertion of a volatile default argument +CREATE FUNCTION foolme(timestamptz DEFAULT clock_timestamp()) + RETURNS timestamptz + IMMUTABLE AS 'select $1' LANGUAGE sql; +ALTER TABLE T ADD COLUMN c3 timestamptz DEFAULT foolme(); + +SELECT attname, atthasmissing, attmissingval FROM pg_attribute + WHERE attrelid = 't'::regclass AND attnum > 0 + ORDER BY attnum; + DROP TABLE T; +DROP FUNCTION foolme(timestamptz); -- Simple querie CREATE TABLE T (pk INT NOT NULL PRIMARY KEY); diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sql index 8d25161..53bccf9 100644 --- a/src/test/regress/sql/generated.sql +++ b/src/test/regress/sql/generated.sql @@ -26,6 +26,9 @@ CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) S -- generation expression must be immutable CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) STORED); +-- ... but be sure that the immutability test is accurate +CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') STORED); +DROP TABLE gtest2; -- cannot have default/identity and generated CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) STORED); diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql index 2724a2b..4065348 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -86,6 +86,7 @@ SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506"; SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244"; SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276"; +SELECT timestamp without time zone '2000-01-01' - interval '2483590 days' AS "out of range"; SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days"; -- Shorthand values @@ -117,6 +118,7 @@ SELECT timestamp with time zone '1996-03-01' - interval '1 second' AS "Feb 29"; SELECT timestamp with time zone '1999-03-01' - interval '1 second' AS "Feb 28"; SELECT timestamp with time zone '2000-03-01' - interval '1 second' AS "Feb 29"; SELECT timestamp with time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31"; +SELECT timestamp with time zone '2000-01-01' - interval '2483590 days' AS "out of range"; SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True"; SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow' - interval '1 day')) as "True"; diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql index 6f60d1d..a48a317 100644 --- a/src/test/regress/sql/indexing.sql +++ b/src/test/regress/sql/indexing.sql @@ -668,6 +668,26 @@ insert into idxpart values (857142, 'six'); select tableoid::regclass, * from idxpart order by a; drop table idxpart; +-- Test some other non-btree index types +create table idxpart (a int, b text, c int[]) partition by range (a); +create table idxpart1 partition of idxpart for values from (0) to (100000); +set enable_seqscan to off; + +create index idxpart_brin on idxpart using brin(b); +explain (costs off) select * from idxpart where b = 'abcd'; +drop index idxpart_brin; + +create index idxpart_spgist on idxpart using spgist(b); +explain (costs off) select * from idxpart where b = 'abcd'; +drop index idxpart_spgist; + +create index idxpart_gin on idxpart using gin(c); +explain (costs off) select * from idxpart where c @> array[42]; +drop index idxpart_gin; + +reset enable_seqscan; +drop table idxpart; + -- intentionally leave some objects around create table idxpart (a int) partition by range (a); create table idxpart1 partition of idxpart for values from (0) to (100); diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 76ea197..bd90834 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -372,6 +372,15 @@ ALTER TABLE inhts RENAME d TO dd; DROP TABLE inhts; +-- Test for adding a column to a parent table with complex inheritance +CREATE TABLE inhta (); +CREATE TABLE inhtb () INHERITS (inhta); +CREATE TABLE inhtc () INHERITS (inhtb); +CREATE TABLE inhtd () INHERITS (inhta, inhtb, inhtc); +ALTER TABLE inhta ADD COLUMN i int; +\d+ inhta +DROP TABLE inhta, inhtb, inhtc, inhtd; + -- Test for renaming in diamond inheritance CREATE TABLE inht2 (x int) INHERITS (inht1); CREATE TABLE inht3 (y int) INHERITS (inht1); diff --git a/src/test/regress/sql/int8.sql b/src/test/regress/sql/int8.sql index 38b7719..8a3d537 100644 --- a/src/test/regress/sql/int8.sql +++ b/src/test/regress/sql/int8.sql @@ -126,6 +126,7 @@ select '9223372036854775808'::int8; select -('-9223372036854775807'::int8); select -('-9223372036854775808'::int8); +select 0::int8 - '-9223372036854775808'::int8; select '9223372036854775800'::int8 + '9223372036854775800'::int8; select '-9223372036854775800'::int8 + '-9223372036854775800'::int8; diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql index 5b4944c..2a3dd0c 100644 --- a/src/test/regress/sql/interval.sql +++ b/src/test/regress/sql/interval.sql @@ -129,6 +129,14 @@ SET IntervalStyle to postgres_verbose; SELECT * FROM INTERVAL_TBL; +-- multiplication and division overflow test cases +SELECT '3000000 months'::interval * 1000; +SELECT '3000000 months'::interval / 0.001; +SELECT '3000000 days'::interval * 1000; +SELECT '3000000 days'::interval / 0.001; +SELECT '1 month 2146410 days'::interval * 1000.5002; +SELECT '4611686018427387904 usec'::interval / 0.1; + -- test avg(interval), which is somewhat fragile since people have been -- known to change the allowed input syntax for type interval without -- updating pg_aggregate.agginitval diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 5fdacce..1113e98 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1978,6 +1978,18 @@ select * from ) on c.q2 = ss2.q1, lateral (select ss2.y offset 0) ss3; +-- another case requiring nested PlaceHolderVars +explain (verbose, costs off) +select * from + (select 0 as val0) as ss0 + left join (select 1 as val) as ss1 on true + left join lateral (select ss1.val as val_filtered where false) as ss2 on true; + +select * from + (select 0 as val0) as ss0 + left join (select 1 as val) as ss1 on true + left join lateral (select ss1.val as val_filtered where false) as ss2 on true; + -- case that breaks the old ph_may_need optimization explain (verbose, costs off) select c.*,a.*,ss1.q1,ss2.q1,ss3.* from diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 68b9ccf..543e0a6 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -231,6 +231,23 @@ REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_foo; DROP OWNED BY regress_user_mvtest CASCADE; DROP ROLE regress_user_mvtest; +-- Concurrent refresh requires a unique index on the materialized +-- view. Test what happens if it's dropped during the refresh. +CREATE OR REPLACE FUNCTION mvtest_drop_the_index() + RETURNS bool AS $$ +BEGIN + EXECUTE 'DROP INDEX IF EXISTS mvtest_drop_idx'; + RETURN true; +END; +$$ LANGUAGE plpgsql; + +CREATE MATERIALIZED VIEW drop_idx_matview AS + SELECT 1 as i WHERE mvtest_drop_the_index(); + +CREATE UNIQUE INDEX mvtest_drop_idx ON drop_idx_matview (i); +REFRESH MATERIALIZED VIEW CONCURRENTLY drop_idx_matview; +DROP MATERIALIZED VIEW drop_idx_matview; -- clean up + -- make sure that create WITH NO DATA works via SPI BEGIN; CREATE FUNCTION mvtest_func() diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 4cf6db9..66cb75a 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -1060,6 +1060,10 @@ INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2 -- try simple MERGE BEGIN; +DO $$ +DECLARE + result integer; +BEGIN MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid @@ -1067,6 +1071,10 @@ MERGE INTO pa_target t UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN INSERT VALUES (sid, delta, 'inserted by merge'); +GET DIAGNOSTICS result := ROW_COUNT; +RAISE NOTICE 'ROW_COUNT = %', result; +END; +$$; SELECT * FROM pa_target ORDER BY tid; ROLLBACK; @@ -1085,6 +1093,34 @@ ROLLBACK; -- try updating the partition key column BEGIN; +DO $$ +DECLARE + result integer; +BEGIN +MERGE INTO pa_target t + USING pa_source s + ON t.tid = s.sid + WHEN MATCHED THEN + UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' + WHEN NOT MATCHED THEN + INSERT VALUES (sid, delta, 'inserted by merge'); +GET DIAGNOSTICS result := ROW_COUNT; +RAISE NOTICE 'ROW_COUNT = %', result; +END; +$$; +SELECT * FROM pa_target ORDER BY tid; +ROLLBACK; + +-- as above, but blocked by BEFORE DELETE ROW trigger +BEGIN; +CREATE FUNCTION trig_fn() RETURNS trigger LANGUAGE plpgsql AS + $$ BEGIN RETURN NULL; END; $$; +CREATE TRIGGER del_trig BEFORE DELETE ON pa_target + FOR EACH ROW EXECUTE PROCEDURE trig_fn(); +DO $$ +DECLARE + result integer; +BEGIN MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid @@ -1092,6 +1128,34 @@ MERGE INTO pa_target t UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN INSERT VALUES (sid, delta, 'inserted by merge'); +GET DIAGNOSTICS result := ROW_COUNT; +RAISE NOTICE 'ROW_COUNT = %', result; +END; +$$; +SELECT * FROM pa_target ORDER BY tid; +ROLLBACK; + +-- as above, but blocked by BEFORE INSERT ROW trigger +BEGIN; +CREATE FUNCTION trig_fn() RETURNS trigger LANGUAGE plpgsql AS + $$ BEGIN RETURN NULL; END; $$; +CREATE TRIGGER ins_trig BEFORE INSERT ON pa_target + FOR EACH ROW EXECUTE PROCEDURE trig_fn(); +DO $$ +DECLARE + result integer; +BEGIN +MERGE INTO pa_target t + USING pa_source s + ON t.tid = s.sid + WHEN MATCHED THEN + UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' + WHEN NOT MATCHED THEN + INSERT VALUES (sid, delta, 'inserted by merge'); +GET DIAGNOSTICS result := ROW_COUNT; +RAISE NOTICE 'ROW_COUNT = %', result; +END; +$$; SELECT * FROM pa_target ORDER BY tid; ROLLBACK; diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index 2b29285..0f039f2 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -1195,7 +1195,7 @@ WHERE p1.oid = a1.amhandler AND a1.amtype = 'i' AND SELECT a1.oid, a1.amname, p1.oid, p1.proname FROM pg_am AS a1, pg_proc AS p1 -WHERE p1.oid = a1.amhandler AND a1.amtype = 's' AND +WHERE p1.oid = a1.amhandler AND a1.amtype = 't' AND (p1.prorettype != 'table_am_handler'::regtype OR p1.proretset OR p1.pronargs != 1 diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index 67f5063..04d7adb 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -91,6 +91,33 @@ SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL (SELECT t2.a AS t2a, t3.a AS t3a, t2.b t2b, t2.c t2c, least(t1.a,t2.a,t3.a) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a; +SET max_parallel_workers_per_gather = 0; +-- If there are lateral references to the other relation in sample scan, +-- we cannot generate a partitionwise join. +EXPLAIN (COSTS OFF) +SELECT * FROM prt1 t1 JOIN LATERAL + (SELECT * FROM prt1 t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s + ON t1.a = s.a; + +-- If there are lateral references to the other relation in scan's restriction +-- clauses, we cannot generate a partitionwise join. +EXPLAIN (COSTS OFF) +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.a; +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.a; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.b; +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.b; +RESET max_parallel_workers_per_gather; + -- bug with inadequate sort key representation SET enable_partitionwise_aggregate TO true; SET enable_hashjoin TO false; @@ -378,6 +405,27 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL (SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND t2.c = t3.c)) ss ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a; +SET max_parallel_workers_per_gather = 0; +-- If there are lateral references to the other relation in sample scan, +-- we cannot generate a partitionwise join. +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_l t1 JOIN LATERAL + (SELECT * FROM prt1_l t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s + ON t1.a = s.a AND t1.b = s.b AND t1.c = s.c; + +-- If there are lateral references to the other relation in scan's restriction +-- clauses, we cannot generate a partitionwise join. +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2_l t2) s + ON t1.a = s.b AND t1.b = s.a AND t1.c = s.c + WHERE s.t1b = s.a; +SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2_l t2) s + ON t1.a = s.b AND t1.b = s.a AND t1.c = s.c + WHERE s.t1b = s.a; +RESET max_parallel_workers_per_gather; + -- join with one side empty EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c; diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index d29e98d..46795a9 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1583,6 +1583,42 @@ create trigger qqq after insert on parted_trig_1_1 for each row execute procedur insert into parted_trig values (50), (1500); drop table parted_trig; +-- Verify that the correct triggers fire for cross-partition updates +create table parted_trig (a int) partition by list (a); +create table parted_trig1 partition of parted_trig for values in (1); +create table parted_trig2 partition of parted_trig for values in (2); +insert into parted_trig values (1); + +create or replace function trigger_notice() returns trigger as $$ + begin + raise notice 'trigger % on % % % for %', TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL; + if TG_LEVEL = 'ROW' then + if TG_OP = 'DELETE' then + return OLD; + else + return NEW; + end if; + end if; + return null; + end; + $$ language plpgsql; +create trigger parted_trig_before_stmt before insert or update or delete on parted_trig + for each statement execute procedure trigger_notice(); +create trigger parted_trig_before_row before insert or update or delete on parted_trig + for each row execute procedure trigger_notice(); +create trigger parted_trig_after_row after insert or update or delete on parted_trig + for each row execute procedure trigger_notice(); +create trigger parted_trig_after_stmt after insert or update or delete on parted_trig + for each statement execute procedure trigger_notice(); + +update parted_trig set a = 2 where a = 1; + +-- update action in merge should behave the same +merge into parted_trig using (select 1) as ss on true + when matched and a = 2 then update set a = 1; + +drop table parted_trig; + -- Verify propagation of trigger arguments to partitions create table parted_trig (a int) partition by list (a); create table parted_trig1 partition of parted_trig for values in (1); diff --git a/src/test/regress/sql/tsdicts.sql b/src/test/regress/sql/tsdicts.sql index ddc6c7f..6a2b003 100644 --- a/src/test/regress/sql/tsdicts.sql +++ b/src/test/regress/sql/tsdicts.sql @@ -251,3 +251,33 @@ CREATE TEXT SEARCH DICTIONARY tsdict_case "DictFile" = ispell_sample, "AffFile" = ispell_sample ); + +-- Test grammar for configurations +CREATE TEXT SEARCH CONFIGURATION dummy_tst (COPY=english); +-- Overriden mapping change with duplicated tokens. +ALTER TEXT SEARCH CONFIGURATION dummy_tst + ALTER MAPPING FOR word, word WITH ispell; +-- Not a token supported by the configuration's parser, fails. +ALTER TEXT SEARCH CONFIGURATION dummy_tst + DROP MAPPING FOR not_a_token, not_a_token; +-- Not a token supported by the configuration's parser, fails even +-- with IF EXISTS. +ALTER TEXT SEARCH CONFIGURATION dummy_tst + DROP MAPPING IF EXISTS FOR not_a_token, not_a_token; +-- Token supported by the configuration's parser, succeeds. +ALTER TEXT SEARCH CONFIGURATION dummy_tst + DROP MAPPING FOR word, word; +-- No mapping for token supported by the configuration's parser, fails. +ALTER TEXT SEARCH CONFIGURATION dummy_tst + DROP MAPPING FOR word; +-- Token supported by the configuration's parser, cannot be found, +-- succeeds with IF EXISTS. +ALTER TEXT SEARCH CONFIGURATION dummy_tst + DROP MAPPING IF EXISTS FOR word, word; +-- Re-add mapping, with duplicated tokens supported by the parser. +ALTER TEXT SEARCH CONFIGURATION dummy_tst + ADD MAPPING FOR word, word WITH ispell; +-- Not a token supported by the configuration's parser, fails. +ALTER TEXT SEARCH CONFIGURATION dummy_tst + ADD MAPPING FOR not_a_token WITH ispell; +DROP TEXT SEARCH CONFIGURATION dummy_tst; -- cgit v1.2.3