Re: is pg_advisory_lock() suitable for long runs

[Merlin Moncure]

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

is pg_advisory_lock() suitable for long runs

[Radoslav Nedyalkov]

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 ?

Thanks and Regards
Radoslav Nedyalkov