diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index c5d8915be8..73aa9435fb 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/sql/publication.sql b/src/test/regress/sql/publication.sql index 0c4f1a8edb..e6a52959c0 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -51,12 +51,35 @@ 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; + +CREATE PUBLICATION testpub_fortable FOR TABLE testpub_tbl1; +-- 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; + +CREATE PUBLICATION testpub_forschema FOR SCHEMA pub_test; +-- 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); diff --git a/src/test/subscription/t/001_rep_changes.pl b/src/test/subscription/t/001_rep_changes.pl index 736fc16487..2cc84a3635 100644 --- a/src/test/subscription/t/001_rep_changes.pl +++ b/src/test/subscription/t/001_rep_changes.pl @@ -325,8 +325,8 @@ $result = $node_subscriber->safe_psql('postgres', 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->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'); @@ -339,8 +339,8 @@ 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->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"); @@ -355,7 +355,7 @@ $node_subscriber->safe_psql('postgres', $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->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"); @@ -363,8 +363,8 @@ 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->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"); @@ -374,7 +374,7 @@ is($result, qq(11|1|11), 'check rows on subscriber catchup'); $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 was dropped on subscriber'); + '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', @@ -384,7 +384,7 @@ is($result, qq(4), # 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->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')"); @@ -403,7 +403,7 @@ is($result, qq(3), # 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->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"); @@ -412,7 +412,7 @@ 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 publications as we don't need them anymore +# Drop publication as we don't need it anymore $node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_schema"); # Clean up the tables on both publisher and subscriber as we don't need them