Re: Bug (#3484) - Missing pg_clog/0AE6

Alexandra Nitzschke napsal(a):
> Hi,
>
> we retrieved again a database error:
>
> pg_dump: Error message from server: ERROR:  could not access status of
> transaction 2926020847
> DETAIL:  Could not open file "pg_clog/0AE6": Datei oder Verzeichnis
> nicht gefunden.
> pg_dump: The command was: COPY public.adresse_080103 (id, adr_id,
> adt_id, name_1, name_2, name_3, land, plz, stadt, telefon, fax, kurznam
> e, strasse, wga_id, steuer_nr, report_flags, zahlziel, email,
> aenderung_ts, aenderung_uid, name_1_old) TO stdout;
>
> It occured on the same system the last error was thrown.
>
> After the last error in December, we have updated postgres with the
> patch provided by Zdenek Kotala.
> We have had a look at the log files if an error message was written by
> the catch-blocks of this patch.
> But we haven't found any.

My patch only add page header control before write. It cannot catch this
problem, because tuple header is damaged in this case. However if
following block in a table has damaged header, then problem is not in
PostgreSQL itself. Can you run command:

SELECT tid FROM public.adresse_080103

to determine where is start of corruption? And after verify next block.

Or you can try pg_check (if it is not production system). Robert
uploaded latest version two weeks ago.

> We are wondering about the table that is concerned by the error.
> It's just a backup from the original table "adresse" and was created at
> 2008/01/03.
> Since that time it wasn't used in any case, just by the nightly vacuum
> and the nightly pg_dump.

If there is not any modification from the creation I don't expect that
vacuum modified this table after initial cleanup (first day).

> We have had an error with missing pg_clog-files some months ago, but at
> that time we used postgres version 8.1.3.
> By now we are using 8.2.4.

It really seems as bugy HW, driver or kernel. Do you use same HW/SW
configuration as before?

> Is it possible to fix the problem by creating the missing file
> containing only zeros?
> I read about this on the internet.

It should work. But you lost affected tuple/row which is probably
damaged anyway.

> Because the table ist not needed any more, we could experiment to fix
> the error in this way.
> If its not possible I will drop the table.
> But it would be nice to find out the problem and not to run into the
> same problem on an essential table.

Can you compare damaged block with previous occurrence?

> I also read on the internet that there are problems with pg_clog when
> the database template1 ist not vaccumed regularly.

I think it was fixed in 8.1.7.


        Zdenek