Regression tests failures due to concurrent grants

Hi,

I've seen a number of regression test failures due to grants failing with
ERROR: tuple concurrently updated
as part of a grant in publication (and I think subscription), e.g. in [1] and
a few locally.

@@ -1678,23 +1678,29 @@
ERROR: permission denied for database regression
SET ROLE regress_publication_user;
GRANT CREATE ON DATABASE regression TO regress_publication_user2;
+ERROR: tuple concurrently updated
SET ROLE regress_publication_user2;
SET client_min_messages = 'ERROR';

The problem is that both publication and subscription grant rights on the
regression database, and that GRANT does not actually perform any object
locking. If you are therefore "unlucky" enough.

This was actually reported before, in [2], but at that time Tom voted to not
fix it by changing the scheduling, due to the rarity of the problem, the
negative impact on test concurrency.

I don't quite know what could have made it more likely, but I've never
encountered it before the last few weeks and since then a few times. Any
theories?

It looks like the problem has been present for a few years, with c3afe8cf5a1
(2023-03-30) introducing a potentially concurrent grant (to subscription,
publication had it before). One reason it might have gotten more frequent is
that 8185bb53476 (2026-03-06) added more grants - but that's still half a year
ago...

Of course we could fix this by deconflicting the two tests on the schedule
level, but I think that'd be a somewhat sad solution. For one, low-concurrency
test groups are bad for test throughput, but also, I think the concurrency
actually has found some bugs?

A local way to fix the problem could be to put the GRANTs into transactions
with something that acquires an exclusive lock on the database object
(assuming that exists).

But perhaps we should just fix the locking? The explanation for the current
locking behavior seems weak to me (introduced in [3]/[4]):

/*
* objectNamesToOids
*
* Turn a list of object names of a given type into an Oid list.
*
* XXX This function intentionally takes only an AccessShareLock. In the face
* of concurrent DDL, we might easily latch onto an old version of an object,
* causing the GRANT or REVOKE statement to fail. But it does prevent the
* object from disappearing altogether. To do better, we would need to use a
* self-exclusive lock, perhaps ShareUpdateExclusiveLock, here and before
* *every* CatalogTupleUpdate() of a row that GRANT/REVOKE can affect.
* Besides that additional work, this could have operational costs. For
* example, it would make GRANT ALL TABLES IN SCHEMA terminate every
* autovacuum running in the schema and consume a shared lock table entry per
* table in the schema. The user-visible benefit of that additional work is
* just changing "ERROR: tuple concurrently updated" to blocking. That's not
* nothing, but it might not outweigh autovacuum termination and lock table
* consumption spikes.
*/

I don't understand the lock table argument: AccessShareLock also enters into
the lock table?

I'm not sure the "GRANT ALL TABLES IN SCHEMA terminate every autovacuum
running in the schema" is *that* strong an argument. If we really really care
about that, we could just weaken the lock level selectively for relations, but
I also am not convinced that cancelling autovacs in response to a command like
this would be the end of the world.

Greetings,

Andres Freund

[1] https://github.com/postgres/postgres/actions/runs/35229345528/job/105229474410
[2] https://postgr.es/m/18dcfb7f-5deb-4487-ae22-a2c16839519a%40gmail.com
[3] https://postgr.es/m/bf72b82c-124d-4efa-a484-bb928e9494e4%40eisentraut.org
[4] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e36fa9319b13