Обсуждение: [PATCH] Skip unpublishable child tables when adding parent to publication

Поиск

Список

Период

Сортировка

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

От

Arunprasad Rajkumar

Дата:

12 декабря, 17:26:10

Hi,

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.

The parent table and any publishable children are successfully added to the
publication.

I've attached the patch for review. Any feedback or suggestions for improvement
would be greatly appreciated.

Best regards,
Arun

Вложения

0001-Skip-unpublishable-child-tables-when-adding-parent-t.patch

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

От

Amit Kapila

Дата:

15 декабря, 09:57:44

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

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

От

Arunprasad Rajkumar

Дата:

15 декабря, 11:41:31

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.

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

От

Arunprasad Rajkumar

Дата:

15 декабря, 12:48:56

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.

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

От

Arunprasad Rajkumar

Дата:

15 декабря, 14:18:16

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.arunprasad@gmail.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.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.

Вложения

v2-0001-Skip-unpublishable-descendant-tables-when-adding-.patch

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

От

"Euler Taveira"

Дата:

16 декабря, 02:27:49

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/

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

От

"Zhijie Hou (Fujitsu)"

Дата:

16 декабря, 06:50:57

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

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

От

Arunprasad Rajkumar

Дата:

16 декабря, 10:07:55

Thank you, Zhijie and Euler, for your thoughts.

I’d like to compare the behavior of UNLOGGED, TEMP, and FOREIGN tables with PostgreSQL streaming (physical) replication. PostgreSQL sets a clear expectation there: these relations are not made available on the replica.

I think we could follow a similar approach for logical replication. Instead of disallowing users from indirectly adding unsupported relations (for example, via partitions or FOR ALL TABLES), we could emit a WARNING wherever possible and remove inconsistent behavior (e.g., performing an initial table sync for UNLOGGED tables) [1].

IMHO, disallowing users from adding an UNLOGGED or FOREIGN table to an existing partitioned table that is already part of a publication may break their business goals. In contrast, excluding such relations from replication—with clear warnings and documentation—would be less disruptive while still setting correct expectations.

BTW, trying to access a partitioned table with UNLOGGED table on read replica throws the following error!

tsdb=> SELECT * FROM test_parted;
ERROR: cannot access temporary or unlogged relations during recovery

Regards,
Arun

[1] https://www.postgresql.org/message-id/CACDxuFxLfruvV5w6Zbr9Yy4dg1nhwgO97-tCYQtyyh%2BT3oDRkg%40mail.gmail.com

On Tue, 16 Dec 2025 at 09:21, 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.

Best Regards,
Hou zj

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

От

Amit Kapila

Дата:

16 декабря, 12:34:26

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.

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: [PATCH] Skip unpublishable child tables when adding parent to publication

Вложения

Вложения