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

Hi Amit,

I’ve given some more thought to how the behavior should be with UNLOGGED
and FOREIGN tables.

IMHO, we *should not* allow adding UNLOGGED and FOREIGN tables in either
inheritance or partitioning scenarios to the publication.
Since these table types cannot be replicated, it doesn’t make sense to keep
them as part of a publication — that breaks user expectations.

What are your thoughts?

Thanks,
Arun

On Mon, 15 Dec 2025 at 14:11, Arunprasad Rajkumar <ar(dot)arunprasad(at)gmail(dot)com>
wrote:

> Hello Amit,
>
> Thank you for reviewing the patch and sharing your valuable feedback.
>
> I did not try with a partitioned table.
>
> After your feedback, I tried with temp, unlogged and foreign tables a
> partition. See below snippets,
>
> postgres=# CREATE TABLE testpub_parent_skip_1 (a int) PARTITION BY RANGE
> (a);
> CREATE TABLE
> postgres=# CREATE TABLE testpub_child_regular_1 PARTITION OF
> testpub_parent_skip_1 FOR VALUES FROM (1) TO (10);
> CREATE TABLE
> postgres=# CREATE temp TABLE testpub_child_temp_1 PARTITION OF
> testpub_parent_skip_1 FOR VALUES FROM (11) TO (20);
> ERROR: cannot create a temporary relation as partition of permanent
> relation "testpub_parent_skip_1"
> postgres=# CREATE unlogged TABLE testpub_child_unlogged_1 PARTITION OF
> testpub_parent_skip_1 FOR VALUES FROM (11) TO (20);
> CREATE TABLE
>
> postgres=# CREATE EXTENSION IF NOT EXISTS postgres_fdw;
> CREATE EXTENSION
> postgres=# CREATE SERVER local_server FOREIGN DATA WRAPPER postgres_fdw
> OPTIONS (host 'localhost', port '5433', dbname 'postgres');
> CREATE SERVER
> postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER local_server OPTIONS
> (user 'arajkumar');
> postgres=# CREATE TABLE actual_data_table (
> a int
> );
> CREATE TABLE
> postgres=# CREATE FOREIGN TABLE testpub_child_foreign_1 (
> a int
> ) SERVER local_server
> OPTIONS (schema_name 'public', table_name 'actual_data_table');
> CREATE FOREIGN TABLE
> postgres=# ALTER TABLE testpub_parent_skip_1 ATTACH PARTITION
> testpub_child_foreign_1 FOR VALUES FROM (21) TO (30);
> ALTER TABLE
> postgres=# CREATE PUBLICATION testpub_skip_child_pub_1 FOR TABLE
> testpub_parent_skip_1;
> CREATE PUBLICATION
> postgres=# SELECT * FROM pg_publication_tables ;
> pubname | schemaname | tablename |
> attnames | rowfilter
>
> --------------------------+------------+--------------------------+----------+-----------
> testpub_skip_child_pub_1 | public | testpub_child_regular_1 | {a}
> |
> testpub_skip_child_pub_1 | public | testpub_child_unlogged_1 | {a}
> |
> testpub_skip_child_pub_1 | public | testpub_child_foreign_1 | {a}
> |
> (3 rows)
>
> I could see FOREIGN TABLE is being added into the publication very similar
> to UNLOGGED table.
>
> With the same table setup on publication, I tried creating a SUBSCRIPTION.
> It fails with an error,
>
> postgres=# CREATE SUBSCRIPTION my_subscription CONNECTION 'host=localhost
> port=5433 dbname=postgres user=arajkumar'
> PUBLICATION testpub_skip_child_pub_1;
> ERROR: cannot use relation "public.testpub_child_foreign_1" as logical
> replication target
> DETAIL: This operation is not supported for foreign tables.
>
> However, I could create a SUBSCRIPTION when I change the publication to
> PUBLISH_VIA_ROOT_PARITION=true.
> On source,
> postgres=# ALTER PUBLICATION testpub_skip_child_pub_1
> SET(PUBLISH_VIA_PARTITION_ROOT=true);
> ALTER PUBLICATION
>
> On Target,
> postgres=# CREATE SUBSCRIPTION my_subscription
> CONNECTION 'host=localhost port=5433 dbname=postgres user=arajkumar'
> PUBLICATION testpub_skip_child_pub_1;
> NOTICE: created replication slot "my_subscription" on publisher
> CREATE SUBSCRIPTION
>
> But, the table sync worker fails with the following log,
>
> 2025-12-15 13:53:28.093 IST [81904] LOG: logical replication table
> synchronization worker for subscription "my_subscription", table
> "testpub_parent_skip_1" has started
> 2025-12-15 13:53:28.120 IST [81904] ERROR: could not start initial
> contents copy for table "public.testpub_parent_skip_1": ERROR: cannot copy
> from foreign table "testpub_child_foreign_1"
> DETAIL: Partition "testpub_child_foreign_1" is a foreign table in
> partitioned table "testpub_parent_skip_1"
> HINT: Try the COPY (SELECT ...) TO variant.
> 2025-12-15 13:53:28.120 IST [46273] LOG: background worker "logical
> replication tablesync worker" (PID 81904) exited with exit code 1
>
>
> My Observation:
>
> 1) Postgres partition with unlogged table as child partition:
> - Added into the publication
> - Could create subscription and completes initial data sync, but
> replication won't work obviously because it is an UNLOGGED table.
>
> 2) Postgres partition with foreign table as child partition:
> - Added into the publication when PUBLISH_VIA_PARTITION_ROOT=true,
> - Could create subscription, but initial data sync fails.
> - Probably this could be fixed to work very similar to an UNLOGGED
> table? If so, should we allow adding foreign tables into publication in
> inheritance as well?
>
> Thanks,
> Arun
>
> On Mon, 15 Dec 2025 at 12:27, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
>> On Fri, Dec 12, 2025 at 7:56 PM Arunprasad Rajkumar
>> <ar(dot)arunprasad(at)gmail(dot)com> wrote:
>> >
>> > I would like to propose a patch that improves the handling of table
>> inheritance
>> > hierarchies when adding tables to publications for logical replication.
>> >
>> > Problem:
>> > Currently, when attempting to add a parent table to a publication
>> using, the operation fails
>> > with an error if any of the inherited child tables are foreign tables,
>> temporary tables, or unlogged tables. This makes it difficult to work with
>> inheritance hierarchies in logical
>> > replication scenarios, as users must manually manage which specific
>> tables to
>> > include or exclude.
>> >
>> > Proposed Solution:
>> > This patch modifies the behavior to automatically skip child tables
>> that cannot
>> > be replicated, rather than failing the entire operation. When
>> unpublishable
>> > children are encountered, a NOTICE message is issued following the same
>> format
>> > used by VACUUM and ANALYZE commands:
>> >
>> > NOTICE: skipping "table_name" --- cannot add relation to publication
>> > DETAIL: Foreign tables cannot be replicated.
>> >
>>
>> BTW, did you try the similar cases for partitioned tables. For
>> example, below case for unlogged partition table works for me:
>> postgres=# CREATE TABLE testpub_parent_skip_1 (a int) PARTITION BY RANGE
>> (a);
>> CREATE TABLE
>> postgres=# CREATE TABLE testpub_child_regular_1 PARTITION OF
>> testpub_parent_skip FOR VALUES FROM (1) TO (10);
>> ERROR: "testpub_parent_skip" is not partitioned
>> postgres=# CREATE TABLE testpub_child_regular_1 PARTITION OF
>> testpub_parent_skip_1 FOR VALUES FROM (1) TO (10);
>> CREATE TABLE
>> postgres=# CREATE unlogged TABLE testpub_child_temp_1 PARTITION OF
>> testpub_parent_skip_1 FOR VALUES FROM (11) TO (20);
>> CREATE TABLE
>> postgres=# CREATE PUBLICATION testpub_skip_child_pub_1 FOR TABLE
>> testpub_parent_skip_1;
>> CREATE PUBLICATION
>>
>> I think the unlogged table is afterwards silently ignored during
>> replication. You can once check this and foreign table variant.
>>
>> BTW, for a somewhat related case, we use WARNING, see below:
>> if (!indexRelation->rd_index->indisvalid)
>> ereport(WARNING,
>> (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
>> errmsg("skipping reindex of invalid index \"%s.%s\"",
>>
>> So, shall we consider raising a WARNING instead of NOTICE?
>>
>> --
>> With Regards,
>> Amit Kapila.
>>
>