Rework the options syntax for logical replication commands

For CREATE/ALTER PUBLICATION/SUBSCRIPTION, use similar option style as
other statements that use a WITH clause for options.

Author: Petr Jelinek <petr.jelinek@2ndquadrant.com>

12 files changed, 51 insertions, 42 deletions

diff --git a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
index 5f3768137c2..77bdc9345d1 100644
--- a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
+++ b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
@@ -69,7 +69,7 @@ CREATE SCHEMA dummy_seclabel_test;
 SECURITY LABEL ON SCHEMA dummy_seclabel_test IS 'unclassified';		-- OK
 SET client_min_messages = error;
 CREATE PUBLICATION dummy_pub;
-CREATE SUBSCRIPTION dummy_sub CONNECTION '' PUBLICATION foo WITH (NOCONNECT, SLOT NAME = NONE);
+CREATE SUBSCRIPTION dummy_sub CONNECTION '' PUBLICATION foo WITH (connect = false, slot_name = NONE);
 RESET client_min_messages;
 SECURITY LABEL ON PUBLICATION dummy_pub IS 'classified';
 SECURITY LABEL ON SUBSCRIPTION dummy_sub IS 'classified';
diff --git a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
index 97311c79711..8c347b6a68b 100644
--- a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
+++ b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
@@ -73,7 +73,7 @@ SECURITY LABEL ON SCHEMA dummy_seclabel_test IS 'unclassified';		-- OK
 
 SET client_min_messages = error;
 CREATE PUBLICATION dummy_pub;
-CREATE SUBSCRIPTION dummy_sub CONNECTION '' PUBLICATION foo WITH (NOCONNECT, SLOT NAME = NONE);
+CREATE SUBSCRIPTION dummy_sub CONNECTION '' PUBLICATION foo WITH (connect = false, slot_name = NONE);
 RESET client_min_messages;
 SECURITY LABEL ON PUBLICATION dummy_pub IS 'classified';
 SECURITY LABEL ON SUBSCRIPTION dummy_sub IS 'classified';
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 40eeeed3d25..700f2618271 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -37,7 +37,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
 	FROM SQL WITH FUNCTION varchar_transform(internal),
 	TO SQL WITH FUNCTION int4recv(internal));
 CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
-CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (NOCONNECT, SLOT NAME = NONE);
+CREATE SUBSCRIPTION 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
 CREATE STATISTICS addr_nsp.gentable_stat ON (a,b) FROM addr_nsp.gentable;
 -- test some error cases
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index f3a348d368c..e81919fd8cb 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -13,8 +13,13 @@ SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
  test publication
 (1 row)
 
-CREATE PUBLICATION testpib_ins_trunct WITH (nopublish delete, nopublish update);
-ALTER PUBLICATION testpub_default WITH (nopublish insert, nopublish delete);
+CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert);
+ALTER PUBLICATION testpub_default SET (publish = update);
+-- error cases
+CREATE PUBLICATION testpub_xxx WITH (foo);
+ERROR:  unrecognized publication parameter: foo
+CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum');
+ERROR:  unrecognized "publish" value: "cluster"
 \dRp
                             List of publications
         Name        |          Owner           | Inserts | Updates | Deletes 
@@ -23,7 +28,7 @@ ALTER PUBLICATION testpub_default WITH (nopublish insert, nopublish delete);
  testpub_default    | regress_publication_user | f       | t       | f
 (2 rows)
 
-ALTER PUBLICATION testpub_default WITH (publish insert, publish delete);
+ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete');
 \dRp
                             List of publications
         Name        |          Owner           | Inserts | Updates | Deletes 
@@ -38,8 +43,8 @@ CREATE TABLE testpub_tbl1 (id serial primary key, data text);
 CREATE TABLE pub_test.testpub_nopk (foo int, bar int);
 CREATE VIEW testpub_view AS SELECT 1;
 CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a);
-CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (nopublish delete, nopublish update);
-ALTER PUBLICATION testpub_foralltables WITH (publish update);
+CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert');
+ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update');
 CREATE TABLE testpub_tbl2 (id serial primary key, data text);
 -- fail - can't add to for all tables publication
 ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2;
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 56f826ba5c0..10c3644e51d 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -17,18 +17,18 @@ LINE 1: CREATE SUBSCRIPTION testsub PUBLICATION foo;
                                     ^
 -- fail - cannot do CREATE SUBSCRIPTION CREATE SLOT inside transaction block
 BEGIN;
-CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub WITH (CREATE SLOT);
-ERROR:  CREATE SUBSCRIPTION ... CREATE SLOT cannot run inside a transaction block
+CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub WITH (create_slot);
+ERROR:  CREATE SUBSCRIPTION ... WITH (create_slot = true) cannot run inside a transaction block
 COMMIT;
 -- fail - invalid connection string
 CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub;
 ERROR:  invalid connection string syntax: missing "=" after "testconn" in connection info string
 
 -- fail - duplicate publications
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, testpub, foo WITH (NOCONNECT);
+CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, testpub, foo WITH (connect = false);
 ERROR:  publication name "foo" used more than once
 -- ok
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (NOCONNECT);
+CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false);
 WARNING:  tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
 COMMENT ON SUBSCRIPTION testsub IS 'test subscription';
 SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
@@ -38,11 +38,11 @@ SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
 (1 row)
 
 -- fail - name already exists
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (NOCONNECT);
+CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false);
 ERROR:  subscription "testsub" already exists
 -- fail - must be superuser
 SET SESSION AUTHORIZATION 'regress_subscription_user2';
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION foo WITH (NOCONNECT);
+CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION foo WITH (connect = false);
 ERROR:  must be superuser to create subscriptions
 SET SESSION AUTHORIZATION 'regress_subscription_user';
 -- fail - invalid connection string
@@ -56,9 +56,9 @@ ERROR:  invalid connection string syntax: missing "=" after "foobar" in connecti
  testsub | regress_subscription_user | f       | {testpub}   | off                | dbname=doesnotexist
 (1 row)
 
-ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3 NOREFRESH;
+ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3 SKIP REFRESH;
 ALTER SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist2';
-ALTER SUBSCRIPTION testsub WITH (SLOT NAME = 'newname');
+ALTER SUBSCRIPTION testsub SET (slot_name = 'newname');
 -- fail
 ALTER SUBSCRIPTION doesnotexist CONNECTION 'dbname=doesnotexist2';
 ERROR:  subscription "doesnotexist" does not exist
@@ -93,8 +93,8 @@ ALTER SUBSCRIPTION testsub RENAME TO testsub_dummy;
 ERROR:  must be owner of subscription testsub
 RESET ROLE;
 ALTER SUBSCRIPTION testsub RENAME TO testsub_foo;
-ALTER SUBSCRIPTION testsub_foo WITH (SYNCHRONOUS_COMMIT = local);
-ALTER SUBSCRIPTION testsub_foo WITH (SYNCHRONOUS_COMMIT = foobar);
+ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = local);
+ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = foobar);
 ERROR:  invalid value for parameter "synchronous_commit": "foobar"
 HINT:  Available values: local, remote_write, remote_apply, on, off.
 \dRs+
@@ -118,7 +118,7 @@ BEGIN;
 DROP SUBSCRIPTION testsub;
 ERROR:  DROP SUBSCRIPTION cannot run inside a transaction block
 COMMIT;
-ALTER SUBSCRIPTION testsub WITH (SLOT NAME = NONE);
+ALTER SUBSCRIPTION testsub SET (slot_name = NONE);
 -- now it works
 BEGIN;
 DROP SUBSCRIPTION testsub;
diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql
index 6940392c018..8a738e20d68 100644
--- a/src/test/regress/sql/object_address.sql
+++ b/src/test/regress/sql/object_address.sql
@@ -40,7 +40,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
 	FROM SQL WITH FUNCTION varchar_transform(internal),
 	TO SQL WITH FUNCTION int4recv(internal));
 CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
-CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (NOCONNECT, SLOT NAME = NONE);
+CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
 CREATE STATISTICS addr_nsp.gentable_stat ON (a,b) FROM addr_nsp.gentable;
 
 -- test some error cases
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 7d1cba5db3e..cc1f33e72cd 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -11,13 +11,17 @@ CREATE PUBLICATION testpub_default;
 COMMENT ON PUBLICATION testpub_default IS 'test publication';
 SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
 
-CREATE PUBLICATION testpib_ins_trunct WITH (nopublish delete, nopublish update);
+CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert);
 
-ALTER PUBLICATION testpub_default WITH (nopublish insert, nopublish delete);
+ALTER PUBLICATION testpub_default SET (publish = update);
+
+-- error cases
+CREATE PUBLICATION testpub_xxx WITH (foo);
+CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum');
 
 \dRp
 
-ALTER PUBLICATION testpub_default WITH (publish insert, publish delete);
+ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete');
 
 \dRp
 
@@ -28,8 +32,8 @@ CREATE TABLE pub_test.testpub_nopk (foo int, bar int);
 CREATE VIEW testpub_view AS SELECT 1;
 CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a);
 
-CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (nopublish delete, nopublish update);
-ALTER PUBLICATION testpub_foralltables WITH (publish update);
+CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert');
+ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update');
 
 CREATE TABLE testpub_tbl2 (id serial primary key, data text);
 -- fail - can't add to for all tables publication
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index b9204460a44..798bb0dbd31 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -15,27 +15,27 @@ CREATE SUBSCRIPTION testsub PUBLICATION foo;
 
 -- fail - cannot do CREATE SUBSCRIPTION CREATE SLOT inside transaction block
 BEGIN;
-CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub WITH (CREATE SLOT);
+CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub WITH (create_slot);
 COMMIT;
 
 -- fail - invalid connection string
 CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub;
 
 -- fail - duplicate publications
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, testpub, foo WITH (NOCONNECT);
+CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, testpub, foo WITH (connect = false);
 
 -- ok
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (NOCONNECT);
+CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false);
 
 COMMENT ON SUBSCRIPTION testsub IS 'test subscription';
 SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
 
 -- fail - name already exists
-CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (NOCONNECT);
+CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false);
 
 -- fail - must be superuser
 SET SESSION AUTHORIZATION 'regress_subscription_user2';
-CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION foo WITH (NOCONNECT);
+CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION foo WITH (connect = false);
 SET SESSION AUTHORIZATION 'regress_subscription_user';
 
 -- fail - invalid connection string
@@ -43,9 +43,9 @@ ALTER SUBSCRIPTION testsub CONNECTION 'foobar';
 
 \dRs+
 
-ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3 NOREFRESH;
+ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3 SKIP REFRESH;
 ALTER SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist2';
-ALTER SUBSCRIPTION testsub WITH (SLOT NAME = 'newname');
+ALTER SUBSCRIPTION testsub SET (slot_name = 'newname');
 
 -- fail
 ALTER SUBSCRIPTION doesnotexist CONNECTION 'dbname=doesnotexist2';
@@ -69,8 +69,8 @@ ALTER SUBSCRIPTION testsub RENAME TO testsub_dummy;
 RESET ROLE;
 
 ALTER SUBSCRIPTION testsub RENAME TO testsub_foo;
-ALTER SUBSCRIPTION testsub_foo WITH (SYNCHRONOUS_COMMIT = local);
-ALTER SUBSCRIPTION testsub_foo WITH (SYNCHRONOUS_COMMIT = foobar);
+ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = local);
+ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = foobar);
 
 \dRs+
 
@@ -88,7 +88,7 @@ BEGIN;
 DROP SUBSCRIPTION testsub;
 COMMIT;
 
-ALTER SUBSCRIPTION testsub WITH (SLOT NAME = NONE);
+ALTER SUBSCRIPTION testsub SET (slot_name = NONE);
 
 -- now it works
 BEGIN;
diff --git a/src/test/subscription/t/001_rep_changes.pl b/src/test/subscription/t/001_rep_changes.pl
index ea99f84534c..1545a5c54aa 100644
--- a/src/test/subscription/t/001_rep_changes.pl
+++ b/src/test/subscription/t/001_rep_changes.pl
@@ -40,7 +40,7 @@ my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
 $node_publisher->safe_psql('postgres',
 	"CREATE PUBLICATION tap_pub");
 $node_publisher->safe_psql('postgres',
-	"CREATE PUBLICATION tap_pub_ins_only WITH (nopublish delete, nopublish update)");
+	"CREATE PUBLICATION tap_pub_ins_only WITH (publish = insert)");
 $node_publisher->safe_psql('postgres',
 	"ALTER PUBLICATION tap_pub ADD TABLE tab_rep, tab_full");
 $node_publisher->safe_psql('postgres',
@@ -136,7 +136,7 @@ $node_publisher->poll_query_until('postgres',
 $oldpid = $node_publisher->safe_psql('postgres',
 	"SELECT pid FROM pg_stat_replication WHERE application_name = '$appname';");
 $node_subscriber->safe_psql('postgres',
-	"ALTER SUBSCRIPTION tap_sub SET PUBLICATION tap_pub_ins_only REFRESH WITH (NOCOPY DATA)");
+	"ALTER SUBSCRIPTION tap_sub SET PUBLICATION tap_pub_ins_only REFRESH WITH (copy_data = false)");
 $node_publisher->poll_query_until('postgres',
 	"SELECT pid != $oldpid FROM pg_stat_replication WHERE application_name = '$appname';")
   or die "Timed out while waiting for apply to restart";
@@ -159,13 +159,13 @@ is($result, qq(20|-20|-1), 'check changes skipped after subscription publication
 
 # check alter publication (relcache invalidation etc)
 $node_publisher->safe_psql('postgres',
-	"ALTER PUBLICATION tap_pub_ins_only WITH (publish delete)");
+	"ALTER PUBLICATION tap_pub_ins_only SET (publish = 'insert, delete')");
 $node_publisher->safe_psql('postgres',
 	"ALTER PUBLICATION tap_pub_ins_only ADD TABLE tab_full");
 $node_publisher->safe_psql('postgres',
 	"DELETE FROM tab_ins WHERE a > 0");
 $node_subscriber->safe_psql('postgres',
-	"ALTER SUBSCRIPTION tap_sub REFRESH PUBLICATION WITH (NOCOPY DATA)");
+	"ALTER SUBSCRIPTION tap_sub REFRESH PUBLICATION WITH (copy_data = false)");
 $node_publisher->safe_psql('postgres',
 	"INSERT INTO tab_full VALUES(0)");
 
diff --git a/src/test/subscription/t/002_types.pl b/src/test/subscription/t/002_types.pl
index 16e8b478855..a9fa4af9205 100644
--- a/src/test/subscription/t/002_types.pl
+++ b/src/test/subscription/t/002_types.pl
@@ -103,7 +103,7 @@ $node_publisher->safe_psql('postgres',
 
 my $appname = 'tap_sub';
 $node_subscriber->safe_psql('postgres',
-	"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub WITH (SLOT NAME = tap_sub_slot)");
+	"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub WITH (slot_name = tap_sub_slot)");
 
 # Wait for subscriber to finish initialization
 my $caughtup_query =
diff --git a/src/test/subscription/t/003_constraints.pl b/src/test/subscription/t/003_constraints.pl
index 074fdb1a30c..b8282af2171 100644
--- a/src/test/subscription/t/003_constraints.pl
+++ b/src/test/subscription/t/003_constraints.pl
@@ -34,7 +34,7 @@ $node_publisher->safe_psql('postgres',
 
 my $appname = 'tap_sub';
 $node_subscriber->safe_psql('postgres',
-	"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub WITH (NOCOPY DATA)");
+	"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub WITH (copy_data = false)");
 
 # Wait for subscriber to finish initialization
 my $caughtup_query =
diff --git a/src/test/subscription/t/004_sync.pl b/src/test/subscription/t/004_sync.pl
index ceeb7a3b5d0..fbc49e3a2ae 100644
--- a/src/test/subscription/t/004_sync.pl
+++ b/src/test/subscription/t/004_sync.pl
@@ -82,7 +82,7 @@ is($result, qq(20), 'initial data synced for second sub');
 
 # now check another subscription for the same node pair
 $node_subscriber->safe_psql('postgres',
-	"CREATE SUBSCRIPTION tap_sub2 CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub WITH (NOCOPY DATA)");
+	"CREATE SUBSCRIPTION tap_sub2 CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub WITH (copy_data = false)");
 
 # wait for it to start
 $node_subscriber->poll_query_until('postgres', "SELECT pid IS NOT NULL FROM pg_stat_subscription WHERE subname = 'tap_sub2' AND relid IS NULL")