unexpected chunk number 2 (expected 0) for toast value ... in pg_toast_18536

[Karsten Hilbert]

Dear community,

we are seeing the below error on PG 9.6.16 on Debian:

(different issue from the psycopg2 one recently posted by me)

> /home/marc# pg_dump -p 5432 --username=gm-dbo --dbname=gnumed_v22 
> --compress=0 --no-sync --format=custom --file=/dev/null
> pg_dump: Ausgabe des Inhalts der Tabelle »doc_obj« fehlgeschlagen: 
> PQgetResult() fehlgeschlagen.
> pg_dump: Fehlermeldung vom Server: ERROR:  unexpected chunk number 2 
> (expected 0) for toast value 99027 in pg_toast_18536
> pg_dump: Die Anweisung war: COPY blobs.doc_obj (pk, fk_doc, seq_idx, comment, 
> fk_intended_reviewer, data, filename) TO stdout;

        (to note: column "data" is of type BYTEA)

We have been able to identify the row (there may be more)
in blobs.doc_obj which leads to the above error.

        blobs.doc_obj.pk -> 82224

We have ruled out (?) below-PG hardware problems by a
successful run of:

        cp -rv —preserve=all /var/lib/postgresql/9.6  /tmp/

We then tried

        gnumed_v22=# REINDEX TABLE pg_toast.pg_toast_18536;
        REINDEX
        gnumed_v22=# REINDEX TABLE blobs.doc_obj ;
        REINDEX
        gnumed_v22=# VACUUM ANALYZE pg_toast.pg_toast_18536;
        VACUUM
        gnumed_v22=# VACUUM FULL pg_toast.pg_toast_18536;
        VACUUM
        gnumed_v22=# VACUUM ANALYZE blobs.doc_obj ;
        VACUUM
        gnumed_v22=# VACUUM FULL blobs.doc_obj ;
        ERROR:  unexpected chunk number 2 (expected 0) for toast value 99027 in 
pg_toast_18536

We then tried to DELETE the offending row

        delete from blobs.doc_obj where pk = 82224;

but that, again, shows the "unexpected chunk" problem.

Now, what else can we try to address the problem short of
doing the

        pg_dump --exclude-table-data=blobs.doc_obj

        judicious use of COPY-FROM-with-subselect from blobs.doc_obj

        restore

dance ?

Many thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B