pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
14+ messages / 5 participants
[nested] [flat]

* BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
@ 2026-11-11 05:33 PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: PG Bug reporting form @ 2026-11-11 05:33 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: imchifan8@gmail.com

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.








^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-12 04:00 ` Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Ajin Cherian @ 2026-11-12 04:00 UTC (permalink / raw)
  To: imchifan8@gmail.com, pgsql-bugs@lists.postgresql.org, Masahiko Sawada <sawada.mshk@gmail.com>

On Thu, Nov 12, 2026 at 2:16 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> 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.
>

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

Attachments:

v1-0001-Fix-cache-lookup-failure-during-concurrent-DROP-P.patch (application/octet-stream, 5.1K) download From 6796a27c885276a4c19fadef2d757adff4acd178 Mon Sep 17 00:00:00 2001 From: Ajin Cherian <itsajin@gmail.com> Date: Thu, 12 Nov 2026 14:50:26 +1100 Subject: [PATCH v1] Fix cache lookup failure during concurrent DROP PUBLICATION pg_get_publication_tables() collects (pubid, relid) pairs on its first call and then looks each publication up again per row with GetPublication(). No lock is held on the publication, so if it is dropped concurrently -- for instance while the function waits for the lock in try_table_open(), which also processes invalidations -- the lookup fails with "cache lookup failed for publication". Reported-by: Noah <imchifan8@gmail.com> Discussion: https://postgr.es/m/19864-5260a1fbeec7728c@postgresql.org --- src/backend/catalog/pg_publication.c | 33 ++++++++++++++++--- .../expected/pub-concurrent-drop.out | 14 ++++++++ .../isolation/specs/pub-concurrent-drop.spec | 11 +++++-- 3 files changed, 52 insertions(+), 6 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index f11ea8839ca..76771e22c23 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1305,9 +1305,12 @@ GetAllSchemaPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt) * Get publication using oid * * The Publication struct and its data are palloc'ed here. + * + * If missing_ok is true, return NULL when the publication does not exist + * (e.g., it was dropped concurrently); otherwise raise an error. */ -Publication * -GetPublication(Oid pubid) +static Publication * +get_publication_internal(Oid pubid, bool missing_ok) { HeapTuple tup; Publication *pub; @@ -1315,7 +1318,11 @@ GetPublication(Oid pubid) tup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(pubid)); if (!HeapTupleIsValid(tup)) + { + if (missing_ok) + return NULL; elog(ERROR, "cache lookup failed for publication %u", pubid); + } pubform = (Form_pg_publication) GETSTRUCT(tup); @@ -1336,6 +1343,12 @@ GetPublication(Oid pubid) return pub; } +Publication * +GetPublication(Oid pubid) +{ + return get_publication_internal(pubid, false); +} + /* * Get Publication using name. */ @@ -1647,10 +1660,22 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, continue; /* - * Form tuple with appropriate data. + * Likewise, the publication OIDs were collected earlier and we don't + * hold a lock on the publication, so it may have been dropped + * concurrently (for instance, while we waited for the lock on the + * table above, which also processes pending invalidations). Skip the + * row in that case, for the same point-in-time reasoning as above. */ + pub = get_publication_internal(table_info->pubid, true); + if (pub == NULL) + { + table_close(rel, AccessShareLock); + continue; + } - pub = GetPublication(table_info->pubid); + /* + * Form tuple with appropriate data. + */ values[0] = ObjectIdGetDatum(pub->oid); values[1] = ObjectIdGetDatum(relid); diff --git a/src/test/isolation/expected/pub-concurrent-drop.out b/src/test/isolation/expected/pub-concurrent-drop.out index 8360af0ec9c..28f8e630b70 100644 --- a/src/test/isolation/expected/pub-concurrent-drop.out +++ b/src/test/isolation/expected/pub-concurrent-drop.out @@ -14,3 +14,17 @@ tablename pubdrop.keepme (1 row) + +starting permutation: lock list_pub_tables drop_pub_and_commit +step lock: BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; +step list_pub_tables: + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables('pub_schema') + ORDER BY tablename; + <waiting ...> +step drop_pub_and_commit: DROP PUBLICATION pub_schema; COMMIT; +step list_pub_tables: <... completed> +tablename +--------- +(0 rows) + diff --git a/src/test/isolation/specs/pub-concurrent-drop.spec b/src/test/isolation/specs/pub-concurrent-drop.spec index 4f7d701d60c..2f52208750b 100644 --- a/src/test/isolation/specs/pub-concurrent-drop.spec +++ b/src/test/isolation/specs/pub-concurrent-drop.spec @@ -1,4 +1,4 @@ -# Tests for concurrently dropping a relation while a publication's tables are +# Tests for concurrently dropping a relation or the publication itself while a publication's tables are # being listed. setup @@ -12,12 +12,13 @@ setup teardown { DROP SCHEMA pubdrop CASCADE; - DROP PUBLICATION pub_schema; + DROP PUBLICATION IF EXISTS pub_schema; } session s1 step lock { BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; } step drop_and_commit { DROP TABLE pubdrop.dropme; COMMIT; } +step drop_pub_and_commit { DROP PUBLICATION pub_schema; COMMIT; } session s2 step list_pub_tables @@ -34,3 +35,9 @@ step list_pub_tables # skips the now-dropped table instead of erroring with "could not open relation # with OID". permutation lock list_pub_tables drop_and_commit + +# Same as above, but drop the publication instead of the table. When the query +# resumes, the publication it collected the table OIDs from is gone, so the +# rows for it are skipped instead of erroring with "cache lookup failed for +# publication". +permutation lock list_pub_tables drop_pub_and_commit -- 2.47.3
^ permalink raw reply [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
@ 2026-11-12 05:20   ` shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: shveta malik @ 2026-11-12 05:20 UTC (permalink / raw)
  To: Ajin Cherian <itsajin@gmail.com>; +Cc: imchifan8@gmail.com, pgsql-bugs@lists.postgresql.org, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

On Thu, Nov 12, 2026 at 9:30 AM Ajin Cherian <itsajin@gmail.com> wrote:
>
> On Thu, Nov 12, 2026 at 2:16 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
> >
> > 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.
> >
>
> 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






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
@ 2026-11-12 10:12     ` Ajin Cherian <itsajin@gmail.com>
  2026-11-12 10:37       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Ajin Cherian @ 2026-11-12 10:12 UTC (permalink / raw)
  To: shveta malik <shveta.malik@gmail.com>; +Cc: imchifan8@gmail.com, pgsql-bugs@lists.postgresql.org, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

On Thu, Nov 12, 2026 at 4:20 PM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Thu, Nov 12, 2026 at 9:30 AM Ajin Cherian <itsajin@gmail.com> wrote:
> >
> 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.

Hi Shveta,

Thanks for the review. I agree that the partial output is misleading.

Skipping the dropped publication completely in pg_publication_tables
turns out to be tricky. The view and an explicit call reach the same C
function with the same arguments (the view passes one publication name
per LATERAL call), so the function can’t tell them apart without
changing the view definition, which needs a catversion bump and can’t
be back-patched. Also, by the time the drop is noticed, some rows for
that publication may already have been returned, so skipping it
“completely” would mean rewriting the function completely. Since this
bug exists in 16 through 18 and the scenario is rare, I’ve gone with
consistent behaviour for both commands, as you suggested.

The attached v2 raises a clear error when a publication is dropped
while its tables are being listed, both for the view and for explicit
calls:

ERROR: publication “pub” was concurrently dropped

That’s in line with what the view already does today when a
publication is dropped before its turn comes (ERROR: publication “pub”
does not exist). The only exception is the
pg_get_publication_tables(text[], oid) variant used by tablesync. It’s
called with pub_missing_ok = true and already ignores missing
publications on its first call, so it keeps skipping the row. The
isolation test covers both cases.

Regards,
Ajin Cherian
Fujitsu Australia

Attachments:

v2-0001-Fix-cache-lookup-failure-in-pg_get_publication_ta.patch (application/octet-stream, 8.0K) download From 63dafeb913fd4102181facf5e0dcc0060075a097 Mon Sep 17 00:00:00 2001 From: Ajin Cherian <itsajin@gmail.com> Date: Thu, 12 Nov 2026 20:01:16 +1100 Subject: [PATCH v2] Fix cache lookup failure in pg_get_publication_tables() after concurrent DROP PUBLICATION pg_get_publication_tables() collects (pubid, relid) pairs on its first call and then looks each publication up again per row with GetPublication(). No lock is held on the publication, so if it is dropped concurrently -- for instance while the function waits for the lock in try_table_open(), which also processes invalidations -- the lookup fails with an internal "cache lookup failed for publication" error. Reported-by: Noah <imchifan8@gmail.com> Discussion: https://postgr.es/m/19864-5260a1fbeec7728c@postgresql.org --- src/backend/catalog/pg_publication.c | 65 +++++++++++++++++-- .../expected/pub-concurrent-drop.out | 24 +++++++ .../isolation/specs/pub-concurrent-drop.spec | 21 +++++- 3 files changed, 104 insertions(+), 6 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index f11ea8839ca..cb72b269164 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1305,9 +1305,12 @@ GetAllSchemaPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt) * Get publication using oid * * The Publication struct and its data are palloc'ed here. + * + * If missing_ok is true, return NULL when the publication does not exist + * (e.g., it was dropped concurrently); otherwise raise an error. */ -Publication * -GetPublication(Oid pubid) +static Publication * +get_publication_internal(Oid pubid, bool missing_ok) { HeapTuple tup; Publication *pub; @@ -1315,7 +1318,11 @@ GetPublication(Oid pubid) tup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(pubid)); if (!HeapTupleIsValid(tup)) + { + if (missing_ok) + return NULL; elog(ERROR, "cache lookup failed for publication %u", pubid); + } pubform = (Form_pg_publication) GETSTRUCT(tup); @@ -1336,6 +1343,12 @@ GetPublication(Oid pubid) return pub; } +Publication * +GetPublication(Oid pubid) +{ + return get_publication_internal(pubid, false); +} + /* * Get Publication using name. */ @@ -1471,6 +1484,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, typedef struct { List *table_infos; /* list of published_rel */ + List *pubs; /* list of Publication, for error messages */ int curr_idx; /* current index into table_infos */ } publication_tables_state; @@ -1483,6 +1497,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, TupleDesc tupdesc; MemoryContext oldcontext; List *table_infos = NIL; + List *pubs = NIL; Datum *elems; int nelems, i; @@ -1521,6 +1536,8 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, if (pub_elem == NULL) continue; + pubs = lappend(pubs, pub_elem); + if (filter_by_relid) { /* Check if the given table is published for the publication */ @@ -1609,6 +1626,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, /* Store the state to be used across SRF calls. */ ptstate = palloc_object(publication_tables_state); ptstate->table_infos = table_infos; + ptstate->pubs = pubs; ptstate->curr_idx = 0; funcctx->user_fctx = ptstate; @@ -1647,10 +1665,49 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, continue; /* - * Form tuple with appropriate data. + * The publication OIDs were also collected earlier and we don't hold + * a lock on the publication, so it may have been dropped concurrently + * (for instance, while we waited for the lock on the table above, + * which also processes pending invalidations). + * + * Unlike a dropped table, we can't just skip it: rows for this + * publication may already have been returned, so the result would be + * a misleading partial list. Report it as an error instead, unless + * the caller asked for missing publications to be ignored, which is + * consistent with how they are treated on the first call. */ + pub = get_publication_internal(table_info->pubid, true); + if (pub == NULL) + { + char *pubname = NULL; + ListCell *lc; - pub = GetPublication(table_info->pubid); + table_close(rel, AccessShareLock); + + if (pub_missing_ok) + continue; + + foreach(lc, ptstate->pubs) + { + Publication *p = (Publication *) lfirst(lc); + + if (p->oid == table_info->pubid) + { + pubname = p->name; + break; + } + } + Assert(pubname != NULL); + + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication \"%s\" was concurrently dropped", + pubname))); + } + + /* + * Form tuple with appropriate data. + */ values[0] = ObjectIdGetDatum(pub->oid); values[1] = ObjectIdGetDatum(relid); diff --git a/src/test/isolation/expected/pub-concurrent-drop.out b/src/test/isolation/expected/pub-concurrent-drop.out index 8360af0ec9c..b9a600c185d 100644 --- a/src/test/isolation/expected/pub-concurrent-drop.out +++ b/src/test/isolation/expected/pub-concurrent-drop.out @@ -14,3 +14,27 @@ tablename pubdrop.keepme (1 row) + +starting permutation: lock list_pub_tables drop_pub_and_commit +step lock: BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; +step list_pub_tables: + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables('pub_schema') + ORDER BY tablename; + <waiting ...> +step drop_pub_and_commit: DROP PUBLICATION pub_schema; COMMIT; +step list_pub_tables: <... completed> +ERROR: publication "pub_schema" was concurrently dropped + +starting permutation: lock list_pub_table drop_pub_and_commit +step lock: BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; +step list_pub_table: + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables(ARRAY['pub_schema'], 'pubdrop.dropme'::regclass); + <waiting ...> +step drop_pub_and_commit: DROP PUBLICATION pub_schema; COMMIT; +step list_pub_table: <... completed> +tablename +--------- +(0 rows) + diff --git a/src/test/isolation/specs/pub-concurrent-drop.spec b/src/test/isolation/specs/pub-concurrent-drop.spec index 4f7d701d60c..3b41ced1ba5 100644 --- a/src/test/isolation/specs/pub-concurrent-drop.spec +++ b/src/test/isolation/specs/pub-concurrent-drop.spec @@ -1,4 +1,4 @@ -# Tests for concurrently dropping a relation while a publication's tables are +# Tests for concurrently dropping a relation or the publication itself while a publication's tables are # being listed. setup @@ -12,12 +12,13 @@ setup teardown { DROP SCHEMA pubdrop CASCADE; - DROP PUBLICATION pub_schema; + DROP PUBLICATION IF EXISTS pub_schema; } session s1 step lock { BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; } step drop_and_commit { DROP TABLE pubdrop.dropme; COMMIT; } +step drop_pub_and_commit { DROP PUBLICATION pub_schema; COMMIT; } session s2 step list_pub_tables @@ -26,6 +27,11 @@ step list_pub_tables FROM pg_get_publication_tables('pub_schema') ORDER BY tablename; } +step list_pub_table +{ + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables(ARRAY['pub_schema'], 'pubdrop.dropme'::regclass); +} # Hold an ACCESS EXCLUSIVE lock on the table in one session, so that the query # listing a publication's tables in another session blocks when it tries to @@ -34,3 +40,14 @@ step list_pub_tables # skips the now-dropped table instead of erroring with "could not open relation # with OID". permutation lock list_pub_tables drop_and_commit + +# Same as above, but drop the publication instead of the table. When the +# query resumes, the publication it collected the table OIDs from is gone. +# Rather than returning a partial list, it reports that the publication was +# concurrently dropped instead of failing with "cache lookup failed for +# publication". +permutation lock list_pub_tables drop_pub_and_commit + +# The variant that takes a table OID ignores missing publications, so it +# skips the row for the dropped publication instead. +permutation lock list_pub_table drop_pub_and_commit -- 2.47.3
^ permalink raw reply [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
@ 2026-11-12 10:37       ` shveta malik <shveta.malik@gmail.com>
  2026-11-12 12:16         ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: shveta malik @ 2026-11-12 10:37 UTC (permalink / raw)
  To: Ajin Cherian <itsajin@gmail.com>; +Cc: imchifan8@gmail.com, pgsql-bugs@lists.postgresql.org, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

On Thu, Nov 12, 2026 at 3:42 PM Ajin Cherian <itsajin@gmail.com> wrote:
>
> On Thu, Nov 12, 2026 at 4:20 PM shveta malik <shveta.malik@gmail.com> wrote:
> >
> > On Thu, Nov 12, 2026 at 9:30 AM Ajin Cherian <itsajin@gmail.com> wrote:
> > >
> > 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.
>
> Hi Shveta,
>
> Thanks for the review. I agree that the partial output is misleading.
>
> Skipping the dropped publication completely in pg_publication_tables
> turns out to be tricky. The view and an explicit call reach the same C
> function with the same arguments (the view passes one publication name
> per LATERAL call), so the function can’t tell them apart without
> changing the view definition, which needs a catversion bump and can’t
> be back-patched. Also, by the time the drop is noticed, some rows for
> that publication may already have been returned, so skipping it
> “completely” would mean rewriting the function completely.

Okay, got your point.

> Since this
> bug exists in 16 through 18 and the scenario is rare, I’ve gone with
> consistent behaviour for both commands, as you suggested.

Okay.

> The attached v2 raises a clear error when a publication is dropped
> while its tables are being listed, both for the view and for explicit
> calls:
>
> ERROR: publication “pub” was concurrently dropped
>
> That’s in line with what the view already does today when a
> publication is dropped before its turn comes (ERROR: publication “pub”
> does not exist). The only exception is the
> pg_get_publication_tables(text[], oid) variant used by tablesync. It’s
> called with pub_missing_ok = true and already ignores missing
> publications on its first call, so it keeps skipping the row. The
> isolation test covers both cases.

Okay. I am fine with errors in both cases. I will review and verify in
depth soon.

thanks
Shveta






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 10:37       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
@ 2026-11-12 12:16         ` shveta malik <shveta.malik@gmail.com>
  2026-11-13 03:51           ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: shveta malik @ 2026-11-12 12:16 UTC (permalink / raw)
  To: Ajin Cherian <itsajin@gmail.com>; +Cc: imchifan8@gmail.com, pgsql-bugs@lists.postgresql.org, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

On Thu, Nov 12, 2026 at 4:07 PM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Thu, Nov 12, 2026 at 3:42 PM Ajin Cherian <itsajin@gmail.com> wrote:
> >
> > On Thu, Nov 12, 2026 at 4:20 PM shveta malik <shveta.malik@gmail.com> wrote:
> > >
> > > On Thu, Nov 12, 2026 at 9:30 AM Ajin Cherian <itsajin@gmail.com> wrote:
> > > >
> > > 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.
> >
> > Hi Shveta,
> >
> > Thanks for the review. I agree that the partial output is misleading.
> >
> > Skipping the dropped publication completely in pg_publication_tables
> > turns out to be tricky. The view and an explicit call reach the same C
> > function with the same arguments (the view passes one publication name
> > per LATERAL call), so the function can’t tell them apart without
> > changing the view definition, which needs a catversion bump and can’t
> > be back-patched. Also, by the time the drop is noticed, some rows for
> > that publication may already have been returned, so skipping it
> > “completely” would mean rewriting the function completely.
>
> Okay, got your point.
>
> > Since this
> > bug exists in 16 through 18 and the scenario is rare, I’ve gone with
> > consistent behaviour for both commands, as you suggested.
>
> Okay.
>
> > The attached v2 raises a clear error when a publication is dropped
> > while its tables are being listed, both for the view and for explicit
> > calls:
> >
> > ERROR: publication “pub” was concurrently dropped
> >
> > That’s in line with what the view already does today when a
> > publication is dropped before its turn comes (ERROR: publication “pub”
> > does not exist). The only exception is the
> > pg_get_publication_tables(text[], oid) variant used by tablesync. It’s
> > called with pub_missing_ok = true and already ignores missing
> > publications on its first call, so it keeps skipping the row. The
> > isolation test covers both cases.
>
> Okay. I am fine with errors in both cases. I will review and verify in
> depth soon.
>

I verified the known cases, they work well. I will verify a bit more tomorrow.
A few code comments:

1)
I noticed that the same condition now produces 2 different msgs
depending on timing, which I initially thought was okay.

(Cases: If the publication is dropped before its name is resolved on
the first call, the result is publication "pub" does not exist. If
it's dropped later, while a row is being formed, the result is
publication "pub" was concurrently dropped).

But on rethinking, it will be better to make the messages consistent.
How about moving 'concurrently dropped' part to errdetail?

ereport(ERROR,
        (errcode(ERRCODE_UNDEFINED_OBJECT),
         errmsg("publication \"%s\" does not exist", pubname),
         errdetail("The publication was dropped concurrently.")));

2)
Also I feel, it will be better to separate the logic of finding
pub-name from core logic. Suggestion:

   pub = get_publication_internal(table_info->pubid, true);
   if (pub == NULL)
   {
       if (!pub_missing_ok)
           ereport(ERROR,
                   errcode(ERRCODE_UNDEFINED_OBJECT),
                   errmsg("publication \"%s\" does not exist",
                          get_pubname_from_list(ptstate->pubs,
                                                table_info->pubid)),
                   errdetail("The publication was dropped concurrently."));

       table_close(rel, AccessShareLock);
       continue;
   }

static char *
get_pubname_from_list(List *pubs, Oid pubid)
 {
       foreach_ptr(Publication, p, pubs)
       {
           if (p->oid == pubid)
               return p->name;
       }

       Assert(false);
       return NULL;
}

thanks
Shveta






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 10:37       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 12:16         ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
@ 2026-11-13 03:51           ` Ajin Cherian <itsajin@gmail.com>
  2026-11-13 06:28             ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Ajin Cherian @ 2026-11-13 03:51 UTC (permalink / raw)
  To: shveta malik <shveta.malik@gmail.com>; +Cc: imchifan8@gmail.com, pgsql-bugs@lists.postgresql.org, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

On Thu, Nov 12, 2026 at 11:16 PM shveta malik <shveta.malik@gmail.com> wrote:
>
>
> I verified the known cases, they work well. I will verify a bit more tomorrow.
> A few code comments:
>
> 1)
> I noticed that the same condition now produces 2 different msgs
> depending on timing, which I initially thought was okay.
>
> (Cases: If the publication is dropped before its name is resolved on
> the first call, the result is publication "pub" does not exist. If
> it's dropped later, while a row is being formed, the result is
> publication "pub" was concurrently dropped).
>
> But on rethinking, it will be better to make the messages consistent.
> How about moving 'concurrently dropped' part to errdetail?
>
> ereport(ERROR,
>         (errcode(ERRCODE_UNDEFINED_OBJECT),
>          errmsg("publication \"%s\" does not exist", pubname),
>          errdetail("The publication was dropped concurrently.")));
>
> 2)
> Also I feel, it will be better to separate the logic of finding
> pub-name from core logic. Suggestion:
>
>    pub = get_publication_internal(table_info->pubid, true);
>    if (pub == NULL)
>    {
>        if (!pub_missing_ok)
>            ereport(ERROR,
>                    errcode(ERRCODE_UNDEFINED_OBJECT),
>                    errmsg("publication \"%s\" does not exist",
>                           get_pubname_from_list(ptstate->pubs,
>                                                 table_info->pubid)),
>                    errdetail("The publication was dropped concurrently."));
>
>        table_close(rel, AccessShareLock);
>        continue;
>    }
>
> static char *
> get_pubname_from_list(List *pubs, Oid pubid)
>  {
>        foreach_ptr(Publication, p, pubs)
>        {
>            if (p->oid == pubid)
>                return p->name;
>        }
>
>        Assert(false);
>        return NULL;
> }
>
> thanks
> Shveta

Thanks for the review and the testing, Shveta.
Both suggestions make sense, and the attached v3 does both. The error
now uses the same message as when the publication is already gone on
the first call, with the concurrent drop in the detail: ERROR:
publication “pub” does not exist DETAIL: The publication was dropped
concurrently. I also moved the name lookup into a separate helper,
get_pubname_from_list(), as you suggested. One small difference:
instead of Assert(false) followed by return NULL, the helper ends with
an elog(ERROR), so that a non-assert build never passes NULL to the %s
in the error message if that case were ever reached.

regards,
Ajin Cherian
Fujitsu Australia

Attachments:

v3-0001-Fix-cache-lookup-failure-in-pg_get_publication_ta.patch (application/octet-stream, 8.9K) download From e658489aebded33fd18249aa303ef0cbbbc8d422 Mon Sep 17 00:00:00 2001 From: Ajin Cherian <itsajin@gmail.com> Date: Fri, 13 Nov 2026 14:37:19 +1100 Subject: [PATCH v3] Fix cache lookup failure in pg_get_publication_tables() after concurrent DROP PUBLICATION pg_get_publication_tables() collects (pubid, relid) pairs on its first call and then looks each publication up again per row with GetPublication(). No lock is held on the publication, so if it is dropped concurrently -- for instance while the function waits for the lock in try_table_open(), which also processes invalidations -- the lookup fails with an internal "cache lookup failed for publication" error. Reported-by: Noah <imchifan8@gmail.com> Author: Ajin Cherian <itsajin@gmail.com> Reviewed-by: shveta malik <shveta.malik@gmail.com> Discussion: https://postgr.es/m/19864-5260a1fbeec7728c@postgresql.org --- src/backend/catalog/pg_publication.c | 72 +++++++++++++++++-- .../expected/pub-concurrent-drop.out | 24 +++++++ .../isolation/specs/pub-concurrent-drop.spec | 21 +++++- 3 files changed, 111 insertions(+), 6 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index f11ea8839ca..05cca134065 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1305,9 +1305,12 @@ GetAllSchemaPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt) * Get publication using oid * * The Publication struct and its data are palloc'ed here. + * + * If missing_ok is true, return NULL when the publication does not exist + * (e.g., it was dropped concurrently); otherwise raise an error. */ -Publication * -GetPublication(Oid pubid) +static Publication * +get_publication_internal(Oid pubid, bool missing_ok) { HeapTuple tup; Publication *pub; @@ -1315,7 +1318,11 @@ GetPublication(Oid pubid) tup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(pubid)); if (!HeapTupleIsValid(tup)) + { + if (missing_ok) + return NULL; elog(ERROR, "cache lookup failed for publication %u", pubid); + } pubform = (Form_pg_publication) GETSTRUCT(tup); @@ -1336,6 +1343,12 @@ GetPublication(Oid pubid) return pub; } +Publication * +GetPublication(Oid pubid) +{ + return get_publication_internal(pubid, false); +} + /* * Get Publication using name. */ @@ -1444,6 +1457,27 @@ is_table_publishable_in_publication(Oid relid, Publication *pub) ObjectIdGetDatum(pub->oid))); } +/* + * A helper function for pg_get_publication_tables() to get the name of the + * publication with the given OID from the list of publications collected on + * the first call. It is used to report a publication that has been dropped + * concurrently, whose name can no longer be looked up in the catalog. + */ +static char * +get_pubname_from_list(List *pubs, Oid pubid) +{ + foreach_ptr(Publication, pub, pubs) + { + if (pub->oid == pubid) + return pub->name; + } + + /* The publication must have been collected on the first call. */ + elog(ERROR, "could not find publication %u in the list of publications", + pubid); + return NULL; /* keep compiler quiet */ +} + /* * Helper function to get information of the tables in the given * publication(s). @@ -1471,6 +1505,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, typedef struct { List *table_infos; /* list of published_rel */ + List *pubs; /* list of Publication, for error messages */ int curr_idx; /* current index into table_infos */ } publication_tables_state; @@ -1483,6 +1518,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, TupleDesc tupdesc; MemoryContext oldcontext; List *table_infos = NIL; + List *pubs = NIL; Datum *elems; int nelems, i; @@ -1521,6 +1557,8 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, if (pub_elem == NULL) continue; + pubs = lappend(pubs, pub_elem); + if (filter_by_relid) { /* Check if the given table is published for the publication */ @@ -1609,6 +1647,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, /* Store the state to be used across SRF calls. */ ptstate = palloc_object(publication_tables_state); ptstate->table_infos = table_infos; + ptstate->pubs = pubs; ptstate->curr_idx = 0; funcctx->user_fctx = ptstate; @@ -1647,10 +1686,35 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, continue; /* - * Form tuple with appropriate data. + * The publication OIDs were also collected earlier and we don't hold + * a lock on the publication, so it may have been dropped concurrently + * (for instance, while we waited for the lock on the table above, + * which also processes pending invalidations). + * + * Unlike a dropped table, we can't just skip it: rows for this + * publication may already have been returned, so the result would be + * a misleading partial list. Report it as an error instead, unless + * the caller asked for missing publications to be ignored, which is + * consistent with how they are treated on the first call. */ + pub = get_publication_internal(table_info->pubid, true); + if (pub == NULL) + { + if (!pub_missing_ok) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication \"%s\" does not exist", + get_pubname_from_list(ptstate->pubs, + table_info->pubid)), + errdetail("The publication was dropped concurrently.")); + + table_close(rel, AccessShareLock); + continue; + } - pub = GetPublication(table_info->pubid); + /* + * Form tuple with appropriate data. + */ values[0] = ObjectIdGetDatum(pub->oid); values[1] = ObjectIdGetDatum(relid); diff --git a/src/test/isolation/expected/pub-concurrent-drop.out b/src/test/isolation/expected/pub-concurrent-drop.out index 8360af0ec9c..e31d06f917d 100644 --- a/src/test/isolation/expected/pub-concurrent-drop.out +++ b/src/test/isolation/expected/pub-concurrent-drop.out @@ -14,3 +14,27 @@ tablename pubdrop.keepme (1 row) + +starting permutation: lock list_pub_tables drop_pub_and_commit +step lock: BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; +step list_pub_tables: + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables('pub_schema') + ORDER BY tablename; + <waiting ...> +step drop_pub_and_commit: DROP PUBLICATION pub_schema; COMMIT; +step list_pub_tables: <... completed> +ERROR: publication "pub_schema" does not exist + +starting permutation: lock list_pub_table drop_pub_and_commit +step lock: BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; +step list_pub_table: + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables(ARRAY['pub_schema'], 'pubdrop.dropme'::regclass); + <waiting ...> +step drop_pub_and_commit: DROP PUBLICATION pub_schema; COMMIT; +step list_pub_table: <... completed> +tablename +--------- +(0 rows) + diff --git a/src/test/isolation/specs/pub-concurrent-drop.spec b/src/test/isolation/specs/pub-concurrent-drop.spec index 4f7d701d60c..3b41ced1ba5 100644 --- a/src/test/isolation/specs/pub-concurrent-drop.spec +++ b/src/test/isolation/specs/pub-concurrent-drop.spec @@ -1,4 +1,4 @@ -# Tests for concurrently dropping a relation while a publication's tables are +# Tests for concurrently dropping a relation or the publication itself while a publication's tables are # being listed. setup @@ -12,12 +12,13 @@ setup teardown { DROP SCHEMA pubdrop CASCADE; - DROP PUBLICATION pub_schema; + DROP PUBLICATION IF EXISTS pub_schema; } session s1 step lock { BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; } step drop_and_commit { DROP TABLE pubdrop.dropme; COMMIT; } +step drop_pub_and_commit { DROP PUBLICATION pub_schema; COMMIT; } session s2 step list_pub_tables @@ -26,6 +27,11 @@ step list_pub_tables FROM pg_get_publication_tables('pub_schema') ORDER BY tablename; } +step list_pub_table +{ + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables(ARRAY['pub_schema'], 'pubdrop.dropme'::regclass); +} # Hold an ACCESS EXCLUSIVE lock on the table in one session, so that the query # listing a publication's tables in another session blocks when it tries to @@ -34,3 +40,14 @@ step list_pub_tables # skips the now-dropped table instead of erroring with "could not open relation # with OID". permutation lock list_pub_tables drop_and_commit + +# Same as above, but drop the publication instead of the table. When the +# query resumes, the publication it collected the table OIDs from is gone. +# Rather than returning a partial list, it reports that the publication was +# concurrently dropped instead of failing with "cache lookup failed for +# publication". +permutation lock list_pub_tables drop_pub_and_commit + +# The variant that takes a table OID ignores missing publications, so it +# skips the row for the dropped publication instead. +permutation lock list_pub_table drop_pub_and_commit -- 2.47.3
^ permalink raw reply [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 10:37       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 12:16         ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 03:51           ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
@ 2026-11-13 06:28             ` shveta malik <shveta.malik@gmail.com>
  2026-11-13 08:42               ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: shveta malik @ 2026-11-13 06:28 UTC (permalink / raw)
  To: Ajin Cherian <itsajin@gmail.com>; +Cc: imchifan8@gmail.com, pgsql-bugs@lists.postgresql.org, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

I found one more issue, which looks like an existing bug. The same
window exists on master with GetPublication(). If the publication is
dropped after get_publication_internal() has succeeded for the last
table of the publication, the remaining lookups for that row
(PUBLICATIONNAMESPACEMAP and PUBLICATIONRELMAP) find no entry, so the
row is built as if there were no column list and no row filter (all
columns and a NULL qual). For earlier tables the next row hits the new
error, but for the last table there is no next row, so the query
succeeds with a wrong result.

Try with this setup:
create table tab1(a int, b int, c int);
create table tab2(a int, b int, c int);
create table tab3(a int, b int, c int);
create publication pub for table tab1(a) where (a > 1), tab2(b) where
(b > 2), tab3(c) where (c > 3);

Run this:
SELECT * FROM pg_get_publication_tables('pub')

When above query is reading last table tab3, drop the publication
immediately in another session post get_publication_internal(). You
will see that the output for 'attrs' and 'qual' is wrong for tab3. It
gives all columns (1 2 3) and a NULL qual instead of col 3 and (c > 3)
qual.

While running this, the cache should be cold (the first query in the
session). With a cold cache, these lookups open the catalogs, which
processes the drop's invalidation. In an already-warm session/cache,
the lookups hit the cache and the result is correct.

I think this bug will impact tablesync in a way that it will end up
syncing tab3 with all columns and all rows and will mark it ready.

Thinking about a potential solution. If the fix is simple enough, we
can address it in sae ethread, else we can raise a new bug,

thanks
Shveta






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 10:37       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 12:16         ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 03:51           ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-13 06:28             ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
@ 2026-11-13 08:42               ` shveta malik <shveta.malik@gmail.com>
  2026-11-13 09:31                 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Zsolt Parragi <zsolt.parragi@percona.com>
  0 siblings, 1 reply; 14+ messages in thread

From: shveta malik @ 2026-11-13 08:42 UTC (permalink / raw)
  To: Ajin Cherian <itsajin@gmail.com>; +Cc: imchifan8@gmail.com, pgsql-bugs@lists.postgresql.org, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

On Fri, Nov 13, 2026 at 11:58 AM shveta malik <shveta.malik@gmail.com> wrote:
>
> I found one more issue, which looks like an existing bug. The same
> window exists on master with GetPublication(). If the publication is
> dropped after get_publication_internal() has succeeded for the last
> table of the publication, the remaining lookups for that row
> (PUBLICATIONNAMESPACEMAP and PUBLICATIONRELMAP) find no entry, so the
> row is built as if there were no column list and no row filter (all
> columns and a NULL qual). For earlier tables the next row hits the new
> error, but for the last table there is no next row, so the query
> succeeds with a wrong result.
>
> Try with this setup:
> create table tab1(a int, b int, c int);
> create table tab2(a int, b int, c int);
> create table tab3(a int, b int, c int);
> create publication pub for table tab1(a) where (a > 1), tab2(b) where
> (b > 2), tab3(c) where (c > 3);
>
> Run this:
> SELECT * FROM pg_get_publication_tables('pub')
>
> When above query is reading last table tab3, drop the publication
> immediately in another session post get_publication_internal(). You
> will see that the output for 'attrs' and 'qual' is wrong for tab3. It
> gives all columns (1 2 3) and a NULL qual instead of col 3 and (c > 3)
> qual.
>
> While running this, the cache should be cold (the first query in the
> session). With a cold cache, these lookups open the catalogs, which
> processes the drop's invalidation. In an already-warm session/cache,
> the lookups hit the cache and the result is correct.
>
> I think this bug will impact tablesync in a way that it will end up
> syncing tab3 with all columns and all rows and will mark it ready.
>
> Thinking about a potential solution. If the fix is simple enough, we
> can address it in sae ethread, else we can raise a new bug,

I think the fix can be simple for this. When looking up
pg_publication_rel for a table after locking it (for non-ALL TABLES
and non-schema publications), we normally expect to find an entry. If
no entry is found, either the publication was dropped concurrently or
the entry is legitimately absent. For example, when partitions are
published via their parent with publish_via_partition_root = false,
pg_pub_rel contains only the parent, while pg_pub_tables contains the
partitions.

So, after SearchSysCacheCopy2(PUBLICATIONRELMAP, ...), if pubtuple is
invalid, check whether the publication still exists. If it doesn't,
raise the same error (or skip based on pub_missing_ok). If pubtuple is
valid, no further check is needed, since it represents the publication
definition from before the drop.

I have attached the top-up patch for this. It applies atop v3. Let me
know what you think.

thanks
Shveta

Attachments:

0001-last-table-fetch-wrong-result-fix.patch (application/octet-stream, 1.9K) download From 6a0457506c0607a935ae7c94caa8c45f46635f09 Mon Sep 17 00:00:00 2001 From: Shveta Malik <shveta.malik@gmail.com> Date: Tue, 10 Nov 2026 01:13:46 +0530 Subject: [PATCH] last table-fetch wrong-result fix --- src/backend/catalog/pg_publication.c | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 05cca134065..6825e7528c5 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1728,10 +1728,37 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, !SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP, ObjectIdGetDatum(schemaid), ObjectIdGetDatum(pub->oid))) + { pubtuple = SearchSysCacheCopy2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid), ObjectIdGetDatum(pub->oid)); + /* + * No pg_publication_rel entry is normal for partitions published + * via their parent. However, it can also mean the publication was + * dropped concurrently, as the lookups above process pending + * invalidations when opening the catalogs with a cold cache. + * Distinguish these cases to avoid returning incorrect column + * lists and row filters. If an entry was found, it represents the + * publication definition before the drop. + */ + if (!HeapTupleIsValid(pubtuple) && + !SearchSysCacheExists1(PUBLICATIONOID, + ObjectIdGetDatum(pub->oid))) + { + if (!pub_missing_ok) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication \"%s\" does not exist", + get_pubname_from_list(ptstate->pubs, + table_info->pubid)), + errdetail("The publication was dropped concurrently.")); + + table_close(rel, AccessShareLock); + continue; + } + } + if (HeapTupleIsValid(pubtuple)) { /* Lookup the column list attribute. */ -- 2.34.1
^ permalink raw reply [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 10:37       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 12:16         ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 03:51           ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-13 06:28             ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 08:42               ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
@ 2026-11-13 09:31                 ` Zsolt Parragi <zsolt.parragi@percona.com>
  2026-11-13 10:10                   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Zsolt Parragi @ 2026-11-13 09:31 UTC (permalink / raw)
  To: shveta malik <shveta.malik@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Ajin Cherian <itsajin@gmail.com>; imchifan8@gmail.com, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

Hello

There's still another remaining issue with the above patches, pointed
out by claude: a missing pg_publication_rel entry while the
publication still exists isn't always the partition-via-parent case,
the table could also have been removed from the publication
concurrently.

CREATE TABLE t (a int, b int, c int);
CREATE PUBLICATION p FOR TABLE t (a, b) WHERE (a > 5);

s1: BEGIN; LOCK t IN ACCESS EXCLUSIVE MODE;
s2: SELECT relid::regclass, attrs, pg_get_expr(qual, relid) FROM
pg_get_publication_tables('p');  -- blocks
s1: ALTER PUBLICATION p DROP TABLE t; COMMIT;
-- s2: t | 1 2 3 | (null)

With multiple publications, a full testcase:

setup
{
	CREATE TABLE pubfilt (a int, b int, c int);
	CREATE PUBLICATION p1 FOR TABLE pubfilt WHERE (a > 5);
	CREATE PUBLICATION p2 FOR TABLE pubfilt WHERE (b > 2);
}

teardown
{
	DROP PUBLICATION p1, p2;
	DROP TABLE pubfilt;
}

session s1
step lock	{ BEGIN; LOCK pubfilt IN ACCESS EXCLUSIVE MODE; }
step unlock	{ COMMIT; }
step alter_and_commit	{ ALTER PUBLICATION p2 DROP TABLE pubfilt; COMMIT; }

session s2
step tablesync_qual
{
	SELECT DISTINCT pg_get_expr(gpt.qual, gpt.relid) AS rowfilter
	FROM pg_get_publication_tables(ARRAY['p1','p2'], 'pubfilt'::regclass) gpt
	ORDER BY 1;
}

permutation lock tablesync_qual unlock
permutation lock tablesync_qual alter_and_commit



Maybe the miss branch could also check
is_table_publishable_in_publication(relid, pub) after the
PUBLICATIONOID check, and skip the row if the table isn't published
anymore? Something like:

+			if (!HeapTupleIsValid(pubtuple) &&
+				!is_table_publishable_in_publication(relid, pub))
+			{
+				table_close(rel, AccessShareLock);
+				continue;
+			}






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 10:37       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 12:16         ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 03:51           ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-13 06:28             ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 08:42               ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 09:31                 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Zsolt Parragi <zsolt.parragi@percona.com>
@ 2026-11-13 10:10                   ` shveta malik <shveta.malik@gmail.com>
  2026-11-14 10:48                     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Dilip Kumar <dilipbalaut@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: shveta malik @ 2026-11-13 10:10 UTC (permalink / raw)
  To: Zsolt Parragi <zsolt.parragi@percona.com>; +Cc: pgsql-bugs@lists.postgresql.org, Ajin Cherian <itsajin@gmail.com>; imchifan8@gmail.com, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

On Fri, Nov 13, 2026 at 3:01 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
>
> Hello
>
> There's still another remaining issue with the above patches, pointed
> out by claude: a missing pg_publication_rel entry while the
> publication still exists isn't always the partition-via-parent case,
> the table could also have been removed from the publication
> concurrently.

Okay, thanks for reporting. I agree with the problem. It seems like
another variation of commit 63e7a0d2c3 (due to DROP TABLE part), but
it's also related to current thread because the publication is
concurrently 'altered' (similar to original concurrent 'drop
publication'). Therefore, I'm okay with incorporating the fix into the
current patch. I have attached the fix as 003.

v4-0001 is same as Ajin's v3-0001.
v4-0002 fixes the wrong column list and filter for the last fetched
table in a concurrently dropped publication.
v4-0003 fixes the wrong column list and row filter for concurrently
altered publication that drops a table.

thanks
Shveta

Attachments: download all (3 files, .tar.gz)

v4-0001-Fix-cache-lookup-failure-in-pg_get_publication_ta.patch (application/octet-stream, 8.9K) download From 59379fc2f13a2afccd80efa924e46754904b155c Mon Sep 17 00:00:00 2001 From: Ajin Cherian <itsajin@gmail.com> Date: Fri, 13 Nov 2026 14:37:19 +1100 Subject: [PATCH v4 1/3] Fix cache lookup failure in pg_get_publication_tables() after concurrent DROP PUBLICATION pg_get_publication_tables() collects (pubid, relid) pairs on its first call and then looks each publication up again per row with GetPublication(). No lock is held on the publication, so if it is dropped concurrently -- for instance while the function waits for the lock in try_table_open(), which also processes invalidations -- the lookup fails with an internal "cache lookup failed for publication" error. Reported-by: Noah <imchifan8@gmail.com> Author: Ajin Cherian <itsajin@gmail.com> Reviewed-by: shveta malik <shveta.malik@gmail.com> Discussion: https://postgr.es/m/19864-5260a1fbeec7728c@postgresql.org --- src/backend/catalog/pg_publication.c | 72 +++++++++++++++++-- .../expected/pub-concurrent-drop.out | 24 +++++++ .../isolation/specs/pub-concurrent-drop.spec | 21 +++++- 3 files changed, 111 insertions(+), 6 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index f11ea8839ca..05cca134065 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1305,9 +1305,12 @@ GetAllSchemaPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt) * Get publication using oid * * The Publication struct and its data are palloc'ed here. + * + * If missing_ok is true, return NULL when the publication does not exist + * (e.g., it was dropped concurrently); otherwise raise an error. */ -Publication * -GetPublication(Oid pubid) +static Publication * +get_publication_internal(Oid pubid, bool missing_ok) { HeapTuple tup; Publication *pub; @@ -1315,7 +1318,11 @@ GetPublication(Oid pubid) tup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(pubid)); if (!HeapTupleIsValid(tup)) + { + if (missing_ok) + return NULL; elog(ERROR, "cache lookup failed for publication %u", pubid); + } pubform = (Form_pg_publication) GETSTRUCT(tup); @@ -1336,6 +1343,12 @@ GetPublication(Oid pubid) return pub; } +Publication * +GetPublication(Oid pubid) +{ + return get_publication_internal(pubid, false); +} + /* * Get Publication using name. */ @@ -1444,6 +1457,27 @@ is_table_publishable_in_publication(Oid relid, Publication *pub) ObjectIdGetDatum(pub->oid))); } +/* + * A helper function for pg_get_publication_tables() to get the name of the + * publication with the given OID from the list of publications collected on + * the first call. It is used to report a publication that has been dropped + * concurrently, whose name can no longer be looked up in the catalog. + */ +static char * +get_pubname_from_list(List *pubs, Oid pubid) +{ + foreach_ptr(Publication, pub, pubs) + { + if (pub->oid == pubid) + return pub->name; + } + + /* The publication must have been collected on the first call. */ + elog(ERROR, "could not find publication %u in the list of publications", + pubid); + return NULL; /* keep compiler quiet */ +} + /* * Helper function to get information of the tables in the given * publication(s). @@ -1471,6 +1505,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, typedef struct { List *table_infos; /* list of published_rel */ + List *pubs; /* list of Publication, for error messages */ int curr_idx; /* current index into table_infos */ } publication_tables_state; @@ -1483,6 +1518,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, TupleDesc tupdesc; MemoryContext oldcontext; List *table_infos = NIL; + List *pubs = NIL; Datum *elems; int nelems, i; @@ -1521,6 +1557,8 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, if (pub_elem == NULL) continue; + pubs = lappend(pubs, pub_elem); + if (filter_by_relid) { /* Check if the given table is published for the publication */ @@ -1609,6 +1647,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, /* Store the state to be used across SRF calls. */ ptstate = palloc_object(publication_tables_state); ptstate->table_infos = table_infos; + ptstate->pubs = pubs; ptstate->curr_idx = 0; funcctx->user_fctx = ptstate; @@ -1647,10 +1686,35 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, continue; /* - * Form tuple with appropriate data. + * The publication OIDs were also collected earlier and we don't hold + * a lock on the publication, so it may have been dropped concurrently + * (for instance, while we waited for the lock on the table above, + * which also processes pending invalidations). + * + * Unlike a dropped table, we can't just skip it: rows for this + * publication may already have been returned, so the result would be + * a misleading partial list. Report it as an error instead, unless + * the caller asked for missing publications to be ignored, which is + * consistent with how they are treated on the first call. */ + pub = get_publication_internal(table_info->pubid, true); + if (pub == NULL) + { + if (!pub_missing_ok) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication \"%s\" does not exist", + get_pubname_from_list(ptstate->pubs, + table_info->pubid)), + errdetail("The publication was dropped concurrently.")); + + table_close(rel, AccessShareLock); + continue; + } - pub = GetPublication(table_info->pubid); + /* + * Form tuple with appropriate data. + */ values[0] = ObjectIdGetDatum(pub->oid); values[1] = ObjectIdGetDatum(relid); diff --git a/src/test/isolation/expected/pub-concurrent-drop.out b/src/test/isolation/expected/pub-concurrent-drop.out index 8360af0ec9c..e31d06f917d 100644 --- a/src/test/isolation/expected/pub-concurrent-drop.out +++ b/src/test/isolation/expected/pub-concurrent-drop.out @@ -14,3 +14,27 @@ tablename pubdrop.keepme (1 row) + +starting permutation: lock list_pub_tables drop_pub_and_commit +step lock: BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; +step list_pub_tables: + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables('pub_schema') + ORDER BY tablename; + <waiting ...> +step drop_pub_and_commit: DROP PUBLICATION pub_schema; COMMIT; +step list_pub_tables: <... completed> +ERROR: publication "pub_schema" does not exist + +starting permutation: lock list_pub_table drop_pub_and_commit +step lock: BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; +step list_pub_table: + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables(ARRAY['pub_schema'], 'pubdrop.dropme'::regclass); + <waiting ...> +step drop_pub_and_commit: DROP PUBLICATION pub_schema; COMMIT; +step list_pub_table: <... completed> +tablename +--------- +(0 rows) + diff --git a/src/test/isolation/specs/pub-concurrent-drop.spec b/src/test/isolation/specs/pub-concurrent-drop.spec index 4f7d701d60c..3b41ced1ba5 100644 --- a/src/test/isolation/specs/pub-concurrent-drop.spec +++ b/src/test/isolation/specs/pub-concurrent-drop.spec @@ -1,4 +1,4 @@ -# Tests for concurrently dropping a relation while a publication's tables are +# Tests for concurrently dropping a relation or the publication itself while a publication's tables are # being listed. setup @@ -12,12 +12,13 @@ setup teardown { DROP SCHEMA pubdrop CASCADE; - DROP PUBLICATION pub_schema; + DROP PUBLICATION IF EXISTS pub_schema; } session s1 step lock { BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; } step drop_and_commit { DROP TABLE pubdrop.dropme; COMMIT; } +step drop_pub_and_commit { DROP PUBLICATION pub_schema; COMMIT; } session s2 step list_pub_tables @@ -26,6 +27,11 @@ step list_pub_tables FROM pg_get_publication_tables('pub_schema') ORDER BY tablename; } +step list_pub_table +{ + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables(ARRAY['pub_schema'], 'pubdrop.dropme'::regclass); +} # Hold an ACCESS EXCLUSIVE lock on the table in one session, so that the query # listing a publication's tables in another session blocks when it tries to @@ -34,3 +40,14 @@ step list_pub_tables # skips the now-dropped table instead of erroring with "could not open relation # with OID". permutation lock list_pub_tables drop_and_commit + +# Same as above, but drop the publication instead of the table. When the +# query resumes, the publication it collected the table OIDs from is gone. +# Rather than returning a partial list, it reports that the publication was +# concurrently dropped instead of failing with "cache lookup failed for +# publication". +permutation lock list_pub_tables drop_pub_and_commit + +# The variant that takes a table OID ignores missing publications, so it +# skips the row for the dropped publication instead. +permutation lock list_pub_table drop_pub_and_commit -- 2.34.1
v4-0002-Do-not-return-wrong-column-list-and-row-filter-fo.patch (application/octet-stream, 2.0K) download From 6ad7cd98eca017054ac98881cbf4893752723f23 Mon Sep 17 00:00:00 2001 From: Shveta Malik <shveta.malik@gmail.com> Date: Tue, 10 Nov 2026 01:13:46 +0530 Subject: [PATCH v4 2/3] Do not return wrong column list and row filter for a dropped publication --- src/backend/catalog/pg_publication.c | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 05cca134065..6825e7528c5 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1728,10 +1728,37 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, !SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP, ObjectIdGetDatum(schemaid), ObjectIdGetDatum(pub->oid))) + { pubtuple = SearchSysCacheCopy2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid), ObjectIdGetDatum(pub->oid)); + /* + * No pg_publication_rel entry is normal for partitions published + * via their parent. However, it can also mean the publication was + * dropped concurrently, as the lookups above process pending + * invalidations when opening the catalogs with a cold cache. + * Distinguish these cases to avoid returning incorrect column + * lists and row filters. If an entry was found, it represents the + * publication definition before the drop. + */ + if (!HeapTupleIsValid(pubtuple) && + !SearchSysCacheExists1(PUBLICATIONOID, + ObjectIdGetDatum(pub->oid))) + { + if (!pub_missing_ok) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication \"%s\" does not exist", + get_pubname_from_list(ptstate->pubs, + table_info->pubid)), + errdetail("The publication was dropped concurrently.")); + + table_close(rel, AccessShareLock); + continue; + } + } + if (HeapTupleIsValid(pubtuple)) { /* Lookup the column list attribute. */ -- 2.34.1
v4-0003-Skip-rows-for-tables-concurrently-removed-from-th.patch (application/octet-stream, 2.4K) download From e4c0844414537efc826ced6e1a887ec964ce4ddc Mon Sep 17 00:00:00 2001 From: Shveta Malik <shveta.malik@gmail.com> Date: Tue, 10 Nov 2026 02:22:43 +0530 Subject: [PATCH v4 3/3] Skip rows for tables concurrently removed from the publication --- src/backend/catalog/pg_publication.c | 27 +++++++++++++++++---------- 1 file changed, 17 insertions(+), 10 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 6825e7528c5..a2acc7c7f04 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1734,19 +1734,26 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, ObjectIdGetDatum(pub->oid)); /* - * No pg_publication_rel entry is normal for partitions published - * via their parent. However, it can also mean the publication was - * dropped concurrently, as the lookups above process pending - * invalidations when opening the catalogs with a cold cache. - * Distinguish these cases to avoid returning incorrect column - * lists and row filters. If an entry was found, it represents the - * publication definition before the drop. + * No pg_publication_rel entry is expected here only for a + * partition whose ancestor is in the publication. Otherwise, the + * table is no longer published by this publication: either the + * table was removed from it or the publication was dropped + * concurrently (for instance, while we waited for the lock on the + * table above, or, with a cold cache, during the lookups above, + * which process pending invalidations). Building the row anyway + * would return incorrect column lists and row filters. + * + * If the table was removed, skip the row, as for a dropped table. + * If the publication was dropped, report it as above. Check for + * the latter only after is_table_publishable_in_publication(), as + * its lookups may also process the drop. */ if (!HeapTupleIsValid(pubtuple) && - !SearchSysCacheExists1(PUBLICATIONOID, - ObjectIdGetDatum(pub->oid))) + !is_table_publishable_in_publication(relid, pub)) { - if (!pub_missing_ok) + if (!pub_missing_ok && + !SearchSysCacheExists1(PUBLICATIONOID, + ObjectIdGetDatum(pub->oid))) ereport(ERROR, errcode(ERRCODE_UNDEFINED_OBJECT), errmsg("publication \"%s\" does not exist", -- 2.34.1
^ permalink raw reply [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 10:37       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 12:16         ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 03:51           ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-13 06:28             ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 08:42               ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 09:31                 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Zsolt Parragi <zsolt.parragi@percona.com>
  2026-11-13 10:10                   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
@ 2026-11-14 10:48                     ` Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-16 03:54                       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Dilip Kumar @ 2026-11-14 10:48 UTC (permalink / raw)
  To: shveta malik <shveta.malik@gmail.com>; +Cc: Zsolt Parragi <zsolt.parragi@percona.com>; pgsql-bugs@lists.postgresql.org, Ajin Cherian <itsajin@gmail.com>; imchifan8@gmail.com, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

On Fri, Nov 13, 2026 at 3:40 PM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Fri, Nov 13, 2026 at 3:01 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
> >
> > Hello
> >
> > There's still another remaining issue with the above patches, pointed
> > out by claude: a missing pg_publication_rel entry while the
> > publication still exists isn't always the partition-via-parent case,
> > the table could also have been removed from the publication
> > concurrently.
>
> Okay, thanks for reporting. I agree with the problem. It seems like
> another variation of commit 63e7a0d2c3 (due to DROP TABLE part), but
> it's also related to current thread because the publication is
> concurrently 'altered' (similar to original concurrent 'drop
> publication'). Therefore, I'm okay with incorporating the fix into the
> current patch. I have attached the fix as 003.
>
> v4-0001 is same as Ajin's v3-0001.
> v4-0002 fixes the wrong column list and filter for the last fetched
> table in a concurrently dropped publication.
> v4-0003 fixes the wrong column list and row filter for concurrently
> altered publication that drops a table.

Thanks for the patch, Shveta. While testing, I see all the reported
cases are fixed but found another issue, check below test

-- Setup
CREATE TABLE tbl_parent (a int) PARTITION BY RANGE (a);
CREATE TABLE tbl_part1 PARTITION OF tbl_parent FOR VALUES FROM (1) TO (10);
CREATE TABLE tbl_part2 PARTITION OF tbl_parent FOR VALUES FROM (10) TO (20);
CREATE PUBLICATION pub FOR TABLE tbl_parent WITH
(publish_via_partition_root = false);

Session 1:
BEGIN;
LOCK TABLE tbl_part2 IN ACCESS EXCLUSIVE MODE;

Session 2:
SELECT relid::regclass FROM pg_get_publication_tables('pub');
-- Emits tbl_part1, then blocks waiting on tbl_part2

Session 1:
ALTER PUBLICATION pub SET (publish_via_partition_root = true);
COMMIT;

Session 2:
   relid
-----------
 tbl_part1
(1 row)

So while it was waiting on the lock on tbl_part2, we concurrently
updated publish_via_partition_root to true, and due to that the
inidividual partitions are not listed and it skipped tbl_part2,
whereas tbl_part1 is already listed, that was never valid either
before (tbl_part1, tbl_part2) or after (tbl_parent) the ALTER
PUBLICATION, what do you think?

-- 
Regards,
Dilip Kumar
Google






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 10:37       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 12:16         ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 03:51           ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-13 06:28             ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 08:42               ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 09:31                 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Zsolt Parragi <zsolt.parragi@percona.com>
  2026-11-13 10:10                   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-14 10:48                     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-11-16 03:54                       ` shveta malik <shveta.malik@gmail.com>
  2026-11-16 04:55                         ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: shveta malik @ 2026-11-16 03:54 UTC (permalink / raw)
  To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: Zsolt Parragi <zsolt.parragi@percona.com>; pgsql-bugs@lists.postgresql.org, Ajin Cherian <itsajin@gmail.com>; imchifan8@gmail.com, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

On Sat, Nov 14, 2026 at 4:18 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Fri, Nov 13, 2026 at 3:40 PM shveta malik <shveta.malik@gmail.com> wrote:
> >
> > On Fri, Nov 13, 2026 at 3:01 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
> > >
> > > Hello
> > >
> > > There's still another remaining issue with the above patches, pointed
> > > out by claude: a missing pg_publication_rel entry while the
> > > publication still exists isn't always the partition-via-parent case,
> > > the table could also have been removed from the publication
> > > concurrently.
> >
> > Okay, thanks for reporting. I agree with the problem. It seems like
> > another variation of commit 63e7a0d2c3 (due to DROP TABLE part), but
> > it's also related to current thread because the publication is
> > concurrently 'altered' (similar to original concurrent 'drop
> > publication'). Therefore, I'm okay with incorporating the fix into the
> > current patch. I have attached the fix as 003.
> >
> > v4-0001 is same as Ajin's v3-0001.
> > v4-0002 fixes the wrong column list and filter for the last fetched
> > table in a concurrently dropped publication.
> > v4-0003 fixes the wrong column list and row filter for concurrently
> > altered publication that drops a table.
>
> Thanks for the patch, Shveta. While testing, I see all the reported
> cases are fixed but found another issue, check below test
>
> -- Setup
> CREATE TABLE tbl_parent (a int) PARTITION BY RANGE (a);
> CREATE TABLE tbl_part1 PARTITION OF tbl_parent FOR VALUES FROM (1) TO (10);
> CREATE TABLE tbl_part2 PARTITION OF tbl_parent FOR VALUES FROM (10) TO (20);
> CREATE PUBLICATION pub FOR TABLE tbl_parent WITH
> (publish_via_partition_root = false);
>
> Session 1:
> BEGIN;
> LOCK TABLE tbl_part2 IN ACCESS EXCLUSIVE MODE;
>
> Session 2:
> SELECT relid::regclass FROM pg_get_publication_tables('pub');
> -- Emits tbl_part1, then blocks waiting on tbl_part2
>
> Session 1:
> ALTER PUBLICATION pub SET (publish_via_partition_root = true);
> COMMIT;
>
> Session 2:
>    relid
> -----------
>  tbl_part1
> (1 row)
>
> So while it was waiting on the lock on tbl_part2, we concurrently
> updated publish_via_partition_root to true, and due to that the
> inidividual partitions are not listed and it skipped tbl_part2,
> whereas tbl_part1 is already listed, that was never valid either
> before (tbl_part1, tbl_part2) or after (tbl_parent) the ALTER
> PUBLICATION, what do you think?

Agreed; the current result is wrong. It seems a regerssion of 0003;
is_table_publishable_in_publication() was evaluated with the
publication’s current 'publish_via_partition_root', while the list of
relations was built on the first call with the old setting. So
tbl_part2 looked “not published” even though only its representation
changed. Since tbl_part1 has already been returned, we can’t produce
the after-state (tbl_parent) without materializing the result, so I
think the right answer is the before-state (tbl_part1, tbl_part2),
which is also what HEAD returns. Thoughts?

The way to achieve this is to let
is_table_publishable_in_publication() decide based on the publication
collected on the first call (saved in publication_tables_state). Its
membership lookups (pg_publication_rel, pg_publication_namespace,
ancestors etc) still read the current catalog, so a table that was
really removed from the publication is still skipped, as 0003 intends;
only the change of pub-setting is no longer confused as a removal of
table.

Do you think 0003, along with the fix suggested above to address its
regerssion, should be handled as a separate bug? That would keep the
drop-pub and alter-pub fixes, and their analysis, separate.

thanks
Shveta






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
  2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
  2026-11-12 04:00 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 05:20   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 10:12     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-12 10:37       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-12 12:16         ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 03:51           ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Ajin Cherian <itsajin@gmail.com>
  2026-11-13 06:28             ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 08:42               ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-13 09:31                 ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Zsolt Parragi <zsolt.parragi@percona.com>
  2026-11-13 10:10                   ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
  2026-11-14 10:48                     ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-16 03:54                       ` Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION shveta malik <shveta.malik@gmail.com>
@ 2026-11-16 04:55                         ` shveta malik <shveta.malik@gmail.com>
  0 siblings, 0 replies; 14+ messages in thread

From: shveta malik @ 2026-11-16 04:55 UTC (permalink / raw)
  To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: Zsolt Parragi <zsolt.parragi@percona.com>; pgsql-bugs@lists.postgresql.org, Ajin Cherian <itsajin@gmail.com>; imchifan8@gmail.com, Masahiko Sawada <sawada.mshk@gmail.com>; shveta malik <shvetamalik@gmail.com>

On Mon, Nov 16, 2026 at 9:24 AM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Sat, Nov 14, 2026 at 4:18 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > On Fri, Nov 13, 2026 at 3:40 PM shveta malik <shveta.malik@gmail.com> wrote:
> > >
> > > On Fri, Nov 13, 2026 at 3:01 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
> > > >
> > > > Hello
> > > >
> > > > There's still another remaining issue with the above patches, pointed
> > > > out by claude: a missing pg_publication_rel entry while the
> > > > publication still exists isn't always the partition-via-parent case,
> > > > the table could also have been removed from the publication
> > > > concurrently.
> > >
> > > Okay, thanks for reporting. I agree with the problem. It seems like
> > > another variation of commit 63e7a0d2c3 (due to DROP TABLE part), but
> > > it's also related to current thread because the publication is
> > > concurrently 'altered' (similar to original concurrent 'drop
> > > publication'). Therefore, I'm okay with incorporating the fix into the
> > > current patch. I have attached the fix as 003.
> > >
> > > v4-0001 is same as Ajin's v3-0001.
> > > v4-0002 fixes the wrong column list and filter for the last fetched
> > > table in a concurrently dropped publication.
> > > v4-0003 fixes the wrong column list and row filter for concurrently
> > > altered publication that drops a table.
> >
> > Thanks for the patch, Shveta. While testing, I see all the reported
> > cases are fixed but found another issue, check below test
> >
> > -- Setup
> > CREATE TABLE tbl_parent (a int) PARTITION BY RANGE (a);
> > CREATE TABLE tbl_part1 PARTITION OF tbl_parent FOR VALUES FROM (1) TO (10);
> > CREATE TABLE tbl_part2 PARTITION OF tbl_parent FOR VALUES FROM (10) TO (20);
> > CREATE PUBLICATION pub FOR TABLE tbl_parent WITH
> > (publish_via_partition_root = false);
> >
> > Session 1:
> > BEGIN;
> > LOCK TABLE tbl_part2 IN ACCESS EXCLUSIVE MODE;
> >
> > Session 2:
> > SELECT relid::regclass FROM pg_get_publication_tables('pub');
> > -- Emits tbl_part1, then blocks waiting on tbl_part2
> >
> > Session 1:
> > ALTER PUBLICATION pub SET (publish_via_partition_root = true);
> > COMMIT;
> >
> > Session 2:
> >    relid
> > -----------
> >  tbl_part1
> > (1 row)
> >
> > So while it was waiting on the lock on tbl_part2, we concurrently
> > updated publish_via_partition_root to true, and due to that the
> > inidividual partitions are not listed and it skipped tbl_part2,
> > whereas tbl_part1 is already listed, that was never valid either
> > before (tbl_part1, tbl_part2) or after (tbl_parent) the ALTER
> > PUBLICATION, what do you think?
>
> Agreed; the current result is wrong. It seems a regerssion of 0003;
> is_table_publishable_in_publication() was evaluated with the
> publication’s current 'publish_via_partition_root', while the list of
> relations was built on the first call with the old setting. So
> tbl_part2 looked “not published” even though only its representation
> changed. Since tbl_part1 has already been returned, we can’t produce
> the after-state (tbl_parent) without materializing the result, so I
> think the right answer is the before-state (tbl_part1, tbl_part2),
> which is also what HEAD returns. Thoughts?
>
> The way to achieve this is to let
> is_table_publishable_in_publication() decide based on the publication
> collected on the first call (saved in publication_tables_state). Its
> membership lookups (pg_publication_rel, pg_publication_namespace,
> ancestors etc) still read the current catalog, so a table that was
> really removed from the publication is still skipped, as 0003 intends;
> only the change of pub-setting is no longer confused as a removal of
> table.

Attached v5 which addresses the issue raised by Dilip.

v5-0001 and v5-0002: Unchanged.
V5-0003: Addresses the bug caused by 'publish_via_partition_root'
setting change.

thanks
Shveta

Attachments: download all (3 files, .tar.gz)

v5-0001-Fix-cache-lookup-failure-in-pg_get_publication_ta.patch (application/octet-stream, 8.9K) download From 2fd42e064610b8fadff107b2a082e2fed0f997ea Mon Sep 17 00:00:00 2001 From: Ajin Cherian <itsajin@gmail.com> Date: Fri, 13 Nov 2026 14:37:19 +1100 Subject: [PATCH v5 1/3] Fix cache lookup failure in pg_get_publication_tables() after concurrent DROP PUBLICATION pg_get_publication_tables() collects (pubid, relid) pairs on its first call and then looks each publication up again per row with GetPublication(). No lock is held on the publication, so if it is dropped concurrently -- for instance while the function waits for the lock in try_table_open(), which also processes invalidations -- the lookup fails with an internal "cache lookup failed for publication" error. Reported-by: Noah <imchifan8@gmail.com> Author: Ajin Cherian <itsajin@gmail.com> Reviewed-by: shveta malik <shveta.malik@gmail.com> Discussion: https://postgr.es/m/19864-5260a1fbeec7728c@postgresql.org --- src/backend/catalog/pg_publication.c | 72 +++++++++++++++++-- .../expected/pub-concurrent-drop.out | 24 +++++++ .../isolation/specs/pub-concurrent-drop.spec | 21 +++++- 3 files changed, 111 insertions(+), 6 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index f11ea8839ca..05cca134065 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1305,9 +1305,12 @@ GetAllSchemaPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt) * Get publication using oid * * The Publication struct and its data are palloc'ed here. + * + * If missing_ok is true, return NULL when the publication does not exist + * (e.g., it was dropped concurrently); otherwise raise an error. */ -Publication * -GetPublication(Oid pubid) +static Publication * +get_publication_internal(Oid pubid, bool missing_ok) { HeapTuple tup; Publication *pub; @@ -1315,7 +1318,11 @@ GetPublication(Oid pubid) tup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(pubid)); if (!HeapTupleIsValid(tup)) + { + if (missing_ok) + return NULL; elog(ERROR, "cache lookup failed for publication %u", pubid); + } pubform = (Form_pg_publication) GETSTRUCT(tup); @@ -1336,6 +1343,12 @@ GetPublication(Oid pubid) return pub; } +Publication * +GetPublication(Oid pubid) +{ + return get_publication_internal(pubid, false); +} + /* * Get Publication using name. */ @@ -1444,6 +1457,27 @@ is_table_publishable_in_publication(Oid relid, Publication *pub) ObjectIdGetDatum(pub->oid))); } +/* + * A helper function for pg_get_publication_tables() to get the name of the + * publication with the given OID from the list of publications collected on + * the first call. It is used to report a publication that has been dropped + * concurrently, whose name can no longer be looked up in the catalog. + */ +static char * +get_pubname_from_list(List *pubs, Oid pubid) +{ + foreach_ptr(Publication, pub, pubs) + { + if (pub->oid == pubid) + return pub->name; + } + + /* The publication must have been collected on the first call. */ + elog(ERROR, "could not find publication %u in the list of publications", + pubid); + return NULL; /* keep compiler quiet */ +} + /* * Helper function to get information of the tables in the given * publication(s). @@ -1471,6 +1505,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, typedef struct { List *table_infos; /* list of published_rel */ + List *pubs; /* list of Publication, for error messages */ int curr_idx; /* current index into table_infos */ } publication_tables_state; @@ -1483,6 +1518,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, TupleDesc tupdesc; MemoryContext oldcontext; List *table_infos = NIL; + List *pubs = NIL; Datum *elems; int nelems, i; @@ -1521,6 +1557,8 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, if (pub_elem == NULL) continue; + pubs = lappend(pubs, pub_elem); + if (filter_by_relid) { /* Check if the given table is published for the publication */ @@ -1609,6 +1647,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, /* Store the state to be used across SRF calls. */ ptstate = palloc_object(publication_tables_state); ptstate->table_infos = table_infos; + ptstate->pubs = pubs; ptstate->curr_idx = 0; funcctx->user_fctx = ptstate; @@ -1647,10 +1686,35 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, continue; /* - * Form tuple with appropriate data. + * The publication OIDs were also collected earlier and we don't hold + * a lock on the publication, so it may have been dropped concurrently + * (for instance, while we waited for the lock on the table above, + * which also processes pending invalidations). + * + * Unlike a dropped table, we can't just skip it: rows for this + * publication may already have been returned, so the result would be + * a misleading partial list. Report it as an error instead, unless + * the caller asked for missing publications to be ignored, which is + * consistent with how they are treated on the first call. */ + pub = get_publication_internal(table_info->pubid, true); + if (pub == NULL) + { + if (!pub_missing_ok) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication \"%s\" does not exist", + get_pubname_from_list(ptstate->pubs, + table_info->pubid)), + errdetail("The publication was dropped concurrently.")); + + table_close(rel, AccessShareLock); + continue; + } - pub = GetPublication(table_info->pubid); + /* + * Form tuple with appropriate data. + */ values[0] = ObjectIdGetDatum(pub->oid); values[1] = ObjectIdGetDatum(relid); diff --git a/src/test/isolation/expected/pub-concurrent-drop.out b/src/test/isolation/expected/pub-concurrent-drop.out index 8360af0ec9c..e31d06f917d 100644 --- a/src/test/isolation/expected/pub-concurrent-drop.out +++ b/src/test/isolation/expected/pub-concurrent-drop.out @@ -14,3 +14,27 @@ tablename pubdrop.keepme (1 row) + +starting permutation: lock list_pub_tables drop_pub_and_commit +step lock: BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; +step list_pub_tables: + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables('pub_schema') + ORDER BY tablename; + <waiting ...> +step drop_pub_and_commit: DROP PUBLICATION pub_schema; COMMIT; +step list_pub_tables: <... completed> +ERROR: publication "pub_schema" does not exist + +starting permutation: lock list_pub_table drop_pub_and_commit +step lock: BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; +step list_pub_table: + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables(ARRAY['pub_schema'], 'pubdrop.dropme'::regclass); + <waiting ...> +step drop_pub_and_commit: DROP PUBLICATION pub_schema; COMMIT; +step list_pub_table: <... completed> +tablename +--------- +(0 rows) + diff --git a/src/test/isolation/specs/pub-concurrent-drop.spec b/src/test/isolation/specs/pub-concurrent-drop.spec index 4f7d701d60c..3b41ced1ba5 100644 --- a/src/test/isolation/specs/pub-concurrent-drop.spec +++ b/src/test/isolation/specs/pub-concurrent-drop.spec @@ -1,4 +1,4 @@ -# Tests for concurrently dropping a relation while a publication's tables are +# Tests for concurrently dropping a relation or the publication itself while a publication's tables are # being listed. setup @@ -12,12 +12,13 @@ setup teardown { DROP SCHEMA pubdrop CASCADE; - DROP PUBLICATION pub_schema; + DROP PUBLICATION IF EXISTS pub_schema; } session s1 step lock { BEGIN; LOCK pubdrop.dropme IN ACCESS EXCLUSIVE MODE; } step drop_and_commit { DROP TABLE pubdrop.dropme; COMMIT; } +step drop_pub_and_commit { DROP PUBLICATION pub_schema; COMMIT; } session s2 step list_pub_tables @@ -26,6 +27,11 @@ step list_pub_tables FROM pg_get_publication_tables('pub_schema') ORDER BY tablename; } +step list_pub_table +{ + SELECT relid::regclass AS tablename + FROM pg_get_publication_tables(ARRAY['pub_schema'], 'pubdrop.dropme'::regclass); +} # Hold an ACCESS EXCLUSIVE lock on the table in one session, so that the query # listing a publication's tables in another session blocks when it tries to @@ -34,3 +40,14 @@ step list_pub_tables # skips the now-dropped table instead of erroring with "could not open relation # with OID". permutation lock list_pub_tables drop_and_commit + +# Same as above, but drop the publication instead of the table. When the +# query resumes, the publication it collected the table OIDs from is gone. +# Rather than returning a partial list, it reports that the publication was +# concurrently dropped instead of failing with "cache lookup failed for +# publication". +permutation lock list_pub_tables drop_pub_and_commit + +# The variant that takes a table OID ignores missing publications, so it +# skips the row for the dropped publication instead. +permutation lock list_pub_table drop_pub_and_commit -- 2.34.1
v5-0002-Do-not-return-wrong-column-list-and-row-filter-fo.patch (application/octet-stream, 2.5K) download From 4335f2b72046c0c32c00c6feda46e65ca5bf9282 Mon Sep 17 00:00:00 2001 From: Shveta Malik <shveta.malik@gmail.com> Date: Tue, 10 Nov 2026 01:13:46 +0530 Subject: [PATCH v5 2/3] Do not return wrong column list and row filter for a dropped publication pg_get_publication_tables() could see a concurrent DROP PUBLICATION in its PUBLICATIONNAMESPACEMAP and PUBLICATIONRELMAP lookups after get_publication_internal() had already found the publication: with a cold cache, these lookups process pending invalidations. The missing pg_publication_rel entry was then taken as "no column list and no row filter", and for the last table nothing else caught it, so the query returned a wrong row. Recheck that the publication still exists when no entry is found. --- src/backend/catalog/pg_publication.c | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 05cca134065..6825e7528c5 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1728,10 +1728,37 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, !SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP, ObjectIdGetDatum(schemaid), ObjectIdGetDatum(pub->oid))) + { pubtuple = SearchSysCacheCopy2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid), ObjectIdGetDatum(pub->oid)); + /* + * No pg_publication_rel entry is normal for partitions published + * via their parent. However, it can also mean the publication was + * dropped concurrently, as the lookups above process pending + * invalidations when opening the catalogs with a cold cache. + * Distinguish these cases to avoid returning incorrect column + * lists and row filters. If an entry was found, it represents the + * publication definition before the drop. + */ + if (!HeapTupleIsValid(pubtuple) && + !SearchSysCacheExists1(PUBLICATIONOID, + ObjectIdGetDatum(pub->oid))) + { + if (!pub_missing_ok) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication \"%s\" does not exist", + get_pubname_from_list(ptstate->pubs, + table_info->pubid)), + errdetail("The publication was dropped concurrently.")); + + table_close(rel, AccessShareLock); + continue; + } + } + if (HeapTupleIsValid(pubtuple)) { /* Lookup the column list attribute. */ -- 2.34.1
v5-0003-Skip-rows-for-tables-concurrently-removed-from-th.patch (application/octet-stream, 5.0K) download From 4f74b71f2e722a126aa4efea1db1312ec37f8e79 Mon Sep 17 00:00:00 2001 From: Shveta Malik <shveta.malik@gmail.com> Date: Mon, 16 Nov 2026 04:32:55 +0000 Subject: [PATCH v5 3/3] Skip rows for tables concurrently removed from the publication A missing pg_publication_rel entry can also mean that the table was concurrently removed from the publication (ALTER PUBLICATION ... DROP TABLE), for instance while we waited for the table lock. The row was then returned with all columns and no row filter. Skip the row if is_table_publishable_in_publication() says the table is no longer published, checking against the publication as collected on the first call, so that a concurrent change of publish_via_partition_root isn't taken as a removal. --- src/backend/catalog/pg_publication.c | 59 +++++++++++++++++++--------- 1 file changed, 41 insertions(+), 18 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 6825e7528c5..504d2efc835 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1458,18 +1458,17 @@ is_table_publishable_in_publication(Oid relid, Publication *pub) } /* - * A helper function for pg_get_publication_tables() to get the name of the - * publication with the given OID from the list of publications collected on - * the first call. It is used to report a publication that has been dropped - * concurrently, whose name can no longer be looked up in the catalog. + * A helper function for pg_get_publication_tables() to get the publication + * with the given OID from the list of publications collected on the first + * call, i.e. the publication as it was when the list of tables was built. */ -static char * -get_pubname_from_list(List *pubs, Oid pubid) +static Publication * +get_publication_from_list(List *pubs, Oid pubid) { foreach_ptr(Publication, pub, pubs) { if (pub->oid == pubid) - return pub->name; + return pub; } /* The publication must have been collected on the first call. */ @@ -1478,6 +1477,20 @@ get_pubname_from_list(List *pubs, Oid pubid) return NULL; /* keep compiler quiet */ } +/* + * A helper function for pg_get_publication_tables() to get the name of the + * publication with the given OID from the list of publications collected on + * the first call. It is used to report a publication that has been dropped + * concurrently, whose name can no longer be looked up in the catalog. + */ +static char * +get_pubname_from_list(List *pubs, Oid pubid) +{ + Publication *pub = get_publication_from_list(pubs, pubid); + + return pub->name; +} + /* * Helper function to get information of the tables in the given * publication(s). @@ -1505,7 +1518,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, typedef struct { List *table_infos; /* list of published_rel */ - List *pubs; /* list of Publication, for error messages */ + List *pubs; /* list of Publication, for error messages */ int curr_idx; /* current index into table_infos */ } publication_tables_state; @@ -1734,19 +1747,29 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, ObjectIdGetDatum(pub->oid)); /* - * No pg_publication_rel entry is normal for partitions published - * via their parent. However, it can also mean the publication was - * dropped concurrently, as the lookups above process pending - * invalidations when opening the catalogs with a cold cache. - * Distinguish these cases to avoid returning incorrect column - * lists and row filters. If an entry was found, it represents the - * publication definition before the drop. + * A missing pg_publication_rel entry is normal for a partition + * published via its ancestor. In any other case, it means that + * the table was concurrently removed from the publication, or + * that the publication was concurrently dropped. Building the + * row would then wrongly show no column list and no row filter, + * so skip the row if the table was removed. If the publication + * was dropped, raise the same error as when + * get_publication_internal() above doesn't find it. + * + * Use the publication as it was on the first call, which built + * our list of tables, so that a concurrent change of + * publish_via_partition_root isn't mistaken for a removal. Check + * whether the publication still exists only afterwards, since + * is_table_publishable_in_publication() may itself see the drop. */ if (!HeapTupleIsValid(pubtuple) && - !SearchSysCacheExists1(PUBLICATIONOID, - ObjectIdGetDatum(pub->oid))) + !is_table_publishable_in_publication(relid, + get_publication_from_list(ptstate->pubs, + pub->oid))) { - if (!pub_missing_ok) + if (!pub_missing_ok && + !SearchSysCacheExists1(PUBLICATIONOID, + ObjectIdGetDatum(pub->oid))) ereport(ERROR, errcode(ERRCODE_UNDEFINED_OBJECT), errmsg("publication \"%s\" does not exist", -- 2.34.1
^ permalink raw reply [nested|flat] 14+ messages in thread


end of thread, other threads:[~2026-11-16 04:55 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-11 05:33 BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION PG Bug reporting form <noreply@postgresql.org>
2026-11-12 04:00 ` Ajin Cherian <itsajin@gmail.com>
2026-11-12 05:20   ` shveta malik <shveta.malik@gmail.com>
2026-11-12 10:12     ` Ajin Cherian <itsajin@gmail.com>
2026-11-12 10:37       ` shveta malik <shveta.malik@gmail.com>
2026-11-12 12:16         ` shveta malik <shveta.malik@gmail.com>
2026-11-13 03:51           ` Ajin Cherian <itsajin@gmail.com>
2026-11-13 06:28             ` shveta malik <shveta.malik@gmail.com>
2026-11-13 08:42               ` shveta malik <shveta.malik@gmail.com>
2026-11-13 09:31                 ` Zsolt Parragi <zsolt.parragi@percona.com>
2026-11-13 10:10                   ` shveta malik <shveta.malik@gmail.com>
2026-11-14 10:48                     ` Dilip Kumar <dilipbalaut@gmail.com>
2026-11-16 03:54                       ` shveta malik <shveta.malik@gmail.com>
2026-11-16 04:55                         ` shveta malik <shveta.malik@gmail.com>

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