Is it a bad idea to try to hold a transaction open indefinitely in order to hold an advisory lock? It appears to function well enough for our use case, however in performance testing the long transaction has already raised a lot of eyebrows.
Our application has several backends in a cluster connected to a database, and could use some sort of mutex to ensure at most one of those backends are responsible for some housekeeping chores. I elected to use transaction level advisory locks for this purpose, as we don't control the environments and can't rule out external connection poolers.
Each backend will periodically try to acquire the lock. When it successfully acquires the lock it will periodically re-execute the same statement in order to ensure the lock is still held (and prevent the idle transaction from being booted.) That backend will run the housekeeping logic until the backend is shut down or there is a problem reacquiring the lock, presumably just in case of a broken connection to db.
SELECT pg_try_advisory_xact_lock($1) as locked;
**edit: the transaction is used ONLY for holding this lock. It is my understanding that this keeps it MVCC friendly.