Re: Out of Shared Memory: max_locks_per_transaction

On Fri, Nov 9, 2012 at 2:50 PM, Eliot Gable
<egable+pgsql-general(at)gmail(dot)com> wrote:
>>> one thing that can cause this unfortunately is advisory locks eating
>>> up exactly the amount of shared memory you have. that's another thing
>>> to rule out.
>>
>> How would I rule this out?
>
> It really was filling the locks table.
>
> Using your suggestion, I managed to catch it in the process of the bad
> behavior, before it exhausted all lock entries. After some sleuthing through
> the resulting pg_locks output and my other code, I was able to isolate and
> resolve the issue. Basically, there was a call going on which tried to
> materialize a stats table based on thousands of records instead of 10 at a
> time. It was supposed to just be materializing the base rows in that table,
> all zeroed out, not based on any of the records. However, it does so using
> the same function which actually crunches numbers for the records, and it
> was coded to try all records from start of day until the function was run!

awesome...glad I could help. in the case of advisory locks, to help
reduce the likelihood of things like this happening, it's always
better to use the recently added 'xact' flavor of the functions that
release the lock at 'end of transaction' when possible.

merlin