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... -- Kevin Brown [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]