Tom Lane wrote:
> $subject would be bad because of potential deadlocks against other
> transactions that might try to exclusive-lock more than one table.
>
> We should be OK for actual vacuum operations, but I think that if
> autovac chooses to just ANALYZE multiple tables, it will do it in
> one transaction and accumulate locks. Probably the use_own_xacts
> logic in vacuum() ought to special-case IsAutoVacuumWorkerProcess.
Sure, it can do that. I think it's easy enough to correct this problem;
see attached patch. Should this be backpatched? Earlier releases also
fall foul of this problem AFAICT.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
? cscope.files
? cscope.out
Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.352
diff -c -p -r1.352 vacuum.c
*** src/backend/commands/vacuum.c 30 May 2007 20:11:57 -0000 1.352
--- src/backend/commands/vacuum.c 14 Jun 2007 01:17:19 -0000
*************** vacuum(VacuumStmt *vacstmt, List *relids
*** 359,372 ****
* For ANALYZE (no VACUUM): if inside a transaction block, we cannot
* start/commit our own transactions. Also, there's no need to do so if
* only processing one relation. For multiple relations when not within a
! * transaction block, use own transactions so we can release locks sooner.
*/
if (vacstmt->vacuum)
use_own_xacts = true;
else
{
Assert(vacstmt->analyze);
! if (in_outer_xact)
use_own_xacts = false;
else if (list_length(relations) > 1)
use_own_xacts = true;
--- 359,375 ----
* For ANALYZE (no VACUUM): if inside a transaction block, we cannot
* start/commit our own transactions. Also, there's no need to do so if
* only processing one relation. For multiple relations when not within a
! * transaction block, and also in an autovacuum worker, use own
! * transactions so we can release locks sooner.
*/
if (vacstmt->vacuum)
use_own_xacts = true;
else
{
Assert(vacstmt->analyze);
! if (IsAutoVacuumWorkerProcess())
! use_own_xacts = true;
! else if (in_outer_xact)
use_own_xacts = false;
else if (list_length(relations) > 1)
use_own_xacts = true;
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
