3 ms·
> If pg_advisory_lock fails to acquire a lock, an error will be returned to the client. Well, I think it just blocks until the lock is released. I tested local
by 3pt14159 5y ago
> If pg_advisory_lock fails to acquire a lock, an error will be returned to the client.
Well, I think it just blocks until the lock is released. I tested locally and checked the docs[0] to confirm. In any event, this is a cool mechanism to be aware of. If you ever need pg_advisory_xact_lock and you find yourself wanting to unlock it mid-transaction and are scratching your head just introduce a nested transaction :)
[0] https://www.postgresql.org/docs/12/functions-admin.html https://www.postgresql.org/docs/12/functions-admin.html
> pg_advisory_lock locks an application-defined resource, which can be identified either by a single 64-bit key value or two 32-bit key values (note that these two key spaces do not overlap). If another session already holds a lock on the same resource identifier, this function will wait until the resource becomes available. The lock is exclusive. Multiple lock requests stack, so that if the same resource is locked three times it must then be unlocked three times to be released for other sessions' use.
- anentropic 5y ago> it just blocks until the lock is released normal Postgres locks have a https://postgresqlco.nf/doc/en/param/lock_timeout/ https://postgresqlco.nf/doc/en/param/lock_timeout/ which is max time a client will wait to acquire lock before returning an error instead SELECT FOR UPDATE queries also accept NOWAIT keyword to return error immediately if locked I would guess the former applies to advisory locks too. I think NOWAIT only applies to FOR UPDATE / FOR SHARE queries But it looks like there is try_advisory_lock method https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS https://www.postgresql.org/docs/current/functions-admin.html... that does the equivalent
- 3pt14159 5y agoRight. Well, I guess I just think the article is worded confusingly then. Because when I read it I thought "wait, it doesn't error, it waits" and I think that's an important feature.
- anentropic 5y agoYes you can choose, either to wait until your configured timeout (and if you set it to 0 it will wait indefinitely) or error immediately
- natmaka 5y agoThere is an equivalent set of non-blocking ("try") functions, their names prefixes are "pg_try_advisory". https://www.postgresql.org/docs/14/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS https://www.postgresql.org/docs/14/functions-admin.html#FUNC...