Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION

On Thu, Nov 12, 2026 at 2:16 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19864
> Logged by: Noah
> Email address: imchifan8(at)gmail(dot)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.
>

Hi Noah,

Thanks for the report and the clear reproducer. I was able to
reproduce this on HEAD.

pg_get_publication_tables() collects (publication OID, table OID)
pairs on its first call, then looks each publication up again with
GetPublication() for every row it returns. No lock is held on the
publication. While the function waits for the table lock in
try_table_open(), it processes pending invalidations, including the
one from the committed DROP PUBLICATION, so the following
GetPublication() fails with “cache lookup failed for publication”.

A similar problem with concurrently dropped tables was fixed in commit
63e7a0d2c3, which made the function skip tables that no longer exist.
The attached patch does the same for publications: if the publication
has been dropped, the row is skipped instead of raising an error. I
didn’t take a lock on the publication, because the tablesync worker
calls this function inside its long-running COPY transaction, and that
would make DROP PUBLICATION wait for the whole COPY. The patch also
adds a permutation to the existing pub-concurrent-drop isolation test.

It applies to HEAD; the same code exists in 16, 17 and 18.

Regards,
Ajin Cherian
Fujitsu Australia