public inbox for [email protected]help / color / mirror / Atom feed
Re: jsonapi: scary new warnings with LTO enabled 5+ messages / 3 participants [nested] [flat]
* Re: jsonapi: scary new warnings with LTO enabled @ 2025-04-22 10:10 Daniel Gustafsson <[email protected]> 2025-04-23 00:01 ` Re: jsonapi: scary new warnings with LTO enabled Jacob Champion <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Daniel Gustafsson @ 2025-04-22 10:10 UTC (permalink / raw) To: Jacob Champion <[email protected]>; +Cc: Tom Lane <[email protected]>; PostgreSQL Developers <[email protected]> > On 21 Apr 2025, at 20:58, Jacob Champion <[email protected]> wrote: > Personally, I'm fine with can't-fail APIs, as long as they're > documented as such. (I think the deferred error API was probably > chosen so that src/common JSON clients could be written without a lot > of pain?) My preference is that no operation can silently work on a failed object, but it's not a hill (even more so given where we are in the cycle). The attached v3 allocates via the JSON api, no specific error handling should be required as it's already handled today. -- Daniel Gustafsson Attachments: [application/octet-stream] v3-0001-Allocate-JsonLexContexts-on-the-heap-to-avoid-war.patch (5.3K, ../../[email protected]/2-v3-0001-Allocate-JsonLexContexts-on-the-heap-to-avoid-war.patch) download | inline diff: From fe421d657aaa361dfb796e28c96975bed97f79f5 Mon Sep 17 00:00:00 2001 From: Daniel Gustafsson <[email protected]> Date: Thu, 17 Apr 2025 23:18:58 +0200 Subject: [PATCH v3] Allocate JsonLexContexts on the heap to avoid warnings The stack allocated JsonLexContexts, in combination with codepaths using goto, were causing warnings when compiling with LTO enabled as the optimizer is unable to figure out that is safe. Rather than contort the code with workarounds for this simply heap allocate the structs instead as these are not in any performance critical paths. Reported-by: Tom Lane <[email protected]> Reviewed-by: Jacob Champion <[email protected]> Reviewed-by: Tom Lane <[email protected]> Discussion: https://postgr.es/m/[email protected] --- src/interfaces/libpq/fe-auth-oauth.c | 12 +++++----- .../test_json_parser_incremental.c | 23 +++++++++++-------- 2 files changed, 20 insertions(+), 15 deletions(-) diff --git a/src/interfaces/libpq/fe-auth-oauth.c b/src/interfaces/libpq/fe-auth-oauth.c index cf1a25e2ccc..52d2b286625 100644 --- a/src/interfaces/libpq/fe-auth-oauth.c +++ b/src/interfaces/libpq/fe-auth-oauth.c @@ -476,7 +476,7 @@ issuer_from_well_known_uri(PGconn *conn, const char *wkuri) static bool handle_oauth_sasl_error(PGconn *conn, const char *msg, int msglen) { - JsonLexContext lex = {0}; + JsonLexContext *lex; JsonSemAction sem = {0}; JsonParseErrorType err; struct json_ctx ctx = {0}; @@ -504,8 +504,8 @@ handle_oauth_sasl_error(PGconn *conn, const char *msg, int msglen) return false; } - makeJsonLexContextCstringLen(&lex, msg, msglen, PG_UTF8, true); - setJsonLexContextOwnsTokens(&lex, true); /* must not leak on error */ + lex = makeJsonLexContextCstringLen(NULL, msg, msglen, PG_UTF8, true); + setJsonLexContextOwnsTokens(lex, true); /* must not leak on error */ initPQExpBuffer(&ctx.errbuf); sem.semstate = &ctx; @@ -516,7 +516,7 @@ handle_oauth_sasl_error(PGconn *conn, const char *msg, int msglen) sem.array_start = oauth_json_array_start; sem.scalar = oauth_json_scalar; - err = pg_parse_json(&lex, &sem); + err = pg_parse_json(lex, &sem); if (err == JSON_SEM_ACTION_FAILED) { @@ -535,7 +535,7 @@ handle_oauth_sasl_error(PGconn *conn, const char *msg, int msglen) } } else if (err != JSON_SUCCESS) - errmsg = json_errdetail(err, &lex); + errmsg = json_errdetail(err, lex); if (errmsg) libpq_append_conn_error(conn, @@ -544,7 +544,7 @@ handle_oauth_sasl_error(PGconn *conn, const char *msg, int msglen) /* Don't need the error buffer or the JSON lexer anymore. */ termPQExpBuffer(&ctx.errbuf); - freeJsonLexContext(&lex); + freeJsonLexContext(lex); if (errmsg) goto cleanup; diff --git a/src/test/modules/test_json_parser/test_json_parser_incremental.c b/src/test/modules/test_json_parser/test_json_parser_incremental.c index a529ee47e9b..d1e3e4ab4ea 100644 --- a/src/test/modules/test_json_parser/test_json_parser_incremental.c +++ b/src/test/modules/test_json_parser/test_json_parser_incremental.c @@ -84,7 +84,7 @@ main(int argc, char **argv) char buff[BUFSIZE]; FILE *json_file; JsonParseErrorType result; - JsonLexContext lex; + JsonLexContext *lex; StringInfoData json; int n_read; size_t chunk_size = DEFAULT_CHUNK_SIZE; @@ -98,6 +98,10 @@ main(int argc, char **argv) pg_logging_init(argv[0]); + lex = calloc(1, sizeof(JsonLexContext)); + if (!lex) + pg_fatal("out of memory"); + while ((c = getopt(argc, argv, "c:os")) != -1) { switch (c) @@ -113,7 +117,7 @@ main(int argc, char **argv) case 's': /* do semantic processing */ testsem = &sem; sem.semstate = palloc(sizeof(struct DoState)); - ((struct DoState *) sem.semstate)->lex = &lex; + ((struct DoState *) sem.semstate)->lex = lex; ((struct DoState *) sem.semstate)->buf = makeStringInfo(); need_strings = true; break; @@ -131,8 +135,8 @@ main(int argc, char **argv) exit(1); } - makeJsonLexContextIncremental(&lex, PG_UTF8, need_strings); - setJsonLexContextOwnsTokens(&lex, lex_owns_tokens); + makeJsonLexContextIncremental(lex, PG_UTF8, need_strings); + setJsonLexContextOwnsTokens(lex, lex_owns_tokens); initStringInfo(&json); if ((json_file = fopen(testfile, PG_BINARY_R)) == NULL) @@ -165,12 +169,12 @@ main(int argc, char **argv) bytes_left -= n_read; if (bytes_left > 0) { - result = pg_parse_json_incremental(&lex, testsem, + result = pg_parse_json_incremental(lex, testsem, json.data, n_read, false); if (result != JSON_INCOMPLETE) { - fprintf(stderr, "%s\n", json_errdetail(result, &lex)); + fprintf(stderr, "%s\n", json_errdetail(result, lex)); ret = 1; goto cleanup; } @@ -178,12 +182,12 @@ main(int argc, char **argv) } else { - result = pg_parse_json_incremental(&lex, testsem, + result = pg_parse_json_incremental(lex, testsem, json.data, n_read, true); if (result != JSON_SUCCESS) { - fprintf(stderr, "%s\n", json_errdetail(result, &lex)); + fprintf(stderr, "%s\n", json_errdetail(result, lex)); ret = 1; goto cleanup; } @@ -195,8 +199,9 @@ main(int argc, char **argv) cleanup: fclose(json_file); - freeJsonLexContext(&lex); + freeJsonLexContext(lex); free(json.data); + free(lex); return ret; } -- 2.39.3 (Apple Git-146) ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: jsonapi: scary new warnings with LTO enabled 2025-04-22 10:10 Re: jsonapi: scary new warnings with LTO enabled Daniel Gustafsson <[email protected]> @ 2025-04-23 00:01 ` Jacob Champion <[email protected]> 2025-04-23 09:35 ` Re: jsonapi: scary new warnings with LTO enabled Daniel Gustafsson <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Jacob Champion @ 2025-04-23 00:01 UTC (permalink / raw) To: Daniel Gustafsson <[email protected]>; +Cc: Tom Lane <[email protected]>; PostgreSQL Developers <[email protected]> On Tue, Apr 22, 2025 at 3:10 AM Daniel Gustafsson <[email protected]> wrote: > My preference is that no operation can silently work on a failed object, but > it's not a hill (even more so given where we are in the cycle). Hm, okay. Something to talk about with Andrew and Peter, maybe. > The attached > v3 allocates via the JSON api, no specific error handling should be required as > it's already handled today. pgindent shows one whitespace change on my machine (comment indentation), but other than that, LGTM! Fuzzers are happy too. Thanks, --Jacob ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: jsonapi: scary new warnings with LTO enabled 2025-04-22 10:10 Re: jsonapi: scary new warnings with LTO enabled Daniel Gustafsson <[email protected]> 2025-04-23 00:01 ` Re: jsonapi: scary new warnings with LTO enabled Jacob Champion <[email protected]> @ 2025-04-23 09:35 ` Daniel Gustafsson <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Daniel Gustafsson @ 2025-04-23 09:35 UTC (permalink / raw) To: Jacob Champion <[email protected]>; +Cc: Tom Lane <[email protected]>; PostgreSQL Developers <[email protected]> > On 23 Apr 2025, at 02:01, Jacob Champion <[email protected]> wrote: > On Tue, Apr 22, 2025 at 3:10 AM Daniel Gustafsson <[email protected]> wrote: >> The attached >> v3 allocates via the JSON api, no specific error handling should be required as >> it's already handled today. > > pgindent shows one whitespace change on my machine (comment > indentation), but other than that, LGTM! Fuzzers are happy too. Thanks for confirming, I've pushed this now. -- Daniel Gustafsson ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Support EXCEPT for TABLES IN SCHEMA publications @ 2026-07-10 11:33 ` Nisha Moond <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Nisha Moond @ 2026-07-10 11:33 UTC (permalink / raw) To: shveta malik <[email protected]>; +Cc: Peter Smith <[email protected]>; Zsolt Parragi <[email protected]>; [email protected]; Amit Kapila <[email protected]> On Wed, Jul 8, 2026 at 2:58 PM shveta malik <[email protected]> wrote: > > On Wed, Jul 8, 2026 at 2:22 PM shveta malik <[email protected]> wrote: > > > > Nisha, the patch (testing wise) is okay, but some code-optimization > > might help. I am stuck on reveiw of GetTopMostAncestorInPublication() > > and all its callers. > > Thanks, Shveta. > > 1) > > caller 1: pub_rf_contains_invalid_column(): > > > > It is not understood why pub_rf_contains_invalid_column() does not > > pass 'exceptPubids' to GetTopMostAncestorInPublication(). Perhaps a > > comment will help. IIUC, a parittion (for which we are trying to > > verify row-filter expression) can not co-exist in a subscription where > > its ROOT is already excluded. This assumption will be true once you > > address the issue of CREATE SUB (in [1]) sent in my previous email. > > After that, this will still need a comment. > > You're right. Neither pub_rf_contains_invalid_column() nor pub_contains_invalid_column() depends on GetTopMostAncestorInPublication(), since RelationBuildPublicationDesc() already prepares puboids after excluding GetRelationExcludedPublications(). This follows the same approach used for the FOR ALL TABLES case. With your suggestion below, the except_pubids argument is no longer needed, so I don't think any additional comments are necessary. > > 2) > > Both caller 2 (get_rel_sync_entry) and caller 3 > > (is_table_publishable_in_publication) are trying to see if partition > > should be considered as included in publication and thus are trying to > > see if ROOT is excluded. But the implementation is very different. > > > > The get_rel_sync_entry() does that by passing except_pubids to > > GetTopMostAncestorInPublication(). This except_pubids was computed for > > topmost ROOT of the parition in the caller and then it skips checking > > pg_pub_namepspace if given pubid is part of except_pubids arguement. > > > > While is_table_publishable_in_publication() does that by selecting > > topmost ancestor incuded in a given pubid using > > GetTopMostAncestorInPublication(), without consulting/computing > > except_pubids. And then later checks if the ancestor returned by > > GetTopMostAncestorInPublication() is in pg_publicaiton_rel with > > 'prexcept' true. > > > > IMO, GetTopMostAncestorInPublication() should itself be inclusive of > > logic where it filters out the table (does not return it as result) if > > ROOT is excluded. And even we should not be passing an argument for > > that (this is my initial thought). By making such a logic, we need not > > to bother about all > > the callers to see if caller has correct logic to deal with output of > > GetTopMostAncestorInPublication(). Can you think on this line and > > check the feasibility. > > > > I thought more on this. Do you think we can do this? > > GetTopMostAncestorInPublication() is already accepting a list of > ancestors in a ordered fashion, root at the end. We get root from this > ancestor list, check if root is in pg_publication_rel with > except=true. If so, we skip the rest of the logic and return > InvalidOid from GetTopMostAncestorInPublication(). > > The 'except_pubids' argument is not required. Callers need not to have > special logic to send this arguement or to have extra-processing on > output as done by is_table_publishable_in_publication() currently. > Let me know if I have missed anything. The suggested optimization looks reasonable to me. I verified the test cases for both callers (2 and 3) and finalized the changes. During verification, I found it easy to miss cross-schema partition cases i.e., when the partition root is excluded from one schema while a child partition belongs to another included schema. To cover this, I added tests in patch 0001 that tests both get_rel_sync_entry() and is_table_publishable_in_publication(). Attached is the v20 patch set. -- Thanks, Nisha Attachments: [application/octet-stream] v20-0001-Support-EXCEPT-clause-for-schema-level-publicati.patch (76.4K, ../../CABdArM72ufPkmOttET=q+kDaFrUMSUGYgZOh_OrBvuH-EPM1Bg@mail.gmail.com/2-v20-0001-Support-EXCEPT-clause-for-schema-level-publicati.patch) download | inline diff: From e8823fca2f98530a6248307fadd948c87ecb0f03 Mon Sep 17 00:00:00 2001 From: Nisha Moond <[email protected]> Date: Tue, 16 Jun 2026 10:58:49 +0530 Subject: [PATCH v20 1/5] 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,...); --- src/backend/catalog/pg_publication.c | 268 ++++++++++++++++---- src/backend/commands/publicationcmds.c | 185 +++++++++++++- src/backend/commands/tablecmds.c | 11 + src/backend/parser/gram.y | 54 +++- src/backend/replication/pgoutput/pgoutput.c | 46 +++- src/backend/utils/cache/relcache.c | 41 ++- src/bin/psql/describe.c | 18 ++ src/bin/psql/tab-complete.in.c | 35 ++- src/include/commands/publicationcmds.h | 2 + src/include/nodes/parsenodes.h | 2 + src/test/regress/expected/publication.out | 221 +++++++++++++++- src/test/regress/sql/publication.sql | 142 ++++++++++- src/test/subscription/t/037_except.pl | 208 ++++++++++++++- 13 files changed, 1142 insertions(+), 91 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 1ec94c851b2..0bdcc07c42d 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -53,9 +53,10 @@ typedef struct * error if not. */ static void -check_publication_add_relation(PublicationRelInfo *pri) +check_publication_add_relation(PublicationRelInfo *pri, Oid pubid) { Relation targetrel = pri->relation; + Oid relid = RelationGetRelid(targetrel); const char *relname; const char *errormsg; @@ -70,12 +71,57 @@ check_publication_add_relation(PublicationRelInfo *pri) errormsg = gettext_noop("cannot add relation \"%s\" to publication"); } - /* If in EXCEPT clause, must be root partitioned table */ - if (pri->except && targetrel->rd_rel->relispartition) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg(errormsg, relname), - errdetail("This operation is not supported for individual partitions."))); + if (targetrel->rd_rel->relispartition) + { + if (pri->except) + { + /* If in EXCEPT clause, must be root partitioned table */ + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg(errormsg, relname), + errdetail("This operation is not supported for individual partitions."))); + } + else + { + /* + * If not in EXCEPT clause, a partition cannot be independently + * included when its partition root is in this publication's + * EXCEPT list. Doing so would leave the catalog inconsistent + * (root excluded, partition explicitly included) and the + * ancestor-cascade rule would silently override the include at + * replication time. + * + * Only the root of a partition hierarchy can ever appear in an + * EXCEPT clause (a partition itself is rejected just above), so + * checking any intermediate ancestor cannot find a match; the + * root is the only one worth a catalog lookup. + */ + Oid root = llast_oid(get_partition_ancestors(relid)); + HeapTuple atup; + + atup = SearchSysCache2(PUBLICATIONRELMAP, + ObjectIdGetDatum(root), + ObjectIdGetDatum(pubid)); + if (HeapTupleIsValid(atup)) + { + bool root_except = ((Form_pg_publication_rel) GETSTRUCT(atup))->prexcept; + + ReleaseSysCache(atup); + + if (root_except) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("cannot add partition \"%s\" to publication \"%s\"", + RelationGetQualifiedRelationName(targetrel), + get_publication_name(pubid, false)), + errdetail("Ancestor \"%s\" of partition \"%s\" is currently listed in the EXCEPT clause of the publication.", + quote_qualified_identifier(get_namespace_name(get_rel_namespace(root)), + get_rel_name(root)), + RelationGetQualifiedRelationName(targetrel)), + errhint("Change EXCEPT list using ALTER PUBLICATION ... SET TABLES IN SCHEMA ... EXCEPT."))); + } + } + } /* Must be a regular or partitioned table */ if (RelationGetForm(targetrel)->relkind != RELKIND_RELATION && @@ -340,19 +386,20 @@ is_table_publication(Oid pubid) scan = systable_beginscan(pubrelsrel, PublicationRelPrpubidIndexId, true, NULL, 1, &scankey); - tup = systable_getnext(scan); - if (HeapTupleIsValid(tup)) - { - Form_pg_publication_rel pubrel; - - pubrel = (Form_pg_publication_rel) GETSTRUCT(tup); - /* - * For any publication, pg_publication_rel contains either only EXCEPT - * entries or only explicitly included tables. Therefore, examining - * the first tuple is sufficient to determine table inclusion. - */ - result = !pubrel->prexcept; + /* + * A publication can hold both explicit-include (prexcept=false) and + * EXCEPT (prexcept=true) rows for the same pubid. Scan until we find an + * explicit-include row, rather than relying on the first row's prexcept + * value. + */ + while (HeapTupleIsValid(tup = systable_getnext(scan))) + { + if (!((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept) + { + result = true; + break; + } } systable_endscan(scan); @@ -456,11 +503,38 @@ GetPubPartitionOptionRelations(List *result, PublicationPartOpt pub_partopt, * ancestor is at the end of the list. */ Oid -GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level) +GetTopMostAncestorInPublication(Oid puboid, List *ancestors, + int *ancestor_level) { ListCell *lc; Oid topmost_relid = InvalidOid; int level = 0; + HeapTuple tup; + + if (ancestors == NIL) + return InvalidOid; + + /* + * If the partition root (the last element of ancestors) is excluded from + * this publication via its EXCEPT clause, the partition is not published + * through this publication at all, so we short-circuit and return + * InvalidOid without walking the ancestor list. Only the root of a + * partition hierarchy can ever appear in a publication's EXCEPT clause (a + * partition itself is rejected from EXCEPT elsewhere), so it's the only + * one worth a catalog lookup. + */ + tup = SearchSysCache2(PUBLICATIONRELMAP, + ObjectIdGetDatum(llast_oid(ancestors)), + ObjectIdGetDatum(puboid)); + if (HeapTupleIsValid(tup)) + { + bool root_except = ((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept; + + ReleaseSysCache(tup); + + if (root_except) + return InvalidOid; + } /* * Find the "topmost" ancestor that is in this publication. @@ -555,21 +629,35 @@ 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("cannot add table \"%s\" to publication \"%s\"", + RelationGetQualifiedRelationName(targetrel), + pub->name), + errdetail("The table is currently listed in the EXCEPT clause of the publication."), + errhint("Change EXCEPT list using ALTER PUBLICATION ... SET TABLES IN SCHEMA ... EXCEPT."))); + else + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("relation \"%s\" is already member of publication \"%s\"", + RelationGetRelationName(targetrel), pub->name))); } - check_publication_add_relation(pri); + check_publication_add_relation(pri, pubid); /* Validate and translate column names into a Bitmapset of attnums. */ attnums = pub_collist_validate(pri->relation, pri->columns); @@ -992,12 +1080,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); } @@ -1059,15 +1148,15 @@ 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 ? - PUBLICATION_PART_ROOT : - PUBLICATION_PART_LEAF); + except_relids = GetExcludedPublicationTables(pubid, pubviaroot ? + PUBLICATION_PART_ROOT : + PUBLICATION_PART_LEAF); classRel = table_open(RelationRelationId, AccessShareLock); @@ -1085,7 +1174,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); } @@ -1107,7 +1196,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); } @@ -1242,22 +1331,68 @@ 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); + + /* + * A partition whose ancestor is in the publication's EXCEPT + * list is also excluded, even if the partition itself lives + * in a different (included) schema. Individual partitions + * cannot appear in EXCEPT, so cascading from the root is the + * only way to exclude a partition; this matches the ALL + * TABLES EXCEPT (TABLE root) behavior. + */ + 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; @@ -1334,6 +1469,7 @@ is_table_publishable_in_publication(Oid relid, Publication *pub) { bool relispartition; List *ancestors = NIL; + HeapTuple tup; /* * For non-pubviaroot publications, a partitioned table is never the @@ -1390,20 +1526,60 @@ is_table_publishable_in_publication(Oid relid, Publication *pub) * If it's false, the partition is covered by its ancestor's presence in * the publication, it should be included (return true). */ - if (relispartition && - OidIsValid(GetTopMostAncestorInPublication(pub->oid, ancestors, NULL))) - return !pub->pubviaroot; + if (relispartition) + { + HeapTuple root_tup; + + if (OidIsValid(GetTopMostAncestorInPublication(pub->oid, ancestors, NULL))) + return !pub->pubviaroot; + + /* + * GetTopMostAncestorInPublication() returns InvalidOid both when no + * ancestor matched at all, and when the root is excluded via EXCEPT. + * Disambiguate here: if the root is excluded, the partition is never + * published through this publication, regardless of whether the + * partition's own schema is separately included. + */ + root_tup = SearchSysCache2(PUBLICATIONRELMAP, + ObjectIdGetDatum(llast_oid(ancestors)), + ObjectIdGetDatum(pub->oid)); + if (HeapTupleIsValid(root_tup)) + { + bool root_except = ((Form_pg_publication_rel) GETSTRUCT(root_tup))->prexcept; + + ReleaseSysCache(root_tup); + if (root_except) + return false; + } + } /* * Check whether the table is explicitly published via pg_publication_rel * or pg_publication_namespace. + * + * A pg_publication_rel row with prexcept=true means the table is + * explicitly excluded via EXCEPT and must not be reported as published, + * even if its schema is otherwise included. A row with prexcept=false + * means it is explicitly included. If no pg_publication_rel row exists, + * the table is published iff its schema appears in + * pg_publication_namespace. */ - return (SearchSysCacheExists2(PUBLICATIONRELMAP, - ObjectIdGetDatum(relid), - ObjectIdGetDatum(pub->oid)) || - SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP, - ObjectIdGetDatum(get_rel_namespace(relid)), - ObjectIdGetDatum(pub->oid))); + + tup = SearchSysCache2(PUBLICATIONRELMAP, + ObjectIdGetDatum(relid), + ObjectIdGetDatum(pub->oid)); + if (HeapTupleIsValid(tup)) + { + bool is_except; + + is_except = ((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept; + ReleaseSysCache(tup); + return !is_except; + } + + return SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP, + ObjectIdGetDatum(get_rel_namespace(relid)), + ObjectIdGetDatum(pub->oid)); } /* diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c index 440adb356ad..9a922a6dcc3 100644 --- a/src/backend/commands/publicationcmds.c +++ b/src/backend/commands/publicationcmds.c @@ -22,6 +22,7 @@ #include "catalog/namespace.h" #include "catalog/objectaccess.h" #include "catalog/objectaddress.h" +#include "catalog/partition.h" #include "catalog/pg_database.h" #include "catalog/pg_inherits.h" #include "catalog/pg_namespace.h" @@ -71,6 +72,7 @@ static void PublicationAddSchemas(Oid pubid, List *schemas, bool if_not_exists, AlterPublicationStmt *stmt); static void PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok); static char defGetGeneratedColsOption(DefElem *def); +static void CheckExceptNotInTableList(List *except_rels, List *explicitrelids); static void @@ -181,7 +183,7 @@ parse_publication_options(ParseState *pstate, */ static void ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate, - List **rels, List **exceptrels, List **schemas) + List **rels, List **except_pubtables, List **schemas) { ListCell *cell; PublicationObjSpec *pubobj; @@ -200,7 +202,7 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate, { case PUBLICATIONOBJ_EXCEPT_TABLE: pubobj->pubtable->except = true; - *exceptrels = lappend(*exceptrels, pubobj->pubtable); + *except_pubtables = lappend(*except_pubtables, pubobj->pubtable); break; case PUBLICATIONOBJ_TABLE: pubobj->pubtable->except = false; @@ -224,6 +226,38 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate, /* Filter out duplicates if user specifies "sch1, sch1" */ *schemas = list_append_unique_oid(*schemas, schemaid); + + /* + * Qualify unqualified EXCEPT table names with the resolved + * current schema and reject any explicitly cross-schema + * entries. This mirrors the parse-time handling done for + * TABLES_IN_SCHEMA in preprocess_pubobj_list(), deferred here + * because CURRENT_SCHEMA is not known until execution time. + */ + if (pubobj->except_tables != NIL) + { + char *cur_schema_name = get_namespace_name(schemaid); + + foreach_ptr(PublicationObjSpec, eobj, pubobj->except_tables) + { + const char *eobj_schemaname = + eobj->pubtable->relation->schemaname; + const char *eobj_relname = + eobj->pubtable->relation->relname; + + if (eobj_schemaname == NULL) + eobj->pubtable->relation->schemaname = cur_schema_name; + else if (strcmp(eobj_schemaname, cur_schema_name) != 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("table \"%s\" in EXCEPT clause does not belong to schema \"%s\"", + quote_qualified_identifier(eobj_schemaname, eobj_relname), + cur_schema_name)); + + *except_pubtables = lappend(*except_pubtables, + eobj->pubtable); + } + } break; default: /* shouldn't happen */ @@ -770,6 +804,48 @@ TransformPubWhereClauses(List *tables, const char *queryString, } } +/* + * Check that no relation in except_rels is also present in explicitrelids, + * the list of OIDs added to the publication by the explicit TABLE list. A + * table cannot be both explicitly published and excluded in the same DDL. + * + * Also check that no relation in explicitrelids is a partition whose + * partition root is in except_rels; that would leave the catalog + * inconsistent (root excluded, partition explicitly included) with the + * ancestor-cascade rule silently overriding the include at replication + * time. + */ +static void +CheckExceptNotInTableList(List *except_rels, List *explicitrelids) +{ + foreach_oid(explicitrelid, explicitrelids) + { + Oid root = InvalidOid; + + if (get_rel_relispartition(explicitrelid)) + root = llast_oid(get_partition_ancestors(explicitrelid)); + + foreach_ptr(PublicationRelInfo, pri, except_rels) + { + Oid exceptrelid = RelationGetRelid(pri->relation); + + if (exceptrelid == explicitrelid) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("table \"%s\" cannot be both published and excluded", + RelationGetQualifiedRelationName(pri->relation))); + + if (OidIsValid(root) && exceptrelid == root) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("partition \"%s\" cannot be both published and excluded", + quote_qualified_identifier(get_namespace_name(get_rel_namespace(explicitrelid)), + get_rel_name(explicitrelid))), + errdetail("Ancestor \"%s\" is also being excluded by this same command.", + RelationGetQualifiedRelationName(pri->relation)))); + } + } +} /* * Given a list of tables that are going to be added to a publication, @@ -849,7 +925,7 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) char publish_generated_columns; AclResult aclresult; List *relations = NIL; - List *exceptrelations = NIL; + List *except_pubtables = NIL; List *schemaidlist = NIL; /* must have CREATE privilege on database */ @@ -936,16 +1012,16 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) /* Associate objects with the publication. */ ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations, - &exceptrelations, &schemaidlist); + &except_pubtables, &schemaidlist); if (stmt->for_all_tables) { /* Process EXCEPT table list */ - if (exceptrelations != NIL) + if (except_pubtables != NIL) { List *rels; - rels = OpenTableList(exceptrelations); + rels = OpenTableList(except_pubtables); PublicationAddTables(puboid, rels, true, NULL); CloseTableList(rels); } @@ -959,6 +1035,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 +1055,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_pubtables != NIL) + { + foreach_ptr(PublicationRelInfo, pri, rels) + explicitrelids = lappend_oid(explicitrelids, + RelationGetRelid(pri->relation)); + } + PublicationAddTables(puboid, rels, true, NULL); CloseTableList(rels); } @@ -989,6 +1080,26 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) */ LockSchemaList(schemaidlist); PublicationAddSchemas(puboid, schemaidlist, true, NULL); + + if (except_pubtables != NIL) + { + List *except_rels; + + except_rels = OpenTableList(except_pubtables); + + /* + * Validate that each excluded table is not also in the + * explicit table list (which would be contradictory), and + * that no explicit partition's root is in the except list. + * Use the in-memory explicitrelids collected above rather + * than re-reading the catalog, which may not yet see the + * just-inserted rows. + */ + CheckExceptNotInTableList(except_rels, explicitrelids); + + PublicationAddTables(puboid, except_rels, true, NULL); + CloseTableList(except_rels); + } } } @@ -1683,12 +1794,12 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt) else { List *relations = NIL; - List *exceptrelations = NIL; + List *except_pubtables = NIL; List *schemaidlist = NIL; Oid pubid = pubform->oid; ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations, - &exceptrelations, &schemaidlist); + &except_pubtables, &schemaidlist); CheckAlterPublication(stmt, tup, relations, schemaidlist); @@ -1711,7 +1822,7 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt) errmsg("publication \"%s\" does not exist", stmt->pubname)); - relations = list_concat(relations, exceptrelations); + relations = list_concat(relations, except_pubtables); AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext, schemaidlist != NIL); AlterPublicationSchemas(stmt, tup, schemaidlist); @@ -1829,6 +1940,62 @@ RemovePublicationSchemaById(Oid psoid) table_close(rel, RowExclusiveLock); } +/* + * Remove any EXCEPT clause entries for a relation from schema publications. + * Called when a table changes schema (ALTER TABLE ... SET SCHEMA), so that + * a schema-scoped exclusion does not silently follow the table to its new + * schema. + */ +void +RemoveSchemaPubExceptForRel(Oid relid, Oid oldNspOid, Oid newNspOid) +{ + List *pubids; + + /* + * If the table is not actually moving to a different schema (no-op ALTER + * TABLE ... SET SCHEMA <current_schema>), there is nothing to do. + */ + if (oldNspOid == newNspOid) + return; + + pubids = GetRelationExcludedPublications(relid); + + foreach_oid(pubid, pubids) + { + Oid proid; + + /* + * This problem does not apply to FOR ALL TABLES publications, because + * their EXCEPT clause is publication-scoped, not schema-scoped: the + * exclusion should persist regardless of what schema the table is in. + */ + if (!is_schema_publication(pubid)) + continue; + + proid = GetSysCacheOid2(PUBLICATIONRELMAP, + Anum_pg_publication_rel_oid, + ObjectIdGetDatum(relid), + ObjectIdGetDatum(pubid)); + if (OidIsValid(proid)) + { + ObjectAddress obj; + + ObjectAddressSet(obj, PublicationRelRelationId, proid); + + ereport(DEBUG2, + errmsg_internal("auto-remove exclusion of table \"%s.%s\" from publication \"%s\": table moved to schema \"%s\"", + get_namespace_name(oldNspOid), + get_rel_name(relid), + get_publication_name(pubid, false), + get_namespace_name(newNspOid))); + + performDeletion(&obj, DROP_CASCADE, 0); + } + } + + list_free(pubids); +} + /* * Open relations specified by a PublicationTable list. * The returned tables are locked in ShareUpdateExclusiveLock mode in order to diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index cb93c3e935a..d8e1959ee6f 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -63,6 +63,7 @@ #include "commands/event_trigger.h" #include "commands/extension.h" #include "commands/repack.h" +#include "commands/publicationcmds.h" #include "commands/sequence.h" #include "commands/tablecmds.h" #include "commands/tablespace.h" @@ -19745,6 +19746,16 @@ AlterTableNamespaceInternal(Relation rel, Oid oldNspOid, Oid nspOid, AlterConstraintNamespaces(RelationGetRelid(rel), oldNspOid, nspOid, false, objsMoved); + /* + * Remove any EXCEPT clause entries for this relation from schema + * publications. A schema-scoped exclusion is no longer meaningful once + * the table moves to a different schema. + */ + if (rel->rd_rel->relkind == RELKIND_RELATION || + rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + RemoveSchemaPubExceptForRel(RelationGetRelid(rel), oldNspOid, + nspOid); + table_close(classRel, RowExclusiveLock); } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 9e05a314707..2a8f999d586 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" @@ -11283,7 +11284,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 [, ...] )] [, ...] * *****************************************************************************/ @@ -11343,23 +11344,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. @@ -11403,10 +11407,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; } ; @@ -20873,6 +20878,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) @@ -20901,6 +20908,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, @@ -20949,6 +20963,36 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner) errcode(ERRCODE_SYNTAX_ERROR), errmsg("invalid schema name"), parser_errposition(pubobj->location)); + + /* + * For TABLES_IN_SCHEMA, qualify unqualified EXCEPT table names + * with the parent schema and reject cross-schema entries at parse + * time, then flatten into the top-level list. + * + * For TABLES_IN_CUR_SCHEMA the schema name is not yet known, so + * skip both steps here; ObjectsInPublicationToOids() will + * qualify names and validate schema membership at execution time. + */ + if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLES_IN_SCHEMA) + { + foreach_ptr(PublicationObjSpec, eobj, pubobj->except_tables) + { + 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_INVALID_OBJECT_DEFINITION), + 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..8d5a534ed1a 100644 --- a/src/backend/replication/pgoutput/pgoutput.c +++ b/src/backend/replication/pgoutput/pgoutput.c @@ -2265,6 +2265,11 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) int level; List *ancestors = get_partition_ancestors(relid); + /* + * GetTopMostAncestorInPublication() itself skips + * schema-based ancestor matches when the partition root + * is excluded via this publication's EXCEPT clause. + */ ancestor = GetTopMostAncestorInPublication(pub->oid, ancestors, &level); @@ -2280,10 +2285,45 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) } } - if (list_member_oid(pubids, pub->oid) || - list_member_oid(schemaPubids, pub->oid) || - ancestor_published) + if (list_member_oid(pubids, pub->oid) || ancestor_published) publish = true; + else if (list_member_oid(schemaPubids, pub->oid)) + { + Oid root_relid; + HeapTuple tup; + bool is_except = false; + + /* + * schemaPubids is relid's own schema, independent of the + * ancestor walk above -- a partition can live in a + * different schema than its root. Still need to check + * for exclusion here, using the top-most ancestor since + * only a root (non-partition) table can appear in an + * EXCEPT clause. + */ + if (am_partition) + { + List *ancestors = get_partition_ancestors(relid); + + root_relid = llast_oid(ancestors); + list_free(ancestors); + } + else + root_relid = relid; + + tup = SearchSysCache2(PUBLICATIONRELMAP, + ObjectIdGetDatum(root_relid), + ObjectIdGetDatum(pub->oid)); + + if (HeapTupleIsValid(tup)) + { + is_except = ((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept; + ReleaseSysCache(tup); + } + + if (!is_except) + publish = true; + } } /* diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index fb4e042be8a..92ff89fa2a4 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -5845,16 +5845,29 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc) /* Fetch the publication membership info. */ puboids = GetRelationIncludedPublications(relid); schemaid = RelationGetNamespace(relation); - puboids = list_concat_unique_oid(puboids, GetSchemaPublications(schemaid)); if (relation->rd_rel->relispartition) { - Oid last_ancestor_relid; - - /* Add publications that the ancestors are in too. */ ancestors = get_partition_ancestors(relid); - last_ancestor_relid = llast_oid(ancestors); + exceptpuboids = GetRelationExcludedPublications(llast_oid(ancestors)); + } + else + exceptpuboids = GetRelationExcludedPublications(relid); + /* + * Subtract exceptpuboids from the schema-publication contribution so that + * a relation EXCEPT'd from a schema publication does not inherit that + * publication's pubactions. Without this filter, + * CheckCmdReplicaIdentity() would demand REPLICA IDENTITY for + * UPDATE/DELETE even on a explicitly excluded table. + */ + puboids = list_concat_unique_oid(puboids, + list_difference_oid(GetSchemaPublications(schemaid), + exceptpuboids)); + + if (relation->rd_rel->relispartition) + { + /* Add publications that the ancestors are in too. */ foreach(lc, ancestors) { Oid ancestor = lfirst_oid(lc); @@ -5863,23 +5876,9 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc) GetRelationIncludedPublications(ancestor)); schemaid = get_rel_namespace(ancestor); puboids = list_concat_unique_oid(puboids, - GetSchemaPublications(schemaid)); + list_difference_oid(GetSchemaPublications(schemaid), + exceptpuboids)); } - - /* - * Only the top-most ancestor can appear in the EXCEPT clause. - * Therefore, for a partition, exclusion must be evaluated at the - * top-most ancestor. - */ - exceptpuboids = GetRelationExcludedPublications(last_ancestor_relid); - } - else - { - /* - * For a regular table or a root partitioned table, check exclusion on - * table itself. - */ - exceptpuboids = GetRelationExcludedPublications(relid); } alltablespuboids = GetAllTablesPublications(); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index fef86b4cca3..72e8f58fc3e 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -6815,6 +6815,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 49ea584cd4f..067168699f6 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -1065,6 +1065,24 @@ 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'" + +#define Query_for_list_of_tables_in_current_schema \ +"SELECT 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 c.relname LIKE '%s' "\ +" AND n.nspname = pg_catalog.current_schema()" + /* 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 "\ @@ -3774,8 +3792,21 @@ 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", "CURRENT_SCHEMA", "EXCEPT", "(", "TABLE")) + COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_current_schema); + 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/commands/publicationcmds.h b/src/include/commands/publicationcmds.h index 4cf45c17cc5..fb1703aa675 100644 --- a/src/include/commands/publicationcmds.h +++ b/src/include/commands/publicationcmds.h @@ -27,6 +27,8 @@ extern void AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt); extern void RemovePublicationById(Oid pubid); extern void RemovePublicationRelById(Oid proid); extern void RemovePublicationSchemaById(Oid psoid); +extern void RemoveSchemaPubExceptForRel(Oid relid, Oid oldNspOid, + Oid newNspOid); extern ObjectAddress AlterPublicationOwner(const char *name, Oid newOwnerId); extern void AlterPublicationOwner_oid(Oid pubid, Oid newOwnerId); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 77f69f94607..63abc0b6960 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -4532,6 +4532,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..0729e546116 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,154 @@ 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" + +-- ALTER TABLE ... SET SCHEMA on a table excluded by a schema publication: +-- the schema-scoped exclusion is no longer meaningful once the table moves +-- out of its schema, so the exclusion is auto-removed. +CREATE PUBLICATION testpub_schema_except_setsch + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2); +\dRp+ testpub_schema_except_setsch + Publication testpub_schema_except_setsch + 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" + +ALTER TABLE pub_test.testpub_tbl_s2 SET SCHEMA public; +\dRp+ testpub_schema_except_setsch + Publication testpub_schema_except_setsch + 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" + +DROP PUBLICATION testpub_schema_except_setsch; +-- Restore for further tests +ALTER TABLE public.testpub_tbl_s2 SET SCHEMA pub_test; +-- 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 be both published and excluded +-- 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: partition still rejected from EXCEPT even when its own root is also +-- named in the same EXCEPT list +CREATE PUBLICATION testpub_except_partition_with_root + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_part_s, TABLE pub_test.testpub_parted_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); + ^ +-- fail: ADD TABLE for a partition is rejected when a partition ancestor +-- is currently in the publication's EXCEPT list. +CREATE PUBLICATION testpub_except_ancestor + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); +ALTER PUBLICATION testpub_except_ancestor ADD TABLE pub_test.testpub_part_s; +ERROR: cannot add partition "pub_test.testpub_part_s" to publication "testpub_except_ancestor" +DETAIL: Ancestor "pub_test.testpub_parted_s" of partition "pub_test.testpub_part_s" is currently listed in the EXCEPT clause of the publication. +HINT: Change EXCEPT list using ALTER PUBLICATION ... SET TABLES IN SCHEMA ... EXCEPT. +DROP PUBLICATION testpub_except_ancestor; +-- fail: same contradiction (explicit partition + EXCEPT-ed ancestor), but +-- given in a single statement instead of two separate ones; the ancestor's +-- EXCEPT row doesn't exist in the catalog yet when the explicit partition is +-- validated, so this must be caught by comparing the in-memory lists +CREATE PUBLICATION testpub_except_ancestor_same_create + FOR TABLE pub_test.testpub_part_s, + TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); +ERROR: partition "pub_test.testpub_part_s" cannot be both published and excluded +DETAIL: Ancestor "pub_test.testpub_parted_s" is also being excluded by this same command. +-- 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 @@ -1953,6 +2106,27 @@ ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA foo, bar (a, b); ERROR: column specification not allowed for schema LINE 1: ...TION testpub1_forschema ADD TABLES IN SCHEMA foo, bar (a, b)... ^ +-- EXCEPT clause with CURRENT_SCHEMA: cross-schema entry must be rejected +SET search_path = pub_test1; +-- qualified name from wrong schema -> error +CREATE PUBLICATION testpub_cursch_except FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE pub_test2.tbl1); +ERROR: table "pub_test2.tbl1" in EXCEPT clause does not belong to schema "pub_test1" +-- unqualified name implicitly qualified with current schema (pub_test1.tbl) +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_cursch_except FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE tbl); +RESET client_min_messages; +\dRp+ testpub_cursch_except + Publication testpub_cursch_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_test1" +Except tables: + "pub_test1.tbl" + +DROP PUBLICATION testpub_cursch_except; +RESET search_path; -- cleanup pub_test1 schema for invalidation tests ALTER PUBLICATION testpub2_forschema DROP TABLES IN SCHEMA pub_test1; DROP PUBLICATION testpub3_forschema, testpub4_forschema, testpub5_forschema, testpub6_forschema, testpub_fortable; @@ -2304,6 +2478,7 @@ DROP ROLE regress_publication_user_dummy; -- Test pg_get_publication_tables(text[], oid) function CREATE SCHEMA gpt_test_sch; CREATE TABLE gpt_test_sch.tbl_sch (id int); +CREATE TABLE gpt_test_sch.tbl_sch2 (id int); CREATE TABLE tbl_normal (id int); CREATE TABLE tbl_parent (id1 int, id2 int, id3 int) PARTITION BY RANGE (id1); CREATE TABLE tbl_part1 PARTITION OF tbl_parent FOR VALUES FROM (1) TO (10); @@ -2314,6 +2489,7 @@ CREATE PUBLICATION pub_all_no_viaroot FOR ALL TABLES WITH (publish_via_partition CREATE PUBLICATION pub_all_except FOR ALL TABLES EXCEPT (TABLE tbl_parent, gpt_test_sch.tbl_sch) WITH (publish_via_partition_root = true); CREATE PUBLICATION pub_all_except_no_viaroot FOR ALL TABLES EXCEPT (TABLE tbl_parent, gpt_test_sch.tbl_sch) WITH (publish_via_partition_root = false); CREATE PUBLICATION pub_schema FOR TABLES IN SCHEMA gpt_test_sch; +CREATE PUBLICATION pub_schema_except FOR TABLES IN SCHEMA gpt_test_sch EXCEPT (TABLE gpt_test_sch.tbl_sch); CREATE PUBLICATION pub_normal FOR TABLE tbl_normal WHERE (id < 10); CREATE PUBLICATION pub_part_leaf FOR TABLE tbl_part1 WITH (publish_via_partition_root = false); CREATE PUBLICATION pub_part_parent FOR TABLE tbl_parent (id1, id2) WHERE (id1 = 10) WITH (publish_via_partition_root = true); @@ -2465,6 +2641,44 @@ SELECT * FROM test_gpt(ARRAY['pub_all_except_no_viaroot'], 'tbl_part1'); -- no r ---------+---------+-------+------ (0 rows) +-- test for EXCEPT clause with schema publication +SELECT * FROM test_gpt(ARRAY['pub_schema_except'], 'gpt_test_sch.tbl_sch'); -- no result (excluded) + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt(ARRAY['pub_schema_except'], 'gpt_test_sch.tbl_sch2'); -- one row (included via schema) + pubname | relname | attrs | qual +-------------------+----------+-------+------ + pub_schema_except | tbl_sch2 | 1 | +(1 row) + +-- test for EXCEPT clause with schema publication, where the excluded root's +-- partition lives in a different schema that is separately, fully included +-- (no EXCEPT) in the same publication. The root's exclusion must cascade to +-- the partition regardless of the partition's own schema membership. +CREATE SCHEMA gpt_cross_sch1; +CREATE SCHEMA gpt_cross_sch2; +CREATE TABLE gpt_cross_sch1.croot (id int) PARTITION BY RANGE (id); +CREATE TABLE gpt_cross_sch2.cpart1 PARTITION OF gpt_cross_sch1.croot FOR VALUES FROM (1) TO (10); +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION pub_cross_schema_except FOR TABLES IN SCHEMA gpt_cross_sch1 EXCEPT (TABLE gpt_cross_sch1.croot), TABLES IN SCHEMA gpt_cross_sch2; +RESET client_min_messages; +SELECT * FROM test_gpt(ARRAY['pub_cross_schema_except'], 'gpt_cross_sch1.croot'); -- no result (excluded) + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +-- no result (excluded via cascading root exclusion, even though cpart1's own +-- schema gpt_cross_sch2 is separately, fully included with no EXCEPT) +SELECT * FROM test_gpt(ARRAY['pub_cross_schema_except'], 'gpt_cross_sch2.cpart1'); + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +DROP PUBLICATION pub_cross_schema_except; +DROP TABLE gpt_cross_sch2.cpart1, gpt_cross_sch1.croot; +DROP SCHEMA gpt_cross_sch1, gpt_cross_sch2; -- two rows with different row filter SELECT * FROM test_gpt(ARRAY['pub_all', 'pub_normal'], 'tbl_normal'); pubname | relname | attrs | qual @@ -2517,6 +2731,7 @@ DROP PUBLICATION pub_all_no_viaroot; DROP PUBLICATION pub_all_except; DROP PUBLICATION pub_all_except_no_viaroot; DROP PUBLICATION pub_schema; +DROP PUBLICATION pub_schema_except; DROP PUBLICATION pub_normal; DROP PUBLICATION pub_part_leaf; DROP PUBLICATION pub_part_parent; @@ -2525,7 +2740,9 @@ DROP PUBLICATION pub_part_parent_child; DROP VIEW gpt_test_view; DROP TABLE tbl_normal, tbl_parent, tbl_part1; DROP SCHEMA gpt_test_sch CASCADE; -NOTICE: drop cascades to table gpt_test_sch.tbl_sch +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table gpt_test_sch.tbl_sch +drop cascades to table gpt_test_sch.tbl_sch2 -- stage objects for pg_dump tests CREATE SCHEMA pubme CREATE TABLE t0 (c int, d int) CREATE TABLE t1 (c int); CREATE SCHEMA pubme2 CREATE TABLE t0 (c int, d int); diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 041e14a4de6..055b9e97dc1 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,103 @@ 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 + +-- ALTER TABLE ... SET SCHEMA on a table excluded by a schema publication: +-- the schema-scoped exclusion is no longer meaningful once the table moves +-- out of its schema, so the exclusion is auto-removed. +CREATE PUBLICATION testpub_schema_except_setsch + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2); +\dRp+ testpub_schema_except_setsch +ALTER TABLE pub_test.testpub_tbl_s2 SET SCHEMA public; +\dRp+ testpub_schema_except_setsch +DROP PUBLICATION testpub_schema_except_setsch; +-- Restore for further tests +ALTER TABLE public.testpub_tbl_s2 SET SCHEMA pub_test; + +-- 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: partition still rejected from EXCEPT even when its own root is also +-- named in the same EXCEPT list +CREATE PUBLICATION testpub_except_partition_with_root + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_part_s, TABLE pub_test.testpub_parted_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); + +-- fail: ADD TABLE for a partition is rejected when a partition ancestor +-- is currently in the publication's EXCEPT list. +CREATE PUBLICATION testpub_except_ancestor + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); +ALTER PUBLICATION testpub_except_ancestor ADD TABLE pub_test.testpub_part_s; +DROP PUBLICATION testpub_except_ancestor; + +-- fail: same contradiction (explicit partition + EXCEPT-ed ancestor), but +-- given in a single statement instead of two separate ones; the ancestor's +-- EXCEPT row doesn't exist in the catalog yet when the explicit partition is +-- validated, so this must be caught by comparing the in-memory lists +CREATE PUBLICATION testpub_except_ancestor_same_create + FOR TABLE pub_test.testpub_part_s, + TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); + +-- 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; @@ -1189,6 +1288,18 @@ ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test1; ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA foo (a, b); ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA foo, bar (a, b); +-- EXCEPT clause with CURRENT_SCHEMA: cross-schema entry must be rejected +SET search_path = pub_test1; +-- qualified name from wrong schema -> error +CREATE PUBLICATION testpub_cursch_except FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE pub_test2.tbl1); +-- unqualified name implicitly qualified with current schema (pub_test1.tbl) +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_cursch_except FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE tbl); +RESET client_min_messages; +\dRp+ testpub_cursch_except +DROP PUBLICATION testpub_cursch_except; +RESET search_path; + -- cleanup pub_test1 schema for invalidation tests ALTER PUBLICATION testpub2_forschema DROP TABLES IN SCHEMA pub_test1; DROP PUBLICATION testpub3_forschema, testpub4_forschema, testpub5_forschema, testpub6_forschema, testpub_fortable; @@ -1443,6 +1554,7 @@ DROP ROLE regress_publication_user_dummy; -- Test pg_get_publication_tables(text[], oid) function CREATE SCHEMA gpt_test_sch; CREATE TABLE gpt_test_sch.tbl_sch (id int); +CREATE TABLE gpt_test_sch.tbl_sch2 (id int); CREATE TABLE tbl_normal (id int); CREATE TABLE tbl_parent (id1 int, id2 int, id3 int) PARTITION BY RANGE (id1); CREATE TABLE tbl_part1 PARTITION OF tbl_parent FOR VALUES FROM (1) TO (10); @@ -1454,6 +1566,7 @@ CREATE PUBLICATION pub_all_no_viaroot FOR ALL TABLES WITH (publish_via_partition CREATE PUBLICATION pub_all_except FOR ALL TABLES EXCEPT (TABLE tbl_parent, gpt_test_sch.tbl_sch) WITH (publish_via_partition_root = true); CREATE PUBLICATION pub_all_except_no_viaroot FOR ALL TABLES EXCEPT (TABLE tbl_parent, gpt_test_sch.tbl_sch) WITH (publish_via_partition_root = false); CREATE PUBLICATION pub_schema FOR TABLES IN SCHEMA gpt_test_sch; +CREATE PUBLICATION pub_schema_except FOR TABLES IN SCHEMA gpt_test_sch EXCEPT (TABLE gpt_test_sch.tbl_sch); CREATE PUBLICATION pub_normal FOR TABLE tbl_normal WHERE (id < 10); CREATE PUBLICATION pub_part_leaf FOR TABLE tbl_part1 WITH (publish_via_partition_root = false); CREATE PUBLICATION pub_part_parent FOR TABLE tbl_parent (id1, id2) WHERE (id1 = 10) WITH (publish_via_partition_root = true); @@ -1510,6 +1623,32 @@ SELECT * FROM test_gpt(ARRAY['pub_all_except_no_viaroot'], 'gpt_test_sch.tbl_sch SELECT * FROM test_gpt(ARRAY['pub_all_except_no_viaroot'], 'tbl_parent'); -- no result (excluded) SELECT * FROM test_gpt(ARRAY['pub_all_except_no_viaroot'], 'tbl_part1'); -- no result +-- test for EXCEPT clause with schema publication +SELECT * FROM test_gpt(ARRAY['pub_schema_except'], 'gpt_test_sch.tbl_sch'); -- no result (excluded) +SELECT * FROM test_gpt(ARRAY['pub_schema_except'], 'gpt_test_sch.tbl_sch2'); -- one row (included via schema) + +-- test for EXCEPT clause with schema publication, where the excluded root's +-- partition lives in a different schema that is separately, fully included +-- (no EXCEPT) in the same publication. The root's exclusion must cascade to +-- the partition regardless of the partition's own schema membership. +CREATE SCHEMA gpt_cross_sch1; +CREATE SCHEMA gpt_cross_sch2; +CREATE TABLE gpt_cross_sch1.croot (id int) PARTITION BY RANGE (id); +CREATE TABLE gpt_cross_sch2.cpart1 PARTITION OF gpt_cross_sch1.croot FOR VALUES FROM (1) TO (10); + +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION pub_cross_schema_except FOR TABLES IN SCHEMA gpt_cross_sch1 EXCEPT (TABLE gpt_cross_sch1.croot), TABLES IN SCHEMA gpt_cross_sch2; +RESET client_min_messages; + +SELECT * FROM test_gpt(ARRAY['pub_cross_schema_except'], 'gpt_cross_sch1.croot'); -- no result (excluded) +-- no result (excluded via cascading root exclusion, even though cpart1's own +-- schema gpt_cross_sch2 is separately, fully included with no EXCEPT) +SELECT * FROM test_gpt(ARRAY['pub_cross_schema_except'], 'gpt_cross_sch2.cpart1'); + +DROP PUBLICATION pub_cross_schema_except; +DROP TABLE gpt_cross_sch2.cpart1, gpt_cross_sch1.croot; +DROP SCHEMA gpt_cross_sch1, gpt_cross_sch2; + -- two rows with different row filter SELECT * FROM test_gpt(ARRAY['pub_all', 'pub_normal'], 'tbl_normal'); @@ -1538,6 +1677,7 @@ DROP PUBLICATION pub_all_no_viaroot; DROP PUBLICATION pub_all_except; DROP PUBLICATION pub_all_except_no_viaroot; DROP PUBLICATION pub_schema; +DROP PUBLICATION pub_schema_except; DROP PUBLICATION pub_normal; DROP PUBLICATION pub_part_leaf; DROP PUBLICATION pub_part_parent; diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl index 8c58d282eee..e9bca3554b1 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,206 @@ $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'); + +# ============================================ +# EXCEPT test cases for TABLES IN SCHEMA with a cross-schema partition +# ============================================ + +# A partition can live in a different schema than its partitioned root. If +# the root is excluded via EXCEPT under its own schema's clause, the +# exclusion must cascade to all its partitions, even when the partition's +# own schema is separately, fully included (no EXCEPT) in the same +# publication. +$node_publisher->safe_psql( + 'postgres', qq( + CREATE SCHEMA csch1; + CREATE SCHEMA csch2; + CREATE TABLE csch1.croot(a int) PARTITION BY RANGE(a); + CREATE TABLE csch2.cpart1 PARTITION OF csch1.croot FOR VALUES FROM (0) TO (100); + INSERT INTO csch1.croot VALUES (generate_series(1,5)); +)); + +$node_subscriber->safe_psql( + 'postgres', qq( + CREATE SCHEMA csch1; + CREATE SCHEMA csch2; + CREATE TABLE csch1.croot(a int); + CREATE TABLE csch2.cpart1(a int); +)); + +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION cross_sch_pub FOR TABLES IN SCHEMA csch1 EXCEPT (TABLE csch1.croot), TABLES IN SCHEMA csch2" +); +$node_subscriber->safe_psql('postgres', + "CREATE SUBSCRIPTION cross_sch_sub CONNECTION '$publisher_connstr' PUBLICATION cross_sch_pub" +); +$node_subscriber->wait_for_subscription_sync($node_publisher, 'cross_sch_sub'); + +# Baseline: the root itself, pre-populated with rows before the subscription +# was created, is excluded by its own schema's EXCEPT clause -- initial sync +# must not have copied it. +$result = + $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM csch1.croot"); +is($result, qq(0), + 'TABLES IN SCHEMA EXCEPT: cross-schema partition root is excluded by initial sync' +); + +# Regression case: the partition's own schema (csch2) is separately, fully +# included with no EXCEPT, but the root's exclusion must still cascade to it +# -- initial sync must not have copied the pre-existing rows routed into this +# partition via the root. +$result = + $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM csch2.cpart1"); +is($result, qq(0), + 'TABLES IN SCHEMA EXCEPT: cross-schema partition is excluded by initial sync via cascading root exclusion' +); + +# Insert distinct, identifiable data directly into the partition and verify +# it is not replicated either. +$node_publisher->safe_psql('postgres', + "INSERT INTO csch2.cpart1 VALUES (generate_series(1,5))"); +$node_publisher->wait_for_catchup('cross_sch_sub'); + +$result = + $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM csch1.croot"); +is($result, qq(0), + 'TABLES IN SCHEMA EXCEPT: cross-schema partition root remains excluded after DML' +); +$result = + $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM csch2.cpart1"); +is($result, qq(0), + 'TABLES IN SCHEMA EXCEPT: cross-schema partition remains excluded after DML via cascading root exclusion' +); + +$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION cross_sch_sub'); +$node_publisher->safe_psql('postgres', 'DROP PUBLICATION cross_sch_pub'); +$node_publisher->safe_psql('postgres', 'DROP SCHEMA csch1, csch2 CASCADE'); +$node_subscriber->safe_psql('postgres', 'DROP SCHEMA csch1, csch2 CASCADE'); + # ============================================ # Test when a subscription is subscribing to multiple publications # ============================================ @@ -254,6 +457,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) [application/octet-stream] v20-0002-Restrict-conflicting-EXCEPT-lists-in-multi-schem.patch (15.0K, ../../CABdArM72ufPkmOttET=q+kDaFrUMSUGYgZOh_OrBvuH-EPM1Bg@mail.gmail.com/3-v20-0002-Restrict-conflicting-EXCEPT-lists-in-multi-schem.patch) download | inline diff: From 34112dd23e62fae82fad9e97ba677cc2c5ab6de7 Mon Sep 17 00:00:00 2001 From: Nisha Moond <[email protected]> Date: Wed, 8 Jul 2026 15:07:22 +0530 Subject: [PATCH v20 2/5] Restrict conflicting EXCEPT lists in multi-schema publications Add ProcessSchemaExceptTables() to qualify each mention's EXCEPT entries, build a sorted signature per schema OID, and compare it against any prior mention of the same schema in the same statement, erroring on a mismatch. --- src/backend/commands/publicationcmds.c | 103 +++++++++++++++------- src/backend/parser/gram.y | 36 +------- src/test/regress/expected/publication.out | 38 ++++++++ src/test/regress/sql/publication.sql | 31 +++++++ 4 files changed, 145 insertions(+), 63 deletions(-) diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c index 9a922a6dcc3..0c3c905f7ea 100644 --- a/src/backend/commands/publicationcmds.c +++ b/src/backend/commands/publicationcmds.c @@ -73,6 +73,11 @@ static void PublicationAddSchemas(Oid pubid, List *schemas, bool if_not_exists, static void PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok); static char defGetGeneratedColsOption(DefElem *def); static void CheckExceptNotInTableList(List *except_rels, List *explicitrelids); +static void ProcessSchemaExceptTables(Oid schemaid, const char *schema_name, + List *except_tables, ParseState *pstate, + bool schema_repeated, + List **schemas_with_except, + List **except_pubtables); static void @@ -177,6 +182,57 @@ parse_publication_options(ParseState *pstate, } } +/* + * Qualify unqualified EXCEPT table names with schema_name, reject entries + * explicitly qualified with a different schema, and append the (now + * qualified) tables to *except_pubtables. + * + * Also rejects a repeated mention of the same schema in the same TABLES IN + * SCHEMA list if either mention specifies a non-empty EXCEPT list, without + * checking whether the two EXCEPT lists actually name the same tables. This + * mirrors OpenTableList()'s handling of repeated tables with column lists + * (e.g. "FOR TABLE t1(a), t1(a)" is rejected even though the lists match): + * two mentions of the same schema wouldn't have a well-defined single + * EXCEPT list to enforce, so it's rejected as conflicting or redundant + * rather than silently unioned or compared for equivalence. + * *schemas_with_except accumulates the OIDs of schemas already seen with a + * non-empty EXCEPT list in this statement. + */ +static void +ProcessSchemaExceptTables(Oid schemaid, const char *schema_name, + List *except_tables, ParseState *pstate, + bool schema_repeated, List **schemas_with_except, + List **except_pubtables) +{ + if (schema_repeated && + (except_tables != NIL || list_member_oid(*schemas_with_except, schemaid))) + ereport(ERROR, + errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("conflicting or redundant EXCEPT lists for schema \"%s\"", + schema_name)); + + if (except_tables != NIL) + *schemas_with_except = lappend_oid(*schemas_with_except, schemaid); + + foreach_ptr(PublicationObjSpec, eobj, except_tables) + { + const char *eobj_schemaname = eobj->pubtable->relation->schemaname; + const char *eobj_relname = eobj->pubtable->relation->relname; + + if (eobj_schemaname == NULL) + eobj->pubtable->relation->schemaname = pstrdup(schema_name); + else if (strcmp(eobj_schemaname, schema_name) != 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("table \"%s\" in EXCEPT clause does not belong to schema \"%s\"", + quote_qualified_identifier(eobj_schemaname, eobj_relname), + schema_name), + parser_errposition(pstate, eobj->location)); + + *except_pubtables = lappend(*except_pubtables, eobj->pubtable); + } +} + /* * Convert the PublicationObjSpecType list into schema oid list and * PublicationTable list. @@ -187,6 +243,7 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate, { ListCell *cell; PublicationObjSpec *pubobj; + List *schemas_with_except = NIL; if (!pubobjspec_list) return; @@ -194,7 +251,9 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate, foreach(cell, pubobjspec_list) { Oid schemaid; + char *schema_name; List *search_path; + bool schema_repeated; pubobj = (PublicationObjSpec *) lfirst(cell); @@ -210,9 +269,16 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate, break; case PUBLICATIONOBJ_TABLES_IN_SCHEMA: schemaid = get_namespace_oid(pubobj->name, false); + schema_name = pubobj->name; + schema_repeated = list_member_oid(*schemas, schemaid); /* Filter out duplicates if user specifies "sch1, sch1" */ *schemas = list_append_unique_oid(*schemas, schemaid); + + ProcessSchemaExceptTables(schemaid, schema_name, + pubobj->except_tables, pstate, + schema_repeated, &schemas_with_except, + except_pubtables); break; case PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA: search_path = fetch_search_path(false); @@ -223,41 +289,16 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate, schemaid = linitial_oid(search_path); list_free(search_path); + schema_name = get_namespace_name(schemaid); + schema_repeated = list_member_oid(*schemas, schemaid); /* Filter out duplicates if user specifies "sch1, sch1" */ *schemas = list_append_unique_oid(*schemas, schemaid); - /* - * Qualify unqualified EXCEPT table names with the resolved - * current schema and reject any explicitly cross-schema - * entries. This mirrors the parse-time handling done for - * TABLES_IN_SCHEMA in preprocess_pubobj_list(), deferred here - * because CURRENT_SCHEMA is not known until execution time. - */ - if (pubobj->except_tables != NIL) - { - char *cur_schema_name = get_namespace_name(schemaid); - - foreach_ptr(PublicationObjSpec, eobj, pubobj->except_tables) - { - const char *eobj_schemaname = - eobj->pubtable->relation->schemaname; - const char *eobj_relname = - eobj->pubtable->relation->relname; - - if (eobj_schemaname == NULL) - eobj->pubtable->relation->schemaname = cur_schema_name; - else if (strcmp(eobj_schemaname, cur_schema_name) != 0) - ereport(ERROR, - errcode(ERRCODE_INVALID_OBJECT_DEFINITION), - errmsg("table \"%s\" in EXCEPT clause does not belong to schema \"%s\"", - quote_qualified_identifier(eobj_schemaname, eobj_relname), - cur_schema_name)); - - *except_pubtables = lappend(*except_pubtables, - eobj->pubtable); - } - } + ProcessSchemaExceptTables(schemaid, schema_name, + pubobj->except_tables, pstate, + schema_repeated, &schemas_with_except, + except_pubtables); break; default: /* shouldn't happen */ diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 2a8f999d586..437416f7c96 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -20878,8 +20878,10 @@ 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. + * The except_tables attached to TABLES IN SCHEMA nodes are left in place here; + * ObjectsInPublicationToOids() qualifies their names, validates schema + * membership, and merges the qualified tables into its except_pubtables + * output list once the schema OID is known. */ static void preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner) @@ -20963,36 +20965,6 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner) errcode(ERRCODE_SYNTAX_ERROR), errmsg("invalid schema name"), parser_errposition(pubobj->location)); - - /* - * For TABLES_IN_SCHEMA, qualify unqualified EXCEPT table names - * with the parent schema and reject cross-schema entries at parse - * time, then flatten into the top-level list. - * - * For TABLES_IN_CUR_SCHEMA the schema name is not yet known, so - * skip both steps here; ObjectsInPublicationToOids() will - * qualify names and validate schema membership at execution time. - */ - if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLES_IN_SCHEMA) - { - foreach_ptr(PublicationObjSpec, eobj, pubobj->except_tables) - { - 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_INVALID_OBJECT_DEFINITION), - 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/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index 0729e546116..5fb7de2f07f 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -543,6 +543,17 @@ Except tables: "pub_test.testpub_tbl_s1" "public.testpub_tbl1" +-- fail: same schema repeated with same EXCEPT lists +CREATE PUBLICATION testpub_schema_except_conflict + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1), + pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); +ERROR: conflicting or redundant EXCEPT lists for schema "pub_test" +-- fail: same schema repeated with conflicting or no EXCEPT lists +CREATE PUBLICATION testpub_schema_except_conflict2 + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1), + pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2), + pub_test; +ERROR: conflicting or redundant EXCEPT lists for schema "pub_test" -- ALTER TABLE ... SET SCHEMA on a table excluded by a schema publication: -- the schema-scoped exclusion is no longer meaningful once the table moves -- out of its schema, so the exclusion is auto-removed. @@ -2111,6 +2122,8 @@ SET search_path = pub_test1; -- qualified name from wrong schema -> error CREATE PUBLICATION testpub_cursch_except FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE pub_test2.tbl1); ERROR: table "pub_test2.tbl1" in EXCEPT clause does not belong to schema "pub_test1" +LINE 1: ...FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE pub_test2.... + ^ -- unqualified name implicitly qualified with current schema (pub_test1.tbl) SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_cursch_except FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE tbl); @@ -2126,6 +2139,31 @@ Except tables: "pub_test1.tbl" DROP PUBLICATION testpub_cursch_except; +-- succeeds: CURRENT_SCHEMA and pub_test1 (same schema) +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_cursch_named_same + FOR TABLES IN SCHEMA CURRENT_SCHEMA, pub_test1; +RESET client_min_messages; +\dRp+ testpub_cursch_named_same + Publication testpub_cursch_named_same + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | +Tables from schemas: + "pub_test1" + +DROP PUBLICATION testpub_cursch_named_same; +-- fail: CURRENT_SCHEMA and pub_test1 (same schema) have +-- conflicting EXCEPT lists +CREATE PUBLICATION testpub_cursch_named_conflict + FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE tbl), + pub_test1 EXCEPT (TABLE tbl1); +ERROR: conflicting or redundant EXCEPT lists for schema "pub_test1" +-- fail: two CURRENT_SCHEMA mentions with conflicting EXCEPT lists +CREATE PUBLICATION testpub_cursch_cursch_conflict + FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE tbl), + CURRENT_SCHEMA EXCEPT (TABLE tbl1); +ERROR: conflicting or redundant EXCEPT lists for schema "pub_test1" RESET search_path; -- cleanup pub_test1 schema for invalidation tests ALTER PUBLICATION testpub2_forschema DROP TABLES IN SCHEMA pub_test1; diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 055b9e97dc1..3755032ad03 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -260,6 +260,17 @@ CREATE PUBLICATION testpub_schema_except_multi public EXCEPT (TABLE testpub_tbl1); \dRp+ testpub_schema_except_multi +-- fail: same schema repeated with same EXCEPT lists +CREATE PUBLICATION testpub_schema_except_conflict + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1), + pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); + +-- fail: same schema repeated with conflicting or no EXCEPT lists +CREATE PUBLICATION testpub_schema_except_conflict2 + FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1), + pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2), + pub_test; + -- ALTER TABLE ... SET SCHEMA on a table excluded by a schema publication: -- the schema-scoped exclusion is no longer meaningful once the table moves -- out of its schema, so the exclusion is auto-removed. @@ -1298,6 +1309,26 @@ CREATE PUBLICATION testpub_cursch_except FOR TABLES IN SCHEMA CURRENT_SCHEMA EXC RESET client_min_messages; \dRp+ testpub_cursch_except DROP PUBLICATION testpub_cursch_except; + +-- succeeds: CURRENT_SCHEMA and pub_test1 (same schema) +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_cursch_named_same + FOR TABLES IN SCHEMA CURRENT_SCHEMA, pub_test1; +RESET client_min_messages; +\dRp+ testpub_cursch_named_same +DROP PUBLICATION testpub_cursch_named_same; + +-- fail: CURRENT_SCHEMA and pub_test1 (same schema) have +-- conflicting EXCEPT lists +CREATE PUBLICATION testpub_cursch_named_conflict + FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE tbl), + pub_test1 EXCEPT (TABLE tbl1); + +-- fail: two CURRENT_SCHEMA mentions with conflicting EXCEPT lists +CREATE PUBLICATION testpub_cursch_cursch_conflict + FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT (TABLE tbl), + CURRENT_SCHEMA EXCEPT (TABLE tbl1); + RESET search_path; -- cleanup pub_test1 schema for invalidation tests -- 2.50.1 (Apple Git-155) [application/octet-stream] v20-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABL.patch (22.5K, ../../CABdArM72ufPkmOttET=q+kDaFrUMSUGYgZOh_OrBvuH-EPM1Bg@mail.gmail.com/4-v20-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABL.patch) download | inline diff: From 8d564bf6b279b80b05309903b886dd31d499f5c2 Mon Sep 17 00:00:00 2001 From: Nisha Moond <[email protected]> Date: Wed, 8 Jul 2026 17:09:06 +0530 Subject: [PATCH v20 3/5] 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. --- src/backend/catalog/pg_publication.c | 19 +++-- src/backend/commands/publicationcmds.c | 98 ++++++++++++++++++++++- src/bin/pg_dump/pg_dump.c | 30 ++++++- src/bin/pg_dump/t/002_pg_dump.pl | 24 ++++++ src/bin/psql/tab-complete.in.c | 17 ++++ src/test/regress/expected/publication.out | 66 ++++++++++++++- src/test/regress/sql/publication.sql | 50 +++++++++++- src/test/subscription/t/037_except.pl | 32 ++++++++ 8 files changed, 322 insertions(+), 14 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 0bdcc07c42d..21f679f8ac1 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -729,15 +729,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 0c3c905f7ea..e6485827b1c 100644 --- a/src/backend/commands/publicationcmds.c +++ b/src/backend/commands/publicationcmds.c @@ -71,6 +71,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_pubtables); +static void AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt, + HeapTuple tup, + List *except_pubtables, + List *schemaidlist); static char defGetGeneratedColsOption(DefElem *def); static void CheckExceptNotInTableList(List *except_rels, List *explicitrelids); static void ProcessSchemaExceptTables(Oid schemaid, const char *schema_name, @@ -1577,7 +1584,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup, */ static void AlterPublicationSchemas(AlterPublicationStmt *stmt, - HeapTuple tup, List *schemaidlist) + HeapTuple tup, List *schemaidlist, + List *except_pubtables) { Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup); @@ -1654,6 +1662,88 @@ 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_pubtables, 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_pubtables, + List *schemaidlist) +{ + Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup); + Oid pubid = pubform->oid; + + /* + * Nothing to do if no EXCEPT entries. + */ + if (!except_pubtables) + 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; + + /* + * Dropping a schema from a publication removes all its EXCEPT entries via + * cascade. The concept of "drop all schema tables from the publication + * EXCEPT these ones" is not supported. + */ + 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_pubtables); + + explicitrelids = GetIncludedPublicationRelations(pubid, + PUBLICATION_PART_ROOT); + + /* + * Validate that each excluded table is not also in the explicit table + * list (which would be contradictory). + */ + CheckExceptNotInTableList(rels, explicitrelids); + + PublicationAddTables(pubid, rels, false, stmt); + + CloseTableList(rels); + } } /* @@ -1863,10 +1953,12 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt) errmsg("publication \"%s\" does not exist", stmt->pubname)); - relations = list_concat(relations, except_pubtables); + if (stmt->for_all_tables) + relations = list_concat(relations, except_pubtables); + AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext, schemaidlist != NIL); - AlterPublicationSchemas(stmt, tup, schemaidlist); + AlterPublicationSchemas(stmt, tup, schemaidlist, except_pubtables); AlterPublicationAllFlags(stmt, rel, tup); } diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 4d660d14b4c..4877fd4546c 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -4973,6 +4973,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) @@ -4983,7 +4984,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 9258948b583..d18b3b0d7b9 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -3283,6 +3283,30 @@ 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 + .*? + \QALTER PUBLICATION pub11 ADD TABLES IN SCHEMA dump_test EXCEPT (TABLE ONLY test_table);\E + /xms, + 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 + .*? + \QALTER PUBLICATION pub12 ADD TABLES IN SCHEMA dump_test EXCEPT (TABLE ONLY test_table, TABLE ONLY test_second_table);\E + /xms, + 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 067168699f6..23009ca2f40 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2359,6 +2359,23 @@ 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", "CURRENT_SCHEMA", "EXCEPT", "(", "TABLE")) + COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_current_schema); + 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 5fb7de2f07f..13dd334d2ba 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -626,12 +626,76 @@ CREATE PUBLICATION testpub_except_ancestor_same_create TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); ERROR: partition "pub_test.testpub_part_s" cannot be both published and excluded DETAIL: Ancestor "pub_test.testpub_parted_s" is also being excluded by this same command. +--------------------------------------------- +-- EXCEPT tests for ALTER PUBLICATION +--------------------------------------------- +CREATE PUBLICATION testpub_alter_except; +-- fail: non-existing table in EXCEPT clause +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... + ^ +-- fail: exact same table given as both explicitly published and excluded +-- in a single ALTER ... ADD +ALTER PUBLICATION testpub_alter_except + ADD 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 be both published and excluded +-- fail: add explicit partition and EXCEPT ancestor, given in a +-- single ALTER ... ADD statement +ALTER PUBLICATION testpub_alter_except + ADD TABLE pub_test.testpub_part_s, + TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); +ERROR: partition "pub_test.testpub_part_s" cannot be both published and excluded +DETAIL: Ancestor "pub_test.testpub_parted_s" is also being excluded by this same command. +-- fail: partition still rejected from EXCEPT even when its own root is also +-- named in the same EXCEPT list, via ALTER ... ADD +ALTER PUBLICATION testpub_alter_except + ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_part_s, TABLE pub_test.testpub_parted_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: partition already explicitly published, then its root is EXCEPT-ed +-- via a later, separate ALTER ... ADD +ALTER PUBLICATION testpub_alter_except ADD TABLE pub_test.testpub_part_s; +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); +ERROR: partition "pub_test.testpub_part_s" cannot be both published and excluded +DETAIL: Ancestor "pub_test.testpub_parted_s" is also being excluded by this same command. +ALTER PUBLICATION testpub_alter_except DROP TABLE pub_test.testpub_part_s; +-- fail: root already explicitly published, then its partition is rejected +-- from EXCEPT via a later, separate ALTER ... ADD (partitions can never +-- appear in EXCEPT, independent of the root's state) +ALTER PUBLICATION testpub_alter_except ADD TABLE pub_test.testpub_parted_s; +ALTER PUBLICATION testpub_alter_except ADD 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. +ALTER PUBLICATION testpub_alter_except DROP TABLE pub_test.testpub_parted_s; +-- ADD: qualified and unqualified names; unqualified is implicitly qualified with the schema +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1, 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" + -- 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 3755032ad03..4801ccac112 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -321,12 +321,60 @@ CREATE PUBLICATION testpub_except_ancestor_same_create FOR TABLE pub_test.testpub_part_s, TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); +--------------------------------------------- +-- EXCEPT tests for ALTER PUBLICATION +--------------------------------------------- +CREATE PUBLICATION testpub_alter_except; + +-- fail: non-existing table in EXCEPT clause +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); + +-- fail: exact same table given as both explicitly published and excluded +-- in a single ALTER ... ADD +ALTER PUBLICATION testpub_alter_except + ADD TABLE pub_test.testpub_tbl_s1, + TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1); + +-- fail: add explicit partition and EXCEPT ancestor, given in a +-- single ALTER ... ADD statement +ALTER PUBLICATION testpub_alter_except + ADD TABLE pub_test.testpub_part_s, + TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); + +-- fail: partition still rejected from EXCEPT even when its own root is also +-- named in the same EXCEPT list, via ALTER ... ADD +ALTER PUBLICATION testpub_alter_except + ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_part_s, TABLE pub_test.testpub_parted_s); + +-- fail: partition already explicitly published, then its root is EXCEPT-ed +-- via a later, separate ALTER ... ADD +ALTER PUBLICATION testpub_alter_except ADD TABLE pub_test.testpub_part_s; +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); +ALTER PUBLICATION testpub_alter_except DROP TABLE pub_test.testpub_part_s; + +-- fail: root already explicitly published, then its partition is rejected +-- from EXCEPT via a later, separate ALTER ... ADD (partitions can never +-- appear in EXCEPT, independent of the root's state) +ALTER PUBLICATION testpub_alter_except ADD TABLE pub_test.testpub_parted_s; +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_part_s); +ALTER PUBLICATION testpub_alter_except DROP TABLE pub_test.testpub_parted_s; + +-- ADD: qualified and unqualified names; unqualified is implicitly qualified with the schema +ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2); +\dRp+ testpub_alter_except + -- 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 e9bca3554b1..27e336267db 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) [application/octet-stream] v20-0004-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABL.patch (27.5K, ../../CABdArM72ufPkmOttET=q+kDaFrUMSUGYgZOh_OrBvuH-EPM1Bg@mail.gmail.com/5-v20-0004-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABL.patch) download | inline diff: From 5695f655343b98fbe540cf9df6c85ce3e6047c24 Mon Sep 17 00:00:00 2001 From: Nisha Moond <[email protected]> Date: Thu, 18 Jun 2026 11:32:32 +0530 Subject: [PATCH v20 4/5] 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. --- src/backend/commands/publicationcmds.c | 181 +++++++++++++++++++--- src/bin/psql/tab-complete.in.c | 17 ++ src/test/regress/expected/publication.out | 109 +++++++++++++ src/test/regress/sql/publication.sql | 47 ++++++ src/test/subscription/t/037_except.pl | 85 ++++++++++ 5 files changed, 414 insertions(+), 25 deletions(-) diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c index e6485827b1c..b431ccf802a 100644 --- a/src/backend/commands/publicationcmds.c +++ b/src/backend/commands/publicationcmds.c @@ -67,7 +67,8 @@ static void CloseTableList(List *rels); static void LockSchemaList(List *schemalist); static void PublicationAddTables(Oid pubid, List *rels, bool if_not_exists, AlterPublicationStmt *stmt); -static void PublicationDropTables(Oid pubid, List *rels, bool missing_ok); +static void PublicationDropTables(Oid pubid, List *rels, bool missing_ok, + bool delete_excluded); static void PublicationAddSchemas(Oid pubid, List *schemas, bool if_not_exists, AlterPublicationStmt *stmt); static void PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok); @@ -1428,7 +1429,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup, PublicationAddTables(pubid, rels, false, stmt); } else if (stmt->action == AP_DropObjects) - PublicationDropTables(pubid, rels, false); + PublicationDropTables(pubid, rels, false, false); else /* AP_SetObjects */ { List *oldrelids = NIL; @@ -1563,7 +1564,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup, } /* And drop them. */ - PublicationDropTables(pubid, delrels, true); + PublicationDropTables(pubid, delrels, true, true); /* * Don't bother calculating the difference for adding, we'll catch and @@ -1665,8 +1666,8 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt, /* * Increment the command counter so that is_schema_publication() in - * GetExcludedPublicationTables() can see the just-inserted schema - * rows when AlterPublicationSchemaExceptTables runs next. + * GetExcludedPublicationTables() can see the just-inserted schema rows + * when AlterPublicationSchemaExceptTables runs next. */ if (stmt->action == AP_AddObjects || stmt->action == AP_SetObjects) CommandCounterIncrement(); @@ -1684,16 +1685,18 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt, */ static void AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt, - HeapTuple tup, List *except_pubtables, - List *schemaidlist) + HeapTuple tup, List *except_pubtables, + List *schemaidlist) { Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup); 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_pubtables) + if (!except_pubtables && stmt->action != AP_SetObjects) return; /* @@ -1714,16 +1717,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; @@ -1742,6 +1735,76 @@ 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_pubtables); + + /* 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 no excluded table is also already explicitly + * published (exact match), and that no already-published partition + * has its root among the tables being newly excluded here. + */ + CheckExceptNotInTableList(rels, explicitrelids); + + /* + * 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)) + { + ObjectAddressSet(obj, PublicationRelRelationId, proid); + performDeletion(&obj, DROP_CASCADE, 0); + } + } + + /* Add new EXCEPT entries, skipping any already present. */ + PublicationAddTables(pubid, rels, true, stmt); + CloseTableList(rels); } } @@ -2365,33 +2428,68 @@ PublicationAddTables(Oid pubid, List *rels, bool if_not_exists, /* * Remove listed tables from the publication. + * + * delete_excluded controls how EXCEPT (prexcept=true) rows are handled: + * true - delete them silently. Used by the SET path to clear stale + * EXCEPT rows that are being replaced by a new EXCEPT list. + * false - treat them as "not a member". Used by ALTER ... DROP TABLE: an + * EXCEPT entry is not something DROP TABLE is expected to remove. */ static void -PublicationDropTables(Oid pubid, List *rels, bool missing_ok) +PublicationDropTables(Oid pubid, List *rels, bool missing_ok, + bool delete_excluded) { ObjectAddress obj; ListCell *lc; - Oid prid; foreach(lc, rels) { PublicationRelInfo *pubrel = (PublicationRelInfo *) lfirst(lc); Relation rel = pubrel->relation; Oid relid = RelationGetRelid(rel); + HeapTuple tup; + Form_pg_publication_rel pubrelform; + Oid prid = InvalidOid; + bool is_except = false; if (pubrel->columns) ereport(ERROR, errcode(ERRCODE_SYNTAX_ERROR), errmsg("column list must not be specified in ALTER PUBLICATION ... DROP")); - prid = GetSysCacheOid2(PUBLICATIONRELMAP, Anum_pg_publication_rel_oid, - ObjectIdGetDatum(relid), - ObjectIdGetDatum(pubid)); - if (!OidIsValid(prid)) + tup = SearchSysCache2(PUBLICATIONRELMAP, + ObjectIdGetDatum(relid), + ObjectIdGetDatum(pubid)); + if (HeapTupleIsValid(tup)) + { + pubrelform = (Form_pg_publication_rel) GETSTRUCT(tup); + is_except = pubrelform->prexcept; + prid = pubrelform->oid; + ReleaseSysCache(tup); + } + + /* + * DROP TABLE operates only on regular member rows. A missing row + * and an EXCEPT row both mean the table isn't currently published, + * but we distinguish them so the EXCEPT case can carry a hint + * pointing the user at the EXCEPT list. The SET-cleanup caller + * (delete_excluded = true) intends to remove EXCEPT rows and so + * bypasses the EXCEPT half of this check. + */ + if (!OidIsValid(prid) || (is_except && !delete_excluded)) { if (missing_ok) continue; + if (is_except) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("cannot drop table \"%s\" from publication \"%s\"", + RelationGetQualifiedRelationName(rel), + get_publication_name(pubid, false)), + errdetail("The table is currently listed in the EXCEPT clause of the publication."), + errhint("Use ALTER PUBLICATION ... SET TABLES IN SCHEMA ... EXCEPT to drop the table from the EXCEPT list."))); + ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), errmsg("relation \"%s\" is not part of the publication", @@ -2447,6 +2545,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, @@ -2463,8 +2562,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)) + { + ObjectAddressSet(obj, PublicationRelRelationId, proid); + performDeletion(&obj, DROP_CASCADE, 0); + } + } + + list_free(except_relids); } } diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 23009ca2f40..dcdc3893112 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2376,6 +2376,23 @@ 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", "CURRENT_SCHEMA", "EXCEPT", "(", "TABLE")) + COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_current_schema); + 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 13dd334d2ba..d0037ed3da4 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -690,6 +690,115 @@ 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 be both published and excluded +-- fail: explicit partition + EXCEPT-ed root, given in a single SET statement +ALTER PUBLICATION testpub_alter_except SET TABLE pub_test.testpub_part_s, TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); +ERROR: partition "pub_test.testpub_part_s" cannot be both published and excluded +DETAIL: Ancestor "pub_test.testpub_parted_s" is also being excluded by this same command. +-- fail: 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... + ^ +-- 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: ALTER PUBLICATION ... DROP TABLE on an excluded table is rejected +-- with an EXCEPT-specific error. +ALTER PUBLICATION testpub_alter_except DROP TABLE pub_test.testpub_tbl_s1; +ERROR: cannot drop table "pub_test.testpub_tbl_s1" from publication "testpub_alter_except" +DETAIL: The table is currently listed in the EXCEPT clause of the publication. +HINT: Use ALTER PUBLICATION ... SET TABLES IN SCHEMA ... EXCEPT to drop the table from the EXCEPT list. +-- the EXCEPT entry is still present after the rejected DROP TABLE +\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: 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 +-- DROP TABLES IN SCHEMA 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 | +Tables from schemas: + "public" +Except tables: + "public.testpub_tbl1" + -- Cleanup RESET client_min_messages; DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2; diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 4801ccac112..93d827f2b2c 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -369,6 +369,53 @@ ALTER PUBLICATION testpub_alter_except DROP TABLE pub_test.testpub_parted_s; ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.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); + +-- fail: explicit partition + EXCEPT-ed root, given in a single SET statement +ALTER PUBLICATION testpub_alter_except SET TABLE pub_test.testpub_part_s, TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_parted_s); + +-- fail: 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); + +-- 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: ALTER PUBLICATION ... DROP TABLE on an excluded table is rejected +-- with an EXCEPT-specific error. +ALTER PUBLICATION testpub_alter_except DROP TABLE pub_test.testpub_tbl_s1; +-- the EXCEPT entry is still present after the rejected DROP TABLE +\dRp+ testpub_alter_except + +-- fail: EXCEPT is not allowed with DROP +ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2); + +-- DROP TABLES IN SCHEMA removes associated EXCEPT entries +ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test; +\dRp+ testpub_alter_except + -- Cleanup RESET client_min_messages; DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2; diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl index 27e336267db..251a87981b4 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 count(*) FROM sch1.tab_excluded WHERE a = 7"); +is($result, qq(1), + 'ALTER ... SET TABLES IN SCHEMA EXCEPT: newly included table is replicated' +); +$result = + $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM sch1.tab_published WHERE a = 7"); +is($result, qq(0), + '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 count(*) FROM sch1.tab_published WHERE a = 8"); +is($result, qq(1), + 'ALTER ... SET TABLES IN SCHEMA (no EXCEPT): tab_published replicated after except list cleared' +); +$result = + $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM sch1.tab_excluded WHERE a = 8"); +is($result, qq(1), + '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'); @@ -518,6 +573,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) [application/octet-stream] v20-0005-Documentation-Patch.patch (11.6K, ../../CABdArM72ufPkmOttET=q+kDaFrUMSUGYgZOh_OrBvuH-EPM1Bg@mail.gmail.com/6-v20-0005-Documentation-Patch.patch) download | inline diff: From a3ae2eb3ab64efa3ff2fca5e1b00fe4178a4205e Mon Sep 17 00:00:00 2001 From: Nisha Moond <[email protected]> Date: Thu, 18 Jun 2026 09:03:08 +0530 Subject: [PATCH v20 5/5] Documentation Patch --- doc/src/sgml/logical-replication.sgml | 3 +- doc/src/sgml/ref/alter_publication.sgml | 58 +++++++++++++++++++++++- doc/src/sgml/ref/create_publication.sgml | 42 +++++++++++++---- 3 files changed, 91 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 690598bff98..46c944beae8 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/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml index 52114a16a39..037ddfa9404 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> [, ... ] @@ -69,6 +73,9 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r The first two variants modify which tables/schemas are part of the publication. The <literal>ADD</literal> and <literal>DROP</literal> clauses will add and remove one or more tables/schemas from the publication. + The <literal>EXCEPT</literal> clause can be used with + <literal>ADD TABLES IN SCHEMA</literal> to exclude specific tables from a + schema-level publication. </para> <para> @@ -93,7 +100,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> @@ -198,6 +209,27 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r </listitem> </varlistentry> + <varlistentry> + <term><literal>EXCEPT</literal></term> + <listitem> + <para> + 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 + 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> + <para> + Dropping a table always removes it from the <literal>EXCEPT</literal> + clause. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term> <listitem> @@ -288,6 +320,28 @@ 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> + 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/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index 85cfcaddafa..26cb536e90b 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,13 +204,21 @@ 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>. For <literal>FOR TABLES IN + SCHEMA</literal>, the exclusion applies only to tables in the schema + associated with the <literal>EXCEPT</literal>. </para> <para> - Once a table is excluded, the exclusion applies to that table - regardless of its name or schema. Renaming the table or moving it to - another schema using <command>ALTER TABLE ... SET SCHEMA</command> does - not remove the exclusion. + Once a table is excluded under <literal>FOR ALL TABLES</literal>, the + exclusion applies to that table regardless of its name or schema. + Renaming the table or moving it to another schema using + <command>ALTER TABLE ... SET SCHEMA</command> does not remove the + exclusion. However, for <literal>FOR TABLES IN SCHEMA</literal>, because + the <literal>EXCEPT</literal> is schema-scoped, moving a schema-excluded + table to another schema does remove the exclusion; if the table is later + moved back, the exclusion is not restored and must be re-established + with <command>ALTER PUBLICATION</command>. </para> <para> For inherited tables, if <literal>ONLY</literal> is specified before the @@ -216,8 +230,9 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable> </para> <para> For partitioned tables, only the root partitioned table may be specified - in <literal>EXCEPT</literal>. Doing so excludes the root table and - all of its partitions from replication. The optional + in <literal>EXCEPT</literal>. Excluding the root table automatically + excludes all of its partitions from replication, including those in + schemas that are otherwise included in the publication. The optional <literal>ONLY</literal> and <literal>*</literal> has no effect for partitioned tables. </para> @@ -521,6 +536,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 -- 2.50.1 (Apple Git-155) ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Support EXCEPT for TABLES IN SCHEMA publications @ 2026-07-10 11:37 ` Nisha Moond <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Nisha Moond @ 2026-07-10 11:37 UTC (permalink / raw) To: shveta malik <[email protected]>; +Cc: Peter Smith <[email protected]>; Amit Kapila <[email protected]>; Zsolt Parragi <[email protected]>; [email protected] On Thu, Jul 9, 2026 at 2:53 PM shveta malik <[email protected]> wrote: > > > > > To reduce duplicate code, I moved the schema > > qualification and eligibility checks for both cases to execution time >> and removed the corresponding processing from gram.y. > > > > Since this changes a fair amount of code, I've kept it as a separate > > patch (v19-0002) to make review easier. Depending on the feedback, we > > can optimize the implementation or move parts of it back into gram.y > > before merging it into patch 0001. > > ~~~ > > > > Nisha, > > - * Also flattens except_tables from TABLES IN SCHEMA nodes into the list so > - * that ObjectsInPublicationToOids() sees them as top-level > EXCEPT_TABLE entries. > + * The except_tables attached to TABLES IN SCHEMA nodes are left in place; > + * ObjectsInPublicationToOids() qualifies names, validates schema membership, > + * and flattens them once the schema OID is known. > > Can you please explain what does 'flatten' mean here. > Each TABLES IN SCHEMA ... EXCEPT (...) entry stores its own nested list of EXCEPT tables. Flatten means collecting the PublicationTable from each nested list into a single top-level except_pubtables list, giving it the same structure as a FOR TABLE list that can be passed directly to OpenTableList(). I've reworded the comment. We can also remove it if it seems unnecessary here. > The flatten logic here is added by your patch001 only, it is not an > existing logic. To me (in my initial round of review), it seems okay > if we moved it to ObjectsInPublicationToOids(). The only thing is that > the one error (table in EXCEPT clause does not belong to schema) which > is possible to get at the parsing stage will now be emitted during > pub-creation stage. Yes, the logic was introduced in patch 0001 itself. I just highlighted it for the case you mentioned, where the error is deferred instead of being raised at parse time. > But since it will keep the entire logic of > except-list verification at one place and considering that > creat/alter-pub is not so frequent operation, I am fine with it. But > lets see if others have any comment here. > My thought was the same: to keep the logic in one place for now. Let's see if others have any opinions on it as well. > ~~ > > I had a quick look at the changes: > > + * Include the ONLY-ness of this entry in its signature. EXCEPT > + * (TABLE parent) excludes parent and its inheritance children, > + * while EXCEPT (TABLE ONLY parent) excludes only parent itself. > > I think this will unconditionally apply to paritioned tables as well > but for paritioned table, EXCEPT doc says that ONLY and * has no > effect, but here it shows the effect: > > Thsi gives error: > postgres=# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA public EXCEPT > (TABLE ONLY tab_root), TABLES IN SCHEMA public EXCEPT (TABLE > tab_root); > ERROR: 42P17: schema "public" specified with conflicting EXCEPT lists > LOCATION: ProcessSchemaExceptTables, publicationcmds.c:299 > > While this does not: > postgres=# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA public EXCEPT > (TABLE tab_root), TABLES IN SCHEMA public EXCEPT (TABLE tab_root); > CREATE PUBLICATION > > But I also checked column-list errors and found that even if columns > are exact sames, we get error: > > postgres=# CREATE PUBLICATION PUB4 FOR TABLE T1(i), T1(i); > ERROR: 42710: conflicting or redundant column lists for table "t1" > LOCATION: OpenTableList, publicationcmds.c:2312 > > While this works: > postgres=# CREATE PUBLICATION PUB4 FOR TABLE T1, T1; > CREATE PUBLICATION > > This makes me think that even introducing the smartness of identifying > ONLY in case of inherited tables and distinguishing it from > parititoned tables is not required. We can just change your error > message to 'conflicting or redundant EXCEPT lists for schmea "s1"' and > throw error in all cases where schmea with except list repeats without > actually comparing them. Does this sound reasonable? > Yes, that works for me as we have FOR TABLES example to follow. I've made the changes in v20. -- Thanks, Nisha ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-07-10 11:37 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-22 10:10 Re: jsonapi: scary new warnings with LTO enabled Daniel Gustafsson <[email protected]> 2025-04-23 00:01 ` Jacob Champion <[email protected]> 2025-04-23 09:35 ` Daniel Gustafsson <[email protected]> 2026-07-10 11:33 ` Re: Support EXCEPT for TABLES IN SCHEMA publications Nisha Moond <[email protected]> 2026-07-10 11:37 ` Re: Support EXCEPT for TABLES IN SCHEMA publications Nisha Moond <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox