Re: Question about locking and pg_locks

On 09/08/2016 04:30 AM, Moreno Andreo wrote:
> Hi folks! :-)
>
> This morning I was woken up by a call of a coworker screaming "Help, our
> Postgres server is throwing strange errors!"
> Not the best way to start your day...
>
> OK, to the serious part.
>
> "Strange errors" were (in postgresql-9.1-main.log)
> WARNING: out of shared memory
> ERROR: out of shared memory
> HINT: you may need to increase max_locks_per_transaction
>
> Restarting Postgresql solved the issue (for now), but that's what I'm
> wondering:
> - the greatest part of this locks are used by rubyrep (that we use to
> replicate users' databases), no new users since 3 weeks, first time
> error show up in almost 2 years
> - I read this: https://wiki.postgresql.org/wiki/Lock_Monitoring but
> still I can't figure out what to do if I need to know if I have to be
> worried or not :-)
> - I have
> OS: Ubuntu 12.04 (won't upgrade because we are leaving this server to a
> new one with Debian Jessie)
> PG: 9.1.6 (same as above, in new server ve have 9.5.4)
> RAM: 32 GB
> shared_buffers = 2GB
> max_connections=800
> max_locks_per_transaction=64 (default value)
> max_prepared_transactions = 0
>
> so, I should be able to manage 800*64 = 5120 locks, right?
>
> Now my pg_locks table has more than 6200 rows, but if I reorder them by
> pid I see that one of them has 5800 of them, and it keeps on eating locks.
> If I dig more and get pid info, its state is "<IDLE> in transaction"

So some transaction is being held open and the system cannot close out
the locks until it is done.

>
> ATM there are no locks that have granted = false.
>
> Now, question time:
> - Is there a number of pg_locks rows to be worried about? At more than
> 6000 I'm still not facing out of shared memory again
> - Is there a way to release locks of that pid without pg_terminate() it?

Look in pg_stat_activity:

https://www.postgresql.org/docs/9.5/static/monitoring-stats.html

for state 'idle in transaction' and the corresponding query. If you know
where that query is coming from you could manually either commit it or
roll it back.

>
> I tried to give most of the details, if you need more, just ask...
> Thanks
> Moreno.-
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com