On Tue, Sep 8, 2026 at 9:22 AM shveta malik <[email protected]> wrote:
>
> >
> > > 2. I am also unclear on the intent. When a partition is undergoing
> > > concurrent detach, do we ultimately want to publish its changes or
> > > not?
> > >
> >
> > It depends. For example, if it is ALL TABLES publication then the
> > in-process detach partition should be replicated as an individual
> > table.
>
> Yes, that's what I thought initially, but the test case validates
> otherwise. See the test in patch-001: the publication is for ALL
> TABLES, but the partition being detached is not replicated.
>
> I think this patch focuses on fixing the crash without clarifying the
> intent for various cases: whether to publish or not publish the
> detached partition.
>
I tested it further on my machine. Here are the observations:
1)
FOR ALL TABLES pub:
While DETACH PARTITION ... CONCURRENTLY is in its intermediate state
(inhdetachpending = true), changes made directly to the detached
partition continue to be replicated. This is expected IMO, since the
partition is independently included in an ALL TABLES publication,
regardless of its partition relationship with t1.
<The testcase of patch001 shows otherwise. It needs some analysis to
determine why. This means comment in get_rel_sync_entry in patch001 is
correct, the functionality is as per that comment.>
2)
FOR TABLE root pub:
During the intermediate state of DETACH PARTITION ... CONCURRENTLY,
changes made directly to the partition being detached (t1_part1) are
also replicated, even though t1_part1 is not explicitly listed in
pg_publication_tables and pg_partition_root(t1_part1) already returns
t1_part1. This needs some thought regarding how it should behave.
Should t1_part1 not replicated here? Thoughts?
Once the detach completes, changes to t1_part1 are no longer
replicated through the publication of t1, which appears correct.
~~
Please see attached test-results for details. The behaviour is the
same whether I test patch001 alone or all three combined.
Thanks
Shveta
Case 1: ALL TABLES pub case:
-----------------------------------------
Pub:
CREATE TABLE t1 (a int) PARTITION BY RANGE (a);
CREATE TABLE t1_part1 PARTITION OF t1 FOR VALUES FROM (1) TO (10);
CREATE TABLE t1_part2 PARTITION OF t1 FOR VALUES FROM (11) TO (20);
create publication pub1 for all tables;
Sub: (individual tables)
CREATE TABLE t1 (a int);
CREATE TABLE t1_part1(a int);
CREATE TABLE t1_part2(a int);
create subscription sub1 connection 'dbname=postgres host=localhost user=shveta
port=5433' publication pub1;
Pub:
INSERT INTO t1 VALUES (5), (15);
Sub: (replicated to sub)
postgres=# select * from t1_part1;
a
---
5
(1 row)
postgres=# select * from t1_part2;
a
----
15
(1 row)
Now block DETACH PARTITION and check:
Pub:
Session A:
BEGIN;
SELECT * FROM t1;
--do not commit
Session B:
--This blocks on 'A' post detach but before making some catalog changes
ALTER TABLE t1 DETACH PARTITION t1_part1 CONCURRENTLY;
Session C, while B is blocked:
postgres=# SELECT inhdetachpending FROM pg_inherits WHERE inhrelid =
't1_part1'::regclass;
inhdetachpending
------------------
t
--cannot run INSERT through t1 for detached ptn: expected
postgres=# INSERT INTO t1 VALUES (6), (16);
ERROR: no partition of relation "t1" found for row
DETAIL: Partition key of the failing row contains (a) = (6).
--try usign ptn-name for detached ptn
INSERT INTO t1_part1 VALUES (6);
INSERT INTO t1 VALUES (16);
Sub: (both t1_part1 and t1 are replicated): as pub was on ALL TABLES.
postgres=# select * from t1_part1;
a
---
5
6
(2 rows)
postgres=# select * from t1_part2;
a
----
15
16
(2 rows)
Session C, while B is blocked, check ptn-root:
postgres=# select pg_partition_root('t1_part1');
pg_partition_root
-------------------
t1_part1
(1 row)
postgres=# select pg_partition_root('t1_part2');
pg_partition_root
-------------------
t1
(1 row)
pg_pub_rel and pg_pub_tables output for reference.
postgres=# SELECT pr.oid, pr.prpubid, pub.pubname, c.relname AS table_name
FROM pg_publication_rel pr JOIN pg_class c ON c.oid = pr.prrelid JOIN
pg_namespace n ON n.oid = c.relnamespace JOIN pg_publication pub ON pub.oid =
pr.prpubid;
oid | prpubid | pubname | table_name
-----+---------+---------+------------
(0 rows)
postgres=# select * from pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
pub1 | public | t1_part1 | {a} |
pub1 | public | t1_part2 | {a} |
(2 rows)
-----------------------------------------
CASE 2: ptn-table pub case:
-----------------------------------------
Pub:
CREATE TABLE t1 (a int) PARTITION BY RANGE (a);
CREATE TABLE t1_part1 PARTITION OF t1 FOR VALUES FROM (1) TO (10);
CREATE TABLE t1_part2 PARTITION OF t1 FOR VALUES FROM (11) TO (20);
create publication pub1 for table t1;
Sub: (individual tables)
CREATE TABLE t1 (a int);
CREATE TABLE t1_part1(a int);
CREATE TABLE t1_part2(a int);
create subscription sub1 connection 'dbname=postgres host=localhost user=shveta
port=5433' publication pub1;
Pub:
INSERT INTO t1 VALUES (5), (15);
Sub:
postgres=# select * from t1_part1;
a
---
5
(1 row)
postgres=# select * from t1_part2;
a
----
15
(1 row)
Now block DETACH PARTITION and check:
Pub:
Session A:
BEGIN;
SELECT * FROM t1;
--do not commit
Session B:
--This blocks on 'A' post detach but before making some catalog changes
ALTER TABLE t1 DETACH PARTITION t1_part1 CONCURRENTLY;
Session C, while B is blocked:
postgres=# SELECT inhdetachpending FROM pg_inherits WHERE inhrelid =
't1_part1'::regclass;
inhdetachpending
------------------
t
INSERT INTO t1_part1 VALUES (6);
INSERT INTO t1 VALUES (16);
INSERT INTO t1_part1 VALUES (7);
INSERT INTO t1 VALUES (17);
Sub: (detached ptn is also replicated even though it is not part of
pg_publication_tables)
postgres=# select * from t1_part1;
a
---
5
6
7
(3 rows)
postgres=# select * from t1_part2;
a
----
15
16
17
(3 rows)
--pg_publication_rel entry
postgres=# SELECT pr.oid, pr.prpubid, pub.pubname, c.relname AS table_name
FROM pg_publication_rel pr JOIN pg_class c ON c.oid = pr.prrelid JOIN
pg_namespace n ON n.oid = c.relnamespace JOIN pg_publication pub ON pub.oid =
pr.prpubid;
oid | prpubid | pubname | table_name
-------+---------+---------+------------
16394 | 16393 | pub1 | t1
(1 row)
postgres=# select * from pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
pub1 | public | t1_part2 | {a} |
(1 row)
Once detach session is finished:
SESSION A: COMMIT
This will make SESSION B's DETACH proceed.
After that, on SESSION C:
INSERT INTO t1_part1 VALUES (9); INSERT INTO t1 VALUES (19);
t1_part1 is not replicated (expected) while t1_part2 is replicated.
SUB:
postgres=# select * from t1_part1;
a
---
5
6
7
(3 rows)
postgres=# select * from t1_part2;
a
----
15
16
17
19
(4 rows)
