Hello,
I have defined some functions to manage advisory locks in a more restrictive way than the default usage.
here my requirements:
- a session cannot stack write locks
- a session cannot aquire a shared lock if it already holds the write locks
- there is a maximum allowed shared locks (per lock id).
As I coun't find a way to count all current share locks for a given key
I also added the restriction that a given session can not get more than one instance of a given shared lock.
This is a "strange" restriction for share locks, but this fits to our lock usage.
doing this, I missed an information in the documentation that would be useful to add:
pg_locks columns definition
---------------------------
current definition:
objid : OID of the object within its system catalog,
or NULL if the object is not a general database object
to add: "For advisory locks it is used to distinguish the two key spaces (one int8 or two int4 keys)
Table 9-56. Advisory Lock Functions (8.3 Beta)
-----------------------------------
current: (note that these two key spaces do not overlap)
to add: "; The two key spaces are separated on the column objid in the pg_locks view"
Moreover I missed some function that would allow a cleaner implementation of my features
(reducing the need to query the pg_locks view)
For example:
--current shared lock count (owned / all)
pg_advisory_lock_shared_count_my(key bigint)
pg_advisory_lock_shared_count_all(key bigint)
--enforce exclusive locks so that they cannot be stacked
pg_try_advisory_lock_single(key bigint)
--enforce shared locks so that they cannot be acquired if the current session already holds the exclusive lock
I guess this should be the dafault behaviour for
pg_advisory_lock_shared (..) and pg_try_advisory_lock_shared (...)
--and possibly define a max number of allowed shared locks (over all sessions)
pg_[try_]advisory_lock_shared_max(key bigint, max_count int)
regards,
Marc Mamin