public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nisha Moond <[email protected]>
To: shveta malik <[email protected]>
Cc: Peter Smith <[email protected]>
Cc: Zsolt Parragi <[email protected]>
Cc: [email protected]
Cc: Amit Kapila <[email protected]>
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
Date: Fri, 10 Jul 2026 17:03:43 +0530
Message-ID: <CABdArM72ufPkmOttET=q+kDaFrUMSUGYgZOh_OrBvuH-EPM1Bg@mail.gmail.com> (raw)
In-Reply-To: <CAJpy0uCY0FVmHCLprk0vhLiYwPHoKvv71os1aD1syzPzWpaDng@mail.gmail.com>
References: <CABdArM5sw4Q1ZU8HGdo4BSc1A_+8xtUNq17j6wcir=yMUy19Cg@mail.gmail.com>
<CAHut+PvnH8QHa035Skoh1e9jm_H08DO9fQ=F-NAMsEpYf0RZ2Q@mail.gmail.com>
<CAJpy0uDu0LcNXcZCP0cR_LHqo+sau33KwPFHemmGVYf_JTxRBQ@mail.gmail.com>
<CAA4eK1KbCWBmEXH-rhQjKgNwq=onZp8vRR-QkRhPpbKwL-kQdw@mail.gmail.com>
<CAHut+Pvj4=GWoJEd4EBdp4pi6KxXQ46ioW=PV+=UktiXr2gCvg@mail.gmail.com>
<CABdArM75F0A+DGP8AOt-_b_XREX40rvFid1jRjnr_+S5b51t8Q@mail.gmail.com>
<CAJpy0uDTshb243L5yEYWB3uO-JrwSoRqQDNovh03K2GZuuR3Pg@mail.gmail.com>
<CAJpy0uDy97ULmJUwPacAzc5u2seuPK6RXgCS1rnsW2MfR4eeSw@mail.gmail.com>
<CABdArM6oXXXSAxxXFktTTfBf4kyxJCvdNtTbUZtSwJ=CepN+Xw@mail.gmail.com>
<CAJpy0uBqM+fq7+g1ZRATuY16H10MFP9i25wfFCYCE5MGu+PE0Q@mail.gmail.com>
<CABdArM4uKaS1coCQj6rAwMmHqU_cCJyEWNic-PFF1_ZjDDM82Q@mail.gmail.com>
<CAHut+Pu5VNakf5JAhKM7T-P_q37eN1Qgv5nvZUe+8RAAT41y4g@mail.gmail.com>
<CABdArM6WTm2gP4pcjVdHT1Nx6zdLKTq7nLPUkzZOhprc95a6Rw@mail.gmail.com>
<CAHut+Ptthc1X-UA8-6zG-iFeCDuoNd+oJRBZ1eCnJ9RNOXjfBQ@mail.gmail.com>
<CABdArM79m7-CTf6KGGGU2QBydFtuonGgfxRSqk-vhwTsH8z1ow@mail.gmail.com>
<CAHut+PuiK_Pa=BkSgBxYzqf1PYh+mcUcUQCr8r1e69-y1r+hhw@mail.gmail.com>
<CABdArM7rH+3GekRgufEOwrJxUeQk=LB182CQwJD35e0oN7q8ZA@mail.gmail.com>
<CAHut+PtP1mbQT==xo=G-37dV9Lt3q7YO2eMEAqSbZuszy93LcQ@mail.gmail.com>
<CABdArM6XRpUR86a-daYMXFqhH-spJQiQAVfJ2+GFiAqeup2jyA@mail.gmail.com>
<CAHut+PuDB=doKUSf94cs8hOo2d5mOc+GxxPOf57xGhdE6e-Aog@mail.gmail.com>
<CABdArM76MZ4dYC87zCv67fVY9t5aFLd2nD5tNBxFFizESyBOSg@mail.gmail.com>
<CAN4CZFM9xXkw8FvHOTan+m4rASSoxbJTWXMbZ_-v2M-84w5Wzg@mail.gmail.com>
<CABdArM5wj0dtieSVMvzvJDh5fJ-GGwh3PxV+E_MbPEntCk4KRg@mail.gmail.com>
<CAN4CZFMaB1a89NZjRT9bWFZ8-XN02amye_fnYAg0NM_23DAwQw@mail.gmail.com>
<CABdArM5qHR=Zmyfqs6AuCn-Crj=_w3CXA-1K9rQ53_zismXCNQ@mail.gmail.com>
<CAHut+Pu0-A92MyRxFCLNQFYgAKGrKbYChG_p4ARogEfbAtMm8A@mail.gmail.com>
<CABdArM4fUnAZ10uyT61tNYu-WWHBiBOz75at9L90jBKLH5YR8g@mail.gmail.com>
<CAHut+Pv-GA1oGa6+nwn_5AVhBg8NuJThQVUzqhQPXJge49jnew@mail.gmail.com>
<CAA4eK1LMooRmK3_w_Zo-g3ftB7mDDkLitXjiEjgZY=0LTeZRoQ@mail.gmail.com>
<CABdArM5j8gX_xYDU6_aPEOuPAiCGtF8QyrpHeqZXQDsQ_wpJKA@mail.gmail.com>
<CAHut+PvAzsQA7BPKFC2dKWAr_kpAdHhLAaGOJQmZ_7=0paZCZA@mail.gmail.com>
<CABdArM4peXa8W9AYw6OHWmBuP4kkN3e01TcrUq_arWQLacoiNw@mail.gmail.com>
<CAHut+PucwGJ4Tyr9kRF4zNstAM_GGheU+-55u3JrKW8epKGSEQ@mail.gmail.com>
<CABdArM48_Dm3cEcrzb0MRuaMoKhCtzbQE7mX7GyaoVOLc9fNXQ@mail.gmail.com>
<CAHut+PsN+_2Me8khvk4bQ1+U8B73YykHv7qnQ7V2qtucaf2c4g@mail.gmail.com>
<CABdArM6Vv9Z4Dyj3+Wvdcq_8AAL2PshoHObZhpi5aBRkRn1eQA@mail.gmail.com>
<CAJpy0uCBkS2MyRzz0WSxg6Ch6Vi9-vNLCWLmEtcu_WwHWP7oiw@mail.gmail.com>
<CABdArM6Ycpng+SYh733ZfZaFFDUkZmjgxh35+6utx03HOTvH2A@mail.gmail.com>
<CAJpy0uD=deQJxbEuug-fbhvG6K47w1k_dwkZvbhAaBbBAgUPeg@mail.gmail.com>
<CABdArM4PzvWHyqG-tdZpf9U+0_-23n8NXJKAPW+_NADOqU42xA@mail.gmail.com>
<CAJpy0uAs2a2R+P=7cPo3iW4SgMUQVMf-1WwmPgJ5ttZErgzZvw@mail.gmail.com>
<CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com>
<CAJpy0uAuL7SqowL7T6HCELuVUr5ubwoGsjV1SQQRCXGme5L=pA@mail.gmail.com>
<CAJpy0uA0w9EERLvE5z8FMy6RxdA26FjcjA3mHYcr1ydz0XnjGw@mail.gmail.com>
<CAA4eK1KT=6zT9dy8iF0320VFHZ4Gs9=zknuXA92Wuj4tsEU0ow@mail.gmail.com>
<CAJpy0uA_d-K-f94d-9Xw66Fqv7gdSGBYGzi9YcybtuEkYdQocQ@mail.gmail.com>
<CAJpy0uCY0FVmHCLprk0vhLiYwPHoKvv71os1aD1syzPzWpaDng@mail.gmail.com>
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)
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
In-Reply-To: <CABdArM72ufPkmOttET=q+kDaFrUMSUGYgZOh_OrBvuH-EPM1Bg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox