On Tue, Sep 27, 2011 at 20:01, David North <dtn@corefiling.co.uk> wrote:
> testdb=# select * from problem_table;
> ERROR: invalid memory alloc request size 2003676411
> Is there some reason why my data can be stored in <1GB but triggers the
> allocation of 2GB of memory when I try to read it back? Is there any setting
> I can change or any alternate method of reading I can use to get around
> this?
I guess that it's converting the whole value to the hex-escaped bytea
format so that doubles its size. The JDBC driver probably doesn't
support tarnsferring bytea values in binary.
I've heard that some people are using substr() to read bytea values in
small chunks. Theoretically TOAST can support this in constant time
(independent of total value size or offset), but I don't know about
the implementation. In any case, it's worth a try.
It *might* help to ALTER column SET STORAGE EXTERNAL, to disable TOAST
compression, but it could also make things worse.
More details here: http://www.postgresql.org/docs/9.0/static/storage-toast.html
Regards,
Marti