v29-0001-Add-RESET-clause-to-Alter-Publication-which-will.patch
application/octet-stream
Filename: v29-0001-Add-RESET-clause-to-Alter-Publication-which-will.patch
Type: application/octet-stream
Part: 0
From 23ef8bf32b7292b5df4cccfdebcf40852fdc188d Mon Sep 17 00:00:00 2001
From: Shlok Kyal <shlok.kyal.oss@gmail.com>
Date: Thu, 30 Oct 2025 10:52:56 +0530
Subject: [PATCH v29 1/4] Add RESET clause to Alter Publication which will
reset the publication with default values.
This patch adds a new RESET clause to ALTER PUBLICATION which will reset
the publication to the default state. This includes resetting the publication
parameters, setting ALL TABLES and ALL SEQUENCES flags to false and dropping
the relations and schemas that are associated with the publication.
Usage:
ALTER PUBLICATION pub1 RESET;
---
doc/src/sgml/ref/alter_publication.sgml | 170 ++++++++++++++--------
src/backend/commands/publicationcmds.c | 130 ++++++++++++++++-
src/backend/parser/gram.y | 13 +-
src/bin/psql/tab-complete.in.c | 2 +-
src/include/catalog/pg_publication.h | 8 +
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/publication.out | 58 ++++++++
src/test/regress/sql/publication.sql | 34 +++++
8 files changed, 349 insertions(+), 67 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 028770f2149..7d7e6341921 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -27,6 +27,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP <replac
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RESET
<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
@@ -49,46 +50,119 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<para>
The command <command>ALTER PUBLICATION</command> can change the attributes
- of a publication.
- </para>
+ of a publication. There are several subforms described below.
- <para>
- The first three variants change which tables/schemas are part of the
- publication. The <literal>SET</literal> clause will replace the list of
- tables/schemas in the publication with the specified list; the existing
- tables/schemas that were present in the publication will be removed. The
- <literal>ADD</literal> and <literal>DROP</literal> clauses will add and
- remove one or more tables/schemas from the publication. Note that adding
- tables/schemas to a publication that is already subscribed to will require an
- <link linkend="sql-altersubscription-params-refresh-publication">
- <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal></link> action on the
- subscribing side in order to become effective. Note also that
- <literal>DROP TABLES IN SCHEMA</literal> will not drop any schema tables
- that were specified using
- <link linkend="sql-createpublication-params-for-table"><literal>FOR TABLE</literal></link>/
- <literal>ADD TABLE</literal>.
- </para>
+ <variablelist>
+ <varlistentry>
+ <term><literal>ADD <replaceable class="parameter">publication_object</replaceable> [, ...]</literal></term>
+ <listitem>
+ <para>
+ This form adds one or more tables/schemas to the publication.
+ Note that adding tables/schemas to a publication that is already
+ subscribed to will require an
+ <link linkend="sql-altersubscription-params-refresh-publication">
+ <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal></link>
+ action on the subscribing side for the changes to take effect.
+ </para>
+ </listitem>
+ </varlistentry>
- <para>
- The fourth variant of this command listed in the synopsis can change
- all of the publication properties specified in
- <xref linkend="sql-createpublication"/>. Properties not mentioned in the
- command retain their previous settings.
- </para>
+ <varlistentry>
+ <term><literal>SET <replaceable class="parameter">publication_object</replaceable> [, ...]</literal></term>
+ <listitem>
+ <para>
+ This form replaces the list of tables/schemas in the publication with the
+ specified list; the existing tables/schemas that were present in the
+ publication are removed.
+ </para>
+ </listitem>
+ </varlistentry>
- <para>
- The remaining variants change the owner and the name of the publication.
+ <varlistentry>
+ <term><literal>DROP <replaceable class="parameter">publication_object</replaceable> [, ...]</literal></term>
+ <listitem>
+ <para>
+ This form removes one or more tables/schemas from the publication.
+ Note also that <literal>DROP TABLES IN SCHEMA</literal> will not drop any
+ schema tables that were specified using
+ <link linkend="sql-createpublication-params-for-table"><literal>FOR TABLE</literal></link>
+ or <literal>ADD TABLE</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
+ <listitem>
+ <para>
+ This form can change all of the publication properties specified in
+ <xref linkend="sql-createpublication"/>. Properties not mentioned in the
+ command retain their previous settings. It is not applicable to
+ sequences.
+ </para>
+ <caution>
+ <para>
+ Altering the <literal>publish_via_partition_root</literal> parameter can
+ lead to data loss or duplication at the subscriber because it changes
+ the identity and schema of the published tables. Note this happens only
+ when a partition root table is specified as the replication target.
+ </para>
+ <para>
+ This problem can be avoided by refraining from modifying partition leaf
+ tables after the <command>ALTER PUBLICATION ... SET</command> until the
+ <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>
+ is executed and by only refreshing using the <literal>copy_data = off</literal>
+ option.
+ </para>
+ </caution>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }</literal></term>
+ <listitem>
+ <para>
+ This form changes the owner of the publication.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RENAME TO <replaceable>new_name</replaceable></literal></term>
+ <listitem>
+ <para>
+ This form changes the name of the publication.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RESET</literal></term>
+ <listitem>
+ <para>
+ This form resets the publication to its default state. This includes
+ resetting all publication parameters, setting
+ <link linkend="catalog-pg-publication"><structname>pg_publication</structname></link>.<structfield>puballtables</structfield>
+ and
+ <link linkend="catalog-pg-publication"><structname>pg_publication</structname></link>.<structfield>puballsequences</structfield>
+ to <literal>false</literal>, and removing all tables and schemas that were
+ explicitly added to the publication.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
</para>
<para>
You must own the publication to use <command>ALTER PUBLICATION</command>.
Adding a table to a publication additionally requires owning that table.
- The <literal>ADD TABLES IN SCHEMA</literal> and
- <literal>SET TABLES IN SCHEMA</literal> to a publication requires the
- invoking user to be a superuser.
- To alter the owner, you must be able to <literal>SET ROLE</literal> to the
- new owning role, and that role must have <literal>CREATE</literal>
- privilege on the database.
+ The <literal>ADD TABLES IN SCHEMA</literal>,
+ <literal>SET TABLES IN SCHEMA</literal> to a publication and
+ <literal>RESET</literal> of publication requires the invoking user to be a
+ superuser. To alter the owner, you must be able to
+ <literal>SET ROLE</literal> to the new owning role, and that role must have
+ <literal>CREATE</literal> privilege on the database.
Also, the new owner of a
<link linkend="sql-createpublication-params-for-tables-in-schema"><literal>FOR TABLES IN SCHEMA</literal></link>
or <link linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL TABLES</literal></link>
@@ -156,32 +230,6 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
</listitem>
</varlistentry>
- <varlistentry>
- <term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
- <listitem>
- <para>
- This clause alters publication parameters originally set by
- <xref linkend="sql-createpublication"/>. See there for more information.
- This clause is not applicable to sequences.
- </para>
- <caution>
- <para>
- Altering the <literal>publish_via_partition_root</literal> parameter can
- lead to data loss or duplication at the subscriber because it changes
- the identity and schema of the published tables. Note this happens only
- when a partition root table is specified as the replication target.
- </para>
- <para>
- This problem can be avoided by refraining from modifying partition leaf
- tables after the <command>ALTER PUBLICATION ... SET</command> until the
- <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>
- is executed and by only refreshing using the <literal>copy_data = off</literal>
- option.
- </para>
- </caution>
- </listitem>
- </varlistentry>
-
<varlistentry>
<term><replaceable class="parameter">new_owner</replaceable></term>
<listitem>
@@ -240,6 +288,12 @@ ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA marketing, sales;
<structname>production_publication</structname>:
<programlisting>
ALTER PUBLICATION production_publication ADD TABLE users, departments, TABLES IN SCHEMA production;
+</programlisting></para>
+
+ <para>
+ Reset the publication <structname>production_publication</structname>:
+<programlisting>
+ALTER PUBLICATION production_publication RESET;
</programlisting></para>
</refsect1>
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 1faf3a8c372..4f8342f721c 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -90,12 +90,12 @@ parse_publication_options(ParseState *pstate,
*publish_generated_columns_given = false;
/* defaults */
- pubactions->pubinsert = true;
- pubactions->pubupdate = true;
- pubactions->pubdelete = true;
- pubactions->pubtruncate = true;
- *publish_via_partition_root = false;
- *publish_generated_columns = PUBLISH_GENCOLS_NONE;
+ pubactions->pubinsert = PUB_DEFAULT_ACTION_INSERT;
+ pubactions->pubupdate = PUB_DEFAULT_ACTION_UPDATE;
+ pubactions->pubdelete = PUB_DEFAULT_ACTION_DELETE;
+ pubactions->pubtruncate = PUB_DEFAULT_ACTION_TRUNCATE;
+ *publish_via_partition_root = PUB_DEFAULT_VIA_ROOT;
+ *publish_generated_columns = PUB_DEFAULT_GENCOLS;
/* Parse options */
foreach(lc, options)
@@ -1209,6 +1209,122 @@ InvalidatePublicationRels(List *relids)
CacheInvalidateRelcacheAll();
}
+/*
+ * Reset the publication.
+ *
+ * Reset the publication parameters, setting ALL TABLES and ALL SEQUENCES flag
+ * to false and drop all relations and schemas that are associated with the
+ * publication.
+ */
+static void
+AlterPublicationReset(ParseState *pstate, AlterPublicationStmt *stmt,
+ Relation rel, HeapTuple tup)
+{
+ Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+ Oid pubid = pubform->oid;
+ List *schemaids = NIL;
+ List *rels = NIL;
+ List *relids = NIL;
+ bool nulls[Natts_pg_publication];
+ bool replaces[Natts_pg_publication];
+ Datum values[Natts_pg_publication];
+
+ /* RESET publication requires superuser */
+ if (!superuser())
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to RESET publication"));
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(nulls));
+ memset(replaces, false, sizeof(replaces));
+
+ /* Reset the publication parameters */
+ values[Anum_pg_publication_pubinsert - 1] = BoolGetDatum(PUB_DEFAULT_ACTION_INSERT);
+ replaces[Anum_pg_publication_pubinsert - 1] = true;
+
+ values[Anum_pg_publication_pubupdate - 1] = BoolGetDatum(PUB_DEFAULT_ACTION_UPDATE);
+ replaces[Anum_pg_publication_pubupdate - 1] = true;
+
+ values[Anum_pg_publication_pubdelete - 1] = BoolGetDatum(PUB_DEFAULT_ACTION_DELETE);
+ replaces[Anum_pg_publication_pubdelete - 1] = true;
+
+ values[Anum_pg_publication_pubtruncate - 1] = BoolGetDatum(PUB_DEFAULT_ACTION_TRUNCATE);
+ replaces[Anum_pg_publication_pubtruncate - 1] = true;
+
+ values[Anum_pg_publication_pubviaroot - 1] = BoolGetDatum(PUB_DEFAULT_VIA_ROOT);
+ replaces[Anum_pg_publication_pubviaroot - 1] = true;
+
+ values[Anum_pg_publication_pubgencols - 1] = CharGetDatum(PUB_DEFAULT_GENCOLS);
+ replaces[Anum_pg_publication_pubgencols - 1] = true;
+
+ values[Anum_pg_publication_puballtables - 1] = BoolGetDatum(false);
+ replaces[Anum_pg_publication_puballtables - 1] = true;
+
+ values[Anum_pg_publication_puballsequences - 1] = BoolGetDatum(false);
+ replaces[Anum_pg_publication_puballsequences - 1] = true;
+
+ /*
+ * Lock the publication so nobody else can do anything with it. This
+ * prevents concurrent publication parameter changes, add/drop tables(s)
+ * to the publication and add/drop schema(s) to the publication.
+ */
+ LockDatabaseObject(PublicationRelationId, pubid, 0,
+ AccessExclusiveLock);
+
+ /*
+ * It is possible that by the time we acquire the lock on publication,
+ * concurrent DDL has removed it. We can test this by checking the
+ * existence of publication. We get the tuple again to avoid the risk of
+ * any publication option getting changed.
+ */
+ tup = SearchSysCacheCopy1(PUBLICATIONOID, ObjectIdGetDatum(pubid));
+ if (!HeapTupleIsValid(tup))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("publication \"%s\" does not exist",
+ stmt->pubname));
+
+ if (pubform->puballtables)
+ CacheInvalidateRelcacheAll();
+
+ tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls,
+ replaces);
+
+ /* Update the catalog. */
+ CatalogTupleUpdate(rel, &tup->t_self, tup);
+
+ /* Remove the associated schemas from the publication */
+ schemaids = GetPublicationSchemas(pubid);
+
+ /*
+ * Schema lock is held until the publication is altered to prevent
+ * concurrent schema deletion.
+ */
+ LockSchemaList(schemaids);
+
+ /* Remove Schemas */
+ PublicationDropSchemas(pubid, schemaids, true);
+
+ /* Get all relations associated with the publication */
+ relids = GetPublicationRelations(pubid, PUBLICATION_PART_ROOT);
+
+ foreach_oid(relid, relids)
+ {
+ PublicationRelInfo *rel;
+
+ rel = palloc(sizeof(PublicationRelInfo));
+ rel->whereClause = NULL;
+ rel->columns = NIL;
+ rel->relation = table_open(relid, ShareUpdateExclusiveLock);
+ rels = lappend(rels, rel);
+ }
+
+ /* Remove the associated relations from the publication */
+ PublicationDropTables(pubid, rels, true);
+ CloseTableList(rels);
+}
+
/*
* Add or remove table to/from publication.
*/
@@ -1553,6 +1669,8 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
if (stmt->options)
AlterPublicationOptions(pstate, stmt, rel, tup);
+ else if (stmt->action == AP_Reset)
+ AlterPublicationReset(pstate, stmt, rel, tup);
else
{
List *relations = NIL;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c3a0a354a9c..a8b9ae6182d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10904,15 +10904,17 @@ pub_all_obj_type_list: PublicationAllObjSpec
*
* ALTER PUBLICATION name ADD pub_obj [, ...]
*
- * ALTER PUBLICATION name DROP pub_obj [, ...]
- *
* ALTER PUBLICATION name SET pub_obj [, ...]
*
+ * ALTER PUBLICATION name DROP pub_obj [, ...]
+ *
* pub_obj is one of:
*
* TABLE table_name [, ...]
* TABLES IN SCHEMA schema_name [, ...]
*
+ * ALTER PUBLICATION name RESET
+ *
*****************************************************************************/
AlterPublicationStmt:
@@ -10954,6 +10956,13 @@ AlterPublicationStmt:
n->action = AP_DropObjects;
$$ = (Node *) n;
}
+ | ALTER PUBLICATION name RESET
+ {
+ AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
+ n->pubname = $3;
+ n->action = AP_Reset;
+ $$ = (Node *)n;
+ }
;
/*****************************************************************************
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 20d7a65c614..83599de2225 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2289,7 +2289,7 @@ match_previous_words(int pattern_id,
/* ALTER PUBLICATION <name> */
else if (Matches("ALTER", "PUBLICATION", MatchAny))
- COMPLETE_WITH("ADD", "DROP", "OWNER TO", "RENAME TO", "SET");
+ COMPLETE_WITH("ADD", "DROP", "OWNER TO", "RENAME TO", "RESET", "SET");
/* ALTER PUBLICATION <name> ADD */
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD"))
COMPLETE_WITH("TABLES IN SCHEMA", "TABLE");
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 22f48bb8975..641017e9496 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -152,6 +152,14 @@ extern Publication *GetPublication(Oid pubid);
extern Publication *GetPublicationByName(const char *pubname, bool missing_ok);
extern List *GetRelationPublications(Oid relid);
+/* default values for flags and publication parameters */
+#define PUB_DEFAULT_ACTION_INSERT true
+#define PUB_DEFAULT_ACTION_UPDATE true
+#define PUB_DEFAULT_ACTION_DELETE true
+#define PUB_DEFAULT_ACTION_TRUNCATE true
+#define PUB_DEFAULT_VIA_ROOT false
+#define PUB_DEFAULT_GENCOLS PUBLISH_GENCOLS_NONE
+
/*---------
* Expected values for pub_partopt parameter of GetPublicationRelations(),
* which allows callers to specify which partitions of partitioned tables
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d14294a4ece..8cf75724a7b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4326,6 +4326,7 @@ typedef enum AlterPublicationAction
AP_AddObjects, /* add objects to publication */
AP_DropObjects, /* remove objects from publication */
AP_SetObjects, /* set list of objects */
+ AP_Reset, /* reset the publication */
} AlterPublicationAction;
typedef struct AlterPublicationStmt
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index e72d1308967..ce5b3b649d5 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -2009,7 +2009,65 @@ Tables:
DROP PUBLICATION pub1;
DROP PUBLICATION pub2;
DROP TABLE gencols;
+-- ======================================================
+-- Tests for ALTER PUBLICATION ... RESET
+CREATE SCHEMA pub_sch1;
+CREATE TABLE pub_sch1.tbl1 (a int);
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_reset FOR ALL TABLES, ALL SEQUENCES;
RESET client_min_messages;
+-- Verify that only superuser can reset a publication
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user2;
+SET ROLE regress_publication_user2;
+ALTER PUBLICATION testpub_reset RESET; -- fail - must be superuser
+ERROR: must be superuser to RESET publication
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user;
+SET ROLE regress_publication_user;
+-- Verify that 'ALL TABLES', 'ALL SEQUENCES' flags are reset
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | t | t | t | t | t | none | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
+(1 row)
+
+-- Verify that a publication RESET removes the associated tables and
+-- schemas, and sets default values for publication parameters 'publish',
+-- 'publish_via_partition_root', and 'publish_generated_columns'.
+ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1, TABLES IN SCHEMA public;
+ALTER PUBLICATION testpub_reset SET (publish_via_partition_root = 'true');
+ALTER PUBLICATION testpub_reset SET (publish = '');
+ALTER PUBLICATION testpub_reset SET (publish_generated_columns = stored);
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | f | f | f | f | stored | t
+Tables:
+ "pub_sch1.tbl1"
+Tables from schemas:
+ "public"
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
+(1 row)
+
+DROP PUBLICATION testpub_reset;
+DROP TABLE pub_sch1.tbl1;
+DROP SCHEMA pub_sch1;
+-- ======================================================
-- Test that the INSERT ON CONFLICT command correctly checks REPLICA IDENTITY
-- when the target table is published.
CREATE TABLE testpub_insert_onconfl_no_ri (a int unique, b int);
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 00390aecd47..f0432f67b4a 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1267,9 +1267,43 @@ ALTER PUBLICATION pub2 SET TABLE gencols(a, gen1);
DROP PUBLICATION pub1;
DROP PUBLICATION pub2;
DROP TABLE gencols;
+-- ======================================================
+-- Tests for ALTER PUBLICATION ... RESET
+CREATE SCHEMA pub_sch1;
+CREATE TABLE pub_sch1.tbl1 (a int);
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_reset FOR ALL TABLES, ALL SEQUENCES;
RESET client_min_messages;
+-- Verify that only superuser can reset a publication
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user2;
+SET ROLE regress_publication_user2;
+ALTER PUBLICATION testpub_reset RESET; -- fail - must be superuser
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user;
+SET ROLE regress_publication_user;
+
+-- Verify that 'ALL TABLES', 'ALL SEQUENCES' flags are reset
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+-- Verify that a publication RESET removes the associated tables and
+-- schemas, and sets default values for publication parameters 'publish',
+-- 'publish_via_partition_root', and 'publish_generated_columns'.
+ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1, TABLES IN SCHEMA public;
+ALTER PUBLICATION testpub_reset SET (publish_via_partition_root = 'true');
+ALTER PUBLICATION testpub_reset SET (publish = '');
+ALTER PUBLICATION testpub_reset SET (publish_generated_columns = stored);
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+DROP PUBLICATION testpub_reset;
+DROP TABLE pub_sch1.tbl1;
+DROP SCHEMA pub_sch1;
+-- ======================================================
+
-- Test that the INSERT ON CONFLICT command correctly checks REPLICA IDENTITY
-- when the target table is published.
CREATE TABLE testpub_insert_onconfl_no_ri (a int unique, b int);
--
2.34.1