BTW, here is the email thread about double-linking MemoryContext children patch, that Kevin at the end committed to master.
https://www.postgresql.org/message-id/55F2D834.8040106%40wi3ck.info Regards, Jan On Sat, Jul 16, 2016 at 3:47 PM, Jan Wieck <j...@wi3ck.info> wrote: > > > 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 > -- Jan Wieck Senior Postgres Architect http://pgblog.wi3ck.info
