summaryrefslogtreecommitdiffstats
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/isolation/expected/merge-update.out43
-rw-r--r--src/test/isolation/specs/merge-update.spec13
-rw-r--r--src/test/kerberos/t/001_auth.pl7
-rw-r--r--src/test/ldap/t/001_auth.pl88
-rw-r--r--src/test/recovery/t/008_fsm_truncation.pl5
-rw-r--r--src/test/regress/expected/create_procedure.out14
-rw-r--r--src/test/regress/expected/foreign_data.out29
-rw-r--r--src/test/regress/expected/horology.out4
-rw-r--r--src/test/regress/expected/identity.out72
-rw-r--r--src/test/regress/expected/index_including.out25
-rw-r--r--src/test/regress/expected/insert.out116
-rw-r--r--src/test/regress/expected/join.out31
-rw-r--r--src/test/regress/expected/memoize.out31
-rw-r--r--src/test/regress/expected/merge.out60
-rw-r--r--src/test/regress/expected/partition_prune.out107
-rw-r--r--src/test/regress/expected/plpgsql.out8
-rw-r--r--src/test/regress/expected/rangefuncs.out16
-rw-r--r--src/test/regress/expected/rules.out8
-rw-r--r--src/test/regress/expected/stats_ext.out43
-rw-r--r--src/test/regress/expected/timestamp.out14
-rw-r--r--src/test/regress/expected/timestamptz.out14
-rw-r--r--src/test/regress/expected/triggers.out8
-rw-r--r--src/test/regress/expected/window.out50
-rw-r--r--src/test/regress/sql/create_procedure.sql11
-rw-r--r--src/test/regress/sql/foreign_data.sql2
-rw-r--r--src/test/regress/sql/horology.sql2
-rw-r--r--src/test/regress/sql/identity.sql18
-rw-r--r--src/test/regress/sql/index_including.sql19
-rw-r--r--src/test/regress/sql/insert.sql79
-rw-r--r--src/test/regress/sql/join.sql19
-rw-r--r--src/test/regress/sql/memoize.sql23
-rw-r--r--src/test/regress/sql/merge.sql28
-rw-r--r--src/test/regress/sql/partition_prune.sql30
-rw-r--r--src/test/regress/sql/plpgsql.sql8
-rw-r--r--src/test/regress/sql/rangefuncs.sql10
-rw-r--r--src/test/regress/sql/stats_ext.sql27
-rw-r--r--src/test/regress/sql/timestamp.sql8
-rw-r--r--src/test/regress/sql/timestamptz.sql8
-rw-r--r--src/test/regress/sql/triggers.sql4
-rw-r--r--src/test/regress/sql/window.sql25
-rw-r--r--src/test/subscription/t/031_column_list.pl43
41 files changed, 1068 insertions, 102 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 64117c2..f2253e5 100644
--- a/src/test/kerberos/t/001_auth.pl
+++ b/src/test/kerberos/t/001_auth.pl
@@ -184,7 +184,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/t/001_auth.pl b/src/test/ldap/t/001_auth.pl
index 2a7435e..3dd56e9 100644
--- a/src/test/ldap/t/001_auth.pl
+++ b/src/test/ldap/t/001_auth.pl
@@ -16,44 +16,77 @@ if ($ENV{with_ldap} ne 'yes')
{
plan skip_all => 'LDAP not supported by this build';
}
-elsif ($^O eq 'darwin' && -d '/opt/homebrew/opt/openldap')
+# Find the OpenLDAP server binary and directory containing schema
+# definition files.
+elsif ($^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
+ {
+ plan skip_all => "OpenLDAP server installation not found";
+ }
}
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
+ {
+ plan skip_all => "OpenLDAP server installation not found";
+ }
}
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
+ {
+ plan skip_all => "OpenLDAP server installation not found";
+ }
}
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
+ {
+ plan skip_all => "OpenLDAP server installation not found";
+ }
}
else
{
- plan skip_all =>
- "ldap tests not supported on $^O or dependencies not installed";
+ plan skip_all => "ldap tests not supported on $^O";
}
# make your own edits here
@@ -130,7 +163,12 @@ system_or_bail $slapd, '-f', $slapd_conf,'-s0', '-h', "$ldap_url $ldaps_url";
END
{
+ # take care not to change the script's exit value
+ my $exit_code = $?;
+
kill 'INT', `cat $slapd_pidfile` if -f $slapd_pidfile;
+
+ $? = $exit_code;
}
append_to_file($ldap_pwfile, $ldap_rootpw);
diff --git a/src/test/recovery/t/008_fsm_truncation.pl b/src/test/recovery/t/008_fsm_truncation.pl
index 5be2153..251f43a 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-2022, 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/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out
index 46c827f..f3eabc0 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 3350535..25e7163 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -894,24 +894,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 2dc9b00..7353529 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -375,6 +375,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
------------------
@@ -637,6 +639,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/join.out b/src/test/regress/expected/join.out
index 867c6d2..b356153 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5949,6 +5949,37 @@ select * from
3 | 3
(6 rows)
+-- check for generation of join EC conditions at wrong level (bug #18429)
+explain (costs off)
+select * from (
+ select arrayd.ad, coalesce(c.hundred, 0) as h
+ from unnest(array[1]) as arrayd(ad)
+ left join lateral (
+ select hundred from tenk1 where unique2 = arrayd.ad
+ ) c on true
+) c2
+where c2.h * c2.ad = c2.h * (c2.ad + 1);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ Filter: ((COALESCE(tenk1.hundred, 0) * arrayd.ad) = (COALESCE(tenk1.hundred, 0) * (arrayd.ad + 1)))
+ -> Function Scan on unnest arrayd
+ -> Index Scan using tenk1_unique2 on tenk1
+ Index Cond: (unique2 = arrayd.ad)
+(5 rows)
+
+select * from (
+ select arrayd.ad, coalesce(c.hundred, 0) as h
+ from unnest(array[1]) as arrayd(ad)
+ left join lateral (
+ select hundred from tenk1 where unique2 = arrayd.ad
+ ) c on true
+) c2
+where c2.h * c2.ad = c2.h * (c2.ad + 1);
+ ad | h
+----+---
+(0 rows)
+
-- check the number of columns specified
SELECT * FROM (int8_tbl i cross join int4_tbl j) ss(a,b,c,d);
ERROR: join expression "ss" has 3 columns available but 4 columns specified
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 60cbdee..bde091a 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 edc0043..63a829e 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) AS s
+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
@@ -2244,3 +2303,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 c5bc6c3..fa1f2f2 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 08e42f1..78d3bc0 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 e2e62db..6b16e75 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 225204c..0a1e17e 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2453,10 +2453,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,
@@ -2529,7 +2526,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 67cae4a..69e5704 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -3266,10 +3266,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 79f8180..2ee8712 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -699,6 +699,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
@@ -710,6 +717,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 eba8419..790cae1 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -743,6 +743,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
@@ -754,6 +761,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 723e8b7..4dd95e2 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 1f23baa..f8914c0 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -3481,13 +3481,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
@@ -3496,7 +3496,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
@@ -3608,19 +3608,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
@@ -3631,7 +3631,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
@@ -3653,13 +3653,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)
@@ -3667,7 +3667,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
@@ -3678,7 +3678,7 @@ SELECT * FROM
Subquery Scan on e
-> WindowAgg
Filter: ((row_number() OVER (?)) <= 1)
- Run Condition: (count(empsalary.salary) OVER (?) <= 3)
+ Run Condition: (count(1) OVER (?) <= 3)
-> Sort
Sort Key: (((empsalary.depname)::text || ''::text))
-> WindowAgg
@@ -3694,7 +3694,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
@@ -3765,6 +3765,24 @@ 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)
+
-- Ensure we don't use a run condition when the WindowFunc contains subplans
EXPLAIN (COSTS OFF)
SELECT * FROM
diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql
index 75cc0fc..50a4d88 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 4065348..7d6153a 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -87,6 +87,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
@@ -119,6 +120,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/join.sql b/src/test/regress/sql/join.sql
index 1113e98..11a8570 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2029,6 +2029,25 @@ select * from
(select q1.v)
) as q2;
+-- check for generation of join EC conditions at wrong level (bug #18429)
+explain (costs off)
+select * from (
+ select arrayd.ad, coalesce(c.hundred, 0) as h
+ from unnest(array[1]) as arrayd(ad)
+ left join lateral (
+ select hundred from tenk1 where unique2 = arrayd.ad
+ ) c on true
+) c2
+where c2.h * c2.ad = c2.h * (c2.ad + 1);
+select * from (
+ select arrayd.ad, coalesce(c.hundred, 0) as h
+ from unnest(array[1]) as arrayd(ad)
+ left join lateral (
+ select hundred from tenk1 where unique2 = arrayd.ad
+ ) c on true
+) c2
+where c2.h * c2.ad = c2.h * (c2.ad + 1);
+
-- check the number of columns specified
SELECT * FROM (int8_tbl i cross join int4_tbl j) ss(a,b,c,d);
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index d66acae..7b02a82 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..f6b8b9f 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) AS s
+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 ff7e535..6977efd 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 588c331..cfff49c 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 f0ee415..76291ef 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -1649,9 +1649,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 ebc969f..bc37803 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -259,6 +259,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');
@@ -269,6 +272,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 a107abc..875479e 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -234,6 +234,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');
@@ -244,6 +247,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 253c1b7..2a5aa38 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1068,14 +1068,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;
@@ -1131,13 +1131,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;
@@ -1146,7 +1146,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;
@@ -1157,7 +1157,7 @@ SELECT * FROM
(SELECT empno,
depname,
salary,
- count(empno) OVER () c
+ count(1) OVER () c
FROM empsalary) emp
WHERE c = 1;
@@ -1165,7 +1165,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
@@ -1175,7 +1175,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
@@ -1215,6 +1215,15 @@ 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;
+
-- 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 2ca120f..86521a1 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;
@@ -1017,7 +1027,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;
@@ -1085,7 +1096,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;
@@ -1133,7 +1145,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;