Allow publications with schema and table of the same schema.

We previously thought that allowing such cases can confuse users when they
specify DROP TABLES IN SCHEMA but that doesn't seem to be the case based
on discussion. This helps to uplift the restriction during
ALTER TABLE ... SET SCHEMA which used to ensure that we couldn't end up
with a publication having both a schema and the same schema's table.

To allow this, we need to forbid having any schema on a publication if
column lists on a table are specified (and vice versa). This is because
otherwise we still need a restriction during ALTER TABLE ... SET SCHEMA to
forbid cases where it could lead to a publication having both a schema and
the same schema's table with column list.

Based on suggestions by Peter Eisentraut.

Author: Hou Zhijie and Vignesh C
Reviewed-By: Peter Smith, Amit Kapila
Backpatch-through: 15, where it was introduced
Discussion: https://postgr.es/m/2729c9e2-9aac-8cda-f2f4-34f2bcc18f4e@enterprisedb.com

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)"