|
|
 | | From: | Alvaro Herrera | | Subject: | Re: pg_clog problem (PG version 7.4.5) | | Date: | Sat, 22 Jan 2005 15:07:35 -0300 |
|
|
 | On Sat, Jan 22, 2005 at 12:06:46PM -0500, Jim Buttafuoco wrote:
> didn't work.
>
>
> ERROR: could not access status of transaction 1076101119
> DETAIL: could not read from file "/usr/local/pgsql/data/pg_clog/0402" at offset 57344: Success
>
> any more ideas?
You need to extend the file further than byte 57344. So repeat the dd
command, with count=8.
Anyway, this situation is suspect anyway. There were bugs related to
pg_clog files not being there, but AFAIR they were triggered on segment
edges, not in the middle like this one.
What other files there are in the pg_clog directory? If they are
nowhere near the 0402 vicinity, you may have a memory or disk
corruption problem.
FWIW, 1076101119 is 1000000001000111111111111111111 in binary. Taking
the highest 1 yeilds Xid 2359295. So, what files do you have?
--
Alvaro Herrera ()
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
|
|
| | From: | Jim Buttafuoco | | Subject: | Re: pg_clog problem (PG version 7.4.5) | | Date: | Sat, 22 Jan 2005 13:14:41 -0500 |
|
|
| Alvaro,
Thanks for the reply. here is an "ls" of my pg_clog directory. The 0402 file, I created as per Joshua's directions.
I might have created one too small. If so, what size do you think I should use.
Jim
bda1:/usr/local/pgsql/data# ls -l pg_clog
total 992
-rw------- 1 postgres dba 262144 Sep 7 10:12 0000
-rw------- 1 postgres dba 262144 Nov 12 09:57 0001
-rw------- 1 postgres dba 262144 Dec 7 17:31 0002
-rw------- 1 postgres dba 204800 Jan 22 13:11 0003
-rw-r--r-- 1 postgres dba 8192 Jan 22 12:05 0402
---------- Original Message -----------
From: Alvaro Herrera
To: Jim Buttafuoco
Cc: "Joshua D. Drake" , pgsql-hackers
Sent: Sat, 22 Jan 2005 15:07:35 -0300
Subject: Re: [HACKERS] pg_clog problem (PG version 7.4.5)
> On Sat, Jan 22, 2005 at 12:06:46PM -0500, Jim Buttafuoco wrote:
> > didn't work.
> >
> >
> > ERROR: could not access status of transaction 1076101119
> > DETAIL: could not read from file "/usr/local/pgsql/data/pg_clog/0402" at offset 57344: Success
> >
> > any more ideas?
>
> You need to extend the file further than byte 57344. So repeat the dd
> command, with count=8.
>
> Anyway, this situation is suspect anyway. There were bugs related to
> pg_clog files not being there, but AFAIR they were triggered on segment
> edges, not in the middle like this one.
>
> What other files there are in the pg_clog directory? If they are
> nowhere near the 0402 vicinity, you may have a memory or disk
> corruption problem.
>
> FWIW, 1076101119 is 1000000001000111111111111111111 in binary. Taking
> the highest 1 yeilds Xid 2359295. So, what files do you have?
>
> --
> Alvaro Herrera ()
> "La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
> Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
------- End of Original Message -------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
|
|
| | From: | Tom Lane | | Subject: | Re: pg_clog problem (PG version 7.4.5) | | Date: | Sat, 22 Jan 2005 13:41:04 -0500 |
|
|
| "Jim Buttafuoco" writes:
> Thanks for the reply. here is an "ls" of my pg_clog directory. The 0402 file, I created as per Joshua's directions.
> I might have created one too small. If so, what size do you think I should use.
> bda1:/usr/local/pgsql/data# ls -l pg_clog
> total 992
> -rw------- 1 postgres dba 262144 Sep 7 10:12 0000
> -rw------- 1 postgres dba 262144 Nov 12 09:57 0001
> -rw------- 1 postgres dba 262144 Dec 7 17:31 0002
> -rw------- 1 postgres dba 204800 Jan 22 13:11 0003
> -rw-r--r-- 1 postgres dba 8192 Jan 22 12:05 0402
Given that set of pre-existing files, there is no possible way that you
really had a transaction in the range of IDs that 0402 would cover.
I agree with Alvaro's theory of a corrupted tuple. In fact it seems
plausible that the error is a single high-order 1 bit and the ID that
appears to be in the range of 0402 really belonged to file 0002.
A single dropped bit sounds more like RAM flakiness than disk problems
to me, so I'd get out the memory tester programs and start looking.
As far as recovering the data goes, you can use the usual techniques for
homing in on the location of the bad tuple and getting rid of it (or try
manually patching the XID field with a hex editor...)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
| | From: | Jim Buttafuoco | | Subject: | Re: pg_clog problem (PG version 7.4.5) | | Date: | Sat, 22 Jan 2005 14:24:52 -0500 |
|
|
| I was able to copy the table over to a temp table and truncate it with only a little loss. I will be able to recover
the lost data from backup so no big deal. I will have to schedule downtime to do the memory test with the "big" snow
storm it will not be until monday night.
thanks for the help
Jim
---------- Original Message -----------
From: Tom Lane
To: jim@contactbda.com
Cc: Alvaro Herrera , "pgsql-hackers"
Sent: Sat, 22 Jan 2005 13:41:04 -0500
Subject: Re: [HACKERS] pg_clog problem (PG version 7.4.5)
> "Jim Buttafuoco" writes:
> > Thanks for the reply. here is an "ls" of my pg_clog directory. The 0402 file, I created as per Joshua's
directions.
> > I might have created one too small. If so, what size do you think I should use.
>
> > bda1:/usr/local/pgsql/data# ls -l pg_clog
> > total 992
> > -rw------- 1 postgres dba 262144 Sep 7 10:12 0000
> > -rw------- 1 postgres dba 262144 Nov 12 09:57 0001
> > -rw------- 1 postgres dba 262144 Dec 7 17:31 0002
> > -rw------- 1 postgres dba 204800 Jan 22 13:11 0003
> > -rw-r--r-- 1 postgres dba 8192 Jan 22 12:05 0402
>
> Given that set of pre-existing files, there is no possible way that you
> really had a transaction in the range of IDs that 0402 would cover.
> I agree with Alvaro's theory of a corrupted tuple. In fact it seems
> plausible that the error is a single high-order 1 bit and the ID that
> appears to be in the range of 0402 really belonged to file 0002.
>
> A single dropped bit sounds more like RAM flakiness than disk problems
> to me, so I'd get out the memory tester programs and start looking.
>
> As far as recovering the data goes, you can use the usual techniques for
> homing in on the location of the bad tuple and getting rid of it (or try
> manually patching the XID field with a hex editor...)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
------- End of Original Message -------
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
|
|
