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

Hello Amit,

I’d be happy to fix the behavior for partitioned tables with UNLOGGED or
FOREIGN descendant tables in a follow-up patch.

In the meantime, I’ve updated the current patch based on your
suggestion(s/NOTICE/WARNING).

Thanks & regards,
Arun

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

> 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.
>>>
>>