Re: is pg_advisory_lock() suitable for long runs

On Sat, Mar 31, 2018 at 1:49 PM, Radoslav Nedyalkov
<rnedyalkov(at)gmail(dot)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