1-- 2-- SUBSCRIPTION 3-- 4CREATE ROLE regress_subscription_user LOGIN SUPERUSER; 5CREATE ROLE regress_subscription_user2; 6CREATE ROLE regress_subscription_user_dummy LOGIN NOSUPERUSER; 7SET SESSION AUTHORIZATION 'regress_subscription_user'; 8-- fail - no publications 9CREATE SUBSCRIPTION testsub CONNECTION 'foo'; 10ERROR: syntax error at or near ";" 11LINE 1: CREATE SUBSCRIPTION testsub CONNECTION 'foo'; 12 ^ 13-- fail - no connection 14CREATE SUBSCRIPTION testsub PUBLICATION foo; 15ERROR: syntax error at or near "PUBLICATION" 16LINE 1: CREATE SUBSCRIPTION testsub PUBLICATION foo; 17 ^ 18-- fail - cannot do CREATE SUBSCRIPTION CREATE SLOT inside transaction block 19BEGIN; 20CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub WITH (create_slot); 21ERROR: CREATE SUBSCRIPTION ... WITH (create_slot = true) cannot run inside a transaction block 22COMMIT; 23-- fail - invalid connection string 24CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub; 25ERROR: invalid connection string syntax: missing "=" after "testconn" in connection info string 26 27-- fail - duplicate publications 28CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, testpub, foo WITH (connect = false); 29ERROR: publication name "foo" used more than once 30-- ok 31CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false); 32WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables 33COMMENT ON SUBSCRIPTION testsub IS 'test subscription'; 34SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s; 35 obj_description 36------------------- 37 test subscription 38(1 row) 39 40-- fail - name already exists 41CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false); 42ERROR: subscription "testsub" already exists 43-- fail - must be superuser 44SET SESSION AUTHORIZATION 'regress_subscription_user2'; 45CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION foo WITH (connect = false); 46ERROR: must be superuser to create subscriptions 47SET SESSION AUTHORIZATION 'regress_subscription_user'; 48-- fail - invalid option combinations 49CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, copy_data = true); 50ERROR: connect = false and copy_data = true are mutually exclusive options 51CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, enabled = true); 52ERROR: connect = false and enabled = true are mutually exclusive options 53CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, create_slot = true); 54ERROR: connect = false and create_slot = true are mutually exclusive options 55CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = true); 56ERROR: slot_name = NONE and enabled = true are mutually exclusive options 57CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = true); 58ERROR: slot_name = NONE and create_slot = true are mutually exclusive options 59CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE); 60ERROR: subscription with slot_name = NONE must also set enabled = false 61CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = false); 62ERROR: subscription with slot_name = NONE must also set create_slot = false 63CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false); 64ERROR: subscription with slot_name = NONE must also set enabled = false 65-- ok - with slot_name = NONE 66CREATE SUBSCRIPTION testsub3 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false); 67WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables 68-- fail 69ALTER SUBSCRIPTION testsub3 ENABLE; 70ERROR: cannot enable subscription that does not have a slot name 71ALTER SUBSCRIPTION testsub3 REFRESH PUBLICATION; 72ERROR: ALTER SUBSCRIPTION ... REFRESH is not allowed for disabled subscriptions 73DROP SUBSCRIPTION testsub3; 74-- fail - invalid connection string 75ALTER SUBSCRIPTION testsub CONNECTION 'foobar'; 76ERROR: invalid connection string syntax: missing "=" after "foobar" in connection info string 77 78\dRs+ 79 List of subscriptions 80 Name | Owner | Enabled | Publication | Synchronous commit | Conninfo 81---------+---------------------------+---------+-------------+--------------------+--------------------- 82 testsub | regress_subscription_user | f | {testpub} | off | dbname=doesnotexist 83(1 row) 84 85ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false); 86ALTER SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist2'; 87ALTER SUBSCRIPTION testsub SET (slot_name = 'newname'); 88-- fail 89ALTER SUBSCRIPTION testsub SET (slot_name = ''); 90ERROR: replication slot name "" is too short 91-- fail 92ALTER SUBSCRIPTION doesnotexist CONNECTION 'dbname=doesnotexist2'; 93ERROR: subscription "doesnotexist" does not exist 94ALTER SUBSCRIPTION testsub SET (create_slot = false); 95ERROR: unrecognized subscription parameter: create_slot 96\dRs+ 97 List of subscriptions 98 Name | Owner | Enabled | Publication | Synchronous commit | Conninfo 99---------+---------------------------+---------+---------------------+--------------------+---------------------- 100 testsub | regress_subscription_user | f | {testpub2,testpub3} | off | dbname=doesnotexist2 101(1 row) 102 103BEGIN; 104ALTER SUBSCRIPTION testsub ENABLE; 105\dRs 106 List of subscriptions 107 Name | Owner | Enabled | Publication 108---------+---------------------------+---------+--------------------- 109 testsub | regress_subscription_user | t | {testpub2,testpub3} 110(1 row) 111 112ALTER SUBSCRIPTION testsub DISABLE; 113\dRs 114 List of subscriptions 115 Name | Owner | Enabled | Publication 116---------+---------------------------+---------+--------------------- 117 testsub | regress_subscription_user | f | {testpub2,testpub3} 118(1 row) 119 120COMMIT; 121-- fail - must be owner of subscription 122SET ROLE regress_subscription_user_dummy; 123ALTER SUBSCRIPTION testsub RENAME TO testsub_dummy; 124ERROR: must be owner of subscription testsub 125RESET ROLE; 126ALTER SUBSCRIPTION testsub RENAME TO testsub_foo; 127ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = local); 128ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = foobar); 129ERROR: invalid value for parameter "synchronous_commit": "foobar" 130HINT: Available values: local, remote_write, remote_apply, on, off. 131\dRs+ 132 List of subscriptions 133 Name | Owner | Enabled | Publication | Synchronous commit | Conninfo 134-------------+---------------------------+---------+---------------------+--------------------+---------------------- 135 testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | local | dbname=doesnotexist2 136(1 row) 137 138-- rename back to keep the rest simple 139ALTER SUBSCRIPTION testsub_foo RENAME TO testsub; 140-- fail - new owner must be superuser 141ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2; 142ERROR: permission denied to change owner of subscription "testsub" 143HINT: The owner of a subscription must be a superuser. 144ALTER ROLE regress_subscription_user2 SUPERUSER; 145-- now it works 146ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2; 147-- fail - cannot do DROP SUBSCRIPTION inside transaction block with slot name 148BEGIN; 149DROP SUBSCRIPTION testsub; 150ERROR: DROP SUBSCRIPTION cannot run inside a transaction block 151COMMIT; 152ALTER SUBSCRIPTION testsub SET (slot_name = NONE); 153-- now it works 154BEGIN; 155DROP SUBSCRIPTION testsub; 156COMMIT; 157DROP SUBSCRIPTION IF EXISTS testsub; 158NOTICE: subscription "testsub" does not exist, skipping 159DROP SUBSCRIPTION testsub; -- fail 160ERROR: subscription "testsub" does not exist 161RESET SESSION AUTHORIZATION; 162DROP ROLE regress_subscription_user; 163DROP ROLE regress_subscription_user2; 164DROP ROLE regress_subscription_user_dummy; 165