From 3207b25d652f118460fc8b86ba7e8dcf9ea833ec Mon Sep 17 00:00:00 2001 From: Vignesh C Date: Mon, 2 Aug 2021 16:45:34 +0530 Subject: [PATCH v19 2/2] Tests and documentation for schema level support for publication. Tests and documentation for schema level support for publication. --- doc/src/sgml/catalogs.sgml | 94 ++++- doc/src/sgml/ref/alter_publication.sgml | 45 ++- doc/src/sgml/ref/create_publication.sgml | 44 ++- src/bin/pg_dump/t/002_pg_dump.pl | 30 ++ src/test/regress/expected/object_address.out | 6 +- src/test/regress/expected/publication.out | 363 ++++++++++++++++++- src/test/regress/sql/object_address.sql | 3 + src/test/regress/sql/publication.sql | 163 ++++++++- src/test/subscription/t/001_rep_changes.pl | 150 +++++++- 9 files changed, 880 insertions(+), 18 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 2b2c70a26e..f5a185a328 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -245,6 +245,11 @@ relation to publication mapping + + pg_publication_schema + schema to publication mapping + + pg_range information about range types @@ -6169,6 +6174,28 @@ SCRAM-SHA-256$<iteration count>:&l publication instead of its own. + + + + pubkind char + + + Publication kind: + a = FOR ALL TABLES publication kind, + t = FOR TABLE publication kind, + s = FOR SCHEMA publication kind, + e = Empty publication kind. + If a publication is created without specifying any of + FOR ALL TABLES, FOR TABLE or + FOR SCHEMA option, then the publication will be + created as an empty publication kind. When a table or schema is added to + the publication using + ALTER PUBLICATION then the publication kind + will be changed to t or s + respectively. The publication kind cannot be changed in other cases. + + + @@ -6236,6 +6263,67 @@ SCRAM-SHA-256$<iteration count>:&l + + <structname>pg_publication_schema</structname> + + + pg_publication_schema + + + + The catalog pg_publication_schema contains the + mapping between schemas and publications in the database. This is a + many-to-many mapping. + + + + <structname>pg_publication_schema</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + pspubid oid + (references pg_publication.oid) + + + Reference to publication + + + + + + psnspcid oid + (references pg_namespace.oid) + + + Reference to schema + + + + +
+ + <structname>pg_range</structname> @@ -11276,9 +11364,9 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx information about the mapping between publications and the tables they contain. Unlike the underlying catalog pg_publication_rel, - this view expands - publications defined as FOR ALL TABLES, so for such - publications there will be a row for each eligible table. + this view expands publications defined as FOR ALL TABLES + and FOR SCHEMA, so for such publications there will be a + row for each eligible table. diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml index faa114b2c6..c05029b9a6 100644 --- a/doc/src/sgml/ref/alter_publication.sgml +++ b/doc/src/sgml/ref/alter_publication.sgml @@ -24,6 +24,9 @@ PostgreSQL documentation ALTER PUBLICATION name ADD TABLE [ ONLY ] table_name [ * ] [, ...] ALTER PUBLICATION name SET TABLE [ ONLY ] table_name [ * ] [, ...] ALTER PUBLICATION name DROP TABLE [ ONLY ] table_name [ * ] [, ...] +ALTER PUBLICATION name ADD SCHEMA { schema_name | CURRENT_SCHEMA } [, ...] +ALTER PUBLICATION name SET SCHEMA { schema_name | CURRENT_SCHEMA } [, ...] +ALTER PUBLICATION name DROP SCHEMA { schema_name | CURRENT_SCHEMA } [, ...] ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] ) ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ALTER PUBLICATION name RENAME TO new_name @@ -50,7 +53,18 @@ ALTER PUBLICATION name RENAME TO - The fourth variant of this command listed in the synopsis can change + The fourth, fifth and sixth variants of this command change which schemas + are part of the publication. The SET SCHEMA clause will + replace the list of schemas in the publication with the specified one. + The ADD SCHEMA and DROP SCHEMA clauses + will add and remove one or more schemas from the publication. Note that + adding schemas to a publication that is already subscribed to will require + a ALTER SUBSCRIPTION ... REFRESH PUBLICATION action on + the subscribing side in order to become effective. + + + + The seventh variant of this command listed in the synopsis can change all of the publication properties specified in . Properties not mentioned in the command retain their previous settings. @@ -97,6 +111,15 @@ ALTER PUBLICATION name RENAME TO + + schema_name + + + Name of an existing schema. + + + + SET ( publication_parameter [= value] [, ... ] ) @@ -141,6 +164,26 @@ ALTER PUBLICATION noinsert SET (publish = 'update, delete'); Add some tables to the publication: ALTER PUBLICATION mypublication ADD TABLE users, departments; + + + + Add some schemas to the publication: + +ALTER PUBLICATION sales_publication ADD SCHEMA marketing_june, sales_june; + + + + + Drop some schema from the publication: + +ALTER PUBLICATION production_quarterly_publication DROP SCHEMA production_july; + + + + + Set schema to the publication: + +ALTER PUBLICATION production_publication SET SCHEMA production_july; diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index ff82fbca55..39628e4fa6 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -22,8 +22,9 @@ PostgreSQL documentation CREATE PUBLICATION name - [ FOR TABLE [ ONLY ] table_name [ * ] [, ...] - | FOR ALL TABLES ] + [ FOR TABLE [ ONLY ] table_name [ * ] [, ... ] + | FOR SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ] + | FOR ALL TABLES [ WITH ( publication_parameter [= value] [, ... ] ) ] @@ -99,6 +100,16 @@ CREATE PUBLICATION name + + FOR SCHEMA + + + Marks the publication as one that replicates changes for the all tables in + the specified list of schemas, including tables created in the future. + + + + WITH ( publication_parameter [= value] [, ... ] ) @@ -153,9 +164,10 @@ CREATE PUBLICATION name Notes - If neither FOR TABLE nor FOR ALL - TABLES is specified, then the publication starts out with an - empty set of tables. That is useful if tables are to be added later. + If FOR TABLE, FOR ALL TABLES or + FOR SCHEMA is not specified, then the publication starts + out with an empty set of tables. That is useful if tables or schemas are to + be added later. @@ -170,9 +182,9 @@ CREATE PUBLICATION name - To add a table to a publication, the invoking user must have ownership - rights on the table. The FOR ALL TABLES clause requires - the invoking user to be a superuser. + To add a table/schema to a publication, the invoking user must have + ownership rights on the table/schema. The FOR ALL TABLES + clause requires the invoking user to be a superuser. @@ -222,6 +234,22 @@ CREATE PUBLICATION alltables FOR ALL TABLES; CREATE PUBLICATION insert_only FOR TABLE mydata WITH (publish = 'insert'); + + + + + Create a publication that publishes all changes for all the tables present in + the schema "production": + +CREATE PUBLICATION production_publication FOR SCHEMA production; + + + + + Create a publication that publishes all changes for all the tables present in + the schemas "marketing" and "sales": + +CREATE PUBLICATION sales_publication FOR SCHEMA marketing, sales; diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index a4ee54d516..fcdd277b83 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -2301,6 +2301,15 @@ my %tests = ( like => { %full_runs, section_post_data => 1, }, }, + 'CREATE PUBLICATION pub3' => { + create_order => 50, + create_sql => 'CREATE PUBLICATION pub3;', + regexp => qr/^ + \QCREATE PUBLICATION pub3 WITH (publish = 'insert, update, delete, truncate');\E + /xm, + like => { %full_runs, section_post_data => 1, }, + }, + 'CREATE SUBSCRIPTION sub1' => { create_order => 50, create_sql => 'CREATE SUBSCRIPTION sub1 @@ -2337,6 +2346,27 @@ my %tests = ( unlike => { exclude_dump_test_schema => 1, }, }, + 'ALTER PUBLICATION pub3 ADD SCHEMA dump_test' => { + create_order => 51, + create_sql => + 'ALTER PUBLICATION pub3 ADD SCHEMA dump_test;', + regexp => qr/^ + \QALTER PUBLICATION pub3 ADD SCHEMA dump_test;\E + /xm, + like => { %full_runs, section_post_data => 1, }, + unlike => { exclude_dump_test_schema => 1, }, + }, + + 'ALTER PUBLICATION pub3 ADD SCHEMA public' => { + create_order => 52, + create_sql => + 'ALTER PUBLICATION pub3 ADD SCHEMA public;', + regexp => qr/^ + \QALTER PUBLICATION pub3 ADD SCHEMA public;\E + /xm, + like => { %full_runs, section_post_data => 1, }, + }, + 'CREATE SCHEMA public' => { regexp => qr/^CREATE SCHEMA public;/m, diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out index 388097a695..49ea22f427 100644 --- a/src/test/regress/expected/object_address.out +++ b/src/test/regress/expected/object_address.out @@ -45,6 +45,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL ( -- suppress warning that depends on wal_level SET client_min_messages = 'ERROR'; CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable; +CREATE PUBLICATION addr_pub_schema FOR SCHEMA addr_nsp; RESET client_min_messages; CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE); WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables @@ -428,6 +429,7 @@ WITH objects (type, name, args) AS (VALUES ('access method', '{btree}', '{}'), ('publication', '{addr_pub}', '{}'), ('publication relation', '{addr_nsp, gentable}', '{addr_pub}'), + ('publication schema', '{addr_nsp}', '{addr_pub_schema}'), ('subscription', '{regress_addr_sub}', '{}'), ('statistics object', '{addr_nsp, gentable_stat}', '{}') ) @@ -490,7 +492,8 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*, subscription | | regress_addr_sub | regress_addr_sub | t publication | | addr_pub | addr_pub | t publication relation | | | addr_nsp.gentable in publication addr_pub | t -(49 rows) + publication schema | | | addr_nsp in publication addr_pub_schema | t +(50 rows) --- --- Cleanup resources @@ -502,6 +505,7 @@ drop cascades to foreign table genftable drop cascades to server integer drop cascades to user mapping for regress_addr_user on server integer DROP PUBLICATION addr_pub; +DROP PUBLICATION addr_pub_schema; DROP SUBSCRIPTION regress_addr_sub; DROP SCHEMA addr_nsp CASCADE; NOTICE: drop cascades to 14 other objects diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index ae42aba1bf..8c99d31ef8 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -69,6 +69,48 @@ DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. +-- fail - can't add schema to for all tables publication +ALTER PUBLICATION testpub_foralltables ADD SCHEMA pub_test; +ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES +DETAIL: Schemas cannot be added to or dropped from FOR ALL TABLES publications. +-- fail - can't drop schema from all tables publication +ALTER PUBLICATION testpub_foralltables DROP SCHEMA pub_test; +ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES +DETAIL: Schemas cannot be added to or dropped from FOR ALL TABLES publications. +-- fail - can't set schema to for all tables publication +ALTER PUBLICATION testpub_foralltables SET SCHEMA pub_test; +ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES +DETAIL: Schemas cannot be added to or dropped from FOR ALL TABLES publications. +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_fortable FOR TABLE testpub_tbl1; +RESET client_min_messages; +-- fail - can't add schema to for table publication +ALTER PUBLICATION testpub_fortable ADD SCHEMA pub_test; +ERROR: publication "testpub_fortable" is defined as FOR TABLE +DETAIL: Schemas cannot be added to or dropped from FOR TABLE publications. +-- fail - can't drop schema from table publication +ALTER PUBLICATION testpub_fortable DROP SCHEMA pub_test; +ERROR: publication "testpub_fortable" is defined as FOR TABLE +DETAIL: Schemas cannot be added to or dropped from FOR TABLE publications. +-- fail - can't set schema to for table publication +ALTER PUBLICATION testpub_fortable SET SCHEMA pub_test; +ERROR: publication "testpub_fortable" is defined as FOR TABLE +DETAIL: Schemas cannot be added to or dropped from FOR TABLE publications. +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_forschema FOR SCHEMA pub_test; +RESET client_min_messages; +-- fail - can't add table to for schema publication +ALTER PUBLICATION testpub_forschema ADD TABLE pub_test2.tbl1; +ERROR: publication "testpub_forschema" is defined as FOR SCHEMA +DETAIL: Tables cannot be added to or dropped from FOR SCHEMA publications. +-- fail - can't drop table from schema publication +ALTER PUBLICATION testpub_forschema DROP TABLE pub_test1.tbl1; +ERROR: publication "testpub_forschema" is defined as FOR SCHEMA +DETAIL: Tables cannot be added to or dropped from FOR SCHEMA publications. +-- fail - can't set table to schema publication +ALTER PUBLICATION testpub_forschema SET TABLE pub_test1.tbl1; +ERROR: publication "testpub_forschema" is defined as FOR SCHEMA +DETAIL: Tables cannot be added to or dropped from FOR SCHEMA publications. SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables'; pubname | puballtables ----------------------+-------------- @@ -94,7 +136,7 @@ Publications: (1 row) DROP TABLE testpub_tbl2; -DROP PUBLICATION testpub_foralltables; +DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema; CREATE TABLE testpub_tbl3 (a int); CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3); SET client_min_messages = 'ERROR'; @@ -246,18 +288,22 @@ GRANT CREATE ON DATABASE regression TO regress_publication_user2; SET ROLE regress_publication_user2; SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub2; -- ok +CREATE PUBLICATION testpub3; -- ok RESET client_min_messages; ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail ERROR: must be owner of table testpub_tbl1 +ALTER PUBLICATION testpub3 ADD SCHEMA pub_test; -- fail +ERROR: must be owner of schema pub_test SET ROLE regress_publication_user; GRANT regress_publication_user TO regress_publication_user2; SET ROLE regress_publication_user2; ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok +ALTER PUBLICATION testpub3 ADD SCHEMA pub_test; -- ok DROP PUBLICATION testpub2; +DROP PUBLICATION testpub3; SET ROLE regress_publication_user; REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; DROP TABLE testpub_parted; -DROP VIEW testpub_view; DROP TABLE testpub_tbl1; \dRp+ testpub_default Publication testpub_default @@ -289,11 +335,324 @@ ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; testpub_default | regress_publication_user2 | f | t | t | t | f | f | t (1 row) +-- adding schemas and tables +CREATE SCHEMA pub_test1; +CREATE SCHEMA pub_test2; +CREATE SCHEMA pub_test3; +CREATE TABLE pub_test1.tbl1 (id serial primary key, data text); +CREATE TABLE pub_test2.tbl1 (id serial primary key, data text); +-- suppress warning that depends on wal_level +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub1_forschema FOR SCHEMA pub_test1; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + +SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test1' ORDER BY 1; + pubname +-------------------- + testpub1_forschema +(1 row) + +CREATE PUBLICATION testpub2_forschema FOR SCHEMA pub_test1, pub_test2, pub_test3; +\dRp+ testpub2_forschema + Publication testpub2_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + "pub_test3" + +SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test1' ORDER BY 1; + pubname +-------------------- + testpub1_forschema + testpub2_forschema +(2 rows) + +SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test2' ORDER BY 1; + pubname +-------------------- + testpub2_forschema +(1 row) + +SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test3' ORDER BY 1; + pubname +-------------------- + testpub2_forschema +(1 row) + +-- check create publication on CURRENT_SCHEMA +CREATE PUBLICATION testpub3_forschema FOR SCHEMA CURRENT_SCHEMA; +RESET client_min_messages; +\dRp+ testpub3_forschema + Publication testpub3_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "public" + +SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'public' ORDER BY 1; + pubname +-------------------- + testpub3_forschema +(1 row) + +-- check create publication on CURRENT_SCHEMA where search_path is not set +SET SEARCH_PATH=''; +CREATE PUBLICATION testpub_forschema FOR SCHEMA CURRENT_SCHEMA; +ERROR: no schema has been selected +RESET SEARCH_PATH; +-- check create publication on a schema that does not exist +CREATE PUBLICATION testpub_forschema FOR SCHEMA non_existent_schema; +ERROR: schema "non_existent_schema" does not exist +-- check create publication on a system schema +CREATE PUBLICATION testpub_forschema FOR SCHEMA pg_catalog; +ERROR: "pg_catalog" is a system schema +DETAIL: System schemas cannot be added to publications. +-- check create publication on an object which is not schema +CREATE PUBLICATION testpub1_forschema1 FOR SCHEMA testpub_view; +ERROR: schema "testpub_view" does not exist +-- dropping the schema should reflect the change in publication +DROP SCHEMA pub_test3; +\dRp+ testpub2_forschema + Publication testpub2_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + +-- renaming the schema should reflect the change in publication +ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed; +\dRp+ testpub2_forschema + Publication testpub2_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1_renamed" + "pub_test2" + +ALTER SCHEMA pub_test1_renamed RENAME to pub_test1; +\dRp+ testpub2_forschema + Publication testpub2_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + +-- alter publication add schema +ALTER PUBLICATION testpub1_forschema ADD SCHEMA pub_test2; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + +-- alter publication add CURRENT_SCHEMA +ALTER PUBLICATION testpub1_forschema ADD SCHEMA CURRENT_SCHEMA; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + "public" + +-- add non existent schema +ALTER PUBLICATION testpub1_forschema ADD SCHEMA non_existent_schema; +ERROR: schema "non_existent_schema" does not exist +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + "public" + +-- add a schema which is already added to the publication +ALTER PUBLICATION testpub1_forschema ADD SCHEMA pub_test1; +ERROR: schema "pub_test1" is already member of publication "testpub1_forschema" +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + "public" + +-- alter publication drop CURRENT_SCHEMA +ALTER PUBLICATION testpub1_forschema DROP SCHEMA CURRENT_SCHEMA; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + +-- alter publication drop schema +ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test2; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + +-- drop schema that is not present in the publication +ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test2; +ERROR: schema "pub_test2" is not part of the publication +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + +-- drop a schema that does not exist in the system +ALTER PUBLICATION testpub1_forschema DROP SCHEMA non_existent_schema; +ERROR: schema "non_existent_schema" does not exist +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + +-- drop all schemas +ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test1; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +(1 row) + +-- alter publication set schema +ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + +-- alter publication set multiple schema +ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test2; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + +-- alter publication set non-existent schema +ALTER PUBLICATION testpub1_forschema SET SCHEMA non_existent_schema; +ERROR: schema "non_existent_schema" does not exist +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + +-- alter publication set it with the same schema +ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test2; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + "pub_test2" + +-- alter publication set it duplicate schemas should set the schemas after +-- removing the duplicate schemas +ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test1; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + +-- alter publication set it with CURRENT_SCHEMA +ALTER PUBLICATION testpub1_forschema SET SCHEMA CURRENT_SCHEMA; +\dRp+ testpub1_forschema + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "public" + +-- alter publication set schema should change the publication type from e to s +-- while altering an empty publication. +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub4_forschema; +RESET client_min_messages; +\dRp+ testpub4_forschema + Publication testpub4_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | e +(1 row) + +ALTER PUBLICATION testpub4_forschema SET SCHEMA pub_test1; +\dRp+ testpub4_forschema + Publication testpub4_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubkind +--------------------------+------------+---------+---------+---------+-----------+----------+--------- + regress_publication_user | f | t | t | t | t | f | s +Schemas: + "pub_test1" + +DROP VIEW testpub_view; DROP PUBLICATION testpub_default; DROP PUBLICATION testpib_ins_trunct; DROP PUBLICATION testpub_fortbl; +DROP PUBLICATION testpub1_forschema; +DROP PUBLICATION testpub2_forschema; +DROP PUBLICATION testpub3_forschema; +DROP PUBLICATION testpub4_forschema; DROP SCHEMA pub_test CASCADE; NOTICE: drop cascades to table pub_test.testpub_nopk +DROP SCHEMA pub_test1 CASCADE; +NOTICE: drop cascades to table pub_test1.tbl1 +DROP SCHEMA pub_test2 CASCADE; +NOTICE: drop cascades to table pub_test2.tbl1 RESET SESSION AUTHORIZATION; DROP ROLE regress_publication_user, regress_publication_user2; DROP ROLE regress_publication_user_dummy; diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql index 2f4f66e3e1..56d9b852fd 100644 --- a/src/test/regress/sql/object_address.sql +++ b/src/test/regress/sql/object_address.sql @@ -48,6 +48,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL ( -- suppress warning that depends on wal_level SET client_min_messages = 'ERROR'; CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable; +CREATE PUBLICATION addr_pub_schema FOR SCHEMA addr_nsp; RESET client_min_messages; CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE); CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable; @@ -198,6 +199,7 @@ WITH objects (type, name, args) AS (VALUES ('access method', '{btree}', '{}'), ('publication', '{addr_pub}', '{}'), ('publication relation', '{addr_nsp, gentable}', '{addr_pub}'), + ('publication schema', '{addr_nsp}', '{addr_pub_schema}'), ('subscription', '{regress_addr_sub}', '{}'), ('statistics object', '{addr_nsp, gentable_stat}', '{}') ) @@ -215,6 +217,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*, --- DROP FOREIGN DATA WRAPPER addr_fdw CASCADE; DROP PUBLICATION addr_pub; +DROP PUBLICATION addr_pub_schema; DROP SUBSCRIPTION regress_addr_sub; DROP SCHEMA addr_nsp CASCADE; diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index d844075368..d67d387559 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -51,12 +51,39 @@ ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2; -- fail - can't add to for all tables publication ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk; +-- fail - can't add schema to for all tables publication +ALTER PUBLICATION testpub_foralltables ADD SCHEMA pub_test; +-- fail - can't drop schema from all tables publication +ALTER PUBLICATION testpub_foralltables DROP SCHEMA pub_test; +-- fail - can't set schema to for all tables publication +ALTER PUBLICATION testpub_foralltables SET SCHEMA pub_test; + +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_fortable FOR TABLE testpub_tbl1; +RESET client_min_messages; +-- fail - can't add schema to for table publication +ALTER PUBLICATION testpub_fortable ADD SCHEMA pub_test; +-- fail - can't drop schema from table publication +ALTER PUBLICATION testpub_fortable DROP SCHEMA pub_test; +-- fail - can't set schema to for table publication +ALTER PUBLICATION testpub_fortable SET SCHEMA pub_test; + +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_forschema FOR SCHEMA pub_test; +RESET client_min_messages; +-- fail - can't add table to for schema publication +ALTER PUBLICATION testpub_forschema ADD TABLE pub_test2.tbl1; +-- fail - can't drop table from schema publication +ALTER PUBLICATION testpub_forschema DROP TABLE pub_test1.tbl1; +-- fail - can't set table to schema publication +ALTER PUBLICATION testpub_forschema SET TABLE pub_test1.tbl1; + SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables'; \d+ testpub_tbl2 \dRp+ testpub_foralltables DROP TABLE testpub_tbl2; -DROP PUBLICATION testpub_foralltables; +DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema; CREATE TABLE testpub_tbl3 (a int); CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3); @@ -133,22 +160,25 @@ GRANT CREATE ON DATABASE regression TO regress_publication_user2; SET ROLE regress_publication_user2; SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub2; -- ok +CREATE PUBLICATION testpub3; -- ok RESET client_min_messages; ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail +ALTER PUBLICATION testpub3 ADD SCHEMA pub_test; -- fail SET ROLE regress_publication_user; GRANT regress_publication_user TO regress_publication_user2; SET ROLE regress_publication_user2; ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok +ALTER PUBLICATION testpub3 ADD SCHEMA pub_test; -- ok DROP PUBLICATION testpub2; +DROP PUBLICATION testpub3; SET ROLE regress_publication_user; REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; DROP TABLE testpub_parted; -DROP VIEW testpub_view; DROP TABLE testpub_tbl1; \dRp+ testpub_default @@ -169,11 +199,140 @@ ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; \dRp testpub_default +-- adding schemas and tables +CREATE SCHEMA pub_test1; +CREATE SCHEMA pub_test2; +CREATE SCHEMA pub_test3; +CREATE TABLE pub_test1.tbl1 (id serial primary key, data text); +CREATE TABLE pub_test2.tbl1 (id serial primary key, data text); + +-- suppress warning that depends on wal_level +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub1_forschema FOR SCHEMA pub_test1; +\dRp+ testpub1_forschema +SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test1' ORDER BY 1; + +CREATE PUBLICATION testpub2_forschema FOR SCHEMA pub_test1, pub_test2, pub_test3; +\dRp+ testpub2_forschema +SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test1' ORDER BY 1; +SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test2' ORDER BY 1; +SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test3' ORDER BY 1; + +-- check create publication on CURRENT_SCHEMA +CREATE PUBLICATION testpub3_forschema FOR SCHEMA CURRENT_SCHEMA; +RESET client_min_messages; + +\dRp+ testpub3_forschema +SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'public' ORDER BY 1; + +-- check create publication on CURRENT_SCHEMA where search_path is not set +SET SEARCH_PATH=''; +CREATE PUBLICATION testpub_forschema FOR SCHEMA CURRENT_SCHEMA; +RESET SEARCH_PATH; + +-- check create publication on a schema that does not exist +CREATE PUBLICATION testpub_forschema FOR SCHEMA non_existent_schema; + +-- check create publication on a system schema +CREATE PUBLICATION testpub_forschema FOR SCHEMA pg_catalog; + +-- check create publication on an object which is not schema +CREATE PUBLICATION testpub1_forschema1 FOR SCHEMA testpub_view; + +-- dropping the schema should reflect the change in publication +DROP SCHEMA pub_test3; +\dRp+ testpub2_forschema + +-- renaming the schema should reflect the change in publication +ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed; +\dRp+ testpub2_forschema + +ALTER SCHEMA pub_test1_renamed RENAME to pub_test1; +\dRp+ testpub2_forschema + +-- alter publication add schema +ALTER PUBLICATION testpub1_forschema ADD SCHEMA pub_test2; +\dRp+ testpub1_forschema + +-- alter publication add CURRENT_SCHEMA +ALTER PUBLICATION testpub1_forschema ADD SCHEMA CURRENT_SCHEMA; +\dRp+ testpub1_forschema + +-- add non existent schema +ALTER PUBLICATION testpub1_forschema ADD SCHEMA non_existent_schema; +\dRp+ testpub1_forschema + +-- add a schema which is already added to the publication +ALTER PUBLICATION testpub1_forschema ADD SCHEMA pub_test1; +\dRp+ testpub1_forschema + +-- alter publication drop CURRENT_SCHEMA +ALTER PUBLICATION testpub1_forschema DROP SCHEMA CURRENT_SCHEMA; +\dRp+ testpub1_forschema + +-- alter publication drop schema +ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test2; +\dRp+ testpub1_forschema + +-- drop schema that is not present in the publication +ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test2; +\dRp+ testpub1_forschema + +-- drop a schema that does not exist in the system +ALTER PUBLICATION testpub1_forschema DROP SCHEMA non_existent_schema; +\dRp+ testpub1_forschema + +-- drop all schemas +ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test1; +\dRp+ testpub1_forschema + +-- alter publication set schema +ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1; +\dRp+ testpub1_forschema + +-- alter publication set multiple schema +ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test2; +\dRp+ testpub1_forschema + +-- alter publication set non-existent schema +ALTER PUBLICATION testpub1_forschema SET SCHEMA non_existent_schema; +\dRp+ testpub1_forschema + +-- alter publication set it with the same schema +ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test2; +\dRp+ testpub1_forschema + +-- alter publication set it duplicate schemas should set the schemas after +-- removing the duplicate schemas +ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test1; +\dRp+ testpub1_forschema + +-- alter publication set it with CURRENT_SCHEMA +ALTER PUBLICATION testpub1_forschema SET SCHEMA CURRENT_SCHEMA; +\dRp+ testpub1_forschema + +-- alter publication set schema should change the publication type from e to s +-- while altering an empty publication. +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub4_forschema; +RESET client_min_messages; +\dRp+ testpub4_forschema +ALTER PUBLICATION testpub4_forschema SET SCHEMA pub_test1; +\dRp+ testpub4_forschema + +DROP VIEW testpub_view; + DROP PUBLICATION testpub_default; DROP PUBLICATION testpib_ins_trunct; DROP PUBLICATION testpub_fortbl; +DROP PUBLICATION testpub1_forschema; +DROP PUBLICATION testpub2_forschema; +DROP PUBLICATION testpub3_forschema; +DROP PUBLICATION testpub4_forschema; DROP SCHEMA pub_test CASCADE; +DROP SCHEMA pub_test1 CASCADE; +DROP SCHEMA pub_test2 CASCADE; RESET SESSION AUTHORIZATION; DROP ROLE regress_publication_user, regress_publication_user2; diff --git a/src/test/subscription/t/001_rep_changes.pl b/src/test/subscription/t/001_rep_changes.pl index 0c84d87873..556af52275 100644 --- a/src/test/subscription/t/001_rep_changes.pl +++ b/src/test/subscription/t/001_rep_changes.pl @@ -6,7 +6,7 @@ use strict; use warnings; use PostgresNode; use TestLib; -use Test::More tests => 32; +use Test::More tests => 46; # Initialize publisher node my $node_publisher = PostgresNode->new('publisher'); @@ -275,6 +275,154 @@ $node_publisher->safe_psql('postgres', "DROP TABLE temp2"); $node_subscriber->safe_psql('postgres', "DROP TABLE temp1"); $node_subscriber->safe_psql('postgres', "DROP TABLE temp2"); +# Test replication with publications created using FOR SCHEMA option. +# Create schemas and tables on publisher +$node_publisher->safe_psql('postgres', "CREATE SCHEMA sch1"); +$node_publisher->safe_psql('postgres', "CREATE SCHEMA sch2"); +$node_publisher->safe_psql('postgres', "CREATE SCHEMA sch3"); +$node_publisher->safe_psql('postgres', "CREATE TABLE sch1.tab1 AS SELECT generate_series(1,10) AS a"); +$node_publisher->safe_psql('postgres', "CREATE TABLE sch1.tab2 AS SELECT generate_series(1,10) AS a"); +$node_publisher->safe_psql('postgres', "CREATE TABLE sch2.tab1 AS SELECT generate_series(1,10) AS a"); +$node_publisher->safe_psql('postgres', "CREATE TABLE sch2.tab2 AS SELECT generate_series(1,10) AS a"); + +# Create schemas and tables on subscriber +$node_subscriber->safe_psql('postgres', "CREATE SCHEMA sch1"); +$node_subscriber->safe_psql('postgres', "CREATE SCHEMA sch2"); +$node_subscriber->safe_psql('postgres', "CREATE SCHEMA sch3"); +$node_subscriber->safe_psql('postgres', "CREATE TABLE sch1.tab1 (a int)"); +$node_subscriber->safe_psql('postgres', "CREATE TABLE sch1.tab2 (a int)"); +$node_subscriber->safe_psql('postgres', "CREATE TABLE sch2.tab1 (a int)"); +$node_subscriber->safe_psql('postgres', "CREATE TABLE sch2.tab2 (a int)"); + +# Setup logical replication for schema sch1 and sch2 that will only be used for +# this test +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION tap_pub_schema FOR SCHEMA sch1,sch2"); +$node_subscriber->safe_psql('postgres', + "CREATE SUBSCRIPTION tap_sub_schema CONNECTION '$publisher_connstr' PUBLICATION tap_pub_schema" + ); + +$node_publisher->wait_for_catchup('tap_sub_schema'); + +# Also wait for initial table sync to finish +$synced_query = + "SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's');"; +$node_subscriber->poll_query_until('postgres', $synced_query) + or die "Timed out while waiting for subscriber to synchronize data"; + +# Check the schema table data is synced up. +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*), min(a), max(a) FROM sch1.tab1"); +is($result, qq(10|1|10), 'check rows on subscriber catchup'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*), min(a), max(a) FROM sch1.tab2"); +is($result, qq(10|1|10), 'check rows on subscriber catchup'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*), min(a), max(a) FROM sch2.tab1"); +is($result, qq(10|1|10), 'check rows on subscriber catchup'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*), min(a), max(a) FROM sch2.tab2"); +is($result, qq(10|1|10), 'check rows on subscriber catchup'); + +# Insert some data into few tables and verify that inserted data is replicated. +$node_publisher->safe_psql('postgres', "INSERT INTO sch1.tab1 VALUES(generate_series(11,20))"); +$node_publisher->safe_psql('postgres', "INSERT INTO sch2.tab1 VALUES(generate_series(11,20))"); + +$node_publisher->wait_for_catchup('tap_sub_schema'); + +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*), min(a), max(a) FROM sch1.tab1"); +is($result, qq(20|1|20), 'check rows on subscriber catchup'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*), min(a), max(a) FROM sch2.tab1"); +is($result, qq(20|1|20), 'check rows on subscriber catchup'); + +# Create new table in the publication schema, verify that subscriber does not get +# the new table data in the subscriber before refresh. +$node_publisher->safe_psql('postgres', "CREATE TABLE sch1.tab3 AS SELECT generate_series(1,10) AS a"); +$node_subscriber->safe_psql('postgres', "CREATE TABLE sch1.tab3(a int)"); +$node_publisher->wait_for_catchup('tap_sub_schema'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM sch1.tab3"); +is($result, qq(0), 'check rows on subscriber catchup'); + +# Table data should be reflected after refreshing the publication in +# subscriber. +$node_subscriber->safe_psql('postgres', + "ALTER SUBSCRIPTION tap_sub_schema REFRESH PUBLICATION"); + +# Also wait for initial table sync to finish +$node_subscriber->poll_query_until('postgres', $synced_query) + or die "Timed out while waiting for subscriber to synchronize data"; + +$node_publisher->safe_psql('postgres', "INSERT INTO sch1.tab3 VALUES(11)"); +$node_publisher->wait_for_catchup('tap_sub_schema'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*), min(a), max(a) FROM sch1.tab3"); +is($result, qq(11|1|11), 'check rows on subscriber catchup'); + +# Set the schema of a publication schema table to a non publication schema and +# verify that inserted data is not reflected by the subscriber. +$node_publisher->safe_psql('postgres', "ALTER TABLE sch1.tab3 SET SCHEMA sch3"); +$node_publisher->safe_psql('postgres', "INSERT INTO sch3.tab3 VALUES(11)"); +$node_publisher->wait_for_catchup('tap_sub_schema'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*), min(a), max(a) FROM sch1.tab3"); +is($result, qq(11|1|11), 'check rows on subscriber catchup'); + +# Verify that the subscription relation list is updated after refresh. +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM pg_subscription_rel WHERE srsubid IN (SELECT oid FROM pg_subscription WHERE subname = 'tap_sub_schema')"); +is($result, qq(5), + 'check subscription relation status is not yet dropped on subscriber'); +$node_subscriber->safe_psql('postgres', + "ALTER SUBSCRIPTION tap_sub_schema REFRESH PUBLICATION"); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM pg_subscription_rel WHERE srsubid IN (SELECT oid FROM pg_subscription WHERE subname = 'tap_sub_schema')"); +is($result, qq(4), + 'check subscription relation status was dropped on subscriber'); + +# Drop table from the publication schema, verify that subscriber removes the +# table entry after refresh. +$node_publisher->safe_psql('postgres', "DROP TABLE sch1.tab2"); +$node_publisher->wait_for_catchup('tap_sub_schema'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM pg_subscription_rel WHERE srsubid IN (SELECT oid FROM pg_subscription WHERE subname = 'tap_sub_schema')"); +is($result, qq(4), + 'check subscription relation status is not yet dropped on subscriber'); + +# Table should be removed from pg_subscription_rel after refreshing the +# publication in subscriber. +$node_subscriber->safe_psql('postgres', + "ALTER SUBSCRIPTION tap_sub_schema REFRESH PUBLICATION"); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM pg_subscription_rel WHERE srsubid IN (SELECT oid FROM pg_subscription WHERE subname = 'tap_sub_schema')"); +is($result, qq(3), + 'check subscription relation status was dropped on subscriber'); + +# Drop schema from publication, verify that the inserts are not published after +# dropping the schema from publication. Here 2nd insert should not be +# published. +$node_publisher->safe_psql('postgres', "INSERT INTO sch2.tab1 VALUES(21); ALTER PUBLICATION tap_pub_schema DROP SCHEMA sch2; INSERT INTO sch2.tab1 values(22)"); +$node_publisher->wait_for_catchup('tap_sub_schema'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT count(*), min(a), max(a) FROM sch2.tab1"); +is($result, qq(21|1|21), 'check rows on subscriber catchup'); + +# Drop subscription as we don't need it anymore +$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_schema"); + +# Drop publication as we don't need it anymore +$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_schema"); + +# Clean up the schemas on both publisher and subscriber as we don't need them +$node_publisher->safe_psql('postgres', "DROP SCHEMA sch1 cascade"); +$node_publisher->safe_psql('postgres', "DROP SCHEMA sch2 cascade"); +$node_publisher->safe_psql('postgres', "DROP SCHEMA sch3 cascade"); +$node_subscriber->safe_psql('postgres', "DROP SCHEMA sch1 cascade"); +$node_subscriber->safe_psql('postgres', "DROP SCHEMA sch2 cascade"); +$node_subscriber->safe_psql('postgres', "DROP SCHEMA sch3 cascade"); + # add REPLICA IDENTITY FULL so we can update $node_publisher->safe_psql('postgres', "ALTER TABLE tab_full REPLICA IDENTITY FULL"); -- 2.30.2