From M.Mamin@intershop.de Wed Jun 3 19:57:26 2026 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-- From bruce@momjian.us Wed Jun 3 19:57:26 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A52BA2E0321 for ; Thu, 6 Mar 2008 14:50:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 94156-09 for ; Thu, 6 Mar 2008 14:50:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from momjian.us (momjian.us [70.90.9.53]) by postgresql.org (Postfix) with ESMTP id 3A7392E02B0 for ; Thu, 6 Mar 2008 14:50:50 -0400 (AST) Received: (from bruce@localhost) by momjian.us (8.11.6/8.11.6) id m26Ioaw26516; Thu, 6 Mar 2008 13:50:36 -0500 (EST) From: Bruce Momjian Message-Id: <200803061850.m26Ioaw26516@momjian.us> Subject: Re: [GENERAL] advisory locks: documentation hint and feature request In-Reply-To: To: Marc Mamin Date: Thu, 6 Mar 2008 13:50:36 -0500 (EST) CC: PostgreSQL-documentation X-Mailer: ELM [version 2.4ME+ PL124 (25)] MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="ELM1204829436-8024-2_" Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/34 X-Sequence-Number: 4846 --ELM1204829436-8024-2_ Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="US-ASCII" Marc Mamin wrote: > 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" > I have added your suggested changes to the docs with the attached patch. Thanks. > 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) Sorry, I am afraid they would not have general-enough usefulness. -- Bruce Momjian http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + --ELM1204829436-8024-2_ Content-Transfer-Encoding: 7bit Content-Type: text/x-diff Content-Disposition: inline; filename="/rtmp/diff" Index: doc/src/sgml/catalogs.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v retrieving revision 2.161 diff -c -c -r2.161 catalogs.sgml *** doc/src/sgml/catalogs.sgml 31 Jan 2008 18:40:02 -0000 2.161 --- doc/src/sgml/catalogs.sgml 6 Mar 2008 18:48:54 -0000 *************** *** 5691,5697 **** any OID column OID of the object within its system catalog, or NULL if the ! object is not a general database object --- 5691,5700 ---- any OID column OID of the object within its system catalog, or NULL if the ! object is not a general database object. ! For advisory locks it is used to distinguish the two key ! spaces (1 for an int8 key, 2 for two ! int4 keys). Index: doc/src/sgml/func.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.422 diff -c -c -r1.422 func.sgml *** doc/src/sgml/func.sgml 3 Mar 2008 18:09:02 -0000 1.422 --- doc/src/sgml/func.sgml 6 Mar 2008 18:48:58 -0000 *************** *** 12378,12384 **** pg_advisory_lock locks an application-defined resource, which can be identified either by a single 64-bit key value or two ! 32-bit key values (note that these two key spaces do not overlap). If another session already holds a lock on the same resource, the function will wait until the resource becomes available. The lock is exclusive. Multiple lock requests stack, so that if the same resource --- 12378,12385 ---- pg_advisory_lock locks an application-defined resource, which can be identified either by a single 64-bit key value or two ! 32-bit key values (note that these two key spaces do not overlap). ! The key type is specified in pg_locks.objid. If another session already holds a lock on the same resource, the function will wait until the resource becomes available. The lock is exclusive. Multiple lock requests stack, so that if the same resource --ELM1204829436-8024-2_--