public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedRe: Support EXCEPT for TABLES IN SCHEMA publications
3+ messages / 2 participants
[nested] [flat]
* Re: Support EXCEPT for TABLES IN SCHEMA publications
@ 2026-05-19 11:14 Nisha Moond <nisha.moond412@gmail.com>
2026-05-22 02:26 ` Re: Support EXCEPT for TABLES IN SCHEMA publications Peter Smith <smithpb2250@gmail.com>
2026-05-22 05:30 ` Re: Support EXCEPT for TABLES IN SCHEMA publications Peter Smith <smithpb2250@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Nisha Moond @ 2026-05-19 11:14 UTC (permalink / raw)
To: Peter Smith <smithpb2250@gmail.com>; +Cc: shveta malik <shveta.malik@gmail.com>; Amit Kapila <amit.kapila16@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Support EXCEPT for TABLES IN SCHEMA publications
2026-05-19 11:14 Re: Support EXCEPT for TABLES IN SCHEMA publications Nisha Moond <nisha.moond412@gmail.com>
@ 2026-05-22 02:26 ` Peter Smith <smithpb2250@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Peter Smith @ 2026-05-22 02:26 UTC (permalink / raw)
To: Nisha Moond <nisha.moond412@gmail.com>; +Cc: shveta malik <shveta.malik@gmail.com>; Amit Kapila <amit.kapila16@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi Nisha.
Here are some review comments for patch v6-0001.
======
src/backend/catalog/pg_publication.c
GetTopMostAncestorInPublication:
1.
+GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
+ int *ancestor_level, List *except_pubids)
I am having dificulty understanding this function. There needs to be a
description what does the input parameter 'except_pubids' mean. The
param name doesn't tell me anything much -- just that it is a list of
pubids that "something" (what?) is excluded from. And how does that
relate to the 'ancestors'?
~~~
2.
{
aschemaPubids = GetSchemaPublications(get_rel_namespace(ancestor));
- if (list_member_oid(aschemaPubids, puboid))
+ if (list_member_oid(aschemaPubids, puboid) &&
+ !list_member_oid(except_pubids, puboid))
Is this new code in the right place? I'm not 100% sure of the
'except_pubids' details, but shouldn't it be checked sooner? e.g. if
we know already that this pubid is no good
(!list_member_oid(except_pubids, puboid)) then what is the point to
even assign/check aschemaPubids?
~~~
3.
+ 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)));
Fully qualified 'targetrel' in the first error, but not in the second? Is it OK?
~~~
GetAllSchemaPublicationRelations:
4.
+ List *exceptlist = NIL;
The varname is a bit vague; it is a list of "what"? Maybe say
'except_relids' or similar.
======
src/backend/replication/pgoutput/pgoutput.c
get_rel_sync_entry:
5.
+ /*
+ * 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);
5a.
The varname 'schemaExceptPubids' seems ambiguous. It sounds like it is
pubids that have EXCEPT SCHEMA. In the future the ALL TABLES may
introduce "EXCEPT SCHEMA", but currently there is no such thing.
Meanwhile, here I think it means "EXCEPT TABLE", so IMO that varname
needs to indicate the meaning better.
~
5b.
Actually, this is becoming a GENERAL comment. There too many ways that
these EXCEPT tables are getting named, and it is causing confusion:
- except_pubids
- exceptlist
- exceptrelations
- exceptrels
- except_relid
- except_tables
- schemaExceptPubids
Can we standardize on some common names, to make all the code more consistent?
~
5c.
Previously, the result of 'get_partition_ancestors' was being freed,
but now it is not. I'm not sure how importatnt that is, because I
found other examples in PG source code also not freeing...
======
src/bin/psql/tab-complete.in.c
6.
+ 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);
I'm not sure if this is working as intended.
When testing for multiple except tables I get results like:
----
test_pub=# create publication pub1 for tables IN SCHEMA myschema <TAB>
EXCEPT ( TABLE WITH (
test_pub=# create publication pub1 for tables IN SCHEMA myschema
except ( table <TAB>
test_pub=# create publication pub1 for tables IN SCHEMA myschema
except ( table myschema.t<TAB>
myschema.t1 myschema.t2 myschema.t3
test_pub=# create publication pub1 for tables IN SCHEMA myschema
except ( table myschema.t1,<TAB>
information_schema. myschema. public. t1
t2 t3
----
Note: it is offering suggstions for schema names outside of the
"myschema". Should this code be calling
Query_for_list_of_tables_in_schema instead of
Query_for_list_of_tables?
======
src/test/regress/sql/publication.sql
7.
---- Tests for publications with SEQUENCES
+---------------------------------------------
+-- EXCEPT tests for TABLES IN SCHEMA
+---------------------------------------------
+SET client_min_messages = 'ERROR';
It looks like a previous comment for the SEQUENCES tests has been
accidentally removed.
I should be put back, and made more prominent like the other big comments.
e.g.
---------------------------------------------
-- Tests for publications with SEQUENCES
---------------------------------------------
~~~
8.
+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;
+
Add a "Cleanup" comment.
======
Kind Regards,
Peter Smith.
Fujitsu Australia.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Support EXCEPT for TABLES IN SCHEMA publications
2026-05-19 11:14 Re: Support EXCEPT for TABLES IN SCHEMA publications Nisha Moond <nisha.moond412@gmail.com>
@ 2026-05-22 05:30 ` Peter Smith <smithpb2250@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Peter Smith @ 2026-05-22 05:30 UTC (permalink / raw)
To: Nisha Moond <nisha.moond412@gmail.com>; +Cc: shveta malik <shveta.malik@gmail.com>; Amit Kapila <amit.kapila16@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi Nisha.
Here are some review comments for patch v6-0002.
======
Commit message
1.
Extend the EXCEPT clause support to allow tables to be excluded when
adding a schema to a publication via ALTER PUBLICATION ... ADD:
~
/ADD:/ADD./
======
doc/src/sgml/ref/alter_publication.sgml
Synopsis.
2.
- 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> [, ... ] ) ] [,
... ]
~
Probably needs to change to introduce the 'tables_in_schema' part,
same as in the CREATE PUBLICATION synopsis.
~~~
3.
+
+<phrase>and <replaceable
class="parameter">except_table_object</replaceable> is:</phrase>
+
+ [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
Something is wrong. Now the synopsis has 'except_table_object' 2x.
~~~
4.
+ <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>
4a.
I didn't think you need to say it is a "schema-level" publication.
That much is obvious because it already says "TABLES IN SCHEMA".
~
4b.
Maybe do not say "is not supported", because IMO that implies it will
cause an error.
SUGGESTION (or something like this)
Using <literal>DROP TABLES IN SCHEMA</literal> on a publication will
automatically also remove any associated <literal>EXCEPT</literal>
entries.
~~~
EXCEPT
5.
+ <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>
+
5a.
Oh! If this EXCEPT part was previously missing even for the "FOR ALL
TABLES", then IMO that is a separate bug that should be in another
thread and patched/fixed asap, then your patch should just make small
changes to to it.
~
5b.
I don't think you need to say "schema-level" here... Maybe reword like
"When used with ADD TABLES IN SCHEMA...". Anyway, all this wording
will need to change a bit after the aforementioned fix for "FOR ALL
TABLES EXCEPT" patched/pushed.
~~~
Examples
6.
+<programlisting>
+ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA sales EXCEPT
(TABLE sales.internal, sales.drafts);
+</programlisting>
It is OK left in the description, but IMO it is better if you don't
use the schema-qualified name in the actual code fragment.
======
src/backend/commands/publicationcmds.c
AlterPublicationSchemas:
7.
+ /*
+ * Increment the command counter so that is_schema_publication() in
+ * GetExcludedPublicationTables() can see the just-inserted schema
+ * rows when AlterPublicationExceptTables runs next.
+ */
+ CommandCounterIncrement();
Should this cut/paste common code be done afterwards just if
stmt->action == AP_AddObjects/SetObjects ?
~~~
AlterPublicationExceptTables:
8.
+ /*
+ * 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;
Huh? It seems contrary to the function comment that was also talking
about "FOR ALL TABLES".
Should this function really be called something different like
'AlterPublicationSchemaExceptTables'?
~~~
9.
+ /*
+ * 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")));
9a.
Not sure about this comment saying "does not have a well-defined
semantics". Should you instead just have XXX comment to simply say
"Not yet implemented", because this is getting replaced later by your
patch 0003 I think.
SUGGESTION
XXX EXCEPT with SET is not currently implemented. Workaround: Users
should DROP and re-ADD the schema with the desired EXCEPT list.
~
9b.
The ereport should be temporarily (until patch 0003 is pushed) have
using an errhint to say the workaround.
~~~
10.
+ 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)
Can tidy this using a foreach_ptr look instead of 'lc'.
~~~
11.
+ 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)));
Make use of the new function to get fully qualified names and replace
\"%s.%s\" with \"%s\".
~~~
12.
+ /*
+ * 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);
Would it be simpler if AlterPublicationExceptTables was merged (or
delegated from) the AlterPublicationSchemas?
======
src/bin/pg_dump/t/002_pg_dump.pl
13.
+ '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, },
+ },
+
Should not need to specify schema-qualified names in the CREATE
PUBLICATION or the ALTER PUBLICATION. I think a better test would have
one of each (e.g. don't qualify the 'dump_test.test_table') in any of
those SQL.
======
src/bin/psql/tab-complete.in.c
14.
+ 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(")");
I've not tested this, but the code looks the same. so I suspect this
suffers the same problem where it lists potentially all tables instead
of just the table of the current schema. Maybe this is an unavoidable
quirk...
======
src/test/regress/sql/publication.sql
15.
Should you add a some more test cases?
e.g. Pass with EXCEPT without the schema-qualified name.
e.g. Pass with multiple excepted tables.
e.g. Fail because non-existing table name.
e.g. Fail because table not in schema.
e.g. Fail syntax because missing keyword TABLE.
e.g. do a DROP TABLES IN SCHEMA to test that the except tables gove removed too
======
Kind Regards,
Peter Smith.
Fujitsu Australia.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-05-22 05:30 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-19 11:14 Re: Support EXCEPT for TABLES IN SCHEMA publications Nisha Moond <nisha.moond412@gmail.com>
2026-05-22 02:26 ` Peter Smith <smithpb2250@gmail.com>
2026-05-22 05:30 ` Peter Smith <smithpb2250@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox