Message-ID: <4704C1B5.6050204@archonet.com>
Date: Thu, 04 Oct 2007 11:34:29 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.6 (X11/20070728)
MIME-Version: 1.0
To: Sabin Coanda <sabin.coanda@deuromedia.ro>
CC: pgsql-performance@postgresql.org
Subject: Re: can't shrink relation
References: <fe2cj6$126i$1@news.hub.org> <fe2coo$187l$1@news.hub.org>
In-Reply-To: <fe2coo$187l$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Sabin Coanda wrote:
> sorry for the previous incomplete post. I continue with the log:

Not really a performance question, this. Perhaps general/admin lists 
would be better next time. No matter...

> NOTICE:  relation "pg_shdepend" TID 11/1: DeleteTransactionInProgress 
> 2657075 --- can't shrink relation
> NOTICE:  relation "pg_shdepend" TID 11/2: DeleteTransactionInProgress 
> 2657075 --- can't shrink relation
> .....
> NOTICE:  relation "pg_shdepend" TID 36/93: DeleteTransactionInProgress 
> 2658105 --- can't shrink relation
> 
> What happen ? What I have to do ?

This is where having a copy of the source pays off. cd to the top-level 
of your source and type:
   find . -type f | xargs grep 'shrink relation'
Amongst the translation files you'll see .../backend/commands/vacuum.c

A quick search in there reveals...

case HEAPTUPLE_DELETE_IN_PROGRESS:
  /*
   * This should not happen, since we hold exclusive lock on
   * the relation; shouldn't we raise an error? (Actually,
   * it can happen in system catalogs, since we tend to
   * release write lock before commit there.)
   */
ereport(NOTICE,
   (errmsg("relation \"%s\" TID %u/%u: DeleteTransactionInProgress %u 
--- can't shrink relation",
relname, blkno, offnum, HeapTupleHeaderGetXmax(tuple.t_data))));
do_shrinking = false;

So - it's wants to shrink a table but there is a delete in progress so 
it can't do so safely. This shouldn't happen unless it's a system table, 
and checking your error message, we're looking at pg_shdepend which is 
indeed a system table.

> I notice that I don't get such messages when I run just VACUUM without FULL 
> option.

That's because VACUUM doesn't reclaim space, it just marks blocks as 
available for re-use. If you insert 2 million rows and then delete 1 
million, your table will have 1 million gaps. A vacuum will try and 
track those gaps (see your "free space map" settings in postgresql.conf) 
whereas a vacuum-full will actually move rows around and then shrink the 
size of the file on-disk once all the gaps are together at the end of 
the file.

A vacuum full needs to lock the table, since it's moving rows around.

HTH

-- 
   Richard Huxton
   Archonet Ltd