On Tue, May 26, 2026 at 11:27 AM Peter Smith <[email protected]> wrote: > > Hi Nisha. > > Some review comments for patch v6-0003. >
Thanks for the review. All comments are addressed in v7. Please find responses below for a few of the comments. > > 3. > The <literal>EXCEPT</literal> clause can be used with > - <literal>ADD TABLES IN SCHEMA</literal> to exclude specific tables from a > + <literal>ADD TABLES IN SCHEMA</literal> and > + <literal>SET TABLES IN SCHEMA</literal> to exclude specific tables from a > schema-level publication. <literal>EXCEPT</literal> is not supported with > <literal>DROP TABLES IN SCHEMA</literal>; instead, dropping a schema from > the publication automatically removes all of its associated > > 3a. > This whole description section seems arranged in a confusing way IMO. > Anyway, it is not all the fault of the current patch. But I don't > think it should be talking about "SET TABLES IN SCHEMA" here because > that was all mentioned already in the earlier "third variant" > paragraph. > Right. it seems repeating. Removed "SET TABLES IN SCHEMA" related info. > ~ > > 3b. > That last sentence all about EXCEPT with DROP TABLES IN SCHEMA seems > redundant to me. It is not allowed by the synopsis, so there is no > possible confusion about it being supported. Isn't it better to just > say nothing? > Okay, that makes sense. Fixed. > ~~~ > > 4b. > This description about EXCEPT is missing talking about FOR ALL TABLES > EXCEPT, but IIRC I already reported that in a previous review. > Yes, we can handle this in a separate patch. > ~~~ > > PublicationDropSchemas: > > 12. > + /* > + * Collect prexcept rows for tables belonging to this schema before > + * removing the schema entry. GetExcludedPublicationTables relies on > + * is_schema_publication(), which scans pg_publication_namespace; if > + * this is the last schema in the publication, performDeletion() below > + * would remove that row and make is_schema_publication() return > + * false, tripping the assertion. > + */ > > What assertion? > The assertion is Assert(GetPublication(pubid)->alltables || is_schema_publication(pubid)) in GetExcludedPublicationTables(). I’ve trimmed the comment a bit, as it felt slightly over-explained. ~~~~ Please find the updated patch-set v7 attached. -- Thanks, Nisha
From b46bea926dfb201005201bbefda3716ba7a8beee Mon Sep 17 00:00:00 2001 From: Nisha Moond <[email protected]> Date: Mon, 4 May 2026 12:49:27 +0530 Subject: [PATCH v7 1/3] Support EXCEPT clause for schema-level publications Extend table exclusion support in publications to allow specific tables to be excluded from schema-level publications using an EXCEPT clause in CREATE PUBLICATION. Supported syntax: CREATE PUBLICATION <pub> FOR TABLES IN SCHEMA s EXCEPT (TABLE t1,...); --- doc/src/sgml/logical-replication.sgml | 3 +- doc/src/sgml/ref/create_publication.sgml | 22 +++- src/backend/catalog/pg_publication.c | 97 +++++++++++--- src/backend/commands/publicationcmds.c | 65 ++++++++-- src/backend/parser/gram.y | 52 +++++++- src/backend/replication/pgoutput/pgoutput.c | 30 ++++- src/bin/psql/describe.c | 18 +++ src/bin/psql/tab-complete.in.c | 24 +++- src/include/catalog/pg_publication.h | 3 +- src/include/nodes/parsenodes.h | 2 + src/test/regress/expected/publication.out | 106 +++++++++++++++- src/test/regress/sql/publication.sql | 69 +++++++++- src/test/subscription/t/037_except.pl | 133 +++++++++++++++++++- 13 files changed, 577 insertions(+), 47 deletions(-) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 9e7868487de..1433d2660fe 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -117,7 +117,8 @@ or <literal>FOR ALL SEQUENCES</literal>. Unlike tables, sequences can be synchronized at any time. For more information, see <xref linkend="logical-replication-sequences"/>. When a publication is - created with <literal>FOR ALL TABLES</literal>, a table or set of tables can + created with <literal>FOR ALL TABLES</literal> or + <literal>FOR TABLES IN SCHEMA</literal>, a table or set of tables can be explicitly excluded from publication using the <link linkend="sql-createpublication-params-for-except-table"><literal>EXCEPT</literal></link> clause. diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index f82d640e6ca..7fa0bd11f7b 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -28,7 +28,7 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable> <phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase> TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ] - TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ] + TABLES IN SCHEMA <replaceable class="parameter">tables_in_schema</replaceable> [, ... ] <phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase> @@ -39,6 +39,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable> <replaceable class="parameter">table_object</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ] +<phrase>and <replaceable class="parameter">tables_in_schema</replaceable> is:</phrase> + + { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [ EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ] + <phrase>and <replaceable class="parameter">except_table_object</replaceable> is:</phrase> TABLE <replaceable class="parameter">table_object</replaceable> [, ... ] @@ -142,6 +146,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable> <para> Marks the publication as one that replicates changes for all tables in the specified list of schemas, including tables created in the future. + Tables listed in the <literal>EXCEPT</literal> clause for a given schema + are excluded from the publication. </para> <para> @@ -173,7 +179,7 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable> <para> Marks the publication as one that replicates changes for all tables in the database, including tables created in the future. Tables listed in - <literal>EXCEPT</literal> clause are excluded from the publication. + the <literal>EXCEPT</literal> clause are excluded from the publication. </para> </listitem> </varlistentry> @@ -198,7 +204,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable> <listitem> <para> This clause specifies a list of tables to be excluded from the - publication. + publication. It can be used with <literal>FOR ALL TABLES</literal> or + <literal>FOR TABLES IN SCHEMA</literal>. </para> <para> For inherited tables, if <literal>ONLY</literal> is specified before the @@ -515,6 +522,15 @@ CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES I CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales; </programlisting></para> + <para> + Create a publication that publishes all changes for all the tables present in + the schema <structname>sales</structname>, except + <structname>internal</structname> and <structname>drafts</structname>: +<programlisting> +CREATE PUBLICATION sales_filtered FOR TABLES IN SCHEMA sales EXCEPT (TABLE internal, drafts); +</programlisting> + </para> + <para> Create a publication that publishes all changes for table <structname>users</structname>, but replicates only columns <structname>user_id</structname> and diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 5c457d9aca8..b4c5a317869 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -444,9 +444,14 @@ GetPubPartitionOptionRelations(List *result, PublicationPartOpt pub_partopt, * * Note that the list of ancestors should be ordered such that the topmost * ancestor is at the end of the list. + * + * except_pubids is a list of publication OIDs whose schema membership + * should be ignored for the ancestor (because the ancestor is in their + * EXCEPT clause). */ Oid -GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level) +GetTopMostAncestorInPublication(Oid puboid, List *ancestors, + int *ancestor_level, List *except_pubids) { ListCell *lc; Oid topmost_relid = InvalidOid; @@ -470,7 +475,7 @@ GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level if (ancestor_level) *ancestor_level = level; } - else + else if (!list_member_oid(except_pubids, puboid)) { aschemaPubids = GetSchemaPublications(get_rel_namespace(ancestor)); if (list_member_oid(aschemaPubids, puboid)) @@ -545,18 +550,30 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri, * duplicates, it's here just to provide nicer error message in common * case. The real protection is the unique key on the catalog. */ - if (SearchSysCacheExists2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid), - ObjectIdGetDatum(pubid))) + tup = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid), + ObjectIdGetDatum(pubid)); + + if (HeapTupleIsValid(tup)) { + bool is_except = ((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept; + + ReleaseSysCache(tup); table_close(rel, RowExclusiveLock); if (if_not_exists) return InvalidObjectAddress; - ereport(ERROR, - (errcode(ERRCODE_DUPLICATE_OBJECT), - errmsg("relation \"%s\" is already member of publication \"%s\"", - RelationGetRelationName(targetrel), pub->name))); + if (is_except) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("relation \"%s\" cannot be added because it is excluded from publication \"%s\"", + RelationGetQualifiedRelationName(targetrel), + pub->name))); + else + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("relation \"%s\" is already member of publication \"%s\"", + RelationGetQualifiedRelationName(targetrel), pub->name))); } check_publication_add_relation(pri); @@ -982,12 +999,13 @@ GetIncludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt) * Gets list of table oids that were specified in the EXCEPT clause for a * publication. * - * This should only be used FOR ALL TABLES publications. + * This is used for FOR ALL TABLES and FOR TABLES IN SCHEMA publications, + * both of which support EXCEPT TABLE. */ List * GetExcludedPublicationTables(Oid pubid, PublicationPartOpt pub_partopt) { - Assert(GetPublication(pubid)->alltables); + Assert(GetPublication(pubid)->alltables || is_schema_publication(pubid)); return get_publication_relations(pubid, pub_partopt, true); } @@ -1049,13 +1067,13 @@ GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot) TableScanDesc scan; HeapTuple tuple; List *result = NIL; - List *exceptlist = NIL; + List *except_relids = NIL; Assert(!(relkind == RELKIND_SEQUENCE && pubviaroot)); /* EXCEPT filtering applies only to relations, not sequences */ if (relkind == RELKIND_RELATION) - exceptlist = GetExcludedPublicationTables(pubid, pubviaroot ? + except_relids = GetExcludedPublicationTables(pubid, pubviaroot ? PUBLICATION_PART_ROOT : PUBLICATION_PART_LEAF); @@ -1075,7 +1093,7 @@ GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot) if (is_publishable_class(relid, relForm) && !(relForm->relispartition && pubviaroot) && - !list_member_oid(exceptlist, relid)) + !list_member_oid(except_relids, relid)) result = lappend_oid(result, relid); } @@ -1097,7 +1115,7 @@ GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot) if (is_publishable_class(relid, relForm) && !relForm->relispartition && - !list_member_oid(exceptlist, relid)) + !list_member_oid(except_relids, relid)) result = lappend_oid(result, relid); } @@ -1232,22 +1250,67 @@ GetSchemaPublicationRelations(Oid schemaid, PublicationPartOpt pub_partopt) /* * Gets the list of all relations published by FOR TABLES IN SCHEMA - * publication. + * publication, excluding any tables listed in the EXCEPT clause. */ List * GetAllSchemaPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt) { List *result = NIL; List *pubschemalist = GetPublicationSchemas(pubid); + List *except_relids = NIL; ListCell *cell; + /* get the list of tables excluded via EXCEPT TABLE for this publication */ + if (pubschemalist != NIL) + except_relids = GetExcludedPublicationTables(pubid, pub_partopt); + foreach(cell, pubschemalist) { Oid schemaid = lfirst_oid(cell); List *schemaRels = NIL; schemaRels = GetSchemaPublicationRelations(schemaid, pub_partopt); - result = list_concat(result, schemaRels); + + if (except_relids != NIL) + { + /* filter out any tables that appear in the EXCEPT list */ + ListCell *rlc; + + foreach(rlc, schemaRels) + { + Oid relid = lfirst_oid(rlc); + bool excluded = list_member_oid(except_relids, relid); + + /* + * Also exclude any relation whose partition ancestor is in + * the EXCEPT list. This matters when pub_partopt is + * PUBLICATION_PART_ROOT: the except list holds only the root + * OID, but the schema scan may also return individual + * partition relations that live in the same schema. + */ + if (!excluded && get_rel_relispartition(relid)) + { + List *ancestors = get_partition_ancestors(relid); + ListCell *alc; + + foreach(alc, ancestors) + { + if (list_member_oid(except_relids, lfirst_oid(alc))) + { + excluded = true; + break; + } + } + list_free(ancestors); + } + + if (!excluded) + result = lappend_oid(result, relid); + } + list_free(schemaRels); + } + else + result = list_concat(result, schemaRels); } return result; @@ -1381,7 +1444,7 @@ is_table_publishable_in_publication(Oid relid, Publication *pub) * the publication, it should be included (return true). */ if (relispartition && - OidIsValid(GetTopMostAncestorInPublication(pub->oid, ancestors, NULL))) + OidIsValid(GetTopMostAncestorInPublication(pub->oid, ancestors, NULL, NIL))) return !pub->pubviaroot; /* diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c index 440adb356ad..65ae40210db 100644 --- a/src/backend/commands/publicationcmds.c +++ b/src/backend/commands/publicationcmds.c @@ -181,7 +181,7 @@ parse_publication_options(ParseState *pstate, */ static void ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate, - List **rels, List **exceptrels, List **schemas) + List **rels, List **except_rel_names, List **schemas) { ListCell *cell; PublicationObjSpec *pubobj; @@ -200,7 +200,7 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate, { case PUBLICATIONOBJ_EXCEPT_TABLE: pubobj->pubtable->except = true; - *exceptrels = lappend(*exceptrels, pubobj->pubtable); + *except_rel_names = lappend(*except_rel_names, pubobj->pubtable); break; case PUBLICATIONOBJ_TABLE: pubobj->pubtable->except = false; @@ -305,7 +305,7 @@ pub_rf_contains_invalid_column(Oid pubid, Relation relation, List *ancestors, if (pubviaroot && relation->rd_rel->relispartition) { publish_as_relid - = GetTopMostAncestorInPublication(pubid, ancestors, NULL); + = GetTopMostAncestorInPublication(pubid, ancestors, NULL, NIL); if (!OidIsValid(publish_as_relid)) publish_as_relid = relid; @@ -389,7 +389,7 @@ pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors, */ if (pubviaroot && relation->rd_rel->relispartition) { - publish_as_relid = GetTopMostAncestorInPublication(pubid, ancestors, NULL); + publish_as_relid = GetTopMostAncestorInPublication(pubid, ancestors, NULL, NIL); if (!OidIsValid(publish_as_relid)) publish_as_relid = relid; @@ -849,7 +849,7 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) char publish_generated_columns; AclResult aclresult; List *relations = NIL; - List *exceptrelations = NIL; + List *except_rel_names = NIL; List *schemaidlist = NIL; /* must have CREATE privilege on database */ @@ -936,16 +936,16 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) /* Associate objects with the publication. */ ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations, - &exceptrelations, &schemaidlist); + &except_rel_names, &schemaidlist); if (stmt->for_all_tables) { /* Process EXCEPT table list */ - if (exceptrelations != NIL) + if (except_rel_names != NIL) { List *rels; - rels = OpenTableList(exceptrelations); + rels = OpenTableList(except_rel_names); PublicationAddTables(puboid, rels, true, NULL); CloseTableList(rels); } @@ -959,6 +959,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) } else if (!stmt->for_all_sequences) { + List *explicitrelids = NIL; + /* FOR TABLES IN SCHEMA requires superuser */ if (schemaidlist != NIL && !superuser()) ereport(ERROR, @@ -977,6 +979,19 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) schemaidlist != NIL, publish_via_partition_root); + /* + * Collect explicit table OIDs now, before we close the relation + * list, so that except-table validation below can check for + * contradictions without relying on a catalog scan that might not + * yet see the just-inserted rows. + */ + if (except_rel_names != NIL) + { + foreach_ptr(PublicationRelInfo, pri, rels) + explicitrelids = lappend_oid(explicitrelids, + RelationGetRelid(pri->relation)); + } + PublicationAddTables(puboid, rels, true, NULL); CloseTableList(rels); } @@ -989,6 +1004,34 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) */ LockSchemaList(schemaidlist); PublicationAddSchemas(puboid, schemaidlist, true, NULL); + + if (except_rel_names != NIL) + { + List *except_rels; + + except_rels = OpenTableList(except_rel_names); + + /* + * Validate that each excluded table is not also in the + * explicit table list (which would be contradictory). Use the + * in-memory explicitrelids collected above rather than + * re-reading the catalog, which may not yet see the + * just-inserted rows. + */ + foreach_ptr(PublicationRelInfo, pri, except_rels) + { + Oid except_relid = RelationGetRelid(pri->relation); + + if (list_member_oid(explicitrelids, except_relid)) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("table \"%s\" cannot appear in both the table list and the EXCEPT clause", + RelationGetQualifiedRelationName(pri->relation))); + } + + PublicationAddTables(puboid, except_rels, true, NULL); + CloseTableList(except_rels); + } } } @@ -1683,12 +1726,12 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt) else { List *relations = NIL; - List *exceptrelations = NIL; + List *except_rel_names = NIL; List *schemaidlist = NIL; Oid pubid = pubform->oid; ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations, - &exceptrelations, &schemaidlist); + &except_rel_names, &schemaidlist); CheckAlterPublication(stmt, tup, relations, schemaidlist); @@ -1711,7 +1754,7 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt) errmsg("publication \"%s\" does not exist", stmt->pubname)); - relations = list_concat(relations, exceptrelations); + relations = list_concat(relations, except_rel_names); AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext, schemaidlist != NIL); AlterPublicationSchemas(stmt, tup, schemaidlist); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index ff4e1388c55..4514ef7f9c2 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -58,6 +58,7 @@ #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "parser/parser.h" +#include "utils/builtins.h" #include "utils/datetime.h" #include "utils/xml.h" @@ -11272,7 +11273,7 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec * pub_obj is one of: * * TABLE table [, ...] - * TABLES IN SCHEMA schema [, ...] + * TABLES IN SCHEMA schema [EXCEPT (TABLE table [, ...] )] [, ...] * *****************************************************************************/ @@ -11332,23 +11333,26 @@ PublicationObjSpec: $$->pubtable->columns = $3; $$->pubtable->whereClause = $4; } - | TABLES IN_P SCHEMA ColId + | TABLES IN_P SCHEMA ColId opt_pub_except_clause { $$ = makeNode(PublicationObjSpec); $$->pubobjtype = PUBLICATIONOBJ_TABLES_IN_SCHEMA; $$->name = $4; + $$->except_tables = $5; $$->location = @4; } - | TABLES IN_P SCHEMA CURRENT_SCHEMA + | TABLES IN_P SCHEMA CURRENT_SCHEMA opt_pub_except_clause { $$ = makeNode(PublicationObjSpec); $$->pubobjtype = PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA; + $$->except_tables = $5; $$->location = @4; } - | ColId opt_column_list OptWhereClause + | ColId opt_column_list OptWhereClause opt_pub_except_clause { $$ = makeNode(PublicationObjSpec); $$->pubobjtype = PUBLICATIONOBJ_CONTINUATION; + $$->except_tables = $4; /* * If either a row filter or column list is specified, create * a PublicationTable object. @@ -11392,10 +11396,11 @@ PublicationObjSpec: $$->pubtable->columns = $2; $$->pubtable->whereClause = $3; } - | CURRENT_SCHEMA + | CURRENT_SCHEMA opt_pub_except_clause { $$ = makeNode(PublicationObjSpec); $$->pubobjtype = PUBLICATIONOBJ_CONTINUATION; + $$->except_tables = $2; $$->location = @1; } ; @@ -20784,6 +20789,8 @@ preprocess_pub_all_objtype_list(List *all_objects_list, List **pubobjects, /* * Process pubobjspec_list to check for errors in any of the objects and * convert PUBLICATIONOBJ_CONTINUATION into appropriate PublicationObjSpecType. + * Also flattens except_tables from TABLES IN SCHEMA nodes into the list so + * that ObjectsInPublicationToOids() sees them as top-level EXCEPT_TABLE entries. */ static void preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner) @@ -20812,6 +20819,13 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner) if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLE) { + /* EXCEPT is not valid for table objects */ + if (pubobj->except_tables != NIL) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("EXCEPT is not allowed for TABLE publication objects"), + parser_errposition(pubobj->location)); + /* relation name or pubtable must be set for this type of object */ if (!pubobj->name && !pubobj->pubtable) ereport(ERROR, @@ -20860,6 +20874,34 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner) errcode(ERRCODE_SYNTAX_ERROR), errmsg("invalid schema name"), parser_errposition(pubobj->location)); + + /* Flatten EXCEPT entries into the top-level list */ + foreach_ptr(PublicationObjSpec, eobj, pubobj->except_tables) + { + /* + * Unqualified names are implicitly qualified with the parent + * schema. Qualified names must match the parent schema — + * each EXCEPT clause is bound to exactly one schema, so + * cross-schema entries are rejected at parse time. + */ + if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLES_IN_SCHEMA) + { + const char *eobj_schemaname = eobj->pubtable->relation->schemaname; + const char *eobj_relname = eobj->pubtable->relation->relname; + + if (eobj_schemaname == NULL) + eobj->pubtable->relation->schemaname = pubobj->name; + else if (strcmp(eobj_schemaname, pubobj->name) != 0) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("table \"%s\" in EXCEPT clause does not belong to schema \"%s\"", + quote_qualified_identifier(eobj_schemaname, eobj_relname), + pubobj->name), + parser_errposition(eobj->location)); + } + } + pubobjspec_list = list_concat(pubobjspec_list, pubobj->except_tables); + pubobj->except_tables = NIL; } prevobjtype = pubobj->pubobjtype; diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c index 4ecfcbff7ab..c5f17bf2338 100644 --- a/src/backend/replication/pgoutput/pgoutput.c +++ b/src/backend/replication/pgoutput/pgoutput.c @@ -2097,6 +2097,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) * are absorbed while decoding WAL. */ List *schemaPubids = GetSchemaPublications(schemaId); + List *except_pubids; ListCell *lc; Oid publish_as_relid = relid; int publish_ancestor_level = 0; @@ -2104,6 +2105,28 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) char relkind = get_rel_relkind(relid); List *rel_publications = NIL; + /* + * For the schema EXCEPT check, we must look up the top-most ancestor + * rather than the relation itself. check_publication_add_relation() + * prevents individual partitions from appearing in the EXCEPT clause, + * so only a root (non-partition) table can have prexcept = true. + * Using the partition's own OID would always return NIL and miss the + * exclusion. + */ + Oid root_relid; + + if (am_partition) + { + List *part_ancestors = get_partition_ancestors(relid); + + root_relid = llast_oid(part_ancestors); + list_free(part_ancestors); + } + else + root_relid = relid; + + except_pubids = GetRelationExcludedPublications(root_relid); + /* Reload publications if needed before use. */ if (!publications_valid) { @@ -2267,7 +2290,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) ancestor = GetTopMostAncestorInPublication(pub->oid, ancestors, - &level); + &level, + except_pubids); if (ancestor != InvalidOid) { @@ -2281,7 +2305,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) } if (list_member_oid(pubids, pub->oid) || - list_member_oid(schemaPubids, pub->oid) || + (list_member_oid(schemaPubids, pub->oid) && + !list_member_oid(except_pubids, pub->oid)) || ancestor_published) publish = true; } @@ -2360,6 +2385,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) list_free(pubids); list_free(schemaPubids); + list_free(except_pubids); list_free(rel_publications); entry->replicate_valid = true; diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index e1449654f96..e5b1a70e05e 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -7038,6 +7038,24 @@ describePublications(const char *pattern) if (!addFooterToPublicationDesc(&buf, _("Tables from schemas:"), true, &cont)) goto error_return; + + if (pset.sversion >= 190000) + { + /* + * Get tables in the EXCEPT clause for this schema + * publication. + */ + printfPQExpBuffer(&buf, + "SELECT concat(c.relnamespace::regnamespace, '.', c.relname)\n" + "FROM pg_catalog.pg_class c\n" + " JOIN pg_catalog.pg_publication_rel pr ON c.oid = pr.prrelid\n" + "WHERE pr.prpubid = '%s'\n" + " AND pr.prexcept\n" + "ORDER BY 1", pubid); + if (!addFooterToPublicationDesc(&buf, _("Except tables:"), + true, &cont)) + goto error_return; + } } } else diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index de547a8cb37..fe11dc619ac 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -1065,6 +1065,15 @@ static const SchemaQuery Query_for_trigger_of_table = { "SELECT nspname FROM pg_catalog.pg_namespace "\ " WHERE nspname LIKE '%s'" +#define Query_for_list_of_tables_in_schema \ +"SELECT n.nspname || '.' || c.relname "\ +" FROM pg_catalog.pg_class c "\ +" JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "\ +" WHERE c.relkind IN (" CppAsString2(RELKIND_RELATION) ", " \ + CppAsString2(RELKIND_PARTITIONED_TABLE) ") "\ +" AND (n.nspname || '.' || c.relname) LIKE '%s' "\ +" AND n.nspname = '%s'" + /* Use COMPLETE_WITH_QUERY_VERBATIM with these queries for GUC names: */ #define Query_for_list_of_alter_system_set_vars \ "SELECT pg_catalog.lower(name) FROM pg_catalog.pg_settings "\ @@ -3787,8 +3796,19 @@ match_previous_words(int pattern_id, COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_schemas " AND nspname NOT LIKE E'pg\\\\_%%'", "CURRENT_SCHEMA"); - else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny) && (!ends_with(prev_wd, ','))) - COMPLETE_WITH("WITH ("); + else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny) && !ends_with(prev_wd, ',')) + COMPLETE_WITH("EXCEPT ( TABLE", "WITH ("); + else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT")) + COMPLETE_WITH("( TABLE"); + else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(")) + COMPLETE_WITH("TABLE"); + else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE")) + { + set_completion_reference(prev4_wd); + COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_schema); + } + else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE", MatchAnyN) && !ends_with(prev_wd, ',')) + COMPLETE_WITH(")"); /* Complete "CREATE PUBLICATION <name> [...] WITH" */ else if (Matches("CREATE", "PUBLICATION", MatchAnyN, "WITH", "(")) COMPLETE_WITH("publish", "publish_generated_columns", "publish_via_partition_root"); diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h index 89b4bb14f62..53e3d7c6f3d 100644 --- a/src/include/catalog/pg_publication.h +++ b/src/include/catalog/pg_publication.h @@ -191,7 +191,8 @@ extern List *GetPubPartitionOptionRelations(List *result, PublicationPartOpt pub_partopt, Oid relid); extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors, - int *ancestor_level); + int *ancestor_level, + List *except_pubids); extern bool is_publishable_relation(Relation rel); extern bool is_schema_publication(Oid pubid); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 91377a6cde3..98a03c0eeda 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -4493,6 +4493,8 @@ typedef struct PublicationObjSpec PublicationObjSpecType pubobjtype; /* type of this publication object */ char *name; PublicationTable *pubtable; + List *except_tables; /* tables specified in the EXCEPT clause (for + * TABLES IN SCHEMA) */ ParseLoc location; /* token location, or -1 if unknown */ } PublicationObjSpec; diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index 29e54b214a0..161db458f49 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -270,6 +270,12 @@ CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (test ERROR: syntax error at or near "testpub_tbl1" LINE 1: ..._foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tb... ^ +-- fail - EXCEPT is not allowed for FOR TABLE publications +CREATE PUBLICATION testpub_except_err + FOR TABLE testpub_tbl1, testpub_tbl2 EXCEPT (TABLE testpub_tbl3); +ERROR: EXCEPT is not allowed for TABLE publication objects +LINE 2: FOR TABLE testpub_tbl1, testpub_tbl2 EXCEPT (TABLE testp... + ^ --------------------------------------------- -- SET ALL TABLES/SEQUENCES --------------------------------------------- @@ -470,7 +476,103 @@ HINT: Change the publication's EXCEPT clause using ALTER PUBLICATION ... SET AL RESET client_min_messages; DROP TABLE testpub_root, testpub_part1, tab_main; DROP PUBLICATION testpub8; ---- Tests for publications with SEQUENCES +--------------------------------------------- +-- EXCEPT tests for TABLES IN SCHEMA +--------------------------------------------- +SET client_min_messages = 'ERROR'; +-- Create tables in pub_test for these tests +CREATE TABLE pub_test.testpub_tbl_s1 (a int primary key, b text); +CREATE TABLE pub_test.testpub_tbl_s2 (x int primary key, y text); +-- Create same-named tables in public to verify unqualified EXCEPT entries +-- are qualified with the named schema, not public +CREATE TABLE testpub_nopk (foo int, bar int); +CREATE TABLE testpub_tbl_s1 (a int primary key, b text); +-- Basic: exclude one table from a schema publication +CREATE PUBLICATION testpub_schema_except1 + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); +\dRp+ testpub_schema_except1 + Publication testpub_schema_except1 + 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" +Except tables: + "pub_test.testpub_tbl_s1" + +-- Exclude multiple tables using unqualified names; same-named tables exist in +-- public to confirm unqualified names resolve to pub_test, not public +CREATE PUBLICATION testpub_schema_except2 + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_nopk, testpub_tbl_s1); +\dRp+ testpub_schema_except2 + Publication testpub_schema_except2 + 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" +Except tables: + "pub_test.testpub_nopk" + "pub_test.testpub_tbl_s1" + +-- fail: EXCEPT table belongs to a different schema +CREATE PUBLICATION testpub_except_wrongschema + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1); +ERROR: table "public.testpub_tbl1" in EXCEPT clause does not belong to schema "pub_test" +LINE 2: FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testp... + ^ +-- fail: cross-schema EXCEPT not allowed; each EXCEPT is bound to its immediate schema +CREATE PUBLICATION testpub_except_crossschema + FOR TABLES IN SCHEMA pub_test, public EXCEPT (TABLE pub_test.testpub_tbl_s1, public.testpub_tbl1); +ERROR: table "pub_test.testpub_tbl_s1" in EXCEPT clause does not belong to schema "public" +LINE 2: ...R TABLES IN SCHEMA pub_test, public EXCEPT (TABLE pub_test.t... + ^ +-- Multiple schemas each with their own EXCEPT clause +CREATE PUBLICATION testpub_schema_except_multi + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1), + public EXCEPT (TABLE testpub_tbl1); +\dRp+ testpub_schema_except_multi + Publication testpub_schema_except_multi + 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" + "public" +Except tables: + "pub_test.testpub_tbl_s1" + "public.testpub_tbl1" + +-- fail: table appears in both the explicit table list and the EXCEPT clause +CREATE PUBLICATION testpub_except_conflict + FOR TABLE pub_test.testpub_tbl_s1, TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); +ERROR: table "pub_test.testpub_tbl_s1" cannot appear in both the table list and the EXCEPT clause +-- fail: nonexistent table in EXCEPT clause +CREATE PUBLICATION testpub_except_norel + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table); +ERROR: relation "pub_test.nonexistent_table" does not exist +-- fail: partition cannot appear in EXCEPT clause; only root tables are allowed +CREATE TABLE pub_test.testpub_parted_s (a int) PARTITION BY LIST (a); +CREATE TABLE pub_test.testpub_part_s PARTITION OF pub_test.testpub_parted_s FOR VALUES IN (1); +CREATE PUBLICATION testpub_except_partition + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_part_s); +ERROR: cannot specify relation "pub_test.testpub_part_s" in the publication EXCEPT clause +DETAIL: This operation is not supported for individual partitions. +-- fail: TABLE keyword is required for the first entry in the EXCEPT clause +CREATE PUBLICATION testpub_except_nokw + FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk); +ERROR: syntax error at or near "testpub_nopk" +LINE 2: FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk); + ^ +-- Cleanup +RESET client_min_messages; +DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2; +DROP TABLE pub_test.testpub_parted_s CASCADE; +DROP TABLE testpub_nopk, testpub_tbl_s1; +DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi; +--------------------------------------------- +-- Tests for publications with SEQUENCES +--------------------------------------------- CREATE SEQUENCE regress_pub_seq0; CREATE SEQUENCE pub_test.regress_pub_seq1; -- FOR ALL SEQUENCES @@ -1522,7 +1624,7 @@ CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk; RESET client_min_messages; -- fail - already added ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1; -ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl" +ERROR: relation "public.testpub_tbl1" is already member of publication "testpub_fortbl" -- fail - already added CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1; ERROR: publication "testpub_fortbl" already exists diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 041e14a4de6..9162d4d15a5 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -123,6 +123,9 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (TABL \d testpub_tbl1 -- fail - first table in the EXCEPT list should use TABLE keyword CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tbl1, testpub_tbl2); +-- fail - EXCEPT is not allowed for FOR TABLE publications +CREATE PUBLICATION testpub_except_err + FOR TABLE testpub_tbl1, testpub_tbl2 EXCEPT (TABLE testpub_tbl3); --------------------------------------------- -- SET ALL TABLES/SEQUENCES @@ -220,7 +223,71 @@ RESET client_min_messages; DROP TABLE testpub_root, testpub_part1, tab_main; DROP PUBLICATION testpub8; ---- Tests for publications with SEQUENCES +--------------------------------------------- +-- EXCEPT tests for TABLES IN SCHEMA +--------------------------------------------- +SET client_min_messages = 'ERROR'; +-- Create tables in pub_test for these tests +CREATE TABLE pub_test.testpub_tbl_s1 (a int primary key, b text); +CREATE TABLE pub_test.testpub_tbl_s2 (x int primary key, y text); +-- Create same-named tables in public to verify unqualified EXCEPT entries +-- are qualified with the named schema, not public +CREATE TABLE testpub_nopk (foo int, bar int); +CREATE TABLE testpub_tbl_s1 (a int primary key, b text); + +-- Basic: exclude one table from a schema publication +CREATE PUBLICATION testpub_schema_except1 + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); +\dRp+ testpub_schema_except1 + +-- Exclude multiple tables using unqualified names; same-named tables exist in +-- public to confirm unqualified names resolve to pub_test, not public +CREATE PUBLICATION testpub_schema_except2 + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_nopk, testpub_tbl_s1); +\dRp+ testpub_schema_except2 + +-- fail: EXCEPT table belongs to a different schema +CREATE PUBLICATION testpub_except_wrongschema + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1); + +-- fail: cross-schema EXCEPT not allowed; each EXCEPT is bound to its immediate schema +CREATE PUBLICATION testpub_except_crossschema + FOR TABLES IN SCHEMA pub_test, public EXCEPT (TABLE pub_test.testpub_tbl_s1, public.testpub_tbl1); + +-- Multiple schemas each with their own EXCEPT clause +CREATE PUBLICATION testpub_schema_except_multi + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1), + public EXCEPT (TABLE testpub_tbl1); +\dRp+ testpub_schema_except_multi + +-- fail: table appears in both the explicit table list and the EXCEPT clause +CREATE PUBLICATION testpub_except_conflict + FOR TABLE pub_test.testpub_tbl_s1, TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); + +-- fail: nonexistent table in EXCEPT clause +CREATE PUBLICATION testpub_except_norel + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table); + +-- fail: partition cannot appear in EXCEPT clause; only root tables are allowed +CREATE TABLE pub_test.testpub_parted_s (a int) PARTITION BY LIST (a); +CREATE TABLE pub_test.testpub_part_s PARTITION OF pub_test.testpub_parted_s FOR VALUES IN (1); +CREATE PUBLICATION testpub_except_partition + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_part_s); + +-- fail: TABLE keyword is required for the first entry in the EXCEPT clause +CREATE PUBLICATION testpub_except_nokw + FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk); + +-- Cleanup +RESET client_min_messages; +DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2; +DROP TABLE pub_test.testpub_parted_s CASCADE; +DROP TABLE testpub_nopk, testpub_tbl_s1; +DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi; + +--------------------------------------------- +-- Tests for publications with SEQUENCES +--------------------------------------------- CREATE SEQUENCE regress_pub_seq0; CREATE SEQUENCE pub_test.regress_pub_seq1; diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl index 8c58d282eee..18c7b2c1fca 100644 --- a/src/test/subscription/t/037_except.pl +++ b/src/test/subscription/t/037_except.pl @@ -24,14 +24,17 @@ my $result; sub test_except_root_partition { - my ($pubviaroot) = @_; + my ($pubviaroot, $pubsql) = @_; + $pubsql //= + "CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT (TABLE root1)"; + $pubsql .= " WITH (publish_via_partition_root = $pubviaroot)"; # If the root partitioned table is in the EXCEPT clause, all its # partitions are excluded from publication, regardless of the # publish_via_partition_root setting. $node_publisher->safe_psql( 'postgres', qq( - CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT (TABLE root1) WITH (publish_via_partition_root = $pubviaroot); + $pubsql; INSERT INTO root1 VALUES (1), (101); )); $node_subscriber->safe_psql('postgres', @@ -223,6 +226,131 @@ $node_subscriber->safe_psql( test_except_root_partition('false'); test_except_root_partition('true'); +# Same validation using TABLES IN SCHEMA instead of FOR ALL TABLES. +my $schema_pub = + "CREATE PUBLICATION tap_pub_part FOR TABLES IN SCHEMA public EXCEPT (TABLE public.root1)"; +test_except_root_partition('false', $schema_pub); +test_except_root_partition('true', $schema_pub); + +# ============================================ +# EXCEPT test cases for TABLES IN SCHEMA +# ============================================ + +# Create a dedicated schema with two tables: one to be published and one to be +# excluded. Also create inherited tables to verify ONLY semantics. +$node_publisher->safe_psql( + 'postgres', qq( + CREATE SCHEMA sch1; + CREATE TABLE sch1.tab_published AS SELECT generate_series(1,5) AS a; + CREATE TABLE sch1.tab_excluded AS SELECT generate_series(1,5) AS a; + CREATE TABLE sch1.parent (a int); + CREATE TABLE sch1.child (b int) INHERITS (sch1.parent); +)); + +$node_subscriber->safe_psql( + 'postgres', qq( + CREATE SCHEMA sch1; + CREATE TABLE sch1.tab_published (a int); + CREATE TABLE sch1.tab_excluded (a int); + CREATE TABLE sch1.parent (a int); + CREATE TABLE sch1.child (b int) INHERITS (sch1.parent); +)); + +# Basic test: initial sync respects EXCEPT. +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.tab_excluded)" +); +$node_subscriber->safe_psql('postgres', + "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub" +); +$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub'); + +$result = + $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM sch1.tab_published"); +is($result, qq(5), + 'TABLES IN SCHEMA EXCEPT: initial sync copies included table'); +$result = + $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM sch1.tab_excluded"); +is($result, qq(0), + 'TABLES IN SCHEMA EXCEPT: initial sync skips excluded table'); + +# DML: only the included table should be replicated. +$node_publisher->safe_psql( + 'postgres', qq( + INSERT INTO sch1.tab_published VALUES (6); + INSERT INTO sch1.tab_excluded VALUES (6); +)); +$node_publisher->wait_for_catchup('sch_sub'); + +$result = + $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM sch1.tab_published"); +is($result, qq(6), + 'TABLES IN SCHEMA EXCEPT: DML on included table is replicated'); +$result = + $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM sch1.tab_excluded"); +is($result, qq(0), + 'TABLES IN SCHEMA EXCEPT: DML on excluded table is not replicated'); + +$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub'); +$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub'); + +# Inherited tables: excluding the parent (without ONLY) also excludes the child. +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.parent)" +); +$node_subscriber->safe_psql('postgres', + "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub" +); +$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub'); + +$node_publisher->safe_psql('postgres', + "INSERT INTO sch1.child VALUES (generate_series(1,5), generate_series(1,5))" +); +$node_publisher->wait_for_catchup('sch_sub'); + +$result = + $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.child"); +is($result, qq(0), + 'TABLES IN SCHEMA EXCEPT: excluding parent (without ONLY) also excludes child' +); + +$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub'); +$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub'); + +# Test that EXCEPT (TABLE ONLY parent) excludes only the parent itself, not its +# child. Truncate child first so rows from the previous test are not copied by +# the initial table sync of the next subscription. +$node_publisher->safe_psql('postgres', 'TRUNCATE sch1.child'); +$node_subscriber->safe_psql('postgres', 'TRUNCATE sch1.child'); +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE ONLY sch1.parent)" +); +$node_subscriber->safe_psql('postgres', + "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub" +); +$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub'); + +$node_publisher->safe_psql('postgres', + "INSERT INTO sch1.child VALUES (generate_series(1,5), generate_series(1,5))" +); +$node_publisher->wait_for_catchup('sch_sub'); + +$result = + $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.child"); +is($result, qq(5), + 'TABLES IN SCHEMA EXCEPT: ONLY parent in EXCEPT does not exclude child'); + +$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub'); +$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub'); + +# Cleanup schema tables before the multi-publication section. +$node_publisher->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE'); +$node_subscriber->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE'); + # ============================================ # Test when a subscription is subscribing to multiple publications # ============================================ @@ -254,6 +382,7 @@ $node_publisher->safe_psql( DROP PUBLICATION tap_pub2; TRUNCATE tab1; )); +$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub'); $node_subscriber->safe_psql('postgres', qq(TRUNCATE tab1)); # OK when a table is excluded by pub1 EXCEPT clause, but it is included by pub2 -- 2.50.1 (Apple Git-155)
From ef242e34926ddd310c35d8e7028b376f493d4771 Mon Sep 17 00:00:00 2001 From: Nisha Moond <[email protected]> Date: Tue, 26 May 2026 15:36:07 +0530 Subject: [PATCH v7 2/3] Add EXCEPT support to ALTER PUBLICATION ADD TABLES IN SCHEMA Extend the EXCEPT clause support to allow tables to be excluded when adding a schema to a publication via ALTER PUBLICATION ... ADD. Syntax: ALTER PUBLICATION pub ADD TABLES IN SCHEMA s EXCEPT (TABLE s.t1); Since pg_dump uses ALTER PUBLICATION ... ADD, support for it is included in this patch. --- doc/src/sgml/ref/alter_publication.sgml | 40 +++++++- src/backend/catalog/pg_publication.c | 19 ++-- src/backend/commands/publicationcmds.c | 108 +++++++++++++++++++++- src/bin/pg_dump/pg_dump.c | 30 +++++- src/bin/pg_dump/t/002_pg_dump.pl | 36 ++++++++ src/bin/psql/tab-complete.in.c | 15 +++ src/test/regress/expected/publication.out | 69 +++++++++++++- src/test/regress/sql/publication.sql | 34 ++++++- src/test/subscription/t/037_except.pl | 32 +++++++ 9 files changed, 368 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml index aa32bb169e9..73f6375a66f 100644 --- a/doc/src/sgml/ref/alter_publication.sgml +++ b/doc/src/sgml/ref/alter_publication.sgml @@ -31,7 +31,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r <phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase> TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ] - TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ] + TABLES IN SCHEMA <replaceable class="parameter">tables_in_schema</replaceable> [, ... ] <phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase> @@ -47,6 +47,10 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r <replaceable class="parameter">table_object</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ] +<phrase>and <replaceable class="parameter">tables_in_schema</replaceable> is:</phrase> + + { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [ EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ] + <phrase>and <replaceable class="parameter">except_table_object</replaceable> is:</phrase> TABLE <replaceable class="parameter">table_object</replaceable> [, ... ] @@ -110,6 +114,14 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r <literal>ADD TABLE</literal>. </para> + <para> + The <literal>EXCEPT</literal> clause can be used with + <literal>ADD TABLES IN SCHEMA</literal> to exclude specific tables from the + publication. Using <literal>DROP TABLES IN SCHEMA</literal> on a publication + will automatically also remove any associated <literal>EXCEPT</literal> + entries. + </para> + <para> The fourth variant of this command listed in the synopsis can change all of the publication properties specified in @@ -198,6 +210,22 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r </listitem> </varlistentry> + <varlistentry> + <term><literal>EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] )</literal></term> + <listitem> + <para> + When used with <literal>ADD TABLES IN SCHEMA</literal>, specifies + tables to be excluded from the publication. Each named + table must belong to the schema specified in the same + <literal>TABLES IN SCHEMA</literal> clause. Table names may be + schema-qualified or unqualified; unqualified names are implicitly + qualified with the schema named in the same clause. See + <xref linkend="sql-createpublication"/> for further details on the + semantics of <literal>EXCEPT</literal>. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term> <listitem> @@ -288,6 +316,16 @@ ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA marketing, sales; </programlisting> </para> + <para> + Add schema <structname>sales</structname> to the publication + <structname>sales_publication</structname>, excluding the + <structname>sales.internal</structname> and + <structname>sales.drafts</structname> tables: +<programlisting> +ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA sales EXCEPT (TABLE internal, drafts); +</programlisting> + </para> + <para> Add tables <structname>users</structname>, <structname>departments</structname> and schema diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index b4c5a317869..c7fa81bfc33 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -648,15 +648,18 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri, * here, as CreatePublication() function invalidates all relations as part * of defining a FOR ALL TABLES publication. * - * For ALTER PUBLICATION, invalidation is needed only when adding an - * EXCEPT table to a publication already marked as ALL TABLES. For - * publications that were originally empty or defined as ALL SEQUENCES and - * are being converted to ALL TABLES, invalidation is skipped here, as - * AlterPublicationAllFlags() function invalidates all relations while - * marking the publication as ALL TABLES publication. + * For ALTER PUBLICATION, invalidation is needed when adding an EXCEPT + * table to either a FOR ALL TABLES publication (pub->alltables is true) + * or a FOR TABLES IN SCHEMA publication (is_schema_publication is true). + * The exception: when a publication is being converted to FOR ALL TABLES + * (pub->alltables is still false at this point), + * AlterPublicationAllFlags() will perform a full invalidation, so we + * skip it here. */ - inval_except_table = (alter_stmt != NULL) && pub->alltables && - (alter_stmt->for_all_tables && pri->except); + inval_except_table = (alter_stmt != NULL) && pri->except && + (pub->alltables + ? alter_stmt->for_all_tables + : is_schema_publication(pubid)); if (!pri->except || inval_except_table) { diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c index 65ae40210db..f23893bbd10 100644 --- a/src/backend/commands/publicationcmds.c +++ b/src/backend/commands/publicationcmds.c @@ -70,6 +70,13 @@ static void PublicationDropTables(Oid pubid, List *rels, bool missing_ok); static void PublicationAddSchemas(Oid pubid, List *schemas, bool if_not_exists, AlterPublicationStmt *stmt); static void PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok); +static void AlterPublicationSchemas(AlterPublicationStmt *stmt, + HeapTuple tup, List *schemaidlist, + List *except_rel_names); +static void AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt, + HeapTuple tup, + List *except_rel_names, + List *schemaidlist); static char defGetGeneratedColsOption(DefElem *def); @@ -1468,7 +1475,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup, */ static void AlterPublicationSchemas(AlterPublicationStmt *stmt, - HeapTuple tup, List *schemaidlist) + HeapTuple tup, List *schemaidlist, + List *except_rel_names) { Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup); @@ -1545,6 +1553,98 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt, */ PublicationAddSchemas(pubform->oid, schemaidlist, true, stmt); } + + /* + * Increment the command counter so that is_schema_publication() in + * GetExcludedPublicationTables() can see the just-inserted schema + * rows when AlterPublicationSchemaExceptTables runs next. + */ + if (stmt->action == AP_AddObjects || stmt->action == AP_SetObjects) + CommandCounterIncrement(); + + AlterPublicationSchemaExceptTables(stmt, tup, except_rel_names, schemaidlist); +} + +/* + * Alter the EXCEPT list of a schema-level publication. + * + * Adds, removes, or replaces except-table entries in pg_publication_rel + * (rows with prexcept = true). These entries suppress publication of the + * named tables that would otherwise be covered by a FOR TABLES IN SCHEMA + * clause. + */ +static void +AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt, + HeapTuple tup, List *except_rel_names, + List *schemaidlist) +{ + Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup); + Oid pubid = pubform->oid; + + /* + * Nothing to do if no EXCEPT entries. + */ + if (!except_rel_names) + return; + + /* + * This function handles EXCEPT entries for schema-level publications + * only. For FOR ALL TABLES publications, EXCEPT entries are already + * processed by AlterPublicationTables(). + */ + if (schemaidlist == NIL && !is_schema_publication(pubid)) + return; + + /* + * EXCEPT is not meaningful with DROP: dropping a schema from a + * publication already removes all its except entries via cascade, and + * there is no sensible interpretation of "drop only the except entry but + * keep the schema". + */ + if (stmt->action == AP_DropObjects) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("EXCEPT clause is not supported with DROP in ALTER PUBLICATION"))); + + /* + * XXX EXCEPT with SET is not currently implemented. Workaround: DROP and + * re-ADD the schema with the desired EXCEPT list. + */ + if (stmt->action == AP_SetObjects) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("EXCEPT clause is not supported with SET in ALTER PUBLICATION"), + errhint("Drop and re-add the schema with the desired EXCEPT list."))); + + if (stmt->action == AP_AddObjects) + { + List *rels; + List *explicitrelids; + + rels = OpenTableList(except_rel_names); + + explicitrelids = GetIncludedPublicationRelations(pubid, + PUBLICATION_PART_ROOT); + + /* + * Validate that each excluded table is not also in the explicit table + * list (which would be contradictory). + */ + foreach_ptr(PublicationRelInfo, pri, rels) + { + Oid relid = RelationGetRelid(pri->relation); + + if (list_member_oid(explicitrelids, relid)) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("table \"%s\" cannot appear in both the table list and the EXCEPT clause", + RelationGetQualifiedRelationName(pri->relation))); + } + + PublicationAddTables(pubid, rels, false, stmt); + + CloseTableList(rels); + } } /* @@ -1754,10 +1854,12 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt) errmsg("publication \"%s\" does not exist", stmt->pubname)); - relations = list_concat(relations, except_rel_names); + if (stmt->for_all_tables) + relations = list_concat(relations, except_rel_names); + AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext, schemaidlist != NIL); - AlterPublicationSchemas(stmt, tup, schemaidlist); + AlterPublicationSchemas(stmt, tup, schemaidlist, except_rel_names); AlterPublicationAllFlags(stmt, rel, tup); } diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index d56dcc701ce..e62d74c8ca0 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -5019,6 +5019,7 @@ dumpPublicationNamespace(Archive *fout, const PublicationSchemaInfo *pubsinfo) PublicationInfo *pubinfo = pubsinfo->publication; PQExpBuffer query; char *tag; + bool has_except = false; /* Do nothing if not dumping schema */ if (!dopt->dumpSchema) @@ -5029,7 +5030,34 @@ dumpPublicationNamespace(Archive *fout, const PublicationSchemaInfo *pubsinfo) query = createPQExpBuffer(); appendPQExpBuffer(query, "ALTER PUBLICATION %s ", fmtId(pubinfo->dobj.name)); - appendPQExpBuffer(query, "ADD TABLES IN SCHEMA %s;\n", fmtId(schemainfo->dobj.name)); + appendPQExpBuffer(query, "ADD TABLES IN SCHEMA %s", fmtId(schemainfo->dobj.name)); + + /* + * Append EXCEPT clause for any tables that belong to this schema + * and are excluded from the publication. + */ + for (SimplePtrListCell *cell = pubinfo->except_tables.head; cell; cell = cell->next) + { + TableInfo *tbinfo = (TableInfo *) cell->ptr; + + if (strcmp(tbinfo->dobj.namespace->dobj.name, schemainfo->dobj.name) == 0) + { + if (!has_except) + { + appendPQExpBufferStr(query, " EXCEPT ("); + has_except = true; + } + else + appendPQExpBufferStr(query, ", "); + + appendPQExpBuffer(query, "TABLE ONLY %s", fmtId(tbinfo->dobj.name)); + } + } + + if (has_except) + appendPQExpBufferStr(query, ")"); + + appendPQExpBufferStr(query, ";\n"); /* * There is no point in creating drop query as the drop is done by schema diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 3ee9fda50e4..de554436205 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -3242,6 +3242,42 @@ my %tests = ( like => { %full_runs, section_post_data => 1, }, }, + 'CREATE PUBLICATION pub11' => { + create_order => 50, + create_sql => + 'CREATE PUBLICATION pub11 FOR TABLES IN SCHEMA dump_test EXCEPT (TABLE test_table);', + regexp => qr/^ + \QCREATE PUBLICATION pub11 WITH (publish = 'insert, update, delete, truncate');\E + /xm, + like => { %full_runs, section_post_data => 1, }, + }, + + 'ALTER PUBLICATION pub11 ADD TABLES IN SCHEMA dump_test EXCEPT (TABLE test_table)' + => { + regexp => qr/^ + \QALTER PUBLICATION pub11 ADD TABLES IN SCHEMA dump_test EXCEPT (TABLE ONLY test_table);\E + /xm, + like => { %full_runs, section_post_data => 1, }, + }, + + 'CREATE PUBLICATION pub12' => { + create_order => 50, + create_sql => + 'CREATE PUBLICATION pub12 FOR TABLES IN SCHEMA dump_test EXCEPT (TABLE test_table, dump_test.test_second_table);', + regexp => qr/^ + \QCREATE PUBLICATION pub12 WITH (publish = 'insert, update, delete, truncate');\E + /xm, + like => { %full_runs, section_post_data => 1, }, + }, + + 'ALTER PUBLICATION pub12 ADD TABLES IN SCHEMA dump_test EXCEPT (TABLE test_table, dump_test.test_second_table)' + => { + regexp => qr/^ + \QALTER PUBLICATION pub12 ADD TABLES IN SCHEMA dump_test EXCEPT (TABLE ONLY test_table, TABLE ONLY test_second_table);\E + /xm, + like => { %full_runs, section_post_data => 1, }, + }, + 'CREATE SUBSCRIPTION sub1' => { create_order => 50, create_sql => 'CREATE SUBSCRIPTION sub1 diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index fe11dc619ac..8db3e129928 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2364,6 +2364,21 @@ match_previous_words(int pattern_id, COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_schemas " AND nspname NOT LIKE E'pg\\\\_%%'", "CURRENT_SCHEMA"); + /* After a single schema name in ADD context, offer EXCEPT ( TABLE */ + else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny) && + !ends_with(prev_wd, ',')) + COMPLETE_WITH("EXCEPT ( TABLE"); + else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT")) + COMPLETE_WITH("( TABLE"); + else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(")) + COMPLETE_WITH("TABLE"); + else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE")) + { + set_completion_reference(prev4_wd); + COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_schema); + } + else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE", MatchAnyN) && !ends_with(prev_wd, ',')) + COMPLETE_WITH(")"); /* ALTER PUBLICATION <name> SET ( */ else if (Matches("ALTER", "PUBLICATION", MatchAny, MatchAnyN, "SET", "(")) COMPLETE_WITH("publish", "publish_generated_columns", "publish_via_partition_root"); diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index 161db458f49..c912cfcea00 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -564,12 +564,79 @@ CREATE PUBLICATION testpub_except_nokw ERROR: syntax error at or near "testpub_nopk" LINE 2: FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk); ^ +--------------------------------------------- +-- EXCEPT tests for ALTER PUBLICATION +--------------------------------------------- +CREATE PUBLICATION testpub_alter_except; +-- ADD: schema-qualified name in EXCEPT +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); +\dRp+ testpub_alter_except + Publication testpub_alter_except + 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" +Except tables: + "pub_test.testpub_tbl_s1" + +-- DROP TABLES IN SCHEMA also removes associated EXCEPT entries +ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; +\dRp+ testpub_alter_except + Publication testpub_alter_except + 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 | +Except tables: + "pub_test.testpub_tbl_s1" + +-- ADD: unqualified name is implicitly qualified with the schema, not public +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s2); +\dRp+ testpub_alter_except + Publication testpub_alter_except + 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" +Except tables: + "pub_test.testpub_tbl_s1" + "pub_test.testpub_tbl_s2" + +-- ADD: multiple excepted tables using unqualified names +ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s1, testpub_tbl_s2); +ERROR: relation "pub_test.testpub_tbl_s1" cannot be added because it is excluded from publication "testpub_alter_except" +\dRp+ testpub_alter_except + Publication testpub_alter_except + 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 | +Except tables: + "pub_test.testpub_tbl_s1" + "pub_test.testpub_tbl_s2" + +-- fail: non-existing table in EXCEPT clause +ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; +ERROR: tables from schema "pub_test" are not part of the publication +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table); +ERROR: relation "pub_test.nonexistent_table" does not exist +-- fail: EXCEPT table belongs to a different schema +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1); +ERROR: table "public.testpub_tbl1" in EXCEPT clause does not belong to schema "pub_test" +LINE 1: ...xcept ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE public.tes... + ^ +-- fail: TABLE keyword is required for the first entry in EXCEPT clause +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk); +ERROR: syntax error at or near "testpub_nopk" +LINE 1: ...lter_except ADD TABLES IN SCHEMA pub_test EXCEPT (testpub_no... + ^ -- Cleanup RESET client_min_messages; DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2; DROP TABLE pub_test.testpub_parted_s CASCADE; DROP TABLE testpub_nopk, testpub_tbl_s1; -DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi; +DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi, testpub_alter_except; --------------------------------------------- -- Tests for publications with SEQUENCES --------------------------------------------- diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 9162d4d15a5..9f816effa70 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -278,12 +278,44 @@ CREATE PUBLICATION testpub_except_partition CREATE PUBLICATION testpub_except_nokw FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk); +--------------------------------------------- +-- EXCEPT tests for ALTER PUBLICATION +--------------------------------------------- +CREATE PUBLICATION testpub_alter_except; + +-- ADD: schema-qualified name in EXCEPT +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); +\dRp+ testpub_alter_except + +-- DROP TABLES IN SCHEMA also removes associated EXCEPT entries +ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; +\dRp+ testpub_alter_except + +-- ADD: unqualified name is implicitly qualified with the schema, not public +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s2); +\dRp+ testpub_alter_except + +-- ADD: multiple excepted tables using unqualified names +ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s1, testpub_tbl_s2); +\dRp+ testpub_alter_except + +-- fail: non-existing table in EXCEPT clause +ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table); + +-- fail: EXCEPT table belongs to a different schema +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1); + +-- fail: TABLE keyword is required for the first entry in EXCEPT clause +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk); + -- Cleanup RESET client_min_messages; DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2; DROP TABLE pub_test.testpub_parted_s CASCADE; DROP TABLE testpub_nopk, testpub_tbl_s1; -DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi; +DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi, testpub_alter_except; --------------------------------------------- -- Tests for publications with SEQUENCES diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl index 18c7b2c1fca..0ba6d6f8bb2 100644 --- a/src/test/subscription/t/037_except.pl +++ b/src/test/subscription/t/037_except.pl @@ -347,6 +347,38 @@ is($result, qq(5), $node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub'); $node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub'); +# ============================================ +# ALTER PUBLICATION EXCEPT for TABLES IN SCHEMA +# ============================================ + +# Truncate subscriber tables to remove data accumulated from previous tests. +$node_subscriber->safe_psql('postgres', + 'TRUNCATE sch1.tab_published, sch1.tab_excluded, sch1.parent, sch1.child'); + +# ADD: add a schema with an excepted table; verify the except entry takes effect. +$node_publisher->safe_psql('postgres', "CREATE PUBLICATION sch_pub"); +$node_publisher->safe_psql('postgres', + "ALTER PUBLICATION sch_pub ADD TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.tab_excluded)" +); +$node_subscriber->safe_psql('postgres', + "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub" +); +$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub'); + +$result = + $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM sch1.tab_published"); +is($result, qq(6), + 'ALTER ... ADD TABLES IN SCHEMA EXCEPT: included table synced'); +$result = + $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM sch1.tab_excluded"); +is($result, qq(0), + 'ALTER ... ADD TABLES IN SCHEMA EXCEPT: excluded table not synced'); + +$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub'); +$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub'); + # Cleanup schema tables before the multi-publication section. $node_publisher->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE'); $node_subscriber->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE'); -- 2.50.1 (Apple Git-155)
From 85d685fffef948dd6917273b8001cdc6da58e232 Mon Sep 17 00:00:00 2001 From: Nisha Moond <[email protected]> Date: Thu, 28 May 2026 12:14:31 +0530 Subject: [PATCH v7 3/3] Add EXCEPT support to ALTER PUBLICATION SET TABLES IN SCHEMA Extend AlterPublicationExceptTables() with the AP_SetObjects case, which redefines the publication and replaces the entire EXCEPT list. Syntax: ALTER PUBLICATION pub SET TABLES IN SCHEMA s EXCEPT (TABLE t1); This patch also cleans up EXCEPT entries when a schema is dropped from the publication. --- doc/src/sgml/ref/alter_publication.sgml | 27 +++- src/backend/commands/publicationcmds.c | 131 ++++++++++++++++++-- src/backend/replication/pgoutput/pgoutput.c | 10 +- src/bin/psql/tab-complete.in.c | 15 +++ src/test/regress/expected/publication.out | 82 +++++++++++- src/test/regress/sql/publication.sql | 34 +++++ src/test/subscription/t/037_except.pl | 85 +++++++++++++ 7 files changed, 355 insertions(+), 29 deletions(-) diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml index 73f6375a66f..80b038e4b2e 100644 --- a/doc/src/sgml/ref/alter_publication.sgml +++ b/doc/src/sgml/ref/alter_publication.sgml @@ -97,7 +97,11 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r used with a publication defined with <literal>FOR TABLE</literal> or <literal>FOR TABLES IN SCHEMA</literal>, replaces the list of tables/schemas in the publication with the specified list; the existing tables or schemas - that were present in the publication will be removed. + that were present in the publication will be removed. When + <literal>SET TABLES IN SCHEMA</literal> is used with an + <literal>EXCEPT</literal> clause, the excluded tables for each schema are + replaced with the specified list; if <literal>EXCEPT</literal> is omitted + for a schema, any existing exclusions for that schema are cleared. </para> <para> @@ -116,10 +120,8 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r <para> The <literal>EXCEPT</literal> clause can be used with - <literal>ADD TABLES IN SCHEMA</literal> to exclude specific tables from the - publication. Using <literal>DROP TABLES IN SCHEMA</literal> on a publication - will automatically also remove any associated <literal>EXCEPT</literal> - entries. + <literal>ADD TABLES IN SCHEMA</literal> to exclude specific tables from a + schema-level publication. </para> <para> @@ -214,7 +216,8 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r <term><literal>EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] )</literal></term> <listitem> <para> - When used with <literal>ADD TABLES IN SCHEMA</literal>, specifies + When used with <literal>ADD TABLES IN SCHEMA</literal> + or <literal>SET TABLES IN SCHEMA</literal>, specifies tables to be excluded from the publication. Each named table must belong to the schema specified in the same <literal>TABLES IN SCHEMA</literal> clause. Table names may be @@ -326,6 +329,18 @@ ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA sales EXCEPT (TABLE int </programlisting> </para> + <para> + Replace the schema list of <structname>sales_publication</structname> with + <structname>sales</structname>, excluding only + <structname>sales.drafts</structname>. Other than + <structname>sales.drafts</structname>, any previously excluded tables for schema + <structname>sales</structname> are no longer excluded. Any schemas previously in + <structname>sales_publication</structname> are removed: +<programlisting> +ALTER PUBLICATION sales_publication SET TABLES IN SCHEMA sales EXCEPT (TABLE drafts); +</programlisting> + </para> + <para> Add tables <structname>users</structname>, <structname>departments</structname> and schema diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c index f23893bbd10..58e0c938f83 100644 --- a/src/backend/commands/publicationcmds.c +++ b/src/backend/commands/publicationcmds.c @@ -1582,9 +1582,11 @@ AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt, Oid pubid = pubform->oid; /* - * Nothing to do if no EXCEPT entries. + * Nothing to do if there are no EXCEPT entries, unless handling the SET + * command, because if the user has removed all exceptions we need to + * drop any existing ones. */ - if (!except_rel_names) + if (!except_rel_names && stmt->action != AP_SetObjects) return; /* @@ -1597,7 +1599,7 @@ AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt, /* * EXCEPT is not meaningful with DROP: dropping a schema from a - * publication already removes all its except entries via cascade, and + * publication already removes all its EXCEPT entries via cascade, and * there is no sensible interpretation of "drop only the except entry but * keep the schema". */ @@ -1606,16 +1608,6 @@ AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("EXCEPT clause is not supported with DROP in ALTER PUBLICATION"))); - /* - * XXX EXCEPT with SET is not currently implemented. Workaround: DROP and - * re-ADD the schema with the desired EXCEPT list. - */ - if (stmt->action == AP_SetObjects) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("EXCEPT clause is not supported with SET in ALTER PUBLICATION"), - errhint("Drop and re-add the schema with the desired EXCEPT list."))); - if (stmt->action == AP_AddObjects) { List *rels; @@ -1643,6 +1635,86 @@ AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt, PublicationAddTables(pubid, rels, false, stmt); + CloseTableList(rels); + } + else /* AP_SetObjects */ + { + List *oldexceptrelids = NIL; + List *newexceptrelids = NIL; + List *delrelids = NIL; + List *rels; + List *explicitrelids; + + rels = OpenTableList(except_rel_names); + + /* Collect OIDs of the desired new EXCEPT list. */ + foreach_ptr(PublicationRelInfo, pri, rels) + { + newexceptrelids = lappend_oid(newexceptrelids, + RelationGetRelid(pri->relation)); + } + + explicitrelids = GetIncludedPublicationRelations(pubid, + PUBLICATION_PART_ROOT); + + /* + * Validate that each excluded table is not also in the explicit table + * list (which would be contradictory). + */ + foreach_ptr(PublicationRelInfo, pri, rels) + { + Oid relid = RelationGetRelid(pri->relation); + + if (list_member_oid(explicitrelids, relid)) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("table \"%s\" cannot appear in both the table list and the EXCEPT clause", + RelationGetQualifiedRelationName(pri->relation))); + } + + /* + * Get the current set of EXCEPT entries. Only FOR ALL TABLES and + * schema-level publications can have EXCEPT entries; for any other + * publication type oldexceptrelids stays NIL. + * + * Note: we check is_schema_publication() against the current catalog + * state (before AlterPublicationSchemas has run), so if the caller is + * doing SET TABLE t1 to convert a schema publication into a plain + * table publication, is_schema_publication() still returns true here. + * That is intentional: it lets us discover and clean up any stale + * EXCEPT entries that belong to the old schema definition. + */ + if (GetPublication(pubid)->alltables || is_schema_publication(pubid)) + oldexceptrelids = GetExcludedPublicationTables(pubid, + PUBLICATION_PART_ROOT); + + /* Build a list of old EXCEPT entries not present in the new list. */ + foreach_oid(oldrelid, oldexceptrelids) + { + if (!list_member_oid(newexceptrelids, oldrelid)) + delrelids = lappend_oid(delrelids, oldrelid); + } + + /* Drop old EXCEPT entries not present in the new list. */ + foreach_oid(relid, delrelids) + { + Oid proid; + ObjectAddress obj; + + proid = GetSysCacheOid2(PUBLICATIONRELMAP, + Anum_pg_publication_rel_oid, + ObjectIdGetDatum(relid), + ObjectIdGetDatum(pubid)); + if (!OidIsValid(proid)) + continue; /* already gone */ + + ObjectAddressSet(obj, PublicationRelRelationId, proid); + performDeletion(&obj, DROP_CASCADE, 0); + } + + /* Add new EXCEPT entries, skipping any already present. */ + PublicationAddTables(pubid, rels, true, stmt); + CloseTableList(rels); } } @@ -2292,6 +2364,7 @@ PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok) foreach(lc, schemas) { Oid schemaid = lfirst_oid(lc); + List *except_relids; psid = GetSysCacheOid2(PUBLICATIONNAMESPACEMAP, Anum_pg_publication_namespace_oid, @@ -2308,8 +2381,40 @@ PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok) get_namespace_name(schemaid)))); } + /* + * Collect EXCEPT entries for tables belonging to this schema before + * removing the schema entry. + */ + except_relids = GetExcludedPublicationTables(pubid, PUBLICATION_PART_ROOT); + ObjectAddressSet(obj, PublicationNamespaceRelationId, psid); performDeletion(&obj, DROP_CASCADE, 0); + + /* + * Drop any prexcept rows for tables belonging to this schema. These + * rows have no pg_depend entry pointing at the + * pg_publication_namespace row, so they are not cascaded by the + * performDeletion() call above and must be cleaned up explicitly. + */ + foreach_oid(relid, except_relids) + { + Oid proid; + + if (get_rel_namespace(relid) != schemaid) + continue; + + proid = GetSysCacheOid2(PUBLICATIONRELMAP, + Anum_pg_publication_rel_oid, + ObjectIdGetDatum(relid), + ObjectIdGetDatum(pubid)); + if (!OidIsValid(proid)) + continue; /* already gone */ + + ObjectAddressSet(obj, PublicationRelRelationId, proid); + performDeletion(&obj, DROP_CASCADE, 0); + } + + list_free(except_relids); } } diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c index c5f17bf2338..785a045ead2 100644 --- a/src/backend/replication/pgoutput/pgoutput.c +++ b/src/backend/replication/pgoutput/pgoutput.c @@ -2229,7 +2229,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) */ if (pub->alltables) { - List *exceptpubids = NIL; + List *except_pubids = NIL; if (am_partition) { @@ -2252,7 +2252,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) * clause. Therefore, for a partition, exclusion must be * evaluated at the top-most ancestor. */ - exceptpubids = GetRelationExcludedPublications(last_ancestor_relid); + except_pubids = GetRelationExcludedPublications(last_ancestor_relid); } else { @@ -2260,13 +2260,13 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) * For a regular table or a root partitioned table, check * exclusion on table itself. */ - exceptpubids = GetRelationExcludedPublications(pub_relid); + except_pubids = GetRelationExcludedPublications(pub_relid); } - if (!list_member_oid(exceptpubids, pub->oid)) + if (!list_member_oid(except_pubids, pub->oid)) publish = true; - list_free(exceptpubids); + list_free(except_pubids); if (!publish) continue; diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 8db3e129928..11c87b1b006 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2379,6 +2379,21 @@ match_previous_words(int pattern_id, } else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE", MatchAnyN) && !ends_with(prev_wd, ',')) COMPLETE_WITH(")"); + /* After a single schema name in SET context, offer EXCEPT ( TABLE */ + else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "TABLES", "IN", "SCHEMA", MatchAny) && + !ends_with(prev_wd, ',')) + COMPLETE_WITH("EXCEPT ( TABLE"); + else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT")) + COMPLETE_WITH("( TABLE"); + else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(")) + COMPLETE_WITH("TABLE"); + else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE")) + { + set_completion_reference(prev4_wd); + COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_schema); + } + else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE", MatchAnyN) && !ends_with(prev_wd, ',')) + COMPLETE_WITH(")"); /* ALTER PUBLICATION <name> SET ( */ else if (Matches("ALTER", "PUBLICATION", MatchAny, MatchAnyN, "SET", "(")) COMPLETE_WITH("publish", "publish_generated_columns", "publish_via_partition_root"); diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index c912cfcea00..ff1cb4bed1e 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -587,8 +587,7 @@ ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; 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 | -Except tables: - "pub_test.testpub_tbl_s1" +(1 row) -- ADD: unqualified name is implicitly qualified with the schema, not public ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s2); @@ -600,25 +599,98 @@ ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TAB Tables from schemas: "pub_test" Except tables: - "pub_test.testpub_tbl_s1" "pub_test.testpub_tbl_s2" -- ADD: multiple excepted tables using unqualified names ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s1, testpub_tbl_s2); -ERROR: relation "pub_test.testpub_tbl_s1" cannot be added because it is excluded from publication "testpub_alter_except" \dRp+ testpub_alter_except Publication testpub_alter_except 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" Except tables: "pub_test.testpub_tbl_s1" "pub_test.testpub_tbl_s2" +-- SET: replace the except list (keep same schema, different except table) +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2); +\dRp+ testpub_alter_except + Publication testpub_alter_except + 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" +Except tables: + "pub_test.testpub_tbl_s2" + +-- fail: table in EXCEPT clause also appears in the explicit table list +ALTER PUBLICATION testpub_alter_except SET TABLE pub_test.testpub_tbl_s1, TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); +ERROR: table "pub_test.testpub_tbl_s1" cannot appear in both the table list and the EXCEPT clause +-- error: except table's schema (public) not in the publication's schema list (pub_test) +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1); +ERROR: table "public.testpub_tbl1" in EXCEPT clause does not belong to schema "pub_test" +LINE 1: ...xcept SET TABLES IN SCHEMA pub_test EXCEPT (TABLE public.tes... + ^ +-- error: EXCEPT is not allowed with DROP +ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2); +ERROR: EXCEPT clause is not supported with DROP in ALTER PUBLICATION +-- SET: unqualified name in EXCEPT is implicitly qualified with the schema +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s1); +\dRp+ testpub_alter_except + Publication testpub_alter_except + 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" +Except tables: + "pub_test.testpub_tbl_s1" + +-- SET without EXCEPT clears the existing except list +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test; +\dRp+ testpub_alter_except + Publication testpub_alter_except + 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 to a different schema removes old schema's EXCEPT entries +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s1); +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA public; +\dRp+ testpub_alter_except + Publication testpub_alter_except + 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" + +-- fail: nonexistent table in EXCEPT clause (SET path) +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table); +ERROR: relation "pub_test.nonexistent_table" does not exist +-- SET: multiple schemas each with their own EXCEPT clause +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1), + public EXCEPT (TABLE testpub_tbl1); +\dRp+ testpub_alter_except + Publication testpub_alter_except + 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" + "public" +Except tables: + "pub_test.testpub_tbl_s1" + "public.testpub_tbl1" + -- fail: non-existing table in EXCEPT clause ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; -ERROR: tables from schema "pub_test" are not part of the publication ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table); ERROR: relation "pub_test.nonexistent_table" does not exist -- fail: EXCEPT table belongs to a different schema diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 9f816effa70..d008114e05b 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -300,6 +300,40 @@ ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s1, testpub_tbl_s2); \dRp+ testpub_alter_except +-- SET: replace the except list (keep same schema, different except table) +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2); +\dRp+ testpub_alter_except + +-- fail: table in EXCEPT clause also appears in the explicit table list +ALTER PUBLICATION testpub_alter_except SET TABLE pub_test.testpub_tbl_s1, TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); + +-- error: except table's schema (public) not in the publication's schema list (pub_test) +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1); + +-- error: EXCEPT is not allowed with DROP +ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2); + +-- SET: unqualified name in EXCEPT is implicitly qualified with the schema +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s1); +\dRp+ testpub_alter_except + +-- SET without EXCEPT clears the existing except list +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test; +\dRp+ testpub_alter_except + +-- SET to a different schema removes old schema's EXCEPT entries +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_tbl_s1); +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA public; +\dRp+ testpub_alter_except + +-- fail: nonexistent table in EXCEPT clause (SET path) +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table); + +-- SET: multiple schemas each with their own EXCEPT clause +ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1), + public EXCEPT (TABLE testpub_tbl1); +\dRp+ testpub_alter_except + -- fail: non-existing table in EXCEPT clause ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table); diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl index 0ba6d6f8bb2..1308b6e43ed 100644 --- a/src/test/subscription/t/037_except.pl +++ b/src/test/subscription/t/037_except.pl @@ -376,6 +376,61 @@ $result = is($result, qq(0), 'ALTER ... ADD TABLES IN SCHEMA EXCEPT: excluded table not synced'); +# SET: replace the except list; tab_excluded is now included and tab_published is excluded. +$node_publisher->safe_psql('postgres', + "ALTER PUBLICATION sch_pub SET TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.tab_published)" +); +$node_subscriber->safe_psql('postgres', + "ALTER SUBSCRIPTION sch_sub REFRESH PUBLICATION"); +$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub'); + +$node_publisher->safe_psql( + 'postgres', qq( + INSERT INTO sch1.tab_published VALUES (7); + INSERT INTO sch1.tab_excluded VALUES (7); +)); +$node_publisher->wait_for_catchup('sch_sub'); + +$result = + $node_subscriber->safe_psql('postgres', + "SELECT a FROM sch1.tab_excluded WHERE a = 7"); +is($result, qq(7), + 'ALTER ... SET TABLES IN SCHEMA EXCEPT: newly included table is replicated' +); +$result = + $node_subscriber->safe_psql('postgres', + "SELECT a FROM sch1.tab_published WHERE a = 7"); +is($result, qq(), + 'ALTER ... SET TABLES IN SCHEMA EXCEPT: now-excluded table is not replicated' +); + +# SET without EXCEPT: clears the except list; both tables are now published. +$node_publisher->safe_psql('postgres', + "ALTER PUBLICATION sch_pub SET TABLES IN SCHEMA sch1"); +$node_subscriber->safe_psql('postgres', + "ALTER SUBSCRIPTION sch_sub REFRESH PUBLICATION"); +$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub'); + +$node_publisher->safe_psql( + 'postgres', qq( + INSERT INTO sch1.tab_published VALUES (8); + INSERT INTO sch1.tab_excluded VALUES (8); +)); +$node_publisher->wait_for_catchup('sch_sub'); + +$result = + $node_subscriber->safe_psql('postgres', + "SELECT a FROM sch1.tab_published WHERE a = 8"); +is($result, qq(8), + 'ALTER ... SET TABLES IN SCHEMA (no EXCEPT): tab_published replicated after except list cleared' +); +$result = + $node_subscriber->safe_psql('postgres', + "SELECT a FROM sch1.tab_excluded WHERE a = 8"); +is($result, qq(8), + 'ALTER ... SET TABLES IN SCHEMA (no EXCEPT): tab_excluded replicated after except list cleared' +); + $node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub'); $node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub'); @@ -443,6 +498,36 @@ $node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub'); $node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub1'); $node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2'); +# OK when a table is excluded by a TABLES IN SCHEMA EXCEPT publication, +# but is included by another publication. +$node_publisher->safe_psql('postgres', 'TRUNCATE tab1'); +$node_subscriber->safe_psql('postgres', 'TRUNCATE tab1'); + +$node_publisher->safe_psql( + 'postgres', qq( + CREATE PUBLICATION tap_pub1 FOR TABLES IN SCHEMA public EXCEPT (TABLE public.tab1); + CREATE PUBLICATION tap_pub2 FOR TABLE tab1; + INSERT INTO tab1 VALUES(1); +)); +$node_subscriber->psql('postgres', + "CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub1, tap_pub2" +); +$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub'); + +$node_publisher->safe_psql('postgres', qq(INSERT INTO tab1 VALUES(2))); +$node_publisher->wait_for_catchup('tap_sub'); + +$result = + $node_subscriber->safe_psql('postgres', "SELECT * FROM tab1 ORDER BY a"); +is( $result, qq(1 +2), + "TABLES IN SCHEMA EXCEPT: table excluded in schema pub but included by another pub is replicated" +); + +$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub'); +$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub1'); +$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2'); + $node_publisher->stop('fast'); done_testing(); -- 2.50.1 (Apple Git-155)
