On Sat, Mar 31, 2018 at 1:49 PM, Radoslav Nedyalkov <rnedyal...@gmail.com> wrote: > Hi all, > it's very simple and intuitive case but let me describe first. > 1. session 1 calls pg_advisory_lock(1234) and succeeds. > 2. session 2 calls pg_advisory_lock(1234) and stops on waiting. > All fine BUT pid for session2 appears already with backend_xmin in > pg_stat_activity > which means vacuum won't be able to remove rows younger than session2 > backend_xmin. > > Well, we planned to use pg_advisory_lock() as a boot phase in a hot-standby > appserver > and apparently this will be problematic as the session2 might wait for > weeks. > > Any thoughts ? Do we miss something ?
Holding a transaction open for weeks is generally not a good idea, at all. Advisory locks were invented very specifically to allow application locks to be held without involving long running transactions. Holding a session open for weeks might be ok, but any blocked lockers ought to time out and try another course of action. merlin
