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