On Tue, Jul 12, 2016 at 3:29 PM, Merlin Moncure <mmonc...@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 > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Jan Wieck Senior Postgres Architect