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

On Thu, Nov 12, 2026 at 9:30 AM Ajin Cherian <itsajin(at)gmail(dot)com> wrote:
>
> 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.
>

Thanks Ajin for the patch.

I see one issue in the patch: currently, when a publication is dropped
in between, we get incomplete output for the dropped pub. See this:

With no drop-pub executed:

postgres=# select * from pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
pub | public | part1 | {a} |
pub | public | tab1 | {i} |
pub | public | tab2 | {i} |
pub | public | tab3 | {i} |
(4 rows)

When pub is dropped after it has looped twice already, outcome:

postgres=# select * from pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
pub | public | part1 | {a} |
pub | public | tab1 | {i} |
(2 rows)

I feel the second outcome is misleading and inappropriate. The better
solution would be to skip the dropped pub completely or raise a clear
error.

There is more to it; the user may either run:

select * from pg_publication_tables;
Or
select pg_get_publication_tables('pub2', 'pub');

Ideally, in the first case (using `pg_publication_tables`), we should
skip the dropped publication completely, while in the second case
(using `pg_get_publication_tables('pub2', 'pub')`), an error seems
more suitable since the user explicitly requested information about a
specific publication.

I am not sure about the implementation complexity in this case. If the
implementation is tricky, consistent behaviour for both commands is
fine, as this scenario seems rare. Let's see what others say about
this.

thanks
Shveta