I've been experimenting with Matthew T. O'Connor's pg_avd (auto vacuum
daemon), which is actually quite an interesting piece of software that
so far seems to function pretty well.
I've ported it to PG 7.2.4, which is the version I'm running on my
system, and experimenting with it has uncovered what I can only
consider a potentially serious bug in PG.
When a heavy INSERT or UPDATE load on a table is occurring (lots of
quick INSERTs or UPDATEs within a single transaction), a VACUUM
ANALYZE (or just straight VACUUM) has a really good chance (10% or so)
of causing either the INSERT/UPDATE or the VACUUM to fail with a
"deadlock detected" error. Usually the INSERT/UPDATE is what fails.
I have verified this with manual VACUUM and VACUUM ANALYZE commands
issued on the command line while a perl script is generating a heavy
load of inserts and/or updates, so this isn't pg_avd's fault.
A straight ANALYZE on the table doesn't seem to trigger the problem,
while a straight VACUUM does.
Statistics collection is enabled -- it's required for pg_avd to run --
but I suspect that this is irrelevant (what does a straight VACUUM do
with the statistics tables, if anything?).
Even dropping the table and all of its indexes and recreating it does
nothing to solve this problem, so I don't have any reason to suspect
corruption of the table itself. Corruption elsewhere is a
possibility, I suppose, but I haven't noticed any strangeness
elsewhere.
The only other thing of note is that a REALLY LONG running INSERT
... SELECT (with the source being a temporary table) is running
concurrently on a different table in a different database. That, too,
is running within a transaction, but I don't have reason to believe
that it's having an effect. When it completes I'll do more testing to
determine if it really did have an effect.
I've searched the archives for references to this and have found
nothing relevant (all references I found are to older implementations
in which VACUUM locks the table). Have I managed to uncover something
new here?
I don't have 7.3.x (or later) installed so I can't test this myself on
that or CVS tip, but I may have to if nobody else has the time.
Anyone have any suggestions on what to do about this? This problem
pretty much kills pg_avd's usefulness, at least on 7.2.x. :-(
I'll be happy to supply the Perl script I'm using to do the inserts,
if that'll help people track this down...
