BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
On Thu, Nov 12, 2026 at 2:16 PM PG Bug reporting form
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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
От:
shveta malik <shveta.malik@gmail.com>
Дата:
On Thu, Nov 12, 2026 at 9:30 AM Ajin Cherian wrote:
>
> On Thu, Nov 12, 2026 at 2:16 PM PG Bug reporting form
> 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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
On Thu, Nov 12, 2026 at 4:20 PM shveta malik wrote:
>
> On Thu, Nov 12, 2026 at 9:30 AM Ajin Cherian 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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
От:
shveta malik <shveta.malik@gmail.com>
Дата:
On Thu, Nov 12, 2026 at 3:42 PM Ajin Cherian wrote:
>
> On Thu, Nov 12, 2026 at 4:20 PM shveta malik wrote:
> >
> > On Thu, Nov 12, 2026 at 9:30 AM Ajin Cherian 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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
От:
shveta malik <shveta.malik@gmail.com>
Дата:
On Thu, Nov 12, 2026 at 4:07 PM shveta malik wrote:
>
> On Thu, Nov 12, 2026 at 3:42 PM Ajin Cherian wrote:
> >
> > On Thu, Nov 12, 2026 at 4:20 PM shveta malik wrote:
> > >
> > > On Thu, Nov 12, 2026 at 9:30 AM Ajin Cherian 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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
On Thu, Nov 12, 2026 at 11:16 PM shveta malik 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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
От:
shveta malik <shveta.malik@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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
On Fri, Nov 13, 2026 at 11:58 AM shveta malik 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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
От:
Zsolt Parragi <zsolt.parragi@percona.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;
+ }
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
On Fri, Nov 13, 2026 at 3:01 PM Zsolt Parragi 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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
От:
Dilip Kumar <dilipbalaut@gmail.com>
Дата:
On Fri, Nov 13, 2026 at 3:40 PM shveta malik wrote:
>
> On Fri, Nov 13, 2026 at 3:01 PM Zsolt Parragi 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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
От:
shveta malik <shveta.malik@gmail.com>
Дата:
On Sat, Nov 14, 2026 at 4:18 PM Dilip Kumar wrote:
>
> On Fri, Nov 13, 2026 at 3:40 PM shveta malik wrote:
> >
> > On Fri, Nov 13, 2026 at 3:01 PM Zsolt Parragi 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
Re: BUG #19864: pg_get_publication_tables() gets cache lookup error after concurrent DROP PUBLICATION
On Mon, Nov 16, 2026 at 9:24 AM shveta malik wrote:
>
> On Sat, Nov 14, 2026 at 4:18 PM Dilip Kumar wrote:
> >
> > On Fri, Nov 13, 2026 at 3:40 PM shveta malik wrote:
> > >
> > > On Fri, Nov 13, 2026 at 3:01 PM Zsolt Parragi 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