Re: GRANT/REVOKE locking semantics in get_object_address() - Mailing list pgsql-hackers

Dear Team,

With reference to the conversation ongioing in message ID: d2d96fe4-6b1c-4bd3-813b-c2cfcb4a79f4, I am writing to express my interest in contributing to the ongoing work on fixing the bug related to Proper object locking for GRANT/REVOKE.

I have been following the discussion around the recent changes where get_object_address() now acquires locks during GRANT/REVOKE.

To help visualize the trade-offs, I prepared an isolation test comparing AccessShareLock (current PG18 behavior) and ShareUpdateExclusiveLock (hypothetical alternative). The attached .spec and .out files show the results.

Summary of outcomes:

• AccessShareLock (PG18):

  • Session A: holds ACCESS SHARE

  • Session B: GRANT waits until A commits

  • Session C: plain SELECT proceeds normally (not blocked)

• ShareUpdateExclusiveLock (hypothetical):

  • Session A: holds SHARE UPDATE EXCLUSIVE

  • Session B: GRANT waits until A commits

  • Session C: plain SELECT is also blocked (autovacuum and readers delayed)

So the stronger lock would block normal readers/autovacuum, which feels too heavy for GRANT/REVOKE.

This test outcome seems to confirm the reasoning that Noah and Nathan outlined - AccessShareLock avoids surprising contention, even though some races remain possible.

Would it make sense to add an isolation test like this (at least for documentation and regression coverage), to preserve visibility on the chosen trade-off?

Regards,
Athiyaman