v6-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABLE.patch
application/octet-stream
Filename: v6-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABLE.patch
Type: application/octet-stream
Part: 2
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)