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