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: Amit Kapila <[email protected]>
Cc: Zsolt Parragi <[email protected]>
Cc: [email protected]
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
Date: Fri, 3 Jul 2026 22:15:03 +0530
Message-ID: <CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com> (raw)
In-Reply-To: <CAJpy0uAs2a2R+P=7cPo3iW4SgMUQVMf-1WwmPgJ5ttZErgzZvw@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>
On Thu, Jul 2, 2026 at 2:19 PM shveta malik <[email protected]> wrote:
>
> A few comments:
>
> 1)
> postgres=# alter publication pub1 add table s2.t1;
> ERROR: table "s2.t1" cannot be added because it is excluded from
> publication "pub1"
>
> It will be good to add hint in above error.
>
> Suggestion:
> ERROR: cannot add table "s2.t1" to publication "pub1"
> DETAIL: Table "s2.t1" is currently in EXCEPT list
> HINT: Change EXCEPT list using ALTER PUBLICATION ..SET TABLES IN
> SCHEMA .. EXCEPT
>
> Please rephrase above as needed. Look earlier such messages/hints
> already added for ALL TABLES.
>
Updated the error as suggested, with a slight change in DETAIL message.
> From the user's perspective, it seems slightly odd that if he has
> excluded a table from publication, he can not add it back using 'add
> table'. Was this point discussed earlier?
>
This hasn't been discussed yet.
I kept the current behavior, as IMO, it is hard to tell whether the
user is trying to re-include a table or whether the ADD is unintended
and should be blocked. To avoid ambiguity, I think EXCEPT entries
should be modified only through the EXCEPT (...) clause itself, such
as ALTER PUBLICATION ... ADD/SET ... EXCEPT(TABLE ...), or by omitting
EXCEPT in the SET case.
This would also keep the behavior of ADD TABLE and DROP TABLE aligned,
as both would operate only on the include list without touching the
EXCEPT list.
I think this also aligns with FOR ALL TABLES, where ADD TABLE is
disallowed, so the EXCEPT list can only be modified through SET ..
EXCEPT (...).
Thoughts?
> 2)
> Currently:
> postgres=# alter publication pub1 drop table s2.t1;
> ERROR: relation "t1" is not part of the publication
>
> s2.t1 is EXCEPT list here.
>
> Should we have below error for this case rather than above generic
> error, it will be more intuitive for user. Plus both add and drop
> table commands will have similar error-style for EXCEPT list.
> Thoughts?
>
> ERROR: cannot drop table "s2.t1" from publication "pub1"
> DETAIL: Table "s2.t1" is currently in EXCEPT list
> HINT: Change EXCEPT list using ALTER PUBLICATION ..SET TABLES IN
> SCHEMA .. EXCEPT
>
Makes sense. Fixed.
> 3)
> Please see below test:
>
> --Setting Except list for pub1 which has all tables of s2 included:
> alter publication pub1 set tables in schema s2 EXCEPT ( TABLE t1, t2);
>
> --pg_publication_rel entries:
> pubname | schema_name | table_name | prexcept
> --------+-------------+------------+---------
> pub1 | s2 | t1 | t
> pub1 | s2 | t2 | t
>
>
> --Then added a partition:
> alter publication pub1 add table s2.tab_part_1_p2;
>
> --pg_publication_rel entries:
> pubname | schema_name | table_name | prexcept
> --------+-------------+---------------+---------
> pub1 | s2 | t1 | t
> pub1 | s2 | t2 | t
> pub1 | s2 | tab_part_1_p2 | f
>
>
> --Then changed pub1 to exclude tab_root (parent of tab_part_1_p2) as well:
>
> alter publication pub1 set tables in schema s2 EXCEPT ( TABLE t1, t2, tab_root);
>
> --pg_publication_rel entries:
> pubname | schema_name | table_name | prexcept
> --------+-------------+------------+---------
> pub1 | s2 | t1 | t
> pub1 | s2 | t2 | t
> pub1 | s2 | tab_root | t
>
> Note theoutput above, tab_part_1_p2 is removed internally as part of
> earlier command since its parent tab_root is now excluded.
>
> --Now add parition back, it allowed to add it:
> alter publication pub1 add table s2.tab_part_1_p2;
>
> pg_publication_rel entries:
> pubname | schema_name | table_name | prexcept
> --------+-------------+---------------+---------
> pub1 | s2 | t1 | t
> pub1 | s2 | t2 | t
> pub1 | s2 | tab_root | t
> pub1 | s2 | tab_part_1_p2 | f
>
>
> FOR ALL TABLES publicaiton, if root is excluded, there is no way
> publication can have parition included independently. Same should hold
> true here. Thoughts?
Right, based on the discussion at [1], since we follow "all partitions
are excluded if the root is excluded", a partition child should not be
allowed to be added when its partition root is already excluded from
the publication.
Fixed. Such an operation will now raise an error:
postgres=# alter publication pub2 add table t_part_p1;
ERROR: cannot add table "public.t_part_p1" to publication "pub2"
DETAIL: Partition ancestor "public.t_part" of table
"public.t_part_p1" is currently listed in the EXCEPT clause of the
publication.
HINT: Change EXCEPT list using ALTER PUBLICATION ... SET TABLES IN
SCHEMA ... EXCEPT.
~~~
Attached are the updated patches v18.
[1] https://www.postgresql.org/message-id/CAA4eK1%2BNmQRjSHPLr0X8YBuC6joivFqgsY3_qJ5-RnuuwNGkRQ%40mail.g...
--
Thanks,
Nisha
Attachments:
[application/x-patch] v18-0001-Support-EXCEPT-clause-for-schema-level-publicati.patch (69.2K, ../CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com/2-v18-0001-Support-EXCEPT-clause-for-schema-level-publicati.patch)
download | inline diff:
From d0be6cb9e0b77728659f7b7f32507523294e5cba Mon Sep 17 00:00:00 2001
From: Nisha Moond <[email protected]>
Date: Tue, 16 Jun 2026 10:58:49 +0530
Subject: [PATCH v18 1/4] 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 | 244 ++++++++++++++++----
src/backend/commands/publicationcmds.c | 162 ++++++++++++-
src/backend/commands/tablecmds.c | 11 +
src/backend/parser/gram.y | 54 ++++-
src/backend/replication/pgoutput/pgoutput.c | 39 +++-
src/backend/utils/cache/relcache.c | 41 ++--
src/bin/psql/describe.c | 18 ++
src/bin/psql/tab-complete.in.c | 35 ++-
src/include/catalog/pg_publication.h | 3 +-
src/include/commands/publicationcmds.h | 2 +
src/include/nodes/parsenodes.h | 2 +
src/test/regress/expected/publication.out | 180 ++++++++++++++-
src/test/regress/sql/publication.sql | 107 ++++++++-
src/test/subscription/t/037_except.pl | 133 ++++++++++-
14 files changed, 943 insertions(+), 88 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 1ec94c851b2..ce0f6d5b8c1 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;
@@ -110,6 +111,48 @@ check_publication_add_relation(PublicationRelInfo *pri)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(errormsg, relname),
errdetail("This operation is not supported for unlogged tables.")));
+
+ /*
+ * A partition cannot be independently included when any of its partition
+ * ancestors 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. Individual partitions cannot appear in EXCEPT
+ * (rejected above), so this check only applies to non-EXCEPT adds.
+ */
+ if (!pri->except && targetrel->rd_rel->relispartition)
+ {
+ List *ancestors = get_partition_ancestors(relid);
+
+ foreach_oid(ancestor, ancestors)
+ {
+ HeapTuple atup;
+ bool ancestor_except;
+
+ atup = SearchSysCache2(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(ancestor),
+ ObjectIdGetDatum(pubid));
+ if (!HeapTupleIsValid(atup))
+ continue;
+
+ ancestor_except = ((Form_pg_publication_rel) GETSTRUCT(atup))->prexcept;
+ ReleaseSysCache(atup);
+
+ if (ancestor_except)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("cannot add table \"%s\" to publication \"%s\"",
+ RelationGetQualifiedRelationName(targetrel),
+ get_publication_name(pubid, false)),
+ errdetail("Partition ancestor \"%s\" of table \"%s\" is currently listed in the EXCEPT clause of the publication.",
+ quote_qualified_identifier(get_namespace_name(get_rel_namespace(ancestor)),
+ get_rel_name(ancestor)),
+ RelationGetQualifiedRelationName(targetrel)),
+ errhint("Change EXCEPT list using ALTER PUBLICATION ... SET TABLES IN SCHEMA ... EXCEPT.")));
+ }
+
+ list_free(ancestors);
+ }
}
/*
@@ -340,19 +383,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);
@@ -454,14 +498,27 @@ GetPubPartitionOptionRelations(List *result, PublicationPartOpt pub_partopt,
*
* Note that the list of ancestors should be ordered such that the topmost
* ancestor is at the end of the list.
+ *
+ * except_pubids is a list of publication OIDs whose schema membership
+ * should be ignored for the ancestor (because the ancestor is in their
+ * EXCEPT clause).
*/
Oid
-GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level)
+GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
+ int *ancestor_level, List *except_pubids)
{
ListCell *lc;
Oid topmost_relid = InvalidOid;
int level = 0;
+ /*
+ * except_pubids is computed once from the partition root and reused
+ * across the ancestor walk. Re-fetching exclusion information for each
+ * ancestor is unnecessary because only the root can have prexcept=true;
+ * partitions themselves cannot be added to EXCEPT.
+ */
+ bool check_schemas = !list_member_oid(except_pubids, puboid);
+
/*
* Find the "topmost" ancestor that is in this publication.
*/
@@ -480,7 +537,7 @@ GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level
if (ancestor_level)
*ancestor_level = level;
}
- else
+ else if (check_schemas)
{
aschemaPubids = GetSchemaPublications(get_rel_namespace(ancestor));
if (list_member_oid(aschemaPubids, puboid))
@@ -555,21 +612,36 @@ 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("Table \"%s\" is currently listed in the EXCEPT clause of the publication.",
+ RelationGetQualifiedRelationName(targetrel)),
+ 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 +1064,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 +1132,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 +1158,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 +1180,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 +1315,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 +1453,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 +1510,62 @@ 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)
+ {
+ Oid ancestor_oid;
+
+ ancestor_oid = GetTopMostAncestorInPublication(pub->oid, ancestors, NULL, NIL);
+ if (OidIsValid(ancestor_oid))
+ {
+ /*
+ * The ancestor was found in the publication (via explicit
+ * membership or schema membership), but it may be excluded. Check
+ * for a prexcept row before concluding the partition is
+ * published.
+ */
+ tup = SearchSysCache2(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(ancestor_oid),
+ ObjectIdGetDatum(pub->oid));
+ if (HeapTupleIsValid(tup))
+ {
+ bool is_except;
+
+ is_except = ((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept;
+ ReleaseSysCache(tup);
+ if (is_except)
+ return false;
+ }
+ return !pub->pubviaroot;
+ }
+ }
/*
* 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..45a8a5c3f71 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -71,6 +71,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 +182,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 +201,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 +225,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 */
@@ -305,7 +338,7 @@ pub_rf_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
if (pubviaroot && relation->rd_rel->relispartition)
{
publish_as_relid
- = GetTopMostAncestorInPublication(pubid, ancestors, NULL);
+ = GetTopMostAncestorInPublication(pubid, ancestors, NULL, NIL);
if (!OidIsValid(publish_as_relid))
publish_as_relid = relid;
@@ -389,7 +422,7 @@ pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
*/
if (pubviaroot && relation->rd_rel->relispartition)
{
- publish_as_relid = GetTopMostAncestorInPublication(pubid, ancestors, NULL);
+ publish_as_relid = GetTopMostAncestorInPublication(pubid, ancestors, NULL, NIL);
if (!OidIsValid(publish_as_relid))
publish_as_relid = relid;
@@ -770,6 +803,23 @@ 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.
+ */
+static void
+CheckExceptNotInTableList(List *except_rels, List *explicitrelids)
+{
+ foreach_ptr(PublicationRelInfo, pri, except_rels)
+ {
+ if (list_member_oid(explicitrelids, RelationGetRelid(pri->relation)))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("table \"%s\" cannot appear in both the table list and the EXCEPT clause",
+ RelationGetQualifiedRelationName(pri->relation)));
+ }
+}
/*
* Given a list of tables that are going to be added to a publication,
@@ -849,7 +899,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 +986,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 +1009,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 +1029,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 +1054,25 @@ 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). 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 +1767,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 +1795,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 +1913,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 95abaf4890c..a6afb262967 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"
@@ -19569,6 +19570,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 ff4e1388c55..6a7a1bc5f8d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -58,6 +58,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "parser/parser.h"
+#include "utils/builtins.h"
#include "utils/datetime.h"
#include "utils/xml.h"
@@ -11272,7 +11273,7 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec
* pub_obj is one of:
*
* TABLE table [, ...]
- * TABLES IN SCHEMA schema [, ...]
+ * TABLES IN SCHEMA schema [EXCEPT (TABLE table [, ...] )] [, ...]
*
*****************************************************************************/
@@ -11332,23 +11333,26 @@ PublicationObjSpec:
$$->pubtable->columns = $3;
$$->pubtable->whereClause = $4;
}
- | TABLES IN_P SCHEMA ColId
+ | TABLES IN_P SCHEMA ColId opt_pub_except_clause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_TABLES_IN_SCHEMA;
$$->name = $4;
+ $$->except_tables = $5;
$$->location = @4;
}
- | TABLES IN_P SCHEMA CURRENT_SCHEMA
+ | TABLES IN_P SCHEMA CURRENT_SCHEMA opt_pub_except_clause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA;
+ $$->except_tables = $5;
$$->location = @4;
}
- | ColId opt_column_list OptWhereClause
+ | ColId opt_column_list OptWhereClause opt_pub_except_clause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
+ $$->except_tables = $4;
/*
* If either a row filter or column list is specified, create
* a PublicationTable object.
@@ -11392,10 +11396,11 @@ PublicationObjSpec:
$$->pubtable->columns = $2;
$$->pubtable->whereClause = $3;
}
- | CURRENT_SCHEMA
+ | CURRENT_SCHEMA opt_pub_except_clause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
+ $$->except_tables = $2;
$$->location = @1;
}
;
@@ -20784,6 +20789,8 @@ preprocess_pub_all_objtype_list(List *all_objects_list, List **pubobjects,
/*
* Process pubobjspec_list to check for errors in any of the objects and
* convert PUBLICATIONOBJ_CONTINUATION into appropriate PublicationObjSpecType.
+ * Also flattens except_tables from TABLES IN SCHEMA nodes into the list so
+ * that ObjectsInPublicationToOids() sees them as top-level EXCEPT_TABLE entries.
*/
static void
preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
@@ -20812,6 +20819,13 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLE)
{
+ /* EXCEPT is not valid for table objects */
+ if (pubobj->except_tables != NIL)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("EXCEPT is not allowed for TABLE publication objects"),
+ parser_errposition(pubobj->location));
+
/* relation name or pubtable must be set for this type of object */
if (!pubobj->name && !pubobj->pubtable)
ereport(ERROR,
@@ -20860,6 +20874,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..b0983775a03 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -2097,6 +2097,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
* are absorbed while decoding WAL.
*/
List *schemaPubids = GetSchemaPublications(schemaId);
+ List *except_pubids;
ListCell *lc;
Oid publish_as_relid = relid;
int publish_ancestor_level = 0;
@@ -2104,6 +2105,28 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
char relkind = get_rel_relkind(relid);
List *rel_publications = NIL;
+ /*
+ * For the schema EXCEPT check, we must look up the top-most ancestor
+ * rather than the relation itself. check_publication_add_relation()
+ * prevents individual partitions from appearing in the EXCEPT clause,
+ * so only a root (non-partition) table can have prexcept = true.
+ * Using the partition's own OID would always return NIL and miss the
+ * exclusion.
+ */
+ Oid root_relid;
+
+ if (am_partition)
+ {
+ List *ancestors = get_partition_ancestors(relid);
+
+ root_relid = llast_oid(ancestors);
+ list_free(ancestors);
+ }
+ else
+ root_relid = relid;
+
+ except_pubids = GetRelationExcludedPublications(root_relid);
+
/* Reload publications if needed before use. */
if (!publications_valid)
{
@@ -2265,9 +2288,19 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
int level;
List *ancestors = get_partition_ancestors(relid);
+ /*
+ * Reach here for FOR TABLE, FOR TABLES IN SCHEMA, or
+ * mixed publications. except_pubids lets
+ * GetTopMostAncestorInPublication() skip schema-based
+ * ancestor matches when the partition root is excluded
+ * via this publication's EXCEPT clause. It has no effect
+ * for pure FOR TABLE publications, which cannot have
+ * prexcept rows.
+ */
ancestor = GetTopMostAncestorInPublication(pub->oid,
ancestors,
- &level);
+ &level,
+ except_pubids);
if (ancestor != InvalidOid)
{
@@ -2281,7 +2314,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
}
if (list_member_oid(pubids, pub->oid) ||
- list_member_oid(schemaPubids, pub->oid) ||
+ (list_member_oid(schemaPubids, pub->oid) &&
+ !list_member_oid(except_pubids, pub->oid)) ||
ancestor_published)
publish = true;
}
@@ -2360,6 +2394,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
list_free(pubids);
list_free(schemaPubids);
+ list_free(except_pubids);
list_free(rel_publications);
entry->replicate_valid = true;
diff --git a/src/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 e4bc2c93145..5fd4f86ce70 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/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 89b4bb14f62..53e3d7c6f3d 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -191,7 +191,8 @@ extern List *GetPubPartitionOptionRelations(List *result,
PublicationPartOpt pub_partopt,
Oid relid);
extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
- int *ancestor_level);
+ int *ancestor_level,
+ List *except_pubids);
extern bool is_publishable_relation(Relation rel);
extern bool is_schema_publication(Oid pubid);
diff --git a/src/include/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 e03556399ab..1f3697ccd23 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4489,6 +4489,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..b4104e8220a 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,139 @@ 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 appear in both the table list and the EXCEPT clause
+-- fail: nonexistent table in EXCEPT clause
+CREATE PUBLICATION testpub_except_norel
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table);
+ERROR: relation "pub_test.nonexistent_table" does not exist
+-- fail: partition cannot appear in EXCEPT clause; only root tables are allowed
+CREATE TABLE pub_test.testpub_parted_s (a int) PARTITION BY LIST (a);
+CREATE TABLE pub_test.testpub_part_s PARTITION OF pub_test.testpub_parted_s FOR VALUES IN (1);
+CREATE PUBLICATION testpub_except_partition
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_part_s);
+ERROR: cannot specify relation "pub_test.testpub_part_s" in the publication EXCEPT clause
+DETAIL: This operation is not supported for individual partitions.
+-- fail: TABLE keyword is required for the first entry in the EXCEPT clause
+CREATE PUBLICATION testpub_except_nokw
+ FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
+ERROR: syntax error at or near "testpub_nopk"
+LINE 2: FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
+ ^
+-- error: 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 table "pub_test.testpub_part_s" to publication "testpub_except_ancestor"
+DETAIL: Partition ancestor "pub_test.testpub_parted_s" of table "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;
+-- 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 +2091,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 +2463,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 +2474,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 +2626,18 @@ 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)
+
-- two rows with different row filter
SELECT * FROM test_gpt(ARRAY['pub_all', 'pub_normal'], 'tbl_normal');
pubname | relname | attrs | qual
@@ -2517,6 +2690,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 +2699,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..013d7249b27 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,90 @@ 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: 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: 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;
+
+-- 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 +1275,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 +1541,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 +1553,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 +1610,10 @@ 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)
+
-- two rows with different row filter
SELECT * FROM test_gpt(ARRAY['pub_all', 'pub_normal'], 'tbl_normal');
@@ -1538,6 +1642,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..18c7b2c1fca 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -24,14 +24,17 @@ my $result;
sub test_except_root_partition
{
- my ($pubviaroot) = @_;
+ my ($pubviaroot, $pubsql) = @_;
+ $pubsql //=
+ "CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT (TABLE root1)";
+ $pubsql .= " WITH (publish_via_partition_root = $pubviaroot)";
# If the root partitioned table is in the EXCEPT clause, all its
# partitions are excluded from publication, regardless of the
# publish_via_partition_root setting.
$node_publisher->safe_psql(
'postgres', qq(
- CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT (TABLE root1) WITH (publish_via_partition_root = $pubviaroot);
+ $pubsql;
INSERT INTO root1 VALUES (1), (101);
));
$node_subscriber->safe_psql('postgres',
@@ -223,6 +226,131 @@ $node_subscriber->safe_psql(
test_except_root_partition('false');
test_except_root_partition('true');
+# Same validation using TABLES IN SCHEMA instead of FOR ALL TABLES.
+my $schema_pub =
+ "CREATE PUBLICATION tap_pub_part FOR TABLES IN SCHEMA public EXCEPT (TABLE public.root1)";
+test_except_root_partition('false', $schema_pub);
+test_except_root_partition('true', $schema_pub);
+
+# ============================================
+# EXCEPT test cases for TABLES IN SCHEMA
+# ============================================
+
+# Create a dedicated schema with two tables: one to be published and one to be
+# excluded. Also create inherited tables to verify ONLY semantics.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE SCHEMA sch1;
+ CREATE TABLE sch1.tab_published AS SELECT generate_series(1,5) AS a;
+ CREATE TABLE sch1.tab_excluded AS SELECT generate_series(1,5) AS a;
+ CREATE TABLE sch1.parent (a int);
+ CREATE TABLE sch1.child (b int) INHERITS (sch1.parent);
+));
+
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ CREATE SCHEMA sch1;
+ CREATE TABLE sch1.tab_published (a int);
+ CREATE TABLE sch1.tab_excluded (a int);
+ CREATE TABLE sch1.parent (a int);
+ CREATE TABLE sch1.child (b int) INHERITS (sch1.parent);
+));
+
+# Basic test: initial sync respects EXCEPT.
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.tab_excluded)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_published");
+is($result, qq(5),
+ 'TABLES IN SCHEMA EXCEPT: initial sync copies included table');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_excluded");
+is($result, qq(0),
+ 'TABLES IN SCHEMA EXCEPT: initial sync skips excluded table');
+
+# DML: only the included table should be replicated.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ INSERT INTO sch1.tab_published VALUES (6);
+ INSERT INTO sch1.tab_excluded VALUES (6);
+));
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_published");
+is($result, qq(6),
+ 'TABLES IN SCHEMA EXCEPT: DML on included table is replicated');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_excluded");
+is($result, qq(0),
+ 'TABLES IN SCHEMA EXCEPT: DML on excluded table is not replicated');
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
+# Inherited tables: excluding the parent (without ONLY) also excludes the child.
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.parent)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.child VALUES (generate_series(1,5), generate_series(1,5))"
+);
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.child");
+is($result, qq(0),
+ 'TABLES IN SCHEMA EXCEPT: excluding parent (without ONLY) also excludes child'
+);
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
+# Test that EXCEPT (TABLE ONLY parent) excludes only the parent itself, not its
+# child. Truncate child first so rows from the previous test are not copied by
+# the initial table sync of the next subscription.
+$node_publisher->safe_psql('postgres', 'TRUNCATE sch1.child');
+$node_subscriber->safe_psql('postgres', 'TRUNCATE sch1.child');
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE ONLY sch1.parent)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.child VALUES (generate_series(1,5), generate_series(1,5))"
+);
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.child");
+is($result, qq(5),
+ 'TABLES IN SCHEMA EXCEPT: ONLY parent in EXCEPT does not exclude child');
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
+# Cleanup schema tables before the multi-publication section.
+$node_publisher->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
+$node_subscriber->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
+
# ============================================
# Test when a subscription is subscribing to multiple publications
# ============================================
@@ -254,6 +382,7 @@ $node_publisher->safe_psql(
DROP PUBLICATION tap_pub2;
TRUNCATE tab1;
));
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
$node_subscriber->safe_psql('postgres', qq(TRUNCATE tab1));
# OK when a table is excluded by pub1 EXCEPT clause, but it is included by pub2
--
2.50.1 (Apple Git-155)
[application/x-patch] v18-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABL.patch (18.6K, ../CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com/3-v18-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABL.patch)
download | inline diff:
From 0ca5916fc07bda872dc95c83aeb21d17cea0120d Mon Sep 17 00:00:00 2001
From: Nisha Moond <[email protected]>
Date: Fri, 3 Jul 2026 19:42:25 +0530
Subject: [PATCH v18 2/4] 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 | 32 +++++++-
src/test/regress/sql/publication.sql | 20 ++++-
src/test/subscription/t/037_except.pl | 32 ++++++++
8 files changed, 258 insertions(+), 14 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index ce0f6d5b8c1..ae51d783798 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -713,15 +713,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 45a8a5c3f71..542579dae86 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -70,6 +70,13 @@ static void PublicationDropTables(Oid pubid, List *rels, bool missing_ok);
static void PublicationAddSchemas(Oid pubid, List *schemas, bool if_not_exists,
AlterPublicationStmt *stmt);
static void PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok);
+static void AlterPublicationSchemas(AlterPublicationStmt *stmt,
+ HeapTuple tup, List *schemaidlist,
+ List *except_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);
@@ -1509,7 +1516,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);
@@ -1586,6 +1594,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);
+ }
}
/*
@@ -1795,10 +1885,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 f67daf85911..007a1eadcaa 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 5fd4f86ce70..1b9cc926d81 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 b4104e8220a..b17ea933b76 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -600,12 +600,42 @@ ERROR: cannot add table "pub_test.testpub_part_s" to publication "testpub_excep
DETAIL: Partition ancestor "pub_test.testpub_parted_s" of table "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;
+---------------------------------------------
+-- 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...
+ ^
+-- 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 013d7249b27..048609bbc96 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -297,12 +297,30 @@ CREATE PUBLICATION testpub_except_ancestor
ALTER PUBLICATION testpub_except_ancestor ADD TABLE pub_test.testpub_part_s;
DROP PUBLICATION testpub_except_ancestor;
+---------------------------------------------
+-- 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);
+
+-- 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 18c7b2c1fca..0ba6d6f8bb2 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -347,6 +347,38 @@ is($result, qq(5),
$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+# ============================================
+# ALTER PUBLICATION EXCEPT for TABLES IN SCHEMA
+# ============================================
+
+# Truncate subscriber tables to remove data accumulated from previous tests.
+$node_subscriber->safe_psql('postgres',
+ 'TRUNCATE sch1.tab_published, sch1.tab_excluded, sch1.parent, sch1.child');
+
+# ADD: add a schema with an excepted table; verify the except entry takes effect.
+$node_publisher->safe_psql('postgres', "CREATE PUBLICATION sch_pub");
+$node_publisher->safe_psql('postgres',
+ "ALTER PUBLICATION sch_pub ADD TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.tab_excluded)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_published");
+is($result, qq(6),
+ 'ALTER ... ADD TABLES IN SCHEMA EXCEPT: included table synced');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_excluded");
+is($result, qq(0),
+ 'ALTER ... ADD TABLES IN SCHEMA EXCEPT: excluded table not synced');
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
# Cleanup schema tables before the multi-publication section.
$node_publisher->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
$node_subscriber->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
--
2.50.1 (Apple Git-155)
[application/x-patch] v18-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABL.patch (26.9K, ../CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com/4-v18-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABL.patch)
download | inline diff:
From 73eef80617be353e601c92e54102434af0cbc924 Mon Sep 17 00:00:00 2001
From: Nisha Moond <[email protected]>
Date: Thu, 18 Jun 2026 11:32:32 +0530
Subject: [PATCH v18 3/4] 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 | 105 +++++++++++++
src/test/regress/sql/publication.sql | 44 ++++++
src/test/subscription/t/037_except.pl | 85 ++++++++++
5 files changed, 407 insertions(+), 25 deletions(-)
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 542579dae86..423dbc152e6 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -66,7 +66,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);
@@ -1360,7 +1361,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;
@@ -1495,7 +1496,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
@@ -1597,8 +1598,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();
@@ -1616,16 +1617,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;
/*
@@ -1646,16 +1649,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;
@@ -1674,6 +1667,75 @@ 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 each excluded table is not also in the explicit table
+ * list (which would be contradictory).
+ */
+ 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);
}
}
@@ -2297,33 +2359,69 @@ 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("Table \"%s\" is currently listed in the EXCEPT clause of the publication.",
+ RelationGetQualifiedRelationName(rel)),
+ errhint("Change EXCEPT list using ALTER PUBLICATION ... SET TABLES IN SCHEMA ... EXCEPT.")));
+
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("relation \"%s\" is not part of the publication",
@@ -2379,6 +2477,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,
@@ -2395,8 +2494,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 1b9cc926d81..7e769638acd 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 b17ea933b76..984159657f6 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -630,6 +630,111 @@ Except tables:
"pub_test.testpub_tbl_s1"
"pub_test.testpub_tbl_s2"
+-- SET: replace the except list (keep same schema, different except table)
+ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2);
+\dRp+ testpub_alter_except
+ Publication testpub_alter_except
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f | f | t | t | t | t | none | f |
+Tables from schemas:
+ "pub_test"
+Except tables:
+ "pub_test.testpub_tbl_s2"
+
+-- fail: table in EXCEPT clause also appears in the explicit table list
+ALTER PUBLICATION testpub_alter_except SET TABLE pub_test.testpub_tbl_s1, TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1);
+ERROR: table "pub_test.testpub_tbl_s1" cannot appear in both the table list and the EXCEPT clause
+-- error: except table's schema (public) not in the publication's schema list (pub_test)
+ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1);
+ERROR: table "public.testpub_tbl1" in EXCEPT clause does not belong to schema "pub_test"
+LINE 1: ...xcept SET TABLES IN SCHEMA pub_test EXCEPT (TABLE public.tes...
+ ^
+-- 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"
+
+-- error: ALTER PUBLICATION ... DROP TABLE on an excluded table is rejected
+-- with an EXCEPT-specific error; an EXCEPT entry is not a member.
+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: Table "pub_test.testpub_tbl_s1" is currently listed in the EXCEPT clause of the publication.
+HINT: Change EXCEPT list using ALTER PUBLICATION ... SET TABLES IN SCHEMA ... EXCEPT.
+-- 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"
+
+-- error: EXCEPT is not allowed with DROP
+ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2);
+ERROR: EXCEPT clause is not supported with DROP in ALTER PUBLICATION
+-- 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 048609bbc96..5c174a58df7 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -315,6 +315,50 @@ ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (tes
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);
+
+-- error: except table's schema (public) not in the publication's schema list (pub_test)
+ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1);
+
+-- 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
+
+-- error: ALTER PUBLICATION ... DROP TABLE on an excluded table is rejected
+-- with an EXCEPT-specific error; an EXCEPT entry is not a member.
+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
+
+-- error: EXCEPT is not allowed with DROP
+ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s2);
+
+-- 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 0ba6d6f8bb2..01eafb5b7c8 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');
@@ -443,6 +498,36 @@ $node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub1');
$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2');
+# OK when a table is excluded by a TABLES IN SCHEMA EXCEPT publication,
+# but is included by another publication.
+$node_publisher->safe_psql('postgres', 'TRUNCATE tab1');
+$node_subscriber->safe_psql('postgres', 'TRUNCATE tab1');
+
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub1 FOR TABLES IN SCHEMA public EXCEPT (TABLE public.tab1);
+ CREATE PUBLICATION tap_pub2 FOR TABLE tab1;
+ INSERT INTO tab1 VALUES(1);
+));
+$node_subscriber->psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub1, tap_pub2"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub');
+
+$node_publisher->safe_psql('postgres', qq(INSERT INTO tab1 VALUES(2)));
+$node_publisher->wait_for_catchup('tap_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM tab1 ORDER BY a");
+is( $result, qq(1
+2),
+ "TABLES IN SCHEMA EXCEPT: table excluded in schema pub but included by another pub is replicated"
+);
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub1');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2');
+
$node_publisher->stop('fast');
done_testing();
--
2.50.1 (Apple Git-155)
[application/x-patch] v18-0004-Documentation-Patch.patch (11.5K, ../CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com/5-v18-0004-Documentation-Patch.patch)
download | inline diff:
From 6fd3ddc0d36b722cb83313620531f3909eae140a Mon Sep 17 00:00:00 2001
From: Nisha Moond <[email protected]>
Date: Thu, 18 Jun 2026 09:03:08 +0530
Subject: [PATCH v18 4/4] Documentation Patch
---
doc/src/sgml/logical-replication.sgml | 3 +-
doc/src/sgml/ref/alter_publication.sgml | 58 +++++++++++++++++++++++-
doc/src/sgml/ref/create_publication.sgml | 40 ++++++++++++----
3 files changed, 90 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index f21239af6b8..c9918788c7f 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..582706e5a8e 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
@@ -217,7 +231,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<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
+ all of its partitions from replication, even any partition that lives
+ in a schema which is itself 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 (67+ 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: <CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@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