BUG #19884: pg_publication_tables omits a detach-pending partition in root mode
BUG #19884: pg_publication_tables omits a detach-pending partition in root mode
От:
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.
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.
Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode
чт, 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
Rachitskiy Andrey
Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode
От:
Nisha Moond <nisha.moond412@gmail.com>
Дата:
On Fri, Nov 13, 2026 at 3:16 AM Andrey Rachitskiy wrote: > > > > чт, 12 нояб. 2026 г. в 22:16, PG Bug reporting form : >> >> 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.gmail.com [2] https://www.postgresql.org/message-id/CADzfLwU97r3QC5oZDeVcWvcvXhNy9VnoFNDVHw%2BERg_DEG4%3DFQ%40mail.gmail.com -- Thanks, Nisha
Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode
От:
Masahiko Sawada <sawada.mshk@gmail.com>
Дата:
On Thu, Nov 12, 2026 at 8:43 PM Nisha Moond wrote: > > On Fri, Nov 13, 2026 at 3:16 AM Andrey Rachitskiy wrote: > > > > > > > > чт, 12 нояб. 2026 г. в 22:16, PG Bug reporting form : > >> > >> 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