Horton

Thread

  1. cannot vacuum a database !

    Ange Michel POZZO <poange@technologist.com> — 2000-08-29T08:23:55Z

    hi all,

i use [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2]  on a
linux mandrake 7.0

after i have drop some tables and index i don't use, i c'ant do a vacuum on the
database.

here is the error :

[me@www me]$ psql mydb -u
Username: postgres
Password:
 
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2]
 
   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: mydb
 
mydb=> vacuum ;
NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1021) IS NOT THE SAME AS HEAP' (1022)
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.
[me@www me]$

...


mydb=> \d pg_proc_prosrc_index
Table    = pg_proc_prosrc_index
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| prosrc                           | text                             |   var |
+----------------------------------+----------------------------------+-------+
mydb=>


what should i do now ?
is my database corrupt ?

any idea ?

thanks for any help !

ANGE


--

******************************************************************************
POZZO Ange Michel
mail : ange@alpinfo.fr
Administrateur - Dveloppeur
ALPINFO
617 Rue Denis Papin
73290 La Motte Servolex
Savoie - France
tel : 04 79 26 06 28
fax : 04 79 25 68 36

Zonecommerce, l'annuaire franais du commerce lectronique

- Plusieurs centaines de magasins rfrencs pour tous vos achats
sur internet, tous avec paiements scuriss en ligne
- Des promotions proposes par les boutiques
- Vente aux enchres, forum de discussion
- Des actualits, sports et loisirs, cinma, horoscope ...
- Les iddes d'olivia, le site du mois, l'interview
- Des liens, des conseils ...

http://www.zonecommerce.com/

******************************************************************************

  2. Re: [GENERAL] cannot vacuum a database !

    Marc G. Fournier <scrappy@hub.org> — 2000-08-29T12:05:25Z

    try 'vacuum verbose;' ... if that doesn't work, try doing a pg_dump/reload
... and, above all ... upgrade to v7.x as soon as possible ...

On Tue, 29 Aug 2000, Ange Michel POZZO wrote:

> hi all,
> 
> i use [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2]  on a
> linux mandrake 7.0
> 
> after i have drop some tables and index i don't use, i c'ant do a vacuum on the
> database.
> 
> here is the error :
> 
> [me@www me]$ psql mydb -u
> Username: postgres
> Password:
>  
> Welcome to the POSTGRESQL interactive sql monitor:
>   Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2]
>  
>    type \? for help on slash commands
>    type \q to quit
>    type \g or terminate with semicolon to execute query
>  You are currently connected to the database: mydb
>  
> mydb=> vacuum ;
> NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1021) IS NOT THE SAME AS HEAP' (1022)
> 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.
> [me@www me]$
> 
> ...
> 
> 
> mydb=> \d pg_proc_prosrc_index
> Table    = pg_proc_prosrc_index
> +----------------------------------+----------------------------------+-------+
> |              Field               |              Type                | Length|
> +----------------------------------+----------------------------------+-------+
> | prosrc                           | text                             |   var |
> +----------------------------------+----------------------------------+-------+
> mydb=>
> 
> 
> what should i do now ?
> is my database corrupt ?
> 
> any idea ?
> 
> thanks for any help !
> 
> ANGE
> 
> 
> --
> 
> ******************************************************************************
> POZZO Ange Michel
> mail : ange@alpinfo.fr
> Administrateur - Dveloppeur
> ALPINFO
> 617 Rue Denis Papin
> 73290 La Motte Servolex
> Savoie - France
> tel : 04 79 26 06 28
> fax : 04 79 25 68 36
> 
> Zonecommerce, l'annuaire franais du commerce lectronique
> 
> - Plusieurs centaines de magasins rfrencs pour tous vos achats
> sur internet, tous avec paiements scuriss en ligne
> - Des promotions proposes par les boutiques
> - Vente aux enchres, forum de discussion
> - Des actualits, sports et loisirs, cinma, horoscope ...
> - Les iddes d'olivia, le site du mois, l'interview
> - Des liens, des conseils ...
> 
> http://www.zonecommerce.com/
> 
> ******************************************************************************
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

  3. Re: Re: [GENERAL] cannot vacuum a database !

    Ange Michel POZZO <poange@technologist.com> — 2000-08-29T16:11:24Z

    ok Hermit Hacker

here is the vacuum verbose :


mydb=> vacuum verbose ;
NOTICE:  --Relation pg_type--
NOTICE:  Pages 2: Changed 0, Reapped 1, Empty 0, New 0; Tup 142: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 2, MinLen 105, MaxLen 109; Re-using: Free/Avail. Space 256/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
NOTICE:  Index pg_type_typname_index: Pages 4; Tuples 142: Deleted 0. Elapsed 0/0 sec.
NOTICE:  Index pg_type_oid_index: Pages 2; Tuples 142: Deleted 0. Elapsed 0/0 sec.
NOTICE:  --Relation pg_attribute--
NOTICE:  Pages 11: Changed 0, Reapped 1, Empty 0, New 0; Tup 856: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 2, MinLen 97, MaxLen 97; Re-using: Free/Avail. Space 272/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
NOTICE:  Index pg_attribute_attrelid_index: Pages 7; Tuples 856: Deleted 0. Elapsed 0/0 sec.
NOTICE:  Index pg_attribute_relid_attnum_index: Pages 9; Tuples 856: Deleted 0. Elapsed 0/0 sec.
NOTICE:  Index pg_attribute_relid_attnam_index: Pages 24; Tuples 856: Deleted 0. Elapsed 0/0 sec.
NOTICE:  --Relation pg_proc--
NOTICE:  Pages 26: Changed 0, Reapped 5, Empty 0, New 0; Tup 1022: Vac 7, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 145, MaxLen 4593; Re-using: Free/Avail. Space 40244/40244; EndEmpty/Avail. Pages 0/5. Elapsed 0/0 sec.
NOTICE:  Index pg_proc_prosrc_index: Pages 11; Tuples 1021: Deleted 0. Elapsed 0/0 sec.
NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1021) IS NOT THE SAME AS HEAP' (1022)
NOTICE:  Index pg_proc_proname_narg_type_index: Pages 17; Tuples 1022: Deleted 0. Elapsed 0/0 sec.
NOTICE:  Index pg_proc_oid_index: Pages 5; Tuples 1022: Deleted 0. Elapsed 0/0 sec.
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.
[me@me me]$


If someone has an idea ?

i read in a post archive that a can not recover this error, i must
dump/restore :(

i think id'better upgrade to 7 ... but this is the prod server, i can't shedule
an upgrade like that  for now :(

the database seems to work,  cannot do a vacuum,  but seems to be slower,
so i will upgrade to 7 in 1 or 2 days


thanks

POZZO ANGE

  4. Re: [BUGS] Re: cannot vacuum a database !

    Steve Wolfe <steve@iboats.com> — 2000-08-29T16:47:51Z

    > here is the vacuum verbose :

> 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.

     Well, here's one possibility.  If you're still using 6.5.x or before,
then if you run out of disk space (even temporarily), Postgres can hork up
the table exactly as you describe.  You can select from it, insert into it,
etc., but can't vacuum it - nor can you describe (\d) it.  A pg_dump won't
work on it, either.

  The only way that I was able to rescue the data was to write a Perl script
to select all of the data, and format it into a flat file with a bunch of
SQL statements - basically, re-creating pg_dump.  There may have been a more
elegant way of doing it (I think pg_dump has an option to select instead of
copy), but I didn't have a lot of time to research it at the time.

 : )

  Upgrading to 7 is a very good thing, even if for nothing else than it has
more protection if you run out of disk.  And even if you have plenty of
disk, it only takes one junior programmer to mess it all up. : )

steve

  5. Re: Re: [GENERAL] cannot vacuum a database !

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-29T22:32:31Z

    It sounds to me like your index on pg_proc(prosrc) is corrupted, which
unfortunately is pretty easy to do in 6.5 and before --- there weren't
any defenses against overlength index entries, which meant a large
function definition could break it.

Fortunately that index isn't actually used for much of anything (in fact
we took it out of 7.0).  So you should be able to limp along until you
can schedule a dump/reload, preferably together with an update to 7.0.
Just don't vacuum pg_proc meanwhile.  You can vacuum individual user
tables if you need to, but don't do a system-wide vacuum.

			regards, tom lane