Error during 'vacuum analyze' - Mailing list pgsql-general

From Mark Dalphin
Subject Error during 'vacuum analyze'
Date
Msg-id 37BC6D0D.866D778D@amgen.com
Whole thread Raw
List pgsql-general
Hi,

Running PostgreSQL 6.5.1 on SGI Irix 6.5.

When I ran vacuum analyze over my database, I had an unexpected error occur:

htg=> vacuum analyze;
NOTICE:  AbortTransaction and not in in-progress state
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible.  Terminating.

I wondered about the "NOTICE: not in transaction", so I repeated the operation
from within a transaction:

htg=> begin;
BEGIN
htg=> vacuum analyze;
NOTICE:  Rel pg_statistic: TID 0/72: DeleteTransactionInProgress 48643 - can't
shrink relation
NOTICE:  Rel pg_statistic: TID 0/73: DeleteTransactionInProgress 48643 - can't
shrink relation
NOTICE:  Rel pg_statistic: TID 0/74: DeleteTransactionInProgress 48643 - can't
shrink relation

 ... many of these lines deleted ...

NOTICE:  Rel pg_statistic: TID 1/85: InsertTransactionInProgress 48643 - can't
shrink relation
NOTICE:  Rel pg_statistic: TID 1/86: InsertTransactionInProgress 48643 - can't
shrink relation
NOTICE:  Rel pg_statistic: TID 1/87: InsertTransactionInProgress 48643 - can't
shrink relation
VACUUM
htg=> commit;
END

I looked at my log file and for the first vacuum, it contains the following
lines just before it died:

Aug 19 12:35:42 5V:mahunui postgres: DEBUG:  --Relation pga_reports--
Aug 19 12:35:42 5V:mahunui postgres: DEBUG:  Pages 0: Changed 0, Reapped 0,
Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen
0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
Aug 19 12:35:42 5V:mahunui postgres: DEBUG:  --Relation pga_layout--
Aug 19 12:35:42 5V:mahunui postgres: DEBUG:  Pages 1: Changed 0, Reapped 0,
Empty 0, New 0; Tup 3: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 87, MaxLen
135; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0
sec.
Aug 19 12:35:42 5V:mahunui postgres: ERROR:  vacuum: can't destroy lock file!
Aug 19 12:35:42 5V:mahunui postgres: NOTICE:  AbortTransaction and not in
in-progress state

Looking at the second vaccum, I see many normal looking DEBUG notices with some
of the notices in the middle:

Aug 19 13:20:49 5V:mahunui postgres: DEBUG:  --Relation pg_index--
Aug 19 13:20:49 5V:mahunui postgres: DEBUG:  Pages 1: Changed 0, Reapped 1,
Empty 0, New 0; Tup 51: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 2, MinLen 116,
MaxLen 116; Re-using: Free/Avail. Space 1852/0; EndEmpty/Avail. Pages 0/0.
Elapsed 0/0 sec.
Aug 19 13:20:49 5V:mahunui postgres: DEBUG:  --Relation pg_statistic--
Aug 19 13:20:49 5V:mahunui postgres: NOTICE:  Rel pg_statistic: TID 0/72:
DeleteTransactionInProgress 48643 - can't shrink relation
Aug 19 13:20:49 5V:mahunui postgres: NOTICE:  Rel pg_statistic: TID 0/73:
DeleteTransactionInProgress 48643 - can't shrink relation
... many lines deleted ...

Any ideas about what is happening?  Should I be worried? It looks to me like a
lock was created on a table in order to vacuum it, and then when the lock could
not be removed, the vacuum crashed, leaving the system table, pg_statistic,
corrupted. Is this correct, and if so, what should I do about it?  There were no
other users accessing the DB when I ran VACUUM.

Also, should VACUUM ANALYZE be run within a BEGIN/COMMIT transaction? I would
have thought that it didn't matter as Postgresql would create a default
transaction for me.

TIA,
Mark

--
Mark Dalphin                          email: mdalphin@amgen.com
Mail Stop: 29-2-A                     phone: +1-805-447-4951 (work)
One Amgen Center Drive                       +1-805-375-0680 (home)
Thousand Oaks, CA 91320                 fax: +1-805-499-9955 (work)




pgsql-general by date:

Previous
From: Henrique Pantarotto
Date:
Subject: Trigger documentation? Need more examples.. pleeeze.. ;-)
Next
From: Henrique Pantarotto
Date:
Subject: Re: [GENERAL] Trigger documentation? Need more examples.. pleeeze.. ;-)