Re: Vacuum return codes (vacuum as db integrity check?)

>> Now I've made a change and I find myself using the command "vacuumdb
>> -f foo" to perform a full vacuum. However, I've noticed that vacuum
>> full seems to return non-zero return values much more often.
>
>This is not a very useful statement. What would be useful is to see the
>error messages you are getting. (Look in the postmaster log, if your
>script is discarding stderr.)

Ok -- I redirected stdout/stderr for postmaster and vacuumdb, and the
problem finally re-occurred a few minutes ago.

This is what happens:

- database is started using "postmaster -D /foo >/tmp/dblog 2>&1 &"

- pg_dumpall is run which results in a return code of zero (Tom Lane
mentionned a few days ago that pg_dumpall might be a better "database
integrity check" than running vacuumdb)

- vacuumdb is started using "vacuumdb -f log >/tmp/vacuumdblog 2>&1"
- vacuumdb results in a return code of 1! (I'm testing how valid our
previous attempts at "integrity checks" might be)

The error received when we run "vacuumdb" is:

-> ERROR: Cannot insert a duplicate key into unique index
pg_class_oid_index
-> vacuumdb: vacuum log failed

The error logged by postmaster is exactly the same:

-> ERROR: Cannot insert a duplicate key into unique index
pg_class_oid_index

Now in the past, when vacuumdb returns non-zero return codes, we've
considered the databasebase to be hosed, and thus, would blow it away
and rebuild it. Many days of data would sometimes be lost. However,
pg_dumpall shows that we seem to still have access to the data.

Looking up "vacuum cannot insert a duplicate key into unique index" on
usenet returns quite a few postings, but no many suggestions or
solutions.

In our case, we are using 7.2 on a linux 2.2.14 kernel. Binaries were
downloaded via RPM directly from RedHat.

My questions now would be:

1) How serious is the vacuumdb error?
2) How do we fix it?
3) Is the database hosed?

Thanks in advance,

Stephane Charette