I guess I'm a couple releases late to review the "autovacuum truncate exclusive lock" patch (a79ae0bc0d454b9f2c95a), but this patch did not only affect autovac, it affects manual vacuum as well (as did the original behavior it is a modification of). So the compiler constants are misnamed, and the elog message when it triggers is misleading. (Is it also misleading to just say "vacuum"? Does it need to say "(auto)vacuum"?)
I've attached a patch that changes that. I also log the number of pages truncated at the time it gave up, as it would be nice to know if it is completely starving or making some progress. Also, I think that permanently boycotting doing autoanalyze because someone is camping out on an access share lock (or because there are a never-ending stream of overlapping locks) and so the truncation cannot be done is a bit drastic, especially for inclusion in a point release. Is there a way to have the autoanalyze happen, but then still arrange for the autovacuum to be triggered again next naptime? Cheers, Jeff
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c new file mode 100644 index 0401b7f..fda2656 *** a/src/backend/commands/vacuumlazy.c --- b/src/backend/commands/vacuumlazy.c *************** *** 75,83 **** * that the potential for improvement was great enough to merit the cost of * supporting them. */ ! #define AUTOVACUUM_TRUNCATE_LOCK_CHECK_INTERVAL 20 /* ms */ ! #define AUTOVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */ ! #define AUTOVACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */ /* * Guesstimation of number of dead tuples per page. This is used to --- 75,83 ---- * that the potential for improvement was great enough to merit the cost of * supporting them. */ ! #define VACUUM_TRUNCATE_LOCK_CHECK_INTERVAL 20 /* ms */ ! #define VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */ ! #define VACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */ /* * Guesstimation of number of dead tuples per page. This is used to *************** lazy_truncate_heap(Relation onerel, LVRe *** 1306,1313 **** */ CHECK_FOR_INTERRUPTS(); ! if (++lock_retry > (AUTOVACUUM_TRUNCATE_LOCK_TIMEOUT / ! AUTOVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL)) { /* * We failed to establish the lock in the specified number of --- 1306,1313 ---- */ CHECK_FOR_INTERRUPTS(); ! if (++lock_retry > (VACUUM_TRUNCATE_LOCK_TIMEOUT / ! VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL)) { /* * We failed to establish the lock in the specified number of *************** lazy_truncate_heap(Relation onerel, LVRe *** 1318,1333 **** */ vacrelstats->lock_waiter_detected = true; ereport(LOG, ! (errmsg("automatic vacuum of table \"%s.%s.%s\": " "could not (re)acquire exclusive " ! "lock for truncate scan", get_database_name(MyDatabaseId), get_namespace_name(RelationGetNamespace(onerel)), ! RelationGetRelationName(onerel)))); return; } ! pg_usleep(AUTOVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL); } /* --- 1318,1334 ---- */ vacrelstats->lock_waiter_detected = true; ereport(LOG, ! (errmsg("vacuum of table \"%s.%s.%s\": " "could not (re)acquire exclusive " ! "lock for truncate scan (%d pages already truncated)", get_database_name(MyDatabaseId), get_namespace_name(RelationGetNamespace(onerel)), ! RelationGetRelationName(onerel), ! vacrelstats->pages_removed))); return; } ! pg_usleep(VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL); } /* *************** count_nondeletable_pages(Relation onerel *** 1437,1443 **** elapsed = currenttime; INSTR_TIME_SUBTRACT(elapsed, starttime); if ((INSTR_TIME_GET_MICROSEC(elapsed) / 1000) ! >= AUTOVACUUM_TRUNCATE_LOCK_CHECK_INTERVAL) { if (LockHasWaitersRelation(onerel, AccessExclusiveLock)) { --- 1438,1444 ---- elapsed = currenttime; INSTR_TIME_SUBTRACT(elapsed, starttime); if ((INSTR_TIME_GET_MICROSEC(elapsed) / 1000) ! >= VACUUM_TRUNCATE_LOCK_CHECK_INTERVAL) { if (LockHasWaitersRelation(onerel, AccessExclusiveLock)) {
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers