Transaction-scope advisory locks
Started by Marko Tiikkajaabout 15 years ago36 messages
Hi,
I often find myself wanting advisory locks that are automatically
released when the transaction ends, so here's a small patch trying to do
just that. I don't know much about the lock system so the patch is in
the state "it looks like this would work". Any comments on the
technical details are welcome. There's obviously a lot of documentation
and READMEs to change too, but I thought I'd see what people think about
the idea before going there.
So, thoughts?
Regards,
Marko Tiikkaja
Attachments:
advisory.patchtext/plain; charset=iso-8859-1; name=advisory.patch; x-mac-creator=0; x-mac-type=0Download
*** a/src/backend/storage/lmgr/lock.c
--- b/src/backend/storage/lmgr/lock.c
***************
*** 130,136 **** static const LockMethodData default_lockmethod = {
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! false,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
--- 130,136 ----
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! true,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
*** a/src/backend/storage/lmgr/proc.c
--- b/src/backend/storage/lmgr/proc.c
***************
*** 629,636 **** LockWaitCancel(void)
* At subtransaction abort, we release all locks held by the subtransaction;
* this is implemented by retail releasing of the locks under control of
* the ResourceOwner mechanism.
- *
- * Note that user locks are not released in any case.
*/
void
ProcReleaseLocks(bool isCommit)
--- 629,634 ----
***************
*** 641,646 **** ProcReleaseLocks(bool isCommit)
--- 639,645 ----
LockWaitCancel();
/* Release locks */
LockReleaseAll(DEFAULT_LOCKMETHOD, !isCommit);
+ LockReleaseAll(USER_LOCKMETHOD, false);
}
*** a/src/backend/utils/adt/lockfuncs.c
--- b/src/backend/utils/adt/lockfuncs.c
***************
*** 333,343 **** Datum
pg_advisory_lock_int8(PG_FUNCTION_ARGS)
{
int64 key = PG_GETARG_INT64(0);
LOCKTAG tag;
SET_LOCKTAG_INT64(tag, key);
! (void) LockAcquire(&tag, ExclusiveLock, true, false);
PG_RETURN_VOID();
}
--- 333,344 ----
pg_advisory_lock_int8(PG_FUNCTION_ARGS)
{
int64 key = PG_GETARG_INT64(0);
+ bool session = PG_GETARG_BOOL(1);
LOCKTAG tag;
SET_LOCKTAG_INT64(tag, key);
! (void) LockAcquire(&tag, ExclusiveLock, session, false);
PG_RETURN_VOID();
}
***************
*** 349,359 **** Datum
pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
{
int64 key = PG_GETARG_INT64(0);
LOCKTAG tag;
SET_LOCKTAG_INT64(tag, key);
! (void) LockAcquire(&tag, ShareLock, true, false);
PG_RETURN_VOID();
}
--- 350,361 ----
pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
{
int64 key = PG_GETARG_INT64(0);
+ bool session = PG_GETARG_BOOL(1);
LOCKTAG tag;
SET_LOCKTAG_INT64(tag, key);
! (void) LockAcquire(&tag, ShareLock, session, false);
PG_RETURN_VOID();
}
***************
*** 367,378 **** Datum
pg_try_advisory_lock_int8(PG_FUNCTION_ARGS)
{
int64 key = PG_GETARG_INT64(0);
LOCKTAG tag;
LockAcquireResult res;
SET_LOCKTAG_INT64(tag, key);
! res = LockAcquire(&tag, ExclusiveLock, true, true);
PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
}
--- 369,381 ----
pg_try_advisory_lock_int8(PG_FUNCTION_ARGS)
{
int64 key = PG_GETARG_INT64(0);
+ bool session = PG_GETARG_BOOL(1);
LOCKTAG tag;
LockAcquireResult res;
SET_LOCKTAG_INT64(tag, key);
! res = LockAcquire(&tag, ExclusiveLock, session, true);
PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
}
***************
*** 386,397 **** Datum
pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
{
int64 key = PG_GETARG_INT64(0);
LOCKTAG tag;
LockAcquireResult res;
SET_LOCKTAG_INT64(tag, key);
! res = LockAcquire(&tag, ShareLock, true, true);
PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
}
--- 389,401 ----
pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
{
int64 key = PG_GETARG_INT64(0);
+ bool session = PG_GETARG_BOOL(1);
LOCKTAG tag;
LockAcquireResult res;
SET_LOCKTAG_INT64(tag, key);
! res = LockAcquire(&tag, ShareLock, session, true);
PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
}
***************
*** 442,452 **** pg_advisory_lock_int4(PG_FUNCTION_ARGS)
{
int32 key1 = PG_GETARG_INT32(0);
int32 key2 = PG_GETARG_INT32(1);
LOCKTAG tag;
SET_LOCKTAG_INT32(tag, key1, key2);
! (void) LockAcquire(&tag, ExclusiveLock, true, false);
PG_RETURN_VOID();
}
--- 446,457 ----
{
int32 key1 = PG_GETARG_INT32(0);
int32 key2 = PG_GETARG_INT32(1);
+ bool session = PG_GETARG_BOOL(2);
LOCKTAG tag;
SET_LOCKTAG_INT32(tag, key1, key2);
! (void) LockAcquire(&tag, ExclusiveLock, session, false);
PG_RETURN_VOID();
}
***************
*** 459,469 **** pg_advisory_lock_shared_int4(PG_FUNCTION_ARGS)
{
int32 key1 = PG_GETARG_INT32(0);
int32 key2 = PG_GETARG_INT32(1);
LOCKTAG tag;
SET_LOCKTAG_INT32(tag, key1, key2);
! (void) LockAcquire(&tag, ShareLock, true, false);
PG_RETURN_VOID();
}
--- 464,475 ----
{
int32 key1 = PG_GETARG_INT32(0);
int32 key2 = PG_GETARG_INT32(1);
+ bool session = PG_GETARG_BOOL(2);
LOCKTAG tag;
SET_LOCKTAG_INT32(tag, key1, key2);
! (void) LockAcquire(&tag, ShareLock, session, false);
PG_RETURN_VOID();
}
***************
*** 478,489 **** pg_try_advisory_lock_int4(PG_FUNCTION_ARGS)
{
int32 key1 = PG_GETARG_INT32(0);
int32 key2 = PG_GETARG_INT32(1);
LOCKTAG tag;
LockAcquireResult res;
SET_LOCKTAG_INT32(tag, key1, key2);
! res = LockAcquire(&tag, ExclusiveLock, true, true);
PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
}
--- 484,496 ----
{
int32 key1 = PG_GETARG_INT32(0);
int32 key2 = PG_GETARG_INT32(1);
+ bool session = PG_GETARG_BOOL(2);
LOCKTAG tag;
LockAcquireResult res;
SET_LOCKTAG_INT32(tag, key1, key2);
! res = LockAcquire(&tag, ExclusiveLock, session, true);
PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
}
***************
*** 498,509 **** pg_try_advisory_lock_shared_int4(PG_FUNCTION_ARGS)
{
int32 key1 = PG_GETARG_INT32(0);
int32 key2 = PG_GETARG_INT32(1);
LOCKTAG tag;
LockAcquireResult res;
SET_LOCKTAG_INT32(tag, key1, key2);
! res = LockAcquire(&tag, ShareLock, true, true);
PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
}
--- 505,517 ----
{
int32 key1 = PG_GETARG_INT32(0);
int32 key2 = PG_GETARG_INT32(1);
+ bool session = PG_GETARG_BOOL(2);
LOCKTAG tag;
LockAcquireResult res;
SET_LOCKTAG_INT32(tag, key1, key2);
! res = LockAcquire(&tag, ShareLock, session, true);
PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
}
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4373,4397 **** DATA(insert OID = 2749 ( arraycontained PGNSP PGUID 12 1 0 0 f f f t f i 2 0
DESCR("is contained by");
/* userlock replacements */
! DATA(insert OID = 2880 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
! DATA(insert OID = 2881 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
! DATA(insert OID = 2882 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
! DATA(insert OID = 2883 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2884 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_int8 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2885 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int8 _null_ _null_ _null_ ));
DESCR("release shared advisory lock");
! DATA(insert OID = 2886 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
! DATA(insert OID = 2887 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
! DATA(insert OID = 2888 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
! DATA(insert OID = 2889 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2890 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_int4 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
--- 4373,4413 ----
DESCR("is contained by");
/* userlock replacements */
! DATA(insert OID = 2880 ( pg_advisory_lock PGNSP PGUID 14 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ "select pg_advisory_lock($1, true)" _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
! DATA(insert OID = 3065 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "20 16" _null_ _null_ _null_ _null_ pg_advisory_lock_int8 _null_ _null_ _null_ ));
! DESCR("obtain exclusive advisory lock");
! DATA(insert OID = 2881 ( pg_advisory_lock_shared PGNSP PGUID 14 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ "select pg_advisory_lock_shared($1, true)" _null_ _null_ _null_ ));
! DESCR("obtain shared advisory lock");
! DATA(insert OID = 3066 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "20 16" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
! DATA(insert OID = 2882 ( pg_try_advisory_lock PGNSP PGUID 14 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ "select pg_try_advisory_lock($1, true)" _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
! DATA(insert OID = 3067 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "20 16" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int8 _null_ _null_ _null_ ));
! DESCR("obtain exclusive advisory lock if available");
! DATA(insert OID = 2883 ( pg_try_advisory_lock_shared PGNSP PGUID 14 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ "select pg_try_advisory_lock_shared($1, true)" _null_ _null_ _null_ ));
! DESCR("obtain shared advisory lock if available");
! DATA(insert OID = 3068 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "20 16" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2884 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_int8 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2885 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int8 _null_ _null_ _null_ ));
DESCR("release shared advisory lock");
! DATA(insert OID = 2886 ( pg_advisory_lock PGNSP PGUID 14 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ "select pg_advisory_lock($1, $2, true)" _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
! DATA(insert OID = 3069 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 3 0 2278 "23 23 16" _null_ _null_ _null_ _null_ pg_advisory_lock_int4 _null_ _null_ _null_ ));
! DESCR("obtain exclusive advisory lock");
! DATA(insert OID = 2887 ( pg_advisory_lock_shared PGNSP PGUID 14 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ "select pg_advisory_lock_shared($1, $2, true)" _null_ _null_ _null_ ));
! DESCR("obtain shared advisory lock");
! DATA(insert OID = 3070 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 3 0 2278 "23 23 16" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
! DATA(insert OID = 2888 ( pg_try_advisory_lock PGNSP PGUID 14 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ "select pg_try_advisory_lock($1, $2, true)" _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
! DATA(insert OID = 3071 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 3 0 16 "23 23 16" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int4 _null_ _null_ _null_ ));
! DESCR("obtain exclusive advisory lock if available");
! DATA(insert OID = 2889 ( pg_try_advisory_lock_shared PGNSP PGUID 14 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ "select pg_try_advisory_lock_shared($1, $2, true)" _null_ _null_ _null_ ));
! DESCR("obtain shared advisory lock if available");
! DATA(insert OID = 3072 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 3 0 16 "23 23 16" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2890 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_int4 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
Re: Transaction-scope advisory locks
On 13 December 2010 23:52, Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi>wrote:
Hi,
I often find myself wanting advisory locks that are automatically released
when the transaction ends, so here's a small patch trying to do just that.
I don't know much about the lock system so the patch is in the state "it
looks like this would work". Any comments on the technical details are
welcome. There's obviously a lot of documentation and READMEs to change
too, but I thought I'd see what people think about the idea before going
there.So, thoughts?
In my opinion changing current behavior is not a good idea. I know some
software that relies on current behavior and this would break it. Maybe add
that as an option, or add another type of advisory lock?
regards
Szymon
Re: Transaction-scope advisory locks
On 2010-12-14 1:08 AM +0200, Szymon Guz wrote:
On 13 December 2010 23:52, Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi>wrote:
So, thoughts?
In my opinion changing current behavior is not a good idea. I know some
software that relies on current behavior and this would break it. Maybe add
that as an option, or add another type of advisory lock?
Oh, I forgot to mention. The patch doesn't change any existing
behaviour; the new behaviour can be invoked only by adding a new boolean
argument:
SELECT pg_advisory_lock(1, false);
The lock space is the same though, but I don't feel strongly about it.
Regards,
Marko Tiikkaja
Re: Transaction-scope advisory locks
On Tue, 2010-12-14 at 01:14 +0200, Marko Tiikkaja wrote:
On 2010-12-14 1:08 AM +0200, Szymon Guz wrote:
On 13 December 2010 23:52, Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi>wrote:
So, thoughts?
In my opinion changing current behavior is not a good idea. I know some
software that relies on current behavior and this would break it. Maybe add
that as an option, or add another type of advisory lock?Oh, I forgot to mention. The patch doesn't change any existing
behaviour; the new behaviour can be invoked only by adding a new boolean
argument:SELECT pg_advisory_lock(1, false);
Don't like adding a boolean. Nobody remembers what it is for and we have
bugs. How about pg_advisory_xact_lock()
The lock space is the same though, but I don't feel strongly about it.
Same lock space is good. Easy to separate if required.
Explicitly nameable lock spaces would be even better, since if multiple
applications use them you get strange and unmanageable contention.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
Re: Transaction-scope advisory locks
On 12/13/2010 07:35 PM, Simon Riggs wrote:
Same lock space is good. Easy to separate if required.
Explicitly nameable lock spaces would be even better, since if multiple
applications use them you get strange and unmanageable contention.
Yeah. I have a table of lock names for different locks, and do stuff like:
perform pg_advisory_lock(l.lockid, some_value)
from my_advisory_locks l
where l.lockname = 'my_lock_name';
I don't know that we need a separately nameable lockspace for
transaction-scoped locks, though, do we?
cheers
andrew
Re: Transaction-scope advisory locks
On 2010-12-14 2:35 AM +0200, Simon Riggs wrote:
On Tue, 2010-12-14 at 01:14 +0200, Marko Tiikkaja wrote:
Oh, I forgot to mention. The patch doesn't change any existing
behaviour; the new behaviour can be invoked only by adding a new boolean
argument:SELECT pg_advisory_lock(1, false);
Don't like adding a boolean. Nobody remembers what it is for and we have
bugs. How about pg_advisory_xact_lock()
That's the other option I was thinking of, but didn't like that too
much. But you're right about the boolean, it is a bit hard to remember
which behaviour is which.
The lock space is the same though, but I don't feel strongly about it.
Same lock space is good. Easy to separate if required.
Explicitly nameable lock spaces would be even better, since if multiple
applications use them you get strange and unmanageable contention.
I think something like this has been suggested in the past, and was
rejected at that time.
Regards,
Marko Tiikkaja
Re: Transaction-scope advisory locks
Oh, I forgot to mention. The patch doesn't change any existing
behaviour; the new behaviour can be invoked only by adding a new boolean
argument:SELECT pg_advisory_lock(1, false);
The lock space is the same though, but I don't feel strongly about it.
I could use this, and I think a lot more people would use advisory locks
with it. Put it in the next CF and remind me to test it.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Re: Transaction-scope advisory locks
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
On 2010-12-14 1:08 AM +0200, Szymon Guz wrote:
In my opinion changing current behavior is not a good idea. I know some
software that relies on current behavior and this would break it. Maybe add
that as an option, or add another type of advisory lock?
Oh, I forgot to mention. The patch doesn't change any existing
behaviour; the new behaviour can be invoked only by adding a new boolean
argument:
Uh, I don't think so. It sure looks like you have changed the user
lockmethod to be transactional, ie, auto-release on commit/abort. As
Szymon stated, that is an utter non-starter, because all current uses of
advisory locks consider the current behavior to be a feature not a bug.
regards, tom lane
Re: Transaction-scope advisory locks
On 2010-12-14 4:23 AM +0200, Tom Lane wrote:
Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi> writes:
On 2010-12-14 1:08 AM +0200, Szymon Guz wrote:
In my opinion changing current behavior is not a good idea. I know some
software that relies on current behavior and this would break it. Maybe add
that as an option, or add another type of advisory lock?Oh, I forgot to mention. The patch doesn't change any existing
behaviour; the new behaviour can be invoked only by adding a new boolean
argument:Uh, I don't think so. It sure looks like you have changed the user
lockmethod to be transactional, ie, auto-release on commit/abort.
I was under the impression that passing sessionLock=true to
LockAcquire(), combined with allLocks=false to LockReleaseAll() would be
enough to prevent that from happening. My tests seem to agree with this.
Am I missing something?
Regards,
Marko Tiikkaja
Re: Transaction-scope advisory locks
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
I often find myself wanting advisory locks that are automatically released
when the transaction ends, so here's a small patch trying to do just that.
Excellent idea, I sure need that (been doing some pl stuff to track
locks granted then unlock them, transaction scope would mean pure SQL
function work). Thanks! :)
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Re: Transaction-scope advisory locks
On Tuesday 14 December 2010 00:14:22 Marko Tiikkaja wrote:
The lock space is the same though, but I don't feel strongly about it.
I feel strongly that it needs the same locking space. I pretty frequently have
the need for multiple clients trying to acquiring a lock in transaction scope
(i.e. for accessing the cache) and one/few acquiring it in session scope (for
building the cache).
Andres
Re: Transaction-scope advisory locks
On Tue, Dec 14, 2010 at 7:07 AM, Andres Freund <andres@anarazel.de> wrote:
On Tuesday 14 December 2010 00:14:22 Marko Tiikkaja wrote:
The lock space is the same though, but I don't feel strongly about it.
I feel strongly that it needs the same locking space. I pretty frequently have
the need for multiple clients trying to acquiring a lock in transaction scope
(i.e. for accessing the cache) and one/few acquiring it in session scope (for
building the cache).
Not that I'm necessarily against the proposal, but what does this do
that can't already be done by locking a table or a table's row?
merlin
Re: Transaction-scope advisory locks
On 2010-12-14 4:19 PM +0200, Merlin Moncure wrote:
On Tue, Dec 14, 2010 at 7:07 AM, Andres Freund<andres@anarazel.de> wrote:
On Tuesday 14 December 2010 00:14:22 Marko Tiikkaja wrote:
The lock space is the same though, but I don't feel strongly about it.
I feel strongly that it needs the same locking space. I pretty frequently have
the need for multiple clients trying to acquiring a lock in transaction scope
(i.e. for accessing the cache) and one/few acquiring it in session scope (for
building the cache).Not that I'm necessarily against the proposal, but what does this do
that can't already be done by locking a table or a table's row?
Try without throwing an error.
Regards,
Marko Tiikkaja
Re: Transaction-scope advisory locks
On Tuesday 14 December 2010 15:19:32 Merlin Moncure wrote:
On Tue, Dec 14, 2010 at 7:07 AM, Andres Freund <andres@anarazel.de> wrote:
On Tuesday 14 December 2010 00:14:22 Marko Tiikkaja wrote:
The lock space is the same though, but I don't feel strongly about it.
I feel strongly that it needs the same locking space. I pretty frequently
have the need for multiple clients trying to acquiring a lock in
transaction scope (i.e. for accessing the cache) and one/few acquiring
it in session scope (for building the cache).Not that I'm necessarily against the proposal, but what does this do
that can't already be done by locking a table or a table's row?
1. trylock without raising errors (the other possibility is nowait, but that
doesnt work very well as it ERRORs).
2. mixing session and transaction scope (I would like to have that e.g. for
materialized views. The writers uses session scope and the readers use
transaction scope. Its not that easy to make code ERROR/exception safe when
you only control some view or such. In contrast the computationally expensive
part of computing the materialized view should be way much more easy to do
sensibly in session scope).
3. nonlocking dequeuing of a table-based queue can e.g. be done with advisory
locks but not with row level locks.
Andres
Re: Transaction-scope advisory locks
Merlin Moncure <mmoncure@gmail.com> writes:
Not that I'm necessarily against the proposal, but what does this do
that can't already be done by locking a table or a table's row?
I agree with Andres' point about this: sometimes it'd be more convenient
for an advisory lock to be released automatically at transaction end.
If you have a mix of clients that want that behavior with others that
want a persistent hold on the same locks, you can't do it with regular
locks.
regards, tom lane
Re: Transaction-scope advisory locks
On 12/14/2010 09:51 AM, Tom Lane wrote:
Merlin Moncure<mmoncure@gmail.com> writes:
Not that I'm necessarily against the proposal, but what does this do
that can't already be done by locking a table or a table's row?I agree with Andres' point about this: sometimes it'd be more convenient
for an advisory lock to be released automatically at transaction end.
If you have a mix of clients that want that behavior with others that
want a persistent hold on the same locks, you can't do it with regular
locks.
Right. And that's why they need to be in the same lockspace.
cheers
andrew
Re: Transaction-scope advisory locks
On Tue, Dec 14, 2010 at 9:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Merlin Moncure <mmoncure@gmail.com> writes:
Not that I'm necessarily against the proposal, but what does this do
that can't already be done by locking a table or a table's row?I agree with Andres' point about this: sometimes it'd be more convenient
for an advisory lock to be released automatically at transaction end.
If you have a mix of clients that want that behavior with others that
want a persistent hold on the same locks, you can't do it with regular
locks.
right, plus 4:
automatic lock release on error. right now if I'm grabbing
in-transaction lock inside a function, I have to put in sub
transaction handler to guarantee release if anything non trivial
happens mid lock.
merlin
Re: Transaction-scope advisory locks
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
On 2010-12-14 4:23 AM +0200, Tom Lane wrote:
Uh, I don't think so. It sure looks like you have changed the user
lockmethod to be transactional, ie, auto-release on commit/abort.
I was under the impression that passing sessionLock=true to
LockAcquire(), combined with allLocks=false to LockReleaseAll() would be
enough to prevent that from happening. My tests seem to agree with this.
Am I missing something?
All the places that look at LockMethodData->transactional ?
regards, tom lane
Re: Transaction-scope advisory locks
On 2010-12-14 7:05 PM +0200, Tom Lane wrote:
Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi> writes:
On 2010-12-14 4:23 AM +0200, Tom Lane wrote:
Uh, I don't think so. It sure looks like you have changed the user
lockmethod to be transactional, ie, auto-release on commit/abort.I was under the impression that passing sessionLock=true to
LockAcquire(), combined with allLocks=false to LockReleaseAll() would be
enough to prevent that from happening. My tests seem to agree with this.Am I missing something?
All the places that look at LockMethodData->transactional ?
As far as I can tell, every code path that looks at
LockMethodData->transactional either has an explicit sessionLock boolean
or looks whether owner == NULL to actually check whether the lock in
question is a session lock or not instead of blindly trusting
->transactional.
Regards,
Marko Tiikkaja
Re: Transaction-scope advisory locks
On 2010-12-14 12:52 AM +0200, Marko Tiikkaja wrote:
<patch>
Here's the latest version of the patch. It now uses the API proposed by
Simon, but still lacks documentation changes, which I'm going to send
tomorrow.
Regards,
Marko Tiikkaja
Attachments:
advisory.patchtext/plain; charset=iso-8859-1; name=advisory.patch; x-mac-creator=0; x-mac-type=0Download
*** a/src/backend/storage/lmgr/lock.c
--- b/src/backend/storage/lmgr/lock.c
***************
*** 130,136 **** static const LockMethodData default_lockmethod = {
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! false,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
--- 130,136 ----
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! true,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
*** a/src/backend/storage/lmgr/proc.c
--- b/src/backend/storage/lmgr/proc.c
***************
*** 629,636 **** LockWaitCancel(void)
* At subtransaction abort, we release all locks held by the subtransaction;
* this is implemented by retail releasing of the locks under control of
* the ResourceOwner mechanism.
- *
- * Note that user locks are not released in any case.
*/
void
ProcReleaseLocks(bool isCommit)
--- 629,634 ----
***************
*** 641,646 **** ProcReleaseLocks(bool isCommit)
--- 639,645 ----
LockWaitCancel();
/* Release locks */
LockReleaseAll(DEFAULT_LOCKMETHOD, !isCommit);
+ LockReleaseAll(USER_LOCKMETHOD, false);
}
*** a/src/backend/utils/adt/lockfuncs.c
--- b/src/backend/utils/adt/lockfuncs.c
***************
*** 343,348 **** pg_advisory_lock_int8(PG_FUNCTION_ARGS)
--- 343,365 ----
}
/*
+ * pg_advisory_xact_lock(int8) - acquire xact scoped
+ * exclusive lock on an int8 key
+ */
+ Datum
+ pg_advisory_xact_lock_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ (void) LockAcquire(&tag, ExclusiveLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_advisory_lock_shared(int8) - acquire share lock on an int8 key
*/
Datum
***************
*** 359,364 **** pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
--- 376,398 ----
}
/*
+ * pg_advisory_xact_lock_shared(int8) - acquire xact scoped
+ * share lock on an int8 key
+ */
+ Datum
+ pg_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ (void) LockAcquire(&tag, ShareLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_try_advisory_lock(int8) - acquire exclusive lock on an int8 key, no wait
*
* Returns true if successful, false if lock not available
***************
*** 378,383 **** pg_try_advisory_lock_int8(PG_FUNCTION_ARGS)
--- 412,437 ----
}
/*
+ * pg_try_advisory_xact_lock(int8) - acquire xact scoped
+ * exclusive lock on an int8 key, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ res = LockAcquire(&tag, ExclusiveLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_try_advisory_lock_shared(int8) - acquire share lock on an int8 key, no wait
*
* Returns true if successful, false if lock not available
***************
*** 397,402 **** pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
--- 451,476 ----
}
/*
+ * pg_try_advisory_xact_lock_shared(int8) - acquire xact scoped
+ * share lock on an int8 key, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ res = LockAcquire(&tag, ShareLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_advisory_unlock(int8) - release exclusive lock on an int8 key
*
* Returns true if successful, false if lock was not held
***************
*** 452,457 **** pg_advisory_lock_int4(PG_FUNCTION_ARGS)
--- 526,549 ----
}
/*
+ * pg_advisory_xact_lock(int4, int4) - acquire xact scoped
+ * exclusive lock on 2 int4 keys
+ */
+ Datum
+ pg_advisory_xact_lock_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ (void) LockAcquire(&tag, ExclusiveLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_advisory_lock_shared(int4, int4) - acquire share lock on 2 int4 keys
*/
Datum
***************
*** 469,474 **** pg_advisory_lock_shared_int4(PG_FUNCTION_ARGS)
--- 561,584 ----
}
/*
+ * pg_advisory_xact_lock_shared(int4, int4) - acquire xact scoped
+ * share lock on 2 int4 keys
+ */
+ Datum
+ pg_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ (void) LockAcquire(&tag, ShareLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_try_advisory_lock(int4, int4) - acquire exclusive lock on 2 int4 keys, no wait
*
* Returns true if successful, false if lock not available
***************
*** 489,494 **** pg_try_advisory_lock_int4(PG_FUNCTION_ARGS)
--- 599,625 ----
}
/*
+ * pg_try_advisory_xact_lock(int4, int4) - acquire xact scoped
+ * exclusive lock on 2 int4 keys, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ res = LockAcquire(&tag, ExclusiveLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_try_advisory_lock_shared(int4, int4) - acquire share lock on 2 int4 keys, no wait
*
* Returns true if successful, false if lock not available
***************
*** 509,514 **** pg_try_advisory_lock_shared_int4(PG_FUNCTION_ARGS)
--- 640,666 ----
}
/*
+ * pg_try_advisory_xact_lock_shared(int4, int4) - acquire xact scoped
+ * share lock on 2 int4 keys, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ res = LockAcquire(&tag, ShareLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_advisory_unlock(int4, int4) - release exclusive lock on 2 int4 keys
*
* Returns true if successful, false if lock was not held
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4404,4428 **** DESCR("is contained by");
--- 4404,4444 ----
/* userlock replacements */
DATA(insert OID = 2880 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_int8 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive a4dvisory lock");
+ DATA(insert OID = 3071 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2881 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
+ DATA(insert OID = 3072 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock");
DATA(insert OID = 2882 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
+ DATA(insert OID = 3073 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int8 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2883 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
+ DATA(insert OID = 3074 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2884 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_int8 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2885 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int8 _null_ _null_ _null_ ));
DESCR("release shared advisory lock");
DATA(insert OID = 2886 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
+ DATA(insert OID = 3075 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2887 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
+ DATA(insert OID = 3076 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock");
DATA(insert OID = 2888 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
+ DATA(insert OID = 3077 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2889 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
+ DATA(insert OID = 3079 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2890 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_int4 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2891 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int4 _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 991,1005 **** extern Datum show_all_settings(PG_FUNCTION_ARGS);
--- 991,1013 ----
/* lockfuncs.c */
extern Datum pg_lock_status(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_int8(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_shared_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_int4(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_shared_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_all(PG_FUNCTION_ARGS);
Re: Transaction-scope advisory locks
On Sun, Jan 16, 2011 at 06:20, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
Here's the latest version of the patch. It now uses the API proposed by
Simon, but still lacks documentation changes, which I'm going to send
tomorrow.
Here is a short review for Transaction scoped advisory locks:
https://commitfest.postgresql.org/action/patch_view?id=518
== Features ==
The patch adds pg_[try_]advisory_xact_lock[_shared] functions.
The function names follows the past discussion -- it's better than
"bool isXact" argument or changing the existing behavior.
== Coding ==
The patch itself is well-formed and be applied cleanly.
I expect documentation will come soon.
There is no regression test, but we have no regression test for
advisory locks even now. Tests for lock conflict might be difficult,
but we could have single-threaded test for lock/unlock and pg_locks view.
== Questions ==
I have a question about unlocking transaction-scope advisory locks.
We cannot unlock them with pg_advisory_unlock(), but can unlock with
pg_advisory_unlock_all(). It's inconsistent behavior.
Furthermore, I wonder we can allow unlocking transaction-scope locks
-- we have LOCK TABLE but don't have UNLOCK TABLE.
postgres=# BEGIN;
BEGIN
postgres=# SELECT pg_advisory_xact_lock(1);
pg_advisory_xact_lock
-----------------------
(1 row)
postgres=# SELECT pg_advisory_unlock(1);
WARNING: you don't own a lock of type ExclusiveLock
pg_advisory_unlock
--------------------
f
(1 row)
postgres=# SELECT pg_advisory_unlock_all();
pg_advisory_unlock_all
------------------------
(1 row)
postgres=# ROLLBACK;
ROLLBACK
--
Itagaki Takahiro
Re: Transaction-scope advisory locks
On 2011-01-17 9:28 AM +0200, Itagaki Takahiro wrote:
Here is a short review for Transaction scoped advisory locks:
https://commitfest.postgresql.org/action/patch_view?id=518
Thanks for reviewing!
== Features ==
The patch adds pg_[try_]advisory_xact_lock[_shared] functions.
The function names follows the past discussion -- it's better than
"bool isXact" argument or changing the existing behavior.== Coding ==
I expect documentation will come soon.
I'm sorry about this, I have been occupied with other stuff. I'm going
to work on this tonight.
There is no regression test, but we have no regression test for
advisory locks even now. Tests for lock conflict might be difficult,
but we could have single-threaded test for lock/unlock and pg_locks view.
Seems useful.
== Questions ==
I have a question about unlocking transaction-scope advisory locks.
We cannot unlock them with pg_advisory_unlock(), but can unlock with
pg_advisory_unlock_all(). It's inconsistent behavior.
Furthermore, I wonder we can allow unlocking transaction-scope locks
-- we have LOCK TABLE but don't have UNLOCK TABLE.
I guess we could add new pg_advisory_txn_unlock() functions to unlock
transaction-scope locks, but I do share your doubt on whether or not we
want to allow this at all. On the other hand, the reasons why we don't
allow non-advisory locks to be unreleased is a lot more clear than the
issue at hand. I have no strong opinion on this.
Another thing I now see is this:
BEGIN;
SELECT pg_advisory_xact_lock(1);
-- do something here
-- upgrade to session lock
SELECT pg_advisory_lock(1);
COMMIT;
This seems useful, since the xact lock would be automatically released
if an error happens during "-- do something here" so you wouldn't need
to worry about releasing the lock elsewhere. But I'm not sure this is
safe. Can anyone see a problem with it?
Regards,
Marko Tiikkaja
Re: Transaction-scope advisory locks
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
Another thing I now see is this:
BEGIN;
SELECT pg_advisory_xact_lock(1);
-- do something here
-- upgrade to session lock
SELECT pg_advisory_lock(1);
COMMIT;
This seems useful, since the xact lock would be automatically released
if an error happens during "-- do something here" so you wouldn't need
to worry about releasing the lock elsewhere. But I'm not sure this is
safe. Can anyone see a problem with it?
I think the POLA dictates that the behavior of that should be that you
now have both a transactional and a nontransactional hold on the lock;
and only the transactional hold goes away at commit.
regards, tom lane
Re: Transaction-scope advisory locks
On 1/20/2011 7:35 AM, Tom Lane wrote:
Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi> writes:
This seems useful, since the xact lock would be automatically released
if an error happens during "-- do something here" so you wouldn't need
to worry about releasing the lock elsewhere. But I'm not sure this is
safe. Can anyone see a problem with it?I think the POLA dictates that the behavior of that should be that you
now have both a transactional and a nontransactional hold on the lock;
and only the transactional hold goes away at commit.
Yes, I believe that's what happens now. But I guess you answered my
question too by not pointing out a huge flaw in that thinking.
Regards,
Marko Tiikkaja
Re: Transaction-scope advisory locks
On Thu, Jan 20, 2011 at 5:22 AM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
On 2011-01-17 9:28 AM +0200, Itagaki Takahiro wrote:
Here is a short review for Transaction scoped advisory locks:
https://commitfest.postgresql.org/action/patch_view?id=518Thanks for reviewing!
== Features ==
The patch adds pg_[try_]advisory_xact_lock[_shared] functions.
The function names follows the past discussion -- it's better than
"bool isXact" argument or changing the existing behavior.== Coding ==
I expect documentation will come soon.I'm sorry about this, I have been occupied with other stuff. I'm going to
work on this tonight.
Any update on this?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: Transaction-scope advisory locks
On 1/23/2011 4:24 AM, Robert Haas wrote:
On Thu, Jan 20, 2011 at 5:22 AM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:On 2011-01-17 9:28 AM +0200, Itagaki Takahiro wrote:
== Coding ==
I expect documentation will come soon.I'm sorry about this, I have been occupied with other stuff. I'm going to
work on this tonight.Any update on this?
Again, my apologies for the delay :-( Things haven't been going as
planned during the last few weeks.
Here's an updated patch with proposed doc changes. I still didn't
address the issue with pg_advisory_unlock_all() releasing transaction
scoped locks, but I'm going to. Another issue I found while testing the
behaviour here:
http://archives.postgresql.org/pgsql-hackers/2011-01/msg01939.php
is that if a session holds both a transaction level and a session level
lock on the same resource, only one of them will appear in pg_locks. Is
that going to be a problem from the user's perspective? Could it be an
indication of a well-hidden bug? Based on my tests it seems to work,
but I'm not at all confident with the code.
Regards,
Marko Tiikkaja
Attachments:
advisory3.patchtext/plain; charset=iso-8859-1; name=advisory3.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 14544,14634 **** SELECT (pg_stat_file('filename')).modification;
<literal><function>pg_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_all()</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Release all advisory locks held by the current session</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared advisory lock if available</entry>
</row>
</tbody>
</tgroup>
--- 14544,14690 ----
<literal><function>pg_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_all()</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Release all session level advisory locks held by the current session</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared session level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain exclusive transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain exclusive transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain shared transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain shared advisory lock for the current transaction</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain exclusive transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain exclusive transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain shared transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain shared transaction level advisory lock if available</entry>
</row>
</tbody>
</tgroup>
***************
*** 14680,14690 **** SELECT (pg_stat_file('filename')).modification;
</para>
<indexterm>
<primary>pg_advisory_unlock</primary>
</indexterm>
<para>
<function>pg_advisory_unlock</> will release a previously-acquired
! exclusive advisory lock. It
returns <literal>true</> if the lock is successfully released.
If the lock was not held, it will return <literal>false</>,
and in addition, an SQL warning will be raised by the server.
--- 14736,14784 ----
</para>
<indexterm>
+ <primary>pg_advisory_xact_lock</primary>
+ </indexterm>
+ <para>
+ <function>pg_advisory_xact_lock</> works the same as pg_advisory_lock,
+ expect the lock is automatically released at the end of the current
+ transaction and can not be released explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_advisory_xact_lock_shared</primary>
+ </indexterm>
+ <para>
+ <function>pg_advisory_xact_lock_shared</> works the same as
+ pg_advisory_lock_shared, expect the lock is automatically released at the
+ end of the current transaction and can not be released explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_try_advisory_xact_lock</primary>
+ </indexterm>
+ <para>
+ <function>pg_try_advisory_xact_lock</> works the same as
+ pg_try_advisory_lock, expect the lock, if acquired, is automatically
+ released at the end of the current transaction and can not be released
+ explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_try_advisory_xact_lock_shared</primary>
+ </indexterm>
+ <para>
+ <function>pg_try_advisory_xact_lock_shared</> works the same as
+ pg_try_advisory_lock_shared, expect the lock, if acquired, is automatically
+ released at the end of the current transaction and can not be released
+ explicitly.
+ </para>
+
+ <indexterm>
<primary>pg_advisory_unlock</primary>
</indexterm>
<para>
<function>pg_advisory_unlock</> will release a previously-acquired
! exclusive session level advisory lock. It
returns <literal>true</> if the lock is successfully released.
If the lock was not held, it will return <literal>false</>,
and in addition, an SQL warning will be raised by the server.
***************
*** 14696,14710 **** SELECT (pg_stat_file('filename')).modification;
<para>
<function>pg_advisory_unlock_shared</> works the same as
<function>pg_advisory_unlock</>,
! except it releases a shared advisory lock.
</para>
<indexterm>
<primary>pg_advisory_unlock_all</primary>
</indexterm>
<para>
! <function>pg_advisory_unlock_all</> will release all advisory locks
! held by the current session. (This function is implicitly invoked
at session end, even if the client disconnects ungracefully.)
</para>
--- 14790,14804 ----
<para>
<function>pg_advisory_unlock_shared</> works the same as
<function>pg_advisory_unlock</>,
! except it releases a shared session level advisory lock.
</para>
<indexterm>
<primary>pg_advisory_unlock_all</primary>
</indexterm>
<para>
! <function>pg_advisory_unlock_all</> will release all session level advisory
! locks held by the current session. (This function is implicitly invoked
at session end, even if the client disconnects ungracefully.)
</para>
*** a/doc/src/sgml/mvcc.sgml
--- b/doc/src/sgml/mvcc.sgml
***************
*** 1038,1056 **** UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
called <firstterm>advisory locks</>, because the system does not
enforce their use — it is up to the application to use them
correctly. Advisory locks can be useful for locking strategies
! that are an awkward fit for the MVCC model. Once acquired, an
! advisory lock is held until explicitly released or the session ends.
! Unlike standard locks, advisory locks do not
! honor transaction semantics: a lock acquired during a
! transaction that is later rolled back will still be held following the
rollback, and likewise an unlock is effective even if the calling
! transaction fails later. The same lock can be acquired multiple times by
! its owning process: for each lock request there must be a corresponding
! unlock request before the lock is actually released. (If a session
! already holds a given lock, additional requests will always succeed, even
! if other sessions are awaiting the lock.) Like all locks in
! <productname>PostgreSQL</productname>, a complete list of advisory
! locks currently held by any session can be found in the
<link linkend="view-pg-locks"><structname>pg_locks</structname></link>
system view.
</para>
--- 1038,1065 ----
called <firstterm>advisory locks</>, because the system does not
enforce their use — it is up to the application to use them
correctly. Advisory locks can be useful for locking strategies
! that are an awkward fit for the MVCC model.</para>
!
! <para>
! There are two different types of advisory locks in
! <productname>PostgreSQL</productname>: session level and transaction level.
! Once acquired, a session level advisory lock is held until explicitly
! released or the session ends. Unlike standard locks, session level
! advisory locks do not honor transaction semantics: a lock acquired during
! a transaction that is later rolled back will still be held following the
rollback, and likewise an unlock is effective even if the calling
! transaction fails later. The same session level lock can be acquired
! multiple times by its owning process: for each lock request there must be
! a corresponding unlock request before the lock is actually released. (If a
! session already holds a given lock, additional requests will always succeed,
! even if other sessions are awaiting the lock.) Transaction level locks on
! the other hand behave more like regular locks; they are automatically
! released at the end of the transaction, and can not be explicitly unlocked.
! Session and transaction level locks share the same lock space, which means
! that a transaction level lock will prevent another session from obtaining
! a session level lock on that same resource and vice versa.
! Like all locks in <productname>PostgreSQL</productname>, a complete list of
! advisory locks currently held by any session can be found in the
<link linkend="view-pg-locks"><structname>pg_locks</structname></link>
system view.
</para>
***************
*** 1072,1078 **** UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
strategies typical of so called <quote>flat file</> data management
systems.
While a flag stored in a table could be used for the same purpose,
! advisory locks are faster, avoid MVCC bloat, and are automatically
cleaned up by the server at the end of the session.
In certain cases using this advisory locking method, especially in queries
involving explicit ordering and <literal>LIMIT</> clauses, care must be
--- 1081,1087 ----
strategies typical of so called <quote>flat file</> data management
systems.
While a flag stored in a table could be used for the same purpose,
! advisory locks are faster, avoid MVCC bloat, and can be automatically
cleaned up by the server at the end of the session.
In certain cases using this advisory locking method, especially in queries
involving explicit ordering and <literal>LIMIT</> clauses, care must be
*** a/src/backend/storage/lmgr/README
--- b/src/backend/storage/lmgr/README
***************
*** 503,523 **** User Locks
----------
User locks are handled totally on the application side as long term
! cooperative locks which extend beyond the normal transaction boundaries.
! Their purpose is to indicate to an application that someone is `working'
! on an item. So it is possible to put an user lock on a tuple's oid,
! retrieve the tuple, work on it for an hour and then update it and remove
! the lock. While the lock is active other clients can still read and write
! the tuple but they can be aware that it has been locked at the application
! level by someone.
User locks and normal locks are completely orthogonal and they don't
interfere with each other.
- User locks are always held as session locks, so that they are not released at
- transaction end. They must be released explicitly by the application --- but
- they are released automatically when a backend terminates.
-
Locking during Hot Standby
--------------------------
--- 503,520 ----
----------
User locks are handled totally on the application side as long term
! cooperative locks which may extend beyond the normal transaction
! boundaries. Their purpose is to indicate to an application that someone
! is `working' on an item. So it is possible to put a user lock on a
! tuple's oid, retrieve the tuple, work on it for an hour and then update it
! and remove the lock. While the lock is active other clients can still
! read and write the tuple but they can be aware that it has been locked at
! the application level by someone. It is also possible to obtain user locks
! so that the the lock is released automatically at the end of the transaction.
User locks and normal locks are completely orthogonal and they don't
interfere with each other.
Locking during Hot Standby
--------------------------
*** a/src/backend/storage/lmgr/lock.c
--- b/src/backend/storage/lmgr/lock.c
***************
*** 130,136 **** static const LockMethodData default_lockmethod = {
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! false,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
--- 130,136 ----
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! true,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
*** a/src/backend/storage/lmgr/proc.c
--- b/src/backend/storage/lmgr/proc.c
***************
*** 629,636 **** LockWaitCancel(void)
* At subtransaction abort, we release all locks held by the subtransaction;
* this is implemented by retail releasing of the locks under control of
* the ResourceOwner mechanism.
- *
- * Note that user locks are not released in any case.
*/
void
ProcReleaseLocks(bool isCommit)
--- 629,634 ----
***************
*** 641,646 **** ProcReleaseLocks(bool isCommit)
--- 639,647 ----
LockWaitCancel();
/* Release locks */
LockReleaseAll(DEFAULT_LOCKMETHOD, !isCommit);
+
+ /* Release transaction level advisory locks */
+ LockReleaseAll(USER_LOCKMETHOD, false);
}
*** a/src/backend/utils/adt/lockfuncs.c
--- b/src/backend/utils/adt/lockfuncs.c
***************
*** 343,348 **** pg_advisory_lock_int8(PG_FUNCTION_ARGS)
--- 343,365 ----
}
/*
+ * pg_advisory_xact_lock(int8) - acquire xact scoped
+ * exclusive lock on an int8 key
+ */
+ Datum
+ pg_advisory_xact_lock_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ (void) LockAcquire(&tag, ExclusiveLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_advisory_lock_shared(int8) - acquire share lock on an int8 key
*/
Datum
***************
*** 359,364 **** pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
--- 376,398 ----
}
/*
+ * pg_advisory_xact_lock_shared(int8) - acquire xact scoped
+ * share lock on an int8 key
+ */
+ Datum
+ pg_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ (void) LockAcquire(&tag, ShareLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_try_advisory_lock(int8) - acquire exclusive lock on an int8 key, no wait
*
* Returns true if successful, false if lock not available
***************
*** 378,383 **** pg_try_advisory_lock_int8(PG_FUNCTION_ARGS)
--- 412,437 ----
}
/*
+ * pg_try_advisory_xact_lock(int8) - acquire xact scoped
+ * exclusive lock on an int8 key, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ res = LockAcquire(&tag, ExclusiveLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_try_advisory_lock_shared(int8) - acquire share lock on an int8 key, no wait
*
* Returns true if successful, false if lock not available
***************
*** 397,402 **** pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
--- 451,476 ----
}
/*
+ * pg_try_advisory_xact_lock_shared(int8) - acquire xact scoped
+ * share lock on an int8 key, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ res = LockAcquire(&tag, ShareLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_advisory_unlock(int8) - release exclusive lock on an int8 key
*
* Returns true if successful, false if lock was not held
***************
*** 452,457 **** pg_advisory_lock_int4(PG_FUNCTION_ARGS)
--- 526,549 ----
}
/*
+ * pg_advisory_xact_lock(int4, int4) - acquire xact scoped
+ * exclusive lock on 2 int4 keys
+ */
+ Datum
+ pg_advisory_xact_lock_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ (void) LockAcquire(&tag, ExclusiveLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_advisory_lock_shared(int4, int4) - acquire share lock on 2 int4 keys
*/
Datum
***************
*** 469,474 **** pg_advisory_lock_shared_int4(PG_FUNCTION_ARGS)
--- 561,584 ----
}
/*
+ * pg_advisory_xact_lock_shared(int4, int4) - acquire xact scoped
+ * share lock on 2 int4 keys
+ */
+ Datum
+ pg_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ (void) LockAcquire(&tag, ShareLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_try_advisory_lock(int4, int4) - acquire exclusive lock on 2 int4 keys, no wait
*
* Returns true if successful, false if lock not available
***************
*** 489,494 **** pg_try_advisory_lock_int4(PG_FUNCTION_ARGS)
--- 599,625 ----
}
/*
+ * pg_try_advisory_xact_lock(int4, int4) - acquire xact scoped
+ * exclusive lock on 2 int4 keys, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ res = LockAcquire(&tag, ExclusiveLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_try_advisory_lock_shared(int4, int4) - acquire share lock on 2 int4 keys, no wait
*
* Returns true if successful, false if lock not available
***************
*** 509,514 **** pg_try_advisory_lock_shared_int4(PG_FUNCTION_ARGS)
--- 640,666 ----
}
/*
+ * pg_try_advisory_xact_lock_shared(int4, int4) - acquire xact scoped
+ * share lock on 2 int4 keys, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ res = LockAcquire(&tag, ShareLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_advisory_unlock(int4, int4) - release exclusive lock on 2 int4 keys
*
* Returns true if successful, false if lock was not held
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4404,4428 **** DESCR("is contained by");
--- 4404,4444 ----
/* userlock replacements */
DATA(insert OID = 2880 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_int8 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive a4dvisory lock");
+ DATA(insert OID = 3071 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2881 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
+ DATA(insert OID = 3072 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock");
DATA(insert OID = 2882 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
+ DATA(insert OID = 3073 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int8 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2883 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
+ DATA(insert OID = 3074 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2884 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_int8 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2885 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int8 _null_ _null_ _null_ ));
DESCR("release shared advisory lock");
DATA(insert OID = 2886 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
+ DATA(insert OID = 3075 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2887 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
+ DATA(insert OID = 3076 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock");
DATA(insert OID = 2888 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
+ DATA(insert OID = 3077 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2889 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
+ DATA(insert OID = 3079 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2890 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_int4 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2891 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int4 _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 991,1005 **** extern Datum show_all_settings(PG_FUNCTION_ARGS);
--- 991,1013 ----
/* lockfuncs.c */
extern Datum pg_lock_status(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_int8(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_shared_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_int4(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_shared_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_all(PG_FUNCTION_ARGS);
Re: Transaction-scope advisory locks
On Fri, Jan 28, 2011 at 17:12, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
I still didn't address
the issue with pg_advisory_unlock_all() releasing transaction scoped locks,
I guess you don't want independent locks, right? If an user object
is locked by session locks, it also blocks backends trying to lock it
with transaction locks.
If so, I think an ideal behavior is below:
- The transaction-or-session property is overwritten by the last lock
function call. We can promote session locks from/to transaction locks.
- Shared and exclusive locks are managed independently.
We could have shared session lock and exclusive transaction
lock on the same resource in a transaction.
- Unlock functions releases both transaction and session locks.
- unlock_all() releases all both locks.
Those might be odd in DBMS-perspective, but would be natural as
programming languages. I guess advisory locks are often used in
standard programming like flows.
Another issue I found while testing the behaviour here:
http://archives.postgresql.org/pgsql-hackers/2011-01/msg01939.php
is that if a session holds both a transaction level and a session level lock
on the same resource, only one of them will appear in pg_locks. Is that
going to be a problem from the user's perspective? Could it be an
indication of a well-hidden bug? Based on my tests it seems to work, but
I'm not at all confident with the code.
In the above proposal, we won't have both session and transaction lock
on the same resource at the same time, though we still need to show
exclusive and shared locks in different lines.
--
Itagaki Takahiro
Re: Transaction-scope advisory locks
On Tue, Feb 1, 2011 at 7:28 AM, Itagaki Takahiro
<itagaki.takahiro@gmail.com> wrote:
On Fri, Jan 28, 2011 at 17:12, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:I still didn't address
the issue with pg_advisory_unlock_all() releasing transaction scoped locks,I guess you don't want independent locks, right? If an user object
is locked by session locks, it also blocks backends trying to lock it
with transaction locks.If so, I think an ideal behavior is below:
- The transaction-or-session property is overwritten by the last lock
function call. We can promote session locks from/to transaction locks.
No. The lock manager already supports session-locks. This patch
should be worried about making sure that LockAcquire() gets called
with the flags the user wants, NOT with redefining the interaction
between transaction locks and session locks.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: Transaction-scope advisory locks
On 2011-01-28 10:12 AM +0200, I wrote:
I still didn't
address the issue with pg_advisory_unlock_all() releasing transaction
scoped locks, but I'm going to.
.. and here's the patch. I'm not too confident with the code I added to
storage/lmgr/lock.c, but it seems to be working.
Earlier there was some discussion about adding regression tests for
advisory locks. However, I don't see where they would fit in our
current .sql files and adding a new one just for a few tests didn't seem
right. Anyone have an idea where they should go or should I just add a
new one?
Regards,
Marko Tiikkaja
Attachments:
advisory4.patchtext/plain; charset=iso-8859-1; name=advisory4.patch; x-mac-creator=0; x-mac-type=0Download
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 14544,14634 **** SELECT (pg_stat_file('filename')).modification;
<literal><function>pg_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_all()</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Release all advisory locks held by the current session</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared advisory lock if available</entry>
</row>
</tbody>
</tgroup>
--- 14544,14690 ----
<literal><function>pg_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_all()</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Release all session level advisory locks held by the current session</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared session level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain exclusive transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain exclusive transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain shared transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain shared advisory lock for the current transaction</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain exclusive transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain exclusive transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain shared transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain shared transaction level advisory lock if available</entry>
</row>
</tbody>
</tgroup>
***************
*** 14680,14690 **** SELECT (pg_stat_file('filename')).modification;
</para>
<indexterm>
<primary>pg_advisory_unlock</primary>
</indexterm>
<para>
<function>pg_advisory_unlock</> will release a previously-acquired
! exclusive advisory lock. It
returns <literal>true</> if the lock is successfully released.
If the lock was not held, it will return <literal>false</>,
and in addition, an SQL warning will be raised by the server.
--- 14736,14784 ----
</para>
<indexterm>
+ <primary>pg_advisory_xact_lock</primary>
+ </indexterm>
+ <para>
+ <function>pg_advisory_xact_lock</> works the same as pg_advisory_lock,
+ expect the lock is automatically released at the end of the current
+ transaction and can not be released explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_advisory_xact_lock_shared</primary>
+ </indexterm>
+ <para>
+ <function>pg_advisory_xact_lock_shared</> works the same as
+ pg_advisory_lock_shared, expect the lock is automatically released at the
+ end of the current transaction and can not be released explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_try_advisory_xact_lock</primary>
+ </indexterm>
+ <para>
+ <function>pg_try_advisory_xact_lock</> works the same as
+ pg_try_advisory_lock, expect the lock, if acquired, is automatically
+ released at the end of the current transaction and can not be released
+ explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_try_advisory_xact_lock_shared</primary>
+ </indexterm>
+ <para>
+ <function>pg_try_advisory_xact_lock_shared</> works the same as
+ pg_try_advisory_lock_shared, expect the lock, if acquired, is automatically
+ released at the end of the current transaction and can not be released
+ explicitly.
+ </para>
+
+ <indexterm>
<primary>pg_advisory_unlock</primary>
</indexterm>
<para>
<function>pg_advisory_unlock</> will release a previously-acquired
! exclusive session level advisory lock. It
returns <literal>true</> if the lock is successfully released.
If the lock was not held, it will return <literal>false</>,
and in addition, an SQL warning will be raised by the server.
***************
*** 14696,14710 **** SELECT (pg_stat_file('filename')).modification;
<para>
<function>pg_advisory_unlock_shared</> works the same as
<function>pg_advisory_unlock</>,
! except it releases a shared advisory lock.
</para>
<indexterm>
<primary>pg_advisory_unlock_all</primary>
</indexterm>
<para>
! <function>pg_advisory_unlock_all</> will release all advisory locks
! held by the current session. (This function is implicitly invoked
at session end, even if the client disconnects ungracefully.)
</para>
--- 14790,14804 ----
<para>
<function>pg_advisory_unlock_shared</> works the same as
<function>pg_advisory_unlock</>,
! except it releases a shared session level advisory lock.
</para>
<indexterm>
<primary>pg_advisory_unlock_all</primary>
</indexterm>
<para>
! <function>pg_advisory_unlock_all</> will release all session level advisory
! locks held by the current session. (This function is implicitly invoked
at session end, even if the client disconnects ungracefully.)
</para>
*** a/doc/src/sgml/mvcc.sgml
--- b/doc/src/sgml/mvcc.sgml
***************
*** 1038,1056 **** UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
called <firstterm>advisory locks</>, because the system does not
enforce their use — it is up to the application to use them
correctly. Advisory locks can be useful for locking strategies
! that are an awkward fit for the MVCC model. Once acquired, an
! advisory lock is held until explicitly released or the session ends.
! Unlike standard locks, advisory locks do not
! honor transaction semantics: a lock acquired during a
! transaction that is later rolled back will still be held following the
rollback, and likewise an unlock is effective even if the calling
! transaction fails later. The same lock can be acquired multiple times by
! its owning process: for each lock request there must be a corresponding
! unlock request before the lock is actually released. (If a session
! already holds a given lock, additional requests will always succeed, even
! if other sessions are awaiting the lock.) Like all locks in
! <productname>PostgreSQL</productname>, a complete list of advisory
! locks currently held by any session can be found in the
<link linkend="view-pg-locks"><structname>pg_locks</structname></link>
system view.
</para>
--- 1038,1065 ----
called <firstterm>advisory locks</>, because the system does not
enforce their use — it is up to the application to use them
correctly. Advisory locks can be useful for locking strategies
! that are an awkward fit for the MVCC model.</para>
!
! <para>
! There are two different types of advisory locks in
! <productname>PostgreSQL</productname>: session level and transaction level.
! Once acquired, a session level advisory lock is held until explicitly
! released or the session ends. Unlike standard locks, session level
! advisory locks do not honor transaction semantics: a lock acquired during
! a transaction that is later rolled back will still be held following the
rollback, and likewise an unlock is effective even if the calling
! transaction fails later. The same session level lock can be acquired
! multiple times by its owning process: for each lock request there must be
! a corresponding unlock request before the lock is actually released. (If a
! session already holds a given lock, additional requests will always succeed,
! even if other sessions are awaiting the lock.) Transaction level locks on
! the other hand behave more like regular locks; they are automatically
! released at the end of the transaction, and can not be explicitly unlocked.
! Session and transaction level locks share the same lock space, which means
! that a transaction level lock will prevent another session from obtaining
! a session level lock on that same resource and vice versa.
! Like all locks in <productname>PostgreSQL</productname>, a complete list of
! advisory locks currently held by any session can be found in the
<link linkend="view-pg-locks"><structname>pg_locks</structname></link>
system view.
</para>
***************
*** 1072,1078 **** UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
strategies typical of so called <quote>flat file</> data management
systems.
While a flag stored in a table could be used for the same purpose,
! advisory locks are faster, avoid MVCC bloat, and are automatically
cleaned up by the server at the end of the session.
In certain cases using this advisory locking method, especially in queries
involving explicit ordering and <literal>LIMIT</> clauses, care must be
--- 1081,1087 ----
strategies typical of so called <quote>flat file</> data management
systems.
While a flag stored in a table could be used for the same purpose,
! advisory locks are faster, avoid MVCC bloat, and can be automatically
cleaned up by the server at the end of the session.
In certain cases using this advisory locking method, especially in queries
involving explicit ordering and <literal>LIMIT</> clauses, care must be
*** a/src/backend/storage/lmgr/README
--- b/src/backend/storage/lmgr/README
***************
*** 503,523 **** User Locks
----------
User locks are handled totally on the application side as long term
! cooperative locks which extend beyond the normal transaction boundaries.
! Their purpose is to indicate to an application that someone is `working'
! on an item. So it is possible to put an user lock on a tuple's oid,
! retrieve the tuple, work on it for an hour and then update it and remove
! the lock. While the lock is active other clients can still read and write
! the tuple but they can be aware that it has been locked at the application
! level by someone.
User locks and normal locks are completely orthogonal and they don't
interfere with each other.
- User locks are always held as session locks, so that they are not released at
- transaction end. They must be released explicitly by the application --- but
- they are released automatically when a backend terminates.
-
Locking during Hot Standby
--------------------------
--- 503,520 ----
----------
User locks are handled totally on the application side as long term
! cooperative locks which may extend beyond the normal transaction
! boundaries. Their purpose is to indicate to an application that someone
! is `working' on an item. So it is possible to put a user lock on a
! tuple's oid, retrieve the tuple, work on it for an hour and then update it
! and remove the lock. While the lock is active other clients can still
! read and write the tuple but they can be aware that it has been locked at
! the application level by someone. It is also possible to obtain user locks
! so that the the lock is released automatically at the end of the transaction.
User locks and normal locks are completely orthogonal and they don't
interfere with each other.
Locking during Hot Standby
--------------------------
*** a/src/backend/storage/lmgr/lock.c
--- b/src/backend/storage/lmgr/lock.c
***************
*** 130,136 **** static const LockMethodData default_lockmethod = {
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! false,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
--- 130,136 ----
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! true,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
***************
*** 1484,1489 **** LockRelease(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock)
--- 1484,1542 ----
}
/*
+ * LockReleaseSession -- Release all session locks of the specified lock method that
+ * are held by the current process.
+ */
+ void
+ LockReleaseSession(LOCKMETHODID lockmethodid)
+ {
+ HASH_SEQ_STATUS status;
+ LOCALLOCK *locallock;
+ LOCALLOCKOWNER *lockOwners;
+ int i;
+
+ hash_seq_init(&status, LockMethodLocalHash);
+
+ while ((locallock = (LOCALLOCK *) hash_seq_search(&status)) != NULL)
+ {
+ /* Scan to see if there are any session locks */
+ lockOwners = locallock->lockOwners;
+ for (i = locallock->numLockOwners - 1; i >= 0; i--)
+ {
+ if (lockOwners[i].owner == NULL)
+ {
+ Assert(lockOwners[i].nLocks > 0);
+ if (lockOwners[i].nLocks < locallock->nLocks)
+ {
+ /*
+ * We will still hold this lock after forgetting this
+ * ResourceOwner.
+ */
+ locallock->nLocks -= lockOwners[i].nLocks;
+ /* compact out unused slot */
+ locallock->numLockOwners--;
+ if (i < locallock->numLockOwners)
+ lockOwners[i] = lockOwners[locallock->numLockOwners];
+ }
+ else
+ {
+ Assert(lockOwners[i].nLocks == locallock->nLocks);
+ /* We want to call LockRelease just once */
+ lockOwners[i].nLocks = 1;
+ locallock->nLocks = 1;
+ if (!LockRelease(&locallock->tag.lock,
+ locallock->tag.mode,
+ true))
+ elog(WARNING, "LockReleaseSession: failed??");
+ }
+ break;
+ }
+ }
+ }
+
+ }
+
+ /*
* LockReleaseAll -- Release all locks of the specified lock method that
* are held by the current process.
*
*** a/src/backend/storage/lmgr/proc.c
--- b/src/backend/storage/lmgr/proc.c
***************
*** 629,636 **** LockWaitCancel(void)
* At subtransaction abort, we release all locks held by the subtransaction;
* this is implemented by retail releasing of the locks under control of
* the ResourceOwner mechanism.
- *
- * Note that user locks are not released in any case.
*/
void
ProcReleaseLocks(bool isCommit)
--- 629,634 ----
***************
*** 641,646 **** ProcReleaseLocks(bool isCommit)
--- 639,647 ----
LockWaitCancel();
/* Release locks */
LockReleaseAll(DEFAULT_LOCKMETHOD, !isCommit);
+
+ /* Release transaction level advisory locks */
+ LockReleaseAll(USER_LOCKMETHOD, false);
}
*** a/src/backend/utils/adt/lockfuncs.c
--- b/src/backend/utils/adt/lockfuncs.c
***************
*** 343,348 **** pg_advisory_lock_int8(PG_FUNCTION_ARGS)
--- 343,365 ----
}
/*
+ * pg_advisory_xact_lock(int8) - acquire xact scoped
+ * exclusive lock on an int8 key
+ */
+ Datum
+ pg_advisory_xact_lock_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ (void) LockAcquire(&tag, ExclusiveLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_advisory_lock_shared(int8) - acquire share lock on an int8 key
*/
Datum
***************
*** 359,364 **** pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
--- 376,398 ----
}
/*
+ * pg_advisory_xact_lock_shared(int8) - acquire xact scoped
+ * share lock on an int8 key
+ */
+ Datum
+ pg_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ (void) LockAcquire(&tag, ShareLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_try_advisory_lock(int8) - acquire exclusive lock on an int8 key, no wait
*
* Returns true if successful, false if lock not available
***************
*** 378,383 **** pg_try_advisory_lock_int8(PG_FUNCTION_ARGS)
--- 412,437 ----
}
/*
+ * pg_try_advisory_xact_lock(int8) - acquire xact scoped
+ * exclusive lock on an int8 key, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ res = LockAcquire(&tag, ExclusiveLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_try_advisory_lock_shared(int8) - acquire share lock on an int8 key, no wait
*
* Returns true if successful, false if lock not available
***************
*** 397,402 **** pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
--- 451,476 ----
}
/*
+ * pg_try_advisory_xact_lock_shared(int8) - acquire xact scoped
+ * share lock on an int8 key, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ res = LockAcquire(&tag, ShareLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_advisory_unlock(int8) - release exclusive lock on an int8 key
*
* Returns true if successful, false if lock was not held
***************
*** 452,457 **** pg_advisory_lock_int4(PG_FUNCTION_ARGS)
--- 526,549 ----
}
/*
+ * pg_advisory_xact_lock(int4, int4) - acquire xact scoped
+ * exclusive lock on 2 int4 keys
+ */
+ Datum
+ pg_advisory_xact_lock_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ (void) LockAcquire(&tag, ExclusiveLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_advisory_lock_shared(int4, int4) - acquire share lock on 2 int4 keys
*/
Datum
***************
*** 469,474 **** pg_advisory_lock_shared_int4(PG_FUNCTION_ARGS)
--- 561,584 ----
}
/*
+ * pg_advisory_xact_lock_shared(int4, int4) - acquire xact scoped
+ * share lock on 2 int4 keys
+ */
+ Datum
+ pg_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ (void) LockAcquire(&tag, ShareLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_try_advisory_lock(int4, int4) - acquire exclusive lock on 2 int4 keys, no wait
*
* Returns true if successful, false if lock not available
***************
*** 489,494 **** pg_try_advisory_lock_int4(PG_FUNCTION_ARGS)
--- 599,625 ----
}
/*
+ * pg_try_advisory_xact_lock(int4, int4) - acquire xact scoped
+ * exclusive lock on 2 int4 keys, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ res = LockAcquire(&tag, ExclusiveLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_try_advisory_lock_shared(int4, int4) - acquire share lock on 2 int4 keys, no wait
*
* Returns true if successful, false if lock not available
***************
*** 509,514 **** pg_try_advisory_lock_shared_int4(PG_FUNCTION_ARGS)
--- 640,666 ----
}
/*
+ * pg_try_advisory_xact_lock_shared(int4, int4) - acquire xact scoped
+ * share lock on 2 int4 keys, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ res = LockAcquire(&tag, ShareLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_advisory_unlock(int4, int4) - release exclusive lock on 2 int4 keys
*
* Returns true if successful, false if lock was not held
***************
*** 554,560 **** pg_advisory_unlock_shared_int4(PG_FUNCTION_ARGS)
Datum
pg_advisory_unlock_all(PG_FUNCTION_ARGS)
{
! LockReleaseAll(USER_LOCKMETHOD, true);
PG_RETURN_VOID();
}
--- 706,712 ----
Datum
pg_advisory_unlock_all(PG_FUNCTION_ARGS)
{
! LockReleaseSession(USER_LOCKMETHOD);
PG_RETURN_VOID();
}
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4404,4428 **** DESCR("is contained by");
--- 4404,4444 ----
/* userlock replacements */
DATA(insert OID = 2880 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_int8 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive a4dvisory lock");
+ DATA(insert OID = 3071 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2881 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
+ DATA(insert OID = 3072 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock");
DATA(insert OID = 2882 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
+ DATA(insert OID = 3073 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int8 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2883 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
+ DATA(insert OID = 3074 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2884 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_int8 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2885 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int8 _null_ _null_ _null_ ));
DESCR("release shared advisory lock");
DATA(insert OID = 2886 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
+ DATA(insert OID = 3075 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2887 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
+ DATA(insert OID = 3076 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock");
DATA(insert OID = 2888 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
+ DATA(insert OID = 3077 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2889 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
+ DATA(insert OID = 3079 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2890 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_int4 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2891 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int4 _null_ _null_ _null_ ));
*** a/src/include/storage/lock.h
--- b/src/include/storage/lock.h
***************
*** 484,489 **** extern LockAcquireResult LockAcquireExtended(const LOCKTAG *locktag,
--- 484,490 ----
bool report_memory_error);
extern bool LockRelease(const LOCKTAG *locktag,
LOCKMODE lockmode, bool sessionLock);
+ extern void LockReleaseSession(LOCKMETHODID lockmethodid);
extern void LockReleaseAll(LOCKMETHODID lockmethodid, bool allLocks);
extern void LockReleaseCurrentOwner(void);
extern void LockReassignCurrentOwner(void);
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 991,1005 **** extern Datum show_all_settings(PG_FUNCTION_ARGS);
--- 991,1013 ----
/* lockfuncs.c */
extern Datum pg_lock_status(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_int8(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_shared_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_int4(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_shared_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_all(PG_FUNCTION_ARGS);
Re: Transaction-scope advisory locks
On Thu, Feb 3, 2011 at 00:24, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
.. and here's the patch. I'm not too confident with the code I added to
storage/lmgr/lock.c, but it seems to be working.
Sorry for the delayed review.
The patch needs adjustment of OIDs for recently commits, but it still works
well. See the attached small fix. The patch looks almost ready to commit
unless we want to fix the pg_locks issue below.
=== Features ===
Now unlock functions only release session-level locks and the behavior
is documented, so no confusion here. We don't have "upgrade" method
for advisory locks actually -- session and xact locks block each other,
but they are acquired and released independently.
One issue might be in pg_locks, as you pointed out in the previous mail:
if a session holds both a transaction level and a session level lock
on the same resource, only one of them will appear in pg_locks.
Also, we cannot distinguish transaction-level locks from session-level
locks from pg_locks.
It was not an issue before because session locks are only used in
internal implementation. It looks as a transaction from users.
However, this feature reveals the status in public. We might need
to add some bits to shared lock state to show which lock is session-level.
=== Implementation ===
* pg_advisory_unlock_all() calls LockReleaseSession(), ant it releases
not only advisory locks but also all session-level locks.
We use session-level locks in some places, but there is no chance
for user to send SQL commands during the lock. The behavior is safe
as of now, but it might break something in the future.
So I'd recommend to keep locktype checks in it.
* user_lockmethod.transactional was changed to 'true', so we don't have
any differences between it and default_lockmethod except trace_flag.
LockMethodData is now almost useless, but we could keep it for compatibility.
Earlier there was some discussion about adding regression tests for advisory
locks. However, I don't see where they would fit in our current .sql files
and adding a new one just for a few tests didn't seem right. Anyone have an
idea where they should go or should I just add a new one?
I think you can add advisory_lock.sql for the test.
--
Itagaki Takahiro
Attachments:
advisory4fix.patchapplication/octet-stream; name=advisory4fix.patchDownload
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index ec8e417..0fd542c 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4415,19 +4415,19 @@ DESCR("is contained by");
/* userlock replacements */
DATA(insert OID = 2880 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive a4dvisory lock");
-DATA(insert OID = 3071 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int8 _null_ _null_ _null_ ));
+DATA(insert OID = 3086 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2881 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
-DATA(insert OID = 3072 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+DATA(insert OID = 3087 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
DATA(insert OID = 2882 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
-DATA(insert OID = 3073 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int8 _null_ _null_ _null_ ));
+DATA(insert OID = 3088 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2883 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
-DATA(insert OID = 3074 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+DATA(insert OID = 3089 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2884 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_int8 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
@@ -4435,19 +4435,19 @@ DATA(insert OID = 2885 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f
DESCR("release shared advisory lock");
DATA(insert OID = 2886 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
-DATA(insert OID = 3075 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+DATA(insert OID = 3090 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2887 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
-DATA(insert OID = 3076 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+DATA(insert OID = 3091 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
DATA(insert OID = 2888 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
-DATA(insert OID = 3077 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+DATA(insert OID = 3092 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2889 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
-DATA(insert OID = 3079 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+DATA(insert OID = 3093 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2890 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_int4 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
Re: Transaction-scope advisory locks
On Wed, Feb 9, 2011 at 7:12 AM, Itagaki Takahiro
<itagaki.takahiro@gmail.com> wrote:
One issue might be in pg_locks, as you pointed out in the previous mail:
if a session holds both a transaction level and a session level lock
on the same resource, only one of them will appear in pg_locks.Also, we cannot distinguish transaction-level locks from session-level
locks from pg_locks.It was not an issue before because session locks are only used in
internal implementation. It looks as a transaction from users.
However, this feature reveals the status in public. We might need
to add some bits to shared lock state to show which lock is session-level.
Presumably that would carry a small performance penalty, since
changing the status of the lock would require modifications to the
shared hash table, not just the backend-private one.
It may still be worth doing, but I'm inclined to think that it's a
separate patch that someone could submit for 9.2.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: Transaction-scope advisory locks
On 2/9/2011 2:12 PM, Itagaki Takahiro wrote:
On Thu, Feb 3, 2011 at 00:24, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:.. and here's the patch. I'm not too confident with the code I added to
storage/lmgr/lock.c, but it seems to be working.Sorry for the delayed review.
It's okay. I really appreciate you looking at this.
The patch needs adjustment of OIDs for recently commits, but it still works
well. See the attached small fix. The patch looks almost ready to commit
unless we want to fix the pg_locks issue below.
Thanks, applied.
=== Features ===
Now unlock functions only release session-level locks and the behavior
is documented, so no confusion here. We don't have "upgrade" method
for advisory locks actually -- session and xact locks block each other,
but they are acquired and released independently.One issue might be in pg_locks, as you pointed out in the previous mail:
if a session holds both a transaction level and a session level lock
on the same resource, only one of them will appear in pg_locks.Also, we cannot distinguish transaction-level locks from session-level
locks from pg_locks.It was not an issue before because session locks are only used in
internal implementation. It looks as a transaction from users.
However, this feature reveals the status in public. We might need
to add some bits to shared lock state to show which lock is session-level.
Robert suggested not doing this for 9.1, and I don't have anything
against that.
=== Implementation ===
* pg_advisory_unlock_all() calls LockReleaseSession(), ant it releases
not only advisory locks but also all session-level locks.
We use session-level locks in some places, but there is no chance
for user to send SQL commands during the lock. The behavior is safe
as of now, but it might break something in the future.
So I'd recommend to keep locktype checks in it.
Whoops. Good catch, that was unintentional. Fixed.
* user_lockmethod.transactional was changed to 'true', so we don't have
any differences between it and default_lockmethod except trace_flag.
LockMethodData is now almost useless, but we could keep it for compatibility.
Agreed.
Earlier there was some discussion about adding regression tests for advisory
locks. However, I don't see where they would fit in our current .sql files
and adding a new one just for a few tests didn't seem right. Anyone have an
idea where they should go or should I just add a new one?I think you add advisory_lock.sql for the test.
Ok.
Updated patch attached.
Regards,
Marko Tiikkaja
Attachments:
advisory5.patchtext/plain; charset=iso-8859-1; name=advisory5.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 14623,14713 **** SELECT (pg_stat_file('filename')).modification;
<literal><function>pg_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_all()</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Release all advisory locks held by the current session</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared advisory lock if available</entry>
</row>
</tbody>
</tgroup>
--- 14623,14769 ----
<literal><function>pg_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_all()</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Release all session level advisory locks held by the current session</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared session level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain exclusive transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain exclusive transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain shared transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain shared advisory lock for the current transaction</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain exclusive transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain exclusive transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain shared transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain shared transaction level advisory lock if available</entry>
</row>
</tbody>
</tgroup>
***************
*** 14759,14769 **** SELECT (pg_stat_file('filename')).modification;
</para>
<indexterm>
<primary>pg_advisory_unlock</primary>
</indexterm>
<para>
<function>pg_advisory_unlock</> will release a previously-acquired
! exclusive advisory lock. It
returns <literal>true</> if the lock is successfully released.
If the lock was not held, it will return <literal>false</>,
and in addition, an SQL warning will be raised by the server.
--- 14815,14863 ----
</para>
<indexterm>
+ <primary>pg_advisory_xact_lock</primary>
+ </indexterm>
+ <para>
+ <function>pg_advisory_xact_lock</> works the same as pg_advisory_lock,
+ expect the lock is automatically released at the end of the current
+ transaction and can not be released explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_advisory_xact_lock_shared</primary>
+ </indexterm>
+ <para>
+ <function>pg_advisory_xact_lock_shared</> works the same as
+ pg_advisory_lock_shared, expect the lock is automatically released at the
+ end of the current transaction and can not be released explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_try_advisory_xact_lock</primary>
+ </indexterm>
+ <para>
+ <function>pg_try_advisory_xact_lock</> works the same as
+ pg_try_advisory_lock, expect the lock, if acquired, is automatically
+ released at the end of the current transaction and can not be released
+ explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_try_advisory_xact_lock_shared</primary>
+ </indexterm>
+ <para>
+ <function>pg_try_advisory_xact_lock_shared</> works the same as
+ pg_try_advisory_lock_shared, expect the lock, if acquired, is automatically
+ released at the end of the current transaction and can not be released
+ explicitly.
+ </para>
+
+ <indexterm>
<primary>pg_advisory_unlock</primary>
</indexterm>
<para>
<function>pg_advisory_unlock</> will release a previously-acquired
! exclusive session level advisory lock. It
returns <literal>true</> if the lock is successfully released.
If the lock was not held, it will return <literal>false</>,
and in addition, an SQL warning will be raised by the server.
***************
*** 14775,14789 **** SELECT (pg_stat_file('filename')).modification;
<para>
<function>pg_advisory_unlock_shared</> works the same as
<function>pg_advisory_unlock</>,
! except it releases a shared advisory lock.
</para>
<indexterm>
<primary>pg_advisory_unlock_all</primary>
</indexterm>
<para>
! <function>pg_advisory_unlock_all</> will release all advisory locks
! held by the current session. (This function is implicitly invoked
at session end, even if the client disconnects ungracefully.)
</para>
--- 14869,14883 ----
<para>
<function>pg_advisory_unlock_shared</> works the same as
<function>pg_advisory_unlock</>,
! except it releases a shared session level advisory lock.
</para>
<indexterm>
<primary>pg_advisory_unlock_all</primary>
</indexterm>
<para>
! <function>pg_advisory_unlock_all</> will release all session level advisory
! locks held by the current session. (This function is implicitly invoked
at session end, even if the client disconnects ungracefully.)
</para>
*** a/doc/src/sgml/mvcc.sgml
--- b/doc/src/sgml/mvcc.sgml
***************
*** 1199,1217 **** UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
called <firstterm>advisory locks</>, because the system does not
enforce their use — it is up to the application to use them
correctly. Advisory locks can be useful for locking strategies
! that are an awkward fit for the MVCC model. Once acquired, an
! advisory lock is held until explicitly released or the session ends.
! Unlike standard locks, advisory locks do not
! honor transaction semantics: a lock acquired during a
! transaction that is later rolled back will still be held following the
rollback, and likewise an unlock is effective even if the calling
! transaction fails later. The same lock can be acquired multiple times by
! its owning process: for each lock request there must be a corresponding
! unlock request before the lock is actually released. (If a session
! already holds a given lock, additional requests will always succeed, even
! if other sessions are awaiting the lock.) Like all locks in
! <productname>PostgreSQL</productname>, a complete list of advisory
! locks currently held by any session can be found in the
<link linkend="view-pg-locks"><structname>pg_locks</structname></link>
system view.
</para>
--- 1199,1226 ----
called <firstterm>advisory locks</>, because the system does not
enforce their use — it is up to the application to use them
correctly. Advisory locks can be useful for locking strategies
! that are an awkward fit for the MVCC model.</para>
!
! <para>
! There are two different types of advisory locks in
! <productname>PostgreSQL</productname>: session level and transaction level.
! Once acquired, a session level advisory lock is held until explicitly
! released or the session ends. Unlike standard locks, session level
! advisory locks do not honor transaction semantics: a lock acquired during
! a transaction that is later rolled back will still be held following the
rollback, and likewise an unlock is effective even if the calling
! transaction fails later. The same session level lock can be acquired
! multiple times by its owning process: for each lock request there must be
! a corresponding unlock request before the lock is actually released. (If a
! session already holds a given lock, additional requests will always succeed,
! even if other sessions are awaiting the lock.) Transaction level locks on
! the other hand behave more like regular locks; they are automatically
! released at the end of the transaction, and can not be explicitly unlocked.
! Session and transaction level locks share the same lock space, which means
! that a transaction level lock will prevent another session from obtaining
! a session level lock on that same resource and vice versa.
! Like all locks in <productname>PostgreSQL</productname>, a complete list of
! advisory locks currently held by any session can be found in the
<link linkend="view-pg-locks"><structname>pg_locks</structname></link>
system view.
</para>
***************
*** 1233,1239 **** UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
strategies typical of so called <quote>flat file</> data management
systems.
While a flag stored in a table could be used for the same purpose,
! advisory locks are faster, avoid MVCC bloat, and are automatically
cleaned up by the server at the end of the session.
In certain cases using this advisory locking method, especially in queries
involving explicit ordering and <literal>LIMIT</> clauses, care must be
--- 1242,1248 ----
strategies typical of so called <quote>flat file</> data management
systems.
While a flag stored in a table could be used for the same purpose,
! advisory locks are faster, avoid MVCC bloat, and can be automatically
cleaned up by the server at the end of the session.
In certain cases using this advisory locking method, especially in queries
involving explicit ordering and <literal>LIMIT</> clauses, care must be
*** a/src/backend/storage/lmgr/README
--- b/src/backend/storage/lmgr/README
***************
*** 505,525 **** User Locks
----------
User locks are handled totally on the application side as long term
! cooperative locks which extend beyond the normal transaction boundaries.
! Their purpose is to indicate to an application that someone is `working'
! on an item. So it is possible to put an user lock on a tuple's oid,
! retrieve the tuple, work on it for an hour and then update it and remove
! the lock. While the lock is active other clients can still read and write
! the tuple but they can be aware that it has been locked at the application
! level by someone.
User locks and normal locks are completely orthogonal and they don't
interfere with each other.
- User locks are always held as session locks, so that they are not released at
- transaction end. They must be released explicitly by the application --- but
- they are released automatically when a backend terminates.
-
Locking during Hot Standby
--------------------------
--- 505,522 ----
----------
User locks are handled totally on the application side as long term
! cooperative locks which may extend beyond the normal transaction
! boundaries. Their purpose is to indicate to an application that someone
! is `working' on an item. So it is possible to put a user lock on a
! tuple's oid, retrieve the tuple, work on it for an hour and then update it
! and remove the lock. While the lock is active other clients can still
! read and write the tuple but they can be aware that it has been locked at
! the application level by someone. It is also possible to obtain user locks
! so that the the lock is released automatically at the end of the transaction.
User locks and normal locks are completely orthogonal and they don't
interfere with each other.
Locking during Hot Standby
--------------------------
*** a/src/backend/storage/lmgr/lock.c
--- b/src/backend/storage/lmgr/lock.c
***************
*** 130,136 **** static const LockMethodData default_lockmethod = {
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! false,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
--- 130,136 ----
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! true,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
***************
*** 1484,1489 **** LockRelease(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock)
--- 1484,1548 ----
}
/*
+ * LockReleaseSession -- Release all session locks of the specified lock method that
+ * are held by the current process.
+ */
+ void
+ LockReleaseSession(LOCKMETHODID lockmethodid)
+ {
+ HASH_SEQ_STATUS status;
+ LOCALLOCK *locallock;
+ LOCALLOCKOWNER *lockOwners;
+ int i;
+
+ if (lockmethodid <= 0 || lockmethodid >= lengthof(LockMethods))
+ elog(ERROR, "unrecognized lcok method: %d", lockmethodid);
+
+ hash_seq_init(&status, LockMethodLocalHash);
+
+ while ((locallock = (LOCALLOCK *) hash_seq_search(&status)) != NULL)
+ {
+ /* Ignore items that are not of the specified lock method */
+ if (LOCALLOCK_LOCKMETHOD(*locallock) != lockmethodid)
+ continue;
+
+ /* Scan to see if there are any session locks */
+ lockOwners = locallock->lockOwners;
+ for (i = locallock->numLockOwners - 1; i >= 0; i--)
+ {
+ if (lockOwners[i].owner == NULL)
+ {
+ Assert(lockOwners[i].nLocks > 0);
+ if (lockOwners[i].nLocks < locallock->nLocks)
+ {
+ /*
+ * We will still hold this lock after forgetting this
+ * ResourceOwner.
+ */
+ locallock->nLocks -= lockOwners[i].nLocks;
+ /* compact out unused slot */
+ locallock->numLockOwners--;
+ if (i < locallock->numLockOwners)
+ lockOwners[i] = lockOwners[locallock->numLockOwners];
+ }
+ else
+ {
+ Assert(lockOwners[i].nLocks == locallock->nLocks);
+ /* We want to call LockRelease just once */
+ lockOwners[i].nLocks = 1;
+ locallock->nLocks = 1;
+ if (!LockRelease(&locallock->tag.lock,
+ locallock->tag.mode,
+ true))
+ elog(WARNING, "LockReleaseSession: failed??");
+ }
+ break;
+ }
+ }
+ }
+ }
+
+ /*
* LockReleaseAll -- Release all locks of the specified lock method that
* are held by the current process.
*
*** a/src/backend/storage/lmgr/proc.c
--- b/src/backend/storage/lmgr/proc.c
***************
*** 629,636 **** LockWaitCancel(void)
* At subtransaction abort, we release all locks held by the subtransaction;
* this is implemented by retail releasing of the locks under control of
* the ResourceOwner mechanism.
- *
- * Note that user locks are not released in any case.
*/
void
ProcReleaseLocks(bool isCommit)
--- 629,634 ----
***************
*** 641,646 **** ProcReleaseLocks(bool isCommit)
--- 639,647 ----
LockWaitCancel();
/* Release locks */
LockReleaseAll(DEFAULT_LOCKMETHOD, !isCommit);
+
+ /* Release transaction level advisory locks */
+ LockReleaseAll(USER_LOCKMETHOD, false);
}
*** a/src/backend/utils/adt/lockfuncs.c
--- b/src/backend/utils/adt/lockfuncs.c
***************
*** 422,427 **** pg_advisory_lock_int8(PG_FUNCTION_ARGS)
--- 422,444 ----
}
/*
+ * pg_advisory_xact_lock(int8) - acquire xact scoped
+ * exclusive lock on an int8 key
+ */
+ Datum
+ pg_advisory_xact_lock_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ (void) LockAcquire(&tag, ExclusiveLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_advisory_lock_shared(int8) - acquire share lock on an int8 key
*/
Datum
***************
*** 438,443 **** pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
--- 455,477 ----
}
/*
+ * pg_advisory_xact_lock_shared(int8) - acquire xact scoped
+ * share lock on an int8 key
+ */
+ Datum
+ pg_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ (void) LockAcquire(&tag, ShareLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_try_advisory_lock(int8) - acquire exclusive lock on an int8 key, no wait
*
* Returns true if successful, false if lock not available
***************
*** 457,462 **** pg_try_advisory_lock_int8(PG_FUNCTION_ARGS)
--- 491,516 ----
}
/*
+ * pg_try_advisory_xact_lock(int8) - acquire xact scoped
+ * exclusive lock on an int8 key, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ res = LockAcquire(&tag, ExclusiveLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_try_advisory_lock_shared(int8) - acquire share lock on an int8 key, no wait
*
* Returns true if successful, false if lock not available
***************
*** 476,481 **** pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS)
--- 530,555 ----
}
/*
+ * pg_try_advisory_xact_lock_shared(int8) - acquire xact scoped
+ * share lock on an int8 key, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ res = LockAcquire(&tag, ShareLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_advisory_unlock(int8) - release exclusive lock on an int8 key
*
* Returns true if successful, false if lock was not held
***************
*** 531,536 **** pg_advisory_lock_int4(PG_FUNCTION_ARGS)
--- 605,628 ----
}
/*
+ * pg_advisory_xact_lock(int4, int4) - acquire xact scoped
+ * exclusive lock on 2 int4 keys
+ */
+ Datum
+ pg_advisory_xact_lock_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ (void) LockAcquire(&tag, ExclusiveLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_advisory_lock_shared(int4, int4) - acquire share lock on 2 int4 keys
*/
Datum
***************
*** 548,553 **** pg_advisory_lock_shared_int4(PG_FUNCTION_ARGS)
--- 640,663 ----
}
/*
+ * pg_advisory_xact_lock_shared(int4, int4) - acquire xact scoped
+ * share lock on 2 int4 keys
+ */
+ Datum
+ pg_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ (void) LockAcquire(&tag, ShareLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_try_advisory_lock(int4, int4) - acquire exclusive lock on 2 int4 keys, no wait
*
* Returns true if successful, false if lock not available
***************
*** 568,573 **** pg_try_advisory_lock_int4(PG_FUNCTION_ARGS)
--- 678,704 ----
}
/*
+ * pg_try_advisory_xact_lock(int4, int4) - acquire xact scoped
+ * exclusive lock on 2 int4 keys, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ res = LockAcquire(&tag, ExclusiveLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_try_advisory_lock_shared(int4, int4) - acquire share lock on 2 int4 keys, no wait
*
* Returns true if successful, false if lock not available
***************
*** 588,593 **** pg_try_advisory_lock_shared_int4(PG_FUNCTION_ARGS)
--- 719,745 ----
}
/*
+ * pg_try_advisory_xact_lock_shared(int4, int4) - acquire xact scoped
+ * share lock on 2 int4 keys, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ res = LockAcquire(&tag, ShareLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_advisory_unlock(int4, int4) - release exclusive lock on 2 int4 keys
*
* Returns true if successful, false if lock was not held
***************
*** 633,639 **** pg_advisory_unlock_shared_int4(PG_FUNCTION_ARGS)
Datum
pg_advisory_unlock_all(PG_FUNCTION_ARGS)
{
! LockReleaseAll(USER_LOCKMETHOD, true);
PG_RETURN_VOID();
}
--- 785,791 ----
Datum
pg_advisory_unlock_all(PG_FUNCTION_ARGS)
{
! LockReleaseSession(USER_LOCKMETHOD);
PG_RETURN_VOID();
}
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4414,4438 **** DESCR("is contained by");
--- 4414,4454 ----
/* userlock replacements */
DATA(insert OID = 2880 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_int8 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive a4dvisory lock");
+ DATA(insert OID = 3086 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2881 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
+ DATA(insert OID = 3087 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock");
DATA(insert OID = 2882 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
+ DATA(insert OID = 3088 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int8 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2883 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
+ DATA(insert OID = 3089 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2884 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_int8 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2885 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int8 _null_ _null_ _null_ ));
DESCR("release shared advisory lock");
DATA(insert OID = 2886 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
+ DATA(insert OID = 3090 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2887 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
+ DATA(insert OID = 3091 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock");
DATA(insert OID = 2888 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
+ DATA(insert OID = 3092 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2889 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
+ DATA(insert OID = 3093 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2890 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_int4 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2891 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int4 _null_ _null_ _null_ ));
*** a/src/include/storage/lock.h
--- b/src/include/storage/lock.h
***************
*** 484,489 **** extern LockAcquireResult LockAcquireExtended(const LOCKTAG *locktag,
--- 484,490 ----
bool report_memory_error);
extern bool LockRelease(const LOCKTAG *locktag,
LOCKMODE lockmode, bool sessionLock);
+ extern void LockReleaseSession(LOCKMETHODID lockmethodid);
extern void LockReleaseAll(LOCKMETHODID lockmethodid, bool allLocks);
extern void LockReleaseCurrentOwner(void);
extern void LockReassignCurrentOwner(void);
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 995,1009 **** extern Datum show_all_settings(PG_FUNCTION_ARGS);
--- 995,1017 ----
/* lockfuncs.c */
extern Datum pg_lock_status(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_int8(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_shared_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_int4(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_shared_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_all(PG_FUNCTION_ARGS);
*** /dev/null
--- b/src/test/regress/expected/advisory_lock.out
***************
*** 0 ****
--- 1,275 ----
+ --
+ -- ADVISORY LOCKS
+ --
+ BEGIN;
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+ pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared
+ -----------------------+------------------------------+-----------------------+------------------------------
+ | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ -- pg_advisory_unlock_all() shouldn't release xact locks
+ SELECT pg_advisory_unlock_all();
+ pg_advisory_unlock_all
+ ------------------------
+
+ (1 row)
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 4
+ (1 row)
+
+ -- can't unlock xact locks
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2);
+ WARNING: you don't own a lock of type ExclusiveLock
+ WARNING: you don't own a lock of type ShareLock
+ WARNING: you don't own a lock of type ExclusiveLock
+ WARNING: you don't own a lock of type ShareLock
+ pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock_shared
+ --------------------+---------------------------+--------------------+---------------------------
+ f | f | f | f
+ (1 row)
+
+ -- automatically release xact locks at commit
+ COMMIT;
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
+ BEGIN;
+ -- holding both session and xact locks on the same objects, xact first
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+ pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared
+ -----------------------+------------------------------+-----------------------+------------------------------
+ | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
+ pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock_shared
+ ------------------+-------------------------+------------------+-------------------------
+ | | |
+ (1 row)
+
+ ROLLBACK;
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ -- unlocking session locks
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock(1),
+ pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
+ pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
+ WARNING: you don't own a lock of type ExclusiveLock
+ WARNING: you don't own a lock of type ShareLock
+ WARNING: you don't own a lock of type ExclusiveLock
+ WARNING: you don't own a lock of type ShareLock
+ pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared
+ --------------------+--------------------+---------------------------+---------------------------+--------------------+--------------------+---------------------------+---------------------------
+ t | f | t | f | t | f | t | f
+ (1 row)
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
+ BEGIN;
+ -- holding both session and xact locks on the same objects, session first
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
+ pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock_shared
+ ------------------+-------------------------+------------------+-------------------------
+ | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+ pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared
+ -----------------------+------------------------------+-----------------------+------------------------------
+ | | |
+ (1 row)
+
+ ROLLBACK;
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ -- releasing all session locks
+ SELECT pg_advisory_unlock_all();
+ pg_advisory_unlock_all
+ ------------------------
+
+ (1 row)
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
+ BEGIN;
+ -- grabbing txn locks multiple times
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock(1),
+ pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1),
+ pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2);
+ pg_advisory_xact_lock | pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock_shared
+ -----------------------+-----------------------+------------------------------+------------------------------+-----------------------+-----------------------+------------------------------+------------------------------
+ | | | | | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ COMMIT;
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
+ -- grabbing session locks multiple times
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock(1),
+ pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
+ pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
+ pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared
+ ------------------+------------------+-------------------------+-------------------------+------------------+------------------+-------------------------+-------------------------
+ | | | | | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock(1),
+ pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
+ pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
+ pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared
+ --------------------+--------------------+---------------------------+---------------------------+--------------------+--------------------+---------------------------+---------------------------
+ t | t | t | t | t | t | t | t
+ (1 row)
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
+ -- .. and releasing them all at once
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock(1),
+ pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
+ pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
+ pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared
+ ------------------+------------------+-------------------------+-------------------------+------------------+------------------+-------------------------+-------------------------
+ | | | | | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ SELECT pg_advisory_unlock_all();
+ pg_advisory_unlock_all
+ ------------------------
+
+ (1 row)
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 84,90 **** test: rules
# ----------
# Another group of parallel tests
# ----------
! test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps
# ----------
# Another group of parallel tests
--- 84,90 ----
# ----------
# Another group of parallel tests
# ----------
! test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock
# ----------
# Another group of parallel tests
*** /dev/null
--- b/src/test/regress/sql/advisory_lock.sql
***************
*** 0 ****
--- 1,146 ----
+ --
+ -- ADVISORY LOCKS
+ --
+
+ BEGIN;
+
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+
+ -- pg_advisory_unlock_all() shouldn't release xact locks
+ SELECT pg_advisory_unlock_all();
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+
+ -- can't unlock xact locks
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2);
+
+
+ -- automatically release xact locks at commit
+ COMMIT;
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+
+ BEGIN;
+
+ -- holding both session and xact locks on the same objects, xact first
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
+
+ ROLLBACK;
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+
+ -- unlocking session locks
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock(1),
+ pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
+ pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+
+ BEGIN;
+
+ -- holding both session and xact locks on the same objects, session first
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+
+ ROLLBACK;
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+
+ -- releasing all session locks
+ SELECT pg_advisory_unlock_all();
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+
+ BEGIN;
+
+ -- grabbing txn locks multiple times
+
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock(1),
+ pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1),
+ pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+ COMMIT;
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+ -- grabbing session locks multiple times
+
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock(1),
+ pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
+ pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock(1),
+ pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
+ pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+ -- .. and releasing them all at once
+
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock(1),
+ pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
+ pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+ SELECT pg_advisory_unlock_all();
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
Re: Transaction-scope advisory locks
On Thu, Feb 10, 2011 at 08:36, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
One issue might be in pg_locks
Robert suggested not doing this for 9.1, and I don't have anything against
that.
Agreed.
Updated patch attached.
Looks good to commit. I note a few minor issues for committer:
* Functions listed in "Table 9-62. Advisory Lock Functions" might need
sorted in alphabetical order.
* We could extend LockReleaseAll() to have the 3rd mode
instead of LockReleaseSession(). Existing behavior is:
| LockReleaseAll(LOCKMETHODID lockmethodid, bool allLocks)
| allLocks == true: release all locks including session locks.
| allLocks == false: release all non-session locks.
* Or, we might have one subroutine for LockReleaseSession() and
LockReleaseCurrentOwner(). They have similar codes.
--
Itagaki Takahiro
Re: Transaction-scope advisory locks
On Thu, Feb 10, 2011 at 17:15, Itagaki Takahiro
<itagaki.takahiro@gmail.com> wrote:
Updated patch attached.
Looks good to commit.
I did a few cosmetic fixes, mainly lmgr/README and make a subroutine
ReleaseLockForOwner() for LockReleaseSession and LockReleaseCurrentOwner.
If no more comments nor objections, I'll apply the version to git.
--
Itagaki Takahiro
Attachments:
advisory6.patchapplication/octet-stream; name=advisory6.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 70a1bd9..a41d3c6 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT (pg_stat_file('filename')).modifi
*** 14623,14713 ****
<literal><function>pg_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_all()</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Release all advisory locks held by the current session</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared advisory lock if available</entry>
</row>
</tbody>
</tgroup>
--- 14623,14769 ----
<literal><function>pg_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Obtain shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release an exclusive session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_all()</function></literal>
</entry>
<entry><type>void</type></entry>
! <entry>Release all session level advisory locks held by the current session</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared session level advisory lock</entry>
</row>
<row>
<entry>
<literal><function>pg_advisory_unlock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Release a shared session level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain exclusive transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain exclusive transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain shared transaction level advisory lock</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>void</type></entry>
! <entry>Obtain shared advisory lock for the current transaction</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain exclusive session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared session level advisory lock if available</entry>
</row>
<row>
<entry>
<literal><function>pg_try_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
! <entry>Obtain shared session level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain exclusive transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain exclusive transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain shared transaction level advisory lock if available</entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
! </entry>
! <entry><type>boolean</type></entry>
! <entry>Obtain shared transaction level advisory lock if available</entry>
</row>
</tbody>
</tgroup>
*************** SELECT (pg_stat_file('filename')).modifi
*** 14759,14769 ****
</para>
<indexterm>
<primary>pg_advisory_unlock</primary>
</indexterm>
<para>
<function>pg_advisory_unlock</> will release a previously-acquired
! exclusive advisory lock. It
returns <literal>true</> if the lock is successfully released.
If the lock was not held, it will return <literal>false</>,
and in addition, an SQL warning will be raised by the server.
--- 14815,14863 ----
</para>
<indexterm>
+ <primary>pg_advisory_xact_lock</primary>
+ </indexterm>
+ <para>
+ <function>pg_advisory_xact_lock</> works the same as pg_advisory_lock,
+ expect the lock is automatically released at the end of the current
+ transaction and can not be released explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_advisory_xact_lock_shared</primary>
+ </indexterm>
+ <para>
+ <function>pg_advisory_xact_lock_shared</> works the same as
+ pg_advisory_lock_shared, expect the lock is automatically released at the
+ end of the current transaction and can not be released explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_try_advisory_xact_lock</primary>
+ </indexterm>
+ <para>
+ <function>pg_try_advisory_xact_lock</> works the same as
+ pg_try_advisory_lock, expect the lock, if acquired, is automatically
+ released at the end of the current transaction and can not be released
+ explicitly.
+ </para>
+
+ <indexterm>
+ <primary>pg_try_advisory_xact_lock_shared</primary>
+ </indexterm>
+ <para>
+ <function>pg_try_advisory_xact_lock_shared</> works the same as
+ pg_try_advisory_lock_shared, expect the lock, if acquired, is automatically
+ released at the end of the current transaction and can not be released
+ explicitly.
+ </para>
+
+ <indexterm>
<primary>pg_advisory_unlock</primary>
</indexterm>
<para>
<function>pg_advisory_unlock</> will release a previously-acquired
! exclusive session level advisory lock. It
returns <literal>true</> if the lock is successfully released.
If the lock was not held, it will return <literal>false</>,
and in addition, an SQL warning will be raised by the server.
*************** SELECT (pg_stat_file('filename')).modifi
*** 14775,14789 ****
<para>
<function>pg_advisory_unlock_shared</> works the same as
<function>pg_advisory_unlock</>,
! except it releases a shared advisory lock.
</para>
<indexterm>
<primary>pg_advisory_unlock_all</primary>
</indexterm>
<para>
! <function>pg_advisory_unlock_all</> will release all advisory locks
! held by the current session. (This function is implicitly invoked
at session end, even if the client disconnects ungracefully.)
</para>
--- 14869,14883 ----
<para>
<function>pg_advisory_unlock_shared</> works the same as
<function>pg_advisory_unlock</>,
! except it releases a shared session level advisory lock.
</para>
<indexterm>
<primary>pg_advisory_unlock_all</primary>
</indexterm>
<para>
! <function>pg_advisory_unlock_all</> will release all session level advisory
! locks held by the current session. (This function is implicitly invoked
at session end, even if the client disconnects ungracefully.)
</para>
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index f42bb09..785c9d7 100644
*** a/doc/src/sgml/mvcc.sgml
--- b/doc/src/sgml/mvcc.sgml
*************** UPDATE accounts SET balance = balance -
*** 1199,1217 ****
called <firstterm>advisory locks</>, because the system does not
enforce their use — it is up to the application to use them
correctly. Advisory locks can be useful for locking strategies
! that are an awkward fit for the MVCC model. Once acquired, an
! advisory lock is held until explicitly released or the session ends.
! Unlike standard locks, advisory locks do not
! honor transaction semantics: a lock acquired during a
! transaction that is later rolled back will still be held following the
rollback, and likewise an unlock is effective even if the calling
! transaction fails later. The same lock can be acquired multiple times by
! its owning process: for each lock request there must be a corresponding
! unlock request before the lock is actually released. (If a session
! already holds a given lock, additional requests will always succeed, even
! if other sessions are awaiting the lock.) Like all locks in
! <productname>PostgreSQL</productname>, a complete list of advisory
! locks currently held by any session can be found in the
<link linkend="view-pg-locks"><structname>pg_locks</structname></link>
system view.
</para>
--- 1199,1226 ----
called <firstterm>advisory locks</>, because the system does not
enforce their use — it is up to the application to use them
correctly. Advisory locks can be useful for locking strategies
! that are an awkward fit for the MVCC model.</para>
!
! <para>
! There are two different types of advisory locks in
! <productname>PostgreSQL</productname>: session level and transaction level.
! Once acquired, a session level advisory lock is held until explicitly
! released or the session ends. Unlike standard locks, session level
! advisory locks do not honor transaction semantics: a lock acquired during
! a transaction that is later rolled back will still be held following the
rollback, and likewise an unlock is effective even if the calling
! transaction fails later. The same session level lock can be acquired
! multiple times by its owning process: for each lock request there must be
! a corresponding unlock request before the lock is actually released. (If a
! session already holds a given lock, additional requests will always succeed,
! even if other sessions are awaiting the lock.) Transaction level locks on
! the other hand behave more like regular locks; they are automatically
! released at the end of the transaction, and can not be explicitly unlocked.
! Session and transaction level locks share the same lock space, which means
! that a transaction level lock will prevent another session from obtaining
! a session level lock on that same resource and vice versa.
! Like all locks in <productname>PostgreSQL</productname>, a complete list of
! advisory locks currently held by any session can be found in the
<link linkend="view-pg-locks"><structname>pg_locks</structname></link>
system view.
</para>
*************** UPDATE accounts SET balance = balance -
*** 1233,1239 ****
strategies typical of so called <quote>flat file</> data management
systems.
While a flag stored in a table could be used for the same purpose,
! advisory locks are faster, avoid MVCC bloat, and are automatically
cleaned up by the server at the end of the session.
In certain cases using this advisory locking method, especially in queries
involving explicit ordering and <literal>LIMIT</> clauses, care must be
--- 1242,1248 ----
strategies typical of so called <quote>flat file</> data management
systems.
While a flag stored in a table could be used for the same purpose,
! advisory locks are faster, avoid MVCC bloat, and can be automatically
cleaned up by the server at the end of the session.
In certain cases using this advisory locking method, especially in queries
involving explicit ordering and <literal>LIMIT</> clauses, care must be
diff --git a/src/backend/storage/lmgr/README b/src/backend/storage/lmgr/README
index 40779d2..87fd312 100644
*** a/src/backend/storage/lmgr/README
--- b/src/backend/storage/lmgr/README
*************** User Locks
*** 505,511 ****
----------
User locks are handled totally on the application side as long term
! cooperative locks which extend beyond the normal transaction boundaries.
Their purpose is to indicate to an application that someone is `working'
on an item. So it is possible to put an user lock on a tuple's oid,
retrieve the tuple, work on it for an hour and then update it and remove
--- 505,511 ----
----------
User locks are handled totally on the application side as long term
! cooperative locks which may extend beyond the normal transaction boundaries.
Their purpose is to indicate to an application that someone is `working'
on an item. So it is possible to put an user lock on a tuple's oid,
retrieve the tuple, work on it for an hour and then update it and remove
*************** level by someone.
*** 516,524 ****
User locks and normal locks are completely orthogonal and they don't
interfere with each other.
! User locks are always held as session locks, so that they are not released at
! transaction end. They must be released explicitly by the application --- but
! they are released automatically when a backend terminates.
Locking during Hot Standby
--------------------------
--- 516,527 ----
User locks and normal locks are completely orthogonal and they don't
interfere with each other.
! There are two types of user locks: session level and transaction level.
! Session level user locks are not released at transaction end. They must
! be released explicitly by the application --- but they are released
! automatically when a backend terminates. On the other hand, transaction
! level user locks are released automatically at the end of the transaction
! as like as other normal locks.
Locking during Hot Standby
--------------------------
diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c
index cea5096..ed0ea12 100644
*** a/src/backend/storage/lmgr/lock.c
--- b/src/backend/storage/lmgr/lock.c
*************** static const LockMethodData default_lock
*** 130,136 ****
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! false,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
--- 130,136 ----
static const LockMethodData user_lockmethod = {
AccessExclusiveLock, /* highest valid lock mode number */
! true,
LockConflicts,
lock_mode_names,
#ifdef LOCK_DEBUG
*************** static uint32 proclock_hash(const void *
*** 256,261 ****
--- 256,262 ----
static void RemoveLocalLock(LOCALLOCK *locallock);
static void GrantLockLocal(LOCALLOCK *locallock, ResourceOwner owner);
static void WaitOnLock(LOCALLOCK *locallock, ResourceOwner owner);
+ static void ReleaseLockForOwner(LOCALLOCK *locallock, ResourceOwner owner);
static bool UnGrantLock(LOCK *lock, LOCKMODE lockmode,
PROCLOCK *proclock, LockMethod lockMethodTable);
static void CleanUpLock(LOCK *lock, PROCLOCK *proclock,
*************** LockRelease(const LOCKTAG *locktag, LOCK
*** 1484,1489 ****
--- 1485,1515 ----
}
/*
+ * LockReleaseSession -- Release all session locks of the specified lock method
+ * that are held by the current process.
+ */
+ void
+ LockReleaseSession(LOCKMETHODID lockmethodid)
+ {
+ HASH_SEQ_STATUS status;
+ LOCALLOCK *locallock;
+
+ if (lockmethodid <= 0 || lockmethodid >= lengthof(LockMethods))
+ elog(ERROR, "unrecognized lock method: %d", lockmethodid);
+
+ hash_seq_init(&status, LockMethodLocalHash);
+
+ while ((locallock = (LOCALLOCK *) hash_seq_search(&status)) != NULL)
+ {
+ /* Ignore items that are not of the specified lock method */
+ if (LOCALLOCK_LOCKMETHOD(*locallock) != lockmethodid)
+ continue;
+
+ ReleaseLockForOwner(locallock, NULL);
+ }
+ }
+
+ /*
* LockReleaseAll -- Release all locks of the specified lock method that
* are held by the current process.
*
*************** LockReleaseCurrentOwner(void)
*** 1679,1686 ****
{
HASH_SEQ_STATUS status;
LOCALLOCK *locallock;
- LOCALLOCKOWNER *lockOwners;
- int i;
hash_seq_init(&status, LockMethodLocalHash);
--- 1705,1710 ----
*************** LockReleaseCurrentOwner(void)
*** 1690,1727 ****
if (!LockMethods[LOCALLOCK_LOCKMETHOD(*locallock)]->transactional)
continue;
! /* Scan to see if there are any locks belonging to current owner */
! lockOwners = locallock->lockOwners;
! for (i = locallock->numLockOwners - 1; i >= 0; i--)
{
! if (lockOwners[i].owner == CurrentResourceOwner)
{
! Assert(lockOwners[i].nLocks > 0);
! if (lockOwners[i].nLocks < locallock->nLocks)
! {
! /*
! * We will still hold this lock after forgetting this
! * ResourceOwner.
! */
! locallock->nLocks -= lockOwners[i].nLocks;
! /* compact out unused slot */
! locallock->numLockOwners--;
! if (i < locallock->numLockOwners)
! lockOwners[i] = lockOwners[locallock->numLockOwners];
! }
! else
! {
! Assert(lockOwners[i].nLocks == locallock->nLocks);
! /* We want to call LockRelease just once */
! lockOwners[i].nLocks = 1;
! locallock->nLocks = 1;
! if (!LockRelease(&locallock->tag.lock,
! locallock->tag.mode,
! false))
! elog(WARNING, "LockReleaseCurrentOwner: failed??");
! }
! break;
}
}
}
}
--- 1714,1764 ----
if (!LockMethods[LOCALLOCK_LOCKMETHOD(*locallock)]->transactional)
continue;
! ReleaseLockForOwner(locallock, CurrentResourceOwner);
! }
! }
!
! /*
! * Subroutine to release a lock belonging to the 'owner' if found.
! * 'owner' can be NULL to release a session lock.
! */
! static void
! ReleaseLockForOwner(LOCALLOCK *locallock, ResourceOwner owner)
! {
! int i;
! LOCALLOCKOWNER *lockOwners;
!
! /* Scan to see if there are any locks belonging to the owner */
! lockOwners = locallock->lockOwners;
! for (i = locallock->numLockOwners - 1; i >= 0; i--)
! {
! if (lockOwners[i].owner == owner)
{
! Assert(lockOwners[i].nLocks > 0);
! if (lockOwners[i].nLocks < locallock->nLocks)
{
! /*
! * We will still hold this lock after forgetting this
! * ResourceOwner.
! */
! locallock->nLocks -= lockOwners[i].nLocks;
! /* compact out unused slot */
! locallock->numLockOwners--;
! if (i < locallock->numLockOwners)
! lockOwners[i] = lockOwners[locallock->numLockOwners];
}
+ else
+ {
+ Assert(lockOwners[i].nLocks == locallock->nLocks);
+ /* We want to call LockRelease just once */
+ lockOwners[i].nLocks = 1;
+ locallock->nLocks = 1;
+ if (!LockRelease(&locallock->tag.lock,
+ locallock->tag.mode,
+ owner == NULL))
+ elog(WARNING, "ReleaseLockForOwner: failed??");
+ }
+ break;
}
}
}
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index be577bc..afaf599 100644
*** a/src/backend/storage/lmgr/proc.c
--- b/src/backend/storage/lmgr/proc.c
*************** LockWaitCancel(void)
*** 629,636 ****
* At subtransaction abort, we release all locks held by the subtransaction;
* this is implemented by retail releasing of the locks under control of
* the ResourceOwner mechanism.
- *
- * Note that user locks are not released in any case.
*/
void
ProcReleaseLocks(bool isCommit)
--- 629,634 ----
*************** ProcReleaseLocks(bool isCommit)
*** 641,646 ****
--- 639,647 ----
LockWaitCancel();
/* Release locks */
LockReleaseAll(DEFAULT_LOCKMETHOD, !isCommit);
+
+ /* Release transaction level advisory locks */
+ LockReleaseAll(USER_LOCKMETHOD, false);
}
diff --git a/src/backend/utils/adt/lockfuncs.c b/src/backend/utils/adt/lockfuncs.c
index 8e36982..c6c948c 100644
*** a/src/backend/utils/adt/lockfuncs.c
--- b/src/backend/utils/adt/lockfuncs.c
*************** pg_advisory_lock_int8(PG_FUNCTION_ARGS)
*** 422,427 ****
--- 422,444 ----
}
/*
+ * pg_advisory_xact_lock(int8) - acquire xact scoped
+ * exclusive lock on an int8 key
+ */
+ Datum
+ pg_advisory_xact_lock_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ (void) LockAcquire(&tag, ExclusiveLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_advisory_lock_shared(int8) - acquire share lock on an int8 key
*/
Datum
*************** pg_advisory_lock_shared_int8(PG_FUNCTION
*** 438,443 ****
--- 455,477 ----
}
/*
+ * pg_advisory_xact_lock_shared(int8) - acquire xact scoped
+ * share lock on an int8 key
+ */
+ Datum
+ pg_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ (void) LockAcquire(&tag, ShareLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_try_advisory_lock(int8) - acquire exclusive lock on an int8 key, no wait
*
* Returns true if successful, false if lock not available
*************** pg_try_advisory_lock_int8(PG_FUNCTION_AR
*** 457,462 ****
--- 491,516 ----
}
/*
+ * pg_try_advisory_xact_lock(int8) - acquire xact scoped
+ * exclusive lock on an int8 key, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ res = LockAcquire(&tag, ExclusiveLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_try_advisory_lock_shared(int8) - acquire share lock on an int8 key, no wait
*
* Returns true if successful, false if lock not available
*************** pg_try_advisory_lock_shared_int8(PG_FUNC
*** 476,481 ****
--- 530,555 ----
}
/*
+ * pg_try_advisory_xact_lock_shared(int8) - acquire xact scoped
+ * share lock on an int8 key, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS)
+ {
+ int64 key = PG_GETARG_INT64(0);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT64(tag, key);
+
+ res = LockAcquire(&tag, ShareLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_advisory_unlock(int8) - release exclusive lock on an int8 key
*
* Returns true if successful, false if lock was not held
*************** pg_advisory_lock_int4(PG_FUNCTION_ARGS)
*** 531,536 ****
--- 605,628 ----
}
/*
+ * pg_advisory_xact_lock(int4, int4) - acquire xact scoped
+ * exclusive lock on 2 int4 keys
+ */
+ Datum
+ pg_advisory_xact_lock_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ (void) LockAcquire(&tag, ExclusiveLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_advisory_lock_shared(int4, int4) - acquire share lock on 2 int4 keys
*/
Datum
*************** pg_advisory_lock_shared_int4(PG_FUNCTION
*** 548,553 ****
--- 640,663 ----
}
/*
+ * pg_advisory_xact_lock_shared(int4, int4) - acquire xact scoped
+ * share lock on 2 int4 keys
+ */
+ Datum
+ pg_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ (void) LockAcquire(&tag, ShareLock, false, false);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
* pg_try_advisory_lock(int4, int4) - acquire exclusive lock on 2 int4 keys, no wait
*
* Returns true if successful, false if lock not available
*************** pg_try_advisory_lock_int4(PG_FUNCTION_AR
*** 568,573 ****
--- 678,704 ----
}
/*
+ * pg_try_advisory_xact_lock(int4, int4) - acquire xact scoped
+ * exclusive lock on 2 int4 keys, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ res = LockAcquire(&tag, ExclusiveLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_try_advisory_lock_shared(int4, int4) - acquire share lock on 2 int4 keys, no wait
*
* Returns true if successful, false if lock not available
*************** pg_try_advisory_lock_shared_int4(PG_FUNC
*** 588,593 ****
--- 719,745 ----
}
/*
+ * pg_try_advisory_xact_lock_shared(int4, int4) - acquire xact scoped
+ * share lock on 2 int4 keys, no wait
+ *
+ * Returns true if successful, false if lock not available
+ */
+ Datum
+ pg_try_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS)
+ {
+ int32 key1 = PG_GETARG_INT32(0);
+ int32 key2 = PG_GETARG_INT32(1);
+ LOCKTAG tag;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_INT32(tag, key1, key2);
+
+ res = LockAcquire(&tag, ShareLock, false, true);
+
+ PG_RETURN_BOOL(res != LOCKACQUIRE_NOT_AVAIL);
+ }
+
+ /*
* pg_advisory_unlock(int4, int4) - release exclusive lock on 2 int4 keys
*
* Returns true if successful, false if lock was not held
*************** pg_advisory_unlock_shared_int4(PG_FUNCTI
*** 633,639 ****
Datum
pg_advisory_unlock_all(PG_FUNCTION_ARGS)
{
! LockReleaseAll(USER_LOCKMETHOD, true);
PG_RETURN_VOID();
}
--- 785,791 ----
Datum
pg_advisory_unlock_all(PG_FUNCTION_ARGS)
{
! LockReleaseSession(USER_LOCKMETHOD);
PG_RETURN_VOID();
}
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 9d70267..7fb33f0 100644
*** a/src/include/catalog/catversion.h
--- b/src/include/catalog/catversion.h
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 201102161
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 201102171
#endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index a16c667..0908427 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DESCR("is contained by");
*** 4420,4444 ****
--- 4420,4460 ----
/* userlock replacements */
DATA(insert OID = 2880 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_int8 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive a4dvisory lock");
+ DATA(insert OID = 3089 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2881 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
+ DATA(insert OID = 3090 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "20" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock");
DATA(insert OID = 2882 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int8 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
+ DATA(insert OID = 3091 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int8 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2883 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int8 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
+ DATA(insert OID = 3092 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int8 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2884 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_int8 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2885 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 1 0 16 "20" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int8 _null_ _null_ _null_ ));
DESCR("release shared advisory lock");
DATA(insert OID = 2886 ( pg_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock");
+ DATA(insert OID = 3093 ( pg_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock");
DATA(insert OID = 2887 ( pg_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock");
+ DATA(insert OID = 3094 ( pg_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "23 23" _null_ _null_ _null_ _null_ pg_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock");
DATA(insert OID = 2888 ( pg_try_advisory_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_int4 _null_ _null_ _null_ ));
DESCR("obtain exclusive advisory lock if available");
+ DATA(insert OID = 3095 ( pg_try_advisory_xact_lock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_int4 _null_ _null_ _null_ ));
+ DESCR("obtain exclusive advisory lock if available");
DATA(insert OID = 2889 ( pg_try_advisory_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_lock_shared_int4 _null_ _null_ _null_ ));
DESCR("obtain shared advisory lock if available");
+ DATA(insert OID = 3096 ( pg_try_advisory_xact_lock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_try_advisory_xact_lock_shared_int4 _null_ _null_ _null_ ));
+ DESCR("obtain shared advisory lock if available");
DATA(insert OID = 2890 ( pg_advisory_unlock PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_int4 _null_ _null_ _null_ ));
DESCR("release exclusive advisory lock");
DATA(insert OID = 2891 ( pg_advisory_unlock_shared PGNSP PGUID 12 1 0 0 f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ pg_advisory_unlock_shared_int4 _null_ _null_ _null_ ));
diff --git a/src/include/storage/lock.h b/src/include/storage/lock.h
index 7b52d2e..7ec961f 100644
*** a/src/include/storage/lock.h
--- b/src/include/storage/lock.h
*************** extern LockAcquireResult LockAcquireExte
*** 484,489 ****
--- 484,490 ----
bool report_memory_error);
extern bool LockRelease(const LOCKTAG *locktag,
LOCKMODE lockmode, bool sessionLock);
+ extern void LockReleaseSession(LOCKMETHODID lockmethodid);
extern void LockReleaseAll(LOCKMETHODID lockmethodid, bool allLocks);
extern void LockReleaseCurrentOwner(void);
extern void LockReassignCurrentOwner(void);
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 4341025..277aec4 100644
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum show_all_settings(PG_FUNCTI
*** 995,1009 ****
--- 995,1017 ----
/* lockfuncs.c */
extern Datum pg_lock_status(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_shared_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_int8(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_shared_int8(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_shared_int8(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_lock_shared_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_int4(PG_FUNCTION_ARGS);
extern Datum pg_try_advisory_lock_shared_int4(PG_FUNCTION_ARGS);
+ extern Datum pg_try_advisory_xact_lock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_shared_int4(PG_FUNCTION_ARGS);
extern Datum pg_advisory_unlock_all(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/advisory_lock.out b/src/test/regress/expected/advisory_lock.out
index ...2a2df6f .
*** a/src/test/regress/expected/advisory_lock.out
--- b/src/test/regress/expected/advisory_lock.out
***************
*** 0 ****
--- 1,275 ----
+ --
+ -- ADVISORY LOCKS
+ --
+ BEGIN;
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+ pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared
+ -----------------------+------------------------------+-----------------------+------------------------------
+ | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ -- pg_advisory_unlock_all() shouldn't release xact locks
+ SELECT pg_advisory_unlock_all();
+ pg_advisory_unlock_all
+ ------------------------
+
+ (1 row)
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 4
+ (1 row)
+
+ -- can't unlock xact locks
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2);
+ WARNING: you don't own a lock of type ExclusiveLock
+ WARNING: you don't own a lock of type ShareLock
+ WARNING: you don't own a lock of type ExclusiveLock
+ WARNING: you don't own a lock of type ShareLock
+ pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock_shared
+ --------------------+---------------------------+--------------------+---------------------------
+ f | f | f | f
+ (1 row)
+
+ -- automatically release xact locks at commit
+ COMMIT;
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
+ BEGIN;
+ -- holding both session and xact locks on the same objects, xact first
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+ pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared
+ -----------------------+------------------------------+-----------------------+------------------------------
+ | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
+ pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock_shared
+ ------------------+-------------------------+------------------+-------------------------
+ | | |
+ (1 row)
+
+ ROLLBACK;
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ -- unlocking session locks
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock(1),
+ pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
+ pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
+ WARNING: you don't own a lock of type ExclusiveLock
+ WARNING: you don't own a lock of type ShareLock
+ WARNING: you don't own a lock of type ExclusiveLock
+ WARNING: you don't own a lock of type ShareLock
+ pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared
+ --------------------+--------------------+---------------------------+---------------------------+--------------------+--------------------+---------------------------+---------------------------
+ t | f | t | f | t | f | t | f
+ (1 row)
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
+ BEGIN;
+ -- holding both session and xact locks on the same objects, session first
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
+ pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock_shared
+ ------------------+-------------------------+------------------+-------------------------
+ | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+ pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared
+ -----------------------+------------------------------+-----------------------+------------------------------
+ | | |
+ (1 row)
+
+ ROLLBACK;
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ -- releasing all session locks
+ SELECT pg_advisory_unlock_all();
+ pg_advisory_unlock_all
+ ------------------------
+
+ (1 row)
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
+ BEGIN;
+ -- grabbing txn locks multiple times
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock(1),
+ pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1),
+ pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2);
+ pg_advisory_xact_lock | pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock_shared
+ -----------------------+-----------------------+------------------------------+------------------------------+-----------------------+-----------------------+------------------------------+------------------------------
+ | | | | | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ COMMIT;
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
+ -- grabbing session locks multiple times
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock(1),
+ pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
+ pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
+ pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared
+ ------------------+------------------+-------------------------+-------------------------+------------------+------------------+-------------------------+-------------------------
+ | | | | | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock(1),
+ pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
+ pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
+ pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared
+ --------------------+--------------------+---------------------------+---------------------------+--------------------+--------------------+---------------------------+---------------------------
+ t | t | t | t | t | t | t | t
+ (1 row)
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
+ -- .. and releasing them all at once
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock(1),
+ pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
+ pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
+ pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared
+ ------------------+------------------+-------------------------+-------------------------+------------------+------------------+-------------------------+-------------------------
+ | | | | | | |
+ (1 row)
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+ locktype | classid | objid | objsubid | mode | granted
+ ----------+---------+-------+----------+---------------+---------
+ advisory | 0 | 1 | 1 | ExclusiveLock | t
+ advisory | 0 | 2 | 1 | ShareLock | t
+ advisory | 1 | 1 | 2 | ExclusiveLock | t
+ advisory | 2 | 2 | 2 | ShareLock | t
+ (4 rows)
+
+ SELECT pg_advisory_unlock_all();
+ pg_advisory_unlock_all
+ ------------------------
+
+ (1 row)
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+ count
+ -------
+ 0
+ (1 row)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 3b99e86..aa6f6d2 100644
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
*************** test: rules
*** 84,90 ****
# ----------
# Another group of parallel tests
# ----------
! test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps
# ----------
# Another group of parallel tests
--- 84,90 ----
# ----------
# Another group of parallel tests
# ----------
! test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/advisory_lock.sql b/src/test/regress/sql/advisory_lock.sql
index ...57c47c0 .
*** a/src/test/regress/sql/advisory_lock.sql
--- b/src/test/regress/sql/advisory_lock.sql
***************
*** 0 ****
--- 1,146 ----
+ --
+ -- ADVISORY LOCKS
+ --
+
+ BEGIN;
+
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+
+ -- pg_advisory_unlock_all() shouldn't release xact locks
+ SELECT pg_advisory_unlock_all();
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+
+ -- can't unlock xact locks
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2);
+
+
+ -- automatically release xact locks at commit
+ COMMIT;
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+
+ BEGIN;
+
+ -- holding both session and xact locks on the same objects, xact first
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
+
+ ROLLBACK;
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+
+ -- unlocking session locks
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock(1),
+ pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
+ pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+
+ BEGIN;
+
+ -- holding both session and xact locks on the same objects, session first
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
+
+ ROLLBACK;
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+
+ -- releasing all session locks
+ SELECT pg_advisory_unlock_all();
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+
+ BEGIN;
+
+ -- grabbing txn locks multiple times
+
+ SELECT
+ pg_advisory_xact_lock(1), pg_advisory_xact_lock(1),
+ pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2),
+ pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1),
+ pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+ COMMIT;
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+ -- grabbing session locks multiple times
+
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock(1),
+ pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
+ pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+ SELECT
+ pg_advisory_unlock(1), pg_advisory_unlock(1),
+ pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
+ pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
+ pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+
+ -- .. and releasing them all at once
+
+ SELECT
+ pg_advisory_lock(1), pg_advisory_lock(1),
+ pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
+ pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
+ pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
+
+ SELECT locktype, classid, objid, objsubid, mode, granted
+ FROM pg_locks WHERE locktype = 'advisory'
+ ORDER BY classid, objid, objsubid;
+
+ SELECT pg_advisory_unlock_all();
+
+ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
Re: Transaction-scope advisory locks
On Thu, Feb 17, 2011 at 17:05, Itagaki Takahiro
<itagaki.takahiro@gmail.com> wrote:
I did a few cosmetic fixes, mainly lmgr/README and make a subroutine
ReleaseLockForOwner() for LockReleaseSession and LockReleaseCurrentOwner.
Committed with a few typo fixes. Thanks, Marko!
--
Itagaki Takahiro