BUG #19884: pg_publication_tables omits a detach-pending partition in root mode

The following bug has been logged on the website:

Bug reference: 19884
Logged by: Milo
Email address: 1548140642(at)qq(dot)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.