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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
--
-- 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: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
COMMIT;
-- fail - invalid connection string
CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub;
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
-- fail - duplicate publications
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, testpub, foo WITH (connect = false);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
-- ok
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
COMMENT ON SUBSCRIPTION testsub IS 'test subscription';
ERROR: subscription "testsub" does not exist
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
obj_description
-----------------
(0 rows)
-- fail - name already exists
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
-- fail - must be superuser
SET SESSION AUTHORIZATION 'regress_subscription_user2';
CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION foo WITH (connect = false);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
SET SESSION AUTHORIZATION 'regress_subscription_user';
-- fail - invalid option combinations
CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, copy_data = true);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, enabled = true);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (connect = false, create_slot = true);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = true);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = true);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = false);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
CREATE SUBSCRIPTION testsub2 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
-- ok - with slot_name = NONE
CREATE SUBSCRIPTION testsub3 CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
ERROR: Postgres-XL does not support CREATE SUBSCRIPTION
DETAIL: The feature is not currently supported
-- fail
ALTER SUBSCRIPTION testsub3 ENABLE;
ERROR: subscription "testsub3" does not exist
ALTER SUBSCRIPTION testsub3 REFRESH PUBLICATION;
ERROR: subscription "testsub3" does not exist
DROP SUBSCRIPTION testsub3;
ERROR: subscription "testsub3" does not exist
-- fail - invalid connection string
ALTER SUBSCRIPTION testsub CONNECTION 'foobar';
ERROR: subscription "testsub" does not exist
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
------+-------+---------+-------------+--------------------+----------
(0 rows)
ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false);
ERROR: subscription "testsub" does not exist
ALTER SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist2';
ERROR: subscription "testsub" does not exist
ALTER SUBSCRIPTION testsub SET (slot_name = 'newname');
ERROR: subscription "testsub" does not exist
-- fail
ALTER SUBSCRIPTION doesnotexist CONNECTION 'dbname=doesnotexist2';
ERROR: subscription "doesnotexist" does not exist
ALTER SUBSCRIPTION testsub SET (create_slot = false);
ERROR: subscription "testsub" does not exist
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
------+-------+---------+-------------+--------------------+----------
(0 rows)
ALTER SUBSCRIPTION testsub ENABLE;
ERROR: subscription "testsub" does not exist
\dRs
List of subscriptions
Name | Owner | Enabled | Publication
------+-------+---------+-------------
(0 rows)
ALTER SUBSCRIPTION testsub DISABLE;
ERROR: subscription "testsub" does not exist
\dRs
List of subscriptions
Name | Owner | Enabled | Publication
------+-------+---------+-------------
(0 rows)
-- fail - must be owner of subscription
SET ROLE regress_subscription_user_dummy;
ALTER SUBSCRIPTION testsub RENAME TO testsub_dummy;
ERROR: subscription "testsub" does not exist
RESET ROLE;
ALTER SUBSCRIPTION testsub RENAME TO testsub_foo;
ERROR: subscription "testsub" does not exist
ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = local);
ERROR: subscription "testsub_foo" does not exist
ALTER SUBSCRIPTION testsub_foo SET (synchronous_commit = foobar);
ERROR: subscription "testsub_foo" does not exist
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
------+-------+---------+-------------+--------------------+----------
(0 rows)
-- rename back to keep the rest simple
ALTER SUBSCRIPTION testsub_foo RENAME TO testsub;
ERROR: subscription "testsub_foo" does not exist
-- fail - new owner must be superuser
ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2;
ERROR: subscription "testsub" does not exist
ALTER ROLE regress_subscription_user2 SUPERUSER;
-- now it works
ALTER SUBSCRIPTION testsub OWNER TO regress_subscription_user2;
ERROR: subscription "testsub" does not exist
-- fail - cannot do DROP SUBSCRIPTION inside transaction block with slot name
BEGIN;
DROP SUBSCRIPTION testsub;
ERROR: subscription "testsub" does not exist
COMMIT;
ALTER SUBSCRIPTION testsub SET (slot_name = NONE);
ERROR: subscription "testsub" does not exist
-- now it works
BEGIN;
DROP SUBSCRIPTION testsub;
ERROR: subscription "testsub" does not exist
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;
|
