Re: pg_restore out of memory

Francisco Reyes <lists(at)stringsutils(dot)com> writes:
> Also the error is about running out of memory when trying to allocate 84MB.
> The default FreeBSD limit is 512MB so 84MB is well below that.

Well, no, it's not.

I traced through a test case involving loading a multi-megabyte text
value, and what I find is that there are actually five concurrently
allocated multi-megabyte areas:

* copy's line buffer
* copy's field buffer
* textin's result value
* heap_form_tuple result
* output workspace for toast_compress_datum

What's more, because the line and field buffers are StringInfos that are
intended for reuse across multiple lines/fields, they're not simply made
equal to the exact size of the big field. They're rounded up to the
next power-of-2, ie, if you've read an 84MB field during the current
COPY IN then they'll be 128MB apiece. In short, COPY is going to need
508MB of process-local RAM to handle this row. That's on top of the
few megabytes of random housekeeping info that a backend keeps around.

And it's entirely likely that your 450MB of shared buffers (plus
whatever else is in your shared memory area) gets counted against each
process' ulimit, too.

In short, you need a bigger per-process memory allowance.

BTW: I think if you were using different client and server encodings
there would be yet a sixth large buffer involved, for the output of
pg_client_to_server.

Basically Postgres is designed on the assumption that you have room for
multiple copies of the widest field you want to process. I have not
bothered to see how many copies of the field would be involved in a
"SELECT * FROM ..." operation, but I can assure you it'd be several.
If you can't afford a factor of 5 or 10 headroom on your widest fields,
you should look at storing them as large objects so you can store and
fetch them a chunk at a time.

regards, tom lane