v29-0002-Support-ADD-ALL-TABLES-in-ALTER-PUBLICATION.patch
application/octet-stream
Filename: v29-0002-Support-ADD-ALL-TABLES-in-ALTER-PUBLICATION.patch
Type: application/octet-stream
Part: 2
From acb7ddbb4a53776c9451a210bed3d53781c81e22 Mon Sep 17 00:00:00 2001
From: Shlok Kyal <shlok.kyal.oss@gmail.com>
Date: Mon, 1 Dec 2025 16:17:53 +0530
Subject: [PATCH v29 2/4] Support ADD ALL TABLES in ALTER PUBLICATION
This patch adds support for using ADD ALL TABLES in ALTER PUBLICATION,
allowing an existing publication to be changed into an ALL TABLES
publication. This command is permitted only when the publication have
no tables or schemas explicitly added and its ALL TABLES flag is not
set.
Usage:
ALTER PUBLICATION pub1 ADD ALL TABLES
---
doc/src/sgml/logical-replication.sgml | 10 +--
doc/src/sgml/ref/alter_publication.sgml | 14 +++-
src/backend/commands/publicationcmds.c | 83 +++++++++++++++++++++--
src/backend/parser/gram.y | 10 +++
src/bin/psql/tab-complete.in.c | 2 +-
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/publication.out | 62 +++++++++++++++++
src/test/regress/sql/publication.sql | 42 ++++++++++++
8 files changed, 214 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index aa013f348d4..c420469feaa 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -2550,10 +2550,12 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
</para>
<para>
- To add tables to a publication, the user must have ownership rights on the
- table. To add all tables in schema to a publication, the user must be a
- superuser. To create a publication that publishes all tables, all tables in
- schema, or all sequences automatically, the user must be a superuser.
+ To create a publication using <literal>FOR ALL TABLES</literal>,
+ <literal>FOR ALL SEQUENCES</literal> or
+ <literal>FOR TABLES IN SCHEMA</literal>, the user must be a superuser. To add
+ <literal>ALL TABLES</literal> or <literal>TABLES IN SCHEMA</literal> to a
+ publication, the user must be a superuser. To add tables to a publication,
+ the user must have ownership rights on the table.
</para>
<para>
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 7d7e6341921..0ab2a9d007e 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -24,6 +24,7 @@ PostgreSQL documentation
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD <replaceable class="parameter">publication_object</replaceable> [, ...]
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET <replaceable class="parameter">publication_object</replaceable> [, ...]
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP <replaceable class="parameter">publication_drop_object</replaceable> [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD ALL TABLES
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>
@@ -91,6 +92,16 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RESET
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>ADD ALL TABLES</literal></term>
+ <listitem>
+ <para>
+ This form adds all tables to the publication. This requires the
+ publication to not have any existing table or schema list.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
@@ -157,7 +168,8 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RESET
<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>,
+ The <literal>ADD ALL TABLES</literal>,
+ <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
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 4f8342f721c..7a9020ad43f 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1325,6 +1325,79 @@ AlterPublicationReset(ParseState *pstate, AlterPublicationStmt *stmt,
CloseTableList(rels);
}
+/*
+ * Check whether we can alter the publication to add ALL TABLES.
+ *
+ * It is not allowed if the publication already is defined as ALL TABLES, or
+ * if there are any schemas or tables associated with the publication.
+ */
+static void
+CheckAlterPublicationAllTables(HeapTuple tup)
+{
+ List *pubobjs;
+ Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+
+ if (pubform->puballtables)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("publication \"%s\" is already defined as FOR ALL TABLES",
+ NameStr(pubform->pubname)));
+
+ pubobjs = GetPublicationSchemas(pubform->oid);
+ if (list_length(pubobjs))
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("publication \"%s\" has schemas associated with it",
+ NameStr(pubform->pubname)),
+ errdetail("ALL TABLES cannot be added when schemas are associated with the publication."));
+
+ pubobjs = GetPublicationRelations(pubform->oid, PUBLICATION_PART_ROOT);
+ if (list_length(pubobjs))
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("publication \"%s\" has tables associated with it",
+ NameStr(pubform->pubname)),
+ errdetail("ALL TABLES cannot be added when tables are associated with the publication."));
+}
+
+/*
+ * Set publication to publish all tables.
+ */
+static void
+AlterPublicationSetAllTables(Relation rel, HeapTuple tup)
+{
+ Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+ bool nulls[Natts_pg_publication];
+ bool replaces[Natts_pg_publication];
+ Datum values[Natts_pg_publication];
+
+ /* Add ALL TABLES to the publication requires superuser */
+ if (!superuser())
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to ADD ALL TABLES to the publication"));
+
+ /* Lock the publication so nobody else can do anything with it. */
+ LockDatabaseObject(PublicationRelationId, pubform->oid, 0,
+ AccessExclusiveLock);
+
+ CheckAlterPublicationAllTables(tup);
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(nulls));
+ memset(replaces, false, sizeof(replaces));
+
+ /* Set ALL TABLES flag */
+ values[Anum_pg_publication_puballtables - 1] = BoolGetDatum(true);
+ replaces[Anum_pg_publication_puballtables - 1] = true;
+
+ tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls,
+ replaces);
+
+ /* Update the catalog. */
+ CatalogTupleUpdate(rel, &tup->t_self, tup);
+}
+
/*
* Add or remove table to/from publication.
*/
@@ -1667,6 +1740,9 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_PUBLICATION,
stmt->pubname);
+ if (stmt->for_all_tables)
+ AlterPublicationSetAllTables(rel, tup);
+
if (stmt->options)
AlterPublicationOptions(pstate, stmt, rel, tup);
else if (stmt->action == AP_Reset)
@@ -1680,10 +1756,7 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
&schemaidlist);
- CheckAlterPublication(stmt, tup, relations, schemaidlist);
-
heap_freetuple(tup);
-
/* Lock the publication so nobody else can do anything with it. */
LockDatabaseObject(PublicationRelationId, pubid, 0,
AccessExclusiveLock);
@@ -1692,7 +1765,7 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
* 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.
+ * of any publication option or ALL TABLES flag getting changed.
*/
tup = SearchSysCacheCopy1(PUBLICATIONOID, ObjectIdGetDatum(pubid));
if (!HeapTupleIsValid(tup))
@@ -1701,6 +1774,8 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
errmsg("publication \"%s\" does not exist",
stmt->pubname));
+ CheckAlterPublication(stmt, tup, relations, schemaidlist);
+
AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
schemaidlist != NIL);
AlterPublicationSchemas(stmt, tup, schemaidlist);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a8b9ae6182d..9d648ccb47b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10913,6 +10913,8 @@ pub_all_obj_type_list: PublicationAllObjSpec
* TABLE table_name [, ...]
* TABLES IN SCHEMA schema_name [, ...]
*
+ * ALTER PUBLICATION name ADD ALL TABLES
+ *
* ALTER PUBLICATION name RESET
*
*****************************************************************************/
@@ -10956,6 +10958,14 @@ AlterPublicationStmt:
n->action = AP_DropObjects;
$$ = (Node *) n;
}
+ | ALTER PUBLICATION name ADD_P ALL TABLES
+ {
+ AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
+ n->pubname = $3;
+ n->for_all_tables = true;
+ n->action = AP_AddObjects;
+ $$ = (Node *)n;
+ }
| ALTER PUBLICATION name RESET
{
AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 83599de2225..b1175e0c08b 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2292,7 +2292,7 @@ match_previous_words(int pattern_id,
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");
+ COMPLETE_WITH("ALL TABLES", "TABLES IN SCHEMA", "TABLE");
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
else if (HeadMatches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE") &&
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 8cf75724a7b..c22d75e80a2 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4342,6 +4342,7 @@ typedef struct AlterPublicationStmt
* objects.
*/
List *pubobjects; /* Optional list of publication objects */
+ bool for_all_tables; /* Special publication for all tables in db */
AlterPublicationAction action; /* What action to perform with the given
* objects */
} AlterPublicationStmt;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index ce5b3b649d5..ec12f7cfbaa 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -2064,6 +2064,68 @@ ALTER PUBLICATION testpub_reset RESET;
regress_publication_user | f | f | t | t | t | t | none | f
(1 row)
+-- ======================================================
+-- Tests for ALTER PUBLICATION ... ADD ALL TABLES
+-- Verify that only superuser can ADD ALL TABLES
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user2;
+SET ROLE regress_publication_user2;
+ALTER PUBLICATION testpub_reset ADD ALL TABLES; -- fail - must be superuser
+ERROR: must be superuser to ADD ALL TABLES to the publication
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user;
+SET ROLE regress_publication_user;
+-- Can add ALL TABLES to an empty publication
+DROP PUBLICATION testpub_reset;
+CREATE PUBLICATION testpub_reset;
+WARNING: "wal_level" is insufficient to publish logical changes
+HINT: Set "wal_level" to "logical" before creating subscriptions.
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+(1 row)
+
+-- Can't add ALL TABLES to 'ALL TABLES' publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+ERROR: publication "testpub_reset" is already defined as FOR ALL TABLES
+-- Can add ALL TABLES to ALL SEQUENCES publication
+DROP PUBLICATION testpub_reset;
+CREATE PUBLICATION testpub_reset for ALL SEQUENCES;
+WARNING: "wal_level" is insufficient to publish logical changes
+HINT: Set "wal_level" to "logical" before creating subscriptions.
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+\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)
+
+-- Can't add ALL TABLES to 'FOR TABLE' publication
+ALTER PUBLICATION testpub_reset RESET;
+ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1;
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+ERROR: publication "testpub_reset" has tables associated with it
+DETAIL: ALL TABLES cannot be added when tables are associated with the publication.
+-- Can't add ALL TABLES to 'TABLES IN SCHEMA' publication
+ALTER PUBLICATION testpub_reset RESET;
+ALTER PUBLICATION testpub_reset ADD TABLES IN SCHEMA public;
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+ERROR: publication "testpub_reset" has schemas associated with it
+DETAIL: ALL TABLES cannot be added when schemas are associated with the publication.
+-- Can add ALL TABLES when the 'publish', 'publish_via_partition_root',
+-- 'publish_generated_columns' parameters does not have default value
+ALTER PUBLICATION testpub_reset RESET;
+ALTER PUBLICATION testpub_reset SET (publish = '', publish_via_partition_root = 'true', publish_generated_columns = stored);
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | f | f | f | f | stored | t
+(1 row)
+
DROP PUBLICATION testpub_reset;
DROP TABLE pub_sch1.tbl1;
DROP SCHEMA pub_sch1;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index f0432f67b4a..5259331137b 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1299,6 +1299,48 @@ ALTER PUBLICATION testpub_reset SET (publish_generated_columns = stored);
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
+-- ======================================================
+
+-- Tests for ALTER PUBLICATION ... ADD ALL TABLES
+-- Verify that only superuser can ADD ALL TABLES
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user2;
+SET ROLE regress_publication_user2;
+ALTER PUBLICATION testpub_reset ADD ALL TABLES; -- fail - must be superuser
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user;
+SET ROLE regress_publication_user;
+
+-- Can add ALL TABLES to an empty publication
+DROP PUBLICATION testpub_reset;
+CREATE PUBLICATION testpub_reset;
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+\dRp+ testpub_reset
+
+-- Can't add ALL TABLES to 'ALL TABLES' publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+
+-- Can add ALL TABLES to ALL SEQUENCES publication
+DROP PUBLICATION testpub_reset;
+CREATE PUBLICATION testpub_reset for ALL SEQUENCES;
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+\dRp+ testpub_reset
+
+-- Can't add ALL TABLES to 'FOR TABLE' publication
+ALTER PUBLICATION testpub_reset RESET;
+ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1;
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+
+-- Can't add ALL TABLES to 'TABLES IN SCHEMA' publication
+ALTER PUBLICATION testpub_reset RESET;
+ALTER PUBLICATION testpub_reset ADD TABLES IN SCHEMA public;
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+
+-- Can add ALL TABLES when the 'publish', 'publish_via_partition_root',
+-- 'publish_generated_columns' parameters does not have default value
+ALTER PUBLICATION testpub_reset RESET;
+ALTER PUBLICATION testpub_reset SET (publish = '', publish_via_partition_root = 'true', publish_generated_columns = stored);
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+\dRp+ testpub_reset
+
DROP PUBLICATION testpub_reset;
DROP TABLE pub_sch1.tbl1;
DROP SCHEMA pub_sch1;
--
2.34.1