Re: Out of Shared Memory: max_locks_per_transaction

> Another process comes along and processes records which are being inserted
> into the database. It pulls up to 10 records from a table, processes them,
> and moves those records into a "processed" table. The processing of the
> records is rather complex. To facilitate the processing, 6 temporary tables
> are created during the processing of each record, and then dropped after
> that record is completed. 8 additional tables are accessed in some way
> during the processing of each record. Each call to the processing stored
> procedure is run in its own transaction and handles only those 10 records
> at a time. This is done to keep the length of the transaction short so it
> does not block other activity in the database.
>
>
>> 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!

Thanks for the assist.

-Eliot