1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
--
-- SUBSCRIPTION
--
CREATE ROLE regress_subscription_user LOGIN SUPERUSER;
CREATE ROLE regress_subscription_user2;
CREATE ROLE regress_subscription_user_dummy LOGIN NOSUPERUSER;
SET SESSION AUTHORIZATION 'regress_subscription_user';
-- fail - no publications
CREATE SUBSCRIPTION testsub CONNECTION 'foo';
ERROR: syntax error at or near ";"
LINE 1: CREATE SUBSCRIPTION testsub CONNECTION 'foo';
^
-- fail - no connection
CREATE SUBSCRIPTION testsub PUBLICATION foo;
ERROR: syntax error at or near "PUBLICATION"
LINE 1: CREATE SUBSCRIPTION testsub PUBLICATION foo;
^
-- fail - cannot do CREATE SUBSCRIPTION CREATE SLOT inside transaction block
BEGIN;
CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub WITH (CREATE SLOT);
ERROR: CREATE SUBSCRIPTION ... CREATE SLOT cannot run inside a transaction block
COMMIT;
-- fail - invalid connection string
CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub;
ERROR: invalid connection string syntax: missing "=" after "testconn" in connection info string
-- fail - duplicate publications
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, testpub, foo WITH (NOCONNECT);
ERROR: publication name "foo" used more than once
-- ok
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (NOCONNECT);
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
COMMENT ON SUBSCRIPTION testsub IS 'test subscription';
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
obj_description
-------------------
test subscription
(1 row)
-- fail - name already exists
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (NOCONNECT);
ERROR: subscription "testsub" already exists
-- fail - must be superuser
SET SESSION AUTHORIZATION 'regress_subscription_user2';
CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION foo WITH (NOCONNECT);
ERROR: must be superuser to create subscriptions
SET SESSION AUTHORIZATION 'regress_subscription_user';
-- fail - invalid connection string
ALTER SUBSCRIPTION testsub CONNECTION 'foobar';
ERROR: invalid connection string syntax: missing "=" after "foobar" in connection info string
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
---------+---------------------------+---------+-------------+--------------------+---------------------
testsub | regress_subscription_user | f | {testpub} | off | dbname=doesnotexist
(1 row)
ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3 NOREFRESH;
ALTER SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist2';
ALTER SUBSCRIPTION testsub WITH (SLOT NAME = 'newname');
-- fail
ALTER SUBSCRIPTION doesnotexist CONNECTION 'dbname=doesnotexist2';
ERROR: subscription "doesnotexist" does not exist
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
---------+---------------------------+---------+---------------------+--------------------+----------------------
testsub | regress_subscription_user | f | {testpub2,testpub3} | off | dbname=doesnotexist2
(1 row)
BEGIN;
ALTER SUBSCRIPTION testsub ENABLE;
\dRs
List of subscriptions
Name | Owner | Enabled | Publication
---------+---------------------------+---------+---------------------
testsub | regress_subscription_user | t | {testpub2,testpub3}
(1 row)
ALTER SUBSCRIPTION testsub DISABLE;
\dRs
List of subscriptions
Name | Owner | Enabled | Publication
---------+---------------------------+---------+---------------------
testsub | regress_subscription_user | f | {testpub2,testpub3}
(1 row)
COMMIT;
-- fail - must be owner of subscription
SET ROLE regress_subscription_user_dummy;
ALTER SUBSCRIPTION testsub RENAME TO testsub_dummy;
ERROR: must be owner of subscription testsub
RESET ROLE;
ALTER SUBSCRIPTION testsub RENAME TO testsub_foo;
ALTER SUBSCRIPTION testsub_foo WITH (SYNCHRONOUS_COMMIT = local);
ALTER SUBSCRIPTION testsub_foo WITH (SYNCHRONOUS_COMMIT = foobar);
ERROR: invalid value for parameter "synchronous_commit": "foobar"
HINT: Available values: local, remote_write, remote_apply, on, off.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
-------------+---------------------------+---------+---------------------+--------------------+----------------------
testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | local | dbname=doesnotexist2
(1 row)
-- rename back to keep the rest simple
ALTER SUBSCRIPTION testsub_foo RENAME TO testsub;
-- fail - new owner must be superuser
ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2;
ERROR: permission denied to change owner of subscription "testsub"
HINT: The owner of a subscription must be a superuser.
ALTER ROLE regress_subscription_user2 SUPERUSER;
-- now it works
ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2;
-- fail - cannot do DROP SUBSCRIPTION inside transaction block with slot name
BEGIN;
DROP SUBSCRIPTION testsub;
ERROR: DROP SUBSCRIPTION cannot run inside a transaction block
COMMIT;
ALTER SUBSCRIPTION testsub WITH (SLOT NAME = NONE);
-- now it works
BEGIN;
DROP SUBSCRIPTION testsub;
COMMIT;
DROP SUBSCRIPTION IF EXISTS testsub;
NOTICE: subscription "testsub" does not exist, skipping
DROP SUBSCRIPTION testsub; -- fail
ERROR: subscription "testsub" does not exist
RESET SESSION AUTHORIZATION;
DROP ROLE regress_subscription_user;
DROP ROLE regress_subscription_user2;
DROP ROLE regress_subscription_user_dummy;
|
