Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id B3F112E3174 for ; Mon, 7 Jan 2008 07:32:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 70963-02 for ; Mon, 7 Jan 2008 07:28:24 -0400 (AST) Received: from mailgate.intershop.de (mailgate.intershop.de [217.17.202.241]) by postgresql.org (Postfix) with ESMTP id 6D7BD2E2DF8 for ; Mon, 7 Jan 2008 07:28:27 -0400 (AST) Received: from JENMAIL01.ad.intershop.net ([10.0.87.43]) by mailgate.intershop.de (8.14.1/8.13.6) with ESMTP id m07BSMqQ022933 for ; Mon, 7 Jan 2008 12:28:23 +0100 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C85120.69550295" Subject: advisory locks: documentation hint and feature request Date: Mon, 7 Jan 2008 12:28:22 +0100 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: advisory locks: documentation hint and feature request Thread-Index: AchRIGlMEjxidp+oRACo2EM0IpsgaQ== From: "Marc Mamin" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/190 X-Sequence-Number: 126400 This is a multi-part message in MIME format. ------_=_NextPart_001_01C85120.69550295 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello, =20 I have defined some functions to manage advisory locks in a more restrictive way than the default usage. =20 here my requirements: =20 - a session cannot stack write locks=20 - 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. =20 =20 doing this, I missed an information in the documentation that would be useful to add: =20 =20 pg_locks columns definition --------------------------- =20 current definition: =20 objid : OID of the object within its system catalog,=20 or NULL if the object is not a general database object =20 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) =20 to add: "; The two key spaces are separated on the column objid in the pg_locks view" =20 =20 =20 =20 Moreover I missed some function that would allow a cleaner implementation of my features=20 (reducing the need to query the pg_locks view) =20 For example: =20 --current shared lock count (owned / all) pg_advisory_lock_shared_count_my(key bigint) pg_advisory_lock_shared_count_all(key bigint) =20 =20 --enforce exclusive locks so that they cannot be stacked pg_try_advisory_lock_single(key bigint) =20 --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 (...) =20 --and possibly define a max number of allowed shared locks (over all sessions) pg_[try_]advisory_lock_shared_max(key bigint, max_count int) =20 =20 regards, =20 Marc Mamin =20 =20 =20 =20 ------_=_NextPart_001_01C85120.69550295 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable advisory locks: documentation hint and feature request

 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
 
 
 
 

------_=_NextPart_001_01C85120.69550295--