Re: large numbers of inserts out of memory strategy

Brian Crowell <brian(at)fluggo(dot)com> writes:
> On Tue, Nov 28, 2017 at 12:38 PM, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com
>> wrote:
>> So what does the script actually do? Because psql certainly is not
>> running pl/pgsql procedures on it's own. We need to understand why
>> you're getting OOM in the first place - just inserts alone should not
>> cause failures like that. Please show us more detailed explanation of
>> what the load actually does, so that we can try reproducing it.

> Perhaps the script is one giant insert statement?

It's pretty clear from the memory map that the big space consumption
is inside a single invocation of a plpgsql function:

SPI Proc: 2464408024 total in 279 blocks; 1672 free (1 chunks); 2464406352 used
PL/pgSQL function context: 537911352 total in 74 blocks; 2387536 free (4 chunks); 535523816 used

So whatever's going on here, there's more to it than a giant client-issued
INSERT (or COPY), or for that matter a large number of small ones. What
would seem to be required is a many-megabyte-sized plpgsql function body
or DO block.

Actually, the truly weird thing about that map is that the "PL/pgSQL
function context" seems to be a child of a "SPI Proc" context, whereas
it's entirely clear from the code that it ought to be a direct child of
TopMemoryContext. I have no idea how this state of affairs came to be,
and am interested to find out.

regards, tom lane