pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcan't shrink relation
4+ messages / 3 participants
[nested] [flat]
* can't shrink relation
@ 2007-10-04 09:43 Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 4+ messages in thread
From: Sabin Coanda @ 2007-10-04 09:43 UTC (permalink / raw)
To: pgsql-performance
Hi there,
I have a database with lowest possible activity. I run VACUUM FULL AND I get
the following log result:
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can't shrink relation
@ 2007-10-04 09:46 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 2 replies; 4+ messages in thread
From: Sabin Coanda @ 2007-10-04 09:46 UTC (permalink / raw)
To: pgsql-performance
sorry for the previous incomplete post. I continue with the log:
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 ?
I notice that I don't get such messages when I run just VACUUM without FULL
option.
TIA,
Sabin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can't shrink relation
@ 2007-10-04 10:11 Guillaume Cottenceau <gc@mnc.ch>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
1 sibling, 0 replies; 4+ messages in thread
From: Guillaume Cottenceau @ 2007-10-04 10:11 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-performance
"Sabin Coanda" <sabin.coanda 'at' deuromedia.ro> writes:
> sorry for the previous incomplete post. I continue with the log:
>
> 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 ?
You have to use google. First match to "postgresql can't shrink
relation" (almost) returns:
http://archives.postgresql.org/pgsql-novice/2002-12/msg00126.php
--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can't shrink relation
@ 2007-10-04 10:34 Richard Huxton <dev@archonet.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2007-10-04 10:34 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-10-04 10:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-04 09:43 can't shrink relation Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-10-04 09:46 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-10-04 10:11 ` Guillaume Cottenceau <gc@mnc.ch>
2007-10-04 10:34 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox