Re: BUG #16197: pg_advisory_xact_lock error

On Wed, Jan 08, 2020 at 10:38:43AM +0000, PG Bug reporting form wrote:
>The following bug has been logged on the website:
>
>Bug reference: 16197
>Logged by: andy ye
>Email address: andy(dot)ye(at)gtssz(dot)net
>PostgreSQL version: 9.5.12
>Operating system: ubuntu 16.04
>Description:
>
>error log:
>2020-01-07 09:45:21 UTC:172.21.1.126(41882):daladmin(at)mobime-pre:[21636]:LOG:
> statement: BEGIN
>2020-01-07 09:45:21 UTC:172.21.1.126(41882):daladmin(at)mobime-pre:[21636]:LOG:
> statement: SELECT pg_advisory_xact_lock(10006, 498408);
>2020-01-07 15:43:14 UTC:172.21.1.126(41882):daladmin(at)mobime-pre:[21636]:LOG:
> duration: 21472423.193 ms
>
>Hello:
>I found that the official documentation seems to introduce very little about
>pg_advisory_xact_lock. When I check the postgresql log, I find that it can't
>acquire the lock for a long time. Does this mean that the last lock was not
>released or not released completely? At present I may need some help:
>1. View the SQL that exists in the database pg_advisory_xact_lock and the
>SQL that was manually released
>2. Corresponds to the fact that I have not obtained the lock for a long
>time. Can I set the lock lifetime and automatically release it for a long
>time without release, that is, if the lock has not been released for a long
>time last time, it will not affect the acquisition of the next lock. What
>should I do? Do, since the official documentation is less, I need your
>help

This is the third thread you started on pgsql-bugs about the issues with
advisory locks you're facing. And the other two kinda got abandoned
because you have not even responded to people trying to help you.

Also, this very much does not seem like a bug - at least you have not
provided any data that would suggest it's not a simple pilot error, e.g.
because someone else is holding the lock. So pgsql-bugs may not be the
best list to discuss thisl.

Please do this:

1) Stop sending messages to pgsql-bugs unless when it's about a bug (and
you have some data to show it). Use pgsql-general instead.

2) Don't abandon the messages, respond to people trying to help you.

3) For this particular issue, it'd be good to know PID of the session
that is waiting, ID of the lock it's waiting on, and data from pg_locks.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services