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