Clog/Transaction problems

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Clog/Transaction problems

Logan Bowers

Hello,

 

I have a really weird problem; queries against a very large table are failing against a very large table with a strange error.  Case in point:

 

dqfull=# vacuum freeze mytable;

ERROR:  could not access status of transaction 538989714

DETAIL:  could not open file "/srv/db/postgresql/pg_clog/0202": No such file or directory

 

WTF?  The only activity this table has seen is a massive data import of around ~40M rows.  Is there a way to fix clog info and make it think all transactions on it have committed?  (Note: I tried a VACUUM FREEZE after other commands were failing)  What’s a good strategy to fix this table.  I’d prefer to not have to reload it since that will take over 1 day. 

 

Logan Bowers

Reply | Threaded
Open this post in threaded view
|

Re: Clog/Transaction problems

Álvaro Herrera
On Fri, Aug 05, 2005 at 04:52:29PM -0400, Logan Bowers wrote:

> Hello,
>
>  
>
> I have a really weird problem; queries against a very large table are
> failing against a very large table with a strange error.  Case in point:
>
>  
>
> dqfull=# vacuum freeze mytable;
>
> ERROR:  could not access status of transaction 538989714
>
> DETAIL:  could not open file "/srv/db/postgresql/pg_clog/0202": No such
> file or directory

Looks like a flipped bit.  538989714 is 100000001000000101010010010010.
Have you checked your RAM?

> WTF?  The only activity this table has seen is a massive data import of
> around ~40M rows.  Is there a way to fix clog info and make it think all
> transactions on it have committed?

You could fill the pg_clog/0202 file with 0x55, which means "all
transactions committed".  You'd also have to change the nextXid counter
to beyond what the 0202 file records (pg_resetxlog can do that for you),
in order not to have problems in the future.

However, if bad RAM is the issue, you'll have other problems in the
future if not fixed.
.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings