/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/regress/expected/publication.out
2 566 строк
143 KB
Daniel Gustafsson
Fix errmsg grammar for publication membership
10 авг 2026, 09:44
10 авг 2026, 09:44
6e89028
Код
Авторство
О чём код?
-- -- PUBLICATION -- CREATE ROLE regress_publication_user LOGIN SUPERUSER; CREATE ROLE regress_publication_user2; CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER; SET SESSION AUTHORIZATION 'regress_publication_user'; -- suppress warning that depends on wal_level SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_default; RESET client_min_messages; COMMENT ON PUBLICATION testpub_default IS 'test publication'; SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p; obj_description ------------------ test publication (1 row) SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_ins_trunct WITH (publish = insert); RESET client_min_messages; ALTER PUBLICATION testpub_default SET (publish = update); -- error cases CREATE PUBLICATION testpub_xxx WITH (foo); ERROR: unrecognized publication parameter: "foo" CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum'); ERROR: unrecognized value for publication option "publish": "cluster" CREATE PUBLICATION testpub_xxx WITH (publish_via_partition_root = 'true', publish_via_partition_root = '0'); ERROR: conflicting or redundant options LINE 1: ...ub_xxx WITH (publish_via_partition_root = 'true', publish_vi... ^ CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = stored, publish_generated_columns = none); ERROR: conflicting or redundant options LINE 1: ...pub_xxx WITH (publish_generated_columns = stored, publish_ge... ^ CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = foo); ERROR: invalid value for publication parameter "publish_generated_columns": "foo" DETAIL: Valid values are "none" and "stored". CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns); ERROR: invalid value for publication parameter "publish_generated_columns": "" DETAIL: Valid values are "none" and "stored". \dRp List of publications Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root --------------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- testpub_default | regress_publication_user | f | f | f | t | f | f | none | f testpub_ins_trunct | regress_publication_user | f | f | t | f | f | f | none | f (2 rows) ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete'); \dRp List of publications Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root --------------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- testpub_default | regress_publication_user | f | f | t | t | t | f | none | f testpub_ins_trunct | regress_publication_user | f | f | t | f | f | f | none | f (2 rows) --- adding tables CREATE SCHEMA pub_test; CREATE TABLE testpub_tbl1 (id serial primary key, data text); CREATE TABLE pub_test.testpub_nopk (foo int, bar int); CREATE VIEW testpub_view AS SELECT 1; CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a); SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert'); RESET client_min_messages; ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update'); CREATE TABLE testpub_tbl2 (id serial primary key, data text); -- fail - can't add to for all tables publication ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES DETAIL: Tables or sequences cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't drop from all tables publication ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES DETAIL: Tables or sequences cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't add to for all tables publication ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES DETAIL: Tables or sequences cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't add schema to 'FOR ALL TABLES' publication ALTER PUBLICATION testpub_foralltables ADD TABLES IN SCHEMA pub_test; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES DETAIL: Schemas cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't drop schema from 'FOR ALL TABLES' publication ALTER PUBLICATION testpub_foralltables DROP TABLES IN SCHEMA pub_test; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES DETAIL: Schemas cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't set schema to 'FOR ALL TABLES' publication ALTER PUBLICATION testpub_foralltables SET TABLES IN SCHEMA pub_test; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES DETAIL: Schemas cannot be added to or dropped from FOR ALL TABLES publications. SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_fortable FOR TABLE testpub_tbl1; RESET client_min_messages; -- should be able to add schema to 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable ADD TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable Publication testpub_fortable Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_tbl1" Tables from schemas: "pub_test" -- should be able to drop schema from 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable Publication testpub_fortable Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_tbl1" -- should be able to set schema to 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable Publication testpub_fortable Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test" SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test; \dn pub_test List of schemas Name | Owner ----------+-------------------------- pub_test | regress_publication_user Included in publications: "testpub_forschema" "testpub_fortable" -- should be able to create publication with schema and table of the same -- schema CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk; RESET client_min_messages; \dRp+ testpub_for_tbl_schema Publication testpub_for_tbl_schema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test" -- table also covered by a published schema should appear only once in \d output \d pub_test.testpub_nopk Table "pub_test.testpub_nopk" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- foo | integer | | | bar | integer | | | Included in publications: "testpub_for_tbl_schema" "testpub_foralltables" "testpub_forschema" "testpub_fortable" -- weird parser corner case CREATE PUBLICATION testpub_parsertst FOR TABLE pub_test.testpub_nopk, CURRENT_SCHEMA; ERROR: invalid table name LINE 1: ...estpub_parsertst FOR TABLE pub_test.testpub_nopk, CURRENT_SC... ^ CREATE PUBLICATION testpub_parsertst FOR TABLES IN SCHEMA foo, test.foo; ERROR: invalid schema name LINE 1: ...CATION testpub_parsertst FOR TABLES IN SCHEMA foo, test.foo; ^ -- should be able to add a table of the same schema to the schema publication ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema Publication testpub_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test" -- should be able to drop the table ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema Publication testpub_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test" -- fail - can't drop a table from the schema publication which isn't in the -- publication ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk; ERROR: relation "testpub_nopk" is not part of the publication -- should be able to set table to schema publication ALTER PUBLICATION testpub_forschema SET TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema Publication testpub_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "pub_test.testpub_nopk" SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables'; pubname | puballtables ----------------------+-------------- testpub_foralltables | t (1 row) \d+ testpub_tbl2 Table "public.testpub_tbl2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | plain | | data | text | | | | extended | | Indexes: "testpub_tbl2_pkey" PRIMARY KEY, btree (id) Included in publications: "testpub_foralltables" Not-null constraints: "testpub_tbl2_id_not_null" NOT NULL "id" \dRp+ testpub_foralltables Publication testpub_foralltables Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | f | f | none | f | (1 row) --------------------------------------------- -- EXCEPT clause tests for normal tables --------------------------------------------- SET client_min_messages = 'ERROR'; CREATE TABLE testpub_tbl3 (id serial primary key, data text); -- Specify table list in the EXCEPT clause of a FOR ALL TABLES publication CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT (TABLE testpub_tbl1, testpub_tbl2, TABLE testpub_tbl3); \dRp+ testpub_foralltables_excepttable Publication testpub_foralltables_excepttable Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | Except tables: "public.testpub_tbl1" "public.testpub_tbl2" "public.testpub_tbl3" -- Specify table in the EXCEPT clause of a FOR ALL TABLES publication CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (TABLE testpub_tbl1); \dRp+ testpub_foralltables_excepttable1 Publication testpub_foralltables_excepttable1 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | Except tables: "public.testpub_tbl1" -- Check that the table description shows the publications where it is listed -- in the EXCEPT clause \d testpub_tbl1 Table "public.testpub_tbl1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------------------ id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) data | text | | | Indexes: "testpub_tbl1_pkey" PRIMARY KEY, btree (id) Included in publications: "testpub_foralltables" Excluded from publications: "testpub_foralltables_excepttable" "testpub_foralltables_excepttable1" -- fail - first table in the EXCEPT list should use TABLE keyword CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tbl1, testpub_tbl2); ERROR: syntax error at or near "testpub_tbl1" LINE 1: ..._foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tb... ^ --------------------------------------------- -- SET ALL TABLES/SEQUENCES --------------------------------------------- -- Replace the existing table list in the EXCEPT clause (testpub_tbl1, -- testpub_tbl2, testpub_tbl3) with table (testpub_tbl2). ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT (TABLE testpub_tbl2); \dRp+ testpub_foralltables_excepttable Publication testpub_foralltables_excepttable Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | Except tables: "public.testpub_tbl2" -- Replace the existing table list in the EXCEPT clause (testpub_tbl2) with a -- table list containing (testpub_tbl1, testpub_tbl2, testpub_tbl3). ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT (TABLE testpub_tbl1, testpub_tbl2, TABLE testpub_tbl3); \dRp+ testpub_foralltables_excepttable Publication testpub_foralltables_excepttable Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | Except tables: "public.testpub_tbl1" "public.testpub_tbl2" "public.testpub_tbl3" -- Clear the table list in the EXCEPT clause, making the publication include all -- tables. ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES; \dRp+ testpub_foralltables_excepttable Publication testpub_foralltables_excepttable Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | (1 row) -- Create an empty publication for subsequent tests. CREATE PUBLICATION testpub_forall_tbls_seqs; -- Enable both puballtables and puballsequences ALTER PUBLICATION testpub_forall_tbls_seqs SET ALL TABLES, ALL SEQUENCES; \dRp+ testpub_forall_tbls_seqs Publication testpub_forall_tbls_seqs Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | t | t | t | t | t | none | f | (1 row) -- Explicitly test that SET ALL TABLES resets puballsequences to false -- Result should be: puballtables = true, puballsequences = false ALTER PUBLICATION testpub_forall_tbls_seqs SET ALL TABLES; \dRp+ testpub_forall_tbls_seqs Publication testpub_forall_tbls_seqs Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | (1 row) -- Explicitly test that SET ALL SEQUENCES resets puballtables to false -- Result should be: puballtables = false, puballsequences = true ALTER PUBLICATION testpub_forall_tbls_seqs SET ALL SEQUENCES; \dRp+ testpub_forall_tbls_seqs Publication testpub_forall_tbls_seqs Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | t | t | t | t | t | none | f | (1 row) -- fail - SET ALL TABLES/SEQUENCES is not allowed for a 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable SET ALL TABLES EXCEPT (TABLE testpub_tbl1); ERROR: publication "testpub_fortable" does not support ALL TABLES operations DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty. ALTER PUBLICATION testpub_fortable SET ALL TABLES; ERROR: publication "testpub_fortable" does not support ALL TABLES operations DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty. ALTER PUBLICATION testpub_fortable SET ALL SEQUENCES; ERROR: publication "testpub_fortable" does not support ALL SEQUENCES operations DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty. -- fail - SET ALL TABLES/SEQUENCES is not allowed for a schema publication ALTER PUBLICATION testpub_forschema SET ALL TABLES EXCEPT (TABLE pub_test.testpub_nopk); ERROR: publication "testpub_forschema" does not support ALL TABLES operations DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty. ALTER PUBLICATION testpub_forschema SET ALL TABLES; ERROR: publication "testpub_forschema" does not support ALL TABLES operations DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty. ALTER PUBLICATION testpub_forschema SET ALL SEQUENCES; ERROR: publication "testpub_forschema" does not support ALL SEQUENCES operations DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty. RESET client_min_messages; DROP TABLE testpub_tbl2, testpub_tbl3; DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema; DROP PUBLICATION testpub_forall_tbls_seqs, testpub_foralltables_excepttable, testpub_foralltables_excepttable1; --------------------------------------------- -- Tests for inherited tables, and -- EXCEPT clause tests for inherited tables --------------------------------------------- SET client_min_messages = 'ERROR'; CREATE TABLE testpub_tbl_parent (a int); CREATE TABLE testpub_tbl_child (b text) INHERITS (testpub_tbl_parent); CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl_parent; \dRp+ testpub3 Publication testpub3 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_tbl_child" "public.testpub_tbl_parent" CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl_parent; \dRp+ testpub4 Publication testpub4 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_tbl_parent" -- List the parent table in the EXCEPT clause (without ONLY or '*') CREATE PUBLICATION testpub5 FOR ALL TABLES EXCEPT (TABLE testpub_tbl_parent); \dRp+ testpub5 Publication testpub5 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | Except tables: "public.testpub_tbl_child" "public.testpub_tbl_parent" -- EXCEPT with '*': list the table and all its descendants in the EXCEPT clause CREATE PUBLICATION testpub6 FOR ALL TABLES EXCEPT (TABLE testpub_tbl_parent *); \dRp+ testpub6 Publication testpub6 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | Except tables: "public.testpub_tbl_child" "public.testpub_tbl_parent" -- EXCEPT with ONLY: list the table in the EXCEPT clause, but not its descendants CREATE PUBLICATION testpub7 FOR ALL TABLES EXCEPT (TABLE ONLY testpub_tbl_parent); \dRp+ testpub7 Publication testpub7 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | Except tables: "public.testpub_tbl_parent" RESET client_min_messages; DROP TABLE testpub_tbl_parent, testpub_tbl_child; DROP PUBLICATION testpub3, testpub4, testpub5, testpub6, testpub7; --------------------------------------------- -- EXCEPT clause tests for partitioned tables --------------------------------------------- SET client_min_messages = 'ERROR'; CREATE TABLE testpub_root(a int) PARTITION BY RANGE(a); CREATE TABLE testpub_part1 PARTITION OF testpub_root FOR VALUES FROM (0) TO (100); CREATE PUBLICATION testpub8 FOR ALL TABLES EXCEPT (TABLE testpub_root); \dRp+ testpub8; Publication testpub8 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | Except tables: "public.testpub_root" \d testpub_part1 Table "public.testpub_part1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | Partition of: testpub_root FOR VALUES FROM (0) TO (100) Excluded from publications: "testpub8" \d testpub_root Partitioned table "public.testpub_root" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | Partition key: RANGE (a) Excluded from publications: "testpub8" Number of partitions: 1 (Use \d+ to list them.) CREATE PUBLICATION testpub9 FOR ALL TABLES EXCEPT (TABLE testpub_part1); ERROR: cannot specify relation "public.testpub_part1" in the publication EXCEPT clause DETAIL: This operation is not supported for individual partitions. CREATE TABLE tab_main (a int) PARTITION BY RANGE(a); -- Attaching a partition is not allowed if the partitioned table appears in a -- publication's EXCEPT clause. ALTER TABLE tab_main ATTACH PARTITION testpub_root FOR VALUES FROM (0) TO (200); ERROR: cannot attach table "testpub_root" as partition because it is referenced in publication "testpub8" EXCEPT clause DETAIL: The publication EXCEPT clause cannot contain tables that are partitions. HINT: Change the publication's EXCEPT clause using ALTER PUBLICATION ... SET ALL TABLES. RESET client_min_messages; DROP TABLE testpub_root, testpub_part1, tab_main; DROP PUBLICATION testpub8; --- Tests for publications with SEQUENCES CREATE SEQUENCE regress_pub_seq0; CREATE SEQUENCE pub_test.regress_pub_seq1; -- FOR ALL SEQUENCES SET client_min_messages = 'ERROR'; CREATE PUBLICATION regress_pub_forallsequences1 FOR ALL SEQUENCES; RESET client_min_messages; SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_forallsequences1'; pubname | puballtables | puballsequences ------------------------------+--------------+----------------- regress_pub_forallsequences1 | f | t (1 row) \d+ regress_pub_seq0 Sequence "public.regress_pub_seq0" Type | Start | Minimum | Maximum | Increment | Cycles? | Cache --------+-------+---------+---------------------+-----------+---------+------- bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 Included in publications: "regress_pub_forallsequences1" \dRp+ regress_pub_forallsequences1 Publication regress_pub_forallsequences1 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | t | t | t | t | t | none | f | (1 row) SET client_min_messages = 'ERROR'; CREATE PUBLICATION regress_pub_forallsequences2 FOR ALL SEQUENCES; RESET client_min_messages; -- check that describe sequence lists both publications the sequence belongs to \d+ pub_test.regress_pub_seq1 Sequence "pub_test.regress_pub_seq1" Type | Start | Minimum | Maximum | Increment | Cycles? | Cache --------+-------+---------+---------------------+-----------+---------+------- bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 Included in publications: "regress_pub_forallsequences1" "regress_pub_forallsequences2" --- Specifying both ALL TABLES and ALL SEQUENCES SET client_min_messages = 'ERROR'; CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES; -- Specifying WITH clause in an ALL SEQUENCES publication will emit a NOTICE. SET client_min_messages = 'NOTICE'; ALTER PUBLICATION regress_pub_for_allsequences_alltables SET (publish = 'insert'); NOTICE: publication parameters are not applicable to sequence synchronization and will be ignored for sequences ALTER PUBLICATION regress_pub_for_allsequences_alltables SET (publish_generated_columns = 'stored'); NOTICE: publication parameters are not applicable to sequence synchronization and will be ignored for sequences RESET client_min_messages; SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_for_allsequences_alltables'; pubname | puballtables | puballsequences ----------------------------------------+--------------+----------------- regress_pub_for_allsequences_alltables | t | t (1 row) \dRp+ regress_pub_for_allsequences_alltables Publication regress_pub_for_allsequences_alltables Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | t | t | f | f | f | stored | f | (1 row) DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1; DROP PUBLICATION regress_pub_forallsequences1; DROP PUBLICATION regress_pub_forallsequences2; DROP PUBLICATION regress_pub_for_allsequences_alltables; -- fail - Specifying ALL TABLES more than once CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL TABLES; ERROR: invalid publication object list LINE 1: ...equences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL TABLES... ^ DETAIL: ALL TABLES can be specified only once. -- fail - Specifying ALL SEQUENCES more than once CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL SEQUENCES; ERROR: invalid publication object list LINE 1: ...equences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL SEQUEN... ^ DETAIL: ALL SEQUENCES can be specified only once. -- Tests for partitioned tables SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_forparted; CREATE PUBLICATION testpub_forparted1; RESET client_min_messages; CREATE TABLE testpub_parted1 (LIKE testpub_parted); CREATE TABLE testpub_parted2 (LIKE testpub_parted); ALTER PUBLICATION testpub_forparted1 SET (publish='insert'); ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted1 FOR VALUES IN (1); ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted2 FOR VALUES IN (2); -- works despite missing REPLICA IDENTITY, because updates are not replicated UPDATE testpub_parted1 SET a = 1; -- only parent is listed as being in publication, not the partition ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted; \dRp+ testpub_forparted Publication testpub_forparted Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_parted" -- works despite missing REPLICA IDENTITY, because no actual update happened UPDATE testpub_parted SET a = 1 WHERE false; -- should now fail, because parent's publication replicates updates UPDATE testpub_parted1 SET a = 1; ERROR: cannot update table "testpub_parted1" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. ALTER TABLE testpub_parted DETACH PARTITION testpub_parted1; -- works again, because parent's publication is no longer considered UPDATE testpub_parted1 SET a = 1; ALTER PUBLICATION testpub_forparted SET (publish_via_partition_root = true); \dRp+ testpub_forparted Publication testpub_forparted Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | t | Tables: "public.testpub_parted" -- still fail, because parent's publication replicates updates UPDATE testpub_parted2 SET a = 2; ERROR: cannot update table "testpub_parted2" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. ALTER PUBLICATION testpub_forparted DROP TABLE testpub_parted; -- works again, because update is no longer replicated UPDATE testpub_parted2 SET a = 2; DROP TABLE testpub_parted1, testpub_parted2; DROP PUBLICATION testpub_forparted, testpub_forparted1; -- Tests for row filters CREATE TABLE testpub_rf_tbl1 (a integer, b text); CREATE TABLE testpub_rf_tbl2 (c text, d integer); CREATE TABLE testpub_rf_tbl3 (e integer); CREATE TABLE testpub_rf_tbl4 (g text); CREATE TABLE testpub_rf_tbl5 (a xml); CREATE SCHEMA testpub_rf_schema1; CREATE TABLE testpub_rf_schema1.testpub_rf_tbl5 (h integer); CREATE SCHEMA testpub_rf_schema2; CREATE TABLE testpub_rf_schema2.testpub_rf_tbl6 (i integer); SET client_min_messages = 'ERROR'; -- Firstly, test using the option publish='insert' because the row filter -- validation of referenced columns is less strict than for delete/update. CREATE PUBLICATION testpub5 FOR TABLE testpub_rf_tbl1, testpub_rf_tbl2 WHERE (c <> 'test' AND d < 5) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub5 Publication testpub5 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5)) \d testpub_rf_tbl3 Table "public.testpub_rf_tbl3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- e | integer | | | ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl3 WHERE (e > 1000 AND e < 2000); \dRp+ testpub5 Publication testpub5 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5)) "public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000)) \d testpub_rf_tbl3 Table "public.testpub_rf_tbl3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- e | integer | | | Included in publications: "testpub5" WHERE ((e > 1000) AND (e < 2000)) ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl2; \dRp+ testpub5 Publication testpub5 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000)) -- remove testpub_rf_tbl1 and add testpub_rf_tbl3 again (another WHERE expression) ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e > 300 AND e < 500); \dRp+ testpub5 Publication testpub5 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl3" WHERE ((e > 300) AND (e < 500)) \d testpub_rf_tbl3 Table "public.testpub_rf_tbl3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- e | integer | | | Included in publications: "testpub5" WHERE ((e > 300) AND (e < 500)) -- test \d <tablename> (now it displays filter information) SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_rf_yes FOR TABLE testpub_rf_tbl1 WHERE (a > 1) WITH (publish = 'insert'); CREATE PUBLICATION testpub_rf_no FOR TABLE testpub_rf_tbl1; RESET client_min_messages; \d testpub_rf_tbl1 Table "public.testpub_rf_tbl1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | b | text | | | Included in publications: "testpub_rf_no" "testpub_rf_yes" WHERE (a > 1) DROP PUBLICATION testpub_rf_yes, testpub_rf_no; -- some more syntax tests to exercise other parser pathways SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_syntax1 FOR TABLE testpub_rf_tbl1, ONLY testpub_rf_tbl3 WHERE (e < 999) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub_syntax1 Publication testpub_syntax1 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl3" WHERE (e < 999) DROP PUBLICATION testpub_syntax1; SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_syntax2 FOR TABLE testpub_rf_tbl1, testpub_rf_schema1.testpub_rf_tbl5 WHERE (h < 999) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub_syntax2 Publication testpub_syntax2 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl1" "testpub_rf_schema1.testpub_rf_tbl5" WHERE (h < 999) DROP PUBLICATION testpub_syntax2; -- fail - schemas don't allow WHERE clause SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_syntax3 FOR TABLES IN SCHEMA testpub_rf_schema1 WHERE (a = 123); ERROR: syntax error at or near "WHERE" LINE 1: ...b_syntax3 FOR TABLES IN SCHEMA testpub_rf_schema1 WHERE (a =... ^ CREATE PUBLICATION testpub_syntax3 FOR TABLES IN SCHEMA testpub_rf_schema1, testpub_rf_schema1 WHERE (a = 123); ERROR: WHERE clause not allowed for schema LINE 1: ..._syntax3 FOR TABLES IN SCHEMA testpub_rf_schema1, testpub_rf... ^ RESET client_min_messages; -- fail - duplicate tables are not allowed if that table has any WHERE clause SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_dups FOR TABLE testpub_rf_tbl1 WHERE (a = 1), testpub_rf_tbl1 WITH (publish = 'insert'); ERROR: conflicting or redundant WHERE clauses for table "testpub_rf_tbl1" CREATE PUBLICATION testpub_dups FOR TABLE testpub_rf_tbl1, testpub_rf_tbl1 WHERE (a = 2) WITH (publish = 'insert'); ERROR: conflicting or redundant WHERE clauses for table "testpub_rf_tbl1" RESET client_min_messages; -- fail - publication WHERE clause must be boolean ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (1234); ERROR: argument of PUBLICATION WHERE must be type boolean, not type integer LINE 1: ...PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (1234); ^ -- fail - aggregate functions not allowed in WHERE clause ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e < AVG(e)); ERROR: aggregate functions are not allowed in WHERE LINE 1: ...ATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e < AVG(e)); ^ -- fail - user-defined operators are not allowed CREATE FUNCTION testpub_rf_func1(integer, integer) RETURNS boolean AS $$ SELECT hashint4($1) > $2 $$ LANGUAGE SQL; CREATE OPERATOR =#> (PROCEDURE = testpub_rf_func1, LEFTARG = integer, RIGHTARG = integer); CREATE PUBLICATION testpub6 FOR TABLE testpub_rf_tbl3 WHERE (e =#> 27); ERROR: invalid publication WHERE expression LINE 1: ...ICATION testpub6 FOR TABLE testpub_rf_tbl3 WHERE (e =#> 27); ^ DETAIL: User-defined operators are not allowed. -- fail - user-defined functions are not allowed CREATE FUNCTION testpub_rf_func2() RETURNS integer IMMUTABLE AS $$ BEGIN RETURN 123; END; $$ LANGUAGE plpgsql; ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (a >= testpub_rf_func2()); ERROR: invalid publication WHERE expression LINE 1: ...ON testpub5 ADD TABLE testpub_rf_tbl1 WHERE (a >= testpub_rf... ^ DETAIL: User-defined or built-in mutable functions are not allowed. -- fail - non-immutable functions are not allowed. random() is volatile. ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (a < random()); ERROR: invalid publication WHERE expression LINE 1: ...ION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (a < random()); ^ DETAIL: User-defined or built-in mutable functions are not allowed. -- fail - user-defined collations are not allowed CREATE COLLATION user_collation FROM "C"; ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (b < '2' COLLATE user_collation); ERROR: invalid publication WHERE expression LINE 1: ...ICATION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (b < '2' CO... ^ DETAIL: User-defined collations are not allowed. -- ok - NULLIF is allowed ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (NULLIF(1,2) = a); -- ok - built-in operators are allowed ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (a IS NULL); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE ((a > 5) IS FALSE); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (a IS DISTINCT FROM 5); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE ((a, a + 1) < (2, 3)); -- ok - built-in type coercions between two binary compatible datatypes are allowed ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (b::varchar < '2'); -- ok - immutable built-in functions are allowed ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl4 WHERE (length(g) < 6); -- fail - user-defined types are not allowed CREATE TYPE rf_bug_status AS ENUM ('new', 'open', 'closed'); CREATE TABLE rf_bug (id serial, description text, status rf_bug_status); CREATE PUBLICATION testpub6 FOR TABLE rf_bug WHERE (status = 'open') WITH (publish = 'insert'); ERROR: invalid publication WHERE expression LINE 1: ...EATE PUBLICATION testpub6 FOR TABLE rf_bug WHERE (status = '... ^ DETAIL: User-defined types are not allowed. DROP TABLE rf_bug; DROP TYPE rf_bug_status; -- fail - row filter expression is not simple CREATE PUBLICATION testpub6 FOR TABLE testpub_rf_tbl1 WHERE (a IN (SELECT generate_series(1,5))); ERROR: invalid publication WHERE expression LINE 1: ...ICATION testpub6 FOR TABLE testpub_rf_tbl1 WHERE (a IN (SELE... ^ DETAIL: Only columns, constants, built-in operators, built-in data types, built-in collations, and immutable built-in functions are allowed. -- fail - system columns are not allowed CREATE PUBLICATION testpub6 FOR TABLE testpub_rf_tbl1 WHERE ('(0,1)'::tid = ctid); ERROR: invalid publication WHERE expression LINE 1: ...tpub6 FOR TABLE testpub_rf_tbl1 WHERE ('(0,1)'::tid = ctid); ^ DETAIL: System columns are not allowed. -- ok - conditional expressions are allowed ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl5 WHERE (a IS DOCUMENT); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl5 WHERE (xmlexists('//foo[text() = ''bar'']' PASSING BY VALUE a)); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (NULLIF(1, 2) = a); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (CASE a WHEN 5 THEN true ELSE false END); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (COALESCE(b, 'foo') = 'foo'); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (GREATEST(a, 10) > 10); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (a IN (2, 4, 6)); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (ARRAY[a] <@ ARRAY[2, 4, 6]); ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (ROW(a, 2) IS NULL); -- fail - WHERE not allowed in DROP ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl1 WHERE (e < 27); ERROR: cannot use a WHERE clause when removing a table from a publication -- fail - cannot ALTER SET table which is a member of a pre-existing schema SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2; -- should be able to set publication with schema and table of the same schema ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99); RESET client_min_messages; \dRp+ testpub6 Publication testpub6 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "testpub_rf_schema2" -- table with a row-filter, also covered by a published schema, should appear -- only once in \d output and without the row filter \d testpub_rf_schema2.testpub_rf_tbl6 Table "testpub_rf_schema2.testpub_rf_tbl6" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- i | integer | | | Included in publications: "testpub6" -- fail - virtual generated column uses user-defined function -- (Actually, this already fails at CREATE TABLE rather than at CREATE -- PUBLICATION, but let's keep the test in case the former gets -- relaxed sometime.) CREATE TABLE testpub_rf_tbl6 (id int PRIMARY KEY, x int, y int GENERATED ALWAYS AS (x * testpub_rf_func2()) VIRTUAL); ERROR: generation expression uses user-defined function LINE 1: ...RIMARY KEY, x int, y int GENERATED ALWAYS AS (x * testpub_rf... ^ DETAIL: Virtual generated columns that make use of user-defined functions are not yet supported. CREATE PUBLICATION testpub7 FOR TABLE testpub_rf_tbl6 WHERE (y > 100); ERROR: relation "testpub_rf_tbl6" does not exist -- test that SET EXPRESSION is rejected, because it could affect a row filter SET client_min_messages = 'ERROR'; CREATE TABLE testpub_rf_tbl7 (id int PRIMARY KEY, x int, y int GENERATED ALWAYS AS (x * 111) VIRTUAL); CREATE PUBLICATION testpub8 FOR TABLE testpub_rf_tbl7 WHERE (y > 100); ALTER TABLE testpub_rf_tbl7 ALTER COLUMN y SET EXPRESSION AS (x * testpub_rf_func2()); ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables that are part of a publication DETAIL: Column "y" of relation "testpub_rf_tbl7" is a virtual generated column. RESET client_min_messages; DROP TABLE testpub_rf_tbl1; DROP TABLE testpub_rf_tbl2; DROP TABLE testpub_rf_tbl3; DROP TABLE testpub_rf_tbl4; DROP TABLE testpub_rf_tbl5; --DROP TABLE testpub_rf_tbl6; DROP TABLE testpub_rf_schema1.testpub_rf_tbl5; DROP TABLE testpub_rf_schema2.testpub_rf_tbl6; DROP SCHEMA testpub_rf_schema1; DROP SCHEMA testpub_rf_schema2; DROP PUBLICATION testpub5; DROP PUBLICATION testpub6; DROP PUBLICATION testpub8; DROP TABLE testpub_rf_tbl7; DROP OPERATOR =#>(integer, integer); DROP FUNCTION testpub_rf_func1(integer, integer); DROP FUNCTION testpub_rf_func2(); DROP COLLATION user_collation; -- ====================================================== -- More row filter tests for validating column references CREATE TABLE rf_tbl_abcd_nopk(a int, b int, c int, d int); CREATE TABLE rf_tbl_abcd_pk(a int, b int, c int, d int, PRIMARY KEY(a,b)); CREATE TABLE rf_tbl_abcd_part_pk (a int PRIMARY KEY, b int) PARTITION by RANGE (a); CREATE TABLE rf_tbl_abcd_part_pk_1 (b int, a int PRIMARY KEY); ALTER TABLE rf_tbl_abcd_part_pk ATTACH PARTITION rf_tbl_abcd_part_pk_1 FOR VALUES FROM (1) TO (10); -- Case 1. REPLICA IDENTITY DEFAULT (means use primary key or nothing) -- 1a. REPLICA IDENTITY is DEFAULT and table has a PK. SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub6 FOR TABLE rf_tbl_abcd_pk WHERE (a > 99); RESET client_min_messages; -- ok - "a" is a PK col UPDATE rf_tbl_abcd_pk SET a = 1; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (b > 99); -- ok - "b" is a PK col UPDATE rf_tbl_abcd_pk SET a = 1; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99); -- fail - "c" is not part of the PK UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" DETAIL: Column used in the publication WHERE expression is not part of the replica identity. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (d > 99); -- fail - "d" is not part of the PK UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" DETAIL: Column used in the publication WHERE expression is not part of the replica identity. -- 1b. REPLICA IDENTITY is DEFAULT and table has no PK ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99); -- fail - "a" is not part of REPLICA IDENTITY UPDATE rf_tbl_abcd_nopk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_nopk" DETAIL: Column used in the publication WHERE expression is not part of the replica identity. -- Case 2. REPLICA IDENTITY FULL ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY FULL; ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY FULL; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99); -- ok - "c" is in REPLICA IDENTITY now even though not in PK UPDATE rf_tbl_abcd_pk SET a = 1; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99); -- ok - "a" is in REPLICA IDENTITY now UPDATE rf_tbl_abcd_nopk SET a = 1; -- Case 3. REPLICA IDENTITY NOTHING ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY NOTHING; ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY NOTHING; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (a > 99); -- fail - "a" is in PK but it is not part of REPLICA IDENTITY NOTHING UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" DETAIL: Column used in the publication WHERE expression is not part of the replica identity. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99); -- fail - "c" is not in PK and not in REPLICA IDENTITY NOTHING UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" DETAIL: Column used in the publication WHERE expression is not part of the replica identity. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99); -- fail - "a" is not in REPLICA IDENTITY NOTHING UPDATE rf_tbl_abcd_nopk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_nopk" DETAIL: Column used in the publication WHERE expression is not part of the replica identity. -- Case 4. REPLICA IDENTITY INDEX ALTER TABLE rf_tbl_abcd_pk ALTER COLUMN c SET NOT NULL; CREATE UNIQUE INDEX idx_abcd_pk_c ON rf_tbl_abcd_pk(c); ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY USING INDEX idx_abcd_pk_c; ALTER TABLE rf_tbl_abcd_nopk ALTER COLUMN c SET NOT NULL; CREATE UNIQUE INDEX idx_abcd_nopk_c ON rf_tbl_abcd_nopk(c); ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY USING INDEX idx_abcd_nopk_c; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (a > 99); -- fail - "a" is in PK but it is not part of REPLICA IDENTITY INDEX UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" DETAIL: Column used in the publication WHERE expression is not part of the replica identity. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99); -- ok - "c" is not in PK but it is part of REPLICA IDENTITY INDEX UPDATE rf_tbl_abcd_pk SET a = 1; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99); -- fail - "a" is not in REPLICA IDENTITY INDEX UPDATE rf_tbl_abcd_nopk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_nopk" DETAIL: Column used in the publication WHERE expression is not part of the replica identity. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (c > 99); -- ok - "c" is part of REPLICA IDENTITY INDEX UPDATE rf_tbl_abcd_nopk SET a = 1; -- Tests for partitioned table -- set PUBLISH_VIA_PARTITION_ROOT to false and test row filter for partitioned -- table ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0); -- fail - cannot use row filter for partitioned table ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk WHERE (a > 99); ERROR: cannot use publication WHERE clause for relation "rf_tbl_abcd_part_pk" DETAIL: WHERE clause cannot be used for a partitioned table when publish_via_partition_root is false. -- ok - can use row filter for partition ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk_1 WHERE (a > 99); -- ok - "a" is a PK col UPDATE rf_tbl_abcd_part_pk SET a = 1; -- set PUBLISH_VIA_PARTITION_ROOT to true and test row filter for partitioned -- table ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=1); -- ok - can use row filter for partitioned table ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk WHERE (a > 99); -- ok - "a" is a PK col UPDATE rf_tbl_abcd_part_pk SET a = 1; -- fail - cannot set PUBLISH_VIA_PARTITION_ROOT to false if any row filter is -- used for partitioned table ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0); ERROR: cannot set parameter "publish_via_partition_root" to false for publication "testpub6" 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. -- remove partitioned table's row filter ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk; -- ok - we don't have row filter for partitioned table. ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0); -- Now change the root filter to use a column "b" -- (which is not in the replica identity) ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk_1 WHERE (b > 99); -- ok - we don't have row filter for partitioned table. ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0); -- fail - "b" is not in REPLICA IDENTITY INDEX UPDATE rf_tbl_abcd_part_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_part_pk_1" DETAIL: Column used in the publication WHERE expression is not part of the replica identity. -- set PUBLISH_VIA_PARTITION_ROOT to true -- can use row filter for partitioned table ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=1); -- ok - can use row filter for partitioned table ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk WHERE (b > 99); -- fail - "b" is not in REPLICA IDENTITY INDEX UPDATE rf_tbl_abcd_part_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_part_pk_1" DETAIL: Column used in the publication WHERE expression is not part of the replica identity. DROP PUBLICATION testpub6; DROP TABLE rf_tbl_abcd_pk; DROP TABLE rf_tbl_abcd_nopk; DROP TABLE rf_tbl_abcd_part_pk; -- ====================================================== -- ====================================================== -- Tests with generated column SET client_min_messages = 'ERROR'; -- stored CREATE TABLE testpub_gencol (a INT, b INT GENERATED ALWAYS AS (a + 1) STORED NOT NULL); CREATE UNIQUE INDEX testpub_gencol_idx ON testpub_gencol (b); ALTER TABLE testpub_gencol REPLICA IDENTITY USING index testpub_gencol_idx; -- error - generated column "b" must be published explicitly as it is -- part of the REPLICA IDENTITY index. CREATE PUBLICATION pub_gencol FOR TABLE testpub_gencol; UPDATE testpub_gencol SET a = 100 WHERE a = 1; ERROR: cannot update table "testpub_gencol" DETAIL: Replica identity must not contain unpublished generated columns. -- error - generated column "b" must be published explicitly as it is -- part of the REPLICA IDENTITY. ALTER TABLE testpub_gencol REPLICA IDENTITY FULL; UPDATE testpub_gencol SET a = 100 WHERE a = 1; ERROR: cannot update table "testpub_gencol" DETAIL: Replica identity must not contain unpublished generated columns. DROP PUBLICATION pub_gencol; -- ok - generated column "b" is published explicitly CREATE PUBLICATION pub_gencol FOR TABLE testpub_gencol with (publish_generated_columns = stored); UPDATE testpub_gencol SET a = 100 WHERE a = 1; DROP PUBLICATION pub_gencol; DROP TABLE testpub_gencol; -- virtual CREATE TABLE testpub_gencol (a INT, b INT GENERATED ALWAYS AS (a + 1) VIRTUAL); CREATE PUBLICATION pub_gencol FOR TABLE testpub_gencol; -- error - generated column "b" must be published explicitly as it is -- part of the REPLICA IDENTITY. ALTER TABLE testpub_gencol REPLICA IDENTITY FULL; UPDATE testpub_gencol SET a = 100 WHERE a = 1; ERROR: cannot update table "testpub_gencol" DETAIL: Replica identity must not contain unpublished generated columns. DROP PUBLICATION pub_gencol; -- error - "stored" setting does not affect virtual column CREATE PUBLICATION pub_gencol FOR TABLE testpub_gencol with (publish_generated_columns = stored); UPDATE testpub_gencol SET a = 100 WHERE a = 1; ERROR: cannot update table "testpub_gencol" DETAIL: Replica identity must not contain unpublished generated columns. DROP PUBLICATION pub_gencol; DROP TABLE testpub_gencol; RESET client_min_messages; -- ====================================================== -- fail - duplicate tables are not allowed if that table has any column lists SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_dups FOR TABLE testpub_tbl1 (a), testpub_tbl1 WITH (publish = 'insert'); ERROR: conflicting or redundant column lists for table "testpub_tbl1" CREATE PUBLICATION testpub_dups FOR TABLE testpub_tbl1, testpub_tbl1 (a) WITH (publish = 'insert'); ERROR: conflicting or redundant column lists for table "testpub_tbl1" RESET client_min_messages; -- test for column lists SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_fortable FOR TABLE testpub_tbl1; CREATE PUBLICATION testpub_fortable_insert WITH (publish = 'insert'); RESET client_min_messages; CREATE TABLE testpub_tbl5 (a int PRIMARY KEY, b text, c text, d int generated always as (a + length(b)) stored, e int generated always as (a + length(b)) virtual ); -- error: column "x" does not exist ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, x); ERROR: column "x" of relation "testpub_tbl5" does not exist -- error: replica identity "a" not included in the column list ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (b, c); UPDATE testpub_tbl5 SET a = 1; ERROR: cannot update table "testpub_tbl5" DETAIL: Column list used by the publication does not cover the replica identity. ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5; -- error: system attributes "ctid" not allowed in column list ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, ctid); ERROR: cannot use system column "ctid" in publication column list ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl1 (id, ctid); ERROR: cannot use system column "ctid" in publication column list -- error: duplicates not allowed in column list ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, a); ERROR: duplicate column "a" in publication column list ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl5 (a, a); ERROR: duplicate column "a" in publication column list -- ok ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, c); ALTER TABLE testpub_tbl5 DROP COLUMN c; -- no dice ERROR: cannot drop column c of table testpub_tbl5 because other objects depend on it DETAIL: publication of table testpub_tbl5 in publication testpub_fortable depends on column c of table testpub_tbl5 HINT: Use DROP ... CASCADE to drop the dependent objects too. -- ok: for insert-only publication, any column list is acceptable ALTER PUBLICATION testpub_fortable_insert ADD TABLE testpub_tbl5 (b, c); /* not all replica identities are good enough */ CREATE UNIQUE INDEX testpub_tbl5_b_key ON testpub_tbl5 (b, c); ALTER TABLE testpub_tbl5 ALTER b SET NOT NULL, ALTER c SET NOT NULL; ALTER TABLE testpub_tbl5 REPLICA IDENTITY USING INDEX testpub_tbl5_b_key; -- error: replica identity (b,c) is not covered by column list (a, c) UPDATE testpub_tbl5 SET a = 1; ERROR: cannot update table "testpub_tbl5" DETAIL: Column list used by the publication does not cover the replica identity. ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5; -- ok: stored generated column "d" can be in the list too ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, d); ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5; -- error: virtual generated column "e" can't be in list ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, e); ERROR: cannot use virtual generated column "e" in publication column list -- error: change the replica identity to "b", and column list to (a, c) -- then update fails, because (a, c) does not cover replica identity ALTER TABLE testpub_tbl5 REPLICA IDENTITY USING INDEX testpub_tbl5_b_key; ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, c); UPDATE testpub_tbl5 SET a = 1; ERROR: cannot update table "testpub_tbl5" DETAIL: Column list used by the publication does not cover the replica identity. /* But if upd/del are not published, it works OK */ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_table_ins WITH (publish = 'insert, truncate'); RESET client_min_messages; ALTER PUBLICATION testpub_table_ins ADD TABLE testpub_tbl5 (a); -- ok \dRp+ testpub_table_ins Publication testpub_table_ins Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | f | f | t | none | f | Tables: "public.testpub_tbl5" (a) -- error: cannot work with deferrable primary keys CREATE TABLE testpub_tbl5d (a int PRIMARY KEY DEFERRABLE); ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5d; UPDATE testpub_tbl5d SET a = 1; ERROR: cannot update table "testpub_tbl5d" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. /* but works fine with FULL replica identity */ ALTER TABLE testpub_tbl5d REPLICA IDENTITY FULL; UPDATE testpub_tbl5d SET a = 1; DROP TABLE testpub_tbl5d; -- tests with REPLICA IDENTITY FULL CREATE TABLE testpub_tbl6 (a int, b text, c text); ALTER TABLE testpub_tbl6 REPLICA IDENTITY FULL; ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl6 (a, b, c); UPDATE testpub_tbl6 SET a = 1; ERROR: cannot update table "testpub_tbl6" DETAIL: Column list used by the publication does not cover the replica identity. ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl6; ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl6; -- ok UPDATE testpub_tbl6 SET a = 1; -- make sure changing the column list is propagated to the catalog CREATE TABLE testpub_tbl7 (a int primary key, b text, c text); ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl7 (a, b); \d+ testpub_tbl7 Table "public.testpub_tbl7" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | integer | | not null | | plain | | b | text | | | | extended | | c | text | | | | extended | | Indexes: "testpub_tbl7_pkey" PRIMARY KEY, btree (a) Included in publications: "testpub_fortable" (a, b) Not-null constraints: "testpub_tbl7_a_not_null" NOT NULL "a" -- ok: the column list is the same, we should skip this table (or at least not fail) ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl7 (a, b); \d+ testpub_tbl7 Table "public.testpub_tbl7" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | integer | | not null | | plain | | b | text | | | | extended | | c | text | | | | extended | | Indexes: "testpub_tbl7_pkey" PRIMARY KEY, btree (a) Included in publications: "testpub_fortable" (a, b) Not-null constraints: "testpub_tbl7_a_not_null" NOT NULL "a" -- ok: the column list changes, make sure the catalog gets updated ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl7 (a, c); \d+ testpub_tbl7 Table "public.testpub_tbl7" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | integer | | not null | | plain | | b | text | | | | extended | | c | text | | | | extended | | Indexes: "testpub_tbl7_pkey" PRIMARY KEY, btree (a) Included in publications: "testpub_fortable" (a, c) Not-null constraints: "testpub_tbl7_a_not_null" NOT NULL "a" -- column list for partitioned tables has to cover replica identities for -- all child relations CREATE TABLE testpub_tbl8 (a int, b text, c text) PARTITION BY HASH (a); -- first partition has replica identity "a" CREATE TABLE testpub_tbl8_0 PARTITION OF testpub_tbl8 FOR VALUES WITH (modulus 2, remainder 0); ALTER TABLE testpub_tbl8_0 ADD PRIMARY KEY (a); ALTER TABLE testpub_tbl8_0 REPLICA IDENTITY USING INDEX testpub_tbl8_0_pkey; -- second partition has replica identity "b" CREATE TABLE testpub_tbl8_1 PARTITION OF testpub_tbl8 FOR VALUES WITH (modulus 2, remainder 1); ALTER TABLE testpub_tbl8_1 ADD PRIMARY KEY (b); ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY USING INDEX testpub_tbl8_1_pkey; -- ok: column list covers both "a" and "b" SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_col_list FOR TABLE testpub_tbl8 (a, b) WITH (publish_via_partition_root = 'true'); RESET client_min_messages; -- ok: the same thing, but try plain ADD TABLE ALTER PUBLICATION testpub_col_list DROP TABLE testpub_tbl8; ALTER PUBLICATION testpub_col_list ADD TABLE testpub_tbl8 (a, b); UPDATE testpub_tbl8 SET a = 1; -- failure: column list does not cover replica identity for the second partition ALTER PUBLICATION testpub_col_list DROP TABLE testpub_tbl8; ALTER PUBLICATION testpub_col_list ADD TABLE testpub_tbl8 (a, c); UPDATE testpub_tbl8 SET a = 1; ERROR: cannot update table "testpub_tbl8_1" DETAIL: Column list used by the publication does not cover the replica identity. ALTER PUBLICATION testpub_col_list DROP TABLE testpub_tbl8; -- failure: one of the partitions has REPLICA IDENTITY FULL ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY FULL; ALTER PUBLICATION testpub_col_list ADD TABLE testpub_tbl8 (a, c); UPDATE testpub_tbl8 SET a = 1; ERROR: cannot update table "testpub_tbl8_1" DETAIL: Column list used by the publication does not cover the replica identity. ALTER PUBLICATION testpub_col_list DROP TABLE testpub_tbl8; -- add table and then try changing replica identity ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY USING INDEX testpub_tbl8_1_pkey; ALTER PUBLICATION testpub_col_list ADD TABLE testpub_tbl8 (a, b); -- failure: replica identity full can't be used with a column list ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY FULL; UPDATE testpub_tbl8 SET a = 1; ERROR: cannot update table "testpub_tbl8_1" DETAIL: Column list used by the publication does not cover the replica identity. -- failure: replica identity has to be covered by the column list ALTER TABLE testpub_tbl8_1 DROP CONSTRAINT testpub_tbl8_1_pkey; ALTER TABLE testpub_tbl8_1 ADD PRIMARY KEY (c); ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY USING INDEX testpub_tbl8_1_pkey; UPDATE testpub_tbl8 SET a = 1; ERROR: cannot update table "testpub_tbl8_1" DETAIL: Column list used by the publication does not cover the replica identity. DROP TABLE testpub_tbl8; -- column list for partitioned tables has to cover replica identities for -- all child relations CREATE TABLE testpub_tbl8 (a int, b text, c text) PARTITION BY HASH (a); ALTER PUBLICATION testpub_col_list ADD TABLE testpub_tbl8 (a, b); -- first partition has replica identity "a" CREATE TABLE testpub_tbl8_0 (a int, b text, c text); ALTER TABLE testpub_tbl8_0 ADD PRIMARY KEY (a); ALTER TABLE testpub_tbl8_0 REPLICA IDENTITY USING INDEX testpub_tbl8_0_pkey; -- second partition has replica identity "b" CREATE TABLE testpub_tbl8_1 (a int, b text, c text); ALTER TABLE testpub_tbl8_1 ADD PRIMARY KEY (c); ALTER TABLE testpub_tbl8_1 REPLICA IDENTITY USING INDEX testpub_tbl8_1_pkey; -- ok: attaching first partition works, because (a) is in column list ALTER TABLE testpub_tbl8 ATTACH PARTITION testpub_tbl8_0 FOR VALUES WITH (modulus 2, remainder 0); -- failure: second partition has replica identity (c), which si not in column list ALTER TABLE testpub_tbl8 ATTACH PARTITION testpub_tbl8_1 FOR VALUES WITH (modulus 2, remainder 1); UPDATE testpub_tbl8 SET a = 1; ERROR: cannot update table "testpub_tbl8_1" DETAIL: Column list used by the publication does not cover the replica identity. -- failure: changing replica identity to FULL for partition fails, because -- of the column list on the parent ALTER TABLE testpub_tbl8_0 REPLICA IDENTITY FULL; UPDATE testpub_tbl8 SET a = 1; ERROR: cannot update table "testpub_tbl8_0" DETAIL: Column list used by the publication does not cover the replica identity. -- test that using column list for table is disallowed if any schemas are -- part of the publication SET client_min_messages = 'ERROR'; -- failure - cannot use column list and schema together CREATE PUBLICATION testpub_tbl9 FOR TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a); ERROR: cannot use column list for relation "public.testpub_tbl7" in publication "testpub_tbl9" DETAIL: Column lists cannot be specified in publications containing FOR TABLES IN SCHEMA elements. -- ok - only publish schema CREATE PUBLICATION testpub_tbl9 FOR TABLES IN SCHEMA public; -- failure - add a table with column list when there is already a schema in the -- publication ALTER PUBLICATION testpub_tbl9 ADD TABLE public.testpub_tbl7(a); ERROR: cannot use column list for relation "public.testpub_tbl7" in publication "testpub_tbl9" DETAIL: Column lists cannot be specified in publications containing FOR TABLES IN SCHEMA elements. -- ok - only publish table with column list ALTER PUBLICATION testpub_tbl9 SET TABLE public.testpub_tbl7(a); -- failure - specify a schema when there is already a column list in the -- publication ALTER PUBLICATION testpub_tbl9 ADD TABLES IN SCHEMA public; ERROR: cannot add schema to publication "testpub_tbl9" DETAIL: Schemas cannot be added if any tables that specify a column list are already part of the publication. -- failure - cannot SET column list and schema together ALTER PUBLICATION testpub_tbl9 SET TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a); ERROR: cannot use column list for relation "public.testpub_tbl7" in publication "testpub_tbl9" DETAIL: Column lists cannot be specified in publications containing FOR TABLES IN SCHEMA elements. -- ok - drop table ALTER PUBLICATION testpub_tbl9 DROP TABLE public.testpub_tbl7; -- failure - cannot ADD column list and schema together ALTER PUBLICATION testpub_tbl9 ADD TABLES IN SCHEMA public, TABLE public.testpub_tbl7(a); ERROR: cannot use column list for relation "public.testpub_tbl7" in publication "testpub_tbl9" DETAIL: Column lists cannot be specified in publications containing FOR TABLES IN SCHEMA elements. RESET client_min_messages; DROP TABLE testpub_tbl5, testpub_tbl6, testpub_tbl7, testpub_tbl8, testpub_tbl8_1; DROP PUBLICATION testpub_table_ins, testpub_fortable, testpub_fortable_insert, testpub_col_list, testpub_tbl9; -- ====================================================== -- Test combination of column list and row filter SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_both_filters; RESET client_min_messages; CREATE TABLE testpub_tbl_both_filters (a int, b int, c int, PRIMARY KEY (a,c)); ALTER TABLE testpub_tbl_both_filters REPLICA IDENTITY USING INDEX testpub_tbl_both_filters_pkey; ALTER PUBLICATION testpub_both_filters ADD TABLE testpub_tbl_both_filters (a,c) WHERE (c != 1); \dRp+ testpub_both_filters Publication testpub_both_filters Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_tbl_both_filters" (a, c) WHERE (c <> 1) \d+ testpub_tbl_both_filters Table "public.testpub_tbl_both_filters" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- a | integer | | not null | | plain | | b | integer | | | | plain | | c | integer | | not null | | plain | | Indexes: "testpub_tbl_both_filters_pkey" PRIMARY KEY, btree (a, c) REPLICA IDENTITY Included in publications: "testpub_both_filters" (a, c) WHERE (c <> 1) Not-null constraints: "testpub_tbl_both_filters_a_not_null" NOT NULL "a" "testpub_tbl_both_filters_c_not_null" NOT NULL "c" DROP TABLE testpub_tbl_both_filters; DROP PUBLICATION testpub_both_filters; -- ====================================================== -- More column list tests for validating column references CREATE TABLE rf_tbl_abcd_nopk(a int, b int, c int, d int); CREATE TABLE rf_tbl_abcd_pk(a int, b int, c int, d int, PRIMARY KEY(a,b)); CREATE TABLE rf_tbl_abcd_part_pk (a int PRIMARY KEY, b int) PARTITION by RANGE (a); CREATE TABLE rf_tbl_abcd_part_pk_1 (b int, a int PRIMARY KEY); ALTER TABLE rf_tbl_abcd_part_pk ATTACH PARTITION rf_tbl_abcd_part_pk_1 FOR VALUES FROM (1) TO (10); -- Case 1. REPLICA IDENTITY DEFAULT (means use primary key or nothing) -- 1a. REPLICA IDENTITY is DEFAULT and table has a PK. SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub6 FOR TABLE rf_tbl_abcd_pk (a, b); RESET client_min_messages; -- ok - (a,b) covers all PK cols UPDATE rf_tbl_abcd_pk SET a = 1; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (a, b, c); -- ok - (a,b,c) covers all PK cols UPDATE rf_tbl_abcd_pk SET a = 1; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (a); -- fail - "b" is missing from the column list UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" DETAIL: Column list used by the publication does not cover the replica identity. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (b); -- fail - "a" is missing from the column list UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" DETAIL: Column list used by the publication does not cover the replica identity. -- 1b. REPLICA IDENTITY is DEFAULT and table has no PK ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (a); -- ok - there's no replica identity, so any column list works -- note: it fails anyway, just a bit later because UPDATE requires RI UPDATE rf_tbl_abcd_nopk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_nopk" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. -- Case 2. REPLICA IDENTITY FULL ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY FULL; ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY FULL; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (c); -- fail - with REPLICA IDENTITY FULL no column list is allowed UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" DETAIL: Column list used by the publication does not cover the replica identity. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (a, b, c, d); -- fail - with REPLICA IDENTITY FULL no column list is allowed UPDATE rf_tbl_abcd_nopk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_nopk" DETAIL: Column list used by the publication does not cover the replica identity. -- Case 3. REPLICA IDENTITY NOTHING ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY NOTHING; ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY NOTHING; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (a); -- ok - REPLICA IDENTITY NOTHING means all column lists are valid -- it still fails later because without RI we can't replicate updates UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (a, b, c, d); -- ok - REPLICA IDENTITY NOTHING means all column lists are valid -- it still fails later because without RI we can't replicate updates UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (d); -- ok - REPLICA IDENTITY NOTHING means all column lists are valid -- it still fails later because without RI we can't replicate updates UPDATE rf_tbl_abcd_nopk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_nopk" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. -- Case 4. REPLICA IDENTITY INDEX ALTER TABLE rf_tbl_abcd_pk ALTER COLUMN c SET NOT NULL; CREATE UNIQUE INDEX idx_abcd_pk_c ON rf_tbl_abcd_pk(c); ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY USING INDEX idx_abcd_pk_c; ALTER TABLE rf_tbl_abcd_nopk ALTER COLUMN c SET NOT NULL; CREATE UNIQUE INDEX idx_abcd_nopk_c ON rf_tbl_abcd_nopk(c); ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY USING INDEX idx_abcd_nopk_c; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (a); -- fail - column list "a" does not cover the REPLICA IDENTITY INDEX on "c" UPDATE rf_tbl_abcd_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_pk" DETAIL: Column list used by the publication does not cover the replica identity. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (c); -- ok - column list "c" does cover the REPLICA IDENTITY INDEX on "c" UPDATE rf_tbl_abcd_pk SET a = 1; ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (a); -- fail - column list "a" does not cover the REPLICA IDENTITY INDEX on "c" UPDATE rf_tbl_abcd_nopk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_nopk" DETAIL: Column list used by the publication does not cover the replica identity. ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (c); -- ok - column list "c" does cover the REPLICA IDENTITY INDEX on "c" UPDATE rf_tbl_abcd_nopk SET a = 1; -- Tests for partitioned table -- set PUBLISH_VIA_PARTITION_ROOT to false and test column list for partitioned -- table ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0); -- fail - cannot use column list for partitioned table ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk (a); ERROR: cannot use column list for relation "public.rf_tbl_abcd_part_pk" in publication "testpub6" DETAIL: Column lists cannot be specified for partitioned tables when publish_via_partition_root is false. -- ok - can use column list for partition ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk_1 (a); -- ok - "a" is a PK col UPDATE rf_tbl_abcd_part_pk SET a = 1; -- set PUBLISH_VIA_PARTITION_ROOT to true and test column list for partitioned -- table ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=1); -- ok - can use column list for partitioned table ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk (a); -- ok - "a" is a PK col UPDATE rf_tbl_abcd_part_pk SET a = 1; -- fail - cannot set PUBLISH_VIA_PARTITION_ROOT to false if any column list is -- used for partitioned table ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0); ERROR: cannot set parameter "publish_via_partition_root" to false for publication "testpub6" 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. -- remove partitioned table's column list ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk; -- ok - we don't have column list for partitioned table. ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0); -- Now change the root column list to use a column "b" -- (which is not in the replica identity) ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk_1 (b); -- ok - we don't have column list for partitioned table. ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0); -- fail - "b" is not in REPLICA IDENTITY INDEX UPDATE rf_tbl_abcd_part_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_part_pk_1" DETAIL: Column list used by the publication does not cover the replica identity. -- set PUBLISH_VIA_PARTITION_ROOT to true -- can use column list for partitioned table ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=1); -- ok - can use column list for partitioned table ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk (b); -- fail - "b" is not in REPLICA IDENTITY INDEX UPDATE rf_tbl_abcd_part_pk SET a = 1; ERROR: cannot update table "rf_tbl_abcd_part_pk_1" DETAIL: Column list used by the publication does not cover the replica identity. DROP PUBLICATION testpub6; DROP TABLE rf_tbl_abcd_pk; DROP TABLE rf_tbl_abcd_nopk; DROP TABLE rf_tbl_abcd_part_pk; -- ====================================================== -- Test cache invalidation FOR ALL TABLES publication SET client_min_messages = 'ERROR'; CREATE TABLE testpub_tbl4(a int); INSERT INTO testpub_tbl4 values(1); UPDATE testpub_tbl4 set a = 2; CREATE PUBLICATION testpub_foralltables FOR ALL TABLES; RESET client_min_messages; -- fail missing REPLICA IDENTITY UPDATE testpub_tbl4 set a = 3; ERROR: cannot update table "testpub_tbl4" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. DROP PUBLICATION testpub_foralltables; -- should pass after dropping the publication UPDATE testpub_tbl4 set a = 3; DROP TABLE testpub_tbl4; -- fail - view CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view; ERROR: cannot add relation "testpub_view" to publication DETAIL: This operation is not supported for views. CREATE TEMPORARY TABLE testpub_temptbl(a int); -- fail - temporary table CREATE PUBLICATION testpub_fortemptbl FOR TABLE testpub_temptbl; ERROR: cannot add relation "testpub_temptbl" to publication DETAIL: This operation is not supported for temporary tables. DROP TABLE testpub_temptbl; CREATE UNLOGGED TABLE testpub_unloggedtbl(a int); -- fail - unlogged table CREATE PUBLICATION testpub_forunloggedtbl FOR TABLE testpub_unloggedtbl; ERROR: cannot add relation "testpub_unloggedtbl" to publication DETAIL: This operation is not supported for unlogged tables. DROP TABLE testpub_unloggedtbl; -- fail - system table CREATE PUBLICATION testpub_forsystemtbl FOR TABLE pg_publication; ERROR: cannot add relation "pg_publication" to publication DETAIL: This operation is not supported for system tables. SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk; RESET client_min_messages; -- fail - already added ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1; ERROR: relation "testpub_tbl1" is already a member of publication "testpub_fortbl" -- fail - already added CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1; ERROR: publication "testpub_fortbl" already exists \dRp+ testpub_fortbl Publication testpub_fortbl Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" -- fail - view ALTER PUBLICATION testpub_default ADD TABLE testpub_view; ERROR: cannot add relation "testpub_view" to publication DETAIL: This operation is not supported for views. ALTER PUBLICATION testpub_default ADD TABLE testpub_tbl1; ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1; ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk; ALTER PUBLICATION testpub_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1; \d+ pub_test.testpub_nopk Table "pub_test.testpub_nopk" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- foo | integer | | | | plain | | bar | integer | | | | plain | | Included in publications: "testpub_default" "testpub_fortbl" "testpub_ins_trunct" \d+ testpub_tbl1 Table "public.testpub_tbl1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | | data | text | | | | extended | | Indexes: "testpub_tbl1_pkey" PRIMARY KEY, btree (id) Included in publications: "testpub_default" "testpub_fortbl" "testpub_ins_trunct" Not-null constraints: "testpub_tbl1_id_not_null" NOT NULL "id" \dRp+ testpub_default Publication testpub_default Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------------ regress_publication_user | f | f | t | t | t | f | none | f | test publication Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk; -- fail - nonexistent ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk; ERROR: relation "testpub_nopk" is not part of the publication \d+ testpub_tbl1 Table "public.testpub_tbl1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | | data | text | | | | extended | | Indexes: "testpub_tbl1_pkey" PRIMARY KEY, btree (id) Included in publications: "testpub_fortbl" "testpub_ins_trunct" Not-null constraints: "testpub_tbl1_id_not_null" NOT NULL "id" -- verify relation cache invalidation when a primary key is added using -- an existing index CREATE TABLE pub_test.testpub_addpk (id int not null, data int); ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_addpk; INSERT INTO pub_test.testpub_addpk VALUES(1, 11); CREATE UNIQUE INDEX testpub_addpk_id_idx ON pub_test.testpub_addpk(id); -- fail: UPDATE pub_test.testpub_addpk SET id = 2; ERROR: cannot update table "testpub_addpk" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. ALTER TABLE pub_test.testpub_addpk ADD PRIMARY KEY USING INDEX testpub_addpk_id_idx; -- now it should work: UPDATE pub_test.testpub_addpk SET id = 2; DROP TABLE pub_test.testpub_addpk; -- permissions SET ROLE regress_publication_user2; CREATE PUBLICATION testpub2; -- fail ERROR: permission denied for database regression SET ROLE regress_publication_user; GRANT CREATE ON DATABASE regression TO regress_publication_user2; SET ROLE regress_publication_user2; SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub2; -- ok CREATE PUBLICATION testpub3 FOR TABLES IN SCHEMA pub_test; -- fail ERROR: must be superuser to create FOR TABLES IN SCHEMA publication CREATE PUBLICATION testpub3; -- ok RESET client_min_messages; ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail ERROR: must be owner of table testpub_tbl1 ALTER PUBLICATION testpub3 ADD TABLES IN SCHEMA pub_test; -- fail ERROR: must be superuser to add or set schemas SET ROLE regress_publication_user; GRANT regress_publication_user TO regress_publication_user2; SET ROLE regress_publication_user2; ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok DROP PUBLICATION testpub2; DROP PUBLICATION testpub3; SET ROLE regress_publication_user; CREATE ROLE regress_publication_user3; GRANT regress_publication_user2 TO regress_publication_user3; SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub4 FOR TABLES IN SCHEMA pub_test; RESET client_min_messages; ALTER PUBLICATION testpub4 OWNER TO regress_publication_user3; SET ROLE regress_publication_user3; -- fail - new owner must be superuser ALTER PUBLICATION testpub4 owner to regress_publication_user2; -- fail ERROR: permission denied to change owner of publication "testpub4" HINT: The owner of a FOR ALL TABLES or ALL SEQUENCES or TABLES IN SCHEMA publication must be a superuser. ALTER PUBLICATION testpub4 owner to regress_publication_user; -- ok SET ROLE regress_publication_user; SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub5 FOR ALL TABLES; RESET client_min_messages; ALTER PUBLICATION testpub5 OWNER TO regress_publication_user3; SET ROLE regress_publication_user3; -- fail - SET ALL TABLES/SEQUENCES on a publication requires superuser privileges ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT (TABLE testpub_tbl1); -- fail ERROR: must be superuser to set ALL TABLES ALTER PUBLICATION testpub5 SET ALL TABLES; -- fail ERROR: must be superuser to set ALL TABLES ALTER PUBLICATION testpub5 SET ALL SEQUENCES; -- fail ERROR: must be superuser to set ALL SEQUENCES SET ROLE regress_publication_user; DROP PUBLICATION testpub4, testpub5; DROP ROLE regress_publication_user3; REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; DROP TABLE testpub_parted; DROP TABLE testpub_tbl1; \dRp+ testpub_default Publication testpub_default Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------------ regress_publication_user | f | f | t | t | t | f | none | f | test publication (1 row) -- fail - must be owner of publication SET ROLE regress_publication_user_dummy; ALTER PUBLICATION testpub_default RENAME TO testpub_dummy; ERROR: must be owner of publication testpub_default RESET ROLE; ALTER PUBLICATION testpub_default RENAME TO testpub_foo; \dRp testpub_foo List of publications Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root -------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- testpub_foo | regress_publication_user | f | f | t | t | t | f | none | f (1 row) -- rename back to keep the rest simple ALTER PUBLICATION testpub_foo RENAME TO testpub_default; ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; \dRp testpub_default List of publications Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root -----------------+---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- testpub_default | regress_publication_user2 | f | f | t | t | t | f | none | f (1 row) -- adding schemas and tables CREATE SCHEMA pub_test1; CREATE SCHEMA pub_test2; CREATE SCHEMA pub_test3; CREATE SCHEMA "CURRENT_SCHEMA"; CREATE TABLE pub_test1.tbl (id int, data text); CREATE TABLE pub_test1.tbl1 (id serial primary key, data text); CREATE TABLE pub_test2.tbl1 (id serial primary key, data text); CREATE TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"(id int); -- suppress warning that depends on wal_level SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub1_forschema FOR TABLES IN SCHEMA pub_test1; \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" CREATE PUBLICATION testpub2_forschema FOR TABLES IN SCHEMA pub_test1, pub_test2, pub_test3; \dRp+ testpub2_forschema Publication testpub2_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" "pub_test3" -- check create publication on CURRENT_SCHEMA CREATE PUBLICATION testpub3_forschema FOR TABLES IN SCHEMA CURRENT_SCHEMA; CREATE PUBLICATION testpub4_forschema FOR TABLES IN SCHEMA "CURRENT_SCHEMA"; CREATE PUBLICATION testpub5_forschema FOR TABLES IN SCHEMA CURRENT_SCHEMA, "CURRENT_SCHEMA"; CREATE PUBLICATION testpub6_forschema FOR TABLES IN SCHEMA "CURRENT_SCHEMA", CURRENT_SCHEMA; CREATE PUBLICATION testpub_fortable FOR TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"; RESET client_min_messages; \dRp+ testpub3_forschema Publication testpub3_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "public" \dRp+ testpub4_forschema Publication testpub4_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "CURRENT_SCHEMA" \dRp+ testpub5_forschema Publication testpub5_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "CURRENT_SCHEMA" "public" \dRp+ testpub6_forschema Publication testpub6_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "CURRENT_SCHEMA" "public" \dRp+ testpub_fortable Publication testpub_fortable Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "CURRENT_SCHEMA.CURRENT_SCHEMA" -- check create publication on CURRENT_SCHEMA where search_path is not set SET SEARCH_PATH=''; CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA CURRENT_SCHEMA; ERROR: no schema has been selected for CURRENT_SCHEMA RESET SEARCH_PATH; -- check create publication on CURRENT_SCHEMA where TABLE/TABLES in SCHEMA -- is not specified CREATE PUBLICATION testpub_forschema1 FOR CURRENT_SCHEMA; ERROR: invalid publication object list LINE 1: CREATE PUBLICATION testpub_forschema1 FOR CURRENT_SCHEMA; ^ DETAIL: One of TABLE or TABLES IN SCHEMA must be specified before a standalone table or schema name. -- check create publication on CURRENT_SCHEMA along with FOR TABLE CREATE PUBLICATION testpub_forschema1 FOR TABLE CURRENT_SCHEMA; ERROR: syntax error at or near "CURRENT_SCHEMA" LINE 1: CREATE PUBLICATION testpub_forschema1 FOR TABLE CURRENT_SCHE... ^ -- check create publication on a schema that does not exist CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist -- check create publication on a system schema CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pg_catalog; ERROR: cannot add schema "pg_catalog" to publication DETAIL: This operation is not supported for system schemas. -- check create publication on an object which is not schema CREATE PUBLICATION testpub1_forschema1 FOR TABLES IN SCHEMA testpub_view; ERROR: schema "testpub_view" does not exist -- dropping the schema should reflect the change in publication DROP SCHEMA pub_test3; \dRp+ testpub2_forschema Publication testpub2_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" -- renaming the schema should reflect the change in publication ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed; \dRp+ testpub2_forschema Publication testpub2_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1_renamed" "pub_test2" ALTER SCHEMA pub_test1_renamed RENAME to pub_test1; \dRp+ testpub2_forschema Publication testpub2_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" -- alter publication add schema ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test2; \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" -- add non existent schema ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" -- add a schema which is already added to the publication ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test1; ERROR: schema "pub_test1" is already a member of publication "testpub1_forschema" \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" -- alter publication drop schema ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2; \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" -- drop schema that is not present in the publication ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2; ERROR: tables from schema "pub_test2" are not part of the publication \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" -- drop a schema that does not exist in the system ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" -- drop all schemas ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test1; \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | (1 row) -- alter publication set multiple schema ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test2; \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" -- alter publication set non-existent schema ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" -- alter publication set it duplicate schemas should set the schemas after -- removing the duplicate schemas ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test1; \dRp+ testpub1_forschema Publication testpub1_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" -- Verify that it fails to add a schema with a column specification ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA foo (a, b); ERROR: syntax error at or near "(" LINE 1: ...LICATION testpub1_forschema ADD TABLES IN SCHEMA foo (a, b); ^ ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA foo, bar (a, b); ERROR: column specification not allowed for schema LINE 1: ...TION testpub1_forschema ADD TABLES IN SCHEMA foo, bar (a, b)... ^ -- cleanup pub_test1 schema for invalidation tests ALTER PUBLICATION testpub2_forschema DROP TABLES IN SCHEMA pub_test1; DROP PUBLICATION testpub3_forschema, testpub4_forschema, testpub5_forschema, testpub6_forschema, testpub_fortable; DROP SCHEMA "CURRENT_SCHEMA" CASCADE; NOTICE: drop cascades to table "CURRENT_SCHEMA"."CURRENT_SCHEMA" -- verify relation cache invalidations through update statement for the -- default REPLICA IDENTITY on the relation, if schema is part of the -- publication then update will fail because relation's relreplident -- option will be set, if schema is not part of the publication then update -- will be successful. INSERT INTO pub_test1.tbl VALUES(1, 'test'); -- fail UPDATE pub_test1.tbl SET id = 2; ERROR: cannot update table "tbl" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test1; -- success UPDATE pub_test1.tbl SET id = 2; ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1; -- fail UPDATE pub_test1.tbl SET id = 2; ERROR: cannot update table "tbl" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. -- verify invalidation of partition table having parent and child tables in -- different schema CREATE SCHEMA pub_testpart1; CREATE SCHEMA pub_testpart2; CREATE TABLE pub_testpart1.parent1 (a int) partition by list (a); CREATE TABLE pub_testpart2.child_parent1 partition of pub_testpart1.parent1 for values in (1); INSERT INTO pub_testpart2.child_parent1 values(1); UPDATE pub_testpart2.child_parent1 set a = 1; SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpubpart_forschema FOR TABLES IN SCHEMA pub_testpart1; RESET client_min_messages; -- fail UPDATE pub_testpart1.parent1 set a = 1; ERROR: cannot update table "child_parent1" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. UPDATE pub_testpart2.child_parent1 set a = 1; ERROR: cannot update table "child_parent1" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. DROP PUBLICATION testpubpart_forschema; -- verify invalidation of partition tables for schema publication that has -- parent and child tables of different partition hierarchies CREATE TABLE pub_testpart2.parent2 (a int) partition by list (a); CREATE TABLE pub_testpart1.child_parent2 partition of pub_testpart2.parent2 for values in (1); INSERT INTO pub_testpart1.child_parent2 values(1); UPDATE pub_testpart1.child_parent2 set a = 1; SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpubpart_forschema FOR TABLES IN SCHEMA pub_testpart2; RESET client_min_messages; -- fail UPDATE pub_testpart2.child_parent1 set a = 1; ERROR: cannot update table "child_parent1" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. UPDATE pub_testpart2.parent2 set a = 1; ERROR: cannot update table "child_parent2" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. UPDATE pub_testpart1.child_parent2 set a = 1; ERROR: cannot update table "child_parent2" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. -- alter publication set 'TABLES IN SCHEMA' on an empty publication. SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub3_forschema; RESET client_min_messages; \dRp+ testpub3_forschema Publication testpub3_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | (1 row) ALTER PUBLICATION testpub3_forschema SET TABLES IN SCHEMA pub_test1; \dRp+ testpub3_forschema Publication testpub3_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" -- create publication including both 'FOR TABLE' and 'FOR TABLES IN SCHEMA' SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_forschema_fortable FOR TABLES IN SCHEMA pub_test1, TABLE pub_test2.tbl1; CREATE PUBLICATION testpub_fortable_forschema FOR TABLE pub_test2.tbl1, TABLES IN SCHEMA pub_test1; RESET client_min_messages; \dRp+ testpub_forschema_fortable Publication testpub_forschema_fortable Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "pub_test2.tbl1" Tables from schemas: "pub_test1" \dRp+ testpub_fortable_forschema Publication testpub_fortable_forschema Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "pub_test2.tbl1" Tables from schemas: "pub_test1" -- fail specifying table without any of 'FOR TABLES IN SCHEMA' or --'FOR TABLE' or 'FOR ALL TABLES' CREATE PUBLICATION testpub_error FOR pub_test2.tbl1; ERROR: invalid publication object list LINE 1: CREATE PUBLICATION testpub_error FOR pub_test2.tbl1; ^ DETAIL: One of TABLE or TABLES IN SCHEMA must be specified before a standalone table or schema name. DROP VIEW testpub_view; DROP PUBLICATION testpub_default; DROP PUBLICATION testpub_ins_trunct; DROP PUBLICATION testpub_fortbl; DROP PUBLICATION testpub1_forschema; DROP PUBLICATION testpub2_forschema; DROP PUBLICATION testpub3_forschema; DROP PUBLICATION testpub_forschema_fortable; DROP PUBLICATION testpub_fortable_forschema; DROP PUBLICATION testpubpart_forschema; DROP SCHEMA pub_test CASCADE; NOTICE: drop cascades to table pub_test.testpub_nopk DROP SCHEMA pub_test1 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table pub_test1.tbl drop cascades to table pub_test1.tbl1 DROP SCHEMA pub_test2 CASCADE; NOTICE: drop cascades to table pub_test2.tbl1 DROP SCHEMA pub_testpart1 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table pub_testpart1.parent1 drop cascades to table pub_testpart1.child_parent2 DROP SCHEMA pub_testpart2 CASCADE; NOTICE: drop cascades to table pub_testpart2.parent2 -- Test the list of partitions published with or without -- 'PUBLISH_VIA_PARTITION_ROOT' parameter SET client_min_messages = 'ERROR'; CREATE SCHEMA sch1; CREATE SCHEMA sch2; CREATE TABLE sch1.tbl1 (a int) PARTITION BY RANGE(a); CREATE TABLE sch2.tbl1_part1 PARTITION OF sch1.tbl1 FOR VALUES FROM (1) to (10); -- Schema publication that does not include the schema that has the parent table CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=1); SELECT * FROM pg_publication_tables; pubname | schemaname | tablename | attnames | rowfilter ---------+------------+------------+----------+----------- pub | sch2 | tbl1_part1 | {a} | (1 row) DROP PUBLICATION pub; -- Table publication that does not include the parent table CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=1); SELECT * FROM pg_publication_tables; pubname | schemaname | tablename | attnames | rowfilter ---------+------------+------------+----------+----------- pub | sch2 | tbl1_part1 | {a} | (1 row) -- Table publication that includes both the parent table and the child table ALTER PUBLICATION pub ADD TABLE sch1.tbl1; SELECT * FROM pg_publication_tables; pubname | schemaname | tablename | attnames | rowfilter ---------+------------+-----------+----------+----------- pub | sch1 | tbl1 | {a} | (1 row) DROP PUBLICATION pub; -- Schema publication that does not include the schema that has the parent table CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=0); SELECT * FROM pg_publication_tables; pubname | schemaname | tablename | attnames | rowfilter ---------+------------+------------+----------+----------- pub | sch2 | tbl1_part1 | {a} | (1 row) DROP PUBLICATION pub; -- Table publication that does not include the parent table CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=0); SELECT * FROM pg_publication_tables; pubname | schemaname | tablename | attnames | rowfilter ---------+------------+------------+----------+----------- pub | sch2 | tbl1_part1 | {a} | (1 row) -- Table publication that includes both the parent table and the child table ALTER PUBLICATION pub ADD TABLE sch1.tbl1; SELECT * FROM pg_publication_tables; pubname | schemaname | tablename | attnames | rowfilter ---------+------------+------------+----------+----------- pub | sch2 | tbl1_part1 | {a} | (1 row) DROP PUBLICATION pub; DROP TABLE sch2.tbl1_part1; DROP TABLE sch1.tbl1; CREATE TABLE sch1.tbl1 (a int) PARTITION BY RANGE(a); CREATE TABLE sch1.tbl1_part1 PARTITION OF sch1.tbl1 FOR VALUES FROM (1) to (10); CREATE TABLE sch1.tbl1_part2 PARTITION OF sch1.tbl1 FOR VALUES FROM (10) to (20); CREATE TABLE sch1.tbl1_part3 (a int) PARTITION BY RANGE(a); ALTER TABLE sch1.tbl1 ATTACH PARTITION sch1.tbl1_part3 FOR VALUES FROM (20) to (30); CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch1 WITH (PUBLISH_VIA_PARTITION_ROOT=1); SELECT * FROM pg_publication_tables; pubname | schemaname | tablename | attnames | rowfilter ---------+------------+-----------+----------+----------- pub | sch1 | tbl1 | {a} | (1 row) RESET client_min_messages; DROP PUBLICATION pub; DROP TABLE sch1.tbl1; DROP SCHEMA sch1 cascade; DROP SCHEMA sch2 cascade; -- ====================================================== -- Test the 'publish_generated_columns' parameter with the following values: -- 'stored', 'none'. SET client_min_messages = 'ERROR'; CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_generated_columns = stored); \dRp+ pub1 Publication pub1 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | stored | f | (1 row) CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_generated_columns = none); \dRp+ pub2 Publication pub2 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | t | f | t | t | t | t | none | f | (1 row) DROP PUBLICATION pub1; DROP PUBLICATION pub2; -- Test the 'publish_generated_columns' parameter as 'none' and 'stored' for -- different scenarios with/without generated columns in column lists. CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED); -- Generated columns in column list, when 'publish_generated_columns'='none' CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns = none); \dRp+ pub1 Publication pub1 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.gencols" (a, gen1) -- Generated columns in column list, when 'publish_generated_columns'='stored' CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns = stored); \dRp+ pub2 Publication pub2 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | stored | f | Tables: "public.gencols" (a, gen1) -- Generated columns in column list, then set 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET (publish_generated_columns = none); \dRp+ pub2 Publication pub2 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.gencols" (a, gen1) -- Remove generated columns from column list, when 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET TABLE gencols(a); \dRp+ pub2 Publication pub2 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.gencols" (a) -- Add generated columns in column list, when 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET TABLE gencols(a, gen1); \dRp+ pub2 Publication pub2 Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description --------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.gencols" (a, gen1) DROP PUBLICATION pub1; DROP PUBLICATION pub2; DROP TABLE gencols; RESET client_min_messages; -- Test that the INSERT ON CONFLICT command correctly checks REPLICA IDENTITY -- when the target table is published. CREATE TABLE testpub_insert_onconfl_no_ri (a int unique, b int); CREATE TABLE testpub_insert_onconfl_parted (a int unique, b int) PARTITION by RANGE (a); CREATE TABLE testpub_insert_onconfl_part_no_ri PARTITION OF testpub_insert_onconfl_parted FOR VALUES FROM (1) TO (10); SET client_min_messages = 'ERROR'; CREATE PUBLICATION pub1 FOR ALL TABLES; RESET client_min_messages; -- fail - missing REPLICA IDENTITY INSERT INTO testpub_insert_onconfl_no_ri VALUES (1, 1) ON CONFLICT (a) DO UPDATE SET b = 2; ERROR: cannot update table "testpub_insert_onconfl_no_ri" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. -- ok - no updates INSERT INTO testpub_insert_onconfl_no_ri VALUES (1, 1) ON CONFLICT DO NOTHING; -- fail - missing REPLICA IDENTITY in partition testpub_insert_onconfl_no_ri INSERT INTO testpub_insert_onconfl_parted VALUES (1, 1) ON CONFLICT (a) DO UPDATE SET b = 2; ERROR: cannot update table "testpub_insert_onconfl_part_no_ri" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. -- ok - no updates INSERT INTO testpub_insert_onconfl_parted VALUES (1, 1) ON CONFLICT DO NOTHING; DROP PUBLICATION pub1; DROP TABLE testpub_insert_onconfl_no_ri; DROP TABLE testpub_insert_onconfl_parted; -- Test that the MERGE command correctly checks REPLICA IDENTITY when the -- target table is published. CREATE TABLE testpub_merge_no_ri (a int, b int); CREATE TABLE testpub_merge_pk (a int primary key, b int); SET client_min_messages = 'ERROR'; CREATE PUBLICATION pub1 FOR ALL TABLES; RESET client_min_messages; -- fail - missing REPLICA IDENTITY MERGE INTO testpub_merge_no_ri USING testpub_merge_pk s ON s.a >= 1 WHEN MATCHED THEN UPDATE SET b = s.b; ERROR: cannot update table "testpub_merge_no_ri" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. -- fail - missing REPLICA IDENTITY MERGE INTO testpub_merge_no_ri USING testpub_merge_pk s ON s.a >= 1 WHEN MATCHED THEN DELETE; ERROR: cannot delete from table "testpub_merge_no_ri" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE. -- ok - insert and do nothing are not restricted MERGE INTO testpub_merge_no_ri USING testpub_merge_pk s ON s.a >= 1 WHEN MATCHED THEN DO NOTHING WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, 0); -- ok - REPLICA IDENTITY is DEFAULT and table has a PK MERGE INTO testpub_merge_pk USING testpub_merge_no_ri s ON s.a >= 1 WHEN MATCHED AND s.a > 0 THEN UPDATE SET b = s.b WHEN MATCHED THEN DELETE; DROP PUBLICATION pub1; DROP TABLE testpub_merge_no_ri; DROP TABLE testpub_merge_pk; RESET SESSION AUTHORIZATION; DROP ROLE regress_publication_user, regress_publication_user2; DROP ROLE regress_publication_user_dummy; -- Test pg_get_publication_tables(text[], oid) function CREATE SCHEMA gpt_test_sch; CREATE TABLE gpt_test_sch.tbl_sch (id int); CREATE TABLE tbl_normal (id int); CREATE TABLE tbl_parent (id1 int, id2 int, id3 int) PARTITION BY RANGE (id1); CREATE TABLE tbl_part1 PARTITION OF tbl_parent FOR VALUES FROM (1) TO (10); CREATE VIEW gpt_test_view AS SELECT * FROM tbl_normal; SET client_min_messages = 'ERROR'; CREATE PUBLICATION pub_all FOR ALL TABLES WITH (publish_via_partition_root = true); CREATE PUBLICATION pub_all_no_viaroot FOR ALL TABLES WITH (publish_via_partition_root = false); CREATE PUBLICATION pub_all_except FOR ALL TABLES EXCEPT (TABLE tbl_parent, gpt_test_sch.tbl_sch) WITH (publish_via_partition_root = true); CREATE PUBLICATION pub_all_except_no_viaroot FOR ALL TABLES EXCEPT (TABLE tbl_parent, gpt_test_sch.tbl_sch) WITH (publish_via_partition_root = false); CREATE PUBLICATION pub_schema FOR TABLES IN SCHEMA gpt_test_sch; CREATE PUBLICATION pub_normal FOR TABLE tbl_normal WHERE (id < 10); CREATE PUBLICATION pub_part_leaf FOR TABLE tbl_part1 WITH (publish_via_partition_root = false); CREATE PUBLICATION pub_part_parent FOR TABLE tbl_parent (id1, id2) WHERE (id1 = 10) WITH (publish_via_partition_root = true); CREATE PUBLICATION pub_part_parent_no_viaroot FOR TABLE tbl_parent WITH (publish_via_partition_root = false); CREATE PUBLICATION pub_part_parent_child FOR TABLE tbl_parent, tbl_part1 WITH (publish_via_partition_root = true); RESET client_min_messages; CREATE FUNCTION test_gpt(pubnames text[], relname text) RETURNS TABLE ( pubname text, relname name, attrs text, qual text ) BEGIN ATOMIC SELECT p.pubname, c.relname, gpt.attrs::text, pg_get_expr(gpt.qual, gpt.relid) FROM pg_get_publication_tables(pubnames, relname::regclass::oid) gpt JOIN pg_publication p ON p.oid = gpt.pubid JOIN pg_class c ON c.oid = gpt.relid ORDER BY p.pubname, c.relname; END; SELECT * FROM test_gpt(ARRAY['pub_normal'], 'tbl_normal'); pubname | relname | attrs | qual ------------+------------+-------+----------- pub_normal | tbl_normal | 1 | (id < 10) (1 row) SELECT * FROM test_gpt(ARRAY['pub_normal'], 'gpt_test_sch.tbl_sch'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_schema'], 'gpt_test_sch.tbl_sch'); pubname | relname | attrs | qual ------------+---------+-------+------ pub_schema | tbl_sch | 1 | (1 row) SELECT * FROM test_gpt(ARRAY['pub_schema'], 'tbl_normal'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_part_parent'], 'tbl_parent'); pubname | relname | attrs | qual -----------------+------------+-------+------------ pub_part_parent | tbl_parent | 1 2 | (id1 = 10) (1 row) SELECT * FROM test_gpt(ARRAY['pub_part_parent'], 'tbl_part1'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_part_parent_no_viaroot'], 'tbl_part1'); pubname | relname | attrs | qual ----------------------------+-----------+-------+------ pub_part_parent_no_viaroot | tbl_part1 | 1 2 3 | (1 row) SELECT * FROM test_gpt(ARRAY['pub_part_parent_no_viaroot'], 'tbl_parent'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_part_leaf'], 'tbl_part1'); pubname | relname | attrs | qual ---------------+-----------+-------+------ pub_part_leaf | tbl_part1 | 1 2 3 | (1 row) SELECT * FROM test_gpt(ARRAY['pub_part_leaf'], 'tbl_parent'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_all'], 'tbl_parent'); pubname | relname | attrs | qual ---------+------------+-------+------ pub_all | tbl_parent | 1 2 3 | (1 row) SELECT * FROM test_gpt(ARRAY['pub_all'], 'tbl_part1'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_all_no_viaroot'], 'tbl_part1'); pubname | relname | attrs | qual --------------------+-----------+-------+------ pub_all_no_viaroot | tbl_part1 | 1 2 3 | (1 row) SELECT * FROM test_gpt(ARRAY['pub_all_no_viaroot'], 'tbl_parent'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_part_parent_child'], 'tbl_parent'); pubname | relname | attrs | qual -----------------------+------------+-------+------ pub_part_parent_child | tbl_parent | 1 2 3 | (1 row) SELECT * FROM test_gpt(ARRAY['pub_part_parent_child'], 'tbl_part1'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -- test for the EXCEPT clause SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'tbl_normal'); pubname | relname | attrs | qual ----------------+------------+-------+------ pub_all_except | tbl_normal | 1 | (1 row) SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'gpt_test_sch.tbl_sch'); -- no result (excluded) pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'tbl_parent'); -- no result (excluded) pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'tbl_part1'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_all_except_no_viaroot'], 'tbl_normal'); pubname | relname | attrs | qual ---------------------------+------------+-------+------ pub_all_except_no_viaroot | tbl_normal | 1 | (1 row) SELECT * FROM test_gpt(ARRAY['pub_all_except_no_viaroot'], 'gpt_test_sch.tbl_sch'); -- no result (excluded) pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_all_except_no_viaroot'], 'tbl_parent'); -- no result (excluded) pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) SELECT * FROM test_gpt(ARRAY['pub_all_except_no_viaroot'], 'tbl_part1'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -- two rows with different row filter SELECT * FROM test_gpt(ARRAY['pub_all', 'pub_normal'], 'tbl_normal'); pubname | relname | attrs | qual ------------+------------+-------+----------- pub_all | tbl_normal | 1 | pub_normal | tbl_normal | 1 | (id < 10) (2 rows) -- one row with 'pub_part_parent' SELECT * FROM test_gpt(ARRAY['pub_part_parent', 'pub_part_parent_no_viaroot'], 'tbl_parent'); pubname | relname | attrs | qual -----------------+------------+-------+------------ pub_part_parent | tbl_parent | 1 2 | (id1 = 10) (1 row) -- no result, tbl_parent is the effective published OID due to pubviaroot SELECT * FROM test_gpt(ARRAY['pub_part_parent', 'pub_all'], 'tbl_part1'); pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -- no result, non-existent publication SELECT * FROM test_gpt(ARRAY['no_such_pub'], 'tbl_normal'); pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -- no result, non-table object SELECT * FROM test_gpt(ARRAY['pub_all'], 'gpt_test_view'); pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -- no result, empty publication array SELECT * FROM test_gpt(ARRAY[]::text[], 'tbl_normal'); pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -- no result, OID 0 as target_relid SELECT * FROM pg_get_publication_tables(ARRAY['pub_normal'], 0::oid); pubid | relid | attrs | qual -------+-------+-------+------ (0 rows) -- Clean up DROP FUNCTION test_gpt(text[], text); DROP PUBLICATION pub_all; DROP PUBLICATION pub_all_no_viaroot; DROP PUBLICATION pub_all_except; DROP PUBLICATION pub_all_except_no_viaroot; DROP PUBLICATION pub_schema; DROP PUBLICATION pub_normal; DROP PUBLICATION pub_part_leaf; DROP PUBLICATION pub_part_parent; DROP PUBLICATION pub_part_parent_no_viaroot; DROP PUBLICATION pub_part_parent_child; DROP VIEW gpt_test_view; DROP TABLE tbl_normal, tbl_parent, tbl_part1; DROP SCHEMA gpt_test_sch CASCADE; NOTICE: drop cascades to table gpt_test_sch.tbl_sch -- stage objects for pg_dump tests CREATE SCHEMA pubme CREATE TABLE t0 (c int, d int) CREATE TABLE t1 (c int); CREATE SCHEMA pubme2 CREATE TABLE t0 (c int, d int); SET client_min_messages = 'ERROR'; CREATE PUBLICATION dump_pub_qual_1ct FOR TABLE ONLY pubme.t0 (c, d) WHERE (c > 0); CREATE PUBLICATION dump_pub_qual_2ct FOR TABLE ONLY pubme.t0 (c) WHERE (c > 0), TABLE ONLY pubme.t1 (c); CREATE PUBLICATION dump_pub_nsp_1ct FOR TABLES IN SCHEMA pubme; CREATE PUBLICATION dump_pub_nsp_2ct FOR TABLES IN SCHEMA pubme, TABLES IN SCHEMA pubme2; CREATE PUBLICATION dump_pub_all FOR TABLE ONLY pubme.t0, TABLE ONLY pubme.t1 WHERE (c < 0), TABLES IN SCHEMA pubme, TABLES IN SCHEMA pubme2 WITH (publish_via_partition_root = true); RESET client_min_messages;