Test publication.sql racing with subscription.sql might fail due to cache lookup error

On Tue, 13 Oct 2026 at 01:30, Alexander Lakhin <exclusion@gmail.com> wrote:

Hello hackers,

Today tamandua emitted a new failure of publication.sql [1]:
# diff -U3 /home/bf/bf-build/tamandua/HEAD/pgsql/src/test/regress/expected/publication.out
/home/bf/bf-build/tamandua/HEAD/pgsql.build/testrun/test_plan_advice/001_replan_regress/data/results/publication.out
# --- /home/bf/bf-build/tamandua/HEAD/pgsql/src/test/regress/expected/publication.out 2026-09-26 17:11:33.701652728 +0200
# +++
/home/bf/bf-build/tamandua/HEAD/pgsql.build/testrun/test_plan_advice/001_replan_regress/data/results/publication.out
2026-10-12 11:50:53.473655771 +0200
# @@ -1349,31 +1349,31 @@
# DETAIL: Column lists cannot be specified in publications containing FOR TABLES IN SCHEMA elements.
# -- ok - only publish schema
# CREATE PUBLICATION testpub_tbl9 FOR TABLES IN SCHEMA public;
# +ERROR: cache lookup failed for relation 36073
# -- failure - add a table with column list when there is already a schema in the
# -- publication

postmaster.log contains:
2026-10-12 11:50:51.429 CEST [418046][client backend][98/742:0] LOG: statement: CREATE PUBLICATION testpub_tbl9 FOR
TABLES IN SCHEMA public;
2026-10-12 11:50:51.432 CEST [418061][client backend][99/395:0] LOG: statement: DROP TABLE public.test_move;
2026-10-12 11:50:51.436 CEST [418061][client backend][99/396:0] LOG: statement: SET client_min_messages = WARNING;
2026-10-12 11:50:51.441 CEST [418046][client backend][98/742:14613] ERROR: cache lookup failed for relation 36073
2026-10-12 11:50:51.441 CEST [418046][client backend][98/742:14613] STATEMENT: CREATE PUBLICATION testpub_tbl9 FOR
TABLES IN SCHEMA public;

I've reproduced this with an isolated script:
for ((n=1;n<=10;n++)); do
echo "ITERATION $n"

for i in {1..100}; do echo "
CREATE TABLE public.test_move (id int);
DROP TABLE public.test_move;
"; done | psql >psql_sub.log &

for i in {1..100}; do echo "
DROP TABLE IF EXISTS testpub_root;
CREATE TABLE testpub_root(a int) PARTITION BY RANGE(a);
CREATE TABLE testpub_part1 PARTITION OF testpub_root FOR VALUES FROM (0) TO (100);

CREATE PUBLICATION testpub_tbl9 FOR TABLES IN SCHEMA public;
DROP PUBLICATION testpub_tbl9;
"; done | psql -v ON_ERROR_STOP=1 >psql_pub.log || break
wait
done
wait
grep -A1 'ERROR: cache lookup failed' server.log

It fails for me as below:
ITERATION 1
NOTICE: table "testpub_root" does not exist, skipping
ERROR: cache lookup failed for relation 16880
2026-10-12 15:21:47.421 EDT|u2|regression|6acd334b.34270f|ERROR: cache lookup failed for relation 16880
2026-10-12 15:21:47.421 EDT|u2|regression|6acd334b.34270f|STATEMENT: CREATE PUBLICATION testpub_tbl9 FOR TABLES IN
SCHEMA public;
or
...
ITERATION 2
ERROR: cache lookup failed for relation 17514
2026-10-12 15:21:05.597 EDT|u2|regression|6acd3321.3426de|ERROR: cache lookup failed for relation 17514
2026-10-12 15:21:05.597 EDT|u2|regression|6acd3321.3426de|STATEMENT: DROP PUBLICATION testpub_tbl9;

Reproduced starting from 5a2832465 (with slightly different syntax).

Thanks for reporting this Alexander.
This fails because RemovePublicationSchemaById() calls
GetSchemaPublicationRelations(), which collects relation OIDs using a
catalog snapshot. If a relation is dropped concurrently after that
snapshot, its OID can still be present in the returned list.
InvalidatePublicationRels() then calls
CacheInvalidateRelcacheByRelid() for that OID. By this point, the
relation may already have been removed and its invalidation processed,
so the lookup fails with:
cache lookup failed for relation %u

The publishing session does not hold a lock on the relation, so the
concurrent DROP TABLE is not blocked.
I felt InvalidatePublicationRels() should simply skip relations that
no longer exist. There is no relcache entry left to invalidate, and
the DROP has already queued the required invalidation.

Attached a patch to handle the same.

Regards,
Vignesh

Hi Vignesh,

Thanks for reporting this Alexander.
This fails because RemovePublicationSchemaById() calls
GetSchemaPublicationRelations(), which collects relation OIDs using a
catalog snapshot. If a relation is dropped concurrently after that
snapshot, its OID can still be present in the returned list.
InvalidatePublicationRels() then calls
CacheInvalidateRelcacheByRelid() for that OID. By this point, the
relation may already have been removed and its invalidation processed,
so the lookup fails with:
cache lookup failed for relation %u

To confirm, publication_add_schema()->GetSchemaPublicationRelations() gathers the
oid of relations without acquiring the lock. Therefore another process can drop
the relation and this can cause the cache lookup failure, right?

I felt InvalidatePublicationRels() should simply skip relations that
no longer exist. There is no relcache entry left to invalidate, and
the DROP has already queued the required invalidation.

I checked your source, but isn't there still a whole that failure can happen?
Relation can be dropped after the SearchSysCache1().

How about the below? It tries to open the relation, and skip if cannot.
Relation cache can be invalidated via CacheInvalidateRelcache() instead of ...ByTuple().
```
if (list_length(relids) < MAX_RELCACHE_INVAL_MSGS)
{
foreach_oid(relid, relids)
{
Relation rel = try_table_open(relid, AccessShareLock);

if (rel == NULL)
continue;

CacheInvalidateRelcache(rel);
table_close(rel, AccessShareLock);
}
}
```

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Hi,

On Tue, Oct 13, 2026 at 2:55 PM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:

Hi Vignesh,

Thanks for reporting this Alexander.
This fails because RemovePublicationSchemaById() calls
GetSchemaPublicationRelations(), which collects relation OIDs using a
catalog snapshot. If a relation is dropped concurrently after that
snapshot, its OID can still be present in the returned list.
InvalidatePublicationRels() then calls
CacheInvalidateRelcacheByRelid() for that OID. By this point, the
relation may already have been removed and its invalidation processed,
so the lookup fails with:
cache lookup failed for relation %u

To confirm, publication_add_schema()->GetSchemaPublicationRelations() gathers the
oid of relations without acquiring the lock. Therefore another process can drop
the relation and this can cause the cache lookup failure, right?

I felt InvalidatePublicationRels() should simply skip relations that
no longer exist. There is no relcache entry left to invalidate, and
the DROP has already queued the required invalidation.

I checked your source, but isn't there still a whole that failure can happen?
Relation can be dropped after the SearchSysCache1().

How about the below? It tries to open the relation, and skip if cannot.
Relation cache can be invalidated via CacheInvalidateRelcache() instead of ...ByTuple().

I think it's unnecessary to open the table. IIUC, the main goal is to avoid the
cache lookup error in CacheInvalidateRelcacheByRelid(), and the v1 patch is
sufficient for that since it uses CacheInvalidateRelcacheByTuple(). The same
strategy is also used in RemoveRoleFromObjectPolicy() to handle concurrent table
drops.

But I think adding a new function for such simple logic is also unnecessary,
we can just search the syscache in the loop instead.

Best Regards,
Zhijie Hou

On Tue, 13 Oct 2026 at 12:55, Zhijie Hou <houzhijie22@gmail.com> wrote:

Hi,

On Tue, Oct 13, 2026 at 2:55 PM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:

Hi Vignesh,

Thanks for reporting this Alexander.
This fails because RemovePublicationSchemaById() calls
GetSchemaPublicationRelations(), which collects relation OIDs using a
catalog snapshot. If a relation is dropped concurrently after that
snapshot, its OID can still be present in the returned list.
InvalidatePublicationRels() then calls
CacheInvalidateRelcacheByRelid() for that OID. By this point, the
relation may already have been removed and its invalidation processed,
so the lookup fails with:
cache lookup failed for relation %u

To confirm, publication_add_schema()->GetSchemaPublicationRelations() gathers the
oid of relations without acquiring the lock. Therefore another process can drop
the relation and this can cause the cache lookup failure, right?

I felt InvalidatePublicationRels() should simply skip relations that
no longer exist. There is no relcache entry left to invalidate, and
the DROP has already queued the required invalidation.

I checked your source, but isn't there still a whole that failure can happen?
Relation can be dropped after the SearchSysCache1().

How about the below? It tries to open the relation, and skip if cannot.
Relation cache can be invalidated via CacheInvalidateRelcache() instead of ...ByTuple().

I think it's unnecessary to open the table. IIUC, the main goal is to avoid the
cache lookup error in CacheInvalidateRelcacheByRelid(), and the v1 patch is
sufficient for that since it uses CacheInvalidateRelcacheByTuple(). The same
strategy is also used in RemoveRoleFromObjectPolicy() to handle concurrent table
drops.

I think the same issue can occur in other callers of
InvalidatePublicationRels() apart from the reported adding tables in a
schema, such as altering publication options, dropping a table from a
publication, and dropping a schema from a publication. We can safely
ignore relations that have been dropped concurrently in these cases.
The other caller, which adds a table directly to a publication, is not
affected because it locks the table and prevents it from being dropped
concurrently. So, we can continue to throw an error if the table no
longer exists in that case.
Thoughts?

Regards,
Vignesh

Hi,

I think it's unnecessary to open the table. IIUC, the main goal is to avoid the
cache lookup error in CacheInvalidateRelcacheByRelid(), and the v1 patch is
sufficient for that since it uses CacheInvalidateRelcacheByTuple(). The same
strategy is also used in RemoveRoleFromObjectPolicy() to handle concurrent
table
drops.

Oh, right, CacheInvalidateRelcacheByTuple() does not require the additional cache
search. My code tried to serialize these operations, but it may be
overengineering.

Best regards,
Hayato Kuroda
FUJITSU LIMITED

On Tue, 13 Oct 2026 at 13:11, vignesh C <vignesh21@gmail.com> wrote:

I think the same issue can occur in other callers of
InvalidatePublicationRels() apart from the reported adding tables in a
schema, such as altering publication options, dropping a table from a
publication, and dropping a schema from a publication. We can safely
ignore relations that have been dropped concurrently in these cases.
The other caller, which adds a table directly to a publication, is not
affected because it locks the table and prevents it from being dropped
concurrently. So, we can continue to throw an error if the table no
longer exists in that case.

The attached v2 version patch has the changes for the same. This also
addresses hou-san comment of not requiring a new function from [1]/messages/by-id/CAFvd2n-rM=MdNjNq8VvSx_0rxWiDpmdXK31OAiSP=G0d6a29TQ@mail.gmail.com.

[1]: /messages/by-id/CAFvd2n-rM=MdNjNq8VvSx_0rxWiDpmdXK31OAiSP=G0d6a29TQ@mail.gmail.com

Regards,
Vignesh

Dear Vignesh,

The attached v2 version patch has the changes for the same. This also
addresses hou-san comment of not requiring a new function from [1].

Thanks for updating the patch. I've been thinking that publication_add_relation()
can really cause the cache lookup failure. The caller PublicationAddTables()
acquires the ShareUpdateExclusiveLock for the expressly specified relations via
OpenTableList(), the issue cannot happen for these targets. So I feel it might be
OK not to raise an ERROR in this case.

FYI, I did several experiments, and found that cache lookup failure can happen
with the below schema definition.

```
root // ADD TABLE
├── mid1
│ └── victim // to be dropped
└── mid2
└── trigger
└── other
```

When the parent table is specified by the ALTER PUB command, all {grand}children
are gathered via find_all_inheritors(NoLock). The cache miss cannot happen if the
the direct child tables are dropped, because the DROP command will acquire the
AccessExclusive lock. It will conflict with the ALTER PUB command, so these commands
can be serialized.

For the grandchild tables, the DROP does not take care the root thus the concurrent
operation can happen. For reproducing it, find_all_inheritors() must gather the
OID which is concurrently dropped then invalidate its relcache. Above schema
definition is reachable with the concurrent DROP TABLE victim command. It's too
complex to provide a reproducer by the injection point though.

Below briefly shows loops in find_all_inheritors(), and the outer loop can seek
all relations. If drop is done after the OID is appended to the list and next
iteration checks the pg_inherit in find_inheritance_children(), the relcache for
victim can be invalidated.

```
foreach(l, rels_list)
{
Oid currentrel = lfirst_oid(l);
List *currentchildren;
ListCell *lc;

/* Get the direct children of this rel */
currentchildren = find_inheritance_children(currentrel, lockmode); // (1)
...
foreach(lc, currentchildren)
{
...
rels_list = lappend_oid(rels_list, child_oid); // (2)
}

// do DROP TABLE victim; after currentrel points the victim here.
// Must be done after the victim is appended at (2).
// Upcoming (1) acquires a lock for pg_inherits, and a relcache for victim
// can be invalidated.
}
```

Above meant the cache mis rarely happens. Do we need a guard for the case?

Best regards,
Hayato Kuroda
FUJITSU LIMITED