Subject: Crashed database?

From: Thilo Gaul <[email-masked]>
Date: Fri, 29 Apr 94 18:33:22 MET DST

Hi to all Postgres-users,

I think I crashed my database, really don't know how:
(these are my first steps in postgres)

Query sent to backend is "retrieve (PKT.all) where PKT.x=42 "
WARN:Apr 29 18:15:37:heap_openr on PKT failed

other tables don't seem to be corrupted...
do I have to rebuild the database or look for the backup ?
every hint could be usefull;

(first I have to find out, how this could happen  (:-(

Thilo.

    +-------------------------------------------+
    |                Thilo Gaul                 |
    | Institut fuer Betriebs- und Dialogsysteme |
    |          University of Karlsruhe          |
    |              76128 Karlsruhe              |
    |                  Germany                  |
    +-------------------------------------------+
    |         Phone:  ++49 721 608-4383         |
    +-------------------------------------------+

===============================================================================
    To add/remove yourself from the POSTGRES mailing list: send mail with 
    the subject line ADD or DEL to "[email-masked]"

    If this fails, send mail to "[email-masked]" and
    a human will deal with it.  DO NOT post to the "postgres" mailing list.
===============================================================================

---

From: [email-masked] (Paul M. Aoki)
Date: Fri, 29 Apr 94 10:22:08 -0700

> other tables don't seem to be corrupted...

if that is the case, this suggests to me that the PKT table
has been "destroy"ed by the "destroy" command.

this message sounds horrifying but it actually just means "i 
couldn't open the file for this table".  (avi pfeffer got tired 
of seeing it and changed it to something more friendly in 4.2.)
a set of crashed catalogs (or catalog indices) tends to fail 
much more spectacularly than that.

test 1:
	retrieve (pg_class.all) where pg_class.relname = "PKT"
test 1a:
	retrieve (pg_class.all) where pg_class.relname ~ "PKT"
test 2:
	ls -l <your postgres directory>/data/base/<your database>/PKT

if both 1 and 2 return nothing then someone ran the "destroy" 
command.  alas.

if 1 returns something but 2 does not, then someone somehow 
dinked your file system (inode got cleared, rm PKT, whatever).
alas, again.

if 2 returns something but 1 does not, you may have lost part of
the class catalog.  if every other table ever created is still
there, then in all likelihood that row in the class table was
somehow accidentally deleted by a user.  far less likely is:
there was an extremely subtle bug (only observed once, fixed in 
4.2) where indices could "lose" tuples.  if 1 returns nothing 
but 1a returns something, you have found this bug (otherwise,
you haven't).

if both 1 and 2 return something then there is something weird 
going on.  if the table is actually corrupted you will generally
be able to scan it but scans will either crash or get weird
answers; i don't know of any failure modes where a call to 
open(2) fails because of corruption of the file *contents*!  in
all likelihood there is a file ownership/permission problem.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

---

From: "Jean T. Anderson" <[email-masked]>
Date: Fri, 29 Apr 1994 10:25:08 -0700 (PDT)

Does PKT exist in the database you are accessing? Names are case sensitive.
This query will list all classes that are not system catalogues:

     retrieve (p.relname)
     from     p in pg_class
     where    p.relkind="r" 
     and      p.relname !~ "pg_*"

 -jean

---

From: Thilo Gaul <[email-masked]>
Date: Mon, 2 May 94 16:01:31 MET DST

>Does PKT exist in the database you are accessing? Names are case sensitive.

yes, they do ....
I think, it's something about defining and removing indices, we'll find out.

Thilo.

---