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.ye@gtssz.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@mobime-pre:[21636]:LOG:
statement: BEGIN
2020-01-07 09:45:21 UTC:172.21.1.126(41882):daladmin@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@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