public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nisha Moond <nisha.moond412@gmail.com>
To: Peter Smith <smithpb2250@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
Date: Tue, 19 May 2026 16:44:09 +0530
Message-ID: <CABdArM79m7-CTf6KGGGU2QBydFtuonGgfxRSqk-vhwTsH8z1ow@mail.gmail.com> (raw)
In-Reply-To: <CAHut+Ptthc1X-UA8-6zG-iFeCDuoNd+oJRBZ1eCnJ9RNOXjfBQ@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>
On Fri, May 15, 2026 at 12:49 PM Peter Smith <smithpb2250@gmail.com> wrote:
>
> Hi Nisha.
>
> Some review comments for patch v5-0001.
>
Thanks Peter, for the review.
> ======
> src/backend/catalog/pg_publication.c
>
> publication_add_relation:
>
> 2.
> + HeapTuple existing;
>
> Not sure if this is the best name. How about "tup"?
>
Noticed that a "HeapTuple tup;" is already declared, so we can use the
existing one.
> ~~~
>
> 3.
> + bool is_except = existing_form->prexcept;
>
> This variable is used only once. Not sure if that vindicates having it.
>
the is_except value is being used after releasing the tuple and
closing the table since the next step errors out. But I have now
removed existing_form and directly extracted the value instead.
> ~~~
>
> 8.
> + list_free(schemaRels);
> + }
> + else
> + result = list_concat(result, schemaRels);
>
> Why is 'schemaRels' only being freed when there is an EXCEPT?
>
IIUC, In the EXCEPT case, relid is an Oid, so lfirst_oid() copies the
integer value from the cell, and lappend_oid() stores that value into
a new cell in result. That means result does not reference schemaRels
cells after the loop, so list_free(schemaRels) is safe.
In the else branch, list_concat() directly transfers schemaRels cells
into result. So freeing schemaRels there would corrupt the result.
> ======
> src/backend/parser/gram.y
>
> 14.
> - | ColId opt_column_list OptWhereClause
> + | ColId opt_column_list OptWhereClause opt_pub_except_clause
> {
> $$ = makeNode(PublicationObjSpec);
> $$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
> + $$->except_tables = $4;
>
> This seems suspicious. You cannot have an EXCEPT clause when there is
> a column list or a WHERE clause, so what is this scenario? Maybe the
> "$$->except_tables = $4;" needs to be moved to the 'else' block?
>
This handles cases where multi-schema continuation is used along with
an EXCEPT clause, e.g.:
create publication pub1 for tables in schema public, s1 except (table t1);
-- without the above, the EXCEPT clause would be silently ignored.
Now, I agree that the above case can also be handled by moving
"$$->except_tables = $4;" into the else branch. But then EXCEPT would
again get silently ignored for table continuations with a column-list
or where clause, e.g.,:
postgres=# create publication pub2 for table t1, t2 (c1) except (table t1);
CREATE PUBLICATION
The idea here is to collect the EXCEPT list whenever it is specified
for such continuation cases, and then explicitly raise an error in
preprocess_pubobj_list() if a table publication object contains an
EXCEPT list.
> ~~~
>
> 16.
> + if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLES_IN_SCHEMA)
> + {
> + if (eobj->pubtable->relation->schemaname == NULL)
> + eobj->pubtable->relation->schemaname = pubobj->name;
> + else if (strcmp(eobj->pubtable->relation->schemaname,
> + pubobj->name) != 0)
> + ereport(ERROR,
> + errcode(ERRCODE_SYNTAX_ERROR),
> + errmsg("table \"%s.%s\" in EXCEPT clause does not belong to schema \"%s\"",
> + eobj->pubtable->relation->schemaname,
> + eobj->pubtable->relation->relname,
> + pubobj->name),
> + parser_errposition(eobj->location));
>
> 16a.
> Introducing some more variables (like 'eobj_schemaname' and
> 'eobj_relname') would simplify this code quite a bit.
>
Done.
> ~
>
> 16b.
> Should make use of the recently committed function that gets
> fully-qualified rel names so you can use "%s" instead of "%s.%s".
>
We cannot use RelationGetQualifiedRelationName() in the grammar, so I
used quote_qualified_identifier() instead.
~~~~
Addressed all other comments as suggested. Please find the updated v6
patches attached.
Patch-0001: updated as per the above comments.
Patch-0002 and Patch-0003: adjusted for the Patch-0001 changes and
some code simplification in tab-complete part.
--
Thanks,
Nisha
Attachments:
[application/octet-stream] v6-0001-Support-EXCEPT-clause-for-schema-level-publicatio.patch (42.4K, 2-v6-0001-Support-EXCEPT-clause-for-schema-level-publicatio.patch)
download | inline diff:
From 4248729b91c72d68c3deea6b540cefa04b2501c1 Mon Sep 17 00:00:00 2001
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Mon, 4 May 2026 12:49:27 +0530
Subject: [PATCH v6 1/3] Support EXCEPT clause for schema-level publications
Extend table exclusion support in publications to allow specific
tables to be excluded from schema-level publications using an
EXCEPT clause in CREATE PUBLICATION.
Supported syntax:
CREATE PUBLICATION <pub> FOR TABLES IN SCHEMA s EXCEPT (TABLE t1,...);
---
doc/src/sgml/logical-replication.sgml | 3 +-
doc/src/sgml/ref/create_publication.sgml | 22 +++-
src/backend/catalog/pg_publication.c | 86 +++++++++++--
src/backend/commands/publicationcmds.c | 47 ++++++-
src/backend/parser/gram.y | 52 +++++++-
src/backend/replication/pgoutput/pgoutput.c | 21 +++-
src/bin/psql/describe.c | 18 +++
src/bin/psql/tab-complete.in.c | 26 +++-
src/include/catalog/pg_publication.h | 3 +-
src/include/nodes/parsenodes.h | 2 +
src/test/regress/expected/publication.out | 100 ++++++++++++++-
src/test/regress/sql/publication.sql | 65 +++++++++-
src/test/subscription/t/037_except.pl | 133 +++++++++++++++++++-
13 files changed, 545 insertions(+), 33 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 9e7868487de..1433d2660fe 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -117,7 +117,8 @@
or <literal>FOR ALL SEQUENCES</literal>. Unlike tables, sequences can be
synchronized at any time. For more information, see
<xref linkend="logical-replication-sequences"/>. When a publication is
- created with <literal>FOR ALL TABLES</literal>, a table or set of tables can
+ created with <literal>FOR ALL TABLES</literal> or
+ <literal>FOR TABLES IN SCHEMA</literal>, a table or set of tables can
be explicitly excluded from publication using the
<link linkend="sql-createpublication-params-for-except-table"><literal>EXCEPT</literal></link>
clause.
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index f82d640e6ca..7fa0bd11f7b 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -28,7 +28,7 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ]
- TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
+ TABLES IN SCHEMA <replaceable class="parameter">tables_in_schema</replaceable> [, ... ]
<phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase>
@@ -39,6 +39,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<replaceable class="parameter">table_object</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ]
+<phrase>and <replaceable class="parameter">tables_in_schema</replaceable> is:</phrase>
+
+ { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [ EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ]
+
<phrase>and <replaceable class="parameter">except_table_object</replaceable> is:</phrase>
TABLE <replaceable class="parameter">table_object</replaceable> [, ... ]
@@ -142,6 +146,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<para>
Marks the publication as one that replicates changes for all tables in
the specified list of schemas, including tables created in the future.
+ Tables listed in the <literal>EXCEPT</literal> clause for a given schema
+ are excluded from the publication.
</para>
<para>
@@ -173,7 +179,7 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<para>
Marks the publication as one that replicates changes for all tables in
the database, including tables created in the future. Tables listed in
- <literal>EXCEPT</literal> clause are excluded from the publication.
+ the <literal>EXCEPT</literal> clause are excluded from the publication.
</para>
</listitem>
</varlistentry>
@@ -198,7 +204,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<listitem>
<para>
This clause specifies a list of tables to be excluded from the
- publication.
+ publication. It can be used with <literal>FOR ALL TABLES</literal> or
+ <literal>FOR TABLES IN SCHEMA</literal>.
</para>
<para>
For inherited tables, if <literal>ONLY</literal> is specified before the
@@ -515,6 +522,15 @@ CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES I
CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;
</programlisting></para>
+ <para>
+ Create a publication that publishes all changes for all the tables present in
+ the schema <structname>sales</structname>, except
+ <structname>internal</structname> and <structname>drafts</structname>:
+<programlisting>
+CREATE PUBLICATION sales_filtered FOR TABLES IN SCHEMA sales EXCEPT (TABLE internal, drafts);
+</programlisting>
+ </para>
+
<para>
Create a publication that publishes all changes for table <structname>users</structname>,
but replicates only columns <structname>user_id</structname> and
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 5c457d9aca8..6f945955901 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -446,7 +446,8 @@ GetPubPartitionOptionRelations(List *result, PublicationPartOpt pub_partopt,
* ancestor is at the end of the list.
*/
Oid
-GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level)
+GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
+ int *ancestor_level, List *except_pubids)
{
ListCell *lc;
Oid topmost_relid = InvalidOid;
@@ -473,7 +474,8 @@ GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level
else
{
aschemaPubids = GetSchemaPublications(get_rel_namespace(ancestor));
- if (list_member_oid(aschemaPubids, puboid))
+ if (list_member_oid(aschemaPubids, puboid) &&
+ !list_member_oid(except_pubids, puboid))
{
topmost_relid = ancestor;
@@ -545,18 +547,30 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
* duplicates, it's here just to provide nicer error message in common
* case. The real protection is the unique key on the catalog.
*/
- if (SearchSysCacheExists2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
- ObjectIdGetDatum(pubid)))
+ tup = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pubid));
+
+ if (HeapTupleIsValid(tup))
{
+ bool is_except = ((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept;
+
+ ReleaseSysCache(tup);
table_close(rel, RowExclusiveLock);
if (if_not_exists)
return InvalidObjectAddress;
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("relation \"%s\" is already member of publication \"%s\"",
- RelationGetRelationName(targetrel), pub->name)));
+ if (is_except)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("table \"%s\" cannot be added because it is excluded from publication \"%s\"",
+ RelationGetQualifiedRelationName(targetrel),
+ pub->name)));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("relation \"%s\" is already member of publication \"%s\"",
+ RelationGetRelationName(targetrel), pub->name)));
}
check_publication_add_relation(pri);
@@ -982,12 +996,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);
}
@@ -1232,22 +1247,67 @@ GetSchemaPublicationRelations(Oid schemaid, PublicationPartOpt pub_partopt)
/*
* Gets the list of all relations published by FOR TABLES IN SCHEMA
- * publication.
+ * publication, excluding any tables listed in the EXCEPT clause.
*/
List *
GetAllSchemaPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
{
List *result = NIL;
List *pubschemalist = GetPublicationSchemas(pubid);
+ List *exceptlist = NIL;
ListCell *cell;
+ /* get the list of tables excluded via EXCEPT TABLE for this publication */
+ if (pubschemalist != NIL)
+ exceptlist = 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 (exceptlist != 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(exceptlist, relid);
+
+ /*
+ * Also exclude any relation whose partition ancestor is in
+ * the EXCEPT list. This matters when pub_partopt is
+ * PUBLICATION_PART_ROOT: the except list holds only the root
+ * OID, but the schema scan may also return individual
+ * partition relations that live in the same schema.
+ */
+ if (!excluded && get_rel_relispartition(relid))
+ {
+ List *ancestors = get_partition_ancestors(relid);
+ ListCell *alc;
+
+ foreach(alc, ancestors)
+ {
+ if (list_member_oid(exceptlist, lfirst_oid(alc)))
+ {
+ excluded = true;
+ break;
+ }
+ }
+ list_free(ancestors);
+ }
+
+ if (!excluded)
+ result = lappend_oid(result, relid);
+ }
+ list_free(schemaRels);
+ }
+ else
+ result = list_concat(result, schemaRels);
}
return result;
@@ -1381,7 +1441,7 @@ is_table_publishable_in_publication(Oid relid, Publication *pub)
* the publication, it should be included (return true).
*/
if (relispartition &&
- OidIsValid(GetTopMostAncestorInPublication(pub->oid, ancestors, NULL)))
+ OidIsValid(GetTopMostAncestorInPublication(pub->oid, ancestors, NULL, NIL)))
return !pub->pubviaroot;
/*
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 440adb356ad..95186ca7377 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -305,7 +305,7 @@ pub_rf_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
if (pubviaroot && relation->rd_rel->relispartition)
{
publish_as_relid
- = GetTopMostAncestorInPublication(pubid, ancestors, NULL);
+ = GetTopMostAncestorInPublication(pubid, ancestors, NULL, NIL);
if (!OidIsValid(publish_as_relid))
publish_as_relid = relid;
@@ -389,7 +389,7 @@ pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
*/
if (pubviaroot && relation->rd_rel->relispartition)
{
- publish_as_relid = GetTopMostAncestorInPublication(pubid, ancestors, NULL);
+ publish_as_relid = GetTopMostAncestorInPublication(pubid, ancestors, NULL, NIL);
if (!OidIsValid(publish_as_relid))
publish_as_relid = relid;
@@ -959,6 +959,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
}
else if (!stmt->for_all_sequences)
{
+ List *explicitrelids = NIL;
+
/* FOR TABLES IN SCHEMA requires superuser */
if (schemaidlist != NIL && !superuser())
ereport(ERROR,
@@ -977,6 +979,19 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
schemaidlist != NIL,
publish_via_partition_root);
+ /*
+ * Collect explicit table OIDs now, before we close the relation
+ * list, so that except-table validation below can check for
+ * contradictions without relying on a catalog scan that might not
+ * yet see the just-inserted rows.
+ */
+ if (exceptrelations != NIL)
+ {
+ foreach_ptr(PublicationRelInfo, pri, rels)
+ explicitrelids = lappend_oid(explicitrelids,
+ RelationGetRelid(pri->relation));
+ }
+
PublicationAddTables(puboid, rels, true, NULL);
CloseTableList(rels);
}
@@ -989,6 +1004,34 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
*/
LockSchemaList(schemaidlist);
PublicationAddSchemas(puboid, schemaidlist, true, NULL);
+
+ if (exceptrelations != NIL)
+ {
+ List *exceptrels;
+
+ exceptrels = OpenTableList(exceptrelations);
+
+ /*
+ * Validate that each excluded table is not also in the
+ * explicit table list (which would be contradictory). Use the
+ * in-memory explicitrelids collected above rather than
+ * re-reading the catalog, which may not yet see the
+ * just-inserted rows.
+ */
+ foreach_ptr(PublicationRelInfo, pri, exceptrels)
+ {
+ Oid except_relid = RelationGetRelid(pri->relation);
+
+ if (list_member_oid(explicitrelids, except_relid))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("table \"%s\" cannot appear in both the table list and the EXCEPT clause",
+ RelationGetQualifiedRelationName(pri->relation)));
+ }
+
+ PublicationAddTables(puboid, exceptrels, true, NULL);
+ CloseTableList(exceptrels);
+ }
}
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..4514ef7f9c2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -58,6 +58,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "parser/parser.h"
+#include "utils/builtins.h"
#include "utils/datetime.h"
#include "utils/xml.h"
@@ -11272,7 +11273,7 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec
* pub_obj is one of:
*
* TABLE table [, ...]
- * TABLES IN SCHEMA schema [, ...]
+ * TABLES IN SCHEMA schema [EXCEPT (TABLE table [, ...] )] [, ...]
*
*****************************************************************************/
@@ -11332,23 +11333,26 @@ PublicationObjSpec:
$$->pubtable->columns = $3;
$$->pubtable->whereClause = $4;
}
- | TABLES IN_P SCHEMA ColId
+ | TABLES IN_P SCHEMA ColId opt_pub_except_clause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_TABLES_IN_SCHEMA;
$$->name = $4;
+ $$->except_tables = $5;
$$->location = @4;
}
- | TABLES IN_P SCHEMA CURRENT_SCHEMA
+ | TABLES IN_P SCHEMA CURRENT_SCHEMA opt_pub_except_clause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA;
+ $$->except_tables = $5;
$$->location = @4;
}
- | ColId opt_column_list OptWhereClause
+ | ColId opt_column_list OptWhereClause opt_pub_except_clause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
+ $$->except_tables = $4;
/*
* If either a row filter or column list is specified, create
* a PublicationTable object.
@@ -11392,10 +11396,11 @@ PublicationObjSpec:
$$->pubtable->columns = $2;
$$->pubtable->whereClause = $3;
}
- | CURRENT_SCHEMA
+ | CURRENT_SCHEMA opt_pub_except_clause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
+ $$->except_tables = $2;
$$->location = @1;
}
;
@@ -20784,6 +20789,8 @@ preprocess_pub_all_objtype_list(List *all_objects_list, List **pubobjects,
/*
* Process pubobjspec_list to check for errors in any of the objects and
* convert PUBLICATIONOBJ_CONTINUATION into appropriate PublicationObjSpecType.
+ * Also flattens except_tables from TABLES IN SCHEMA nodes into the list so
+ * that ObjectsInPublicationToOids() sees them as top-level EXCEPT_TABLE entries.
*/
static void
preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
@@ -20812,6 +20819,13 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLE)
{
+ /* EXCEPT is not valid for table objects */
+ if (pubobj->except_tables != NIL)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("EXCEPT is not allowed for TABLE publication objects"),
+ parser_errposition(pubobj->location));
+
/* relation name or pubtable must be set for this type of object */
if (!pubobj->name && !pubobj->pubtable)
ereport(ERROR,
@@ -20860,6 +20874,34 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
errcode(ERRCODE_SYNTAX_ERROR),
errmsg("invalid schema name"),
parser_errposition(pubobj->location));
+
+ /* Flatten EXCEPT entries into the top-level list */
+ foreach_ptr(PublicationObjSpec, eobj, pubobj->except_tables)
+ {
+ /*
+ * Unqualified names are implicitly qualified with the parent
+ * schema. Qualified names must match the parent schema —
+ * each EXCEPT clause is bound to exactly one schema, so
+ * cross-schema entries are rejected at parse time.
+ */
+ if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLES_IN_SCHEMA)
+ {
+ const char *eobj_schemaname = eobj->pubtable->relation->schemaname;
+ const char *eobj_relname = eobj->pubtable->relation->relname;
+
+ if (eobj_schemaname == NULL)
+ eobj->pubtable->relation->schemaname = pubobj->name;
+ else if (strcmp(eobj_schemaname, pubobj->name) != 0)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("table \"%s\" in EXCEPT clause does not belong to schema \"%s\"",
+ quote_qualified_identifier(eobj_schemaname, eobj_relname),
+ pubobj->name),
+ parser_errposition(eobj->location));
+ }
+ }
+ pubobjspec_list = list_concat(pubobjspec_list, pubobj->except_tables);
+ pubobj->except_tables = NIL;
}
prevobjtype = pubobj->pubobjtype;
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 4ecfcbff7ab..b55d7ab7cd1 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 *schemaExceptPubids;
ListCell *lc;
Oid publish_as_relid = relid;
int publish_ancestor_level = 0;
@@ -2104,6 +2105,19 @@ 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 = am_partition ?
+ llast_oid(get_partition_ancestors(relid)) : relid;
+
+ schemaExceptPubids = GetRelationExcludedPublications(root_relid);
+
/* Reload publications if needed before use. */
if (!publications_valid)
{
@@ -2267,7 +2281,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
ancestor = GetTopMostAncestorInPublication(pub->oid,
ancestors,
- &level);
+ &level,
+ schemaExceptPubids);
if (ancestor != InvalidOid)
{
@@ -2281,7 +2296,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(schemaExceptPubids, pub->oid)) ||
ancestor_published)
publish = true;
}
@@ -2360,6 +2376,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
list_free(pubids);
list_free(schemaPubids);
+ list_free(schemaExceptPubids);
list_free(rel_publications);
entry->replicate_valid = true;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e1449654f96..e5b1a70e05e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -7038,6 +7038,24 @@ describePublications(const char *pattern)
if (!addFooterToPublicationDesc(&buf, _("Tables from schemas:"),
true, &cont))
goto error_return;
+
+ if (pset.sversion >= 190000)
+ {
+ /*
+ * Get tables in the EXCEPT clause for this schema
+ * publication.
+ */
+ printfPQExpBuffer(&buf,
+ "SELECT concat(c.relnamespace::regnamespace, '.', c.relname)\n"
+ "FROM pg_catalog.pg_class c\n"
+ " JOIN pg_catalog.pg_publication_rel pr ON c.oid = pr.prrelid\n"
+ "WHERE pr.prpubid = '%s'\n"
+ " AND pr.prexcept\n"
+ "ORDER BY 1", pubid);
+ if (!addFooterToPublicationDesc(&buf, _("Except tables:"),
+ true, &cont))
+ goto error_return;
+ }
}
}
else
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 75132528f3a..2c652cf32a0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1065,6 +1065,15 @@ static const SchemaQuery Query_for_trigger_of_table = {
"SELECT nspname FROM pg_catalog.pg_namespace "\
" WHERE nspname LIKE '%s'"
+#define Query_for_list_of_tables_in_schema \
+"SELECT n.nspname || '.' || c.relname "\
+" FROM pg_catalog.pg_class c "\
+" JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "\
+" WHERE c.relkind IN (" CppAsString2(RELKIND_RELATION) ", " \
+ CppAsString2(RELKIND_PARTITIONED_TABLE) ") "\
+" AND (n.nspname || '.' || c.relname) LIKE '%s' "\
+" AND n.nspname = '%s'"
+
/* Use COMPLETE_WITH_QUERY_VERBATIM with these queries for GUC names: */
#define Query_for_list_of_alter_system_set_vars \
"SELECT pg_catalog.lower(name) FROM pg_catalog.pg_settings "\
@@ -3785,8 +3794,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", 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_SCHEMA_QUERY(Query_for_list_of_tables);
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE", MatchAnyN) && !ends_with(prev_wd, ','))
+ COMPLETE_WITH(")");
/* Complete "CREATE PUBLICATION <name> [...] WITH" */
else if (Matches("CREATE", "PUBLICATION", MatchAnyN, "WITH", "("))
COMPLETE_WITH("publish", "publish_generated_columns", "publish_via_partition_root");
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 89b4bb14f62..53e3d7c6f3d 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -191,7 +191,8 @@ extern List *GetPubPartitionOptionRelations(List *result,
PublicationPartOpt pub_partopt,
Oid relid);
extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
- int *ancestor_level);
+ int *ancestor_level,
+ List *except_pubids);
extern bool is_publishable_relation(Relation rel);
extern bool is_schema_publication(Oid pubid);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 91377a6cde3..98a03c0eeda 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4493,6 +4493,8 @@ typedef struct PublicationObjSpec
PublicationObjSpecType pubobjtype; /* type of this publication object */
char *name;
PublicationTable *pubtable;
+ List *except_tables; /* tables specified in the EXCEPT clause (for
+ * TABLES IN SCHEMA) */
ParseLoc location; /* token location, or -1 if unknown */
} PublicationObjSpec;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 29e54b214a0..77d77c89d80 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,99 @@ HINT: Change the publication's EXCEPT clause using ALTER PUBLICATION ... SET AL
RESET client_min_messages;
DROP TABLE testpub_root, testpub_part1, tab_main;
DROP PUBLICATION testpub8;
---- Tests for publications with SEQUENCES
+---------------------------------------------
+-- EXCEPT tests for TABLES IN SCHEMA
+---------------------------------------------
+SET client_min_messages = 'ERROR';
+-- Create tables in pub_test for these tests
+CREATE TABLE pub_test.testpub_tbl_s1 (a int primary key, b text);
+CREATE TABLE pub_test.testpub_tbl_s2 (x int primary key, y text);
+-- Create same-named tables in public to verify unqualified EXCEPT entries
+-- are qualified with the named schema, not public
+CREATE TABLE testpub_nopk (foo int, bar int);
+CREATE TABLE testpub_tbl_s1 (a int primary key, b text);
+-- Basic: exclude one table from a schema publication
+CREATE PUBLICATION testpub_schema_except1
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1);
+\dRp+ testpub_schema_except1
+ Publication testpub_schema_except1
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f | f | t | t | t | t | none | f |
+Tables from schemas:
+ "pub_test"
+Except tables:
+ "pub_test.testpub_tbl_s1"
+
+-- Exclude multiple tables using unqualified names; same-named tables exist in
+-- public to confirm unqualified names resolve to pub_test, not public
+CREATE PUBLICATION testpub_schema_except2
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_nopk, testpub_tbl_s1);
+\dRp+ testpub_schema_except2
+ Publication testpub_schema_except2
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f | f | t | t | t | t | none | f |
+Tables from schemas:
+ "pub_test"
+Except tables:
+ "pub_test.testpub_nopk"
+ "pub_test.testpub_tbl_s1"
+
+-- fail: EXCEPT table belongs to a different schema
+CREATE PUBLICATION testpub_except_wrongschema
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1);
+ERROR: table "public.testpub_tbl1" in EXCEPT clause does not belong to schema "pub_test"
+LINE 2: FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testp...
+ ^
+-- fail: cross-schema EXCEPT not allowed; each EXCEPT is bound to its immediate schema
+CREATE PUBLICATION testpub_except_crossschema
+ FOR TABLES IN SCHEMA pub_test, public EXCEPT (TABLE pub_test.testpub_tbl_s1, public.testpub_tbl1);
+ERROR: table "pub_test.testpub_tbl_s1" in EXCEPT clause does not belong to schema "public"
+LINE 2: ...R TABLES IN SCHEMA pub_test, public EXCEPT (TABLE pub_test.t...
+ ^
+-- Multiple schemas each with their own EXCEPT clause
+CREATE PUBLICATION testpub_schema_except_multi
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1),
+ public EXCEPT (TABLE testpub_tbl1);
+\dRp+ testpub_schema_except_multi
+ Publication testpub_schema_except_multi
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f | f | t | t | t | t | none | f |
+Tables from schemas:
+ "pub_test"
+ "public"
+Except tables:
+ "pub_test.testpub_tbl_s1"
+ "public.testpub_tbl1"
+
+-- fail: table appears in both the explicit table list and the EXCEPT clause
+CREATE PUBLICATION testpub_except_conflict
+ FOR TABLE pub_test.testpub_tbl_s1, TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1);
+ERROR: table "pub_test.testpub_tbl_s1" cannot appear in both the table list and the EXCEPT clause
+-- fail: nonexistent table in EXCEPT clause
+CREATE PUBLICATION testpub_except_norel
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table);
+ERROR: relation "pub_test.nonexistent_table" does not exist
+-- fail: partition cannot appear in EXCEPT clause; only root tables are allowed
+CREATE TABLE pub_test.testpub_parted_s (a int) PARTITION BY LIST (a);
+CREATE TABLE pub_test.testpub_part_s PARTITION OF pub_test.testpub_parted_s FOR VALUES IN (1);
+CREATE PUBLICATION testpub_except_partition
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_part_s);
+ERROR: cannot specify relation "pub_test.testpub_part_s" in the publication EXCEPT clause
+DETAIL: This operation is not supported for individual partitions.
+-- fail: TABLE keyword is required for the first entry in the EXCEPT clause
+CREATE PUBLICATION testpub_except_nokw
+ FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
+ERROR: syntax error at or near "testpub_nopk"
+LINE 2: FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
+ ^
+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;
CREATE SEQUENCE regress_pub_seq0;
CREATE SEQUENCE pub_test.regress_pub_seq1;
-- FOR ALL SEQUENCES
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 041e14a4de6..5d8a4e2637e 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,67 @@ RESET client_min_messages;
DROP TABLE testpub_root, testpub_part1, tab_main;
DROP PUBLICATION testpub8;
---- Tests for publications with SEQUENCES
+---------------------------------------------
+-- EXCEPT tests for TABLES IN SCHEMA
+---------------------------------------------
+SET client_min_messages = 'ERROR';
+-- Create tables in pub_test for these tests
+CREATE TABLE pub_test.testpub_tbl_s1 (a int primary key, b text);
+CREATE TABLE pub_test.testpub_tbl_s2 (x int primary key, y text);
+-- Create same-named tables in public to verify unqualified EXCEPT entries
+-- are qualified with the named schema, not public
+CREATE TABLE testpub_nopk (foo int, bar int);
+CREATE TABLE testpub_tbl_s1 (a int primary key, b text);
+
+-- Basic: exclude one table from a schema publication
+CREATE PUBLICATION testpub_schema_except1
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1);
+\dRp+ testpub_schema_except1
+
+-- Exclude multiple tables using unqualified names; same-named tables exist in
+-- public to confirm unqualified names resolve to pub_test, not public
+CREATE PUBLICATION testpub_schema_except2
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_nopk, testpub_tbl_s1);
+\dRp+ testpub_schema_except2
+
+-- fail: EXCEPT table belongs to a different schema
+CREATE PUBLICATION testpub_except_wrongschema
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1);
+
+-- fail: cross-schema EXCEPT not allowed; each EXCEPT is bound to its immediate schema
+CREATE PUBLICATION testpub_except_crossschema
+ FOR TABLES IN SCHEMA pub_test, public EXCEPT (TABLE pub_test.testpub_tbl_s1, public.testpub_tbl1);
+
+-- Multiple schemas each with their own EXCEPT clause
+CREATE PUBLICATION testpub_schema_except_multi
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1),
+ public EXCEPT (TABLE testpub_tbl1);
+\dRp+ testpub_schema_except_multi
+
+-- fail: table appears in both the explicit table list and the EXCEPT clause
+CREATE PUBLICATION testpub_except_conflict
+ FOR TABLE pub_test.testpub_tbl_s1, TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1);
+
+-- fail: nonexistent table in EXCEPT clause
+CREATE PUBLICATION testpub_except_norel
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table);
+
+-- fail: partition cannot appear in EXCEPT clause; only root tables are allowed
+CREATE TABLE pub_test.testpub_parted_s (a int) PARTITION BY LIST (a);
+CREATE TABLE pub_test.testpub_part_s PARTITION OF pub_test.testpub_parted_s FOR VALUES IN (1);
+CREATE PUBLICATION testpub_except_partition
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_part_s);
+
+-- fail: TABLE keyword is required for the first entry in the EXCEPT clause
+CREATE PUBLICATION testpub_except_nokw
+ FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
+
+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;
+
CREATE SEQUENCE regress_pub_seq0;
CREATE SEQUENCE pub_test.regress_pub_seq1;
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 8c58d282eee..18c7b2c1fca 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -24,14 +24,17 @@ my $result;
sub test_except_root_partition
{
- my ($pubviaroot) = @_;
+ my ($pubviaroot, $pubsql) = @_;
+ $pubsql //=
+ "CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT (TABLE root1)";
+ $pubsql .= " WITH (publish_via_partition_root = $pubviaroot)";
# If the root partitioned table is in the EXCEPT clause, all its
# partitions are excluded from publication, regardless of the
# publish_via_partition_root setting.
$node_publisher->safe_psql(
'postgres', qq(
- CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT (TABLE root1) WITH (publish_via_partition_root = $pubviaroot);
+ $pubsql;
INSERT INTO root1 VALUES (1), (101);
));
$node_subscriber->safe_psql('postgres',
@@ -223,6 +226,131 @@ $node_subscriber->safe_psql(
test_except_root_partition('false');
test_except_root_partition('true');
+# Same validation using TABLES IN SCHEMA instead of FOR ALL TABLES.
+my $schema_pub =
+ "CREATE PUBLICATION tap_pub_part FOR TABLES IN SCHEMA public EXCEPT (TABLE public.root1)";
+test_except_root_partition('false', $schema_pub);
+test_except_root_partition('true', $schema_pub);
+
+# ============================================
+# EXCEPT test cases for TABLES IN SCHEMA
+# ============================================
+
+# Create a dedicated schema with two tables: one to be published and one to be
+# excluded. Also create inherited tables to verify ONLY semantics.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE SCHEMA sch1;
+ CREATE TABLE sch1.tab_published AS SELECT generate_series(1,5) AS a;
+ CREATE TABLE sch1.tab_excluded AS SELECT generate_series(1,5) AS a;
+ CREATE TABLE sch1.parent (a int);
+ CREATE TABLE sch1.child (b int) INHERITS (sch1.parent);
+));
+
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ CREATE SCHEMA sch1;
+ CREATE TABLE sch1.tab_published (a int);
+ CREATE TABLE sch1.tab_excluded (a int);
+ CREATE TABLE sch1.parent (a int);
+ CREATE TABLE sch1.child (b int) INHERITS (sch1.parent);
+));
+
+# Basic test: initial sync respects EXCEPT.
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.tab_excluded)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_published");
+is($result, qq(5),
+ 'TABLES IN SCHEMA EXCEPT: initial sync copies included table');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_excluded");
+is($result, qq(0),
+ 'TABLES IN SCHEMA EXCEPT: initial sync skips excluded table');
+
+# DML: only the included table should be replicated.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ INSERT INTO sch1.tab_published VALUES (6);
+ INSERT INTO sch1.tab_excluded VALUES (6);
+));
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_published");
+is($result, qq(6),
+ 'TABLES IN SCHEMA EXCEPT: DML on included table is replicated');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_excluded");
+is($result, qq(0),
+ 'TABLES IN SCHEMA EXCEPT: DML on excluded table is not replicated');
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
+# Inherited tables: excluding the parent (without ONLY) also excludes the child.
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.parent)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.child VALUES (generate_series(1,5), generate_series(1,5))"
+);
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.child");
+is($result, qq(0),
+ 'TABLES IN SCHEMA EXCEPT: excluding parent (without ONLY) also excludes child'
+);
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
+# Test that EXCEPT (TABLE ONLY parent) excludes only the parent itself, not its
+# child. Truncate child first so rows from the previous test are not copied by
+# the initial table sync of the next subscription.
+$node_publisher->safe_psql('postgres', 'TRUNCATE sch1.child');
+$node_subscriber->safe_psql('postgres', 'TRUNCATE sch1.child');
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE ONLY sch1.parent)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.child VALUES (generate_series(1,5), generate_series(1,5))"
+);
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.child");
+is($result, qq(5),
+ 'TABLES IN SCHEMA EXCEPT: ONLY parent in EXCEPT does not exclude child');
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
+# Cleanup schema tables before the multi-publication section.
+$node_publisher->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
+$node_subscriber->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
+
# ============================================
# Test when a subscription is subscribing to multiple publications
# ============================================
@@ -254,6 +382,7 @@ $node_publisher->safe_psql(
DROP PUBLICATION tap_pub2;
TRUNCATE tab1;
));
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
$node_subscriber->safe_psql('postgres', qq(TRUNCATE tab1));
# OK when a table is excluded by pub1 EXCEPT clause, but it is included by pub2
--
2.50.1 (Apple Git-155)
[application/octet-stream] v6-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABLE.patch (21.6K, 3-v6-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABLE.patch)
download | inline diff:
From 0313eb30e5ea34cb3983fa61e54945d6bf0dcc01 Mon Sep 17 00:00:00 2001
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Tue, 19 May 2026 13:35:05 +0530
Subject: [PATCH v6 2/3] Add EXCEPT support to ALTER PUBLICATION ADD TABLES IN
SCHEMA
Extend the EXCEPT clause support to allow tables to be excluded when
adding a schema to a publication via ALTER PUBLICATION ... ADD:
Syntax:
ALTER PUBLICATION pub ADD TABLES IN SCHEMA s EXCEPT (TABLE s.t1);
Since pg_dump uses ALTER PUBLICATION ... ADD, support for it is
included in this patch.
---
doc/src/sgml/ref/alter_publication.sgml | 42 +++++++-
src/backend/catalog/pg_publication.c | 19 ++--
src/backend/commands/publicationcmds.c | 113 +++++++++++++++++++++-
src/bin/pg_dump/pg_dump.c | 30 +++++-
src/bin/pg_dump/t/002_pg_dump.pl | 36 +++++++
src/bin/psql/tab-complete.in.c | 17 ++++
src/test/regress/expected/publication.out | 18 +++-
src/test/regress/sql/publication.sql | 11 ++-
src/test/subscription/t/037_except.pl | 32 ++++++
9 files changed, 305 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index aa32bb169e9..8aedfd951a5 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">schema_name</replaceable> | CURRENT_SCHEMA } [ EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ] [, ... ]
<phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase>
@@ -54,6 +54,10 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<phrase>and <replaceable class="parameter">table_object</replaceable> is:</phrase>
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
+
+<phrase>and <replaceable class="parameter">except_table_object</replaceable> is:</phrase>
+
+ [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
</synopsis>
</refsynopsisdiv>
@@ -110,6 +114,15 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<literal>ADD TABLE</literal>.
</para>
+ <para>
+ The <literal>EXCEPT</literal> clause can be used with
+ <literal>ADD TABLES IN SCHEMA</literal> to exclude specific tables from a
+ schema-level publication. <literal>EXCEPT</literal> is not supported with
+ <literal>DROP TABLES IN SCHEMA</literal>; instead, dropping a schema from
+ the publication automatically removes all of its associated
+ <literal>EXCEPT</literal> entries.
+ </para>
+
<para>
The fourth variant of this command listed in the synopsis can change
all of the publication properties specified in
@@ -198,6 +211,23 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] )</literal></term>
+ <listitem>
+ <para>
+ Specifies tables to be excluded from a schema-level publication entry.
+ This clause may be used with <literal>ADD TABLES IN SCHEMA</literal>
+ and not with <literal>DROP TABLES IN SCHEMA</literal>. Each named
+ table must belong to the schema specified in the same
+ <literal>TABLES IN SCHEMA</literal> clause. Table names may be
+ schema-qualified or unqualified; unqualified names are implicitly
+ qualified with the schema named in the same clause. See
+ <xref linkend="sql-createpublication"/> for further details on the
+ semantics of <literal>EXCEPT</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
@@ -288,6 +318,16 @@ ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA marketing, sales;
</programlisting>
</para>
+ <para>
+ Add schema <structname>sales</structname> to the publication
+ <structname>sales_publication</structname>, excluding the
+ <structname>sales.internal</structname> and
+ <structname>sales.drafts</structname> tables:
+<programlisting>
+ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA sales EXCEPT (TABLE sales.internal, sales.drafts);
+</programlisting>
+ </para>
+
<para>
Add tables <structname>users</structname>,
<structname>departments</structname> and schema
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 6f945955901..a339abf5e1b 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -645,15 +645,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 95186ca7377..ea689e5da7c 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -70,6 +70,9 @@ 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 AlterPublicationExceptTables(AlterPublicationStmt *stmt,
+ HeapTuple tup, List *exceptrelations,
+ List *schemaidlist);
static char defGetGeneratedColsOption(DefElem *def);
@@ -1519,6 +1522,13 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
}
PublicationAddSchemas(pubform->oid, schemaidlist, false, stmt);
+
+ /*
+ * Increment the command counter so that is_schema_publication() in
+ * GetExcludedPublicationTables() can see the just-inserted schema
+ * rows when AlterPublicationExceptTables runs next.
+ */
+ CommandCounterIncrement();
}
else if (stmt->action == AP_DropObjects)
PublicationDropSchemas(pubform->oid, schemaidlist, false);
@@ -1544,6 +1554,100 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
* skip existing ones when doing catalog update.
*/
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 AlterPublicationExceptTables runs next.
+ */
+ CommandCounterIncrement();
+ }
+}
+
+/*
+ * Alter the EXCEPT list of a 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
+ * or FOR ALL TABLES clause.
+ */
+static void
+AlterPublicationExceptTables(AlterPublicationStmt *stmt,
+ HeapTuple tup, List *exceptrelations,
+ List *schemaidlist)
+{
+ Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+ Oid pubid = pubform->oid;
+
+ /*
+ * Nothing to do if no EXCEPT entries.
+ */
+ if (!exceptrelations)
+ return;
+
+ /*
+ * This function handles EXCEPT entries for schema-level publications
+ * only. For FOR ALL TABLES publications, EXCEPT entries are already
+ * processed by AlterPublicationTables().
+ */
+ if (schemaidlist == NIL && !is_schema_publication(pubid))
+ return;
+
+ /*
+ * EXCEPT is not meaningful with DROP: dropping a schema from a
+ * publication already removes all its except entries via cascade, and
+ * there is no sensible interpretation of "drop only the except entry but
+ * keep the schema".
+ */
+ if (stmt->action == AP_DropObjects)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("EXCEPT clause is not supported with DROP in ALTER PUBLICATION")));
+
+ /*
+ * EXCEPT with SET is not supported: SET replaces the schema list but does
+ * not have a well-defined semantics for merging or replacing existing
+ * except entries. Users should DROP and re-ADD the schema with the
+ * desired EXCEPT list instead.
+ */
+ if (stmt->action == AP_SetObjects)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("EXCEPT clause is not supported with SET in ALTER PUBLICATION")));
+
+ if (stmt->action == AP_AddObjects)
+ {
+ List *rels;
+ List *explicitrelids;
+ ListCell *lc;
+
+ rels = OpenTableList(exceptrelations);
+
+ explicitrelids = GetIncludedPublicationRelations(pubid,
+ PUBLICATION_PART_ROOT);
+
+ /*
+ * Validate that each excluded table is not also in the explicit table
+ * list (which would be contradictory).
+ */
+ foreach(lc, rels)
+ {
+ PublicationRelInfo *pri = (PublicationRelInfo *) lfirst(lc);
+ Oid relid = RelationGetRelid(pri->relation);
+ Oid relns = RelationGetNamespace(pri->relation);
+
+ if (list_member_oid(explicitrelids, relid))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("table \"%s.%s\" cannot appear in both the table list and the EXCEPT clause",
+ get_namespace_name(relns),
+ RelationGetRelationName(pri->relation)));
+ }
+
+ PublicationAddTables(pubid, rels, false, stmt);
+
+ CloseTableList(rels);
}
}
@@ -1754,10 +1858,17 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
errmsg("publication \"%s\" does not exist",
stmt->pubname));
- relations = list_concat(relations, exceptrelations);
+ /*
+ * For FOR ALL TABLES, EXCEPT entries are processed by
+ * AlterPublicationTables(), so merge them in. For TABLES IN SCHEMA,
+ * they are handled separately by AlterPublicationExceptTables().
+ */
+ if (stmt->for_all_tables)
+ relations = list_concat(relations, exceptrelations);
AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
schemaidlist != NIL);
AlterPublicationSchemas(stmt, tup, schemaidlist);
+ AlterPublicationExceptTables(stmt, tup, exceptrelations, schemaidlist);
AlterPublicationAllFlags(stmt, rel, tup);
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d56dcc701ce..e62d74c8ca0 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5019,6 +5019,7 @@ dumpPublicationNamespace(Archive *fout, const PublicationSchemaInfo *pubsinfo)
PublicationInfo *pubinfo = pubsinfo->publication;
PQExpBuffer query;
char *tag;
+ bool has_except = false;
/* Do nothing if not dumping schema */
if (!dopt->dumpSchema)
@@ -5029,7 +5030,34 @@ dumpPublicationNamespace(Archive *fout, const PublicationSchemaInfo *pubsinfo)
query = createPQExpBuffer();
appendPQExpBuffer(query, "ALTER PUBLICATION %s ", fmtId(pubinfo->dobj.name));
- appendPQExpBuffer(query, "ADD TABLES IN SCHEMA %s;\n", fmtId(schemainfo->dobj.name));
+ appendPQExpBuffer(query, "ADD TABLES IN SCHEMA %s", fmtId(schemainfo->dobj.name));
+
+ /*
+ * Append EXCEPT clause for any tables that belong to this schema
+ * and are excluded from the publication.
+ */
+ for (SimplePtrListCell *cell = pubinfo->except_tables.head; cell; cell = cell->next)
+ {
+ TableInfo *tbinfo = (TableInfo *) cell->ptr;
+
+ if (strcmp(tbinfo->dobj.namespace->dobj.name, schemainfo->dobj.name) == 0)
+ {
+ if (!has_except)
+ {
+ appendPQExpBufferStr(query, " EXCEPT (");
+ has_except = true;
+ }
+ else
+ appendPQExpBufferStr(query, ", ");
+
+ appendPQExpBuffer(query, "TABLE ONLY %s", fmtId(tbinfo->dobj.name));
+ }
+ }
+
+ if (has_except)
+ appendPQExpBufferStr(query, ")");
+
+ appendPQExpBufferStr(query, ";\n");
/*
* There is no point in creating drop query as the drop is done by schema
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 3ee9fda50e4..af37bcb3627 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3242,6 +3242,42 @@ my %tests = (
like => { %full_runs, section_post_data => 1, },
},
+ 'CREATE PUBLICATION pub11' => {
+ create_order => 50,
+ create_sql =>
+ 'CREATE PUBLICATION pub11 FOR TABLES IN SCHEMA dump_test EXCEPT (TABLE dump_test.test_table);',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub11 WITH (publish = 'insert, update, delete, truncate');\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
+ 'ALTER PUBLICATION pub11 ADD TABLES IN SCHEMA dump_test EXCEPT (dump_test.test_table)'
+ => {
+ regexp => qr/^
+ \QALTER PUBLICATION pub11 ADD TABLES IN SCHEMA dump_test EXCEPT (TABLE ONLY test_table);\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
+ 'CREATE PUBLICATION pub12' => {
+ create_order => 50,
+ create_sql =>
+ 'CREATE PUBLICATION pub12 FOR TABLES IN SCHEMA dump_test EXCEPT (TABLE dump_test.test_table, dump_test.test_second_table);',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub12 WITH (publish = 'insert, update, delete, truncate');\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
+ 'ALTER PUBLICATION pub12 ADD TABLES IN SCHEMA dump_test EXCEPT (dump_test.test_table, dump_test.test_second_table)'
+ => {
+ regexp => qr/^
+ \QALTER PUBLICATION pub12 ADD TABLES IN SCHEMA dump_test EXCEPT (TABLE ONLY test_table, TABLE ONLY test_second_table);\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
'CREATE SUBSCRIPTION sub1' => {
create_order => 50,
create_sql => 'CREATE SUBSCRIPTION sub1
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 2c652cf32a0..6ea92444105 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2364,6 +2364,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", 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_SCHEMA_QUERY(Query_for_list_of_tables);
+ 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 77d77c89d80..a9d5e7a49db 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -564,11 +564,27 @@ CREATE PUBLICATION testpub_except_nokw
ERROR: syntax error at or near "testpub_nopk"
LINE 2: FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
^
+---------------------------------------------
+-- EXCEPT tests for ALTER PUBLICATION
+---------------------------------------------
+CREATE PUBLICATION testpub_alter_except;
+-- ADD: add a schema with an excepted table
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1);
+\dRp+ testpub_alter_except
+ Publication testpub_alter_except
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f | f | t | t | t | t | none | f |
+Tables from schemas:
+ "pub_test"
+Except tables:
+ "pub_test.testpub_tbl_s1"
+
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;
CREATE SEQUENCE regress_pub_seq0;
CREATE SEQUENCE pub_test.regress_pub_seq1;
-- FOR ALL SEQUENCES
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 5d8a4e2637e..6b9eb26a2af 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -278,11 +278,20 @@ CREATE PUBLICATION testpub_except_partition
CREATE PUBLICATION testpub_except_nokw
FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
+---------------------------------------------
+-- EXCEPT tests for ALTER PUBLICATION
+---------------------------------------------
+CREATE PUBLICATION testpub_alter_except;
+
+-- ADD: add a schema with an excepted table
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1);
+\dRp+ testpub_alter_except
+
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;
CREATE SEQUENCE regress_pub_seq0;
CREATE SEQUENCE pub_test.regress_pub_seq1;
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 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/octet-stream] v6-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABLE.patch (20.0K, 4-v6-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABLE.patch)
download | inline diff:
From d7088cb54fa57c9000e57054f6db16721bbf0013 Mon Sep 17 00:00:00 2001
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Tue, 19 May 2026 13:44:35 +0530
Subject: [PATCH v6 3/3] Add EXCEPT support to ALTER PUBLICATION SET TABLES IN
SCHEMA
Extend AlterPublicationExceptTables() with the AP_SetObjects case,
which redefine the publication and replaces the entire EXCEPT list.
Syntax:
ALTER PUBLICATION pub SET TABLES IN SCHEMA s EXCEPT (TABLE t1);
This patch also cleans up EXCEPT entries when a schema is dropped
from the publication.
---
doc/src/sgml/ref/alter_publication.sgml | 36 ++++--
src/backend/commands/publicationcmds.c | 145 ++++++++++++++++++++--
src/bin/psql/tab-complete.in.c | 17 +++
src/test/regress/expected/publication.out | 20 +++
src/test/regress/sql/publication.sql | 10 ++
src/test/subscription/t/037_except.pl | 90 ++++++++++++++
6 files changed, 293 insertions(+), 25 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 8aedfd951a5..1b73b34e43a 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -54,10 +54,6 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<phrase>and <replaceable class="parameter">table_object</replaceable> is:</phrase>
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
-
-<phrase>and <replaceable class="parameter">except_table_object</replaceable> is:</phrase>
-
- [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
</synopsis>
</refsynopsisdiv>
@@ -97,7 +93,11 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
used with a publication defined with <literal>FOR TABLE</literal> or
<literal>FOR TABLES IN SCHEMA</literal>, replaces the list of tables/schemas
in the publication with the specified list; the existing tables or schemas
- that were present in the publication will be removed.
+ that were present in the publication will be removed. When
+ <literal>SET TABLES IN SCHEMA</literal> is used with an
+ <literal>EXCEPT</literal> clause, the excluded tables for each schema are
+ replaced with the specified list; if <literal>EXCEPT</literal> is omitted
+ for a schema, any existing exclusions for that schema are cleared.
</para>
<para>
@@ -116,7 +116,8 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<para>
The <literal>EXCEPT</literal> clause can be used with
- <literal>ADD TABLES IN SCHEMA</literal> to exclude specific tables from a
+ <literal>ADD TABLES IN SCHEMA</literal> and
+ <literal>SET TABLES IN SCHEMA</literal> to exclude specific tables from a
schema-level publication. <literal>EXCEPT</literal> is not supported with
<literal>DROP TABLES IN SCHEMA</literal>; instead, dropping a schema from
the publication automatically removes all of its associated
@@ -217,12 +218,12 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<para>
Specifies tables to be excluded from a schema-level publication entry.
This clause may be used with <literal>ADD TABLES IN SCHEMA</literal>
- and not with <literal>DROP TABLES IN SCHEMA</literal>. 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
+ and <literal>SET TABLES IN SCHEMA</literal>, and not with
+ <literal>DROP TABLES IN SCHEMA</literal>. Each named table must belong
+ to the schema specified in the same <literal>TABLES IN SCHEMA</literal>
+ clause. Table names may be schema-qualified or unqualified; unqualified
+ names are implicitly qualified with the schema named in the same clause.
+ See <xref linkend="sql-createpublication"/> for further details on the
semantics of <literal>EXCEPT</literal>.
</para>
</listitem>
@@ -328,6 +329,17 @@ ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA sales EXCEPT (TABLE sal
</programlisting>
</para>
+ <para>
+ Replace the schema list of <structname>sales_publication</structname> with
+ <structname>sales</structname>, excluding only
+ <structname>sales.drafts</structname> (any previously excluded tables for
+ that schema are replaced, and schemas previously in the publication are
+ removed):
+<programlisting>
+ALTER PUBLICATION sales_publication SET TABLES IN SCHEMA sales EXCEPT (TABLE sales.drafts);
+</programlisting>
+ </para>
+
<para>
Add tables <structname>users</structname>,
<structname>departments</structname> and schema
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index ea689e5da7c..5118881ff6c 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1581,9 +1581,11 @@ AlterPublicationExceptTables(AlterPublicationStmt *stmt,
Oid pubid = pubform->oid;
/*
- * Nothing to do if no EXCEPT entries.
+ * Nothing to do if no EXCEPT entries, except in SET: for that it is quite
+ * possible that the user has removed all exceptions, in which case we
+ * need to drop any existing ones.
*/
- if (!exceptrelations)
+ if (!exceptrelations && stmt->action != AP_SetObjects)
return;
/*
@@ -1605,17 +1607,6 @@ AlterPublicationExceptTables(AlterPublicationStmt *stmt,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("EXCEPT clause is not supported with DROP in ALTER PUBLICATION")));
- /*
- * EXCEPT with SET is not supported: SET replaces the schema list but does
- * not have a well-defined semantics for merging or replacing existing
- * except entries. Users should DROP and re-ADD the schema with the
- * desired EXCEPT list instead.
- */
- if (stmt->action == AP_SetObjects)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("EXCEPT clause is not supported with SET in ALTER PUBLICATION")));
-
if (stmt->action == AP_AddObjects)
{
List *rels;
@@ -1647,6 +1638,95 @@ AlterPublicationExceptTables(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;
+ ListCell *lc;
+
+ rels = OpenTableList(exceptrelations);
+
+ /* Collect OIDs of the desired new except list. */
+ foreach(lc, rels)
+ {
+ PublicationRelInfo *pri = (PublicationRelInfo *) lfirst(lc);
+
+ newexceptrelids = lappend_oid(newexceptrelids,
+ RelationGetRelid(pri->relation));
+ }
+
+ explicitrelids = GetIncludedPublicationRelations(pubid,
+ PUBLICATION_PART_ROOT);
+
+ /*
+ * Validate that each excluded table is not also in the explicit table
+ * list (which would be contradictory).
+ */
+ foreach(lc, rels)
+ {
+ PublicationRelInfo *pri = (PublicationRelInfo *) lfirst(lc);
+ Oid relid = RelationGetRelid(pri->relation);
+ Oid relns = RelationGetNamespace(pri->relation);
+
+ if (list_member_oid(explicitrelids, relid))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("table \"%s.%s\" cannot appear in both the table list and the EXCEPT clause",
+ get_namespace_name(relns),
+ RelationGetRelationName(pri->relation)));
+ }
+
+ /*
+ * Get the current set of except entries. Only FOR ALL TABLES and
+ * schema-level publications can have except entries; for any other
+ * publication type oldexceptrelids stays NIL.
+ *
+ * Note: we check is_schema_publication() against the current catalog
+ * state (before AlterPublicationSchemas has run), so if the caller is
+ * doing SET TABLE t1 to convert a schema publication into a plain
+ * table publication, is_schema_publication() still returns true here.
+ * That is intentional: it lets us discover and clean up any stale
+ * except entries that belong to the old schema definition.
+ */
+ if (GetPublication(pubid)->alltables || is_schema_publication(pubid))
+ oldexceptrelids = GetExcludedPublicationTables(pubid,
+ PUBLICATION_PART_ROOT);
+
+ /* Build a list of old except entries not present in the new list. */
+ foreach(lc, oldexceptrelids)
+ {
+ Oid oldrelid = lfirst_oid(lc);
+
+ if (!list_member_oid(newexceptrelids, oldrelid))
+ delrelids = lappend_oid(delrelids, oldrelid);
+ }
+
+ /* Drop old except entries not present in the new list. */
+ foreach(lc, delrelids)
+ {
+ Oid relid = lfirst_oid(lc);
+ Oid proid;
+ ObjectAddress obj;
+
+ proid = GetSysCacheOid2(PUBLICATIONRELMAP,
+ Anum_pg_publication_rel_oid,
+ ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pubid));
+ if (!OidIsValid(proid))
+ continue; /* already gone */
+
+ ObjectAddressSet(obj, PublicationRelRelationId, proid);
+ performDeletion(&obj, DROP_CASCADE, 0);
+ }
+
+ /* Add new except entries, skipping any already present. */
+ PublicationAddTables(pubid, rels, true, stmt);
+
CloseTableList(rels);
}
}
@@ -2301,6 +2381,8 @@ PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok)
foreach(lc, schemas)
{
Oid schemaid = lfirst_oid(lc);
+ List *exceptoids;
+ ListCell *elc;
psid = GetSysCacheOid2(PUBLICATIONNAMESPACEMAP,
Anum_pg_publication_namespace_oid,
@@ -2317,8 +2399,45 @@ PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok)
get_namespace_name(schemaid))));
}
+ /*
+ * Collect prexcept rows for tables belonging to this schema before
+ * removing the schema entry. GetExcludedPublicationTables relies on
+ * is_schema_publication(), which scans pg_publication_namespace; if
+ * this is the last schema in the publication, performDeletion() below
+ * would remove that row and make is_schema_publication() return
+ * false, tripping the assertion.
+ */
+ exceptoids = 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(elc, exceptoids)
+ {
+ Oid relid = lfirst_oid(elc);
+ Oid proid;
+
+ if (get_rel_namespace(relid) != schemaid)
+ continue;
+
+ proid = GetSysCacheOid2(PUBLICATIONRELMAP,
+ Anum_pg_publication_rel_oid,
+ ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pubid));
+ if (!OidIsValid(proid))
+ continue; /* already gone */
+
+ ObjectAddressSet(obj, PublicationRelRelationId, proid);
+ performDeletion(&obj, DROP_CASCADE, 0);
+ }
+
+ list_free(exceptoids);
}
}
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 6ea92444105..8dce408143f 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2381,6 +2381,23 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE", MatchAnyN) && !ends_with(prev_wd, ','))
COMPLETE_WITH(")");
+ /* After a single schema name in SET context, offer EXCEPT ( TABLE */
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "TABLES", "IN", "SCHEMA", MatchAny) &&
+ !ends_with(prev_wd, ','))
+ COMPLETE_WITH("EXCEPT ( TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT"))
+ COMPLETE_WITH("( TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "("))
+ COMPLETE_WITH("TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE"))
+ {
+ set_completion_reference(prev4_wd);
+ COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_schema);
+ }
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE", MatchAnyN) && ends_with(prev_wd, ','))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+ 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 a9d5e7a49db..c93d1c96276 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -580,6 +580,26 @@ Tables from schemas:
Except tables:
"pub_test.testpub_tbl_s1"
+-- 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"
+
+-- 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
+-- 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...
+ ^
RESET client_min_messages;
DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
DROP TABLE pub_test.testpub_parted_s CASCADE;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 6b9eb26a2af..496c039ca25 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -287,6 +287,16 @@ CREATE PUBLICATION testpub_alter_except;
ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1);
\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
+
+-- 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 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);
+
RESET client_min_messages;
DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
DROP TABLE pub_test.testpub_parted_s CASCADE;
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 0ba6d6f8bb2..a32b2d7861a 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -376,6 +376,66 @@ $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");
+is($result, qq(7),
+ 'ALTER ... SET TABLES IN SCHEMA EXCEPT: newly included table is replicated'
+);
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_published");
+is($result, qq(6),
+ 'ALTER ... SET TABLES IN SCHEMA EXCEPT: now-excluded table is not replicated'
+);
+
+# SET without EXCEPT: clears the except list; both tables are now published.
+# tab_published will be re-synced because REFRESH removed its entry when it was
+# excluded. Truncate the subscriber copy beforehand so the re-sync produces
+# a predictable count: publisher has 7 rows (6 original + INSERT(7)), so the
+# subscriber ends up with 7 after re-sync, then 8 after INSERT(8).
+$node_subscriber->safe_psql('postgres', 'TRUNCATE sch1.tab_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");
+is($result, qq(8),
+ 'ALTER ... SET TABLES IN SCHEMA (no EXCEPT): tab_published replicated after except list cleared'
+);
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_excluded");
+is($result, qq(8),
+ 'ALTER ... SET TABLES IN SCHEMA (no EXCEPT): tab_excluded replicated after except list cleared'
+);
+
$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
@@ -443,6 +503,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)
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: pgsql-hackers@postgresql.org
Cc: nisha.moond412@gmail.com, smithpb2250@gmail.com, shveta.malik@gmail.com, amit.kapila16@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
In-Reply-To: <CABdArM79m7-CTf6KGGGU2QBydFtuonGgfxRSqk-vhwTsH8z1ow@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