Thread: BUG #14403: Large numbers of CREATE OR UPDATE function causes memory exhaustion
BUG #14403: Large numbers of CREATE OR UPDATE function causes memory exhaustion
From
will.pearson@digital.cabinet-office.gov.uk
Date:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDQwMwpMb2dnZWQgYnk6ICAg ICAgICAgIFdpbGwgUGVhcnNvbgpFbWFpbCBhZGRyZXNzOiAgICAgIHdpbGwu cGVhcnNvbkBkaWdpdGFsLmNhYmluZXQtb2ZmaWNlLmdvdi51awpQb3N0Z3Jl U1FMIHZlcnNpb246IDkuNi4xCk9wZXJhdGluZyBzeXN0ZW06ICAgT1NYIEVs IENhcGl0YW4gKGFsc28gaW4gTGludXggQ29udGFpbmVycy9SRFMpCkRlc2Ny aXB0aW9uOiAgICAgICAgCgpTdW1tYXJ5Og0KDQpXZSBoYWQgYSBwcm9ibGVt IGluIHByb2R1Y3Rpb24gd2hlcmUgUkRTIGZhaWxlZCBvdmVyIGF2YWlsYWJp bGl0eSB6b25lcy4gV2UKbGF0ZXIgZm91bmQgdGhpcyB3YXMgZHVlIHRvIGEg bWVtb3J5IG92ZXJmbG93LiBEb2luZyBtb3JlIGRpZ2dpbmcgaXQgc2VlbXMK dG8gZHVlIHRvIGxhcmdlIG51bWJlcnMgb2YgQ1JFQVRFIE9SIFVQREFURSBm dW5jdGlvbnMgZG9uZSBieSBzZXF1ZWxpemUgKGEKbm9kZSBPUk0pIGR1cmlu ZyBzZXNzaW9uIGNyZWF0aW9uLiBXZSd2ZSBtaXRpZ2F0ZWQgdGhlIHByb2Js ZW0uDQoNCldlJ3ZlIG5vdyBpc29sYXRlZCB0aGUgcHJvYmxlbSBmcm9tIG91 ciBjb2RlIGFuZCBoYXZlIGNyZWF0ZWQgdGhlIGZvbGxvd2luZwpweXRob24g c2NyaXB0IGRvaW5nIHN0YW5kYXJkIHNxbC4gICBCdXQgd2UgY291bGQgZG8g d2l0aCBtb3JlIHVuZGVyc3RhbmRpbmcKb2YgcG9zdGdyZXNxbCBpbnRlcm5h bHMgaG93IHRvIGRpZyBmdXJ0aGVyIChhbmQgYW55IHR1bmluZyBwb3NzaWJp bGl0aWVzKS4KDQoNCi0tLS0NClB5dGhvbiBzY3JpcHQgdG8gcmVwbGljYXRl IHRoZSBwcm9ibGVtcw0KDQppbXBvcnQgcHN5Y29wZzINCmltcG9ydCBzeXMN Cg0KZGVmIGNyZWF0ZV9vcl9yZXBsYWNlIChuLCBjdXIsIGNvbm4pOg0KICBw cmludCAoIkRvaW5nIGFjdGlvbiAiICsgc3RyKG4pKSANCiAgY3VyLmV4ZWN1 dGUoIkNSRUFURSBPUiBSRVBMQUNFIEZVTkNUSU9OIHBnX3RlbXAudGVzdGZ1 bmMoT1VUIHJlc3BvbnNlCnRlc3QsIE9VVCBzZXF1ZWxpemVfY2F1Z2h0X2V4 Y2VwdGlvbiB0ZXh0KSBSRVRVUk5TIFJFQ09SRCBBUyAkZnVuY19hNyIgKwpz dHIobikgKyAiJCBCRUdJTiBJTlNFUlQgSU5UTyB0ZXN0IChcImlkXCIpIFZB TFVFUyAoIiArIHN0cihuKSArICIpClJFVFVSTklORyAqIElOVE8gcmVzcG9u c2U7IEVYQ0VQVElPTiBXSEVOIHVuaXF1ZV92aW9sYXRpb24gVEhFTiBHRVQg U1RBQ0tFRApESUFHTk9TVElDUyBzZXF1ZWxpemVfY2F1Z2h0X2V4Y2VwdGlv biA9IFBHX0VYQ0VQVElPTl9ERVRBSUw7IEVORCAkZnVuY19hNyIKKyBzdHIo bikgKyAiJCBMQU5HVUFHRSBwbHBnc3FsOyIpDQogIGN1ci5leGVjdXRlKCJT RUxFQ1QgKHRlc3RmdW5jLnJlc3BvbnNlKS4qLAp0ZXN0ZnVuYy5zZXF1ZWxp emVfY2F1Z2h0X2V4Y2VwdGlvbiBGUk9NIHBnX3RlbXAudGVzdGZ1bmMoKTsi KQ0KICBjdXIuZXhlY3V0ZSgiRFJPUCBGVU5DVElPTiBJRiBFWElTVFMgcGdf dGVtcC50ZXN0ZnVuYygpOyIpDQogIGNvbm4uY29tbWl0KCkNCg0KZGVmIGxv dHNfb2ZfY3JlYXRlX29yX3JlcGxhY2UodCwgY3VyLCBjb25uKSA6DQogIHBy aW50ICgiRG9pbmcgbG90cyBvZiBhY3Rpb25zIGZvciB0IiArIHN0cih0KSkg DQogIGZvciBuIGluIHJhbmdlKDEsIDEwMDAwMDAwMCk6DQogICAgIGNyZWF0 ZV9vcl9yZXBsYWNlKG4rdCwgY3VyLCBjb25uKQ0KICBwcmludCAiRmluaXNo ZWQiDQoNCmNvbm4gPSBwc3ljb3BnMi5jb25uZWN0KCJkYm5hbWUgPXRlc3Rk YiIpDQoNCmN1ciA9IGNvbm4uY3Vyc29yKCkNCmN1ci5leGVjdXRlKCJERUxF VEUgRlJPTSB0ZXN0OyIpDQpsb3RzX29mX2NyZWF0ZV9vcl9yZXBsYWNlKGlu dChzeXMuYXJndlsxXSksIGN1cixjb25uKQ0KDQojVXNhZ2UjDQpDcmVhdGUg dGhlIHJlcXVpcmVkIHRhYmxlcyBhbmQgZGF0YWJhc2UgdGhlbiBydW4gaXQg d2l0aCBhIHN0YXJ0aW5nIHBvaW50LgpUaGUgY29kZSBpcyBkZXNpZ25lZCBz byB0aGF0IHlvdSBjYW4gcnVuIG11bHRpcGxlIGNvcGllcyBhdCBkaWZmZXJl bnQKcG9pbnRzLg0KDQpMb29rIGF0IHRoZSBtZW1vcnkgdXNhZ2UgZ28gdXAg b24gdGhlIHB5dGhvbiBwcm9jZXNzZXMuDQoNCktpbGxpbmcgdGhlIHB5dGhv biBwcm9jZXNzIGRvaW5nIHRoZSB1cGRhdGVzIG1lYW5zIHRoZSBtZW1vcnkg aXMgZnJlZWQuIE9uCnNvbWUgdGVzdHMgYWdhaW5zdCBvdXIgZGV2IGVudmly b25tZW50cyBpdCBhcHBlYXJlZCB0aGF0IHNpbXBseSBzdG9wcGluZwpkb2lu ZyB0aGUgcmVxdWVzdHMgdG8gY3JlYXRlIHNlc3Npb25zIGFsbG93ZWQgdGhl IHN5c3RlbSB0byByZWNvdmVyIHRoZQptZW1vcnkuDQoNCi0tLS0NCk1vcmUg ZGV0YWlsczoNCg0KU2VlbiBpbiBSRFMgKDkuNC40KQ0KbG9jYWxseSBpbiBN YWMgOS41LjQNCk9uIExpbnV4IGluIGEgY29udGFpbmVyIDkuNC40DQoNClNl cXVlbGl6ZSBjb2RlIHRoYXQgc2hvd3MgaXQgaW4gdGhlIHdpbGQ6DQoNCmh0 dHBzOi8vZ2l0aHViLmNvbS9zZXF1ZWxpemUvc2VxdWVsaXplL2Jsb2IvOTVk M2E1NzEzNDk5YWI5MGIxOWQ3YTMxMmI4Y2I4NzhiMjZlYjFiNC9saWIvZGlh bGVjdHMvYWJzdHJhY3QvcXVlcnktZ2VuZXJhdG9yLmpzI0wxNTQNCg0KLS0t LQ0KDQpIeXBvdGhlc2VzIEkndmUgbG9va2VkIGF0IC0gUmVhc29uIEkgZG9u J3QgdGhpbmsgdGhhdCBpcyB0aGUgcHJvYmxlbToNCg0KRnVuY3Rpb25zIGFy ZW4ndCBiZWluZyBjbGVhbmVkIHVwIHByb3Blcmx5IC0gXGRmIGRvZXNuJ3Qg c2hvdyBtb3JlIGZ1bmN0aW9ucwpkdXJpbmcgdGhlIHJ1bi4gQWx0aG91Z2gg YSBjb2xsZWFndWUgcG9pbnRzIG91dCB0aGF0IHRoZXNlIHRlbXAgZnVuY3Rp b25zCmFyZSBwZXIgY29ubmVjdGlvbiwgc28gd291bGRuJ3Qgc2hvdyB1cC4g V2UncmUgbm90IHN1cmUgaG93IHRvIHNlZSB0aGVtLg0KTm8gdmFjdXVtcyBo YXBwZW5pbmcgLSBUaGVyZSBhcmUgdmFjdXVtcyBoYXBwZW5pbmcgZHVyaW5n IG1lbW9yeSB1c2FnZQppbmNyZWFzZS4NCg0KQ3VycmVudCBoeXBvdGhlc2lz Og0KDQpUaGUgcHJvY2VzcyB0aGF0IGZyZWVzIHVwIG1lbW9yeSBmb3IgdGVt cG9yYXJ5IGZ1bmN0aW9ucyBkb2Vzbid0IGhhdmUgYQpjaGFuY2UgdG8gYWN0 IGJlY2F1c2Ugb2YgdGhlIGNvbnNpc3RlbnQgbnVtYmVyIG9mIGluc2VydHMg YW5kIGRlbGV0aW9ucwpwcmUtZW1wdGluZyBpdC4gSXQgaXMgbm90IGEgbm9y bWFsIHZhY3V1bSB0byBkZWFsIHdpdGggaXQuIA0KDQpUaGFua3MgZm9yIGFu eSBoZWxwLA0KDQogV2lsbCBQZWFyc29uDQogV2ViT3BzCgo=
Re: BUG #14403: Large numbers of CREATE OR UPDATE function causes memory exhaustion
From
Tom Lane
Date:
will.pearson@digital.cabinet-office.gov.uk writes:
> [ lots-n-lots-of CREATE FUNCTION/execute function/DROP FUNCTION eat memory ]
I think probably what's going on here is that plpgsql is creating cache
entries for these functions on first execution, and not reclaiming them
before end of session.
I'm not terribly excited about adding overhead to make it keep track of
DROP FUNCTION operations, because this coding style seems less than great
anyway. Have you considered using DO blocks instead of short-lived
functions?
regards, tom lane
Re: BUG #14403: Large numbers of CREATE OR UPDATE function causes memory exhaustion
From
Will Pearson
Date:
Hi Tom,
DO blocks do seem to do what we want. We'll talk with the sequelize
maintainers to try and get a patched based on this incorporated.
Thanks!
I didn't show the server crashes that we managed to create with the
above behaviour. We think it managed to make our RDS availability
zone failover.
How feasible would it be to try and free the cache entries at the
point of a failed memory allocation before exiting (or maybe
periodically)? I can imagine people using this functionality in a
saner way than us and a malicious user hammering that sane usage and
causing problems.
Thanks again for your help,
Will Pearson
```WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
DEBUG: proc_exit(-1): 0 callbacks to make
DEBUG: shmem_exit(-1): 0 before_shmem_exit callbacks to make
DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
DEBUG: proc_exit(-1): 0 callbacks to make
DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
DEBUG: proc_exit(-1): 0 callbacks to make
DEBUG: writing stats file "pg_stat/db_16400.stat"
DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
DEBUG: proc_exit(-1): 0 callbacks to make
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
DEBUG: shmem_exit(-1): 0 before_shmem_exit callbacks to make
DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
DEBUG: proc_exit(-1): 0 callbacks to make
DEBUG: reaping dead processes
DEBUG: shmem_exit(-1): 0 before_shmem_exit callbacks to make
DEBUG: server process (PID 71) exited with exit code 2
DETAIL: Failed process was running: SELECT "id", "username",
"createdAt", "updatedAt" FROM "People" AS "Person" WHERE
"Person"."username" = 'aaa-1973721468-bb@example.com' LIMIT 1;
DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
DEBUG: proc_exit(-1): 0 callbacks to make
DEBUG: removing temporary stats file "pg_stat_tmp/db_16400.stat"
DEBUG: reaping dead processes
DEBUG: server process (PID 69) exited with exit code 2
DETAIL: Failed process was running: SELECT "id", "username",
"createdAt", "updatedAt" FROM "People" AS "Person" WHERE
"Person"."username" = 'aaa-604124768-bb@example.com' LIMIT 1;
DEBUG: writing stats file "pg_stat/db_0.stat"
DEBUG: removing temporary stats file "pg_stat_tmp/db_0.stat"
DEBUG: reaping dead processes
DEBUG: shmem_exit(-1): 0 before_shmem_exit callbacks to make
DEBUG: reaping dead processes
DEBUG: server process (PID 68) was terminated by signal 9: Killed
DETAIL: Failed process was running: CREATE OR REPLACE FUNCTION
pg_temp.testfunc(OUT response "People", OUT sequelize_caught_exception
text) RETURNS RECORD AS $func_46fb58d873b34b40b4ace61f7ac30040$ BEGIN
INSERT INTO "People" ("id","username","createdAt","updatedAt") VALUES
(DEFAULT,'aaa-1616469740-bb@example.com','2016-10-27 14:15:07.166
+00:00','2016-10-27 14:15:07.166 +00:00') RETURNING * INTO response;
EXCEPTION WHEN unique_violation THEN GET STACKED DIAGNOSTICS
sequelize_caught_exception = PG_EXCEPTION_DETAIL; END
$func_46fb58d873b34b40b4ace61f7ac30040$ LANGUAGE plpgsql; SELECT
(testfunc.response).*, testfunc.sequelize_caught_exception FROM
pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();
DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
DEBUG: proc_exit(-1): 0 callbacks to make
DEBUG: reaping dead processes
DEBUG: reaping dead processes
DEBUG: server process (PID 24) exited with exit code 2
DETAIL: Failed process was running: select count(*) from "People";
DEBUG: reaping dead processes
DEBUG: sending signal 9 to process 70
DEBUG: reaping dead processes
DEBUG: server process (PID 70) was terminated by signal 9: Killed
DETAIL: Failed process was running: CREATE OR REPLACE FUNCTION
pg_temp.testfunc(OUT response "People", OUT sequelize_caught_exception
text) RETURNS RECORD AS $func_21fe0323d32f40bb817efe5a470becc9$ BEGIN
INSERT INTO "People" ("id","username","createdAt","updatedAt") VALUES
(DEFAULT,'aaa--1665959435-bb@example.com','2016-10-27 14:15:07.168
+00:00','2016-10-27 14:15:07.168 +00:00') RETURNING * INTO response;
EXCEPTION WHEN unique_violation THEN GET STACKED DIAGNOSTICS
sequelize_caught_exception = PG_EXCEPTION_DETAIL; END
$func_21fe0323d32f40bb817efe5a470becc9$ LANGUAGE plpgsql; SELECT
(testfunc.response).*, testfunc.sequelize_caught_exception FROM
pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();
LOG: all server processes terminated; reinitializing
On 28 October 2016 at 19:34, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> will.pearson@digital.cabinet-office.gov.uk writes:
>> [ lots-n-lots-of CREATE FUNCTION/execute function/DROP FUNCTION eat memory ]
>
> I think probably what's going on here is that plpgsql is creating cache
> entries for these functions on first execution, and not reclaiming them
> before end of session.
>
> I'm not terribly excited about adding overhead to make it keep track of
> DROP FUNCTION operations, because this coding style seems less than great
> anyway. Have you considered using DO blocks instead of short-lived
> functions?
>
> regards, tom lane