BUG #16194: use postgresql's pg_advisory_xact_lock error

On Tue, Jan 07, 2020 at 09:33:26AM +0000, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 16194
Logged by: andy ye
Email address: andy.ye@gtssz.net
PostgreSQL version: 9.5.11
Operating system: ubuntu 16.04
Description:

When I use postgresql's pg_advisory_xact_lock for transaction security,
especially RPC services, I often see that the lock is not released, causing
the service to block for a long time. The blocking time is about 15 minutes.

That's rather suspicious. My guess would be that you're not actually
terminating the transaction, it's staying open and so the advisory lock
is not being released. And then ~15 minutes later some sort of timeout
is being hit, closing the connection (e.g. in a connection pool, app
server or something like that) and releasing the lock.

We need to see some sort of debugging info, demonstrating that the
transaction was actually finished (committed/released) and the lock is
still being held. So if you observe this, you need to get the relevant
info from pg_locks and pg_stat_activity.

A reproducer would be very useful, of course.

regards

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