On Sat, Jul 16, 2016 at 3:47 PM, Jan Wieck <jan@wi3ck.info> wrote:
On Tue, Jul 12, 2016 at 3:29 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
I've noticed that pl/pgsql functions/do commands do not behave well when the statement resolves and frees memory. To be clear:
FOR i in 1..1000000 LOOP INSERT INTO foo VALUES (i); END LOOP;
...runs just fine while
BEGIN INSERT INTO foo VALUES (1); INSERT INTO foo VALUES (2); ... INSERT INTO foo VALUES (1000000); END;
This sounds very much like what led to commit 25c539233044c235e97fd7c9dc600fb5f08fe065.
It seems that patch was only applied to master and never backpatched to 9.5 or earlier.
Regards, Jan
(for the curious, create a script yourself via copy ( select 'do $$begin create temp table foo(i int);' union all select format('insert into foo values (%s);', i) from generate_series(1,1000000) i union all select 'raise notice ''abandon all hope!''; end; $$;' ) to '/tmp/breakit.sql';
...while consume amounts of resident memory proportional to the number of statemnts and eventually crash the server. The problem is obvious; each statement causes a plan to get created and the server gets stuck in a loop where SPI_freeplan() is called repeatedly. Everything is working as designed I guess, but when this happens it's really unpleasant: the query is uncancellable and unterminatable, nicht gut. A pg_ctl kill ABRT <pid> will do the trick but I was quite astonished to see linux take a few minutes to clean up the mess (!) on a somewhat pokey virtualized server with lots of memory. With even as little as ten thousand statements the cleanup time far exceed the runtime of the statement block.
I guess the key takeaway here is, "don't do that"; pl/pgsql aggressively generates plans and turns out to be a poor choice for bulk loading because of all the plan caching. Having said that, I can't help but wonder if there should be a (perhaps user configurable) limit to the amount of SPI plans a single function call should be able to acquire on the basis you are going to smack into very poor behaviors in the memory subsystem.
Stepping back, I can't help but wonder what the value of all the plan caching going on is at all for statement blocks. Loops might comprise a notable exception, noted. I'd humbly submit though that (relative to functions) it's much more likely to want to do something like insert a lot of statements and a impossible to utilize any cached plans.
This is not an academic gripe -- I just exploded production :-D. merlin