Re: [PATCH] Skip unpublishable child tables when adding parent to publication - Mailing list pgsql-hackers

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.