Thread: pg_clog

pg_clog

From
"Edoardo Ceccarelli"
Date:
Hi,

just encountered this error trying to dump my db:
any ideas?

Thank you


pg_dump: ERROR:  could not access status of transaction 1629514106

DETAIL:  could not open file "pg_clog/0612": No such file or directory

pg_dump: SQL command to dump the contents of table "annuncio400" failed: PQendcopy() failed.

pg_dump: Error message from server: ERROR:  could not access status of transaction 1629514106

DETAIL:  could not open file "pg_clog/0612": No such file or directory

pg_dump: The command was: COPY public.annuncio400 (testo, telef1, pref1, rubric, tiprec, uscita, telef2, pref2, giornouscita, meseuscita, annouscita, prezzo, annofabbr, locali, codedi, codcliente, email, tipoannuncio, ka) TO stdout;

Re: pg_clog

From
"Edoardo Ceccarelli"
Date:
sorry, forgot to mention: PG 8.1.5

On 12/18/06, Edoardo Ceccarelli <eddy@axa.it> wrote:
Hi,

just encountered this error trying to dump my db:
any ideas?

Thank you


pg_dump: ERROR:  could not access status of transaction 1629514106

DETAIL:  could not open file "pg_clog/0612": No such file or directory

pg_dump: SQL command to dump the contents of table "annuncio400" failed: PQendcopy() failed.

pg_dump: Error message from server: ERROR:  could not access status of transaction 1629514106

DETAIL:  could not open file "pg_clog/0612": No such file or directory

pg_dump: The command was: COPY public.annuncio400 (testo, telef1, pref1, rubric, tiprec, uscita, telef2, pref2, giornouscita, meseuscita, annouscita, prezzo, annofabbr, locali, codedi, codcliente, email, tipoannuncio, ka) TO stdout;


Re: pg_clog

From
"Shoaib Mir"
Date:
You can try creating the missing clog file as 256K of zeros, once that is done you can now try going through the pg_dump process again.

This suggestion might help you as well --> http://archives.postgresql.org/pgsql-admin/2003-02/msg00263.php

---------------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 12/18/06, Edoardo Ceccarelli <eddy@axa.it> wrote:
Hi,

just encountered this error trying to dump my db:
any ideas?

Thank you


pg_dump: ERROR:  could not access status of transaction 1629514106

DETAIL:  could not open file "pg_clog/0612": No such file or directory

pg_dump: SQL command to dump the contents of table "annuncio400" failed: PQendcopy() failed.

pg_dump: Error message from server: ERROR:  could not access status of transaction 1629514106

DETAIL:  could not open file "pg_clog/0612": No such file or directory

pg_dump: The command was: COPY public.annuncio400 (testo, telef1, pref1, rubric, tiprec, uscita, telef2, pref2, giornouscita, meseuscita, annouscita, prezzo, annofabbr, locali, codedi, codcliente, email, tipoannuncio, ka) TO stdout;


Re: pg_clog

From
"Edoardo Ceccarelli"
Date:
Thanks for the help, I will probably try this but I would like to know how all of this was originated:

I am posting here the evolution of my db, hoping that somebody can help:

- I upgraded from 7.x to 8.1.5 about 20 days ago
- the db is about 2 / 3 giga and the application that works on it has not been changed across pg versions(apart from the jdbc driver)
- db is in production, with an average of 2000 web sessions per day launching several queries and everything was working perfectly

last night I noticed that the usual LARGE OBJECT "cleaning" wasn't been executed with the new version due to a password problem
the java code that does the LO cleaning is this:

                        db1 = DriverManager.getConnection("jdbc:postgresql://mydatabase", "zzzzzzzzz", "xxxxxxxxx");
                        Statement stat = db1.createStatement();
                        ResultSet rs = stat.executeQuery("select loid, imgoid from pg_largeobject LEFT JOIN images on pg_largeobject.loid=images.imgoid group by loid, imgoid");
                        // query semplice: select * from pg_largeobject LEFT JOIN images on pg_largeobject.loid= images.imgoid

                        db2 = DriverManager.getConnection("jdbc:postgresql://mydatabase", "zzzzzzzzz", "xxxxxxxxx");

                        // fetch the large object manager
                        LargeObjectManager lom = ((org.postgresql.PGConnection) db2).getLargeObjectAPI();
                        while (rs.next()) {
                            count++;
                            imgoid = rs.getInt("imgoid");
                            loid = rs.getInt("loid");

                            if (imgoid == 0) {
                                ccount++;
                                lom.delete(loid);
                            }
                        }
                        stat.close();


after I have activated this code I started noticing these problems (database won't reindex, dump doesn't work)
Does anybody knows if this way of dealing with LO's has changed from 7.x to 8.1.5?
Maybe this is not related at all with clog file problems?

any help apreciated
Edoardo


On 12/18/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
You can try creating the missing clog file as 256K of zeros, once that is done you can now try going through the pg_dump process again.

This suggestion might help you as well --> http://archives.postgresql.org/pgsql-admin/2003-02/msg00263.php

---------------------
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)

On 12/18/06, Edoardo Ceccarelli <eddy@axa.it> wrote:
Hi,

just encountered this error trying to dump my db:
any ideas?

Thank you


pg_dump: ERROR:  could not access status of transaction 1629514106

DETAIL:  could not open file "pg_clog/0612": No such file or directory

pg_dump: SQL command to dump the contents of table "annuncio400" failed: PQendcopy() failed.

pg_dump: Error message from server: ERROR:  could not access status of transaction 1629514106

DETAIL:  could not open file "pg_clog/0612": No such file or directory

pg_dump: The command was: COPY public.annuncio400 (testo, telef1, pref1, rubric, tiprec, uscita, telef2, pref2, giornouscita, meseuscita, annouscita, prezzo, annofabbr, locali, codedi, codcliente, email, tipoannuncio, ka) TO stdout;



Re: pg_clog

From
Tom Lane
Date:
"Edoardo Ceccarelli" <eddy@axa.it> writes:
> pg_dump: ERROR:  could not access status of transaction 1629514106
> DETAIL:  could not open file "pg_clog/0612": No such file or directory

This looks like a data corruption problem to me.  It may or may not be
significant that 1629514106 = 0x6120697a = ASCII 'zi a' (or 'a iz' if
you are on a big-endian machine).  That looks at least a little bit
plausible for Italian text, so I'm wondering if your disk drive or OS
has hiccuped and dumped a sector or two of some other file into your
database :-(.  We've seen it happen before.

Check the archives for past discussions about locating and recovering
from corrupted data.

            regards, tom lane

Re: pg_clog

From
"Edoardo Ceccarelli"
Date:
Thanks, I tought about that as well but with PG7.X we had the same machine with the same database running smoothly for more than a year
now strange things like this are happening:

2006-12-18 14:49:35 CET [14691]  : LOG:  server process (PID 13421) wasterminated by signal 11
2006-12-18 14:49:35 CET [14691]  : LOG:  terminating any other active serverprocesses
2006-12-18 14:49:35 CET [14009] db idle: WARNING:  terminating connection because of crash of another server process
2006-12-18 14:49:35 CET [14009] db idle: DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2006-12-18 14:49:35 CET [14009] db idle: HINT:  In a moment you should be able to reconnect to the database and repeat your command.

I bet a crashing process could cause that data corruption, right?


On 12/18/06, Tom Lane <tgl@sss.pgh.pa.us > wrote:
"Edoardo Ceccarelli" <eddy@axa.it > writes:
> pg_dump: ERROR:  could not access status of transaction 1629514106
> DETAIL:  could not open file "pg_clog/0612": No such file or directory

This looks like a data corruption problem to me.  It may or may not be
significant that 1629514106 = 0x6120697a = ASCII 'zi a' (or 'a iz' if
you are on a big-endian machine).  That looks at least a little bit
plausible for Italian text, so I'm wondering if your disk drive or OS
has hiccuped and dumped a sector or two of some other file into your
database :-(.  We've seen it happen before.

Check the archives for past discussions about locating and recovering
from corrupted data.

                        regards, tom lane

Re: pg_clog

From
Tom Lane
Date:
"Edoardo Ceccarelli" <eddy@axa.it> writes:
> I bet a crashing process could cause that data corruption, right?

No, Postgres is remarkably resistant to causing corruption during a
crash; it's deliberately engineered that way.  I don't say this
*couldn't* be the explanation but it shouldn't be your first guess.
Frankly I think hardware flakiness is more likely.

There are currently two known crashing bugs in 8.2.0, and both of them
will result in a simple crash, no data corruption.

            regards, tom lane