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
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
|
--
-- 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';
-- suppress warning that depends on wal_level
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_default;
RESET client_min_messages;
COMMENT ON PUBLICATION testpub_default IS 'test publication';
SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
obj_description
------------------
test publication
(1 row)
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert);
RESET client_min_messages;
ALTER PUBLICATION testpub_default SET (publish = update);
-- error cases
CREATE PUBLICATION testpub_xxx WITH (foo);
ERROR: unrecognized publication parameter: "foo"
CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum');
ERROR: unrecognized "publish" value: "cluster"
CREATE PUBLICATION testpub_xxx WITH (publish_via_partition_root = 'true', publish_via_partition_root = '0');
ERROR: conflicting or redundant options
\dRp
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------+--------------------------+------------+---------+---------+---------+-----------+----------
testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f
testpub_default | regress_publication_user | f | f | t | f | f | f
(2 rows)
ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete');
\dRp
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------+--------------------------+------------+---------+---------+---------+-----------+----------
testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f
testpub_default | regress_publication_user | f | t | t | t | f | f
(2 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);
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert');
RESET client_min_messages;
ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update');
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" is defined as FOR ALL TABLES
DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
-- fail - can't drop from all tables publication
ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2;
ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
-- fail - can't add to for all tables publication
ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk;
ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables';
pubname | puballtables
----------------------+--------------
testpub_foralltables | t
(1 row)
\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)
Publications:
"testpub_foralltables"
\dRp+ testpub_foralltables
Publication testpub_foralltables
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------------+------------+---------+---------+---------+-----------+----------
regress_publication_user | t | t | t | f | f | f
(1 row)
DROP TABLE testpub_tbl2;
DROP PUBLICATION testpub_foralltables;
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
RESET client_min_messages;
\dRp+ testpub3
Publication testpub3
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------------+------------+---------+---------+---------+-----------+----------
regress_publication_user | f | t | t | t | t | f
Tables:
"public.testpub_tbl3"
"public.testpub_tbl3a"
\dRp+ testpub4
Publication testpub4
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------------+------------+---------+---------+---------+-----------+----------
regress_publication_user | f | t | t | t | t | f
Tables:
"public.testpub_tbl3"
DROP TABLE testpub_tbl3, testpub_tbl3a;
DROP PUBLICATION testpub3, testpub4;
-- Tests for partitioned tables
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forparted;
CREATE PUBLICATION testpub_forparted1;
RESET client_min_messages;
CREATE TABLE testpub_parted1 (LIKE testpub_parted);
ALTER PUBLICATION testpub_forparted1 SET (publish='insert');
-- works despite missing REPLICA IDENTITY, because updates are not replicated
UPDATE testpub_parted1 SET a = 1;
ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted1 FOR VALUES IN (1);
-- only parent is listed as being in publication, not the partition
ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted;
\dRp+ testpub_forparted
Publication testpub_forparted
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------------+------------+---------+---------+---------+-----------+----------
regress_publication_user | f | t | t | t | t | f
Tables:
"public.testpub_parted"
-- should now fail, because parent's publication replicates updates
UPDATE testpub_parted1 SET a = 1;
ERROR: cannot update table "testpub_parted1" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
ALTER TABLE testpub_parted DETACH PARTITION testpub_parted1;
-- works again, because parent's publication is no longer considered
UPDATE testpub_parted1 SET a = 1;
ALTER PUBLICATION testpub_forparted SET (publish_via_partition_root = true);
\dRp+ testpub_forparted
Publication testpub_forparted
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------------+------------+---------+---------+---------+-----------+----------
regress_publication_user | f | t | t | t | t | t
Tables:
"public.testpub_parted"
DROP TABLE testpub_parted1;
DROP PUBLICATION testpub_forparted, testpub_forparted1;
-- fail - view
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view;
ERROR: "testpub_view" is not a table
DETAIL: Only tables can be added to publications.
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk;
RESET client_min_messages;
-- fail - already added
ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1;
ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl"
-- fail - already added
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1;
ERROR: publication "testpub_fortbl" already exists
\dRp+ testpub_fortbl
Publication testpub_fortbl
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------------+------------+---------+---------+---------+-----------+----------
regress_publication_user | f | t | t | t | t | f
Tables:
"pub_test.testpub_nopk"
"public.testpub_tbl1"
-- fail - view
ALTER PUBLICATION testpub_default ADD TABLE testpub_view;
ERROR: "testpub_view" is not a table
DETAIL: Only tables can be added to publications.
ALTER PUBLICATION testpub_default ADD TABLE testpub_tbl1;
ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1;
ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk;
ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1;
\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 | |
Publications:
"testpib_ins_trunct"
"testpub_default"
"testpub_fortbl"
\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)
Publications:
"testpib_ins_trunct"
"testpub_default"
"testpub_fortbl"
\dRp+ testpub_default
Publication testpub_default
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------------+------------+---------+---------+---------+-----------+----------
regress_publication_user | f | t | t | t | f | f
Tables:
"pub_test.testpub_nopk"
"public.testpub_tbl1"
ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk;
-- fail - nonexistent
ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk;
ERROR: relation "testpub_nopk" is not part of the publication
\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)
Publications:
"testpib_ins_trunct"
"testpub_fortbl"
-- permissions
SET ROLE regress_publication_user2;
CREATE PUBLICATION testpub2; -- fail
ERROR: permission denied for database regression
SET ROLE regress_publication_user;
GRANT CREATE ON DATABASE regression TO regress_publication_user2;
SET ROLE regress_publication_user2;
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub2; -- ok
RESET client_min_messages;
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail
ERROR: must be owner of table testpub_tbl1
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
DROP PUBLICATION testpub2;
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
Publication testpub_default
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------------+------------+---------+---------+---------+-----------+----------
regress_publication_user | f | t | t | t | f | f
(1 row)
-- fail - must be owner of publication
SET ROLE regress_publication_user_dummy;
ALTER PUBLICATION testpub_default RENAME TO testpub_dummy;
ERROR: must be owner of publication testpub_default
RESET ROLE;
ALTER PUBLICATION testpub_default RENAME TO testpub_foo;
\dRp testpub_foo
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
-------------+--------------------------+------------+---------+---------+---------+-----------+----------
testpub_foo | regress_publication_user | f | t | t | t | f | f
(1 row)
-- rename back to keep the rest simple
ALTER PUBLICATION testpub_foo RENAME TO testpub_default;
ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2;
\dRp testpub_default
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
-----------------+---------------------------+------------+---------+---------+---------+-----------+----------
testpub_default | regress_publication_user2 | f | t | t | t | f | f
(1 row)
DROP PUBLICATION testpub_default;
DROP PUBLICATION testpib_ins_trunct;
DROP PUBLICATION testpub_fortbl;
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;
|
