All,
I've taken a stab at creating a reproduceable test case based on the
characterisitics of the production issues I'm seeing. But clearly
there's an element I'm missing, because I'm not able to produce the bug
with a pgbench-based test case.
My current test has FKs, updating both FK'd tables, updating both
indexed and non-indexed columns, and doing multiple updates in the same
transaction, and lock-blocking.
Files are attached in case someone has better ideas. queue_bench.sql is
the setup file, and then you do:
pgbench -n -T 600 -c 15 -j 5 -f queue_adder.bench -f queue_worker.bench
-f queue_worker.bench -f queue_worker.bench -f queue_worker.bench {dbname}
... or whatever levels of c and j make sense on your hardware.
FWIW, this particular test case might make a nice destruction test case
for replication testing, too.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com