Обсуждение: Scary behavior after power failure

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

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