Re: [PATCH] Skip unpublishable child tables when adding parent to publication

On Mon, Dec 15, 2025, at 3:57 AM, Amit Kapila wrote:
>
> I think the unlogged table is afterwards silently ignored during
> replication.
>

Is it an acceptable behavior? I'm not sure. Even if you are using an allowed
object (partitioned table), the replication happens using a partition (that can
or cannot be a supported relation kind). Hence, if this partition is a
temporary or unlogged table, the publication could not be created or modified.

Adding a WARNING is not sufficient. If you run an automated script, it is
easily ignored by the user. The strict behavior should be disallow relations
that are not supported for logical replication. It also includes changing the
relation kind (ALTER TABLE ... SET LOGGED|UNLOGGED). It guarantees that every
data in the tables for that publication is replicated. It means no surprises like

# publisher
cat << EOF | psql -f - -p 5432 -d postgres
CREATE TABLE test_parted (a integer primary key, b integer) PARTITION BY RANGE (a);
CREATE TABLE test_parted_100 (LIKE test_parted);                                
CREATE TABLE test_parted_200 (LIKE test_parted);                                
CREATE UNLOGGED TABLE test_parted_300 (LIKE test_parted);                       
                                                                                
ALTER TABLE test_parted ATTACH PARTITION test_parted_100 FOR VALUES FROM (0) TO (101);
ALTER TABLE test_parted ATTACH PARTITION test_parted_200 FOR VALUES FROM (101) TO (201);
ALTER TABLE test_parted ATTACH PARTITION test_parted_300 FOR VALUES FROM (201) TO (301);
CREATE PUBLICATION pub_parted FOR TABLE test_parted WITH (publish_via_partition_root = true);
EOF

# subscriber
psql -X -c "CREATE TABLE test_parted (a integer primary key, b integer)" -p 9876 -d postgres
psql -X -c  "CREATE SUBSCRIPTION sub_parted CONNECTION 'port=5432 dbname=postgres' PUBLICATION pub_parted" -p 9876 -d
postgres

# publisher
psql -X -c "INSERT INTO test_parted (a, b) VALUES(50, 1)" -p 5432 -d postgres
psql -X -c "INSERT INTO test_parted (a, b) VALUES(150, 1)" -p 5432 -d postgres
psql -X -c "INSERT INTO test_parted (a, b) VALUES(250, 1)" -p 5432 -d postgres
psql -X -c "SELECT * FROM test_parted" -p 5432 -d postgres
  a  | b 
-----+---
  50 | 1
 150 | 1
 250 | 1
(3 rows)

# subscriber
psql -X -c 'SELECT * FROM test_parted' -p 9876 -d postgres
  a  | b 
-----+---
  50 | 1
 150 | 1
(2 rows)

Ugh. Where is the missing row? After some investigation, that partition is
unlogged. (Although I used publish_via_partition_root in this example, it also
happens without it.)

What is the implication for prohibiting publication to be created in this
partitioned table case? The only scenario I have in mind is an ETL routine to
load data using unlogged tables. Even if you consider this scenario, you can
adjust the commands to attach the partition *after* loading and setting the
table from unlogged to logged.

There is also the FOR ALL TABLES case. The manual says

  Marks the publication as one that replicates changes for all tables in the
  database, including tables created in the future.

It says nothing about relation kind. This is an oversight. FOR TABLE and FOR
TABLES IN SCHEMA mention about the unsupported relations. One suggestion is to
avoid repeating the same sentence in each clause and add it to the command
description. Maybe using a <note>...</note>.

Regarding the FOR ALL TABLES behavior, should it prohibit creating/attaching a
partition for an unsupported relation? Different from the FOR TABLE clause that
you have a specified relation, in this case you don't one. It means you could
have an error for regular commands (CREATE TABLE or ALTER TABLE ... SET
UNLOGGED) if you simply have a publication with FOR ALL TABLES. This change
might break routines that are working today and I think that is a bad idea. A
reasonable solution is to ignore the unsupported objects.  It means a
partitioned table that has a single unlogged table as a partition will be
ignored. It changes the current behavior to have "all or nothing" instead of
"some". IMO it is easier to detect an issue if the partitioned table is empty
then if there is just partial data in it.

In summary, I think we should prohibit adding a partitioned table to a
publication if there is any unsupported relation that is a partition of it. The
FOR ALL TABLES ignores the partitioned table if there is any unsupported
relation. Opinions?


-- 
Euler Taveira
EDB   https://www.enterprisedb.com/