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

On Fri, Dec 12, 2025 at 7:56 PM Arunprasad Rajkumar
<ar.arunprasad@gmail.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.

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.kapila16@gmail.com> wrote:

On Fri, Dec 12, 2025 at 7:56 PM Arunprasad Rajkumar
<ar.arunprasad@gmail.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.

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.arunprasad@gmail.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.kapila16@gmail.com>
wrote:

On Fri, Dec 12, 2025 at 7:56 PM Arunprasad Rajkumar
<ar.arunprasad@gmail.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.

On Tuesday, December 16, 2025 7:28 AM Euler Taveira <euler@eulerto.com>
wrote:

On Mon, Dec 15, 2025, at 3:57 AM, Amit Kapila wrote:

I think the unlogged table is afterwards silently ignored during
replication.

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?

I thought about implementing a rule within publication DDLs to prevent
adding
partitioned tables with unsupported partitions to a publication. However,
users
can still create problematic partitioned tables later using commands like
ATTACH
PARTITION, CREATE PARTITION OF, or ALTER TABLE SET UNLOGGED. These
commands are
similar to those that you identified in the FOR ALL TABLES scenario. This
raises
uncertainty about how we should address these commands in the FOR single
TABLE
scenario. Should we permit these user commands but restrict only adding
unsupported relation to publication, or should we apply restrictions
across all
such commands? The former might lead to inconsistency with the FOR ALL
TABLES
setting, where unsupported relations are silently ignored.

Best Regards,
Hou zj

On Tue, Dec 16, 2025 at 9:21 AM Zhijie Hou (Fujitsu)
<houzj.fnst@fujitsu.com> wrote:

On Tuesday, December 16, 2025 7:28 AM Euler Taveira <euler@eulerto.com>

wrote:

On Mon, Dec 15, 2025, at 3:57 AM, Amit Kapila wrote:

I think the unlogged table is afterwards silently ignored during
replication.

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?

I thought about implementing a rule within publication DDLs to prevent

adding

partitioned tables with unsupported partitions to a publication.

However, users

can still create problematic partitioned tables later using commands

like ATTACH

PARTITION, CREATE PARTITION OF, or ALTER TABLE SET UNLOGGED. These

commands are

similar to those that you identified in the FOR ALL TABLES scenario.

This raises

uncertainty about how we should address these commands in the FOR single

TABLE

scenario. Should we permit these user commands but restrict only adding
unsupported relation to publication, or should we apply restrictions

across all

such commands? The former might lead to inconsistency with the FOR ALL

TABLES

setting, where unsupported relations are silently ignored.

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.

--
With Regards,
Amit Kapila.

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/