Hola lista
Encontré este video para eliminar el error
https://www.youtube.com/watch?v=4jcC-lYGM0k
pero al ejecutarlo me tira un error en la sintaxis
El scrpt mencionado permite encontrar el ctid de la fila corrupta
para poder corregirlo
El codigo es el siguiente:
DO $f$
declare
curid INT := 0;
vdata BYTEA;
badid VARCHAR;
loc VARCHAR;
begin
FOR badid IN SELECT key FROM public.archivosanexos LOOP
begin
select 'ctid - '||ctid||'pagenumber - '||(ctid::text::point) [0]::bigint
into loc
from public.archivosanexos where key = badid;
SELECT contenido || ' '
INTO vdata
FROM public.archivosanexos where key = badid;
exception
when others then
raise notice 'Block/PageNumber - % ',loc;
raise notice 'Corrupted id - % ', badid;
--return;
end;
end loop;
end;
$f$;
Copio aqui la salida de consola:
comdoc=# DO $f$
comdoc$# declare
comdoc$# curid INT := 0;
comdoc$# vdata BYTEA;
comdoc$# badid VARCHAR;
comdoc$# loc VARCHAR;
comdoc$# begin
comdoc$# FOR badid IN SELECT key FROM public.archivosanexos LOOP
comdoc$# begin
end;
comdoc$# select 'ctid - '||ctid||'pagenumber - '||(ctid::text::point)
[0]::bigint
comdoc$#
ABORT COMMENT DROP LISTEN RELEASE SHOW
ALTER COMMIT END LOAD RESET START
ANALYZE COPY EXECUTE LOCK REVOKE TRUNCATE
BEGIN CREATE EXPLAIN MOVE ROLLBACK UNLISTEN
CHECKPOINT DEALLOCATE FETCH NOTIFY SAVEPOINT UPDATE
CLOSE DECLARE GRANT PREPARE SELECT VACUUM
CLUSTER DELETE FROM INSERT REINDEX SET
comdoc$# into loc
comdoc$#
ABORT COMMENT DROP LISTEN RELEASE SHOW
ALTER COMMIT END LOAD RESET START
ANALYZE COPY EXECUTE LOCK REVOKE TRUNCATE
BEGIN CREATE EXPLAIN MOVE ROLLBACK UNLISTEN
CHECKPOINT DEALLOCATE FETCH NOTIFY SAVEPOINT UPDATE
CLOSE DECLARE GRANT PREPARE SELECT VACUUM
CLUSTER DELETE FROM INSERT REINDEX SET
comdoc$# from public.archivosanexos where key = badid;
comdoc$#
ABORT COMMENT DROP LISTEN RELEASE SHOW
ALTER COMMIT END LOAD RESET START
ANALYZE COPY EXECUTE LOCK REVOKE TRUNCATE
BEGIN CREATE EXPLAIN MOVE ROLLBACK UNLISTEN
CHECKPOINT DEALLOCATE FETCH NOTIFY SAVEPOINT UPDATE
CLOSE DECLARE GRANT PREPARE SELECT VACUUM
CLUSTER DELETE FROM INSERT REINDEX SET
comdoc$# SELECT contenido || ' '
comdoc$#
ABORT COMMENT DROP LISTEN RELEASE SHOW
ALTER COMMIT END LOAD RESET START
ANALYZE COPY EXECUTE LOCK REVOKE TRUNCATE
BEGIN CREATE EXPLAIN MOVE ROLLBACK UNLISTEN
CHECKPOINT DEALLOCATE FETCH NOTIFY SAVEPOINT UPDATE
CLOSE DECLARE GRANT PREPARE SELECT VACUUM
CLUSTER DELETE FROM INSERT REINDEX SET
comdoc$# INTO vdata
comdoc$#
ABORT COMMENT DROP LISTEN RELEASE SHOW
ALTER COMMIT END LOAD RESET START
ANALYZE COPY EXECUTE LOCK REVOKE TRUNCATE
BEGIN CREATE EXPLAIN MOVE ROLLBACK UNLISTEN
CHECKPOINT DEALLOCATE FETCH NOTIFY SAVEPOINT UPDATE
CLOSE DECLARE GRANT PREPARE SELECT VACUUM
CLUSTER DELETE FROM INSERT REINDEX SET
comdoc$# FROM public.archivosanexos where key = badid;
comdoc$# exception
comdoc$#
ABORT COMMENT DROP LISTEN RELEASE SHOW
ALTER COMMIT END LOAD RESET START
ANALYZE COPY EXECUTE LOCK REVOKE TRUNCATE
BEGIN CREATE EXPLAIN MOVE ROLLBACK UNLISTEN
CHECKPOINT DEALLOCATE FETCH NOTIFY SAVEPOINT UPDATE
CLOSE DECLARE GRANT PREPARE SELECT VACUUM
CLUSTER DELETE FROM INSERT REINDEX SET
comdoc$# when others then
comdoc$#
ABORT COMMENT DROP LISTEN RELEASE SHOW
ALTER COMMIT END LOAD RESET START
ANALYZE COPY EXECUTE LOCK REVOKE TRUNCATE
BEGIN CREATE EXPLAIN MOVE ROLLBACK UNLISTEN
CHECKPOINT DEALLOCATE FETCH NOTIFY SAVEPOINT UPDATE
CLOSE DECLARE GRANT PREPARE SELECT VACUUM
CLUSTER DELETE FROM INSERT REINDEX SET
comdoc$# raise notice 'Block/PageNumber - % ',loc;
comdoc$#
ABORT COMMENT DROP LISTEN RELEASE SHOW
ALTER COMMIT END LOAD RESET START
ANALYZE COPY EXECUTE LOCK REVOKE TRUNCATE
BEGIN CREATE EXPLAIN MOVE ROLLBACK UNLISTEN
CHECKPOINT DEALLOCATE FETCH NOTIFY SAVEPOINT UPDATE
CLOSE DECLARE GRANT PREPARE SELECT VACUUM
CLUSTER DELETE FROM INSERT REINDEX SET
comdoc$# raise notice 'Corrupted id - % ', badid;
comdoc$#
ABORT COMMENT DROP LISTEN RELEASE SHOW
ALTER COMMIT END LOAD RESET START
ANALYZE COPY EXECUTE LOCK REVOKE TRUNCATE
BEGIN CREATE EXPLAIN MOVE ROLLBACK UNLISTEN
CHECKPOINT DEALLOCATE FETCH NOTIFY SAVEPOINT UPDATE
CLOSE DECLARE GRANT PREPARE SELECT VACUUM
CLUSTER DELETE FROM INSERT REINDEX SET
comdoc$# --return;
comdoc$# end;
comdoc$# end loop;
comdoc$# end;
comdoc$# $f$;
ERROR: syntax error at or near "DO" at character 1
LINE 1: DO $f$
^
comdoc=#
Mis conocimientos son bastante limitados al respecto,
asi que agradezco cualquier ayuda al respecto.
Saludos
Baru
El vie., 25 oct. 2019 a las 9:52, baru gerardi (<soyb...@gmail.com>)
escribió:
> Hola lista
>
> Estoy intentando un respaldo de la BD y me encuentro con el siguiente
> error:
>
> pg_dump: ERROR: unexpected chunk number 5 (expected 4) for toast value
> 177979
> pg_dump: SQL command to dump the contents of table "archivosanexos"
> failed: PQendcopy() failed.
> pg_dump: Error message from server: ERROR: unexpected chunk number 5
> (expected 4) for toast value 177979
> pg_dump: The command was: COPY public.archivosanexos (nombre, cudap,
> nombreoriginal, contenido, tipo, tamanio, textoplano, pdfasociado,
> obtenidotextoplano, convertidopdf) TO stdout;
>
> Intento correr el Vacuum de la tabla y me tira esto:
>
> INFO: vacuuming "public.archivosanexos"
> INFO: index "pk_archivosanexos34" now contains 2987 row versions in 246
> pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "archanexoscud" now contains 2987 row versions in 110 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "archivosanexos": found 0 removable, 2987 nonremovable row versions
> in 889 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 15729 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.01s/0.00u sec elapsed 0.01 sec.
> INFO: vacuuming "pg_toast.pg_toast_29946"
>
>
> ERROR: invalid page header in block 4552 of relation "pg_toast_29946"
>
> Intento ver la tabla via Pgadmin y no la abre
> Usando la versión 8.1.22
> Alguna idea de como reparar la tabla?
> Soy bastante nuevo en ésto asi que agradezco cualquier ayuda
>
> Desde ya, muchas gracias
> Baru
>
>
>
> --
>
> *Enviado desde mi Nokia 1100*
>
--
*Enviado desde mi Nokia 1100*
