On Tue, Dec 16, 2025, at 6:34 AM, Amit Kapila wrote:
>
> Prohibiting all commands sounds too restrictive in all cases (FOR ALL
> TABLES, FOR TABLE, etc.). It would be better if we can disallow
> creating a publication when the user explicitly adds such a relation
> in a FOR TABLE publication, otherwise raise a WARNING and don't make
> it part of publication. The behavior should be the same for both
> partition and inherited tables.
>
WFM. The attached patch checks the partitions too. The behavior is similar to
inherited tables. However, I didn't understand the "otherwise" part. Are you
suggesting to add WARNING in the FOR ALL TABLES and FOR TABLES IN SCHEMA if
there is any unsupported relations?
--
Euler Taveira
EDB https://www.enterprisedb.com/
From b1bab0a22d811d9d3de457e29ed2d7cae123b108 Mon Sep 17 00:00:00 2001
From: Euler Taveira <[email protected]>
Date: Tue, 16 Dec 2025 11:30:42 -0300
Subject: [PATCH v1] Fix unsupported relations for publications
Although publication check for unsupported relations for the table list,
it is not checking the partitions of a partitioned table. The
consequence is that data from this partitioned table is not replicated.
Inheritance is already covered because parent and children tables are
included in the publication-relation mapping. There is no test coverage
for this case so add it.
This is not a complete solution. SQL commands that manipulate the
partitioned table (ALTER TABLE ... ATTACH PARTITION, ALTER TABLE ... SET
UNLOGGED, CREATE TABLE ... PARTITION OF) might break this contract.
---
src/backend/catalog/pg_publication.c | 18 +++++++++++---
src/test/regress/expected/publication.out | 29 ++++++++++++++++++++++-
src/test/regress/sql/publication.sql | 24 ++++++++++++++++++-
3 files changed, 66 insertions(+), 5 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 7aa3f179924..306187012fb 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -466,8 +466,23 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
RelationGetRelationName(targetrel), pub->name)));
}
+ /*
+ * Check for supported relation. For partitioned tables, all partitions
+ * should be supported relations too.
+ */
check_publication_add_relation(targetrel);
+ relids = GetPubPartitionOptionRelations(relids, PUBLICATION_PART_ALL,
+ relid);
+ foreach_oid(partoid, relids)
+ {
+ Relation partrel = RelationIdGetRelation(partoid);
+
+ check_publication_add_relation(partrel);
+ RelationClose(partrel);
+ }
+
+
/* Validate and translate column names into a Bitmapset of attnums. */
attnums = pub_collist_validate(pri->relation, pri->columns);
@@ -537,9 +552,6 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
* mentioned in the publication. This is required because we implicitly
* publish the child tables when the parent table is published.
*/
- relids = GetPubPartitionOptionRelations(relids, PUBLICATION_PART_ALL,
- relid);
-
InvalidatePublicationRels(relids);
return myself;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index e72d1308967..e6638c6e517 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -326,6 +326,20 @@ ERROR: invalid publication object list
LINE 1: ...equences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL SEQUEN...
^
DETAIL: ALL SEQUENCES can be specified only once.
+-- Tests for inheritance
+CREATE TABLE testpub_inh (a int);
+CREATE TABLE testpub_inh1 () INHERITS(testpub_inh);
+CREATE TEMPORARY TABLE testpub_inh2 () INHERITS(testpub_inh);
+SET client_min_messages = 'ERROR';
+-- fail - unsupported relation as a child relation
+CREATE PUBLICATION testpub_inh FOR TABLE testpub_inh;
+ERROR: cannot add relation "testpub_inh2" to publication
+DETAIL: This operation is not supported for temporary tables.
+ALTER TABLE testpub_inh2 NO INHERIT testpub_inh;
+CREATE PUBLICATION testpub_inh FOR TABLE testpub_inh;
+RESET client_min_messages;
+DROP TABLE testpub_inh, testpub_inh1, testpub_inh2;
+DROP PUBLICATION testpub_inh;
-- Tests for partitioned tables
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forparted;
@@ -333,9 +347,22 @@ CREATE PUBLICATION testpub_forparted1;
RESET client_min_messages;
CREATE TABLE testpub_parted1 (LIKE testpub_parted);
CREATE TABLE testpub_parted2 (LIKE testpub_parted);
+CREATE UNLOGGED TABLE testpub_parted3 (LIKE testpub_parted);
ALTER PUBLICATION testpub_forparted1 SET (publish='insert');
ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted1 FOR VALUES IN (1);
ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted2 FOR VALUES IN (2);
+ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted3 FOR VALUES IN (3);
+-- fail - unsupported relation as a partition
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_forparted2 FOR TABLE testpub_parted;
+ERROR: cannot add relation "testpub_parted3" to publication
+DETAIL: This operation is not supported for unlogged tables.
+RESET client_min_messages;
+-- fail - unsupported relation as a partition
+ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted;
+ERROR: cannot add relation "testpub_parted3" to publication
+DETAIL: This operation is not supported for unlogged tables.
+ALTER TABLE testpub_parted DETACH PARTITION testpub_parted3;
-- works despite missing REPLICA IDENTITY, because updates are not replicated
UPDATE testpub_parted1 SET a = 1;
-- only parent is listed as being in publication, not the partition
@@ -373,7 +400,7 @@ HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
ALTER PUBLICATION testpub_forparted DROP TABLE testpub_parted;
-- works again, because update is no longer replicated
UPDATE testpub_parted2 SET a = 2;
-DROP TABLE testpub_parted1, testpub_parted2;
+DROP TABLE testpub_parted1, testpub_parted2, testpub_parted3;
DROP PUBLICATION testpub_forparted, testpub_forparted1;
-- Tests for row filters
CREATE TABLE testpub_rf_tbl1 (a integer, b text);
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 00390aecd47..b5a4c291516 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -166,6 +166,19 @@ CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL
-- fail - Specifying ALL SEQUENCES more than once
CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL SEQUENCES;
+-- Tests for inheritance
+CREATE TABLE testpub_inh (a int);
+CREATE TABLE testpub_inh1 () INHERITS(testpub_inh);
+CREATE TEMPORARY TABLE testpub_inh2 () INHERITS(testpub_inh);
+SET client_min_messages = 'ERROR';
+-- fail - unsupported relation as a child relation
+CREATE PUBLICATION testpub_inh FOR TABLE testpub_inh;
+ALTER TABLE testpub_inh2 NO INHERIT testpub_inh;
+CREATE PUBLICATION testpub_inh FOR TABLE testpub_inh;
+RESET client_min_messages;
+DROP TABLE testpub_inh, testpub_inh1, testpub_inh2;
+DROP PUBLICATION testpub_inh;
+
-- Tests for partitioned tables
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forparted;
@@ -173,9 +186,18 @@ CREATE PUBLICATION testpub_forparted1;
RESET client_min_messages;
CREATE TABLE testpub_parted1 (LIKE testpub_parted);
CREATE TABLE testpub_parted2 (LIKE testpub_parted);
+CREATE UNLOGGED TABLE testpub_parted3 (LIKE testpub_parted);
ALTER PUBLICATION testpub_forparted1 SET (publish='insert');
ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted1 FOR VALUES IN (1);
ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted2 FOR VALUES IN (2);
+ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted3 FOR VALUES IN (3);
+-- fail - unsupported relation as a partition
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_forparted2 FOR TABLE testpub_parted;
+RESET client_min_messages;
+-- fail - unsupported relation as a partition
+ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted;
+ALTER TABLE testpub_parted DETACH PARTITION testpub_parted3;
-- works despite missing REPLICA IDENTITY, because updates are not replicated
UPDATE testpub_parted1 SET a = 1;
-- only parent is listed as being in publication, not the partition
@@ -195,7 +217,7 @@ UPDATE testpub_parted2 SET a = 2;
ALTER PUBLICATION testpub_forparted DROP TABLE testpub_parted;
-- works again, because update is no longer replicated
UPDATE testpub_parted2 SET a = 2;
-DROP TABLE testpub_parted1, testpub_parted2;
+DROP TABLE testpub_parted1, testpub_parted2, testpub_parted3;
DROP PUBLICATION testpub_forparted, testpub_forparted1;
-- Tests for row filters
--
2.39.5
