| blob | c48f11f2935180fc78ea44861eab3f2a128f9676 |
1 --
2 -- PUBLICATION
3 --
4 CREATE ROLE regress_publication_user LOGIN SUPERUSER;
5 CREATE ROLE regress_publication_user2;
6 CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER;
7 SET SESSION AUTHORIZATION 'regress_publication_user';
8 -- suppress warning that depends on wal_level
9 SET client_min_messages = 'ERROR';
10 CREATE PUBLICATION testpub_default;
11 RESET client_min_messages;
12 COMMENT ON PUBLICATION testpub_default IS 'test publication';
13 SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
14 obj_description
15 ------------------
16 test publication
17 (1 row)
19 SET client_min_messages = 'ERROR';
20 CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert);
21 RESET client_min_messages;
22 ALTER PUBLICATION testpub_default SET (publish = update);
23 -- error cases
24 CREATE PUBLICATION testpub_xxx WITH (foo);
25 ERROR: unrecognized publication parameter: "foo"
26 CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum');
27 ERROR: unrecognized value for publication option "publish": "cluster"
28 CREATE PUBLICATION testpub_xxx WITH (publish_via_partition_root = 'true', publish_via_partition_root = '0');
29 ERROR: conflicting or redundant options
30 LINE 1: ...ub_xxx WITH (publish_via_partition_root = 'true', publish_vi...
31 ^
32 CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = 'true', publish_generated_columns = '0');
33 ERROR: conflicting or redundant options
34 LINE 1: ...pub_xxx WITH (publish_generated_columns = 'true', publish_ge...
35 ^
36 CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = 'foo');
37 ERROR: publish_generated_columns requires a Boolean value
38 \dRp
39 List of publications
40 Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
41 --------------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
42 testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f | f
43 testpub_default | regress_publication_user | f | f | t | f | f | f | f
44 (2 rows)
46 ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete');
47 \dRp
48 List of publications
49 Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
50 --------------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
51 testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f | f
52 testpub_default | regress_publication_user | f | t | t | t | f | f | f
53 (2 rows)
55 --- adding tables
56 CREATE SCHEMA pub_test;
57 CREATE TABLE testpub_tbl1 (id serial primary key, data text);
58 CREATE TABLE pub_test.testpub_nopk (foo int, bar int);
59 CREATE VIEW testpub_view AS SELECT 1;
60 CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a);
61 SET client_min_messages = 'ERROR';
62 CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert');
63 RESET client_min_messages;
64 ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update');
65 CREATE TABLE testpub_tbl2 (id serial primary key, data text);
66 -- fail - can't add to for all tables publication
67 ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2;
68 ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
69 DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
70 -- fail - can't drop from all tables publication
71 ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2;
72 ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
73 DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
74 -- fail - can't add to for all tables publication
75 ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk;
76 ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
77 DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
78 -- fail - can't add schema to 'FOR ALL TABLES' publication
79 ALTER PUBLICATION testpub_foralltables ADD TABLES IN SCHEMA pub_test;
80 ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
81 DETAIL: Schemas cannot be added to or dropped from FOR ALL TABLES publications.
82 -- fail - can't drop schema from 'FOR ALL TABLES' publication
83 ALTER PUBLICATION testpub_foralltables DROP TABLES IN SCHEMA pub_test;
84 ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
85 DETAIL: Schemas cannot be added to or dropped from FOR ALL TABLES publications.
86 -- fail - can't set schema to 'FOR ALL TABLES' publication
87 ALTER PUBLICATION testpub_foralltables SET TABLES IN SCHEMA pub_test;
88 ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
89 DETAIL: Schemas cannot be added to or dropped from FOR ALL TABLES publications.
90 SET client_min_messages = 'ERROR';
91 CREATE PUBLICATION testpub_fortable FOR TABLE testpub_tbl1;
92 RESET client_min_messages;
93 -- should be able to add schema to 'FOR TABLE' publication
94 ALTER PUBLICATION testpub_fortable ADD TABLES IN SCHEMA pub_test;
95 \dRp+ testpub_fortable
96 Publication testpub_fortable
97 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
98 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
99 regress_publication_user | f | t | t | t | t | f | f
100 Tables:
101 "public.testpub_tbl1"
102 Tables from schemas:
103 "pub_test"
105 -- should be able to drop schema from 'FOR TABLE' publication
106 ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test;
107 \dRp+ testpub_fortable
108 Publication testpub_fortable
109 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
110 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
111 regress_publication_user | f | t | t | t | t | f | f
112 Tables:
113 "public.testpub_tbl1"
115 -- should be able to set schema to 'FOR TABLE' publication
116 ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
117 \dRp+ testpub_fortable
118 Publication testpub_fortable
119 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
120 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
121 regress_publication_user | f | t | t | t | t | f | f
122 Tables from schemas:
123 "pub_test"
125 SET client_min_messages = 'ERROR';
126 CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
127 -- should be able to create publication with schema and table of the same
128 -- schema
129 CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk;
130 RESET client_min_messages;
131 \dRp+ testpub_for_tbl_schema
132 Publication testpub_for_tbl_schema
133 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
134 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
135 regress_publication_user | f | t | t | t | t | f | f
136 Tables:
137 "pub_test.testpub_nopk"
138 Tables from schemas:
139 "pub_test"
141 -- weird parser corner case
142 CREATE PUBLICATION testpub_parsertst FOR TABLE pub_test.testpub_nopk, CURRENT_SCHEMA;
143 ERROR: invalid table name
144 LINE 1: ...estpub_parsertst FOR TABLE pub_test.testpub_nopk, CURRENT_SC...
145 ^
146 CREATE PUBLICATION testpub_parsertst FOR TABLES IN SCHEMA foo, test.foo;
147 ERROR: invalid schema name
148 LINE 1: ...CATION testpub_parsertst FOR TABLES IN SCHEMA foo, test.foo;
149 ^
150 -- should be able to add a table of the same schema to the schema publication
151 ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk;
152 \dRp+ testpub_forschema
153 Publication testpub_forschema
154 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
155 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
156 regress_publication_user | f | t | t | t | t | f | f
157 Tables:
158 "pub_test.testpub_nopk"
159 Tables from schemas:
160 "pub_test"
162 -- should be able to drop the table
163 ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk;
164 \dRp+ testpub_forschema
165 Publication testpub_forschema
166 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
167 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
168 regress_publication_user | f | t | t | t | t | f | f
169 Tables from schemas:
170 "pub_test"
172 -- fail - can't drop a table from the schema publication which isn't in the
173 -- publication
174 ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk;
175 ERROR: relation "testpub_nopk" is not part of the publication
176 -- should be able to set table to schema publication
177 ALTER PUBLICATION testpub_forschema SET TABLE pub_test.testpub_nopk;
178 \dRp+ testpub_forschema
179 Publication testpub_forschema
180 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
181 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
182 regress_publication_user | f | t | t | t | t | f | f
183 Tables:
184 "pub_test.testpub_nopk"
186 SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables';
187 pubname | puballtables
188 ----------------------+--------------
189 testpub_foralltables | t
190 (1 row)
192 \d+ testpub_tbl2
193 Table "public.testpub_tbl2"
194 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
195 --------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
196 id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | plain | |
197 data | text | | | | extended | |
198 Indexes:
199 "testpub_tbl2_pkey" PRIMARY KEY, btree (id)
200 Publications:
201 "testpub_foralltables"
202 Not-null constraints:
203 "testpub_tbl2_id_not_null" NOT NULL "id"
205 \dRp+ testpub_foralltables
206 Publication testpub_foralltables
207 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
208 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
209 regress_publication_user | t | t | t | f | f | f | f
210 (1 row)
212 DROP TABLE testpub_tbl2;
213 DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema;
214 CREATE TABLE testpub_tbl3 (a int);
215 CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
216 SET client_min_messages = 'ERROR';
217 CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
218 CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
219 RESET client_min_messages;
220 \dRp+ testpub3
221 Publication testpub3
222 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
223 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
224 regress_publication_user | f | t | t | t | t | f | f
225 Tables:
226 "public.testpub_tbl3"
227 "public.testpub_tbl3a"
229 \dRp+ testpub4
230 Publication testpub4
231 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
232 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
233 regress_publication_user | f | t | t | t | t | f | f
234 Tables:
235 "public.testpub_tbl3"
237 DROP TABLE testpub_tbl3, testpub_tbl3a;
238 DROP PUBLICATION testpub3, testpub4;
239 -- Tests for partitioned tables
240 SET client_min_messages = 'ERROR';
241 CREATE PUBLICATION testpub_forparted;
242 CREATE PUBLICATION testpub_forparted1;
243 RESET client_min_messages;
244 CREATE TABLE testpub_parted1 (LIKE testpub_parted);
245 CREATE TABLE testpub_parted2 (LIKE testpub_parted);
246 ALTER PUBLICATION testpub_forparted1 SET (publish='insert');
247 ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted1 FOR VALUES IN (1);
248 ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted2 FOR VALUES IN (2);
249 -- works despite missing REPLICA IDENTITY, because updates are not replicated
250 UPDATE testpub_parted1 SET a = 1;
251 -- only parent is listed as being in publication, not the partition
252 ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted;
253 \dRp+ testpub_forparted
254 Publication testpub_forparted
255 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
256 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
257 regress_publication_user | f | t | t | t | t | f | f
258 Tables:
259 "public.testpub_parted"
261 -- works despite missing REPLICA IDENTITY, because no actual update happened
262 UPDATE testpub_parted SET a = 1 WHERE false;
263 -- should now fail, because parent's publication replicates updates
264 UPDATE testpub_parted1 SET a = 1;
265 ERROR: cannot update table "testpub_parted1" because it does not have a replica identity and publishes updates
266 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
267 ALTER TABLE testpub_parted DETACH PARTITION testpub_parted1;
268 -- works again, because parent's publication is no longer considered
269 UPDATE testpub_parted1 SET a = 1;
270 ALTER PUBLICATION testpub_forparted SET (publish_via_partition_root = true);
271 \dRp+ testpub_forparted
272 Publication testpub_forparted
273 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
274 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
275 regress_publication_user | f | t | t | t | t | f | t
276 Tables:
277 "public.testpub_parted"
279 -- still fail, because parent's publication replicates updates
280 UPDATE testpub_parted2 SET a = 2;
281 ERROR: cannot update table "testpub_parted2" because it does not have a replica identity and publishes updates
282 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
283 ALTER PUBLICATION testpub_forparted DROP TABLE testpub_parted;
284 -- works again, because update is no longer replicated
285 UPDATE testpub_parted2 SET a = 2;
286 DROP TABLE testpub_parted1, testpub_parted2;
287 DROP PUBLICATION testpub_forparted, testpub_forparted1;
288 -- Tests for row filters
289 CREATE TABLE testpub_rf_tbl1 (a integer, b text);
290 CREATE TABLE testpub_rf_tbl2 (c text, d integer);
291 CREATE TABLE testpub_rf_tbl3 (e integer);
292 CREATE TABLE testpub_rf_tbl4 (g text);
293 CREATE TABLE testpub_rf_tbl5 (a xml);
294 CREATE SCHEMA testpub_rf_schema1;
295 CREATE TABLE testpub_rf_schema1.testpub_rf_tbl5 (h integer);
296 CREATE SCHEMA testpub_rf_schema2;
297 CREATE TABLE testpub_rf_schema2.testpub_rf_tbl6 (i integer);
298 SET client_min_messages = 'ERROR';
299 -- Firstly, test using the option publish='insert' because the row filter
300 -- validation of referenced columns is less strict than for delete/update.
301 CREATE PUBLICATION testpub5 FOR TABLE testpub_rf_tbl1, testpub_rf_tbl2 WHERE (c <> 'test' AND d < 5) WITH (publish = 'insert');
302 RESET client_min_messages;
303 \dRp+ testpub5
304 Publication testpub5
305 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
306 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
307 regress_publication_user | f | t | f | f | f | f | f
308 Tables:
309 "public.testpub_rf_tbl1"
310 "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5))
312 \d testpub_rf_tbl3
313 Table "public.testpub_rf_tbl3"
314 Column | Type | Collation | Nullable | Default
315 --------+---------+-----------+----------+---------
316 e | integer | | |
318 ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl3 WHERE (e > 1000 AND e < 2000);
319 \dRp+ testpub5
320 Publication testpub5
321 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
322 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
323 regress_publication_user | f | t | f | f | f | f | f
324 Tables:
325 "public.testpub_rf_tbl1"
326 "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5))
327 "public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000))
329 \d testpub_rf_tbl3
330 Table "public.testpub_rf_tbl3"
331 Column | Type | Collation | Nullable | Default
332 --------+---------+-----------+----------+---------
333 e | integer | | |
334 Publications:
335 "testpub5" WHERE ((e > 1000) AND (e < 2000))
337 ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl2;
338 \dRp+ testpub5
339 Publication testpub5
340 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
341 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
342 regress_publication_user | f | t | f | f | f | f | f
343 Tables:
344 "public.testpub_rf_tbl1"
345 "public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000))
347 -- remove testpub_rf_tbl1 and add testpub_rf_tbl3 again (another WHERE expression)
348 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e > 300 AND e < 500);
349 \dRp+ testpub5
350 Publication testpub5
351 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
352 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
353 regress_publication_user | f | t | f | f | f | f | f
354 Tables:
355 "public.testpub_rf_tbl3" WHERE ((e > 300) AND (e < 500))
357 \d testpub_rf_tbl3
358 Table "public.testpub_rf_tbl3"
359 Column | Type | Collation | Nullable | Default
360 --------+---------+-----------+----------+---------
361 e | integer | | |
362 Publications:
363 "testpub5" WHERE ((e > 300) AND (e < 500))
365 -- test \d <tablename> (now it displays filter information)
366 SET client_min_messages = 'ERROR';
367 CREATE PUBLICATION testpub_rf_yes FOR TABLE testpub_rf_tbl1 WHERE (a > 1) WITH (publish = 'insert');
368 CREATE PUBLICATION testpub_rf_no FOR TABLE testpub_rf_tbl1;
369 RESET client_min_messages;
370 \d testpub_rf_tbl1
371 Table "public.testpub_rf_tbl1"
372 Column | Type | Collation | Nullable | Default
373 --------+---------+-----------+----------+---------
374 a | integer | | |
375 b | text | | |
376 Publications:
377 "testpub_rf_no"
378 "testpub_rf_yes" WHERE (a > 1)
380 DROP PUBLICATION testpub_rf_yes, testpub_rf_no;
381 -- some more syntax tests to exercise other parser pathways
382 SET client_min_messages = 'ERROR';
383 CREATE PUBLICATION testpub_syntax1 FOR TABLE testpub_rf_tbl1, ONLY testpub_rf_tbl3 WHERE (e < 999) WITH (publish = 'insert');
384 RESET client_min_messages;
385 \dRp+ testpub_syntax1
386 Publication testpub_syntax1
387 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
388 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
389 regress_publication_user | f | t | f | f | f | f | f
390 Tables:
391 "public.testpub_rf_tbl1"
392 "public.testpub_rf_tbl3" WHERE (e < 999)
394 DROP PUBLICATION testpub_syntax1;
395 SET client_min_messages = 'ERROR';
396 CREATE PUBLICATION testpub_syntax2 FOR TABLE testpub_rf_tbl1, testpub_rf_schema1.testpub_rf_tbl5 WHERE (h < 999) WITH (publish = 'insert');
397 RESET client_min_messages;
398 \dRp+ testpub_syntax2
399 Publication testpub_syntax2
400 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
401 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
402 regress_publication_user | f | t | f | f | f | f | f
403 Tables:
404 "public.testpub_rf_tbl1"
405 "testpub_rf_schema1.testpub_rf_tbl5" WHERE (h < 999)
407 DROP PUBLICATION testpub_syntax2;
408 -- fail - schemas don't allow WHERE clause
409 SET client_min_messages = 'ERROR';
410 CREATE PUBLICATION testpub_syntax3 FOR TABLES IN SCHEMA testpub_rf_schema1 WHERE (a = 123);
411 ERROR: syntax error at or near "WHERE"
412 LINE 1: ...b_syntax3 FOR TABLES IN SCHEMA testpub_rf_schema1 WHERE (a =...
413 ^
414 CREATE PUBLICATION testpub_syntax3 FOR TABLES IN SCHEMA testpub_rf_schema1, testpub_rf_schema1 WHERE (a = 123);
415 ERROR: WHERE clause not allowed for schema
416 LINE 1: ..._syntax3 FOR TABLES IN SCHEMA testpub_rf_schema1, testpub_rf...
417 ^
418 RESET client_min_messages;
419 -- fail - duplicate tables are not allowed if that table has any WHERE clause
420 SET client_min_messages = 'ERROR';
421 CREATE PUBLICATION testpub_dups FOR TABLE testpub_rf_tbl1 WHERE (a = 1), testpub_rf_tbl1 WITH (publish = 'insert');
422 ERROR: conflicting or redundant WHERE clauses for table "testpub_rf_tbl1"
423 CREATE PUBLICATION testpub_dups FOR TABLE testpub_rf_tbl1, testpub_rf_tbl1 WHERE (a = 2) WITH (publish = 'insert');
424 ERROR: conflicting or redundant WHERE clauses for table "testpub_rf_tbl1"
425 RESET client_min_messages;
426 -- fail - publication WHERE clause must be boolean
427 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (1234);
428 ERROR: argument of PUBLICATION WHERE must be type boolean, not type integer
429 LINE 1: ...PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (1234);
430 ^
431 -- fail - aggregate functions not allowed in WHERE clause
432 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e < AVG(e));
433 ERROR: aggregate functions are not allowed in WHERE
434 LINE 1: ...ATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e < AVG(e));
435 ^
436 -- fail - user-defined operators are not allowed
437 CREATE FUNCTION testpub_rf_func1(integer, integer) RETURNS boolean AS $$ SELECT hashint4($1) > $2 $$ LANGUAGE SQL;
438 CREATE OPERATOR =#> (PROCEDURE = testpub_rf_func1, LEFTARG = integer, RIGHTARG = integer);
439 CREATE PUBLICATION testpub6 FOR TABLE testpub_rf_tbl3 WHERE (e =#> 27);
440 ERROR: invalid publication WHERE expression
441 LINE 1: ...ICATION testpub6 FOR TABLE testpub_rf_tbl3 WHERE (e =#> 27);
442 ^
443 DETAIL: User-defined operators are not allowed.
444 -- fail - user-defined functions are not allowed
445 CREATE FUNCTION testpub_rf_func2() RETURNS integer AS $$ BEGIN RETURN 123; END; $$ LANGUAGE plpgsql;
446 ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (a >= testpub_rf_func2());
447 ERROR: invalid publication WHERE expression
448 LINE 1: ...ON testpub5 ADD TABLE testpub_rf_tbl1 WHERE (a >= testpub_rf...
449 ^
450 DETAIL: User-defined or built-in mutable functions are not allowed.
451 -- fail - non-immutable functions are not allowed. random() is volatile.
452 ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (a < random());
453 ERROR: invalid publication WHERE expression
454 LINE 1: ...ION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (a < random());
455 ^
456 DETAIL: User-defined or built-in mutable functions are not allowed.
457 -- fail - user-defined collations are not allowed
458 CREATE COLLATION user_collation FROM "C";
459 ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (b < '2' COLLATE user_collation);
460 ERROR: invalid publication WHERE expression
461 LINE 1: ...ICATION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (b < '2' CO...
462 ^
463 DETAIL: User-defined collations are not allowed.
464 -- ok - NULLIF is allowed
465 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (NULLIF(1,2) = a);
466 -- ok - built-in operators are allowed
467 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (a IS NULL);
468 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE ((a > 5) IS FALSE);
469 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (a IS DISTINCT FROM 5);
470 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE ((a, a + 1) < (2, 3));
471 -- ok - built-in type coercions between two binary compatible datatypes are allowed
472 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (b::varchar < '2');
473 -- ok - immutable built-in functions are allowed
474 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl4 WHERE (length(g) < 6);
475 -- fail - user-defined types are not allowed
476 CREATE TYPE rf_bug_status AS ENUM ('new', 'open', 'closed');
477 CREATE TABLE rf_bug (id serial, description text, status rf_bug_status);
478 CREATE PUBLICATION testpub6 FOR TABLE rf_bug WHERE (status = 'open') WITH (publish = 'insert');
479 ERROR: invalid publication WHERE expression
480 LINE 1: ...EATE PUBLICATION testpub6 FOR TABLE rf_bug WHERE (status = '...
481 ^
482 DETAIL: User-defined types are not allowed.
483 DROP TABLE rf_bug;
484 DROP TYPE rf_bug_status;
485 -- fail - row filter expression is not simple
486 CREATE PUBLICATION testpub6 FOR TABLE testpub_rf_tbl1 WHERE (a IN (SELECT generate_series(1,5)));
487 ERROR: invalid publication WHERE expression
488 LINE 1: ...ICATION testpub6 FOR TABLE testpub_rf_tbl1 WHERE (a IN (SELE...
489 ^
490 DETAIL: Only columns, constants, built-in operators, built-in data types, built-in collations, and immutable built-in functions are allowed.
491 -- fail - system columns are not allowed
492 CREATE PUBLICATION testpub6 FOR TABLE testpub_rf_tbl1 WHERE ('(0,1)'::tid = ctid);
493 ERROR: invalid publication WHERE expression
494 LINE 1: ...tpub6 FOR TABLE testpub_rf_tbl1 WHERE ('(0,1)'::tid = ctid);
495 ^
496 DETAIL: System columns are not allowed.
497 -- ok - conditional expressions are allowed
498 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl5 WHERE (a IS DOCUMENT);
499 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl5 WHERE (xmlexists('//foo[text() = ''bar'']' PASSING BY VALUE a));
500 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (NULLIF(1, 2) = a);
501 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (CASE a WHEN 5 THEN true ELSE false END);
502 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (COALESCE(b, 'foo') = 'foo');
503 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (GREATEST(a, 10) > 10);
504 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (a IN (2, 4, 6));
505 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (ARRAY[a] <@ ARRAY[2, 4, 6]);
506 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (ROW(a, 2) IS NULL);
507 -- fail - WHERE not allowed in DROP
508 ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl1 WHERE (e < 27);
509 ERROR: cannot use a WHERE clause when removing a table from a publication
510 -- fail - cannot ALTER SET table which is a member of a pre-existing schema
511 SET client_min_messages = 'ERROR';
512 CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2;
513 -- should be able to set publication with schema and table of the same schema
514 ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99);
515 RESET client_min_messages;
516 \dRp+ testpub6
517 Publication testpub6
518 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
519 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
520 regress_publication_user | f | t | t | t | t | f | f
521 Tables:
522 "testpub_rf_schema2.testpub_rf_tbl6" WHERE (i < 99)
523 Tables from schemas:
524 "testpub_rf_schema2"
526 DROP TABLE testpub_rf_tbl1;
527 DROP TABLE testpub_rf_tbl2;
528 DROP TABLE testpub_rf_tbl3;
529 DROP TABLE testpub_rf_tbl4;
530 DROP TABLE testpub_rf_tbl5;
531 DROP TABLE testpub_rf_schema1.testpub_rf_tbl5;
532 DROP TABLE testpub_rf_schema2.testpub_rf_tbl6;
533 DROP SCHEMA testpub_rf_schema1;
534 DROP SCHEMA testpub_rf_schema2;
535 DROP PUBLICATION testpub5;
536 DROP PUBLICATION testpub6;
537 DROP OPERATOR =#>(integer, integer);
538 DROP FUNCTION testpub_rf_func1(integer, integer);
539 DROP FUNCTION testpub_rf_func2();
540 DROP COLLATION user_collation;
541 -- ======================================================
542 -- More row filter tests for validating column references
543 CREATE TABLE rf_tbl_abcd_nopk(a int, b int, c int, d int);
544 CREATE TABLE rf_tbl_abcd_pk(a int, b int, c int, d int, PRIMARY KEY(a,b));
545 CREATE TABLE rf_tbl_abcd_part_pk (a int PRIMARY KEY, b int) PARTITION by RANGE (a);
546 CREATE TABLE rf_tbl_abcd_part_pk_1 (b int, a int PRIMARY KEY);
547 ALTER TABLE rf_tbl_abcd_part_pk ATTACH PARTITION rf_tbl_abcd_part_pk_1 FOR VALUES FROM (1) TO (10);
548 -- Case 1. REPLICA IDENTITY DEFAULT (means use primary key or nothing)
549 -- 1a. REPLICA IDENTITY is DEFAULT and table has a PK.
550 SET client_min_messages = 'ERROR';
551 CREATE PUBLICATION testpub6 FOR TABLE rf_tbl_abcd_pk WHERE (a > 99);
552 RESET client_min_messages;
553 -- ok - "a" is a PK col
554 UPDATE rf_tbl_abcd_pk SET a = 1;
555 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (b > 99);
556 -- ok - "b" is a PK col
557 UPDATE rf_tbl_abcd_pk SET a = 1;
558 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
559 -- fail - "c" is not part of the PK
560 UPDATE rf_tbl_abcd_pk SET a = 1;
561 ERROR: cannot update table "rf_tbl_abcd_pk"
562 DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
563 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (d > 99);
564 -- fail - "d" is not part of the PK
565 UPDATE rf_tbl_abcd_pk SET a = 1;
566 ERROR: cannot update table "rf_tbl_abcd_pk"
567 DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
568 -- 1b. REPLICA IDENTITY is DEFAULT and table has no PK
569 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
570 -- fail - "a" is not part of REPLICA IDENTITY
571 UPDATE rf_tbl_abcd_nopk SET a = 1;
572 ERROR: cannot update table "rf_tbl_abcd_nopk"
573 DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
574 -- Case 2. REPLICA IDENTITY FULL
575 ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY FULL;
576 ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY FULL;
577 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
578 -- ok - "c" is in REPLICA IDENTITY now even though not in PK
579 UPDATE rf_tbl_abcd_pk SET a = 1;
580 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
581 -- ok - "a" is in REPLICA IDENTITY now
582 UPDATE rf_tbl_abcd_nopk SET a = 1;
583 -- Case 3. REPLICA IDENTITY NOTHING
584 ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY NOTHING;
585 ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY NOTHING;
586 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (a > 99);
587 -- fail - "a" is in PK but it is not part of REPLICA IDENTITY NOTHING
588 UPDATE rf_tbl_abcd_pk SET a = 1;
589 ERROR: cannot update table "rf_tbl_abcd_pk"
590 DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
591 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
592 -- fail - "c" is not in PK and not in REPLICA IDENTITY NOTHING
593 UPDATE rf_tbl_abcd_pk SET a = 1;
594 ERROR: cannot update table "rf_tbl_abcd_pk"
595 DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
596 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
597 -- fail - "a" is not in REPLICA IDENTITY NOTHING
598 UPDATE rf_tbl_abcd_nopk SET a = 1;
599 ERROR: cannot update table "rf_tbl_abcd_nopk"
600 DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
601 -- Case 4. REPLICA IDENTITY INDEX
602 ALTER TABLE rf_tbl_abcd_pk ALTER COLUMN c SET NOT NULL;
603 CREATE UNIQUE INDEX idx_abcd_pk_c ON rf_tbl_abcd_pk(c);
604 ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY USING INDEX idx_abcd_pk_c;
605 ALTER TABLE rf_tbl_abcd_nopk ALTER COLUMN c SET NOT NULL;
606 CREATE UNIQUE INDEX idx_abcd_nopk_c ON rf_tbl_abcd_nopk(c);
607 ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY USING INDEX idx_abcd_nopk_c;
608 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (a > 99);
609 -- fail - "a" is in PK but it is not part of REPLICA IDENTITY INDEX
610 UPDATE rf_tbl_abcd_pk SET a = 1;
611 ERROR: cannot update table "rf_tbl_abcd_pk"
612 DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
613 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
614 -- ok - "c" is not in PK but it is part of REPLICA IDENTITY INDEX
615 UPDATE rf_tbl_abcd_pk SET a = 1;
616 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
617 -- fail - "a" is not in REPLICA IDENTITY INDEX
618 UPDATE rf_tbl_abcd_nopk SET a = 1;
619 ERROR: cannot update table "rf_tbl_abcd_nopk"
620 DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
621 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (c > 99);
622 -- ok - "c" is part of REPLICA IDENTITY INDEX
623 UPDATE rf_tbl_abcd_nopk SET a = 1;
624 -- Tests for partitioned table
625 -- set PUBLISH_VIA_PARTITION_ROOT to false and test row filter for partitioned
626 -- table
627 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0);
628 -- fail - cannot use row filter for partitioned table
629 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk WHERE (a > 99);
630 ERROR: cannot use publication WHERE clause for relation "rf_tbl_abcd_part_pk"
631 DETAIL: WHERE clause cannot be used for a partitioned table when publish_via_partition_root is false.
632 -- ok - can use row filter for partition
633 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk_1 WHERE (a > 99);
634 -- ok - "a" is a PK col
635 UPDATE rf_tbl_abcd_part_pk SET a = 1;
636 -- set PUBLISH_VIA_PARTITION_ROOT to true and test row filter for partitioned
637 -- table
638 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=1);
639 -- ok - can use row filter for partitioned table
640 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk WHERE (a > 99);
641 -- ok - "a" is a PK col
642 UPDATE rf_tbl_abcd_part_pk SET a = 1;
643 -- fail - cannot set PUBLISH_VIA_PARTITION_ROOT to false if any row filter is
644 -- used for partitioned table
645 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0);
646 ERROR: cannot set parameter "publish_via_partition_root" to false for publication "testpub6"
647 DETAIL: The publication contains a WHERE clause for partitioned table "rf_tbl_abcd_part_pk", which is not allowed when "publish_via_partition_root" is false.
648 -- remove partitioned table's row filter
649 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk;
650 -- ok - we don't have row filter for partitioned table.
651 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0);
652 -- Now change the root filter to use a column "b"
653 -- (which is not in the replica identity)
654 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk_1 WHERE (b > 99);
655 -- ok - we don't have row filter for partitioned table.
656 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0);
657 -- fail - "b" is not in REPLICA IDENTITY INDEX
658 UPDATE rf_tbl_abcd_part_pk SET a = 1;
659 ERROR: cannot update table "rf_tbl_abcd_part_pk_1"
660 DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
661 -- set PUBLISH_VIA_PARTITION_ROOT to true
662 -- can use row filter for partitioned table
663 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=1);
664 -- ok - can use row filter for partitioned table
665 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk WHERE (b > 99);
666 -- fail - "b" is not in REPLICA IDENTITY INDEX
667 UPDATE rf_tbl_abcd_part_pk SET a = 1;
668 ERROR: cannot update table "rf_tbl_abcd_part_pk_1"
669 DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
670 DROP PUBLICATION testpub6;
671 DROP TABLE rf_tbl_abcd_pk;
672 DROP TABLE rf_tbl_abcd_nopk;
673 DROP TABLE rf_tbl_abcd_part_pk;
674 -- ======================================================
675 -- ======================================================
676 -- Tests with generated column
677 SET client_min_messages = 'ERROR';
678 CREATE TABLE testpub_gencol (a INT, b INT GENERATED ALWAYS AS (a + 1) STORED NOT NULL);
679 CREATE UNIQUE INDEX testpub_gencol_idx ON testpub_gencol (b);
680 ALTER TABLE testpub_gencol REPLICA IDENTITY USING index testpub_gencol_idx;
681 -- error - generated column "b" must be published explicitly as it is
682 -- part of the REPLICA IDENTITY index.
683 CREATE PUBLICATION pub_gencol FOR TABLE testpub_gencol;
684 UPDATE testpub_gencol SET a = 100 WHERE a = 1;
685 ERROR: cannot update table "testpub_gencol"
686 DETAIL: Replica identity must not contain unpublished generated columns.
687 -- error - generated column "b" must be published explicitly as it is
688 -- part of the REPLICA IDENTITY.
689 ALTER TABLE testpub_gencol REPLICA IDENTITY FULL;
690 UPDATE testpub_gencol SET a = 100 WHERE a = 1;
691 ERROR: cannot update table "testpub_gencol"
692 DETAIL: Replica identity must not contain unpublished generated columns.
693 DROP PUBLICATION pub_gencol;
694 -- ok - generated column "b" is published explicitly
695 CREATE PUBLICATION pub_gencol FOR TABLE testpub_gencol with (publish_generated_columns = true);
696 UPDATE testpub_gencol SET a = 100 WHERE a = 1;
697 DROP PUBLICATION pub_gencol;
698 DROP TABLE testpub_gencol;
699 RESET client_min_messages;
700 -- ======================================================
701 -- fail - duplicate tables are not allowed if that table has any column lists
702 SET client_min_messages = 'ERROR';
703 CREATE PUBLICATION testpub_dups FOR TABLE testpub_tbl1 (a), testpub_tbl1 WITH (publish = 'insert');
704 ERROR: conflicting or redundant column lists for table "testpub_tbl1"
705 CREATE PUBLICATION testpub_dups FOR TABLE testpub_tbl1, testpub_tbl1 (a) WITH (publish = 'insert');
706 ERROR: conflicting or redundant column lists for table "testpub_tbl1"
707 RESET client_min_messages;
708 -- test for column lists
709 SET client_min_messages = 'ERROR';
710 CREATE PUBLICATION testpub_fortable FOR TABLE testpub_tbl1;
711 CREATE PUBLICATION testpub_fortable_insert WITH (publish = 'insert');
712 RESET client_min_messages;
713 CREATE TABLE testpub_tbl5 (a int PRIMARY KEY, b text, c text,
714 d int generated always as (a + length(b)) stored);
715 -- error: column "x" does not exist
716 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, x);
717 ERROR: column "x" of relation "testpub_tbl5" does not exist
718 -- error: replica identity "a" not included in the column list
719 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (b, c);
720 UPDATE testpub_tbl5 SET a = 1;
721 ERROR: cannot update table "testpub_tbl5"
722 DETAIL: Column list used by the publication does not cover the replica identity.
723 ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
724 -- error: system attributes "ctid" not allowed in column list
725 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, ctid);
726 ERROR: cannot use system column "ctid" in publication column list
727 ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl1 (id, ctid);
728 ERROR: cannot use system column "ctid" in publication column list
729 -- error: duplicates not allowed in column list
730 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, a);
731 ERROR: duplicate column "a" in publication column list
732 ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl5 (a, a);
733 ERROR: duplicate column "a" in publication column list
734 -- ok
735 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, c);
736 ALTER TABLE testpub_tbl5 DROP COLUMN c; -- no dice
737 ERROR: cannot drop column c of table testpub_tbl5 because other objects depend on it
738 DETAIL: publication of table testpub_tbl5 in publication testpub_fortable depends on column c of table testpub_tbl5
739 HINT: Use DROP ... CASCADE to drop the dependent objects too.
740 -- ok: for insert-only publication, any column list is acceptable
741 ALTER PUBLICATION testpub_fortable_insert ADD TABLE testpub_tbl5 (b, c);
742 /* not all replica identities are good enough */
743 CREATE UNIQUE INDEX testpub_tbl5_b_key ON testpub_tbl5 (b, c);
744 ALTER TABLE testpub_tbl5 ALTER b SET NOT NULL, ALTER c SET NOT NULL;
745 ALTER TABLE testpub_tbl5 REPLICA IDENTITY USING INDEX testpub_tbl5_b_key;
746 -- error: replica identity (b,c) is not covered by column list (a, c)
747 UPDATE testpub_tbl5 SET a = 1;
748 ERROR: cannot update table "testpub_tbl5"
749 DETAIL: Column list used by the publication does not cover the replica identity.
750 ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
751 -- ok: generated column "d" can be in the list too
752 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, d);
753 ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
754 -- error: change the replica identity to "b", and column list to (a, c)
755 -- then update fails, because (a, c) does not cover replica identity
756 ALTER TABLE testpub_tbl5 REPLICA IDENTITY USING INDEX testpub_tbl5_b_key;
757 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, c);
758 UPDATE testpub_tbl5 SET a = 1;
759 ERROR: cannot update table "testpub_tbl5"
760 DETAIL: Column list used by the publication does not cover the replica identity.
761 /* But if upd/del are not published, it works OK */
762 SET client_min_messages = 'ERROR';
763 CREATE PUBLICATION testpub_table_ins WITH (publish = 'insert, truncate');
764 RESET client_min_messages;
765 ALTER PUBLICATION testpub_table_ins ADD TABLE testpub_tbl5 (a); -- ok
766 \dRp+ testpub_table_ins
767 Publication testpub_table_ins
768 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
769 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
770 regress_publication_user | f | t | f | f | t | f | f
771 Tables:
772 "public.testpub_tbl5" (a)
774 -- error: cannot work with deferrable primary keys
775 CREATE TABLE testpub_tbl5d (a int PRIMARY KEY DEFERRABLE);
776 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5d;
777 UPDATE testpub_tbl5d SET a = 1;
778 ERROR: cannot update table "testpub_tbl5d" because it does not have a replica identity and publishes updates
779 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
780 /* but works fine with FULL replica identity */
781 ALTER TABLE testpub_tbl5d REPLICA IDENTITY FULL;
782 UPDATE testpub_tbl5d SET a = 1;
783 DROP TABLE testpub_tbl5d;
784 -- tests with REPLICA IDENTITY FULL
785 CREATE TABLE testpub_tbl6 (a int, b text, c text);
786 ALTER TABLE testpub_tbl6 REPLICA IDENTITY FULL;
787 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl6 (a, b, c);
788 UPDATE testpub_tbl6 SET a = 1;
789 ERROR: cannot update table "testpub_tbl6"
790 DETAIL: Column list used by the publication does not cover the replica identity.
791 ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl6;
792 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl6; -- ok
793 UPDATE testpub_tbl6 SET a = 1;
794 -- make sure changing the column list is propagated to the catalog
795 CREATE TABLE testpub_tbl7 (a int primary key, b text, c text);
796 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl7 (a, b);
797 \d+ testpub_tbl7
798 Table "public.testpub_tbl7"
799 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
800 --------+---------+-----------+----------+---------+----------+--------------+-------------
801 a | integer | | not null | | plain | |
802 b | text | | | | extended | |
803 c | text | | | | extended | |
804 Indexes:
805 "testpub_tbl7_pkey" PRIMARY KEY, btree (a)
806 Publications:
807 "testpub_fortable" (a, b)
808 Not-null constraints:
809 "testpub_tbl7_a_not_null" NOT NULL "a"
811 -- ok: the column list is the same, we should skip this table (or at least not fail)
812 ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl7 (a, b);
813 \d+ testpub_tbl7
814 Table "public.testpub_tbl7"
815 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
816 --------+---------+-----------+----------+---------+----------+--------------+-------------
817 a | integer | | not null | | plain | |
818 b | text | | | | extended | |
819 c | text | | | | extended | |
820 Indexes:
821 "testpub_tbl7_pkey" PRIMARY KEY, btree (a)
822 Publications:
823 "testpub_fortable" (a, b)
824 Not-null constraints:
825 "testpub_tbl7_a_not_null" NOT NULL "a"
827 -- ok: the column list changes, make sure the catalog gets updated
828 ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl7 (a, c);
829 \d+ testpub_tbl7
830 Table "public.testpub_tbl7"
831 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
832 --------+---------+-----------+----------+---------+----------+--------------+-------------
833 a | integer | | not null | | plain | |
834 b | text | | | | extended | |
835 c | text | | | | extended | |
836 Indexes:
837 "testpub_tbl7_pkey" PRIMARY KEY, btree (a)
838 Publications:
839 "testpub_fortable" (a, c)
840 Not-null constraints:
841 "testpub_tbl7_a_not_null" NOT NULL "a"
843 -- column list for partitioned tables has to cover replica identities for
844 -- all child relations
845 CREATE TABLE testpub_tbl8 (a int, b text, c text) PARTITION BY HASH (a);
846 -- first partition has replica identity "a"
847 CREATE TABLE testpub_tbl8_0 PARTITION OF testpub_tbl8 FOR VALUES WITH (modulus 2, remainder 0);
848 ALTER TABLE testpub_tbl8_0 ADD PRIMARY KEY (a);
849 ALTER TABLE testpub_tbl8_0 REPLICA IDENTITY USING INDEX testpub_tbl8_0_pkey;
850 -- second partition has replica identity "b"
851 CREATE TABLE testpub_tbl8_1 PARTITION OF testpub_tbl8 FOR VALUES WITH (modulus 2, remainder 1);
852 ALTER TABLE testpub_tbl8_1 ADD PRIMARY KEY (b);
853 ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY USING INDEX testpub_tbl8_1_pkey;
854 -- ok: column list covers both "a" and "b"
855 SET client_min_messages = 'ERROR';
856 CREATE PUBLICATION testpub_col_list FOR TABLE testpub_tbl8 (a, b) WITH (publish_via_partition_root = 'true');
857 RESET client_min_messages;
858 -- ok: the same thing, but try plain ADD TABLE
859 ALTER PUBLICATION testpub_col_list DROP TABLE testpub_tbl8;
860 ALTER PUBLICATION testpub_col_list ADD TABLE testpub_tbl8 (a, b);
861 UPDATE testpub_tbl8 SET a = 1;
862 -- failure: column list does not cover replica identity for the second partition
863 ALTER PUBLICATION testpub_col_list DROP TABLE testpub_tbl8;
864 ALTER PUBLICATION testpub_col_list ADD TABLE testpub_tbl8 (a, c);
865 UPDATE testpub_tbl8 SET a = 1;
866 ERROR: cannot update table "testpub_tbl8_1"
867 DETAIL: Column list used by the publication does not cover the replica identity.
868 ALTER PUBLICATION testpub_col_list DROP TABLE testpub_tbl8;
869 -- failure: one of the partitions has REPLICA IDENTITY FULL
870 ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY FULL;
871 ALTER PUBLICATION testpub_col_list ADD TABLE testpub_tbl8 (a, c);
872 UPDATE testpub_tbl8 SET a = 1;
873 ERROR: cannot update table "testpub_tbl8_1"
874 DETAIL: Column list used by the publication does not cover the replica identity.
875 ALTER PUBLICATION testpub_col_list DROP TABLE testpub_tbl8;
876 -- add table and then try changing replica identity
877 ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY USING INDEX testpub_tbl8_1_pkey;
878 ALTER PUBLICATION testpub_col_list ADD TABLE testpub_tbl8 (a, b);
879 -- failure: replica identity full can't be used with a column list
880 ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY FULL;
881 UPDATE testpub_tbl8 SET a = 1;
882 ERROR: cannot update table "testpub_tbl8_1"
883 DETAIL: Column list used by the publication does not cover the replica identity.
884 -- failure: replica identity has to be covered by the column list
885 ALTER TABLE testpub_tbl8_1 DROP CONSTRAINT testpub_tbl8_1_pkey;
886 ALTER TABLE testpub_tbl8_1 ADD PRIMARY KEY (c);
887 ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY USING INDEX testpub_tbl8_1_pkey;
888 UPDATE testpub_tbl8 SET a = 1;
889 ERROR: cannot update table "testpub_tbl8_1"
890 DETAIL: Column list used by the publication does not cover the replica identity.
891 DROP TABLE testpub_tbl8;
892 -- column list for partitioned tables has to cover replica identities for
893 -- all child relations
894 CREATE TABLE testpub_tbl8 (a int, b text, c text) PARTITION BY HASH (a);
895 ALTER PUBLICATION testpub_col_list ADD TABLE testpub_tbl8 (a, b);
896 -- first partition has replica identity "a"
897 CREATE TABLE testpub_tbl8_0 (a int, b text, c text);
898 ALTER TABLE testpub_tbl8_0 ADD PRIMARY KEY (a);
899 ALTER TABLE testpub_tbl8_0 REPLICA IDENTITY USING INDEX testpub_tbl8_0_pkey;
900 -- second partition has replica identity "b"
901 CREATE TABLE testpub_tbl8_1 (a int, b text, c text);
902 ALTER TABLE testpub_tbl8_1 ADD PRIMARY KEY (c);
903 ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY USING INDEX testpub_tbl8_1_pkey;
904 -- ok: attaching first partition works, because (a) is in column list
905 ALTER TABLE testpub_tbl8 ATTACH PARTITION testpub_tbl8_0 FOR VALUES WITH (modulus 2, remainder 0);
906 -- failure: second partition has replica identity (c), which si not in column list
907 ALTER TABLE testpub_tbl8 ATTACH PARTITION testpub_tbl8_1 FOR VALUES WITH (modulus 2, remainder 1);
908 UPDATE testpub_tbl8 SET a = 1;
909 ERROR: cannot update table "testpub_tbl8_1"
910 DETAIL: Column list used by the publication does not cover the replica identity.
911 -- failure: changing replica identity to FULL for partition fails, because
912 -- of the column list on the parent
913 ALTER TABLE testpub_tbl8_0 REPLICA IDENTITY FULL;
914 UPDATE testpub_tbl8 SET a = 1;
915 ERROR: cannot update table "testpub_tbl8_0"
916 DETAIL: Column list used by the publication does not cover the replica identity.
917 -- test that using column list for table is disallowed if any schemas are
918 -- part of the publication
919 SET client_min_messages = 'ERROR';
920 -- failure - cannot use column list and schema together
921 CREATE PUBLICATION testpub_tbl9 FOR TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a);
922 ERROR: cannot use column list for relation "public.testpub_tbl7" in publication "testpub_tbl9"
923 DETAIL: Column lists cannot be specified in publications containing FOR TABLES IN SCHEMA elements.
924 -- ok - only publish schema
925 CREATE PUBLICATION testpub_tbl9 FOR TABLES IN SCHEMA public;
926 -- failure - add a table with column list when there is already a schema in the
927 -- publication
928 ALTER PUBLICATION testpub_tbl9 ADD TABLE public.testpub_tbl7(a);
929 ERROR: cannot use column list for relation "public.testpub_tbl7" in publication "testpub_tbl9"
930 DETAIL: Column lists cannot be specified in publications containing FOR TABLES IN SCHEMA elements.
931 -- ok - only publish table with column list
932 ALTER PUBLICATION testpub_tbl9 SET TABLE public.testpub_tbl7(a);
933 -- failure - specify a schema when there is already a column list in the
934 -- publication
935 ALTER PUBLICATION testpub_tbl9 ADD TABLES IN SCHEMA public;
936 ERROR: cannot add schema to publication "testpub_tbl9"
937 DETAIL: Schemas cannot be added if any tables that specify a column list are already part of the publication.
938 -- failure - cannot SET column list and schema together
939 ALTER PUBLICATION testpub_tbl9 SET TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a);
940 ERROR: cannot use column list for relation "public.testpub_tbl7" in publication "testpub_tbl9"
941 DETAIL: Column lists cannot be specified in publications containing FOR TABLES IN SCHEMA elements.
942 -- ok - drop table
943 ALTER PUBLICATION testpub_tbl9 DROP TABLE public.testpub_tbl7;
944 -- failure - cannot ADD column list and schema together
945 ALTER PUBLICATION testpub_tbl9 ADD TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a);
946 ERROR: cannot use column list for relation "public.testpub_tbl7" in publication "testpub_tbl9"
947 DETAIL: Column lists cannot be specified in publications containing FOR TABLES IN SCHEMA elements.
948 RESET client_min_messages;
949 DROP TABLE testpub_tbl5, testpub_tbl6, testpub_tbl7, testpub_tbl8, testpub_tbl8_1;
950 DROP PUBLICATION testpub_table_ins, testpub_fortable, testpub_fortable_insert, testpub_col_list, testpub_tbl9;
951 -- ======================================================
952 -- Test combination of column list and row filter
953 SET client_min_messages = 'ERROR';
954 CREATE PUBLICATION testpub_both_filters;
955 RESET client_min_messages;
956 CREATE TABLE testpub_tbl_both_filters (a int, b int, c int, PRIMARY KEY (a,c));
957 ALTER TABLE testpub_tbl_both_filters REPLICA IDENTITY USING INDEX testpub_tbl_both_filters_pkey;
958 ALTER PUBLICATION testpub_both_filters ADD TABLE testpub_tbl_both_filters (a,c) WHERE (c != 1);
959 \dRp+ testpub_both_filters
960 Publication testpub_both_filters
961 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
962 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
963 regress_publication_user | f | t | t | t | t | f | f
964 Tables:
965 "public.testpub_tbl_both_filters" (a, c) WHERE (c <> 1)
967 \d+ testpub_tbl_both_filters
968 Table "public.testpub_tbl_both_filters"
969 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
970 --------+---------+-----------+----------+---------+---------+--------------+-------------
971 a | integer | | not null | | plain | |
972 b | integer | | | | plain | |
973 c | integer | | not null | | plain | |
974 Indexes:
975 "testpub_tbl_both_filters_pkey" PRIMARY KEY, btree (a, c) REPLICA IDENTITY
976 Publications:
977 "testpub_both_filters" (a, c) WHERE (c <> 1)
978 Not-null constraints:
979 "testpub_tbl_both_filters_a_not_null" NOT NULL "a"
980 "testpub_tbl_both_filters_c_not_null" NOT NULL "c"
982 DROP TABLE testpub_tbl_both_filters;
983 DROP PUBLICATION testpub_both_filters;
984 -- ======================================================
985 -- More column list tests for validating column references
986 CREATE TABLE rf_tbl_abcd_nopk(a int, b int, c int, d int);
987 CREATE TABLE rf_tbl_abcd_pk(a int, b int, c int, d int, PRIMARY KEY(a,b));
988 CREATE TABLE rf_tbl_abcd_part_pk (a int PRIMARY KEY, b int) PARTITION by RANGE (a);
989 CREATE TABLE rf_tbl_abcd_part_pk_1 (b int, a int PRIMARY KEY);
990 ALTER TABLE rf_tbl_abcd_part_pk ATTACH PARTITION rf_tbl_abcd_part_pk_1 FOR VALUES FROM (1) TO (10);
991 -- Case 1. REPLICA IDENTITY DEFAULT (means use primary key or nothing)
992 -- 1a. REPLICA IDENTITY is DEFAULT and table has a PK.
993 SET client_min_messages = 'ERROR';
994 CREATE PUBLICATION testpub6 FOR TABLE rf_tbl_abcd_pk (a, b);
995 RESET client_min_messages;
996 -- ok - (a,b) covers all PK cols
997 UPDATE rf_tbl_abcd_pk SET a = 1;
998 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (a, b, c);
999 -- ok - (a,b,c) covers all PK cols
1000 UPDATE rf_tbl_abcd_pk SET a = 1;
1001 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (a);
1002 -- fail - "b" is missing from the column list
1003 UPDATE rf_tbl_abcd_pk SET a = 1;
1004 ERROR: cannot update table "rf_tbl_abcd_pk"
1005 DETAIL: Column list used by the publication does not cover the replica identity.
1006 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (b);
1007 -- fail - "a" is missing from the column list
1008 UPDATE rf_tbl_abcd_pk SET a = 1;
1009 ERROR: cannot update table "rf_tbl_abcd_pk"
1010 DETAIL: Column list used by the publication does not cover the replica identity.
1011 -- 1b. REPLICA IDENTITY is DEFAULT and table has no PK
1012 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (a);
1013 -- ok - there's no replica identity, so any column list works
1014 -- note: it fails anyway, just a bit later because UPDATE requires RI
1015 UPDATE rf_tbl_abcd_nopk SET a = 1;
1016 ERROR: cannot update table "rf_tbl_abcd_nopk" because it does not have a replica identity and publishes updates
1017 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1018 -- Case 2. REPLICA IDENTITY FULL
1019 ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY FULL;
1020 ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY FULL;
1021 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (c);
1022 -- fail - with REPLICA IDENTITY FULL no column list is allowed
1023 UPDATE rf_tbl_abcd_pk SET a = 1;
1024 ERROR: cannot update table "rf_tbl_abcd_pk"
1025 DETAIL: Column list used by the publication does not cover the replica identity.
1026 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (a, b, c, d);
1027 -- fail - with REPLICA IDENTITY FULL no column list is allowed
1028 UPDATE rf_tbl_abcd_nopk SET a = 1;
1029 ERROR: cannot update table "rf_tbl_abcd_nopk"
1030 DETAIL: Column list used by the publication does not cover the replica identity.
1031 -- Case 3. REPLICA IDENTITY NOTHING
1032 ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY NOTHING;
1033 ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY NOTHING;
1034 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (a);
1035 -- ok - REPLICA IDENTITY NOTHING means all column lists are valid
1036 -- it still fails later because without RI we can't replicate updates
1037 UPDATE rf_tbl_abcd_pk SET a = 1;
1038 ERROR: cannot update table "rf_tbl_abcd_pk" because it does not have a replica identity and publishes updates
1039 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1040 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (a, b, c, d);
1041 -- ok - REPLICA IDENTITY NOTHING means all column lists are valid
1042 -- it still fails later because without RI we can't replicate updates
1043 UPDATE rf_tbl_abcd_pk SET a = 1;
1044 ERROR: cannot update table "rf_tbl_abcd_pk" because it does not have a replica identity and publishes updates
1045 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1046 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (d);
1047 -- ok - REPLICA IDENTITY NOTHING means all column lists are valid
1048 -- it still fails later because without RI we can't replicate updates
1049 UPDATE rf_tbl_abcd_nopk SET a = 1;
1050 ERROR: cannot update table "rf_tbl_abcd_nopk" because it does not have a replica identity and publishes updates
1051 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1052 -- Case 4. REPLICA IDENTITY INDEX
1053 ALTER TABLE rf_tbl_abcd_pk ALTER COLUMN c SET NOT NULL;
1054 CREATE UNIQUE INDEX idx_abcd_pk_c ON rf_tbl_abcd_pk(c);
1055 ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY USING INDEX idx_abcd_pk_c;
1056 ALTER TABLE rf_tbl_abcd_nopk ALTER COLUMN c SET NOT NULL;
1057 CREATE UNIQUE INDEX idx_abcd_nopk_c ON rf_tbl_abcd_nopk(c);
1058 ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY USING INDEX idx_abcd_nopk_c;
1059 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (a);
1060 -- fail - column list "a" does not cover the REPLICA IDENTITY INDEX on "c"
1061 UPDATE rf_tbl_abcd_pk SET a = 1;
1062 ERROR: cannot update table "rf_tbl_abcd_pk"
1063 DETAIL: Column list used by the publication does not cover the replica identity.
1064 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (c);
1065 -- ok - column list "c" does cover the REPLICA IDENTITY INDEX on "c"
1066 UPDATE rf_tbl_abcd_pk SET a = 1;
1067 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (a);
1068 -- fail - column list "a" does not cover the REPLICA IDENTITY INDEX on "c"
1069 UPDATE rf_tbl_abcd_nopk SET a = 1;
1070 ERROR: cannot update table "rf_tbl_abcd_nopk"
1071 DETAIL: Column list used by the publication does not cover the replica identity.
1072 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (c);
1073 -- ok - column list "c" does cover the REPLICA IDENTITY INDEX on "c"
1074 UPDATE rf_tbl_abcd_nopk SET a = 1;
1075 -- Tests for partitioned table
1076 -- set PUBLISH_VIA_PARTITION_ROOT to false and test column list for partitioned
1077 -- table
1078 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0);
1079 -- fail - cannot use column list for partitioned table
1080 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk (a);
1081 ERROR: cannot use column list for relation "public.rf_tbl_abcd_part_pk" in publication "testpub6"
1082 DETAIL: Column lists cannot be specified for partitioned tables when publish_via_partition_root is false.
1083 -- ok - can use column list for partition
1084 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk_1 (a);
1085 -- ok - "a" is a PK col
1086 UPDATE rf_tbl_abcd_part_pk SET a = 1;
1087 -- set PUBLISH_VIA_PARTITION_ROOT to true and test column list for partitioned
1088 -- table
1089 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=1);
1090 -- ok - can use column list for partitioned table
1091 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk (a);
1092 -- ok - "a" is a PK col
1093 UPDATE rf_tbl_abcd_part_pk SET a = 1;
1094 -- fail - cannot set PUBLISH_VIA_PARTITION_ROOT to false if any column list is
1095 -- used for partitioned table
1096 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0);
1097 ERROR: cannot set parameter "publish_via_partition_root" to false for publication "testpub6"
1098 DETAIL: The publication contains a column list for partitioned table "rf_tbl_abcd_part_pk", which is not allowed when "publish_via_partition_root" is false.
1099 -- remove partitioned table's column list
1100 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk;
1101 -- ok - we don't have column list for partitioned table.
1102 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0);
1103 -- Now change the root column list to use a column "b"
1104 -- (which is not in the replica identity)
1105 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk_1 (b);
1106 -- ok - we don't have column list for partitioned table.
1107 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0);
1108 -- fail - "b" is not in REPLICA IDENTITY INDEX
1109 UPDATE rf_tbl_abcd_part_pk SET a = 1;
1110 ERROR: cannot update table "rf_tbl_abcd_part_pk_1"
1111 DETAIL: Column list used by the publication does not cover the replica identity.
1112 -- set PUBLISH_VIA_PARTITION_ROOT to true
1113 -- can use column list for partitioned table
1114 ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=1);
1115 -- ok - can use column list for partitioned table
1116 ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk (b);
1117 -- fail - "b" is not in REPLICA IDENTITY INDEX
1118 UPDATE rf_tbl_abcd_part_pk SET a = 1;
1119 ERROR: cannot update table "rf_tbl_abcd_part_pk_1"
1120 DETAIL: Column list used by the publication does not cover the replica identity.
1121 DROP PUBLICATION testpub6;
1122 DROP TABLE rf_tbl_abcd_pk;
1123 DROP TABLE rf_tbl_abcd_nopk;
1124 DROP TABLE rf_tbl_abcd_part_pk;
1125 -- ======================================================
1126 -- Test cache invalidation FOR ALL TABLES publication
1127 SET client_min_messages = 'ERROR';
1128 CREATE TABLE testpub_tbl4(a int);
1129 INSERT INTO testpub_tbl4 values(1);
1130 UPDATE testpub_tbl4 set a = 2;
1131 CREATE PUBLICATION testpub_foralltables FOR ALL TABLES;
1132 RESET client_min_messages;
1133 -- fail missing REPLICA IDENTITY
1134 UPDATE testpub_tbl4 set a = 3;
1135 ERROR: cannot update table "testpub_tbl4" because it does not have a replica identity and publishes updates
1136 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1137 DROP PUBLICATION testpub_foralltables;
1138 -- should pass after dropping the publication
1139 UPDATE testpub_tbl4 set a = 3;
1140 DROP TABLE testpub_tbl4;
1141 -- fail - view
1142 CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view;
1143 ERROR: cannot add relation "testpub_view" to publication
1144 DETAIL: This operation is not supported for views.
1145 CREATE TEMPORARY TABLE testpub_temptbl(a int);
1146 -- fail - temporary table
1147 CREATE PUBLICATION testpub_fortemptbl FOR TABLE testpub_temptbl;
1148 ERROR: cannot add relation "testpub_temptbl" to publication
1149 DETAIL: This operation is not supported for temporary tables.
1150 DROP TABLE testpub_temptbl;
1151 CREATE UNLOGGED TABLE testpub_unloggedtbl(a int);
1152 -- fail - unlogged table
1153 CREATE PUBLICATION testpub_forunloggedtbl FOR TABLE testpub_unloggedtbl;
1154 ERROR: cannot add relation "testpub_unloggedtbl" to publication
1155 DETAIL: This operation is not supported for unlogged tables.
1156 DROP TABLE testpub_unloggedtbl;
1157 -- fail - system table
1158 CREATE PUBLICATION testpub_forsystemtbl FOR TABLE pg_publication;
1159 ERROR: cannot add relation "pg_publication" to publication
1160 DETAIL: This operation is not supported for system tables.
1161 SET client_min_messages = 'ERROR';
1162 CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk;
1163 RESET client_min_messages;
1164 -- fail - already added
1165 ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1;
1166 ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl"
1167 -- fail - already added
1168 CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1;
1169 ERROR: publication "testpub_fortbl" already exists
1170 \dRp+ testpub_fortbl
1171 Publication testpub_fortbl
1172 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1173 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1174 regress_publication_user | f | t | t | t | t | f | f
1175 Tables:
1176 "pub_test.testpub_nopk"
1177 "public.testpub_tbl1"
1179 -- fail - view
1180 ALTER PUBLICATION testpub_default ADD TABLE testpub_view;
1181 ERROR: cannot add relation "testpub_view" to publication
1182 DETAIL: This operation is not supported for views.
1183 ALTER PUBLICATION testpub_default ADD TABLE testpub_tbl1;
1184 ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1;
1185 ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk;
1186 ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1;
1187 \d+ pub_test.testpub_nopk
1188 Table "pub_test.testpub_nopk"
1189 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1190 --------+---------+-----------+----------+---------+---------+--------------+-------------
1191 foo | integer | | | | plain | |
1192 bar | integer | | | | plain | |
1193 Publications:
1194 "testpib_ins_trunct"
1195 "testpub_default"
1196 "testpub_fortbl"
1198 \d+ testpub_tbl1
1199 Table "public.testpub_tbl1"
1200 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1201 --------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
1202 id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | |
1203 data | text | | | | extended | |
1204 Indexes:
1205 "testpub_tbl1_pkey" PRIMARY KEY, btree (id)
1206 Publications:
1207 "testpib_ins_trunct"
1208 "testpub_default"
1209 "testpub_fortbl"
1210 Not-null constraints:
1211 "testpub_tbl1_id_not_null" NOT NULL "id"
1213 \dRp+ testpub_default
1214 Publication testpub_default
1215 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1216 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1217 regress_publication_user | f | t | t | t | f | f | f
1218 Tables:
1219 "pub_test.testpub_nopk"
1220 "public.testpub_tbl1"
1222 ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk;
1223 -- fail - nonexistent
1224 ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk;
1225 ERROR: relation "testpub_nopk" is not part of the publication
1226 \d+ testpub_tbl1
1227 Table "public.testpub_tbl1"
1228 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1229 --------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
1230 id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | |
1231 data | text | | | | extended | |
1232 Indexes:
1233 "testpub_tbl1_pkey" PRIMARY KEY, btree (id)
1234 Publications:
1235 "testpib_ins_trunct"
1236 "testpub_fortbl"
1237 Not-null constraints:
1238 "testpub_tbl1_id_not_null" NOT NULL "id"
1240 -- verify relation cache invalidation when a primary key is added using
1241 -- an existing index
1242 CREATE TABLE pub_test.testpub_addpk (id int not null, data int);
1243 ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_addpk;
1244 INSERT INTO pub_test.testpub_addpk VALUES(1, 11);
1245 CREATE UNIQUE INDEX testpub_addpk_id_idx ON pub_test.testpub_addpk(id);
1246 -- fail:
1247 UPDATE pub_test.testpub_addpk SET id = 2;
1248 ERROR: cannot update table "testpub_addpk" because it does not have a replica identity and publishes updates
1249 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1250 ALTER TABLE pub_test.testpub_addpk ADD PRIMARY KEY USING INDEX testpub_addpk_id_idx;
1251 -- now it should work:
1252 UPDATE pub_test.testpub_addpk SET id = 2;
1253 DROP TABLE pub_test.testpub_addpk;
1254 -- permissions
1255 SET ROLE regress_publication_user2;
1256 CREATE PUBLICATION testpub2; -- fail
1257 ERROR: permission denied for database regression
1258 SET ROLE regress_publication_user;
1259 GRANT CREATE ON DATABASE regression TO regress_publication_user2;
1260 SET ROLE regress_publication_user2;
1261 SET client_min_messages = 'ERROR';
1262 CREATE PUBLICATION testpub2; -- ok
1263 CREATE PUBLICATION testpub3 FOR TABLES IN SCHEMA pub_test; -- fail
1264 ERROR: must be superuser to create FOR TABLES IN SCHEMA publication
1265 CREATE PUBLICATION testpub3; -- ok
1266 RESET client_min_messages;
1267 ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail
1268 ERROR: must be owner of table testpub_tbl1
1269 ALTER PUBLICATION testpub3 ADD TABLES IN SCHEMA pub_test; -- fail
1270 ERROR: must be superuser to add or set schemas
1271 SET ROLE regress_publication_user;
1272 GRANT regress_publication_user TO regress_publication_user2;
1273 SET ROLE regress_publication_user2;
1274 ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok
1275 DROP PUBLICATION testpub2;
1276 DROP PUBLICATION testpub3;
1277 SET ROLE regress_publication_user;
1278 CREATE ROLE regress_publication_user3;
1279 GRANT regress_publication_user2 TO regress_publication_user3;
1280 SET client_min_messages = 'ERROR';
1281 CREATE PUBLICATION testpub4 FOR TABLES IN SCHEMA pub_test;
1282 RESET client_min_messages;
1283 ALTER PUBLICATION testpub4 OWNER TO regress_publication_user3;
1284 SET ROLE regress_publication_user3;
1285 -- fail - new owner must be superuser
1286 ALTER PUBLICATION testpub4 owner to regress_publication_user2; -- fail
1287 ERROR: permission denied to change owner of publication "testpub4"
1288 HINT: The owner of a FOR TABLES IN SCHEMA publication must be a superuser.
1289 ALTER PUBLICATION testpub4 owner to regress_publication_user; -- ok
1290 SET ROLE regress_publication_user;
1291 DROP PUBLICATION testpub4;
1292 DROP ROLE regress_publication_user3;
1293 REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
1294 DROP TABLE testpub_parted;
1295 DROP TABLE testpub_tbl1;
1296 \dRp+ testpub_default
1297 Publication testpub_default
1298 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1299 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1300 regress_publication_user | f | t | t | t | f | f | f
1301 (1 row)
1303 -- fail - must be owner of publication
1304 SET ROLE regress_publication_user_dummy;
1305 ALTER PUBLICATION testpub_default RENAME TO testpub_dummy;
1306 ERROR: must be owner of publication testpub_default
1307 RESET ROLE;
1308 ALTER PUBLICATION testpub_default RENAME TO testpub_foo;
1309 \dRp testpub_foo
1310 List of publications
1311 Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1312 -------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1313 testpub_foo | regress_publication_user | f | t | t | t | f | f | f
1314 (1 row)
1316 -- rename back to keep the rest simple
1317 ALTER PUBLICATION testpub_foo RENAME TO testpub_default;
1318 ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2;
1319 \dRp testpub_default
1320 List of publications
1321 Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1322 -----------------+---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1323 testpub_default | regress_publication_user2 | f | t | t | t | f | f | f
1324 (1 row)
1326 -- adding schemas and tables
1327 CREATE SCHEMA pub_test1;
1328 CREATE SCHEMA pub_test2;
1329 CREATE SCHEMA pub_test3;
1330 CREATE SCHEMA "CURRENT_SCHEMA";
1331 CREATE TABLE pub_test1.tbl (id int, data text);
1332 CREATE TABLE pub_test1.tbl1 (id serial primary key, data text);
1333 CREATE TABLE pub_test2.tbl1 (id serial primary key, data text);
1334 CREATE TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"(id int);
1335 -- suppress warning that depends on wal_level
1336 SET client_min_messages = 'ERROR';
1337 CREATE PUBLICATION testpub1_forschema FOR TABLES IN SCHEMA pub_test1;
1338 \dRp+ testpub1_forschema
1339 Publication testpub1_forschema
1340 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1341 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1342 regress_publication_user | f | t | t | t | t | f | f
1343 Tables from schemas:
1344 "pub_test1"
1346 CREATE PUBLICATION testpub2_forschema FOR TABLES IN SCHEMA pub_test1, pub_test2, pub_test3;
1347 \dRp+ testpub2_forschema
1348 Publication testpub2_forschema
1349 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1350 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1351 regress_publication_user | f | t | t | t | t | f | f
1352 Tables from schemas:
1353 "pub_test1"
1354 "pub_test2"
1355 "pub_test3"
1357 -- check create publication on CURRENT_SCHEMA
1358 CREATE PUBLICATION testpub3_forschema FOR TABLES IN SCHEMA CURRENT_SCHEMA;
1359 CREATE PUBLICATION testpub4_forschema FOR TABLES IN SCHEMA "CURRENT_SCHEMA";
1360 CREATE PUBLICATION testpub5_forschema FOR TABLES IN SCHEMA CURRENT_SCHEMA, "CURRENT_SCHEMA";
1361 CREATE PUBLICATION testpub6_forschema FOR TABLES IN SCHEMA "CURRENT_SCHEMA", CURRENT_SCHEMA;
1362 CREATE PUBLICATION testpub_fortable FOR TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA";
1363 RESET client_min_messages;
1364 \dRp+ testpub3_forschema
1365 Publication testpub3_forschema
1366 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1367 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1368 regress_publication_user | f | t | t | t | t | f | f
1369 Tables from schemas:
1370 "public"
1372 \dRp+ testpub4_forschema
1373 Publication testpub4_forschema
1374 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1375 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1376 regress_publication_user | f | t | t | t | t | f | f
1377 Tables from schemas:
1378 "CURRENT_SCHEMA"
1380 \dRp+ testpub5_forschema
1381 Publication testpub5_forschema
1382 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1383 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1384 regress_publication_user | f | t | t | t | t | f | f
1385 Tables from schemas:
1386 "CURRENT_SCHEMA"
1387 "public"
1389 \dRp+ testpub6_forschema
1390 Publication testpub6_forschema
1391 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1392 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1393 regress_publication_user | f | t | t | t | t | f | f
1394 Tables from schemas:
1395 "CURRENT_SCHEMA"
1396 "public"
1398 \dRp+ testpub_fortable
1399 Publication testpub_fortable
1400 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1401 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1402 regress_publication_user | f | t | t | t | t | f | f
1403 Tables:
1404 "CURRENT_SCHEMA.CURRENT_SCHEMA"
1406 -- check create publication on CURRENT_SCHEMA where search_path is not set
1407 SET SEARCH_PATH='';
1408 CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA CURRENT_SCHEMA;
1409 ERROR: no schema has been selected for CURRENT_SCHEMA
1410 RESET SEARCH_PATH;
1411 -- check create publication on CURRENT_SCHEMA where TABLE/TABLES in SCHEMA
1412 -- is not specified
1413 CREATE PUBLICATION testpub_forschema1 FOR CURRENT_SCHEMA;
1414 ERROR: invalid publication object list
1415 LINE 1: CREATE PUBLICATION testpub_forschema1 FOR CURRENT_SCHEMA;
1416 ^
1417 DETAIL: One of TABLE or TABLES IN SCHEMA must be specified before a standalone table or schema name.
1418 -- check create publication on CURRENT_SCHEMA along with FOR TABLE
1419 CREATE PUBLICATION testpub_forschema1 FOR TABLE CURRENT_SCHEMA;
1420 ERROR: syntax error at or near "CURRENT_SCHEMA"
1421 LINE 1: CREATE PUBLICATION testpub_forschema1 FOR TABLE CURRENT_SCHE...
1422 ^
1423 -- check create publication on a schema that does not exist
1424 CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA non_existent_schema;
1425 ERROR: schema "non_existent_schema" does not exist
1426 -- check create publication on a system schema
1427 CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pg_catalog;
1428 ERROR: cannot add schema "pg_catalog" to publication
1429 DETAIL: This operation is not supported for system schemas.
1430 -- check create publication on an object which is not schema
1431 CREATE PUBLICATION testpub1_forschema1 FOR TABLES IN SCHEMA testpub_view;
1432 ERROR: schema "testpub_view" does not exist
1433 -- dropping the schema should reflect the change in publication
1434 DROP SCHEMA pub_test3;
1435 \dRp+ testpub2_forschema
1436 Publication testpub2_forschema
1437 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1438 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1439 regress_publication_user | f | t | t | t | t | f | f
1440 Tables from schemas:
1441 "pub_test1"
1442 "pub_test2"
1444 -- renaming the schema should reflect the change in publication
1445 ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed;
1446 \dRp+ testpub2_forschema
1447 Publication testpub2_forschema
1448 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1449 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1450 regress_publication_user | f | t | t | t | t | f | f
1451 Tables from schemas:
1452 "pub_test1_renamed"
1453 "pub_test2"
1455 ALTER SCHEMA pub_test1_renamed RENAME to pub_test1;
1456 \dRp+ testpub2_forschema
1457 Publication testpub2_forschema
1458 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1459 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1460 regress_publication_user | f | t | t | t | t | f | f
1461 Tables from schemas:
1462 "pub_test1"
1463 "pub_test2"
1465 -- alter publication add schema
1466 ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test2;
1467 \dRp+ testpub1_forschema
1468 Publication testpub1_forschema
1469 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1470 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1471 regress_publication_user | f | t | t | t | t | f | f
1472 Tables from schemas:
1473 "pub_test1"
1474 "pub_test2"
1476 -- add non existent schema
1477 ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA non_existent_schema;
1478 ERROR: schema "non_existent_schema" does not exist
1479 \dRp+ testpub1_forschema
1480 Publication testpub1_forschema
1481 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1482 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1483 regress_publication_user | f | t | t | t | t | f | f
1484 Tables from schemas:
1485 "pub_test1"
1486 "pub_test2"
1488 -- add a schema which is already added to the publication
1489 ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test1;
1490 ERROR: schema "pub_test1" is already member of publication "testpub1_forschema"
1491 \dRp+ testpub1_forschema
1492 Publication testpub1_forschema
1493 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1494 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1495 regress_publication_user | f | t | t | t | t | f | f
1496 Tables from schemas:
1497 "pub_test1"
1498 "pub_test2"
1500 -- alter publication drop schema
1501 ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2;
1502 \dRp+ testpub1_forschema
1503 Publication testpub1_forschema
1504 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1505 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1506 regress_publication_user | f | t | t | t | t | f | f
1507 Tables from schemas:
1508 "pub_test1"
1510 -- drop schema that is not present in the publication
1511 ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2;
1512 ERROR: tables from schema "pub_test2" are not part of the publication
1513 \dRp+ testpub1_forschema
1514 Publication testpub1_forschema
1515 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1516 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1517 regress_publication_user | f | t | t | t | t | f | f
1518 Tables from schemas:
1519 "pub_test1"
1521 -- drop a schema that does not exist in the system
1522 ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA non_existent_schema;
1523 ERROR: schema "non_existent_schema" does not exist
1524 \dRp+ testpub1_forschema
1525 Publication testpub1_forschema
1526 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1527 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1528 regress_publication_user | f | t | t | t | t | f | f
1529 Tables from schemas:
1530 "pub_test1"
1532 -- drop all schemas
1533 ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test1;
1534 \dRp+ testpub1_forschema
1535 Publication testpub1_forschema
1536 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1537 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1538 regress_publication_user | f | t | t | t | t | f | f
1539 (1 row)
1541 -- alter publication set multiple schema
1542 ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test2;
1543 \dRp+ testpub1_forschema
1544 Publication testpub1_forschema
1545 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1546 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1547 regress_publication_user | f | t | t | t | t | f | f
1548 Tables from schemas:
1549 "pub_test1"
1550 "pub_test2"
1552 -- alter publication set non-existent schema
1553 ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA non_existent_schema;
1554 ERROR: schema "non_existent_schema" does not exist
1555 \dRp+ testpub1_forschema
1556 Publication testpub1_forschema
1557 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1558 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1559 regress_publication_user | f | t | t | t | t | f | f
1560 Tables from schemas:
1561 "pub_test1"
1562 "pub_test2"
1564 -- alter publication set it duplicate schemas should set the schemas after
1565 -- removing the duplicate schemas
1566 ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test1;
1567 \dRp+ testpub1_forschema
1568 Publication testpub1_forschema
1569 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1570 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1571 regress_publication_user | f | t | t | t | t | f | f
1572 Tables from schemas:
1573 "pub_test1"
1575 -- Verify that it fails to add a schema with a column specification
1576 ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA foo (a, b);
1577 ERROR: syntax error at or near "("
1578 LINE 1: ...LICATION testpub1_forschema ADD TABLES IN SCHEMA foo (a, b);
1579 ^
1580 ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA foo, bar (a, b);
1581 ERROR: column specification not allowed for schema
1582 LINE 1: ...TION testpub1_forschema ADD TABLES IN SCHEMA foo, bar (a, b)...
1583 ^
1584 -- cleanup pub_test1 schema for invalidation tests
1585 ALTER PUBLICATION testpub2_forschema DROP TABLES IN SCHEMA pub_test1;
1586 DROP PUBLICATION testpub3_forschema, testpub4_forschema, testpub5_forschema, testpub6_forschema, testpub_fortable;
1587 DROP SCHEMA "CURRENT_SCHEMA" CASCADE;
1588 NOTICE: drop cascades to table "CURRENT_SCHEMA"."CURRENT_SCHEMA"
1589 -- verify relation cache invalidations through update statement for the
1590 -- default REPLICA IDENTITY on the relation, if schema is part of the
1591 -- publication then update will fail because relation's relreplident
1592 -- option will be set, if schema is not part of the publication then update
1593 -- will be successful.
1594 INSERT INTO pub_test1.tbl VALUES(1, 'test');
1595 -- fail
1596 UPDATE pub_test1.tbl SET id = 2;
1597 ERROR: cannot update table "tbl" because it does not have a replica identity and publishes updates
1598 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1599 ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test1;
1600 -- success
1601 UPDATE pub_test1.tbl SET id = 2;
1602 ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1;
1603 -- fail
1604 UPDATE pub_test1.tbl SET id = 2;
1605 ERROR: cannot update table "tbl" because it does not have a replica identity and publishes updates
1606 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1607 -- verify invalidation of partition table having parent and child tables in
1608 -- different schema
1609 CREATE SCHEMA pub_testpart1;
1610 CREATE SCHEMA pub_testpart2;
1611 CREATE TABLE pub_testpart1.parent1 (a int) partition by list (a);
1612 CREATE TABLE pub_testpart2.child_parent1 partition of pub_testpart1.parent1 for values in (1);
1613 INSERT INTO pub_testpart2.child_parent1 values(1);
1614 UPDATE pub_testpart2.child_parent1 set a = 1;
1615 SET client_min_messages = 'ERROR';
1616 CREATE PUBLICATION testpubpart_forschema FOR TABLES IN SCHEMA pub_testpart1;
1617 RESET client_min_messages;
1618 -- fail
1619 UPDATE pub_testpart1.parent1 set a = 1;
1620 ERROR: cannot update table "child_parent1" because it does not have a replica identity and publishes updates
1621 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1622 UPDATE pub_testpart2.child_parent1 set a = 1;
1623 ERROR: cannot update table "child_parent1" because it does not have a replica identity and publishes updates
1624 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1625 DROP PUBLICATION testpubpart_forschema;
1626 -- verify invalidation of partition tables for schema publication that has
1627 -- parent and child tables of different partition hierarchies
1628 CREATE TABLE pub_testpart2.parent2 (a int) partition by list (a);
1629 CREATE TABLE pub_testpart1.child_parent2 partition of pub_testpart2.parent2 for values in (1);
1630 INSERT INTO pub_testpart1.child_parent2 values(1);
1631 UPDATE pub_testpart1.child_parent2 set a = 1;
1632 SET client_min_messages = 'ERROR';
1633 CREATE PUBLICATION testpubpart_forschema FOR TABLES IN SCHEMA pub_testpart2;
1634 RESET client_min_messages;
1635 -- fail
1636 UPDATE pub_testpart2.child_parent1 set a = 1;
1637 ERROR: cannot update table "child_parent1" because it does not have a replica identity and publishes updates
1638 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1639 UPDATE pub_testpart2.parent2 set a = 1;
1640 ERROR: cannot update table "child_parent2" because it does not have a replica identity and publishes updates
1641 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1642 UPDATE pub_testpart1.child_parent2 set a = 1;
1643 ERROR: cannot update table "child_parent2" because it does not have a replica identity and publishes updates
1644 HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
1645 -- alter publication set 'TABLES IN SCHEMA' on an empty publication.
1646 SET client_min_messages = 'ERROR';
1647 CREATE PUBLICATION testpub3_forschema;
1648 RESET client_min_messages;
1649 \dRp+ testpub3_forschema
1650 Publication testpub3_forschema
1651 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1652 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1653 regress_publication_user | f | t | t | t | t | f | f
1654 (1 row)
1656 ALTER PUBLICATION testpub3_forschema SET TABLES IN SCHEMA pub_test1;
1657 \dRp+ testpub3_forschema
1658 Publication testpub3_forschema
1659 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1660 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1661 regress_publication_user | f | t | t | t | t | f | f
1662 Tables from schemas:
1663 "pub_test1"
1665 -- create publication including both 'FOR TABLE' and 'FOR TABLES IN SCHEMA'
1666 SET client_min_messages = 'ERROR';
1667 CREATE PUBLICATION testpub_forschema_fortable FOR TABLES IN SCHEMA pub_test1, TABLE pub_test2.tbl1;
1668 CREATE PUBLICATION testpub_fortable_forschema FOR TABLE pub_test2.tbl1, TABLES IN SCHEMA pub_test1;
1669 RESET client_min_messages;
1670 \dRp+ testpub_forschema_fortable
1671 Publication testpub_forschema_fortable
1672 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1673 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1674 regress_publication_user | f | t | t | t | t | f | f
1675 Tables:
1676 "pub_test2.tbl1"
1677 Tables from schemas:
1678 "pub_test1"
1680 \dRp+ testpub_fortable_forschema
1681 Publication testpub_fortable_forschema
1682 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1683 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1684 regress_publication_user | f | t | t | t | t | f | f
1685 Tables:
1686 "pub_test2.tbl1"
1687 Tables from schemas:
1688 "pub_test1"
1690 -- fail specifying table without any of 'FOR TABLES IN SCHEMA' or
1691 --'FOR TABLE' or 'FOR ALL TABLES'
1692 CREATE PUBLICATION testpub_error FOR pub_test2.tbl1;
1693 ERROR: invalid publication object list
1694 LINE 1: CREATE PUBLICATION testpub_error FOR pub_test2.tbl1;
1695 ^
1696 DETAIL: One of TABLE or TABLES IN SCHEMA must be specified before a standalone table or schema name.
1697 DROP VIEW testpub_view;
1698 DROP PUBLICATION testpub_default;
1699 DROP PUBLICATION testpib_ins_trunct;
1700 DROP PUBLICATION testpub_fortbl;
1701 DROP PUBLICATION testpub1_forschema;
1702 DROP PUBLICATION testpub2_forschema;
1703 DROP PUBLICATION testpub3_forschema;
1704 DROP PUBLICATION testpub_forschema_fortable;
1705 DROP PUBLICATION testpub_fortable_forschema;
1706 DROP PUBLICATION testpubpart_forschema;
1707 DROP SCHEMA pub_test CASCADE;
1708 NOTICE: drop cascades to table pub_test.testpub_nopk
1709 DROP SCHEMA pub_test1 CASCADE;
1710 NOTICE: drop cascades to 2 other objects
1711 DETAIL: drop cascades to table pub_test1.tbl
1712 drop cascades to table pub_test1.tbl1
1713 DROP SCHEMA pub_test2 CASCADE;
1714 NOTICE: drop cascades to table pub_test2.tbl1
1715 DROP SCHEMA pub_testpart1 CASCADE;
1716 NOTICE: drop cascades to 2 other objects
1717 DETAIL: drop cascades to table pub_testpart1.parent1
1718 drop cascades to table pub_testpart1.child_parent2
1719 DROP SCHEMA pub_testpart2 CASCADE;
1720 NOTICE: drop cascades to table pub_testpart2.parent2
1721 -- Test the list of partitions published with or without
1722 -- 'PUBLISH_VIA_PARTITION_ROOT' parameter
1723 SET client_min_messages = 'ERROR';
1724 CREATE SCHEMA sch1;
1725 CREATE SCHEMA sch2;
1726 CREATE TABLE sch1.tbl1 (a int) PARTITION BY RANGE(a);
1727 CREATE TABLE sch2.tbl1_part1 PARTITION OF sch1.tbl1 FOR VALUES FROM (1) to (10);
1728 -- Schema publication that does not include the schema that has the parent table
1729 CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
1730 SELECT * FROM pg_publication_tables;
1731 pubname | schemaname | tablename | attnames | rowfilter
1732 ---------+------------+------------+----------+-----------
1733 pub | sch2 | tbl1_part1 | {a} |
1734 (1 row)
1736 DROP PUBLICATION pub;
1737 -- Table publication that does not include the parent table
1738 CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
1739 SELECT * FROM pg_publication_tables;
1740 pubname | schemaname | tablename | attnames | rowfilter
1741 ---------+------------+------------+----------+-----------
1742 pub | sch2 | tbl1_part1 | {a} |
1743 (1 row)
1745 -- Table publication that includes both the parent table and the child table
1746 ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
1747 SELECT * FROM pg_publication_tables;
1748 pubname | schemaname | tablename | attnames | rowfilter
1749 ---------+------------+-----------+----------+-----------
1750 pub | sch1 | tbl1 | {a} |
1751 (1 row)
1753 DROP PUBLICATION pub;
1754 -- Schema publication that does not include the schema that has the parent table
1755 CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=0);
1756 SELECT * FROM pg_publication_tables;
1757 pubname | schemaname | tablename | attnames | rowfilter
1758 ---------+------------+------------+----------+-----------
1759 pub | sch2 | tbl1_part1 | {a} |
1760 (1 row)
1762 DROP PUBLICATION pub;
1763 -- Table publication that does not include the parent table
1764 CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=0);
1765 SELECT * FROM pg_publication_tables;
1766 pubname | schemaname | tablename | attnames | rowfilter
1767 ---------+------------+------------+----------+-----------
1768 pub | sch2 | tbl1_part1 | {a} |
1769 (1 row)
1771 -- Table publication that includes both the parent table and the child table
1772 ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
1773 SELECT * FROM pg_publication_tables;
1774 pubname | schemaname | tablename | attnames | rowfilter
1775 ---------+------------+------------+----------+-----------
1776 pub | sch2 | tbl1_part1 | {a} |
1777 (1 row)
1779 DROP PUBLICATION pub;
1780 DROP TABLE sch2.tbl1_part1;
1781 DROP TABLE sch1.tbl1;
1782 CREATE TABLE sch1.tbl1 (a int) PARTITION BY RANGE(a);
1783 CREATE TABLE sch1.tbl1_part1 PARTITION OF sch1.tbl1 FOR VALUES FROM (1) to (10);
1784 CREATE TABLE sch1.tbl1_part2 PARTITION OF sch1.tbl1 FOR VALUES FROM (10) to (20);
1785 CREATE TABLE sch1.tbl1_part3 (a int) PARTITION BY RANGE(a);
1786 ALTER TABLE sch1.tbl1 ATTACH PARTITION sch1.tbl1_part3 FOR VALUES FROM (20) to (30);
1787 CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch1 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
1788 SELECT * FROM pg_publication_tables;
1789 pubname | schemaname | tablename | attnames | rowfilter
1790 ---------+------------+-----------+----------+-----------
1791 pub | sch1 | tbl1 | {a} |
1792 (1 row)
1794 RESET client_min_messages;
1795 DROP PUBLICATION pub;
1796 DROP TABLE sch1.tbl1;
1797 DROP SCHEMA sch1 cascade;
1798 DROP SCHEMA sch2 cascade;
1799 -- ======================================================
1800 -- Test the publication 'publish_generated_columns' parameter enabled or disabled
1801 SET client_min_messages = 'ERROR';
1802 CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_generated_columns=1);
1803 \dRp+ pub1
1804 Publication pub1
1805 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1806 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1807 regress_publication_user | t | t | t | t | t | t | f
1808 (1 row)
1810 CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_generated_columns=0);
1811 \dRp+ pub2
1812 Publication pub2
1813 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1814 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1815 regress_publication_user | t | t | t | t | t | f | f
1816 (1 row)
1818 DROP PUBLICATION pub1;
1819 DROP PUBLICATION pub2;
1820 -- Test the 'publish_generated_columns' parameter enabled or disabled for
1821 -- different scenarios with/without generated columns in column lists.
1822 CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
1823 -- Generated columns in column list, when 'publish_generated_columns'=false
1824 CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns=false);
1825 \dRp+ pub1
1826 Publication pub1
1827 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1828 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1829 regress_publication_user | f | t | t | t | t | f | f
1830 Tables:
1831 "public.gencols" (a, gen1)
1833 -- Generated columns in column list, when 'publish_generated_columns'=true
1834 CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns=true);
1835 \dRp+ pub2
1836 Publication pub2
1837 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1838 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1839 regress_publication_user | f | t | t | t | t | t | f
1840 Tables:
1841 "public.gencols" (a, gen1)
1843 -- Generated columns in column list, then set 'publication_generate_columns'=false
1844 ALTER PUBLICATION pub2 SET (publish_generated_columns = false);
1845 \dRp+ pub2
1846 Publication pub2
1847 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1848 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1849 regress_publication_user | f | t | t | t | t | f | f
1850 Tables:
1851 "public.gencols" (a, gen1)
1853 -- Remove generated columns from column list, when 'publish_generated_columns'=false
1854 ALTER PUBLICATION pub2 SET TABLE gencols(a);
1855 \dRp+ pub2
1856 Publication pub2
1857 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1858 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1859 regress_publication_user | f | t | t | t | t | f | f
1860 Tables:
1861 "public.gencols" (a)
1863 -- Add generated columns in column list, when 'publish_generated_columns'=false
1864 ALTER PUBLICATION pub2 SET TABLE gencols(a, gen1);
1865 \dRp+ pub2
1866 Publication pub2
1867 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1868 --------------------------+------------+---------+---------+---------+-----------+-------------------+----------
1869 regress_publication_user | f | t | t | t | t | f | f
1870 Tables:
1871 "public.gencols" (a, gen1)
1873 DROP PUBLICATION pub1;
1874 DROP PUBLICATION pub2;
1875 DROP TABLE gencols;
1876 RESET client_min_messages;
1877 RESET SESSION AUTHORIZATION;
1878 DROP ROLE regress_publication_user, regress_publication_user2;
1879 DROP ROLE regress_publication_user_dummy;