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*