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
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
|
--
-- PUBLICATION
--
CREATE ROLE regress_publication_user LOGIN SUPERUSER;
CREATE ROLE regress_publication_user2;
CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER;
SET SESSION AUTHORIZATION 'regress_publication_user';
CREATE PUBLICATION testpub_default;
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
COMMENT ON PUBLICATION testpub_default IS 'test publication';
ERROR: publication "testpub_default" does not exist
SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
obj_description
-----------------
(0 rows)
CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert);
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
ALTER PUBLICATION testpub_default SET (publish = update);
ERROR: publication "testpub_default" does not exist
-- error cases
CREATE PUBLICATION testpub_xxx WITH (foo);
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum');
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
\dRp
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes
------+-------+------------+---------+---------+---------
(0 rows)
ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete');
ERROR: publication "testpub_default" does not exist
\dRp
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes
------+-------+------------+---------+---------+---------
(0 rows)
--- adding tables
CREATE SCHEMA pub_test;
CREATE TABLE testpub_tbl1 (id serial primary key, data text);
CREATE TABLE pub_test.testpub_nopk (foo int, bar int);
CREATE VIEW testpub_view AS SELECT 1;
CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a);
CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert');
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update');
ERROR: publication "testpub_foralltables" does not exist
CREATE TABLE testpub_tbl2 (id serial primary key, data text);
-- fail - can't add to for all tables publication
ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2;
ERROR: publication "testpub_foralltables" does not exist
-- fail - can't drop from all tables publication
ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2;
ERROR: publication "testpub_foralltables" does not exist
-- fail - can't add to for all tables publication
ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk;
ERROR: publication "testpub_foralltables" does not exist
SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables';
pubname | puballtables
---------+--------------
(0 rows)
\d+ testpub_tbl2
Table "public.testpub_tbl2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | plain | |
data | text | | | | extended | |
Indexes:
"testpub_tbl2_pkey" PRIMARY KEY, btree (id)
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
\dRp+ testpub_foralltables
DROP TABLE testpub_tbl2;
DROP PUBLICATION testpub_foralltables;
ERROR: publication "testpub_foralltables" does not exist
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
\dRp+ testpub3
\dRp+ testpub4
DROP TABLE testpub_tbl3, testpub_tbl3a;
DROP PUBLICATION testpub3, testpub4;
ERROR: publication "testpub3" does not exist
-- fail - view
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view;
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk;
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
-- fail - already added
ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1;
ERROR: publication "testpub_fortbl" does not exist
-- fail - already added
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1;
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
\dRp+ testpub_fortbl
-- fail - view
ALTER PUBLICATION testpub_default ADD TABLE testpub_view;
ERROR: publication "testpub_default" does not exist
-- fail - partitioned table
ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_parted;
ERROR: publication "testpub_fortbl" does not exist
ALTER PUBLICATION testpub_default ADD TABLE testpub_tbl1;
ERROR: publication "testpub_default" does not exist
ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1;
ERROR: publication "testpub_default" does not exist
ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk;
ERROR: publication "testpub_default" does not exist
ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1;
ERROR: publication "testpib_ins_trunct" does not exist
\d+ pub_test.testpub_nopk
Table "pub_test.testpub_nopk"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
foo | integer | | | | plain | |
bar | integer | | | | plain | |
Distribute By: HASH(foo)
Location Nodes: ALL DATANODES
\d+ testpub_tbl1
Table "public.testpub_tbl1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | |
data | text | | | | extended | |
Indexes:
"testpub_tbl1_pkey" PRIMARY KEY, btree (id)
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
\dRp+ testpub_default
ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk;
ERROR: publication "testpub_default" does not exist
-- fail - nonexistent
ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk;
ERROR: publication "testpub_default" does not exist
\d+ testpub_tbl1
Table "public.testpub_tbl1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | |
data | text | | | | extended | |
Indexes:
"testpub_tbl1_pkey" PRIMARY KEY, btree (id)
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
-- permissions
SET ROLE regress_publication_user2;
CREATE PUBLICATION testpub2; -- fail
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
SET ROLE regress_publication_user;
GRANT CREATE ON DATABASE regression TO regress_publication_user2;
SET ROLE regress_publication_user2;
CREATE PUBLICATION testpub2; -- ok
ERROR: Postgres-XL does not support CREATE PUBLICATION
DETAIL: The feature is not currently supported
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail
ERROR: publication "testpub2" does not exist
SET ROLE regress_publication_user;
GRANT regress_publication_user TO regress_publication_user2;
SET ROLE regress_publication_user2;
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok
ERROR: publication "testpub2" does not exist
DROP PUBLICATION testpub2;
ERROR: publication "testpub2" does not exist
SET ROLE regress_publication_user;
REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
DROP TABLE testpub_parted;
DROP VIEW testpub_view;
DROP TABLE testpub_tbl1;
\dRp+ testpub_default
-- fail - must be owner of publication
SET ROLE regress_publication_user_dummy;
ALTER PUBLICATION testpub_default RENAME TO testpub_dummy;
ERROR: publication "testpub_default" does not exist
RESET ROLE;
ALTER PUBLICATION testpub_default RENAME TO testpub_foo;
ERROR: publication "testpub_default" does not exist
\dRp testpub_foo
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes
------+-------+------------+---------+---------+---------
(0 rows)
-- rename back to keep the rest simple
ALTER PUBLICATION testpub_foo RENAME TO testpub_default;
ERROR: publication "testpub_foo" does not exist
ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2;
ERROR: publication "testpub_default" does not exist
\dRp testpub_default
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes
------+-------+------------+---------+---------+---------
(0 rows)
DROP PUBLICATION testpub_default;
ERROR: publication "testpub_default" does not exist
DROP PUBLICATION testpib_ins_trunct;
ERROR: publication "testpib_ins_trunct" does not exist
DROP PUBLICATION testpub_fortbl;
ERROR: publication "testpub_fortbl" does not exist
DROP SCHEMA pub_test CASCADE;
NOTICE: drop cascades to table pub_test.testpub_nopk
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
|
