Jaromír Talíř wrote: > Alvaro Herrera píše v Čt 24. 09. 2009 v 14:41 -0400: > > Jaromír Talíř wrote: > > > > > we are facing strange situation with exclusively locked table during > > > normal lazy vacuum. There is one big table (66GB) that is heavily > > > inserted and updated in our database. Suddenly (after backup and delete > > > of almost all records) we are not able to run VACUUM over this table > > > because after 50 minutes of work it allocate AccessExclusiveLock on this > > > table and all other connections start to timeout. > > > > What version are you running? > > We are running 8.3.5 on Ubuntu LTS 8.04. Okay, I was thinking on a fix that was applied before 8.3, so you're not being bitten by that bug. Yes, lazy vacuum does hold an exclusive lock: it does so to be able to truncate the empty pages at the end of the table. This lock is acquired only if available (vacuum will skip truncating if the table cannot be locked immediately), and it is supposed to last a short amount of time; namely the time it takes to scan the table backwards to find out how many pages to truncate. I guess in your case the amount of time is not as short as all that :-( The bug fixed was this one: revision 1.92 date: 2007-09-10 13:58:45 -0400; author: alvherre; state: Exp; lines: +6 -2; Remove the vacuum_delay_point call in count_nondeletable_pages, because we hold an exclusive lock on the table at this point, which we want to release as soon as possible. This is called in the phase of lazy vacuum where we truncate the empty pages at the end of the table. An alternative solution would be to lower the vacuum delay settings before starting the truncating phase, but this doesn't work very well in autovacuum due to the autobalancing code (which can cause other processes to change our cost delay settings). This case could be considered in the balancing code, but it is simpler this way. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
