summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/alter_table.out14
-rw-r--r--src/test/regress/expected/publication.out90
-rw-r--r--src/test/regress/sql/alter_table.sql3
-rw-r--r--src/test/regress/sql/publication.sql44
-rw-r--r--src/test/subscription/t/028_row_filter.pl2
5 files changed, 131 insertions, 22 deletions
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 656dac6801f..346f594ad02 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -4595,10 +4595,16 @@ create table alter1.t1 (a int);
set client_min_messages = 'ERROR';
create publication pub1 for table alter1.t1, tables in schema alter2;
reset client_min_messages;
-alter table alter1.t1 set schema alter2; -- should fail
-ERROR: cannot move table "t1" to schema "alter2"
-DETAIL: The schema "alter2" and same schema's table "t1" cannot be part of the same publication "pub1".
+alter table alter1.t1 set schema alter2;
+\d+ alter2.t1
+ Table "alter2.t1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Publications:
+ "pub1"
+
drop publication pub1;
drop schema alter1 cascade;
-NOTICE: drop cascades to table alter1.t1
drop schema alter2 cascade;
+NOTICE: drop cascades to table alter2.t1
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index ce63511b94e..c0c0e7d92e0 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -118,15 +118,42 @@ Tables from schemas:
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
-RESET client_min_messages;
--- fail - can't create publication with schema and table of the same schema
+-- should be able to create publication with schema and table of the same
+-- schema
CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk;
-ERROR: cannot add relation "pub_test.testpub_nopk" to publication
-DETAIL: Table's schema "pub_test" is already part of the publication or part of the specified schema list.
--- fail - can't add a table of the same schema to the schema publication
+RESET client_min_messages;
+\dRp+ testpub_for_tbl_schema
+ Publication testpub_for_tbl_schema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "pub_test.testpub_nopk"
+Tables from schemas:
+ "pub_test"
+
+-- should be able to add a table of the same schema to the schema publication
ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk;
-ERROR: cannot add relation "pub_test.testpub_nopk" to publication
-DETAIL: Table's schema "pub_test" is already part of the publication or part of the specified schema list.
+\dRp+ testpub_forschema
+ Publication testpub_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "pub_test.testpub_nopk"
+Tables from schemas:
+ "pub_test"
+
+-- should be able to drop the table
+ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk;
+\dRp+ testpub_forschema
+ Publication testpub_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables from schemas:
+ "pub_test"
+
-- fail - can't drop a table from the schema publication which isn't in the
-- publication
ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk;
@@ -166,7 +193,7 @@ Publications:
(1 row)
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema;
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
SET client_min_messages = 'ERROR';
@@ -466,10 +493,19 @@ ERROR: cannot use a WHERE clause when removing a table from a publication
-- fail - cannot ALTER SET table which is a member of a pre-existing schema
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2;
+-- should be able to set publication with schema and table of the same schema
ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99);
-ERROR: cannot add relation "testpub_rf_schema2.testpub_rf_tbl6" to publication
-DETAIL: Table's schema "testpub_rf_schema2" is already part of the publication or part of the specified schema list.
RESET client_min_messages;
+\dRp+ testpub6
+ Publication testpub6
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "testpub_rf_schema2.testpub_rf_tbl6" WHERE (i < 99)
+Tables from schemas:
+ "testpub_rf_schema2"
+
DROP TABLE testpub_rf_tbl1;
DROP TABLE testpub_rf_tbl2;
DROP TABLE testpub_rf_tbl3;
@@ -812,8 +848,40 @@ ALTER TABLE testpub_tbl8_0 REPLICA IDENTITY FULL;
UPDATE testpub_tbl8 SET a = 1;
ERROR: cannot update table "testpub_tbl8_0"
DETAIL: Column list used by the publication does not cover the replica identity.
+-- test that using column list for table is disallowed if any schemas are
+-- part of the publication
+SET client_min_messages = 'ERROR';
+-- failure - cannot use column list and schema together
+CREATE PUBLICATION testpub_tbl9 FOR TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a);
+ERROR: cannot use publication column list for relation "public.testpub_tbl7"
+DETAIL: Column list cannot be specified if any schema is part of the publication or specified in the list.
+-- ok - only publish schema
+CREATE PUBLICATION testpub_tbl9 FOR TABLES IN SCHEMA public;
+-- failure - add a table with column list when there is already a schema in the
+-- publication
+ALTER PUBLICATION testpub_tbl9 ADD TABLE public.testpub_tbl7(a);
+ERROR: cannot use publication column list for relation "public.testpub_tbl7"
+DETAIL: Column list cannot be specified if any schema is part of the publication or specified in the list.
+-- ok - only publish table with column list
+ALTER PUBLICATION testpub_tbl9 SET TABLE public.testpub_tbl7(a);
+-- failure - specify a schema when there is already a column list in the
+-- publication
+ALTER PUBLICATION testpub_tbl9 ADD TABLES IN SCHEMA public;
+ERROR: cannot add schema to the publication
+DETAIL: Schema cannot be added if any table that specifies column list is already part of the publication.
+-- failure - cannot SET column list and schema together
+ALTER PUBLICATION testpub_tbl9 SET TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a);
+ERROR: cannot use publication column list for relation "public.testpub_tbl7"
+DETAIL: Column list cannot be specified if any schema is part of the publication or specified in the list.
+-- ok - drop table
+ALTER PUBLICATION testpub_tbl9 DROP TABLE public.testpub_tbl7;
+-- failure - cannot ADD column list and schema together
+ALTER PUBLICATION testpub_tbl9 ADD TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a);
+ERROR: cannot use publication column list for relation "public.testpub_tbl7"
+DETAIL: Column list cannot be specified if any schema is part of the publication or specified in the list.
+RESET client_min_messages;
DROP TABLE testpub_tbl5, testpub_tbl6, testpub_tbl7, testpub_tbl8, testpub_tbl8_1;
-DROP PUBLICATION testpub_table_ins, testpub_fortable, testpub_fortable_insert, testpub_col_list;
+DROP PUBLICATION testpub_table_ins, testpub_fortable, testpub_fortable_insert, testpub_col_list, testpub_tbl9;
-- ======================================================
-- Test combination of column list and row filter
SET client_min_messages = 'ERROR';
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 8846c14dbb2..9f773aeeb94 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -3031,7 +3031,8 @@ create table alter1.t1 (a int);
set client_min_messages = 'ERROR';
create publication pub1 for table alter1.t1, tables in schema alter2;
reset client_min_messages;
-alter table alter1.t1 set schema alter2; -- should fail
+alter table alter1.t1 set schema alter2;
+\d+ alter2.t1
drop publication pub1;
drop schema alter1 cascade;
drop schema alter2 cascade;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 231c9d5c53c..a47c5939d58 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -73,11 +73,20 @@ ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
-RESET client_min_messages;
--- fail - can't create publication with schema and table of the same schema
+-- should be able to create publication with schema and table of the same
+-- schema
CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk;
--- fail - can't add a table of the same schema to the schema publication
+RESET client_min_messages;
+\dRp+ testpub_for_tbl_schema
+
+-- should be able to add a table of the same schema to the schema publication
ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk;
+\dRp+ testpub_forschema
+
+-- should be able to drop the table
+ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk;
+\dRp+ testpub_forschema
+
-- fail - can't drop a table from the schema publication which isn't in the
-- publication
ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk;
@@ -90,7 +99,7 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
\dRp+ testpub_foralltables
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema;
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
@@ -242,8 +251,10 @@ ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl1 WHERE (e < 27);
-- fail - cannot ALTER SET table which is a member of a pre-existing schema
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2;
+-- should be able to set publication with schema and table of the same schema
ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99);
RESET client_min_messages;
+\dRp+ testpub6
DROP TABLE testpub_rf_tbl1;
DROP TABLE testpub_rf_tbl2;
@@ -525,8 +536,31 @@ UPDATE testpub_tbl8 SET a = 1;
ALTER TABLE testpub_tbl8_0 REPLICA IDENTITY FULL;
UPDATE testpub_tbl8 SET a = 1;
+-- test that using column list for table is disallowed if any schemas are
+-- part of the publication
+SET client_min_messages = 'ERROR';
+-- failure - cannot use column list and schema together
+CREATE PUBLICATION testpub_tbl9 FOR TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a);
+-- ok - only publish schema
+CREATE PUBLICATION testpub_tbl9 FOR TABLES IN SCHEMA public;
+-- failure - add a table with column list when there is already a schema in the
+-- publication
+ALTER PUBLICATION testpub_tbl9 ADD TABLE public.testpub_tbl7(a);
+-- ok - only publish table with column list
+ALTER PUBLICATION testpub_tbl9 SET TABLE public.testpub_tbl7(a);
+-- failure - specify a schema when there is already a column list in the
+-- publication
+ALTER PUBLICATION testpub_tbl9 ADD TABLES IN SCHEMA public;
+-- failure - cannot SET column list and schema together
+ALTER PUBLICATION testpub_tbl9 SET TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a);
+-- ok - drop table
+ALTER PUBLICATION testpub_tbl9 DROP TABLE public.testpub_tbl7;
+-- failure - cannot ADD column list and schema together
+ALTER PUBLICATION testpub_tbl9 ADD TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a);
+RESET client_min_messages;
+
DROP TABLE testpub_tbl5, testpub_tbl6, testpub_tbl7, testpub_tbl8, testpub_tbl8_1;
-DROP PUBLICATION testpub_table_ins, testpub_fortable, testpub_fortable_insert, testpub_col_list;
+DROP PUBLICATION testpub_table_ins, testpub_fortable, testpub_fortable_insert, testpub_col_list, testpub_tbl9;
-- ======================================================
-- Test combination of column list and row filter
diff --git a/src/test/subscription/t/028_row_filter.pl b/src/test/subscription/t/028_row_filter.pl
index ba07ed37b77..da52289dde8 100644
--- a/src/test/subscription/t/028_row_filter.pl
+++ b/src/test/subscription/t/028_row_filter.pl
@@ -119,7 +119,7 @@ $node_publisher->safe_psql('postgres',
"CREATE PUBLICATION tap_pub_x FOR TABLE schema_rf_x.tab_rf_x WHERE (x > 10)"
);
$node_publisher->safe_psql('postgres',
- "CREATE PUBLICATION tap_pub_allinschema FOR TABLES IN SCHEMA schema_rf_x"
+ "CREATE PUBLICATION tap_pub_allinschema FOR TABLES IN SCHEMA schema_rf_x, TABLE schema_rf_x.tab_rf_x WHERE (x > 10)"
);
$node_publisher->safe_psql('postgres',
"ALTER PUBLICATION tap_pub_allinschema ADD TABLE public.tab_rf_partition WHERE (x > 10)"
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-14 22:35:19 +0000