pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: imchifan8@gmail.com
Subject: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
Date: Wed, 11 Nov 2026 05:33:01 +0000
Message-ID: <19864-5260a1fbeec7728c@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19864
Logged by:          Noah
Email address:      imchifan8@gmail.com
PostgreSQL version: 18.6
Operating system:   Linux/amd64
Description:        

pg_get_publication_tables() raises an internal cache lookup error when it
blocks while opening a published table and another session drops and commits
the publication before the function resumes. A multi-call catalog SRF should
safely handle retained object identifiers after concurrent removal, either
by omitting stale rows or otherwise completing without an internal cache
lookup failure.

How to repeat:

The dblink extension must be available. Run the following as one script in a
fresh database using psql:

DROP PUBLICATION IF EXISTS race_pub;
DROP SCHEMA IF EXISTS race_schema CASCADE;
CREATE EXTENSION dblink;
CREATE SCHEMA race_schema;
CREATE TABLE race_schema.race_table (id integer);
CREATE PUBLICATION race_pub FOR TABLES IN SCHEMA race_schema;

BEGIN;
LOCK TABLE race_schema.race_table IN ACCESS EXCLUSIVE MODE;

SELECT dblink_connect(
  'race_conn',
  'dbname=' || current_database());
SELECT dblink_send_query(
  'race_conn',
  $$SELECT relid::regclass::text
    FROM pg_get_publication_tables('race_pub')
    ORDER BY 1$$);

SELECT pg_sleep(1);
SELECT EXISTS (
  SELECT 1
  FROM pg_locks
  WHERE relation = 'race_schema.race_table'::regclass
    AND mode = 'AccessShareLock'
    AND NOT granted
) AS remote_enumerator_waiting_on_table;

DROP PUBLICATION race_pub;
COMMIT;

SELECT *
FROM dblink_get_result('race_conn')
  AS result(published_table text);
SELECT dblink_disconnect('race_conn');

The lock check returned true, confirming that the remote call was waiting to
open the table. After DROP PUBLICATION committed, dblink_get_result()
reported:

ERROR:  cache lookup failed for publication 17010
CONTEXT:  while executing query on dblink connection named "race_conn"

The affected statement failed, but PostgreSQL remained healthy. Retrying
after the concurrent catalog DDL has finished allows the introspection
operation to proceed.

The runtime failure was reproduced on PostgreSQL 18.6 on Linux/amd64.
Separately, inspection of PostgreSQL 20devel source found the same relevant
path: pg_get_publication_tables() retains publication OIDs across SRF calls
and later passes each retained OID to GetPublication(). Its concurrent-drop
handling tolerates a missing relation through try_table_open(), but not a
publication that has disappeared.

This occurs when publication tables are enumerated while concurrent
publication DDL removes the publication.








view thread (14+ messages)  latest in thread

Message-ID: <19864-5260a1fbeec7728c@postgresql.org>
Permalink:  ../19864-5260a1fbeec7728c@postgresql.org/
Also on:    postgresql.org/message-id/19864-5260a1fbeec7728c@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, imchifan8@gmail.com
  Subject: Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  In-Reply-To: <19864-5260a1fbeec7728c@postgresql.org>
  References: <19864-5260a1fbeec7728c@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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