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