pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_clog
7+ messages / 3 participants
[nested] [flat]

* pg_clog
@ 2006-12-18 17:57  Edoardo Ceccarelli <eddy@axa.it>
  0 siblings, 3 replies; 7+ messages in thread

From: Edoardo Ceccarelli @ 2006-12-18 17:57 UTC (permalink / raw)
  To: pgsql-admin

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;

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pg_clog
@ 2006-12-18 18:04  Edoardo Ceccarelli <eddy@axa.it>
  parent: Edoardo Ceccarelli <eddy@axa.it>
  2 siblings, 0 replies; 7+ messages in thread

From: Edoardo Ceccarelli @ 2006-12-18 18:04 UTC (permalink / raw)
  To: pgsql-admin

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;
>
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pg_clog
@ 2006-12-18 18:09  Shoaib Mir <shoaibmir@gmail.com>
  parent: Edoardo Ceccarelli <eddy@axa.it>
  2 siblings, 1 reply; 7+ messages in thread

From: Shoaib Mir @ 2006-12-18 18:09 UTC (permalink / raw)
  To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-admin

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;
>
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pg_clog
@ 2006-12-18 18:26  Edoardo Ceccarelli <eddy@axa.it>
  parent: Shoaib Mir <shoaibmir@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Edoardo Ceccarelli @ 2006-12-18 18:26 UTC (permalink / raw)
  To: pgsql-admin; pgsql-jdbc@postgresql.org

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;
> >
> >
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pg_clog
@ 2006-12-18 18:48  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Edoardo Ceccarelli <eddy@axa.it>
  2 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2006-12-18 18:48 UTC (permalink / raw)
  To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pg_clog
@ 2006-12-18 19:01  Edoardo Ceccarelli <eddy@axa.it>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Edoardo Ceccarelli @ 2006-12-18 19:01 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

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
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pg_clog
@ 2006-12-18 19:55  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Edoardo Ceccarelli <eddy@axa.it>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2006-12-18 19:55 UTC (permalink / raw)
  To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2006-12-18 19:55 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-18 17:57 pg_clog Edoardo Ceccarelli <eddy@axa.it>
2006-12-18 18:04 ` Edoardo Ceccarelli <eddy@axa.it>
2006-12-18 18:09 ` Shoaib Mir <shoaibmir@gmail.com>
2006-12-18 18:26   ` Edoardo Ceccarelli <eddy@axa.it>
2006-12-18 18:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-18 19:01   ` Edoardo Ceccarelli <eddy@axa.it>
2006-12-18 19:55     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox