v8-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABLE.patch
application/x-patch
Filename: v8-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABLE.patch
Type: application/x-patch
Part: 2
From 6b0adb50ccda7ace301d398e4f5fc43e14b74c98 Mon Sep 17 00:00:00 2001
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Fri, 29 May 2026 20:04:57 +0530
Subject: [PATCH v8 3/4] Add EXCEPT support to ALTER PUBLICATION ADD TABLES IN
SCHEMA
Extend the EXCEPT clause support to allow tables to be excluded when
adding a schema to a publication via ALTER PUBLICATION ... ADD.
Syntax:
ALTER PUBLICATION pub ADD TABLES IN SCHEMA s EXCEPT (TABLE s.t1);
Since pg_dump uses ALTER PUBLICATION ... ADD, support for it is
included in this patch.
---
doc/src/sgml/ref/alter_publication.sgml | 40 +++++++-
src/backend/catalog/pg_publication.c | 19 ++--
src/backend/commands/publicationcmds.c | 108 +++++++++++++++++++++-
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 | 15 +++
src/test/regress/expected/publication.out | 32 ++++++-
src/test/regress/sql/publication.sql | 20 +++-
src/test/subscription/t/037_except.pl | 32 +++++++
9 files changed, 317 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index aa32bb169e9..73f6375a66f 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -31,7 +31,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ]
- TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
+ TABLES IN SCHEMA <replaceable class="parameter">tables_in_schema</replaceable> [, ... ]
<phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase>
@@ -47,6 +47,10 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<replaceable class="parameter">table_object</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ]
+<phrase>and <replaceable class="parameter">tables_in_schema</replaceable> is:</phrase>
+
+ { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [ EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ]
+
<phrase>and <replaceable class="parameter">except_table_object</replaceable> is:</phrase>
TABLE <replaceable class="parameter">table_object</replaceable> [, ... ]
@@ -110,6 +114,14 @@ 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 the
+ publication. Using <literal>DROP TABLES IN SCHEMA</literal> on a publication
+ will automatically also remove any 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 +210,22 @@ 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>
+ When used with <literal>ADD TABLES IN SCHEMA</literal>, specifies
+ tables to be excluded from the publication. Each named
+ table must belong to the schema specified in the same
+ <literal>TABLES IN SCHEMA</literal> clause. Table names may be
+ schema-qualified or unqualified; unqualified names are implicitly
+ qualified with the schema named in the same clause. See
+ <xref linkend="sql-createpublication"/> for further details on the
+ semantics of <literal>EXCEPT</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
@@ -288,6 +316,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 internal, 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 4af0d74814a..b30eada51df 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -649,15 +649,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 cd39d6375cd..f9ae00a6c2f 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -70,6 +70,13 @@ static void PublicationDropTables(Oid pubid, List *rels, bool missing_ok);
static void PublicationAddSchemas(Oid pubid, List *schemas, bool if_not_exists,
AlterPublicationStmt *stmt);
static void PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok);
+static void AlterPublicationSchemas(AlterPublicationStmt *stmt,
+ HeapTuple tup, List *schemaidlist,
+ List *except_pubtables);
+static void AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt,
+ HeapTuple tup,
+ List *except_pubtables,
+ List *schemaidlist);
static char defGetGeneratedColsOption(DefElem *def);
@@ -1468,7 +1475,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
*/
static void
AlterPublicationSchemas(AlterPublicationStmt *stmt,
- HeapTuple tup, List *schemaidlist)
+ HeapTuple tup, List *schemaidlist,
+ List *except_pubtables)
{
Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
@@ -1545,6 +1553,98 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
*/
PublicationAddSchemas(pubform->oid, schemaidlist, true, stmt);
}
+
+ /*
+ * Increment the command counter so that is_schema_publication() in
+ * GetExcludedPublicationTables() can see the just-inserted schema
+ * rows when AlterPublicationSchemaExceptTables runs next.
+ */
+ if (stmt->action == AP_AddObjects || stmt->action == AP_SetObjects)
+ CommandCounterIncrement();
+
+ AlterPublicationSchemaExceptTables(stmt, tup, except_pubtables, schemaidlist);
+}
+
+/*
+ * Alter the EXCEPT list of a schema-level publication.
+ *
+ * Adds, removes, or replaces except-table entries in pg_publication_rel
+ * (rows with prexcept = true). These entries suppress publication of the
+ * named tables that would otherwise be covered by a FOR TABLES IN SCHEMA
+ * clause.
+ */
+static void
+AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt,
+ HeapTuple tup, List *except_pubtables,
+ List *schemaidlist)
+{
+ Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+ Oid pubid = pubform->oid;
+
+ /*
+ * Nothing to do if no EXCEPT entries.
+ */
+ if (!except_pubtables)
+ return;
+
+ /*
+ * This function handles EXCEPT entries for schema-level publications
+ * only. For FOR ALL TABLES publications, EXCEPT entries are already
+ * processed by AlterPublicationTables().
+ */
+ if (schemaidlist == NIL && !is_schema_publication(pubid))
+ return;
+
+ /*
+ * 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")));
+
+ /*
+ * XXX EXCEPT with SET is not currently implemented. Workaround: DROP and
+ * re-ADD the schema with the desired EXCEPT list.
+ */
+ if (stmt->action == AP_SetObjects)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("EXCEPT clause is not supported with SET in ALTER PUBLICATION"),
+ errhint("Drop and re-add the schema with the desired EXCEPT list.")));
+
+ if (stmt->action == AP_AddObjects)
+ {
+ List *rels;
+ List *explicitrelids;
+
+ rels = OpenTableList(except_pubtables);
+
+ explicitrelids = GetIncludedPublicationRelations(pubid,
+ PUBLICATION_PART_ROOT);
+
+ /*
+ * Validate that each excluded table is not also in the explicit table
+ * list (which would be contradictory).
+ */
+ foreach_ptr(PublicationRelInfo, pri, rels)
+ {
+ Oid relid = RelationGetRelid(pri->relation);
+
+ if (list_member_oid(explicitrelids, 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(pubid, rels, false, stmt);
+
+ CloseTableList(rels);
+ }
}
/*
@@ -1754,10 +1854,12 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
errmsg("publication \"%s\" does not exist",
stmt->pubname));
- relations = list_concat(relations, except_pubtables);
+ if (stmt->for_all_tables)
+ relations = list_concat(relations, except_pubtables);
+
AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
schemaidlist != NIL);
- AlterPublicationSchemas(stmt, tup, schemaidlist);
+ AlterPublicationSchemas(stmt, tup, schemaidlist, except_pubtables);
AlterPublicationAllFlags(stmt, rel, tup);
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 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..ed722140877 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 test_table);',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub11 WITH (publish = 'insert, update, delete, truncate');\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
+ 'CREATE PUBLICATION pub11 - ADD TABLES IN SCHEMA EXCEPT dump'
+ => {
+ 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 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, },
+ },
+
+ 'CREATE PUBLICATION pub12 - ADD TABLES IN SCHEMA EXCEPT dump'
+ => {
+ 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 831be27ad08..3ffe7c6e2e9 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2364,6 +2364,21 @@ 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(")");
/* 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 008c6cebaca..f56b0524ae9 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -564,12 +564,42 @@ 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;
+-- fail: non-existing table in EXCEPT clause
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table);
+ERROR: relation "pub_test.nonexistent_table" does not exist
+-- fail: EXCEPT table belongs to a different schema
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1);
+ERROR: table "public.testpub_tbl1" in EXCEPT clause does not belong to schema "pub_test"
+LINE 1: ...xcept ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE public.tes...
+ ^
+-- fail: TABLE keyword is required for the first entry in EXCEPT clause
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
+ERROR: syntax error at or near "testpub_nopk"
+LINE 1: ...lter_except ADD TABLES IN SCHEMA pub_test EXCEPT (testpub_no...
+ ^
+-- ADD: qualified and unqualified names; unqualified is implicitly qualified with the schema
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2);
+\dRp+ testpub_alter_except
+ Publication testpub_alter_except
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f | f | t | t | t | t | none | f |
+Tables from schemas:
+ "pub_test"
+Except tables:
+ "pub_test.testpub_tbl_s1"
+ "pub_test.testpub_tbl_s2"
+
-- Cleanup
RESET client_min_messages;
DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
DROP TABLE pub_test.testpub_parted_s CASCADE;
DROP TABLE testpub_nopk, testpub_tbl_s1;
-DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi;
+DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi, testpub_alter_except;
---------------------------------------------
-- Tests for publications with SEQUENCES
---------------------------------------------
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 9162d4d15a5..072d50050cd 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -278,12 +278,30 @@ 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;
+
+-- fail: non-existing table in EXCEPT clause
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table);
+
+-- fail: EXCEPT table belongs to a different schema
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1);
+
+-- fail: TABLE keyword is required for the first entry in EXCEPT clause
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
+
+-- ADD: qualified and unqualified names; unqualified is implicitly qualified with the schema
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2);
+\dRp+ testpub_alter_except
+
-- Cleanup
RESET client_min_messages;
DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
DROP TABLE pub_test.testpub_parted_s CASCADE;
DROP TABLE testpub_nopk, testpub_tbl_s1;
-DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi;
+DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi, testpub_alter_except;
---------------------------------------------
-- Tests for publications with SEQUENCES
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 18c7b2c1fca..0ba6d6f8bb2 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -347,6 +347,38 @@ is($result, qq(5),
$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+# ============================================
+# ALTER PUBLICATION EXCEPT for TABLES IN SCHEMA
+# ============================================
+
+# Truncate subscriber tables to remove data accumulated from previous tests.
+$node_subscriber->safe_psql('postgres',
+ 'TRUNCATE sch1.tab_published, sch1.tab_excluded, sch1.parent, sch1.child');
+
+# ADD: add a schema with an excepted table; verify the except entry takes effect.
+$node_publisher->safe_psql('postgres', "CREATE PUBLICATION sch_pub");
+$node_publisher->safe_psql('postgres',
+ "ALTER PUBLICATION sch_pub ADD TABLES IN SCHEMA sch1 EXCEPT (TABLE sch1.tab_excluded)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_published");
+is($result, qq(6),
+ 'ALTER ... ADD TABLES IN SCHEMA EXCEPT: included table synced');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab_excluded");
+is($result, qq(0),
+ 'ALTER ... ADD TABLES IN SCHEMA EXCEPT: excluded table not synced');
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
# Cleanup schema tables before the multi-publication section.
$node_publisher->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
$node_subscriber->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
--
2.50.1 (Apple Git-155)