Matthew Schumacher <matt.s@aptalaska.net> writes:
> Here is the proc that has very inconsistent (anywhere from 25ms to
> 8000ms) performance:
> ...
> This setup, with concurrency, is returning very inconsistent query
> performance. Sometimes its very fast, other times it's slow and waits.
> This makes me think I have a table locking issue, but I'm not sure
> since pg_locks rarely reports more than this:
Yeah, I suspect a locking issue too, but you won't find out what it is
unless you can capture the content of pg_locks at a time when the proc
is being delayed.
You could try making the proc do
LOCK TABLE ... IN ROW EXCLUSIVE MODE NOWAIT
inside an exception block, catching the error, and logging the contents
of pg_locks to someplace.
regards, tom lane