Scary behavior after power failure

Started by John Siracusaalmost 22 years ago2 messagesgeneral
Jump to latest
#1John Siracusa
siracusa@mindspring.com

We had a power failure (and a UPS failure) on our database machine. It's
back up now but some spooky stuff is happening. Here's an example:

(Names changed to protect the guilty.) First, the table:

---

CREATE TABLE foo
(
id SERIAL PRIMARY KEY,

bar INT NOT NULL REFERENCES blah (id),
...

UNIQUE(bar, baz, blee)
);

---

Now take a look at this:

---

pdm=# select * from foo where bar = 5;
id | bar
----------+------
13495206 | 5
13495206 | 5
(2 rows)

(Me: huh? How is that possible? The "id" column is the primary key!)

xxx=# delete from foo where id = 13495206;
DELETE 1

xxx=# select * from foo where bar = 5;
id | bar
----------+------
13495206 | 5
(1 row)

xxx=# delete from related_purchases where id = 13495206;
DELETE 1

xxx=# select * from foo where bar = 5;
id | bar
----------+------
(0 rows)

---

Is there any way I can "sanity check" the remaining tables for craziness
like this?

-John

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: John Siracusa (#1)
Re: Scary behavior after power failure

John Siracusa <siracusa@mindspring.com> writes:

We had a power failure (and a UPS failure) on our database machine. It's
back up now but some spooky stuff is happening.

I'm wondering if you are using IDE drives that lie about write
completion. You'd never notice until something like this happens.

Is there any way I can "sanity check" the remaining tables for craziness
like this?

dump, initdb, reload would be my suggestion.

regards, tom lane