Thread: could not access status of transaction 4244329

Hi:

I've migrated a couple of weeks ago from 7.4.2 to 7.4.5 and I am getting
this error after executing a query:

   Warning: pg_exec() query failed: ERROR: could not access status of
transaction 4244329 in /home/wisconsin/www/_proc/bbdd/_c_bbdd.php on
line 160
   ERROR ACCESO BASE DE DATOSERROR: could not access status of
transaction 4244329

I tried VACCUM:

   wisconsin=# VACUUM ANALYZE verbose movimientos_c_c;
   INFO:  vacuuming "public.movimientos_c_c"
   INFO:  index "movimientos_c_cod_movimient_key" now contains 3658193
row versions in 13316 pages
   DETAIL:  1397781 index row versions were removed.
   0 index pages have been deleted, 0 are currently reusable.
   CPU 0.95s/7.85u sec elapsed 90.56 sec.
   INFO:  index "movimientos_c_c_i01" now contains 3658193 row versions
in 24737 pages
   DETAIL:  1397781 index row versions were removed.
   0 index pages have been deleted, 0 are currently reusable.
   CPU 1.57s/7.28u sec elapsed 126.50 sec.
   INFO:  "movimientos_c_c": removed 1397781 row versions in 56621 pages
   DETAIL:  CPU 2.93s/5.94u sec elapsed 60.67 sec.
   INFO:  index "movimientos_c_cod_movimient_key" now contains 2260414
row versions in 13316 pages
   DETAIL:  1397780 index row versions were removed.
   0 index pages have been deleted, 0 are currently reusable.
   CPU 1.07s/6.40u sec elapsed 140.73 sec.

   INFO:  index "movimientos_c_c_i01" now contains 2260414 row versions
in 24737 pages
   DETAIL:  1397780 index row versions were removed.
   0 index pages have been deleted, 0 are currently reusable.
   CPU 1.62s/6.27u sec elapsed 199.39 sec.
   INFO:  "movimientos_c_c": removed 1397780 row versions in 56642 pages
   DETAIL:  CPU 2.85s/5.98u sec elapsed 66.71 sec.
   INFO:  index "movimientos_c_cod_movimient_key" now contains 862646
row versions in 13316 pages
   DETAIL:  1397769 index row versions were removed.
   0 index pages have been deleted, 0 are currently reusable.
   CPU 1.00s/5.21u sec elapsed 136.48 sec.
   INFO:  index "movimientos_c_c_i01" now contains 862646 row versions
in 24737 pages
   DETAIL:  1397769 index row versions were removed.
   0 index pages have been deleted, 0 are currently reusable.
   CPU 1.72s/5.13u sec elapsed 212.03 sec.
   INFO:  "movimientos_c_c": removed 1397769 row versions in 56650 pages
   DETAIL:  CPU 2.76s/5.86u sec elapsed 45.32 sec.
   ERROR:  could not access status of transaction 4244329
   DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0004": No
existe el fichero o el directorio

I tried reindexing:

   DROP INDEX movimientos_c_c_i01;
   CREATE INDEX movimientos_c_c_i01 ON movimientos_c_c (cod_empresa,
cod_per_emp, cod_movimiento, fecha_movimiento);

   ERROR:  could not access status of transaction 4244329
   DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0004": No
existe el fichero o el directorio



Any help will be much appreciated.
Ruben.

ruben20@superguai.com wrote:
> Hi:
>
> I've migrated a couple of weeks ago from 7.4.2 to 7.4.5 and I am getting
> this error after executing a query:
>
>   Warning: pg_exec() query failed: ERROR: could not access status of
> transaction 4244329 in /home/wisconsin/www/_proc/bbdd/_c_bbdd.php on
> line 160
>   ERROR ACCESO BASE DE DATOSERROR: could not access status of
> transaction 4244329
>

[SNIP]

>
> I tried reindexing:
>
>   DROP INDEX movimientos_c_c_i01;
>   CREATE INDEX movimientos_c_c_i01 ON movimientos_c_c (cod_empresa,
> cod_per_emp, cod_movimiento, fecha_movimiento);
>
>   ERROR:  could not access status of transaction 4244329
>   DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0004": No
> existe el fichero o el directorio
>

create a empty file:

# touch /usr/local/pgsql/data/pg_clog/0004

at this point postgres will complain about the fact that an offset is missing,

at this point fill with 0 your file ( blocks of 8K ) till reach that offset reclaimed.



Regards
Gaetano Mendola

"ruben20@superguai.com" <ruben20@superguai.com> writes:
>    ERROR:  could not access status of transaction 4244329
>    DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0004": No
> existe el fichero o el directorio

What files actually appear in /usr/local/pgsql/data/pg_clog/ ?

The standard advice for working around this sort of thing is to create a
dummy pg_clog/0004 file and fill it with 256K of zeroes, so that the
VACUUM will decide that the affected row is dead.  However it would be
a good idea to first try to understand what's gone wrong.  Is this an
isolated dropped-bit in a transaction status field, or a symptom of more
general corruption in the table?  You could try to determine which page
of the table contains the corrupted row, and then dump out that page
with pg_filedump for visual analysis.  (See past discussions of
corrupted-data recovery in the list archives for details.)

            regards, tom lane