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*

Reply via email to