On Tue, May 10, 2022 at 9:08 AM vignesh C <vignes...@gmail.com> wrote:
>
> On Fri, May 6, 2022 at 8:05 AM Peter Smith <smithpb2...@gmail.com> wrote:
> >
> > On Thu, Apr 28, 2022 at 9:32 PM Amit Kapila <amit.kapil...@gmail.com> wrote:
> > >
> > ...
> > >
> > > Another idea that occurred to me today for tables this is as follows:
> > > 1. Allow to mention except during create publication ... For All Tables.
> > > CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT TABLE t1,t2;
> > > 2. Allow to Reset it. This new syntax will reset all objects in the
> > > publications.
> > > Alter Publication ... RESET;
> > > 3. Allow to add it to an existing publication
> > > Alter Publication ... Add ALL TABLES [EXCEPT TABLE t1,t2];
> > >
> > > I think it can be extended in a similar way for schema syntax as well.
> > >
> >
> > If the proposed syntax ALTER PUBLICATION ... RESET will reset all the
> > objects in the publication then there still seems simple way to remove
> > only the EXCEPT list but leave everything else intact. IIUC to clear
> > just the EXCEPT list would require a 2 step process - 1) ALTER ...
> > RESET then 2) ALTER ... ADD ALL TABLES again.
> >
> > I was wondering if it might be useful to have a variation that *only*
> > resets the EXCEPT list, but still leaves everything else as-is?
> >
> > So, instead of:
> > ALTER PUBLICATION pubname RESET
>
> +1 for this syntax as this syntax can be extendable to include options
> like (except/all/etc) later.
> Currently we can support this syntax and can be extended later based
> on the requirements.
The attached patch has the implementation for "ALTER PUBLICATION
pubname RESET". This command will reset the publication to default
state which includes resetting the publication options, setting ALL
TABLES option to false and dropping the relations and schemas that are
associated with the publication.
Regards,
Vignesh
From c55befe6f53649babce1dd526b1c123b77731dcd Mon Sep 17 00:00:00 2001
From: Vigneshwaran C <vignes...@gmail.com>
Date: Thu, 12 May 2022 08:29:38 +0530
Subject: [PATCH v1] Add RESET option to Alter Publication which will reset the
publication with default values.
This patch adds a new RESET option to ALTER PUBLICATION which will reset
the publication to default state which includes resetting the publication
options, setting ALL TABLES option 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 | 29 +++++--
src/backend/commands/publicationcmds.c | 99 +++++++++++++++++++++++
src/backend/parser/gram.y | 9 +++
src/bin/psql/tab-complete.c | 2 +-
src/include/nodes/parsenodes.h | 3 +-
src/test/regress/expected/publication.out | 69 ++++++++++++++++
src/test/regress/sql/publication.sql | 37 +++++++++
7 files changed, 241 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index e2cce49471..29f3858de1 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>
@@ -65,7 +66,18 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
</para>
<para>
- The remaining variants change the owner and the name of the publication.
+ The <literal>OWNER</literal> clause will change the owner of the publication.
+ </para>
+
+ <para>
+ The <literal>RENAME</literal> clause will change the name of the publication.
+ </para>
+
+ <para>
+ The <literal>RESET</literal> clause will reset the publication to default
+ state which includes resetting the publication options, setting
+ <literal>ALL TABLES</literal> option to <literal>false</literal> and drop the
+ relations and schemas that are associated with the publication.
</para>
<para>
@@ -73,10 +85,11 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
Adding a table to a publication additionally requires owning that table.
The <literal>ADD ALL TABLES IN SCHEMA</literal> and
<literal>SET ALL TABLES IN SCHEMA</literal> to a publication requires the
- invoking user to be a superuser. To alter the owner, you must also be a
- direct or indirect member of the new owning role. The new owner must have
- <literal>CREATE</literal> privilege on the database. Also, the new owner
- of a <literal>FOR ALL TABLES</literal> or <literal>FOR ALL TABLES IN
+ invoking user to be a superuser. <literal>RESET</literal> of publication
+ requires invoking user to be a superuser. To alter the owner, you must also
+ be a direct or indirect member of the new owning role. The new owner must
+ have <literal>CREATE</literal> privilege on the database. Also, the new
+ owner of a <literal>FOR ALL TABLES</literal> or <literal>FOR ALL TABLES IN
SCHEMA</literal> publication must be a superuser. However, a superuser can
change the ownership of a publication regardless of these restrictions.
</para>
@@ -207,6 +220,12 @@ ALTER PUBLICATION sales_publication ADD ALL TABLES IN SCHEMA marketing, sales;
<structname>production_publication</structname>:
<programlisting>
ALTER PUBLICATION production_publication ADD TABLE users, departments, ALL TABLES IN SCHEMA production;
+</programlisting></para>
+
+ <para>
+ Resetting 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 6df0e6670f..c883c4f75a 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1104,6 +1104,103 @@ InvalidatePublicationRels(List *relids)
CacheInvalidateRelcacheAll();
}
+/*
+ * Reset the publication.
+ *
+ * Reset the publication options, publication relations and publication schemas.
+ */
+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 *schemas = NIL;
+ List *rels = NIL;
+ bool nulls[Natts_pg_publication];
+ bool replaces[Natts_pg_publication];
+ Datum values[Natts_pg_publication];
+ ObjectAddress obj;
+ ListCell *lc;
+ Oid prid;
+
+ /* 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));
+
+ /* Check and reset the options */
+ if (pubform->puballtables)
+ {
+ values[Anum_pg_publication_puballtables - 1] = BoolGetDatum(false);
+ replaces[Anum_pg_publication_puballtables - 1] = true;
+ }
+
+ if (!pubform->pubinsert)
+ {
+ values[Anum_pg_publication_pubinsert - 1] = BoolGetDatum(true);
+ replaces[Anum_pg_publication_pubinsert - 1] = true;
+ }
+
+ if (!pubform->pubupdate)
+ {
+ values[Anum_pg_publication_pubupdate - 1] = BoolGetDatum(true);
+ replaces[Anum_pg_publication_pubupdate - 1] = true;
+ }
+
+ if (!pubform->pubdelete)
+ {
+ values[Anum_pg_publication_pubdelete - 1] = BoolGetDatum(true);
+ replaces[Anum_pg_publication_pubdelete - 1] = true;
+ }
+
+ if (!pubform->pubtruncate)
+ {
+ values[Anum_pg_publication_pubtruncate - 1] = BoolGetDatum(true);
+ replaces[Anum_pg_publication_pubtruncate - 1] = true;
+ }
+
+ if (pubform->pubviaroot)
+ {
+ values[Anum_pg_publication_pubviaroot - 1] = BoolGetDatum(false);
+ replaces[Anum_pg_publication_pubviaroot - 1] = true;
+ }
+
+ tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls,
+ replaces);
+
+ /* Update the catalog. */
+ CatalogTupleUpdate(rel, &tup->t_self, tup);
+
+ /* Drop the schemas associated with the publication */
+ schemas = GetPublicationSchemas(pubid);
+ PublicationDropSchemas(pubid, schemas, false);
+
+ /* Drop the relations associated with the publication */
+ rels = GetPublicationRelations(pubid, PUBLICATION_PART_ROOT);
+ foreach(lc, rels)
+ {
+ Oid relid = lfirst_oid(lc);
+
+ prid = GetSysCacheOid2(PUBLICATIONRELMAP, Anum_pg_publication_rel_oid,
+ ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pubid));
+ if (!OidIsValid(prid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("relation \"%s\" is not part of the publication",
+ RelationGetRelationName(rel))));
+
+ ObjectAddressSet(obj, PublicationRelRelationId, prid);
+ performDeletion(&obj, DROP_CASCADE, 0);
+ }
+}
+
/*
* Add or remove table to/from publication.
*/
@@ -1415,6 +1512,8 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
if (stmt->options)
AlterPublicationOptions(pstate, stmt, rel, tup);
+ else if (stmt->action == AP_ReSetPublication)
+ AlterPublicationReset(pstate, stmt, rel, tup);
else
{
List *relations = NIL;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c9941d9cb4..755a861613 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10005,6 +10005,8 @@ pub_obj_list: PublicationObjSpec
*
* ALTER PUBLICATION name SET pub_obj [, ...]
*
+ * ALTER PUBLICATION name RESET
+ *
* pub_obj is one of:
*
* TABLE table_name [, ...]
@@ -10047,6 +10049,13 @@ AlterPublicationStmt:
n->action = AP_DropObjects;
$$ = (Node *)n;
}
+ | ALTER PUBLICATION name RESET
+ {
+ AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
+ n->pubname = $3;
+ n->action = AP_ReSetPublication;
+ $$ = (Node *)n;
+ }
;
/*****************************************************************************
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 588c0841fe..232f56a01c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1819,7 +1819,7 @@ psql_completion(const char *text, int start, int end)
/* 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("ALL TABLES IN SCHEMA", "TABLE");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9a716f3794..ac30c4f6c8 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4033,7 +4033,8 @@ typedef enum AlterPublicationAction
{
AP_AddObjects, /* add objects to publication */
AP_DropObjects, /* remove objects from publication */
- AP_SetObjects /* set list of objects */
+ AP_SetObjects, /* set list of objects */
+ AP_ReSetPublication /* reset the publication */
} AlterPublicationAction;
typedef struct AlterPublicationStmt
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 398c0f38f6..dc89094e08 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -1653,6 +1653,75 @@ DROP PUBLICATION pub;
DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
+-- Test for RESET PUBLICATION
+CREATE SCHEMA pub_sch1;
+CREATE TABLE pub_sch1.tbl1 (a int);
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_reset FOR ALL TABLES;
+RESET client_min_messages;
+-- Verify that 'ALL TABLES' option is reset
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | t | t | t | t | t | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1, ALL TABLES IN SCHEMA public;
+-- Verify that tables and schemas associated with the publication are dropped
+-- after RESET
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "pub_sch1.tbl1"
+Tables from schemas:
+ "public"
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH = '', PUBLISH_VIA_PARTITION_ROOT = 'true');
+-- Verify that publish option and publish via root option is reset
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | f | f | f | f | t
+(1 row)
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+(1 row)
+
+-- Verify that only superuser can execute RESET publication
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user2;
+SET ROLE regress_publication_user2;
+ALTER PUBLICATION testpub_reset RESET; -- fail
+ERROR: must be superuser to RESET publication
+SET ROLE regress_publication_user;
+DROP PUBLICATION testpub_reset;
+DROP TABLE pub_sch1.tbl1;
+DROP SCHEMA pub_sch1;
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 9eb86fd54f..696f723da6 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1057,6 +1057,43 @@ DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
+-- Test for RESET PUBLICATION
+CREATE SCHEMA pub_sch1;
+CREATE TABLE pub_sch1.tbl1 (a int);
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_reset FOR ALL TABLES;
+RESET client_min_messages;
+
+-- Verify that 'ALL TABLES' option is reset
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1, ALL TABLES IN SCHEMA public;
+
+-- Verify that tables and schemas associated with the publication are dropped
+-- after RESET
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH = '', PUBLISH_VIA_PARTITION_ROOT = 'true');
+
+-- Verify that publish option and publish via root option is reset
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+-- Verify that only superuser can execute RESET publication
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user2;
+SET ROLE regress_publication_user2;
+ALTER PUBLICATION testpub_reset RESET; -- fail
+SET ROLE regress_publication_user;
+
+DROP PUBLICATION testpub_reset;
+DROP TABLE pub_sch1.tbl1;
+DROP SCHEMA pub_sch1;
+
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
--
2.32.0
