Re: Trouble Upgrading Postgres

The first query timed out, but the second one returned this:

0 "623140"
1 "53"
2 "12"
3 "10"
4 "1"
5 "1"
7 "1"
[null] "162"

Not quite sure what that means, but if there is just a small number of
overly-large records, I might be able to delete them. If I can find them.

On Mon, Nov 5, 2018 at 12:54 PM Daniel Verite <daniel(at)manitou-mail(dot)org>
wrote:

> Charles Martin wrote:
>
> > SELECT max(length(docfilecontents::text)) FROM docfile;
> > and after a very long time, got:
> > ERROR: invalid memory alloc request size 1636085512 SQL state: XX000
>
> It would mean that at least one row has a "docfilecontents"
> close to 0.5GB in size. Or that the size fields in certain rows
> are corrupted, although that's less plausible if you have
> no reason to suspect hardware errors.
>
> Does the following query work:
>
> SELECT max(octet_length(docfilecontents)) FROM docfile;
>
> or maybe a histogram by size in hundred of megabytes:
>
> SELECT octet_length(docfilecontents)/(1024*1024*100),
> count(*)
> FROM docfile
> GROUP BY octet_length(docfilecontents)/(1024*1024*100);
>
> Note that the error message above does not say that there's not enough
> free memory, it says that it won't even try to allocate that much, because
> 1636085512 is over the "varlena limit" of 1GB.
> AFAICS I'm afraid that this table as it is now cannot be exported
> by pg_dump, even if you had enough free memory, because any individual
> row in COPY cannot exceed 1GB in text format.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>