Scary behavior after power failure

[John Siracusa]

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

John Siracusa <si******@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend