pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19884: pg_publication_tables omits a detach-pending partition in root mode
4+ messages / 4 participants
[nested] [flat]
* BUG #19884: pg_publication_tables omits a detach-pending partition in root mode
@ 2026-11-11 14:13 PG Bug reporting form <noreply@postgresql.org>
2026-11-12 21:45 ` Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2026-11-11 14:13 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 1548140642@qq.com
The following bug has been logged on the website:
Bug reference: 19884
Logged by: Milo
Email address: 1548140642@qq.com
PostgreSQL version: 18.6
Operating system: Linux/amd64
Description:
After phase one of ALTER TABLE ... DETACH PARTITION CONCURRENTLY,
pg_publication_tables omits the detach-pending child from an ALL TABLES
publication when publish_via_partition_root is enabled. At that point the
child has no ancestors and is treated as a standalone relation for
publication purposes, so it should appear in the view. The query instead
returns zero rows.
How to repeat:
Save this as reproducer.sql and run it with psql -X -f reproducer.sql:
DROP PUBLICATION IF EXISTS pub_root;
DROP TABLE IF EXISTS parent_tbl CASCADE;
DROP TABLE IF EXISTS child_tbl;
CREATE EXTENSION dblink;
CREATE TABLE parent_tbl (id integer)
PARTITION BY RANGE (id);
CREATE TABLE child_tbl
PARTITION OF parent_tbl
FOR VALUES FROM (0) TO (100);
INSERT INTO parent_tbl VALUES (1);
CREATE PUBLICATION pub_root
FOR ALL TABLES
WITH (publish_via_partition_root = true);
SELECT dblink_connect('detacher',
'dbname=' || current_database());
SELECT dblink_connect('observer',
'dbname=' || current_database());
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM parent_tbl;
SELECT dblink_send_query(
'detacher',
'ALTER TABLE parent_tbl '
'DETACH PARTITION child_tbl CONCURRENTLY');
DO $wait$
DECLARE
pending boolean := false;
BEGIN
FOR attempt IN 1..200 LOOP
SELECT x.pending INTO pending
FROM dblink(
'observer',
$$SELECT inhdetachpending
FROM pg_catalog.pg_inherits
WHERE inhrelid = 'child_tbl'::regclass$$)
AS x(pending boolean);
EXIT WHEN pending;
PERFORM pg_sleep(0.025);
END LOOP;
IF NOT pending THEN
RAISE EXCEPTION 'concurrent detach did not reach pending state';
END IF;
END
$wait$;
SELECT dblink_cancel_query('detacher');
SELECT *
FROM dblink_get_result('detacher', false)
AS x(status text);
COMMIT;
SELECT i.inhdetachpending
FROM pg_catalog.pg_inherits AS i
WHERE i.inhrelid = 'child_tbl'::regclass;
SELECT count(*) AS visible_in_root_publication
FROM pg_catalog.pg_publication_tables
WHERE pubname = 'pub_root'
AND tablename = 'child_tbl';
SELECT dblink_disconnect('detacher');
SELECT dblink_disconnect('observer');
ALTER TABLE parent_tbl
DETACH PARTITION child_tbl FINALIZE;
DROP PUBLICATION pub_root;
DROP TABLE parent_tbl;
DROP TABLE child_tbl;
DROP EXTENSION dblink;
The decisive output is:
inhdetachpending
------------------
t
(1 row)
visible_in_root_publication
-----------------------------
0
(1 row)
The second value should be 1 because the detach-pending child has no
ancestors and therefore belongs in the root-mode publication as a standalone
table. The result appears to come from publication membership relying on the
partition catalog flag rather than the relation's effective ancestry while
detach is pending.
The issue was reproduced on PostgreSQL 18.6 on Linux/amd64. The run emitted
the warning that wal_level was insufficient to publish logical changes; the
publication and catalog-view query nevertheless completed. This occurs when
an interrupted concurrent partition detach is inspected through publication
membership with publish_via_partition_root enabled.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode
2026-11-11 14:13 BUG #19884: pg_publication_tables omits a detach-pending partition in root mode PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-12 21:45 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-11-13 04:42 ` Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode Nisha Moond <nisha.moond412@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andrey Rachitskiy @ 2026-11-12 21:45 UTC (permalink / raw)
To: 1548140642@qq.com; pgsql-bugs@lists.postgresql.org; +Cc: Masahiko Sawada <sawada.mshk@gmail.com>
чт, 12 нояб. 2026 г. в 22:16, PG Bug reporting form <noreply@postgresql.org
>:
> The following bug has been logged on the website:
>
> Bug reference: 19884
> Logged by: Milo
> Email address: 1548140642@qq.com
> PostgreSQL version: 18.6
> Operating system: Linux/amd64
> Description:
>
> After phase one of ALTER TABLE ... DETACH PARTITION CONCURRENTLY,
> pg_publication_tables omits the detach-pending child from an ALL TABLES
> publication when publish_via_partition_root is enabled. At that point the
> child has no ancestors and is treated as a standalone relation for
> publication purposes, so it should appear in the view. The query instead
> returns zero rows.
>
>
>
Hi Milo!
Thanks for the report.
I was also able to reproduce the problem on the master branch.
I think we can fix this by using get_partition_ancestors() in
pg_get_publication_tables().
After the pending detach commits, that list is empty, so the table stays in
the publication.
Patch in attachment.
Sawada-san in CC
--
Regards,
Rachitskiy Andrey
Attachments:
0001-Fix-publication-membership-of-a-partition-pending-de.patch (text/x-patch, 6.2K) download
From ab091678466269c32a98fe93356346a1fa869837 Mon Sep 17 00:00:00 2001
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Fri, 13 Nov 2026 01:43:20 +0500
Subject: [PATCH] Fix publication membership of a partition pending detach.
DETACH PARTITION CONCURRENTLY commits inhdetachpending before FINALIZE
clears relispartition. get_partition_ancestors() is then empty, and
pg_get_publication_tables() still skips the table because
relispartition is set. A FOR ALL TABLES publication with
publish_via_partition_root omits it. Use that ancestor list for the
check. An empty list leaves the table in the publication.
BUG #19884
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reported-by: Milo <1548140642@qq.com>
Discussion: https://www.postgresql.org/message-id/19884-3b4fbbdf292f33f1@postgresql.org
---
src/backend/catalog/pg_publication.c | 38 ++++++++++++++-----
.../isolation/expected/pub-detach-pending.out | 13 +++++++
src/test/isolation/isolation_schedule | 1 +
.../isolation/specs/pub-detach-pending.spec | 27 +++++++++++++
4 files changed, 70 insertions(+), 9 deletions(-)
create mode 100644 src/test/isolation/expected/pub-detach-pending.out
create mode 100644 src/test/isolation/specs/pub-detach-pending.spec
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index f11ea8839ca..36aec816d83 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1077,6 +1077,27 @@ GetAllTablesPublications(void)
return result;
}
+/*
+ * relispartition stays set until DETACH PARTITION ... FINALIZE.
+ * get_partition_ancestors() is empty after the first transaction of
+ * DETACH PARTITION ... CONCURRENTLY commits. The table is then published
+ * as itself.
+ */
+static bool
+pub_rel_is_partition(Oid relid, bool relispartition)
+{
+ List *ancestors;
+ bool result;
+
+ if (!relispartition)
+ return false;
+
+ ancestors = get_partition_ancestors(relid);
+ result = (ancestors != NIL);
+ list_free(ancestors);
+ return result;
+}
+
/*
* Gets list of all relations published by FOR ALL TABLES/SEQUENCES
* publication.
@@ -1122,7 +1143,8 @@ GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot)
Oid relid = relForm->oid;
if (is_publishable_class(relid, relForm) &&
- !(relForm->relispartition && pubviaroot) &&
+ !(pubviaroot &&
+ pub_rel_is_partition(relid, relForm->relispartition)) &&
!list_member_oid(exceptlist, relid))
result = lappend_oid(result, relid);
}
@@ -1144,7 +1166,7 @@ GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot)
Oid relid = relForm->oid;
if (is_publishable_class(relid, relForm) &&
- !relForm->relispartition &&
+ !pub_rel_is_partition(relid, relForm->relispartition) &&
!list_member_oid(exceptlist, relid))
result = lappend_oid(result, relid);
}
@@ -1388,10 +1410,12 @@ is_table_publishable_in_publication(Oid relid, Publication *pub)
if (pub->alltables)
{
/*
- * ALL TABLES with pubviaroot includes only regular tables or top-most
- * partitioned tables -- never child partitions.
+ * ALL TABLES with pubviaroot publishes regular tables and top-most
+ * partitioned tables. A partition that still has ancestors is covered
+ * by its root. Pending detach leaves that list empty, so the table is
+ * published as itself.
*/
- if (pub->pubviaroot && relispartition)
+ if (pub->pubviaroot && ancestors != NIL)
return false;
/*
@@ -1400,10 +1424,6 @@ is_table_publishable_in_publication(Oid relid, Publication *pub)
* EXCEPT clause, so exclusion must be evaluated at the top-most
* ancestor if it has. These publications store only EXCEPT'ed tables
* in pg_publication_rel, so checking existence is sufficient.
- *
- * Note that this existence check below would incorrectly return true
- * (published) for partitions when pubviaroot is enabled; however,
- * that case is already caught and returned false by the above check.
*/
return !SearchSysCacheExists2(PUBLICATIONRELMAP,
ObjectIdGetDatum(ancestors
diff --git a/src/test/isolation/expected/pub-detach-pending.out b/src/test/isolation/expected/pub-detach-pending.out
new file mode 100644
index 00000000000..4acd019d49a
--- /dev/null
+++ b/src/test/isolation/expected/pub-detach-pending.out
@@ -0,0 +1,13 @@
+Parsed test spec with 3 sessions
+
+starting permutation: lock detach listed unlock
+step lock: BEGIN; LOCK TABLE parent_tbl IN ACCESS SHARE MODE;
+step detach: ALTER TABLE parent_tbl DETACH PARTITION child_tbl CONCURRENTLY; <waiting ...>
+step listed: SELECT tablename FROM pg_publication_tables WHERE pubname = 'pub' AND tablename = 'child_tbl';
+tablename
+---------
+child_tbl
+(1 row)
+
+step unlock: COMMIT;
+step detach: <... completed>
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index f448e4d6e13..e8ee37fd5e9 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -135,5 +135,6 @@ test: lock-nowait
test: ddl-dependency-locking
test: tablespace-dependency-locking
test: pub-concurrent-drop
+test: pub-detach-pending
test: type-ddl-locking
test: drop-owned-grant
diff --git a/src/test/isolation/specs/pub-detach-pending.spec b/src/test/isolation/specs/pub-detach-pending.spec
new file mode 100644
index 00000000000..e5cc4a6bd0c
--- /dev/null
+++ b/src/test/isolation/specs/pub-detach-pending.spec
@@ -0,0 +1,27 @@
+# Pending detach commits inhdetachpending, then waits. An ALL TABLES
+# publication with publish_via_partition_root must list that partition.
+
+setup
+{
+ CREATE TABLE parent_tbl (id int) PARTITION BY RANGE (id);
+ CREATE TABLE child_tbl PARTITION OF parent_tbl FOR VALUES FROM (0) TO (100);
+ CREATE PUBLICATION pub FOR ALL TABLES WITH (publish_via_partition_root = true);
+}
+
+teardown
+{
+ DROP PUBLICATION pub;
+ DROP TABLE parent_tbl, child_tbl;
+}
+
+session s1
+step lock { BEGIN; LOCK TABLE parent_tbl IN ACCESS SHARE MODE; }
+step unlock { COMMIT; }
+
+session s2
+step detach { ALTER TABLE parent_tbl DETACH PARTITION child_tbl CONCURRENTLY; }
+
+session s3
+step listed { SELECT tablename FROM pg_publication_tables WHERE pubname = 'pub' AND tablename = 'child_tbl'; }
+
+permutation lock detach listed unlock
--
2.53.0
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode
2026-11-11 14:13 BUG #19884: pg_publication_tables omits a detach-pending partition in root mode PG Bug reporting form <noreply@postgresql.org>
2026-11-12 21:45 ` Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-11-13 04:42 ` Nisha Moond <nisha.moond412@gmail.com>
2026-11-14 01:44 ` Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Nisha Moond @ 2026-11-13 04:42 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: 1548140642@qq.com, pgsql-bugs@lists.postgresql.org, Masahiko Sawada <sawada.mshk@gmail.com>
On Fri, Nov 13, 2026 at 3:16 AM Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:
>
>
>
> чт, 12 нояб. 2026 г. в 22:16, PG Bug reporting form <noreply@postgresql.org>:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference: 19884
>> Logged by: Milo
>> Email address: 1548140642@qq.com
>> PostgreSQL version: 18.6
>> Operating system: Linux/amd64
>> Description:
>>
>> After phase one of ALTER TABLE ... DETACH PARTITION CONCURRENTLY,
>> pg_publication_tables omits the detach-pending child from an ALL TABLES
>> publication when publish_via_partition_root is enabled. At that point the
>> child has no ancestors and is treated as a standalone relation for
>> publication purposes, so it should appear in the view. The query instead
>> returns zero rows.
>>
>>
>
> Hi Milo!
>
> Thanks for the report.
>
> I was also able to reproduce the problem on the master branch.
>
> I think we can fix this by using get_partition_ancestors() in pg_get_publication_tables().
> After the pending detach commits, that list is empty, so the table stays in the publication.
>
> Patch in attachment.
>
Thanks for the report and the patch. This appears to be the same issue
reported in [1]. Patch v2-0003 shared in [2] fixes it for ALL TABLES
publications, and also for the EXCEPT case, which is affected by the
same root cause.
[1] https://www.postgresql.org/message-id/CAN4CZFOxn6rv52YJRXE56sUx5iB-njB854o9jYLx2e-wO9hiAA%40mail.gma...
[2] https://www.postgresql.org/message-id/CADzfLwU97r3QC5oZDeVcWvcvXhNy9VnoFNDVHw%2BERg_DEG4%3DFQ%40mail...
--
Thanks,
Nisha
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode
2026-11-11 14:13 BUG #19884: pg_publication_tables omits a detach-pending partition in root mode PG Bug reporting form <noreply@postgresql.org>
2026-11-12 21:45 ` Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-11-13 04:42 ` Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode Nisha Moond <nisha.moond412@gmail.com>
@ 2026-11-14 01:44 ` Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Masahiko Sawada @ 2026-11-14 01:44 UTC (permalink / raw)
To: Nisha Moond <nisha.moond412@gmail.com>; +Cc: Andrey Rachitskiy <pl0h0yp1@gmail.com>; 1548140642@qq.com; pgsql-bugs@lists.postgresql.org
On Thu, Nov 12, 2026 at 8:43 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
>
> On Fri, Nov 13, 2026 at 3:16 AM Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:
> >
> >
> >
> > чт, 12 нояб. 2026 г. в 22:16, PG Bug reporting form <noreply@postgresql.org>:
> >>
> >> The following bug has been logged on the website:
> >>
> >> Bug reference: 19884
> >> Logged by: Milo
> >> Email address: 1548140642@qq.com
> >> PostgreSQL version: 18.6
> >> Operating system: Linux/amd64
> >> Description:
> >>
> >> After phase one of ALTER TABLE ... DETACH PARTITION CONCURRENTLY,
> >> pg_publication_tables omits the detach-pending child from an ALL TABLES
> >> publication when publish_via_partition_root is enabled. At that point the
> >> child has no ancestors and is treated as a standalone relation for
> >> publication purposes, so it should appear in the view. The query instead
> >> returns zero rows.
> >>
> >>
> >
> > Hi Milo!
> >
> > Thanks for the report.
> >
> > I was also able to reproduce the problem on the master branch.
> >
> > I think we can fix this by using get_partition_ancestors() in pg_get_publication_tables().
> > After the pending detach commits, that list is empty, so the table stays in the publication.
> >
> > Patch in attachment.
> >
>
> Thanks for the report and the patch. This appears to be the same issue
> reported in [1]. Patch v2-0003 shared in [2] fixes it for ALL TABLES
> publications, and also for the EXCEPT case, which is affected by the
> same root cause.
Yes, this has the same root cause and I've also confirmed that these
patches fix the issue reported here.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-11-14 01:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-11 14:13 BUG #19884: pg_publication_tables omits a detach-pending partition in root mode PG Bug reporting form <noreply@postgresql.org>
2026-11-12 21:45 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-11-13 04:42 ` Nisha Moond <nisha.moond412@gmail.com>
2026-11-14 01:44 ` Masahiko Sawada <sawada.mshk@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox