diff options
Diffstat (limited to '')
48 files changed, 1116 insertions, 164 deletions
diff --git a/src/test/isolation/expected/merge-update.out b/src/test/isolation/expected/merge-update.out index 55b1f90..f5f7e3b 100644 --- a/src/test/isolation/expected/merge-update.out +++ b/src/test/isolation/expected/merge-update.out @@ -48,6 +48,27 @@ key|val step c2: COMMIT; +starting permutation: pa_merge1 c1 pa_merge2c_dup a2 +step pa_merge1: + MERGE INTO pa_target t + USING (SELECT 1 as key, 'pa_merge1' as val) s + ON s.key = t.key + WHEN NOT MATCHED THEN + INSERT VALUES (s.key, s.val) + WHEN MATCHED THEN + UPDATE set val = t.val || ' updated by ' || s.val; + +step c1: COMMIT; +step pa_merge2c_dup: + MERGE INTO pa_target t + USING (VALUES (1), (1)) v(a) + ON t.key = v.a + WHEN MATCHED THEN + UPDATE set val = t.val || ' updated by pa_merge2c_dup'; -- should fail + +ERROR: MERGE command cannot affect row a second time +step a2: ABORT; + starting permutation: merge1 merge2a c1 select2 c2 step merge1: MERGE INTO target t @@ -312,3 +333,25 @@ key|val (2 rows) step c2: COMMIT; + +starting permutation: pa_merge1 pa_merge2c_dup c1 a2 +step pa_merge1: + MERGE INTO pa_target t + USING (SELECT 1 as key, 'pa_merge1' as val) s + ON s.key = t.key + WHEN NOT MATCHED THEN + INSERT VALUES (s.key, s.val) + WHEN MATCHED THEN + UPDATE set val = t.val || ' updated by ' || s.val; + +step pa_merge2c_dup: + MERGE INTO pa_target t + USING (VALUES (1), (1)) v(a) + ON t.key = v.a + WHEN MATCHED THEN + UPDATE set val = t.val || ' updated by pa_merge2c_dup'; -- should fail + <waiting ...> +step c1: COMMIT; +step pa_merge2c_dup: <... completed> +ERROR: MERGE command cannot affect row a second time +step a2: ABORT; diff --git a/src/test/isolation/specs/merge-update.spec b/src/test/isolation/specs/merge-update.spec index e8d0166..3ccd466 100644 --- a/src/test/isolation/specs/merge-update.spec +++ b/src/test/isolation/specs/merge-update.spec @@ -4,6 +4,7 @@ # 1. UPDATEs of PKs that change the join in the ON clause # 2. UPDATEs with WHEN conditions that would fail after concurrent update # 3. UPDATEs with extra ON conditions that would fail after concurrent update +# 4. UPDATEs with duplicate source rows setup { @@ -134,15 +135,26 @@ step "pa_merge2b_when" WHEN MATCHED AND t.val like 'initial%' THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; } +# Duplicate source row should fail +step "pa_merge2c_dup" +{ + MERGE INTO pa_target t + USING (VALUES (1), (1)) v(a) + ON t.key = v.a + WHEN MATCHED THEN + UPDATE set val = t.val || ' updated by pa_merge2c_dup'; -- should fail +} step "select2" { SELECT * FROM target; } step "pa_select2" { SELECT * FROM pa_target; } step "c2" { COMMIT; } +step "a2" { ABORT; } # Basic effects permutation "merge1" "c1" "select2" "c2" # One after the other, no concurrency permutation "merge1" "c1" "merge2a" "select2" "c2" +permutation "pa_merge1" "c1" "pa_merge2c_dup" "a2" # Now with concurrency permutation "merge1" "merge2a" "c1" "select2" "c2" @@ -154,3 +166,4 @@ permutation "pa_merge2" "pa_merge2a" "c1" "pa_select2" "c2" # fails permutation "pa_merge2" "c1" "pa_merge2a" "pa_select2" "c2" # succeeds permutation "pa_merge3" "pa_merge2b_when" "c1" "pa_select2" "c2" # WHEN not satisfied by updated tuple permutation "pa_merge1" "pa_merge2b_when" "c1" "pa_select2" "c2" # WHEN satisfied by updated tuple +permutation "pa_merge1" "pa_merge2c_dup" "c1" "a2" diff --git a/src/test/kerberos/t/001_auth.pl b/src/test/kerberos/t/001_auth.pl index 0deb9bf..d74e4af 100644 --- a/src/test/kerberos/t/001_auth.pl +++ b/src/test/kerberos/t/001_auth.pl @@ -203,7 +203,12 @@ system_or_bail $krb5kdc, '-P', $kdc_pidfile; END { - kill 'INT', `cat $kdc_pidfile` if -f $kdc_pidfile; + # take care not to change the script's exit value + my $exit_code = $?; + + kill 'INT', `cat $kdc_pidfile` if defined($kdc_pidfile) && -f $kdc_pidfile; + + $? = $exit_code; } note "setting up PostgreSQL instance"; diff --git a/src/test/ldap/LdapServer.pm b/src/test/ldap/LdapServer.pm index a4c1a18..2ff580e 100644 --- a/src/test/ldap/LdapServer.pm +++ b/src/test/ldap/LdapServer.pm @@ -57,55 +57,97 @@ use File::Basename; # private variables my ($slapd, $ldap_schema_dir, @servers); -# visible variable -our ($setup); +# visible variables +our ($setup, $setup_error); INIT { + # Find the OpenLDAP server binary and directory containing schema + # definition files. On success, $setup is set to 1. On failure, + # it's set to 0, and an error message is set in $setup_error. $setup = 1; - if ($^O eq 'darwin' && -d '/opt/homebrew/opt/openldap') + if ($^O eq 'darwin') { - # typical paths for Homebrew on ARM - $slapd = '/opt/homebrew/opt/openldap/libexec/slapd'; - $ldap_schema_dir = '/opt/homebrew/etc/openldap/schema'; - } - elsif ($^O eq 'darwin' && -d '/usr/local/opt/openldap') - { - # typical paths for Homebrew on Intel - $slapd = '/usr/local/opt/openldap/libexec/slapd'; - $ldap_schema_dir = '/usr/local/etc/openldap/schema'; - } - elsif ($^O eq 'darwin' && -d '/opt/local/etc/openldap') - { - # typical paths for MacPorts - $slapd = '/opt/local/libexec/slapd'; - $ldap_schema_dir = '/opt/local/etc/openldap/schema'; + if (-d '/opt/homebrew/opt/openldap') + { + # typical paths for Homebrew on ARM + $slapd = '/opt/homebrew/opt/openldap/libexec/slapd'; + $ldap_schema_dir = '/opt/homebrew/etc/openldap/schema'; + } + elsif (-d '/usr/local/opt/openldap') + { + # typical paths for Homebrew on Intel + $slapd = '/usr/local/opt/openldap/libexec/slapd'; + $ldap_schema_dir = '/usr/local/etc/openldap/schema'; + } + elsif (-d '/opt/local/etc/openldap') + { + # typical paths for MacPorts + $slapd = '/opt/local/libexec/slapd'; + $ldap_schema_dir = '/opt/local/etc/openldap/schema'; + } + else + { + $setup_error = "OpenLDAP server installation not found"; + $setup = 0; + } } elsif ($^O eq 'linux') { - $slapd = '/usr/sbin/slapd'; - $ldap_schema_dir = '/etc/ldap/schema' if -d '/etc/ldap/schema'; - $ldap_schema_dir = '/etc/openldap/schema' - if -d '/etc/openldap/schema'; + if (-d '/etc/ldap/schema') + { + $slapd = '/usr/sbin/slapd'; + $ldap_schema_dir = '/etc/ldap/schema'; + } + elsif (-d '/etc/openldap/schema') + { + $slapd = '/usr/sbin/slapd'; + $ldap_schema_dir = '/etc/openldap/schema'; + } + else + { + $setup_error = "OpenLDAP server installation not found"; + $setup = 0; + } } elsif ($^O eq 'freebsd') { - $slapd = '/usr/local/libexec/slapd'; - $ldap_schema_dir = '/usr/local/etc/openldap/schema'; + if (-d '/usr/local/etc/openldap/schema') + { + $slapd = '/usr/local/libexec/slapd'; + $ldap_schema_dir = '/usr/local/etc/openldap/schema'; + } + else + { + $setup_error = "OpenLDAP server installation not found"; + $setup = 0; + } } elsif ($^O eq 'openbsd') { - $slapd = '/usr/local/libexec/slapd'; - $ldap_schema_dir = '/usr/local/share/examples/openldap/schema'; + if (-d '/usr/local/share/examples/openldap/schema') + { + $slapd = '/usr/local/libexec/slapd'; + $ldap_schema_dir = '/usr/local/share/examples/openldap/schema'; + } + else + { + $setup_error = "OpenLDAP server installation not found"; + $setup = 0; + } } else { + $setup_error = "ldap tests not supported on $^O"; $setup = 0; } } END { + # take care not to change the script's exit value + my $exit_code = $?; + foreach my $server (@servers) { next unless -f $server->{pidfile}; @@ -113,6 +155,8 @@ END chomp $pid; kill 'INT', $pid; } + + $? = $exit_code; } =pod diff --git a/src/test/ldap/t/001_auth.pl b/src/test/ldap/t/001_auth.pl index 3e113fd..ef8ad17 100644 --- a/src/test/ldap/t/001_auth.pl +++ b/src/test/ldap/t/001_auth.pl @@ -25,8 +25,7 @@ elsif ($ENV{PG_TEST_EXTRA} !~ /\bldap\b/) } elsif (!$LdapServer::setup) { - plan skip_all => - "ldap tests not supported on $^O or dependencies not installed"; + plan skip_all => $LdapServer::setup_error; } note "setting up LDAP server"; diff --git a/src/test/ldap/t/002_bindpasswd.pl b/src/test/ldap/t/002_bindpasswd.pl index bcd4aa2..9242faf 100644 --- a/src/test/ldap/t/002_bindpasswd.pl +++ b/src/test/ldap/t/002_bindpasswd.pl @@ -25,8 +25,7 @@ elsif ($ENV{PG_TEST_EXTRA} !~ /\bldap\b/) } elsif (!$LdapServer::setup) { - plan skip_all => - "ldap tests not supported on $^O or dependencies not installed"; + plan skip_all => $LdapServer::setup_error; } note "setting up LDAP server"; diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm index 5f0f04b..7743ffd 100644 --- a/src/test/perl/PostgreSQL/Test/Cluster.pm +++ b/src/test/perl/PostgreSQL/Test/Cluster.pm @@ -3092,6 +3092,36 @@ $SIG{TERM} = $SIG{INT} = sub { =pod +=item $node->log_standby_snapshot(self, standby, slot_name) + +Log a standby snapshot on primary once the slot restart_lsn is determined on +the standby. + +=cut + +sub log_standby_snapshot +{ + my ($self, $standby, $slot_name) = @_; + + # Once the slot's restart_lsn is determined, the standby looks for + # xl_running_xacts WAL record from the restart_lsn onwards. First wait + # until the slot restart_lsn is determined. + + $standby->poll_query_until( + 'postgres', qq[ + SELECT restart_lsn IS NOT NULL + FROM pg_catalog.pg_replication_slots WHERE slot_name = '$slot_name' + ]) + or die + "timed out waiting for logical slot to calculate its restart_lsn"; + + # Then arrange for the xl_running_xacts record for which the standby is + # waiting. + $self->safe_psql('postgres', 'SELECT pg_log_standby_snapshot()'); +} + +=pod + =item $node->create_logical_slot_on_standby(self, primary, slot_name, dbname) Create logical replication slot on given standby @@ -3117,21 +3147,9 @@ sub create_logical_slot_on_standby '2>', \$stderr); - # Once the slot's restart_lsn is determined, the standby looks for - # xl_running_xacts WAL record from the restart_lsn onwards. First wait - # until the slot restart_lsn is determined. - - $self->poll_query_until( - 'postgres', qq[ - SELECT restart_lsn IS NOT NULL - FROM pg_catalog.pg_replication_slots WHERE slot_name = '$slot_name' - ]) - or die - "timed out waiting for logical slot to calculate its restart_lsn"; - - # Then arrange for the xl_running_xacts record for which pg_recvlogical is + # Arrange for the xl_running_xacts record for which pg_recvlogical is # waiting. - $primary->safe_psql('postgres', 'SELECT pg_log_standby_snapshot()'); + $primary->log_standby_snapshot($self, $slot_name); $handle->finish(); diff --git a/src/test/recovery/t/008_fsm_truncation.pl b/src/test/recovery/t/008_fsm_truncation.pl index acac0a0..2c11ecb 100644 --- a/src/test/recovery/t/008_fsm_truncation.pl +++ b/src/test/recovery/t/008_fsm_truncation.pl @@ -1,9 +1,8 @@ # Copyright (c) 2021-2023, PostgreSQL Global Development Group -# Test WAL replay of FSM changes. -# -# FSM changes don't normally need to be WAL-logged, except for truncation. +# Test FSM-driven INSERT just after truncation clears FSM slots indicating +# free space in removed blocks. # The FSM mustn't return a page that doesn't exist (anymore). use strict; use warnings; diff --git a/src/test/recovery/t/035_standby_logical_decoding.pl b/src/test/recovery/t/035_standby_logical_decoding.pl index 831c32d..e90191f 100644 --- a/src/test/recovery/t/035_standby_logical_decoding.pl +++ b/src/test/recovery/t/035_standby_logical_decoding.pl @@ -21,7 +21,6 @@ my $node_cascading_standby = PostgreSQL::Test::Cluster->new('cascading_standby'); my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber'); my $default_timeout = $PostgreSQL::Test::Utils::timeout_default; -my $psql_timeout = IPC::Run::timer($default_timeout); my $res; # Name for the physical slot on primary @@ -90,7 +89,8 @@ sub make_slot_active '>', $to_stdout, '2>', - $to_stderr); + $to_stderr, + IPC::Run::timeout($default_timeout)); if ($wait) { @@ -343,7 +343,7 @@ $psql_subscriber{run} = IPC::Run::start( \$psql_subscriber{subscriber_stdout}, '2>', \$psql_subscriber{subscriber_stderr}, - $psql_timeout); + IPC::Run::timeout($default_timeout)); ################################################## # Test that logical decoding on the standby @@ -467,8 +467,8 @@ $psql_subscriber{subscriber_stdin} .= "\n"; $psql_subscriber{run}->pump_nb(); -# Speed up the subscription creation -$node_primary->safe_psql('postgres', "SELECT pg_log_standby_snapshot()"); +# Log the standby snapshot to speed up the subscription creation +$node_primary->log_standby_snapshot($node_standby, 'tap_sub'); # Explicitly shut down psql instance gracefully - to avoid hangs # or worse on windows diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index f635c5a..68fd716 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -1930,7 +1930,7 @@ select string_agg(v, decode('ee', 'hex')) from bytea_test_table; drop table bytea_test_table; -- Test parallel string_agg and array_agg -create table pagg_test (x int, y int); +create table pagg_test (x int, y int) with (autovacuum_enabled = off); insert into pagg_test select (case x % 4 when 1 then null else x end), x % 10 from generate_series(1,5000) x; diff --git a/src/test/regress/expected/bit.out b/src/test/regress/expected/bit.out index 98c2655..e17cbf4 100644 --- a/src/test/regress/expected/bit.out +++ b/src/test/regress/expected/bit.out @@ -40,6 +40,23 @@ SELECT * FROM VARBIT_TABLE; 01010101010 (4 rows) +-- Literals with syntax errors +SELECT b' 0'; +ERROR: " " is not a valid binary digit +LINE 1: SELECT b' 0'; + ^ +SELECT b'0 '; +ERROR: " " is not a valid binary digit +LINE 1: SELECT b'0 '; + ^ +SELECT x' 0'; +ERROR: " " is not a valid hexadecimal digit +LINE 1: SELECT x' 0'; + ^ +SELECT x'0 '; +ERROR: " " is not a valid hexadecimal digit +LINE 1: SELECT x'0 '; + ^ -- Concatenation SELECT v, b, (v || b) AS concat FROM BIT_TABLE, VARBIT_TABLE diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index f2a677f..6ab09d7 100644 --- a/src/test/regress/expected/create_procedure.out +++ b/src/test/regress/expected/create_procedure.out @@ -148,7 +148,19 @@ CALL ptest4a(a, b); -- error, not supported $$; ERROR: calling procedures with output arguments is not supported in SQL functions CONTEXT: SQL function "ptest4b" -DROP PROCEDURE ptest4a; +-- we used to get confused by a single output argument that is composite +CREATE PROCEDURE ptest4c(INOUT comp int8_tbl) +LANGUAGE SQL +AS $$ +SELECT ROW(1, 2); +$$; +CALL ptest4c(NULL); + comp +------- + (1,2) +(1 row) + +DROP PROCEDURE ptest4a, ptest4c; -- named and default parameters CREATE OR REPLACE PROCEDURE ptest5(a int, b text, c int default 100) LANGUAGE SQL diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 5b30ee4..6ed50fd 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -892,24 +892,29 @@ ERROR: column "no_column" of relation "ft1" does not exist ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; NOTICE: column "no_column" of relation "ft1" does not exist, skipping ALTER FOREIGN TABLE ft1 DROP COLUMN c9; +ALTER FOREIGN TABLE ft1 ADD COLUMN c11 serial; ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR ERROR: relation "ft1" does not exist +ALTER SEQUENCE foreign_schema.ft1_c11_seq SET SCHEMA public; -- ERROR +ERROR: cannot move an owned sequence into another schema +DETAIL: Sequence "ft1_c11_seq" is linked to table "ft1". ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; \d foreign_schema.foreign_table_1 - Foreign table "foreign_schema.foreign_table_1" - Column | Type | Collation | Nullable | Default | FDW options -------------------+---------+-----------+----------+---------+-------------------------------- - foreign_column_1 | integer | | not null | | ("param 1" 'val1') - c2 | text | | | | (param2 'val2', param3 'val3') - c3 | date | | | | - c4 | integer | | | 0 | - c5 | integer | | | | - c6 | integer | | not null | | - c7 | integer | | | | (p1 'v1', p2 'v2') - c8 | text | | | | (p2 'V2') - c10 | integer | | | | (p1 'v1') + Foreign table "foreign_schema.foreign_table_1" + Column | Type | Collation | Nullable | Default | FDW options +------------------+---------+-----------+----------+-------------------------------------------------+-------------------------------- + foreign_column_1 | integer | | not null | | ("param 1" 'val1') + c2 | text | | | | (param2 'val2', param3 'val3') + c3 | date | | | | + c4 | integer | | | 0 | + c5 | integer | | | | + c6 | integer | | not null | | + c7 | integer | | | | (p1 'v1', p2 'v2') + c8 | text | | | | (p2 'V2') + c10 | integer | | | | (p1 'v1') + c11 | integer | | not null | nextval('foreign_schema.ft1_c11_seq'::regclass) | Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out index f3cda4a..e48534b 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -484,6 +484,8 @@ SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' SELECT timestamp without time zone '2000-01-01' - interval '2483590 days' AS "out of range"; ERROR: timestamp out of range +SELECT timestamp without time zone '294276-12-31 23:59:59' + interval '9223372036854775807 microseconds' AS "out of range"; +ERROR: timestamp out of range SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days"; 106751991 Days ------------------ @@ -746,6 +748,8 @@ SELECT timestamp with time zone '1999-12-01' + interval '1 month - 1 second' AS SELECT timestamp with time zone '2000-01-01' - interval '2483590 days' AS "out of range"; ERROR: timestamp out of range +SELECT timestamp with time zone '294276-12-31 23:59:59 UTC' + interval '9223372036854775807 microseconds' AS "out of range"; +ERROR: timestamp out of range SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True"; True ------ diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 5f03d8e..cc77723 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -365,6 +365,78 @@ SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regcl ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error ERROR: identity column type must be smallint, integer, or bigint +-- check that unlogged propagates to sequence +CREATE UNLOGGED TABLE itest17 (a int NOT NULL, b text); +ALTER TABLE itest17 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ALTER TABLE itest17 ADD COLUMN c int GENERATED ALWAYS AS IDENTITY; +\d itest17 + Unlogged table "public.itest17" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------ + a | integer | | not null | generated always as identity + b | text | | | + c | integer | | not null | generated always as identity + +\d itest17_a_seq + Unlogged sequence "public.itest17_a_seq" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +---------+-------+---------+------------+-----------+---------+------- + integer | 1 | 1 | 2147483647 | 1 | no | 1 +Sequence for identity column: public.itest17.a + +\d itest17_c_seq + Unlogged sequence "public.itest17_c_seq" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +---------+-------+---------+------------+-----------+---------+------- + integer | 1 | 1 | 2147483647 | 1 | no | 1 +Sequence for identity column: public.itest17.c + +CREATE TABLE itest18 (a int NOT NULL, b text); +ALTER TABLE itest18 SET UNLOGGED, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +\d itest18 + Unlogged table "public.itest18" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------ + a | integer | | not null | generated always as identity + b | text | | | + +\d itest18_a_seq + Unlogged sequence "public.itest18_a_seq" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +---------+-------+---------+------------+-----------+---------+------- + integer | 1 | 1 | 2147483647 | 1 | no | 1 +Sequence for identity column: public.itest18.a + +ALTER TABLE itest18 SET LOGGED; +\d itest18 + Table "public.itest18" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------ + a | integer | | not null | generated always as identity + b | text | | | + +\d itest18_a_seq + Sequence "public.itest18_a_seq" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +---------+-------+---------+------------+-----------+---------+------- + integer | 1 | 1 | 2147483647 | 1 | no | 1 +Sequence for identity column: public.itest18.a + +ALTER TABLE itest18 SET UNLOGGED; +\d itest18 + Unlogged table "public.itest18" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------ + a | integer | | not null | generated always as identity + b | text | | | + +\d itest18_a_seq + Unlogged sequence "public.itest18_a_seq" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +---------+-------+---------+------------+-----------+---------+------- + integer | 1 | 1 | 2147483647 | 1 | no | 1 +Sequence for identity column: public.itest18.a + -- kinda silly to change property in the same command, but it should work ALTER TABLE itest3 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY, diff --git a/src/test/regress/expected/index_including.out b/src/test/regress/expected/index_including.out index 8651068..ea8b245 100644 --- a/src/test/regress/expected/index_including.out +++ b/src/test/regress/expected/index_including.out @@ -398,3 +398,28 @@ Indexes: "tbl_c1_c2_c3_c4_key" UNIQUE CONSTRAINT, btree (c1, c2) INCLUDE (c3, c4) DROP TABLE tbl; +/* + * 10. Test coverage for names stored as cstrings in indexes + */ +CREATE TABLE nametbl (c1 int, c2 name, c3 float); +CREATE INDEX nametbl_c1_c2_idx ON nametbl (c2, c1) INCLUDE (c3); +INSERT INTO nametbl VALUES(1, 'two', 3.0); +VACUUM nametbl; +SET enable_seqscan = 0; +-- Ensure we get an index only scan plan +EXPLAIN (COSTS OFF) SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1; + QUERY PLAN +---------------------------------------------------- + Index Only Scan using nametbl_c1_c2_idx on nametbl + Index Cond: ((c2 = 'two'::name) AND (c1 = 1)) +(2 rows) + +-- Validate the results look sane +SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1; + c2 | c1 | c3 +-----+----+---- + two | 1 | 3 +(1 row) + +RESET enable_seqscan; +DROP TABLE nametbl; diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index dd4354f..cf4b522 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -180,7 +180,121 @@ Rules: drop table inserttest2; drop table inserttest; -drop type insert_test_type; +-- Make the same tests with domains over the array and composite fields +create domain insert_pos_ints as int[] check (value[1] > 0); +create domain insert_test_domain as insert_test_type + check ((value).if2[1] is not null); +create table inserttesta (f1 int, f2 insert_pos_ints); +create table inserttestb (f3 insert_test_domain, f4 insert_test_domain[]); +insert into inserttesta (f2[1], f2[2]) values (1,2); +insert into inserttesta (f2[1], f2[2]) values (3,4), (5,6); +insert into inserttesta (f2[1], f2[2]) select 7,8; +insert into inserttesta (f2[1], f2[2]) values (1,default); -- not supported +ERROR: cannot set an array element to DEFAULT +LINE 1: insert into inserttesta (f2[1], f2[2]) values (1,default); + ^ +insert into inserttesta (f2[1], f2[2]) values (0,2); +ERROR: value for domain insert_pos_ints violates check constraint "insert_pos_ints_check" +insert into inserttesta (f2[1], f2[2]) values (3,4), (0,6); +ERROR: value for domain insert_pos_ints violates check constraint "insert_pos_ints_check" +insert into inserttesta (f2[1], f2[2]) select 0,8; +ERROR: value for domain insert_pos_ints violates check constraint "insert_pos_ints_check" +insert into inserttestb (f3.if1, f3.if2) values (1,array['foo']); +insert into inserttestb (f3.if1, f3.if2) values (1,'{foo}'), (2,'{bar}'); +insert into inserttestb (f3.if1, f3.if2) select 3, '{baz,quux}'; +insert into inserttestb (f3.if1, f3.if2) values (1,default); -- not supported +ERROR: cannot set a subfield to DEFAULT +LINE 1: insert into inserttestb (f3.if1, f3.if2) values (1,default); + ^ +insert into inserttestb (f3.if1, f3.if2) values (1,array[null]); +ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" +insert into inserttestb (f3.if1, f3.if2) values (1,'{null}'), (2,'{bar}'); +ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" +insert into inserttestb (f3.if1, f3.if2) select 3, '{null,quux}'; +ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" +insert into inserttestb (f3.if2[1], f3.if2[2]) values ('foo', 'bar'); +insert into inserttestb (f3.if2[1], f3.if2[2]) values ('foo', 'bar'), ('baz', 'quux'); +insert into inserttestb (f3.if2[1], f3.if2[2]) select 'bear', 'beer'; +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) values (row(1,'{x}'), 'foo', 'bar'); +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) values (row(1,'{x}'), 'foo', 'bar'), (row(2,'{y}'), 'baz', 'quux'); +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) select row(1,'{x}')::insert_test_domain, 'bear', 'beer'; +select * from inserttesta; + f1 | f2 +----+------- + | {1,2} + | {3,4} + | {5,6} + | {7,8} +(4 rows) + +select * from inserttestb; + f3 | f4 +------------------+------------------------ + (1,{foo}) | + (1,{foo}) | + (2,{bar}) | + (3,"{baz,quux}") | + (,"{foo,bar}") | + (,"{foo,bar}") | + (,"{baz,quux}") | + (,"{bear,beer}") | + (1,{x}) | {"(,\"{foo,bar}\")"} + (1,{x}) | {"(,\"{foo,bar}\")"} + (2,{y}) | {"(,\"{baz,quux}\")"} + (1,{x}) | {"(,\"{bear,beer}\")"} +(12 rows) + +-- also check reverse-listing +create table inserttest2 (f1 bigint, f2 text); +create rule irule1 as on insert to inserttest2 do also + insert into inserttestb (f3.if2[1], f3.if2[2]) + values (new.f1,new.f2); +create rule irule2 as on insert to inserttest2 do also + insert into inserttestb (f4[1].if1, f4[1].if2[2]) + values (1,'fool'),(new.f1,new.f2); +create rule irule3 as on insert to inserttest2 do also + insert into inserttestb (f4[1].if1, f4[1].if2[2]) + select new.f1, new.f2; +\d+ inserttest2 + Table "public.inserttest2" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+--------+-----------+----------+---------+----------+--------------+------------- + f1 | bigint | | | | plain | | + f2 | text | | | | extended | | +Rules: + irule1 AS + ON INSERT TO inserttest2 DO INSERT INTO inserttestb (f3.if2[1], f3.if2[2]) + VALUES (new.f1, new.f2) + irule2 AS + ON INSERT TO inserttest2 DO INSERT INTO inserttestb (f4[1].if1, f4[1].if2[2]) VALUES (1,'fool'::text), (new.f1,new.f2) + irule3 AS + ON INSERT TO inserttest2 DO INSERT INTO inserttestb (f4[1].if1, f4[1].if2[2]) SELECT new.f1, + new.f2 + +drop table inserttest2; +drop table inserttesta; +drop table inserttestb; +drop domain insert_pos_ints; +drop domain insert_test_domain; +-- Verify that multiple inserts to subfields of a domain-over-container +-- check the domain constraints only on the finished value +create domain insert_nnarray as int[] + check (value[1] is not null and value[2] is not null); +create domain insert_test_domain as insert_test_type + check ((value).if1 is not null and (value).if2 is not null); +create table inserttesta (f1 insert_nnarray); +insert into inserttesta (f1[1]) values (1); -- fail +ERROR: value for domain insert_nnarray violates check constraint "insert_nnarray_check" +insert into inserttesta (f1[1], f1[2]) values (1, 2); +create table inserttestb (f1 insert_test_domain); +insert into inserttestb (f1.if1) values (1); -- fail +ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" +insert into inserttestb (f1.if1, f1.if2) values (1, '{foo}'); +drop table inserttesta; +drop table inserttestb; +drop domain insert_nnarray; +drop type insert_test_type cascade; +NOTICE: drop cascades to type insert_test_domain -- direct partition inserts should check partition bound constraint create table range_parted ( a text, diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index f520243..5be2cc9 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -92,10 +92,39 @@ WHERE t1.unique1 < 1000; 1000 | 9.5000000000000000 (1 row) +SET enable_mergejoin TO off; +-- Test for varlena datatype with expr evaluation +CREATE TABLE expr_key (x numeric, t text); +INSERT INTO expr_key (x, t) +SELECT d1::numeric, d1::text FROM ( + SELECT round((d / pi())::numeric, 7) AS d1 FROM generate_series(1, 20) AS d +) t; +-- duplicate rows so we get some cache hits +INSERT INTO expr_key SELECT * FROM expr_key; +CREATE INDEX expr_key_idx_x_t ON expr_key (x, t); +VACUUM ANALYZE expr_key; +-- Ensure we get we get a cache miss and hit for each of the 20 distinct values +SELECT explain_memoize(' +SELECT * FROM expr_key t1 INNER JOIN expr_key t2 +ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false); + explain_memoize +------------------------------------------------------------------------------------------- + Nested Loop (actual rows=80 loops=N) + -> Seq Scan on expr_key t1 (actual rows=40 loops=N) + -> Memoize (actual rows=2 loops=N) + Cache Key: t1.x, (t1.t)::numeric + Cache Mode: logical + Hits: 20 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB + -> Index Only Scan using expr_key_idx_x_t on expr_key t2 (actual rows=2 loops=N) + Index Cond: (x = (t1.t)::numeric) + Filter: (t1.x = (t)::numeric) + Heap Fetches: N +(10 rows) + +DROP TABLE expr_key; -- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions SET work_mem TO '64kB'; SET hash_mem_multiplier TO 1.0; -SET enable_mergejoin TO off; -- Ensure we get some evictions. We're unable to validate the hits and misses -- here as the number of entries that fit in the cache at once will vary -- between different machines. diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index f87905f..bfdf59a 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -3,6 +3,7 @@ -- CREATE USER regress_merge_privs; CREATE USER regress_merge_no_privs; +CREATE USER regress_merge_none; DROP TABLE IF EXISTS target; NOTICE: table "target" does not exist, skipping DROP TABLE IF EXISTS source; @@ -159,6 +160,14 @@ ERROR: cannot execute MERGE on relation "mv" DETAIL: This operation is not supported for materialized views. DROP MATERIALIZED VIEW mv; -- permissions +SET SESSION AUTHORIZATION regress_merge_none; +MERGE INTO target +USING (SELECT 1) +ON true +WHEN MATCHED THEN + DO NOTHING; +ERROR: permission denied for table target +SET SESSION AUTHORIZATION regress_merge_privs; MERGE INTO target USING source2 ON target.tid = source2.sid @@ -1474,6 +1483,56 @@ WHEN MATCHED AND t.a < 10 THEN DROP TABLE ex_msource, ex_mtarget; DROP FUNCTION explain_merge(text); +-- EXPLAIN SubPlans and InitPlans +CREATE TABLE src (a int, b int, c int, d int); +CREATE TABLE tgt (a int, b int, c int, d int); +CREATE TABLE ref (ab int, cd int); +EXPLAIN (verbose, costs off) +MERGE INTO tgt t +USING (SELECT *, (SELECT count(*) FROM ref r + WHERE r.ab = s.a + s.b + AND r.cd = s.c - s.d) cnt + FROM src s) s +ON t.a = s.a AND t.b < s.cnt +WHEN MATCHED AND t.c > s.cnt THEN + UPDATE SET (b, c) = (SELECT s.b, s.cnt); + QUERY PLAN +------------------------------------------------------------------------------------- + Merge on public.tgt t + -> Hash Join + Output: t.ctid, s.a, s.b, s.c, s.d, s.ctid + Hash Cond: (t.a = s.a) + Join Filter: (t.b < (SubPlan 1)) + -> Seq Scan on public.tgt t + Output: t.ctid, t.a, t.b + -> Hash + Output: s.a, s.b, s.c, s.d, s.ctid + -> Seq Scan on public.src s + Output: s.a, s.b, s.c, s.d, s.ctid + SubPlan 1 + -> Aggregate + Output: count(*) + -> Seq Scan on public.ref r + Output: r.ab, r.cd + Filter: ((r.ab = (s.a + s.b)) AND (r.cd = (s.c - s.d))) + SubPlan 4 + -> Aggregate + Output: count(*) + -> Seq Scan on public.ref r_2 + Output: r_2.ab, r_2.cd + Filter: ((r_2.ab = (s.a + s.b)) AND (r_2.cd = (s.c - s.d))) + SubPlan 3 (returns $9,$10) + -> Result + Output: s.b, $8 + InitPlan 2 (returns $8) + -> Aggregate + Output: count(*) + -> Seq Scan on public.ref r_1 + Output: r_1.ab, r_1.cd + Filter: ((r_1.ab = (s.a + s.b)) AND (r_1.cd = (s.c - s.d))) +(32 rows) + +DROP TABLE src, tgt, ref; -- Subqueries BEGIN; MERGE INTO sq_target t @@ -2248,3 +2307,4 @@ DROP TABLE source, source2; DROP FUNCTION merge_trigfunc(); DROP USER regress_merge_privs; DROP USER regress_merge_no_privs; +DROP USER regress_merge_none; diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index a820385..4f88e7b 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1169,6 +1169,57 @@ select * from boolpart where a is not unknown; t (2 rows) +-- try some other permutations with a NULL partition instead of a DEFAULT +delete from boolpart where a is null; +create table boolpart_null partition of boolpart for values in (null); +insert into boolpart values(null); +explain (costs off) select * from boolpart where a is not true; + QUERY PLAN +-------------------------------------------- + Append + -> Seq Scan on boolpart_f boolpart_1 + Filter: (a IS NOT TRUE) + -> Seq Scan on boolpart_null boolpart_2 + Filter: (a IS NOT TRUE) +(5 rows) + +explain (costs off) select * from boolpart where a is not true and a is not false; + QUERY PLAN +-------------------------------------------------- + Seq Scan on boolpart_null boolpart + Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE)) +(2 rows) + +explain (costs off) select * from boolpart where a is not false; + QUERY PLAN +-------------------------------------------- + Append + -> Seq Scan on boolpart_t boolpart_1 + Filter: (a IS NOT FALSE) + -> Seq Scan on boolpart_null boolpart_2 + Filter: (a IS NOT FALSE) +(5 rows) + +select * from boolpart where a is not true; + a +--- + f + +(2 rows) + +select * from boolpart where a is not true and a is not false; + a +--- + +(1 row) + +select * from boolpart where a is not false; + a +--- + t + +(2 rows) + -- inverse boolean partitioning - a seemingly unlikely design, but we've got -- code for it, so we'd better test it. create table iboolpart (a bool) partition by list ((not a)); @@ -1315,11 +1366,37 @@ select * from iboolpart where a is not unknown; f (2 rows) +-- Try some other permutations with a NULL partition instead of a DEFAULT +delete from iboolpart where a is null; +create table iboolpart_null partition of iboolpart for values in (null); +insert into iboolpart values(null); +-- Pruning shouldn't take place for these. Just check the result is correct +select * from iboolpart where a is not true; + a +--- + f + +(2 rows) + +select * from iboolpart where a is not true and a is not false; + a +--- + +(1 row) + +select * from iboolpart where a is not false; + a +--- + t + +(2 rows) + create table boolrangep (a bool, b bool, c int) partition by range (a,b,c); create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100); create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100); create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50); create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100); +create table boolrangep_null partition of boolrangep default; -- try a more complex case that's been known to trip up pruning in the past explain (costs off) select * from boolrangep where not a and not b and c = 25; QUERY PLAN @@ -1328,6 +1405,32 @@ explain (costs off) select * from boolrangep where not a and not b and c = 25; Filter: ((NOT a) AND (NOT b) AND (c = 25)) (2 rows) +-- ensure we prune boolrangep_tf +explain (costs off) select * from boolrangep where a is not true and not b and c = 25; + QUERY PLAN +------------------------------------------------------------ + Append + -> Seq Scan on boolrangep_ff1 boolrangep_1 + Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) + -> Seq Scan on boolrangep_ff2 boolrangep_2 + Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) + -> Seq Scan on boolrangep_ft boolrangep_3 + Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) + -> Seq Scan on boolrangep_null boolrangep_4 + Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) +(9 rows) + +-- ensure we prune everything apart from boolrangep_tf and boolrangep_null +explain (costs off) select * from boolrangep where a is not false and not b and c = 25; + QUERY PLAN +------------------------------------------------------------- + Append + -> Seq Scan on boolrangep_tf boolrangep_1 + Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25)) + -> Seq Scan on boolrangep_null boolrangep_2 + Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25)) +(5 rows) + -- test scalar-to-array operators create table coercepart (a varchar) partition by list (a); create table coercepart_ab partition of coercepart for values in ('ab'); @@ -2709,6 +2812,7 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; Index Cond: (a = 1) -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1) Recheck Cond: (a = 1) + Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) Index Cond: (a = 1) -> Materialize (actual rows=1 loops=1) @@ -2724,9 +2828,10 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; Index Cond: (a = 1) -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1) Recheck Cond: (a = 1) + Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) Index Cond: (a = 1) -(34 rows) +(36 rows) table ab; a | b diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 272f5d2..fb5a0d8 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -2390,11 +2390,9 @@ select namedparmcursor_test7(); ERROR: division by zero CONTEXT: SQL expression "42/0 AS p1, 77 AS p2" PL/pgSQL function namedparmcursor_test7() line 6 at OPEN --- check that line comments work correctly within the argument list (there --- is some special handling of this case in the code: the newline after the --- comment must be preserved when the argument-evaluating query is --- constructed, otherwise the comment effectively comments out the next --- argument, too) +-- check that line comments work correctly within the argument list +-- (this used to require a special hack in the code; it no longer does, +-- but let's keep the test anyway) create function namedparmcursor_test8() returns int4 as $$ declare c1 cursor (p1 int, p2 int) for diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out index fbb840e..397a8b3 100644 --- a/src/test/regress/expected/rangefuncs.out +++ b/src/test/regress/expected/rangefuncs.out @@ -2485,3 +2485,19 @@ select * from [{"id": "1"}] | 1 (1 row) +-- check detection of mismatching record types with a const-folded expression +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int); -- fail +ERROR: function return row and query-specified return row do not match +DETAIL: Returned row contains 3 attributes, but query expects 2. +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int, f int, g int); -- fail +ERROR: function return row and query-specified return row do not match +DETAIL: Returned row contains 3 attributes, but query expects 4. +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int, f float); -- fail +ERROR: function return row and query-specified return row do not match +DETAIL: Returned type integer at ordinal position 3, but query expects double precision. +select * from int8_tbl, coalesce(row(1)) as (a int, b int); -- fail +ERROR: function return row and query-specified return row do not match +DETAIL: Returned row contains 1 attribute, but query expects 2. diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 7fd81e6..09a2556 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2497,10 +2497,7 @@ pg_stats_ext| SELECT cn.nspname AS schemaname, array_agg(pg_mcv_list_items.frequency) AS most_common_freqs, array_agg(pg_mcv_list_items.base_frequency) AS most_common_base_freqs FROM pg_mcv_list_items(sd.stxdmcv) pg_mcv_list_items(index, "values", nulls, frequency, base_frequency)) m ON ((sd.stxdmcv IS NOT NULL))) - WHERE ((NOT (EXISTS ( SELECT 1 - FROM (unnest(s.stxkeys) k(k) - JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k)))) - WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); + WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); pg_stats_ext_exprs| SELECT cn.nspname AS schemaname, c.relname AS tablename, sn.nspname AS statistics_schemaname, @@ -2573,7 +2570,8 @@ pg_stats_ext_exprs| SELECT cn.nspname AS schemaname, LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace))) LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace))) JOIN LATERAL ( SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr, - unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL))); + unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL))) + WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); pg_tables| SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index a430153..b4c8561 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -3281,10 +3281,53 @@ SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not le (0 rows) DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak +-- privilege checks for pg_stats_ext and pg_stats_ext_exprs +RESET SESSION AUTHORIZATION; +CREATE TABLE stats_ext_tbl (id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, col TEXT); +INSERT INTO stats_ext_tbl (col) VALUES ('secret'), ('secret'), ('very secret'); +CREATE STATISTICS s_col ON id, col FROM stats_ext_tbl; +CREATE STATISTICS s_expr ON mod(id, 2), lower(col) FROM stats_ext_tbl; +ANALYZE stats_ext_tbl; +-- unprivileged role should not have access +SET SESSION AUTHORIZATION regress_stats_user1; +SELECT statistics_name, most_common_vals FROM pg_stats_ext x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + statistics_name | most_common_vals +-----------------+------------------ +(0 rows) + +SELECT statistics_name, most_common_vals FROM pg_stats_ext_exprs x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + statistics_name | most_common_vals +-----------------+------------------ +(0 rows) + +-- give unprivileged role ownership of table +RESET SESSION AUTHORIZATION; +ALTER TABLE stats_ext_tbl OWNER TO regress_stats_user1; +-- unprivileged role should now have access +SET SESSION AUTHORIZATION regress_stats_user1; +SELECT statistics_name, most_common_vals FROM pg_stats_ext x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + statistics_name | most_common_vals +-----------------+------------------------------------------- + s_col | {{1,secret},{2,secret},{3,"very secret"}} + s_expr | {{0,secret},{1,secret},{1,"very secret"}} +(2 rows) + +SELECT statistics_name, most_common_vals FROM pg_stats_ext_exprs x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + statistics_name | most_common_vals +-----------------+------------------ + s_expr | {secret} + s_expr | {1} +(2 rows) + -- Tidy up DROP OPERATOR <<< (int, int); DROP FUNCTION op_leak(int, int); RESET SESSION AUTHORIZATION; +DROP TABLE stats_ext_tbl; DROP SCHEMA tststats CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table tststats.priv_test_tbl diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index c64bcb7..ef45d28 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -736,6 +736,13 @@ SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2 Sat Feb 01 00:57:30 2020 (1 row) +-- test roundoff edge case when source < origin +SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00'); + date_bin +-------------------------- + Thu Feb 01 15:00:00 2024 +(1 row) + -- disallow intervals with months or years SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01'); ERROR: timestamps cannot be binned into intervals containing months or years @@ -747,6 +754,13 @@ ERROR: stride must be greater than zero -- disallow negative intervals SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00'); ERROR: stride must be greater than zero +-- test overflow cases +select date_bin('15 minutes'::interval, timestamp '294276-12-30', timestamp '4000-12-20 BC'); +ERROR: interval out of range +select date_bin('200000000 days'::interval, '2024-02-01'::timestamp, '2024-01-01'::timestamp); +ERROR: interval out of range +select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamp, '4000-01-01 BC'::timestamp); +ERROR: timestamp out of range -- Test casting within a BETWEEN qualifier SELECT d1 - timestamp without time zone '1997-01-02' AS diff FROM TIMESTAMP_TBL diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index 0dd2fe2..db56fcf 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -780,6 +780,13 @@ SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timesta Fri Jan 31 16:57:30 2020 PST (1 row) +-- test roundoff edge case when source < origin +SELECT date_bin('30 minutes'::interval, timestamptz '2024-02-01 15:00:00', timestamptz '2024-02-01 17:00:00'); + date_bin +------------------------------ + Thu Feb 01 15:00:00 2024 PST +(1 row) + -- disallow intervals with months or years SELECT date_bin('5 months'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00'); ERROR: timestamps cannot be binned into intervals containing months or years @@ -791,6 +798,13 @@ ERROR: stride must be greater than zero -- disallow negative intervals SELECT date_bin('-2 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00'); ERROR: stride must be greater than zero +-- test overflow cases +select date_bin('15 minutes'::interval, timestamptz '294276-12-30', timestamptz '4000-12-20 BC'); +ERROR: interval out of range +select date_bin('200000000 days'::interval, '2024-02-01'::timestamptz, '2024-01-01'::timestamptz); +ERROR: interval out of range +select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamptz, '4000-01-01 BC'::timestamptz); +ERROR: timestamp out of range -- Test casting within a BETWEEN qualifier SELECT d1 - timestamp with time zone '1997-01-02' AS diff FROM TIMESTAMPTZ_TBL diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 78e9030..7f774e5 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1745,6 +1745,10 @@ select * from parent; select * from child; update parent set val1 = 'b' where aid = 1; -- should fail ERROR: tuple to be updated was already modified by an operation triggered by the current command HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. +merge into parent p using (values (1)) as v(id) on p.aid = v.id + when matched then update set val1 = 'b'; -- should fail +ERROR: tuple to be updated or deleted was already modified by an operation triggered by the current command +HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. select * from parent; select * from child; aid | val1 | val2 | val3 | val4 | bcnt -----+------+------+------+------+------ @@ -1759,6 +1763,10 @@ select * from parent; select * from child; delete from parent where aid = 1; -- should fail ERROR: tuple to be deleted was already modified by an operation triggered by the current command HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. +merge into parent p using (values (1)) as v(id) on p.aid = v.id + when matched then delete; -- should fail +ERROR: tuple to be updated or deleted was already modified by an operation triggered by the current command +HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. select * from parent; select * from child; aid | val1 | val2 | val3 | val4 | bcnt -----+------+------+------+------+------ diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index 8612788..1a9cc66 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -3577,13 +3577,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, salary, - count(empno) OVER (ORDER BY salary DESC) c + count(1) OVER (ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +------------------------------------------- WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) <= 3) + Run Condition: (count(1) OVER (?) <= 3) -> Sort Sort Key: empsalary.salary DESC -> Seq Scan on empsalary @@ -3592,7 +3592,7 @@ WHERE c <= 3; SELECT * FROM (SELECT empno, salary, - count(empno) OVER (ORDER BY salary DESC) c + count(1) OVER (ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; empno | salary | c @@ -3704,19 +3704,19 @@ WHERE rn < 3; -> Seq Scan on empsalary (6 rows) --- likewise with count(empno) instead of row_number() +-- likewise with count(1) instead of row_number() EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c + count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; QUERY PLAN ------------------------------------------------------------ WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) <= 3) + Run Condition: (count(1) OVER (?) <= 3) -> Sort Sort Key: empsalary.depname, empsalary.salary DESC -> Seq Scan on empsalary @@ -3727,7 +3727,7 @@ SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c + count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; empno | depname | salary | c @@ -3749,13 +3749,13 @@ SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER () c + count(1) OVER () c FROM empsalary) emp WHERE c = 1; - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +------------------------------------------ WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) = 1) + Run Condition: (count(1) OVER (?) = 1) -> Seq Scan on empsalary (3 rows) @@ -3763,7 +3763,7 @@ WHERE c = 1; EXPLAIN (COSTS OFF) SELECT * FROM (SELECT *, - count(salary) OVER (PARTITION BY depname || '') c1, -- w1 + count(1) OVER (PARTITION BY depname || '') c1, -- w1 row_number() OVER (PARTITION BY depname) rn, -- w2 count(*) OVER (PARTITION BY depname) c2, -- w2 count(*) OVER (PARTITION BY '' || depname) c3, -- w3 @@ -3775,7 +3775,7 @@ SELECT * FROM Subquery Scan on e -> WindowAgg Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER (?)) < 2)) - Run Condition: (count(empsalary.salary) OVER (?) <= 3) + Run Condition: (count(1) OVER (?) <= 3) -> Sort Sort Key: (((empsalary.depname)::text || ''::text)) -> WindowAgg @@ -3791,7 +3791,7 @@ SELECT * FROM -- Ensure we correctly filter out all of the run conditions from each window SELECT * FROM (SELECT *, - count(salary) OVER (PARTITION BY depname || '') c1, -- w1 + count(1) OVER (PARTITION BY depname || '') c1, -- w1 row_number() OVER (PARTITION BY depname) rn, -- w2 count(*) OVER (PARTITION BY depname) c2, -- w2 count(*) OVER (PARTITION BY '' || depname) c3, -- w3 @@ -3804,32 +3804,6 @@ SELECT * FROM sales | 3 | 4800 | 08-01-2007 | 3 | 1 | 3 | 3 | 1 (2 rows) --- Ensure we remove references to reduced outer joins as nulling rels in run --- conditions -EXPLAIN (COSTS OFF) -SELECT 1 FROM - (SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c - FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE - WHERE e1.empno = e2.empno) s -WHERE s.c = 1; - QUERY PLAN ---------------------------------------------------------- - Subquery Scan on s - Filter: (s.c = 1) - -> WindowAgg - Run Condition: (ntile(e2.salary) OVER (?) <= 1) - -> Sort - Sort Key: e1.depname - -> Merge Join - Merge Cond: (e1.empno = e2.empno) - -> Sort - Sort Key: e1.empno - -> Seq Scan on empsalary e1 - -> Sort - Sort Key: e2.empno - -> Seq Scan on empsalary e2 -(14 rows) - -- Tests to ensure we don't push down the run condition when it's not valid to -- do so. -- Ensure we don't push down when the frame options show that the window @@ -3889,6 +3863,42 @@ WHERE c = 1; -> Seq Scan on empsalary (6 rows) +-- Ensure we don't use a run condition when the WindowFunc arg contains a Var +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + count(empno) OVER (ORDER BY empno DESC) c + FROM empsalary) emp +WHERE c = 1; + QUERY PLAN +---------------------------------------------- + Subquery Scan on emp + Filter: (emp.c = 1) + -> WindowAgg + -> Sort + Sort Key: empsalary.empno DESC + -> Seq Scan on empsalary +(6 rows) + +-- As above but with ntile(). +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + ntile(empno::int) OVER (ORDER BY empno DESC) nt + FROM empsalary) emp +WHERE nt = 1; + QUERY PLAN +---------------------------------------------- + Subquery Scan on emp + Filter: (emp.nt = 1) + -> WindowAgg + -> Sort + Sort Key: empsalary.empno DESC + -> Seq Scan on empsalary +(6 rows) + -- Ensure we don't use a run condition when the WindowFunc contains subplans EXPLAIN (COSTS OFF) SELECT * FROM diff --git a/src/test/regress/pg_regress_main.c b/src/test/regress/pg_regress_main.c index 4274299..ff0fde1 100644 --- a/src/test/regress/pg_regress_main.c +++ b/src/test/regress/pg_regress_main.c @@ -54,7 +54,7 @@ psql_start_test(const char *testname, outputdir, testname); snprintf(expectfile, sizeof(expectfile), "%s/expected/%s.out", - outputdir, testname); + expecteddir, testname); if (!file_exists(expectfile)) snprintf(expectfile, sizeof(expectfile), "%s/expected/%s.out", inputdir, testname); diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index cc8f0ef..758ad90 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -749,7 +749,7 @@ select string_agg(v, decode('ee', 'hex')) from bytea_test_table; drop table bytea_test_table; -- Test parallel string_agg and array_agg -create table pagg_test (x int, y int); +create table pagg_test (x int, y int) with (autovacuum_enabled = off); insert into pagg_test select (case x % 4 when 1 then null else x end), x % 10 from generate_series(1,5000) x; diff --git a/src/test/regress/sql/bit.sql b/src/test/regress/sql/bit.sql index 2cd550d..34230b9 100644 --- a/src/test/regress/sql/bit.sql +++ b/src/test/regress/sql/bit.sql @@ -29,6 +29,11 @@ INSERT INTO VARBIT_TABLE VALUES (B'101011111010'); -- too long --INSERT INTO VARBIT_TABLE VALUES ('X555'); SELECT * FROM VARBIT_TABLE; +-- Literals with syntax errors +SELECT b' 0'; +SELECT b'0 '; +SELECT x' 0'; +SELECT x'0 '; -- Concatenation SELECT v, b, (v || b) AS concat diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql index 35b8727..012cdf3 100644 --- a/src/test/regress/sql/create_procedure.sql +++ b/src/test/regress/sql/create_procedure.sql @@ -90,7 +90,16 @@ AS $$ CALL ptest4a(a, b); -- error, not supported $$; -DROP PROCEDURE ptest4a; +-- we used to get confused by a single output argument that is composite +CREATE PROCEDURE ptest4c(INOUT comp int8_tbl) +LANGUAGE SQL +AS $$ +SELECT ROW(1, 2); +$$; + +CALL ptest4c(NULL); + +DROP PROCEDURE ptest4a, ptest4c; -- named and default parameters diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql index eefb860..aa147b1 100644 --- a/src/test/regress/sql/foreign_data.sql +++ b/src/test/regress/sql/foreign_data.sql @@ -419,8 +419,10 @@ ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; ALTER FOREIGN TABLE ft1 DROP COLUMN c9; +ALTER FOREIGN TABLE ft1 ADD COLUMN c11 serial; ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR +ALTER SEQUENCE foreign_schema.ft1_c11_seq SET SCHEMA public; -- ERROR ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; \d foreign_schema.foreign_table_1 diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql index 8621446..af70f28 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -121,6 +121,7 @@ SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' 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 '294276-12-31 23:59:59' + interval '9223372036854775807 microseconds' AS "out of range"; SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days"; -- Shorthand values @@ -153,6 +154,7 @@ 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 '294276-12-31 23:59:59 UTC' + interval '9223372036854775807 microseconds' 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/identity.sql b/src/test/regress/sql/identity.sql index 9b8db2e..91d2e44 100644 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -214,6 +214,24 @@ SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regcl ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error +-- check that unlogged propagates to sequence +CREATE UNLOGGED TABLE itest17 (a int NOT NULL, b text); +ALTER TABLE itest17 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ALTER TABLE itest17 ADD COLUMN c int GENERATED ALWAYS AS IDENTITY; +\d itest17 +\d itest17_a_seq +\d itest17_c_seq +CREATE TABLE itest18 (a int NOT NULL, b text); +ALTER TABLE itest18 SET UNLOGGED, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +\d itest18 +\d itest18_a_seq +ALTER TABLE itest18 SET LOGGED; +\d itest18 +\d itest18_a_seq +ALTER TABLE itest18 SET UNLOGGED; +\d itest18 +\d itest18_a_seq + -- kinda silly to change property in the same command, but it should work ALTER TABLE itest3 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY, diff --git a/src/test/regress/sql/index_including.sql b/src/test/regress/sql/index_including.sql index 44b3400..ad9cbdd 100644 --- a/src/test/regress/sql/index_including.sql +++ b/src/test/regress/sql/index_including.sql @@ -217,3 +217,22 @@ ALTER TABLE tbl ALTER c1 TYPE bigint; ALTER TABLE tbl ALTER c3 TYPE bigint; \d tbl DROP TABLE tbl; + +/* + * 10. Test coverage for names stored as cstrings in indexes + */ +CREATE TABLE nametbl (c1 int, c2 name, c3 float); +CREATE INDEX nametbl_c1_c2_idx ON nametbl (c2, c1) INCLUDE (c3); +INSERT INTO nametbl VALUES(1, 'two', 3.0); +VACUUM nametbl; +SET enable_seqscan = 0; + +-- Ensure we get an index only scan plan +EXPLAIN (COSTS OFF) SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1; + +-- Validate the results look sane +SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1; + +RESET enable_seqscan; + +DROP TABLE nametbl;
\ No newline at end of file diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql index bdcffd0..2b086ee 100644 --- a/src/test/regress/sql/insert.sql +++ b/src/test/regress/sql/insert.sql @@ -105,7 +105,84 @@ create rule irule3 as on insert to inserttest2 do also drop table inserttest2; drop table inserttest; -drop type insert_test_type; + +-- Make the same tests with domains over the array and composite fields + +create domain insert_pos_ints as int[] check (value[1] > 0); + +create domain insert_test_domain as insert_test_type + check ((value).if2[1] is not null); + +create table inserttesta (f1 int, f2 insert_pos_ints); +create table inserttestb (f3 insert_test_domain, f4 insert_test_domain[]); + +insert into inserttesta (f2[1], f2[2]) values (1,2); +insert into inserttesta (f2[1], f2[2]) values (3,4), (5,6); +insert into inserttesta (f2[1], f2[2]) select 7,8; +insert into inserttesta (f2[1], f2[2]) values (1,default); -- not supported +insert into inserttesta (f2[1], f2[2]) values (0,2); +insert into inserttesta (f2[1], f2[2]) values (3,4), (0,6); +insert into inserttesta (f2[1], f2[2]) select 0,8; + +insert into inserttestb (f3.if1, f3.if2) values (1,array['foo']); +insert into inserttestb (f3.if1, f3.if2) values (1,'{foo}'), (2,'{bar}'); +insert into inserttestb (f3.if1, f3.if2) select 3, '{baz,quux}'; +insert into inserttestb (f3.if1, f3.if2) values (1,default); -- not supported +insert into inserttestb (f3.if1, f3.if2) values (1,array[null]); +insert into inserttestb (f3.if1, f3.if2) values (1,'{null}'), (2,'{bar}'); +insert into inserttestb (f3.if1, f3.if2) select 3, '{null,quux}'; + +insert into inserttestb (f3.if2[1], f3.if2[2]) values ('foo', 'bar'); +insert into inserttestb (f3.if2[1], f3.if2[2]) values ('foo', 'bar'), ('baz', 'quux'); +insert into inserttestb (f3.if2[1], f3.if2[2]) select 'bear', 'beer'; + +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) values (row(1,'{x}'), 'foo', 'bar'); +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) values (row(1,'{x}'), 'foo', 'bar'), (row(2,'{y}'), 'baz', 'quux'); +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) select row(1,'{x}')::insert_test_domain, 'bear', 'beer'; + +select * from inserttesta; +select * from inserttestb; + +-- also check reverse-listing +create table inserttest2 (f1 bigint, f2 text); +create rule irule1 as on insert to inserttest2 do also + insert into inserttestb (f3.if2[1], f3.if2[2]) + values (new.f1,new.f2); +create rule irule2 as on insert to inserttest2 do also + insert into inserttestb (f4[1].if1, f4[1].if2[2]) + values (1,'fool'),(new.f1,new.f2); +create rule irule3 as on insert to inserttest2 do also + insert into inserttestb (f4[1].if1, f4[1].if2[2]) + select new.f1, new.f2; +\d+ inserttest2 + +drop table inserttest2; +drop table inserttesta; +drop table inserttestb; +drop domain insert_pos_ints; +drop domain insert_test_domain; + +-- Verify that multiple inserts to subfields of a domain-over-container +-- check the domain constraints only on the finished value + +create domain insert_nnarray as int[] + check (value[1] is not null and value[2] is not null); + +create domain insert_test_domain as insert_test_type + check ((value).if1 is not null and (value).if2 is not null); + +create table inserttesta (f1 insert_nnarray); +insert into inserttesta (f1[1]) values (1); -- fail +insert into inserttesta (f1[1], f1[2]) values (1, 2); + +create table inserttestb (f1 insert_test_domain); +insert into inserttestb (f1.if1) values (1); -- fail +insert into inserttestb (f1.if1, f1.if2) values (1, '{foo}'); + +drop table inserttesta; +drop table inserttestb; +drop domain insert_nnarray; +drop type insert_test_type cascade; -- direct partition inserts should check partition bound constraint create table range_parted ( diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql index 29ab1ea..09a0ba1 100644 --- a/src/test/regress/sql/memoize.sql +++ b/src/test/regress/sql/memoize.sql @@ -57,10 +57,31 @@ LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1 OFFSET 0) t2 WHERE t1.unique1 < 1000; +SET enable_mergejoin TO off; + +-- Test for varlena datatype with expr evaluation +CREATE TABLE expr_key (x numeric, t text); +INSERT INTO expr_key (x, t) +SELECT d1::numeric, d1::text FROM ( + SELECT round((d / pi())::numeric, 7) AS d1 FROM generate_series(1, 20) AS d +) t; + +-- duplicate rows so we get some cache hits +INSERT INTO expr_key SELECT * FROM expr_key; + +CREATE INDEX expr_key_idx_x_t ON expr_key (x, t); +VACUUM ANALYZE expr_key; + +-- Ensure we get we get a cache miss and hit for each of the 20 distinct values +SELECT explain_memoize(' +SELECT * FROM expr_key t1 INNER JOIN expr_key t2 +ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false); + +DROP TABLE expr_key; + -- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions SET work_mem TO '64kB'; SET hash_mem_multiplier TO 1.0; -SET enable_mergejoin TO off; -- Ensure we get some evictions. We're unable to validate the hits and misses -- here as the number of entries that fit in the cache at once will vary -- between different machines. diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 66cb75a..ac6d2f0 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -4,6 +4,8 @@ CREATE USER regress_merge_privs; CREATE USER regress_merge_no_privs; +CREATE USER regress_merge_none; + DROP TABLE IF EXISTS target; DROP TABLE IF EXISTS source; CREATE TABLE target (tid integer, balance integer) @@ -118,6 +120,14 @@ DROP MATERIALIZED VIEW mv; -- permissions +SET SESSION AUTHORIZATION regress_merge_none; +MERGE INTO target +USING (SELECT 1) +ON true +WHEN MATCHED THEN + DO NOTHING; + +SET SESSION AUTHORIZATION regress_merge_privs; MERGE INTO target USING source2 ON target.tid = source2.sid @@ -938,6 +948,23 @@ WHEN MATCHED AND t.a < 10 THEN DROP TABLE ex_msource, ex_mtarget; DROP FUNCTION explain_merge(text); +-- EXPLAIN SubPlans and InitPlans +CREATE TABLE src (a int, b int, c int, d int); +CREATE TABLE tgt (a int, b int, c int, d int); +CREATE TABLE ref (ab int, cd int); + +EXPLAIN (verbose, costs off) +MERGE INTO tgt t +USING (SELECT *, (SELECT count(*) FROM ref r + WHERE r.ab = s.a + s.b + AND r.cd = s.c - s.d) cnt + FROM src s) s +ON t.a = s.a AND t.b < s.cnt +WHEN MATCHED AND t.c > s.cnt THEN + UPDATE SET (b, c) = (SELECT s.b, s.cnt); + +DROP TABLE src, tgt, ref; + -- Subqueries BEGIN; MERGE INTO sq_target t @@ -1471,3 +1498,4 @@ DROP TABLE source, source2; DROP FUNCTION merge_trigfunc(); DROP USER regress_merge_privs; DROP USER regress_merge_no_privs; +DROP USER regress_merge_none; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 70b20fb..45f934a 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -178,6 +178,19 @@ select * from boolpart where a is not true and a is not false; select * from boolpart where a is unknown; select * from boolpart where a is not unknown; +-- try some other permutations with a NULL partition instead of a DEFAULT +delete from boolpart where a is null; +create table boolpart_null partition of boolpart for values in (null); +insert into boolpart values(null); + +explain (costs off) select * from boolpart where a is not true; +explain (costs off) select * from boolpart where a is not true and a is not false; +explain (costs off) select * from boolpart where a is not false; + +select * from boolpart where a is not true; +select * from boolpart where a is not true and a is not false; +select * from boolpart where a is not false; + -- inverse boolean partitioning - a seemingly unlikely design, but we've got -- code for it, so we'd better test it. create table iboolpart (a bool) partition by list ((not a)); @@ -204,15 +217,32 @@ select * from iboolpart where a is not true and a is not false; select * from iboolpart where a is unknown; select * from iboolpart where a is not unknown; +-- Try some other permutations with a NULL partition instead of a DEFAULT +delete from iboolpart where a is null; +create table iboolpart_null partition of iboolpart for values in (null); +insert into iboolpart values(null); + +-- Pruning shouldn't take place for these. Just check the result is correct +select * from iboolpart where a is not true; +select * from iboolpart where a is not true and a is not false; +select * from iboolpart where a is not false; + create table boolrangep (a bool, b bool, c int) partition by range (a,b,c); create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100); create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100); create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50); create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100); +create table boolrangep_null partition of boolrangep default; -- try a more complex case that's been known to trip up pruning in the past explain (costs off) select * from boolrangep where not a and not b and c = 25; +-- ensure we prune boolrangep_tf +explain (costs off) select * from boolrangep where a is not true and not b and c = 25; + +-- ensure we prune everything apart from boolrangep_tf and boolrangep_null +explain (costs off) select * from boolrangep where a is not false and not b and c = 25; + -- test scalar-to-array operators create table coercepart (a varchar) partition by list (a); create table coercepart_ab partition of coercepart for values in ('ab'); diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 924d524..b3fc1e2 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -2047,11 +2047,9 @@ begin end $$ language plpgsql; select namedparmcursor_test7(); --- check that line comments work correctly within the argument list (there --- is some special handling of this case in the code: the newline after the --- comment must be preserved when the argument-evaluating query is --- constructed, otherwise the comment effectively comments out the next --- argument, too) +-- check that line comments work correctly within the argument list +-- (this used to require a special hack in the code; it no longer does, +-- but let's keep the test anyway) create function namedparmcursor_test8() returns int4 as $$ declare c1 cursor (p1 int, p2 int) for diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql index 63351e1..3c47c98 100644 --- a/src/test/regress/sql/rangefuncs.sql +++ b/src/test/regress/sql/rangefuncs.sql @@ -815,3 +815,13 @@ select * from from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb]) as unnested_modules(module)) as ss, jsonb_to_recordset(ss.lecture) as j (id text); + +-- check detection of mismatching record types with a const-folded expression + +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int); -- fail +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int, f int, g int); -- fail +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int, f float); -- fail +select * from int8_tbl, coalesce(row(1)) as (a int, b int); -- fail diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 90b625a..1b80d36 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -1657,9 +1657,36 @@ SET SESSION AUTHORIZATION regress_stats_user1; SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak +-- privilege checks for pg_stats_ext and pg_stats_ext_exprs +RESET SESSION AUTHORIZATION; +CREATE TABLE stats_ext_tbl (id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, col TEXT); +INSERT INTO stats_ext_tbl (col) VALUES ('secret'), ('secret'), ('very secret'); +CREATE STATISTICS s_col ON id, col FROM stats_ext_tbl; +CREATE STATISTICS s_expr ON mod(id, 2), lower(col) FROM stats_ext_tbl; +ANALYZE stats_ext_tbl; + +-- unprivileged role should not have access +SET SESSION AUTHORIZATION regress_stats_user1; +SELECT statistics_name, most_common_vals FROM pg_stats_ext x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); +SELECT statistics_name, most_common_vals FROM pg_stats_ext_exprs x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + +-- give unprivileged role ownership of table +RESET SESSION AUTHORIZATION; +ALTER TABLE stats_ext_tbl OWNER TO regress_stats_user1; + +-- unprivileged role should now have access +SET SESSION AUTHORIZATION regress_stats_user1; +SELECT statistics_name, most_common_vals FROM pg_stats_ext x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); +SELECT statistics_name, most_common_vals FROM pg_stats_ext_exprs x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + -- Tidy up DROP OPERATOR <<< (int, int); DROP FUNCTION op_leak(int, int); RESET SESSION AUTHORIZATION; +DROP TABLE stats_ext_tbl; DROP SCHEMA tststats CASCADE; DROP USER regress_stats_user1; diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql index b9bcce9..b3ebed8 100644 --- a/src/test/regress/sql/timestamp.sql +++ b/src/test/regress/sql/timestamp.sql @@ -268,6 +268,9 @@ FROM ( -- shift bins using the origin parameter: SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2020-02-01 00:02:30'); +-- test roundoff edge case when source < origin +SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00'); + -- disallow intervals with months or years SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01'); SELECT date_bin('5 years'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01'); @@ -278,6 +281,11 @@ SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp -- disallow negative intervals SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00'); +-- test overflow cases +select date_bin('15 minutes'::interval, timestamp '294276-12-30', timestamp '4000-12-20 BC'); +select date_bin('200000000 days'::interval, '2024-02-01'::timestamp, '2024-01-01'::timestamp); +select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamp, '4000-01-01 BC'::timestamp); + -- Test casting within a BETWEEN qualifier SELECT d1 - timestamp without time zone '1997-01-02' AS diff FROM TIMESTAMP_TBL diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index 69b36d0..60cd841 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -243,6 +243,9 @@ FROM ( -- shift bins using the origin parameter: SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2020-02-01 00:02:30+00'); +-- test roundoff edge case when source < origin +SELECT date_bin('30 minutes'::interval, timestamptz '2024-02-01 15:00:00', timestamptz '2024-02-01 17:00:00'); + -- disallow intervals with months or years SELECT date_bin('5 months'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00'); SELECT date_bin('5 years'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00'); @@ -253,6 +256,11 @@ SELECT date_bin('0 days'::interval, timestamp with time zone '1970-01-01 01:00:0 -- disallow negative intervals SELECT date_bin('-2 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00'); +-- test overflow cases +select date_bin('15 minutes'::interval, timestamptz '294276-12-30', timestamptz '4000-12-20 BC'); +select date_bin('200000000 days'::interval, '2024-02-01'::timestamptz, '2024-01-01'::timestamptz); +select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamptz, '4000-01-01 BC'::timestamptz); + -- Test casting within a BETWEEN qualifier SELECT d1 - timestamp with time zone '1997-01-02' AS diff FROM TIMESTAMPTZ_TBL diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 46795a9..6c9e066 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1186,9 +1186,13 @@ insert into child values (10, 1, 'b'); select * from parent; select * from child; update parent set val1 = 'b' where aid = 1; -- should fail +merge into parent p using (values (1)) as v(id) on p.aid = v.id + when matched then update set val1 = 'b'; -- should fail select * from parent; select * from child; delete from parent where aid = 1; -- should fail +merge into parent p using (values (1)) as v(id) on p.aid = v.id + when matched then delete; -- should fail select * from parent; select * from child; -- replace the trigger function with one that restarts the deletion after diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index c0ad51c..e35c16a 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -1120,14 +1120,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, salary, - count(empno) OVER (ORDER BY salary DESC) c + count(1) OVER (ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; SELECT * FROM (SELECT empno, salary, - count(empno) OVER (ORDER BY salary DESC) c + count(1) OVER (ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; @@ -1183,13 +1183,13 @@ SELECT empno, depname FROM FROM empsalary) emp WHERE rn < 3; --- likewise with count(empno) instead of row_number() +-- likewise with count(1) instead of row_number() EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c + count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; @@ -1198,7 +1198,7 @@ SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c + count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; @@ -1209,7 +1209,7 @@ SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER () c + count(1) OVER () c FROM empsalary) emp WHERE c = 1; @@ -1217,7 +1217,7 @@ WHERE c = 1; EXPLAIN (COSTS OFF) SELECT * FROM (SELECT *, - count(salary) OVER (PARTITION BY depname || '') c1, -- w1 + count(1) OVER (PARTITION BY depname || '') c1, -- w1 row_number() OVER (PARTITION BY depname) rn, -- w2 count(*) OVER (PARTITION BY depname) c2, -- w2 count(*) OVER (PARTITION BY '' || depname) c3, -- w3 @@ -1228,7 +1228,7 @@ SELECT * FROM -- Ensure we correctly filter out all of the run conditions from each window SELECT * FROM (SELECT *, - count(salary) OVER (PARTITION BY depname || '') c1, -- w1 + count(1) OVER (PARTITION BY depname || '') c1, -- w1 row_number() OVER (PARTITION BY depname) rn, -- w2 count(*) OVER (PARTITION BY depname) c2, -- w2 count(*) OVER (PARTITION BY '' || depname) c3, -- w3 @@ -1236,15 +1236,6 @@ SELECT * FROM FROM empsalary ) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2; --- Ensure we remove references to reduced outer joins as nulling rels in run --- conditions -EXPLAIN (COSTS OFF) -SELECT 1 FROM - (SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c - FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE - WHERE e1.empno = e2.empno) s -WHERE s.c = 1; - -- Tests to ensure we don't push down the run condition when it's not valid to -- do so. @@ -1278,6 +1269,24 @@ SELECT * FROM FROM empsalary) emp WHERE c = 1; +-- Ensure we don't use a run condition when the WindowFunc arg contains a Var +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + count(empno) OVER (ORDER BY empno DESC) c + FROM empsalary) emp +WHERE c = 1; + +-- As above but with ntile(). +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + ntile(empno::int) OVER (ORDER BY empno DESC) nt + FROM empsalary) emp +WHERE nt = 1; + -- Ensure we don't use a run condition when the WindowFunc contains subplans EXPLAIN (COSTS OFF) SELECT * FROM diff --git a/src/test/subscription/t/031_column_list.pl b/src/test/subscription/t/031_column_list.pl index dbff806..b817d33 100644 --- a/src/test/subscription/t/031_column_list.pl +++ b/src/test/subscription/t/031_column_list.pl @@ -370,7 +370,8 @@ $node_subscriber->safe_psql( $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub2, pub3 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub2, pub3 )); $node_subscriber->wait_for_subscription_sync($node_publisher, 'sub1'); @@ -411,7 +412,8 @@ $node_subscriber->safe_psql( $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub4 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub4 )); $node_subscriber->wait_for_subscription_sync; @@ -487,7 +489,8 @@ $node_subscriber->safe_psql( $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub5 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub5 )); $node_subscriber->wait_for_subscription_sync; @@ -601,10 +604,12 @@ $node_publisher->safe_psql( ALTER PUBLICATION pub6 ADD TABLE test_part_a_2 (b); )); -# add the publication to our subscription, wait for sync to complete +# create the subscription for the above publication, wait for sync to +# complete $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub6 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub6 )); $node_subscriber->wait_for_subscription_sync; @@ -667,10 +672,12 @@ $node_publisher->safe_psql( CREATE PUBLICATION pub7 FOR TABLE test_part_b (a, b) WITH (publish_via_partition_root = true); )); -# add the publication to our subscription, wait for sync to complete +# create the subscription for the above publication, wait for sync to +# complete $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub7 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub7 )); $node_subscriber->wait_for_subscription_sync; @@ -737,7 +744,8 @@ $node_publisher->safe_psql( ALTER PUBLICATION pub8 ADD TABLE test_part_c_2 (a,b); )); -# add the publication to our subscription, wait for sync to complete +# create the subscription for the above publication, wait for sync to +# complete $node_subscriber->safe_psql( 'postgres', qq( DROP SUBSCRIPTION sub1; @@ -835,10 +843,12 @@ $node_publisher->safe_psql( CREATE PUBLICATION pub9 FOR TABLE test_part_d (a) WITH (publish_via_partition_root = true); )); -# add the publication to our subscription, wait for sync to complete +# create the subscription for the above publication, wait for sync to +# complete $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub9 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub9 )); $node_subscriber->wait_for_subscription_sync; @@ -880,8 +890,8 @@ $node_publisher->safe_psql( $node_subscriber->safe_psql( 'postgres', qq( CREATE TABLE test_mix_2 (a int PRIMARY KEY, b int, c int); - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub_mix_3, pub_mix_4; - ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION; + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub_mix_3, pub_mix_4; )); $node_subscriber->wait_for_subscription_sync; @@ -1022,7 +1032,8 @@ $node_subscriber->safe_psql( CREATE TABLE s1.t (a int, b int, c int) PARTITION BY RANGE (a); CREATE TABLE t_1 PARTITION OF s1.t FOR VALUES FROM (1) TO (10); - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub1, pub2; + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub1, pub2; )); $node_subscriber->wait_for_subscription_sync; @@ -1090,7 +1101,8 @@ $node_subscriber->safe_psql( PARTITION BY RANGE (a); CREATE TABLE t_2 PARTITION OF t_1 FOR VALUES FROM (1) TO (10); - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub3; + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub3; )); $node_subscriber->wait_for_subscription_sync; @@ -1138,7 +1150,8 @@ $node_subscriber->safe_psql( PARTITION BY RANGE (a); CREATE TABLE t_2 PARTITION OF t_1 FOR VALUES FROM (1) TO (10); - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub4; + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub4; )); $node_subscriber->wait_for_subscription_sync; |