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
grant_lock_modes.out
Description: Binary data
# grant_lock_modes.spec
# Illustrates difference between AccessShareLock and ShareUpdateExclusiveLock
# when GRANT/REVOKE looks up relations via get_object_address().
setup
{
CREATE TABLE t (id int);
CREATE ROLE r1;
}
teardown
{
DROP TABLE t;
DROP ROLE r1;
}
session "s1"
step "begin1" { BEGIN; }
step "lock_as" { LOCK TABLE t IN ACCESS SHARE MODE; }
step "lock_sue" { LOCK TABLE t IN SHARE UPDATE EXCLUSIVE MODE; }
step "commit1" { COMMIT; }
session "s2"
step "grant" { GRANT SELECT ON t TO r1; }
session "s3"
step "select" { SELECT count(*) FROM t; }
permutation "aslock"
"begin1" "lock_as" "grant" "select" "commit1"
permutation "suelock"
"begin1" "lock_sue" "grant" "select" "commit1"
