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
