Using your idea, I found the records over 400MB, and deleted them in the application. The largest two were in inactive matters, and the third is still available elsewhere if needed. I'll try pg_dump again after work hours and see if it works now. Hopefully it will, now that I've
Adrian, I'll try changing shared_buffers the next time I can restart postgres, at least if deleting the largest records and adding VM hasn't worked.
> 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.
The query was:
SELECT octet_length(docfilecontents)/(1024*1024*100), count(*) FROM docfile GROUP BY octet_length(docfilecontents)/(1024*1024*100);
The results above show that there is one document weighing over 700 MB (the first column being the multiple of 100MB), one between 500 and 600 MB, one between 400 MB and 500 MB, 10 between 300 and 400 MB, and so on.
The hex expansion performed by COPY must allocate twice that size, plus the rest of the row, and if that resulting size is above 1GB, it will error out with the message you mentioned upthread: ERROR: invalid memory alloc request size <some value over 1 billion>. So there's no way it can deal with the contents over 500MB, and the ones just under that limit may also be problematic.
A quick and dirty way of getting rid of these contents would be to nullify them. For instance, nullify anything over 400MB:
UPDATE docfile SET docfilecontents=NULL WHERE octet_length(docfilecontents) > 1024*1024*400;
Or a cleaner solution would be to delete them with the application if that's possible. You may turn the above query into a SELECT that retrieve the fields of interest (avoid SELECT * because of the huge column).
Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
