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