1-- 2-- SUBSCRIPTION 3-- 4 5CREATE ROLE regress_subscription_user LOGIN SUPERUSER; 6CREATE ROLE regress_subscription_user2; 7CREATE ROLE regress_subscription_user_dummy LOGIN NOSUPERUSER; 8SET SESSION AUTHORIZATION 'regress_subscription_user'; 9 10-- fail - no publications 11CREATE SUBSCRIPTION testsub CONNECTION 'foo'; 12 13-- fail - no connection 14CREATE SUBSCRIPTION testsub PUBLICATION foo; 15 16-- fail - cannot do CREATE SUBSCRIPTION CREATE SLOT inside transaction block 17BEGIN; 18CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub WITH (create_slot); 19COMMIT; 20 21-- fail - invalid connection string 22CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub; 23 24-- fail - duplicate publications 25CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, testpub, foo WITH (connect = false); 26 27-- ok 28CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false); 29 30COMMENT ON SUBSCRIPTION testsub IS 'test subscription'; 31SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s; 32 33-- fail - name already exists 34CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false); 35 36-- fail - must be superuser 37SET SESSION AUTHORIZATION 'regress_subscription_user2'; 38CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION foo WITH (connect = false); 39SET SESSION AUTHORIZATION 'regress_subscription_user'; 40 41-- fail - invalid option combinations 42CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, copy_data = true); 43CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, enabled = true); 44CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, create_slot = true); 45CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = true); 46CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = true); 47CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE); 48CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = false); 49CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false); 50 51-- ok - with slot_name = NONE 52CREATE SUBSCRIPTION testsub3 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false); 53-- fail 54ALTER SUBSCRIPTION testsub3 ENABLE; 55ALTER SUBSCRIPTION testsub3 REFRESH PUBLICATION; 56 57DROP SUBSCRIPTION testsub3; 58 59-- fail - invalid connection string 60ALTER SUBSCRIPTION testsub CONNECTION 'foobar'; 61 62\dRs+ 63 64ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false); 65ALTER SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist2'; 66ALTER SUBSCRIPTION testsub SET (slot_name = 'newname'); 67 68-- fail 69ALTER SUBSCRIPTION doesnotexist CONNECTION 'dbname=doesnotexist2'; 70ALTER SUBSCRIPTION testsub SET (create_slot = false); 71 72\dRs+ 73 74BEGIN; 75ALTER SUBSCRIPTION testsub ENABLE; 76 77\dRs 78 79ALTER SUBSCRIPTION testsub DISABLE; 80 81\dRs 82 83COMMIT; 84 85-- fail - must be owner of subscription 86SET ROLE regress_subscription_user_dummy; 87ALTER SUBSCRIPTION testsub RENAME TO testsub_dummy; 88RESET ROLE; 89 90ALTER SUBSCRIPTION testsub RENAME TO testsub_foo; 91ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = local); 92ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = foobar); 93 94\dRs+ 95 96-- rename back to keep the rest simple 97ALTER SUBSCRIPTION testsub_foo RENAME TO testsub; 98 99-- fail - new owner must be superuser 100ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2; 101ALTER ROLE regress_subscription_user2 SUPERUSER; 102-- now it works 103ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2; 104 105-- fail - cannot do DROP SUBSCRIPTION inside transaction block with slot name 106BEGIN; 107DROP SUBSCRIPTION testsub; 108COMMIT; 109 110ALTER SUBSCRIPTION testsub SET (slot_name = NONE); 111 112-- now it works 113BEGIN; 114DROP SUBSCRIPTION testsub; 115COMMIT; 116 117DROP SUBSCRIPTION IF EXISTS testsub; 118DROP SUBSCRIPTION testsub; -- fail 119 120RESET SESSION AUTHORIZATION; 121DROP ROLE regress_subscription_user; 122DROP ROLE regress_subscription_user2; 123DROP ROLE regress_subscription_user_dummy; 124