v5-0001-Support-EXCEPT-clause-for-schema-level-publicatio.patch
application/octet-stream
Filename: v5-0001-Support-EXCEPT-clause-for-schema-level-publicatio.patch
Type: application/octet-stream
Part: 0
From 906bb9a40f1d5e75dd5ff43409fda5e86dc03b01 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 v5 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 | 20 ++-
src/backend/catalog/pg_publication.c | 107 ++++++++++++---
src/backend/commands/publicationcmds.c | 52 ++++++++
src/backend/parser/gram.y | 53 +++++++-
src/backend/replication/pgoutput/pgoutput.c | 34 ++++-
src/bin/psql/describe.c | 18 +++
src/bin/psql/tab-complete.in.c | 37 +++++-
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/publication.out | 56 +++++++-
src/test/regress/sql/publication.sql | 35 ++++-
src/test/subscription/t/037_except.pl | 139 +++++++++++++++++++-
12 files changed, 519 insertions(+), 36 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..f9ee7a1a4cf 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 <literal>EXCEPT</literal> for a given schema are
+ excluded from the publication.
</para>
<para>
@@ -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 a43d385c605..85c2528873f 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -529,6 +529,7 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
referenced;
List *relids = NIL;
int i;
+ HeapTuple existing;
bool inval_except_table;
rel = table_open(PublicationRelRelationId, RowExclusiveLock);
@@ -538,18 +539,33 @@ 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)))
+ existing = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pubid));
+
+ if (HeapTupleIsValid(existing))
{
+ Form_pg_publication_rel existing_form =
+ (Form_pg_publication_rel) GETSTRUCT(existing);
+ bool is_except = existing_form->prexcept;
+
+ ReleaseSysCache(existing);
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.%s\" cannot be added because it is listed in EXCEPT clause of publication \"%s\"",
+ get_namespace_name(RelationGetNamespace(targetrel)),
+ RelationGetRelationName(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);
@@ -620,19 +636,24 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
/*
* Determine whether EXCEPT tables require explicit relcache invalidation.
*
- * For CREATE PUBLICATION with EXCEPT tables, invalidation is skipped
- * here, as CreatePublication() function invalidates all relations as part
- * of defining a FOR ALL TABLES publication.
+ * For CREATE PUBLICATION with EXCEPT tables, invalidation is skipped here
+ * because the excluded tables were never included in the publication, so
+ * no subscriber cache entry needs to be cleared.
+ *
+ * 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).
*
- * 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.
+ * 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)
{
@@ -975,7 +996,8 @@ 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)
@@ -1225,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 = get_publication_relations(pubid, pub_partopt, true);
+
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;
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 440adb356ad..dfe3e1f9706 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -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,
@@ -968,6 +970,7 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
if (relations != NIL)
{
List *rels;
+ ListCell *lc;
rels = OpenTableList(relations);
TransformPubWhereClauses(rels, pstate->p_sourcetext,
@@ -977,6 +980,23 @@ 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(lc, rels)
+ {
+ PublicationRelInfo *pri = (PublicationRelInfo *) lfirst(lc);
+
+ explicitrelids = lappend_oid(explicitrelids,
+ RelationGetRelid(pri->relation));
+ }
+ }
+
PublicationAddTables(puboid, rels, true, NULL);
CloseTableList(rels);
}
@@ -989,6 +1009,38 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
*/
LockSchemaList(schemaidlist);
PublicationAddSchemas(puboid, schemaidlist, true, NULL);
+
+ if (exceptrelations != NIL)
+ {
+ List *rels;
+ ListCell *lc;
+
+ rels = 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(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(puboid, rels, true, NULL);
+ CloseTableList(rels);
+ }
}
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..cd8d0453521 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11272,7 +11272,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 [, ...] )] [, ...]
*
*****************************************************************************/
@@ -11332,23 +11332,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 +11395,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,11 +20788,14 @@ 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)
{
ListCell *cell;
+ ListCell *lc;
PublicationObjSpec *pubobj;
PublicationObjSpecType prevobjtype = PUBLICATIONOBJ_CONTINUATION;
@@ -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,35 @@ 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(lc, pubobj->except_tables)
+ {
+ PublicationObjSpec *eobj = (PublicationObjSpec *) lfirst(lc);
+
+ /*
+ * 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)
+ {
+ 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));
+ }
+ }
+ 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..942ddb51d3a 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,25 @@ 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.
+ */
+ if (am_partition)
+ {
+ List *pancestore = get_partition_ancestors(relid);
+
+ schemaExceptPubids =
+ GetRelationExcludedPublications(llast_oid(pancestore));
+ list_free(pancestore);
+ }
+ else
+ schemaExceptPubids = GetRelationExcludedPublications(relid);
+
/* Reload publications if needed before use. */
if (!publications_valid)
{
@@ -2269,7 +2289,15 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
ancestors,
&level);
- if (ancestor != InvalidOid)
+ /*
+ * The ancestor is only considered published if it is not
+ * in the EXCEPT clause of this schema publication.
+ * GetTopMostAncestorInPublication checks schema
+ * membership but does not account for the EXCEPT list, so
+ * we must filter that out here.
+ */
+ if (ancestor != InvalidOid &&
+ !list_member_oid(schemaExceptPubids, pub->oid))
{
ancestor_published = true;
if (pub->pubviaroot)
@@ -2281,7 +2309,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 +2389,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 db65d130fcb..6c9c7552627 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1064,6 +1064,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 "\
@@ -3784,8 +3793,32 @@ 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"))
+ {
+ if (strchr(previous_words[3], ',') == NULL)
+ {
+ set_completion_reference(previous_words[3]);
+ 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, ','))
+ {
+ char *schema_word = previous_words[previous_words_count - 8];
+
+ if (strchr(schema_word, ',') == NULL)
+ {
+ set_completion_reference(schema_word);
+ COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_schema);
+ }
+ }
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE", MatchAnyN) && !ends_with(prev_wd, ','))
+ COMPLETE_WITH(")");
/* Complete "CREATE PUBLICATION <name> [...] WITH" */
else if (Matches("CREATE", "PUBLICATION", MatchAnyN, "WITH", "("))
COMPLETE_WITH("publish", "publish_generated_columns", "publish_via_partition_root");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 91377a6cde3..a9177de0d43 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4493,6 +4493,7 @@ typedef struct PublicationObjSpec
PublicationObjSpecType pubobjtype; /* type of this publication object */
char *name;
PublicationTable *pubtable;
+ List *except_tables; /* tables to exclude (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 0345f6c5e47..e78192edd01 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -470,7 +470,61 @@ 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);
+-- 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 (implicitly qualified with the schema)
+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 is not allowed for FOR TABLE publications
+CREATE PUBLICATION testpub_except_err
+ FOR TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2 EXCEPT (TABLE pub_test.testpub_nopk);
+ERROR: EXCEPT is not allowed for TABLE publication objects
+LINE 2: FOR TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2 EXCEPT...
+ ^
+-- 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...
+ ^
+RESET client_min_messages;
+DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
+DROP PUBLICATION testpub_schema_except1, testpub_schema_except2;
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..9120902dfac 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -220,7 +220,40 @@ 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);
+
+-- 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 (implicitly qualified with the schema)
+CREATE PUBLICATION testpub_schema_except2
+ FOR TABLES IN SCHEMA pub_test EXCEPT (TABLE testpub_nopk, testpub_tbl_s1);
+\dRp+ testpub_schema_except2
+
+-- fail: EXCEPT is not allowed for FOR TABLE publications
+CREATE PUBLICATION testpub_except_err
+ FOR TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2 EXCEPT (TABLE pub_test.testpub_nopk);
+
+-- 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);
+
+RESET client_min_messages;
+DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
+DROP PUBLICATION testpub_schema_except1, testpub_schema_except2;
+
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..d2b1fb8f9ff 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -24,14 +24,16 @@ my $result;
sub test_except_root_partition
{
- my ($pubviaroot) = @_;
+ my ($pubviaroot, $pubsql) = @_;
+ $pubsql //=
+ "CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT (TABLE root1) 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 +225,138 @@ $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 WITH (publish_via_partition_root = false)");
+test_except_root_partition('true',
+ "$schema_pub WITH (publish_via_partition_root = true)");
+
+# ============================================
+# 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_pub AS SELECT generate_series(1,5) AS a;
+ CREATE TABLE sch1.tab_exc AS SELECT generate_series(1,5) AS a;
+ CREATE TABLE sch1.par (a int);
+ CREATE TABLE sch1.chi (b int) INHERITS (sch1.par);
+));
+
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ CREATE SCHEMA sch1;
+ CREATE TABLE sch1.tab_pub (a int);
+ CREATE TABLE sch1.tab_exc (a int);
+ CREATE TABLE sch1.par (a int);
+ CREATE TABLE sch1.chi (b int) INHERITS (sch1.par);
+));
+
+# Basic test: initial sync respects EXCEPT.
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.tab_exc)"
+);
+$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_pub");
+is($result, qq(5),
+ 'TABLES IN SCHEMA EXCEPT: initial sync copies included table');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_exc");
+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_pub VALUES (6);
+ INSERT INTO sch1.tab_exc VALUES (6);
+));
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_pub");
+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_exc");
+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.par)"
+);
+$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.chi 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.chi");
+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');
+
+# Truncate chi on the publisher so the next test starts with a clean slate.
+# (The previous test inserted rows into chi that would otherwise be copied by
+# the initial table sync of the next subscription.)
+$node_publisher->safe_psql('postgres', 'TRUNCATE sch1.chi');
+$node_subscriber->safe_psql('postgres', 'TRUNCATE sch1.chi');
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (TABLE ONLY sch1.par)"
+);
+$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.chi 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.chi");
+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');
+$node_publisher->safe_psql('postgres',
+ 'TRUNCATE sch1.par, sch1.chi, sch1.tab_exc');
+$node_subscriber->safe_psql('postgres',
+ 'TRUNCATE sch1.par, sch1.chi, sch1.tab_pub, sch1.tab_exc');
+
+# 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 +388,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)