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 regress_testsub CONNECTION 'foo';
10ERROR:  syntax error at or near ";"
11LINE 1: CREATE SUBSCRIPTION regress_testsub CONNECTION 'foo';
12                                                            ^
13-- fail - no connection
14CREATE SUBSCRIPTION regress_testsub PUBLICATION foo;
15ERROR:  syntax error at or near "PUBLICATION"
16LINE 1: CREATE SUBSCRIPTION regress_testsub PUBLICATION foo;
17                                            ^
18-- fail - cannot do CREATE SUBSCRIPTION CREATE SLOT inside transaction block
19BEGIN;
20CREATE SUBSCRIPTION regress_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 regress_testsub CONNECTION 'testconn' PUBLICATION testpub;
25ERROR:  invalid connection string syntax: missing "=" after "testconn" in connection info string
26
27-- fail - duplicate publications
28CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION foo, testpub, foo WITH (connect = false);
29ERROR:  publication name "foo" used more than once
30-- ok
31CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_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 regress_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 regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
42ERROR:  subscription "regress_testsub" already exists
43-- fail - must be superuser
44SET SESSION AUTHORIZATION 'regress_subscription_user2';
45CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_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 regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, copy_data = true);
50ERROR:  connect = false and copy_data = true are mutually exclusive options
51CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, enabled = true);
52ERROR:  connect = false and enabled = true are mutually exclusive options
53CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, create_slot = true);
54ERROR:  connect = false and create_slot = true are mutually exclusive options
55CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = true);
56ERROR:  slot_name = NONE and enabled = true are mutually exclusive options
57CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = true);
58ERROR:  slot_name = NONE and create_slot = true are mutually exclusive options
59CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE);
60ERROR:  subscription with slot_name = NONE must also set enabled = false
61CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = false);
62ERROR:  subscription with slot_name = NONE must also set create_slot = false
63CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_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 regress_testsub3 CONNECTION 'dbname=regress_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 regress_testsub3 ENABLE;
70ERROR:  cannot enable subscription that does not have a slot name
71ALTER SUBSCRIPTION regress_testsub3 REFRESH PUBLICATION;
72ERROR:  ALTER SUBSCRIPTION ... REFRESH is not allowed for disabled subscriptions
73DROP SUBSCRIPTION regress_testsub3;
74-- fail - invalid connection string
75ALTER SUBSCRIPTION regress_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 regress_testsub | regress_subscription_user | f       | {testpub}   | off                | dbname=regress_doesnotexist
83(1 row)
84
85ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false);
86ALTER SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist2';
87ALTER SUBSCRIPTION regress_testsub SET (slot_name = 'newname');
88-- fail
89ALTER SUBSCRIPTION regress_testsub SET (slot_name = '');
90ERROR:  replication slot name "" is too short
91-- fail
92ALTER SUBSCRIPTION regress_doesnotexist CONNECTION 'dbname=regress_doesnotexist2';
93ERROR:  subscription "regress_doesnotexist" does not exist
94ALTER SUBSCRIPTION regress_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 regress_testsub | regress_subscription_user | f       | {testpub2,testpub3} | off                | dbname=regress_doesnotexist2
101(1 row)
102
103BEGIN;
104ALTER SUBSCRIPTION regress_testsub ENABLE;
105\dRs
106                            List of subscriptions
107      Name       |           Owner           | Enabled |     Publication
108-----------------+---------------------------+---------+---------------------
109 regress_testsub | regress_subscription_user | t       | {testpub2,testpub3}
110(1 row)
111
112ALTER SUBSCRIPTION regress_testsub DISABLE;
113\dRs
114                            List of subscriptions
115      Name       |           Owner           | Enabled |     Publication
116-----------------+---------------------------+---------+---------------------
117 regress_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 regress_testsub RENAME TO regress_testsub_dummy;
124ERROR:  must be owner of subscription regress_testsub
125RESET ROLE;
126ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub_foo;
127ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = local);
128ALTER SUBSCRIPTION regress_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 regress_testsub_foo | regress_subscription_user | f       | {testpub2,testpub3} | local              | dbname=regress_doesnotexist2
136(1 row)
137
138-- rename back to keep the rest simple
139ALTER SUBSCRIPTION regress_testsub_foo RENAME TO regress_testsub;
140-- fail - new owner must be superuser
141ALTER SUBSCRIPTION regress_testsub OWNER TO regress_subscription_user2;
142ERROR:  permission denied to change owner of subscription "regress_testsub"
143HINT:  The owner of a subscription must be a superuser.
144ALTER ROLE regress_subscription_user2 SUPERUSER;
145-- now it works
146ALTER SUBSCRIPTION regress_testsub OWNER TO regress_subscription_user2;
147-- fail - cannot do DROP SUBSCRIPTION inside transaction block with slot name
148BEGIN;
149DROP SUBSCRIPTION regress_testsub;
150ERROR:  DROP SUBSCRIPTION cannot run inside a transaction block
151COMMIT;
152ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
153\dRs+
154                                                      List of subscriptions
155      Name       |           Owner            | Enabled |     Publication     | Synchronous commit |           Conninfo
156-----------------+----------------------------+---------+---------------------+--------------------+------------------------------
157 regress_testsub | regress_subscription_user2 | f       | {testpub2,testpub3} | local              | dbname=regress_doesnotexist2
158(1 row)
159
160-- now it works
161BEGIN;
162DROP SUBSCRIPTION regress_testsub;
163COMMIT;
164DROP SUBSCRIPTION IF EXISTS regress_testsub;
165NOTICE:  subscription "regress_testsub" does not exist, skipping
166DROP SUBSCRIPTION regress_testsub;  -- fail
167ERROR:  subscription "regress_testsub" does not exist
168RESET SESSION AUTHORIZATION;
169DROP ROLE regress_subscription_user;
170DROP ROLE regress_subscription_user2;
171DROP ROLE regress_subscription_user_dummy;
172