Re: Vacuum returns error

PG 7.2 and RH 7.2

Developer dropped ~30+ tables from 4 different DBs on the same
server. Queiries began to fail: unable to locate file;
/var/lib/pgsql/pg_clog/0006.

Immediately VACUUMed DBs. Queiries began returning OK.

Minutes later vacuumdb -z DB_1, returned OK.

Attempted vacuumdb -z DB_2, failed with error similar to above.

Where can I locate more comprehensive error message
information? Any recommendations would be greatly appreciated.

Thanks

- Bill

(* NOTE: I realize that answering your own posts is like talking to
yourself, please forgive me... *)

On 1 Apr 2002 at 14:29, William Meloney wrote:

> PG 7.2 and RH 7.2
>
> Developer dropped ~30+ tables from 4 different DBs on the same
> server. Queiries began to fail: unable to locate file;
> /var/lib/pgsql/pg_clog/0006.
>
> Immediately VACUUMed DBs. Queiries began returning OK.
>
> Minutes later vacuumdb -z DB_1, returned OK.
>
> Attempted vacuumdb -z DB_2, failed with error similar to above.

pg_dump DB_2 failed citing a specific table as the point of failure. A
review of the dump file ( DB_2.txt.gz) gave the last record number of
the table BEFORE it failed. We then executed a DELETE query
and removed the offending record in the cited table.

An immediate pg_dump completed successfully. Confidence is high
that a vacuum will complete as well.

>
> Where can I locate more comprehensive error message
> information? Any recommendations would be greatly appreciated.
>
> Thanks
>
> - Bill
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

"William Meloney" <bmeloney(at)mindspring(dot)com> writes:
> PG 7.2 and RH 7.2
> Developer dropped ~30+ tables from 4 different DBs on the same
> server. Queiries began to fail: unable to locate file;
> /var/lib/pgsql/pg_clog/0006.

Hmm. What files do you actually have in /var/lib/pgsql/pg_clog?
(I'd like to see an ls -l of that directory if possible; the sizes
and mod dates might be useful clues.) Another useful bit of info
would be "select * from pg_database".

We've seen two or three reports of errors like this, which make me
think there's a corner-case bug or two lurking in the clog code.
Haven't been able to get enough data to track it down yet.

regards, tom lane

On 2 Apr 2002 at 2:09, Tom Lane wrote:

> "William Meloney" <bmeloney(at)mindspring(dot)com> writes:
> > PG 7.2 and RH 7.2
> > Developer dropped ~30+ tables from 4 different DBs on the same
> > server. Queiries began to fail: unable to locate file;
> > /var/lib/pgsql/pg_clog/0006.
>
> Hmm. What files do you actually have in /var/lib/pgsql/pg_clog?
> (I'd like to see an ls -l of that directory if possible; the sizes
> and mod dates might be useful clues.)
total 252
-rw------- 1 postgres postgres 253952 apr 3 13:47 0000

Another useful bit of info
> would be "select * from pg_database".
>
database datdba encoding datistemplate datallowconn datlassysoid datvacuumxi
qc 100 0 false true 16554 964425
plant1 100 0 false true 16554 964106
template1 1 0 true true 16554 1810
template0 1 0 true false 16554 49
plant2 100 0 false true 16554 964326
plant3 100 0 false true 16554 964264

Best of luck

- William

"William Meloney" <bmeloney(at)mindspring(dot)com> writes:
> Developer dropped ~30+ tables from 4 different DBs on the same
> server. Queiries began to fail: unable to locate file;
> /var/lib/pgsql/pg_clog/0006.
>>
>> Hmm. What files do you actually have in /var/lib/pgsql/pg_clog?
> [ info snipped ]

Okay. It seems quite clear that you've executed less than a million
transactions so far in this installation; so 0000 is the only clog
segment that does or should exist, and there's no valid reason
for anything to be trying to access segment 0006. I do not think
this could be a clog logic error as I first suspected; it's probably
more like a garden-variety data corruption problem, ie, something
dropped a bit and there's now an xmin or xmax field someplace that
contains a bogus transaction ID up in the six-million-something range.

My advice is to try to track down and get rid of the bad tuple.

regards, tom lane