v2-0001-Skip-unpublishable-descendant-tables-when-adding-.patch

From 4d6978ba7b7c059508abbd56e027011de6250ea2 Mon Sep 17 00:00:00 2001
From: Arunprasad Rajkumar <ar.arunprasad@gmail.com>
Date: Mon, 15 Dec 2025 12:22:13 +0530
Subject: [PATCH v2] Skip unpublishable descendant tables when adding parent to
 publication

When adding a parent table to a publication using CREATE PUBLICATION or
ALTER PUBLICATION ... ADD TABLE, PostgreSQL automatically includes all
descendant tables in the inheritance hierarchy (via find_all_inheritors()).
However, if any descendant is unpublishable (foreign table, temporary table,
or unlogged table), the operation fails with an error.

This commit changes the behavior to skip unpublishable descendants instead
of failing. A WARNING message is issued for each skipped descendant to inform
the user why it was excluded.

Note that this only affects traditional inheritance (INHERITS). Partition
hierarchies use a different code path and are unaffected.

Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>
---
 doc/src/sgml/ref/alter_publication.sgml   |  3 ++
 doc/src/sgml/ref/create_publication.sgml  |  8 ++--
 src/backend/commands/publicationcmds.c    | 38 ++++++++++++++++
 src/test/regress/expected/publication.out | 54 +++++++++++++++++++++++
 src/test/regress/sql/publication.sql      | 37 ++++++++++++++++
 5 files changed, 137 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 028770f2149..c9fc9401866 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -125,6 +125,9 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
       specified, the table and all its descendant tables (if any) are
       affected.  Optionally, <literal>*</literal> can be specified after the table
       name to explicitly indicate that descendant tables are included.
+      Descendant tables that cannot be replicated (foreign tables, temporary
+      tables, or unlogged tables) are automatically skipped with a warning
+      message.
      </para>
 
      <para>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 75a508bebfa..8262d0194cb 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -81,9 +81,11 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
       specified, the table and all its descendant tables (if any) are added.
       Optionally, <literal>*</literal> can be specified after the table name to
       explicitly indicate that descendant tables are included.
-      This does not apply to a partitioned table, however.  The partitions of
-      a partitioned table are always implicitly considered part of the
-      publication, so they are never explicitly added to the publication.
+      Descendant tables that cannot be replicated (foreign tables, temporary
+      tables, or unlogged tables) are automatically skipped with a warning
+      message.  This does not apply to a partitioned table, however.  The
+      partitions of a partitioned table are always implicitly considered part
+      of the publication, so they are never explicitly added to the publication.
      </para>
 
      <para>
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index a1983508950..32cc6fc8b45 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1826,6 +1826,44 @@ OpenTableList(List *tables)
 
 				/* find_all_inheritors already got lock */
 				rel = table_open(childrelid, NoLock);
+
+				/*
+				 * Skip descendant relations that cannot be replicated. When
+				 * adding a parent table with INHERITS descendants to a
+				 * publication, we want to include only regular tables and
+				 * partitioned tables. Foreign tables and temporary/unlogged
+				 * tables cannot be replicated.
+				 *
+				 * Note: Views, materialized views, and sequences cannot use
+				 * INHERITS, so find_all_inheritors() will only return tables
+				 * and foreign tables.
+				 */
+				if (!is_publishable_relation(rel))
+				{
+					if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+						ereport(WARNING,
+								(errmsg("skipping \"%s\" --- cannot add relation to publication",
+										RelationGetRelationName(rel)),
+								 errdetail("Foreign tables cannot be replicated.")));
+					else if (rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
+						ereport(WARNING,
+								(errmsg("skipping \"%s\" --- cannot add relation to publication",
+										RelationGetRelationName(rel)),
+								 errdetail("Temporary tables cannot be replicated.")));
+					else if (rel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
+						ereport(WARNING,
+								(errmsg("skipping \"%s\" --- cannot add relation to publication",
+										RelationGetRelationName(rel)),
+								 errdetail("Unlogged tables cannot be replicated.")));
+					else
+						ereport(WARNING,
+								(errmsg("skipping \"%s\" --- cannot add relation to publication",
+										RelationGetRelationName(rel))));
+
+					table_close(rel, NoLock);
+					continue;
+				}
+
 				pub_rel = palloc_object(PublicationRelInfo);
 				pub_rel->relation = rel;
 				/* child inherits WHERE clause from parent */
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index e72d1308967..c7c0dad82b9 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -240,6 +240,60 @@ Tables:
 
 DROP TABLE testpub_tbl3, testpub_tbl3a;
 DROP PUBLICATION testpub3, testpub4;
+-- Test skipping unpublishable descendant tables (foreign, temp, unlogged)
+-- with nested inheritance hierarchy
+CREATE FOREIGN DATA WRAPPER dummy_fdw;
+CREATE SERVER dummy_server FOREIGN DATA WRAPPER dummy_fdw;
+CREATE TABLE testpub_parent_skip (a int);
+CREATE TABLE testpub_child_regular (a int) INHERITS (testpub_parent_skip);
+NOTICE:  merging column "a" with inherited definition
+CREATE FOREIGN TABLE testpub_child_foreign (a int) INHERITS (testpub_parent_skip)
+    SERVER dummy_server;
+NOTICE:  merging column "a" with inherited definition
+-- Regular table inheriting from foreign table should still be added
+CREATE TABLE testpub_child_foreign_child (b int) INHERITS (testpub_child_foreign);
+CREATE TEMP TABLE testpub_child_temp (a int) INHERITS (testpub_parent_skip);
+NOTICE:  merging column "a" with inherited definition
+-- Regular table inheriting from temp table is not allowed.
+-- CREATE TABLE testpub_child_temp_child (c int) INHERITS (testpub_child_temp);
+CREATE UNLOGGED TABLE testpub_child_unlogged (a int) INHERITS (testpub_parent_skip);
+NOTICE:  merging column "a" with inherited definition
+-- Regular table inheriting from unlogged table should still be added
+CREATE TABLE testpub_child_unlogged_child (d int) INHERITS (testpub_child_unlogged);
+-- Should skip foreign, temp, and unlogged descendants with WARNING
+SET client_min_messages = 'NOTICE';
+CREATE PUBLICATION testpub_skip_unpublishable_child FOR TABLE testpub_parent_skip;
+WARNING:  skipping "testpub_child_foreign" --- cannot add relation to publication
+DETAIL:  Foreign tables cannot be replicated.
+WARNING:  skipping "testpub_child_temp" --- cannot add relation to publication
+DETAIL:  Temporary tables cannot be replicated.
+WARNING:  skipping "testpub_child_unlogged" --- cannot add relation to publication
+DETAIL:  Unlogged tables cannot be replicated.
+WARNING:  "wal_level" is insufficient to publish logical changes
+HINT:  Set "wal_level" to "logical" before creating subscriptions.
+RESET client_min_messages;
+-- Verify only parent and regular descendants are in publication
+SELECT * FROM pg_publication_tables
+WHERE pubname = 'testpub_skip_unpublishable_child'
+ORDER BY tablename;
+             pubname              | schemaname |          tablename           | attnames | rowfilter 
+----------------------------------+------------+------------------------------+----------+-----------
+ testpub_skip_unpublishable_child | public     | testpub_child_foreign_child  | {a,b}    | 
+ testpub_skip_unpublishable_child | public     | testpub_child_regular        | {a}      | 
+ testpub_skip_unpublishable_child | public     | testpub_child_unlogged_child | {a,d}    | 
+ testpub_skip_unpublishable_child | public     | testpub_parent_skip          | {a}      | 
+(4 rows)
+
+DROP PUBLICATION testpub_skip_unpublishable_child;
+DROP TABLE testpub_child_unlogged_child;
+DROP TABLE testpub_child_unlogged;
+DROP TABLE testpub_child_foreign_child;
+DROP FOREIGN TABLE testpub_child_foreign;
+DROP TABLE testpub_child_regular;
+DROP TABLE testpub_parent_skip CASCADE;
+NOTICE:  drop cascades to table testpub_child_temp
+DROP SERVER dummy_server;
+DROP FOREIGN DATA WRAPPER dummy_fdw;
 --- Tests for publications with SEQUENCES
 CREATE SEQUENCE regress_pub_seq0;
 CREATE SEQUENCE pub_test.regress_pub_seq1;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 00390aecd47..06f12f97cca 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -120,6 +120,43 @@ RESET client_min_messages;
 DROP TABLE testpub_tbl3, testpub_tbl3a;
 DROP PUBLICATION testpub3, testpub4;
 
+-- Test skipping unpublishable descendant tables (foreign, temp, unlogged)
+-- with nested inheritance hierarchy
+CREATE FOREIGN DATA WRAPPER dummy_fdw;
+CREATE SERVER dummy_server FOREIGN DATA WRAPPER dummy_fdw;
+CREATE TABLE testpub_parent_skip (a int);
+CREATE TABLE testpub_child_regular (a int) INHERITS (testpub_parent_skip);
+CREATE FOREIGN TABLE testpub_child_foreign (a int) INHERITS (testpub_parent_skip)
+    SERVER dummy_server;
+-- Regular table inheriting from foreign table should still be added
+CREATE TABLE testpub_child_foreign_child (b int) INHERITS (testpub_child_foreign);
+CREATE TEMP TABLE testpub_child_temp (a int) INHERITS (testpub_parent_skip);
+-- Regular table inheriting from temp table is not allowed.
+-- CREATE TABLE testpub_child_temp_child (c int) INHERITS (testpub_child_temp);
+CREATE UNLOGGED TABLE testpub_child_unlogged (a int) INHERITS (testpub_parent_skip);
+-- Regular table inheriting from unlogged table should still be added
+CREATE TABLE testpub_child_unlogged_child (d int) INHERITS (testpub_child_unlogged);
+
+-- Should skip foreign, temp, and unlogged descendants with WARNING
+SET client_min_messages = 'NOTICE';
+CREATE PUBLICATION testpub_skip_unpublishable_child FOR TABLE testpub_parent_skip;
+RESET client_min_messages;
+
+-- Verify only parent and regular descendants are in publication
+SELECT * FROM pg_publication_tables
+WHERE pubname = 'testpub_skip_unpublishable_child'
+ORDER BY tablename;
+
+DROP PUBLICATION testpub_skip_unpublishable_child;
+DROP TABLE testpub_child_unlogged_child;
+DROP TABLE testpub_child_unlogged;
+DROP TABLE testpub_child_foreign_child;
+DROP FOREIGN TABLE testpub_child_foreign;
+DROP TABLE testpub_child_regular;
+DROP TABLE testpub_parent_skip CASCADE;
+DROP SERVER dummy_server;
+DROP FOREIGN DATA WRAPPER dummy_fdw;
+
 --- Tests for publications with SEQUENCES
 CREATE SEQUENCE regress_pub_seq0;
 CREATE SEQUENCE pub_test.regress_pub_seq1;
-- 
2.39.5 (Apple Git-154)