BUG #14403: Large numbers of CREATE OR UPDATE function causes memory exhaustion

The following bug has been logged on the website:

Bug reference: 14403
Logged by: Will Pearson
Email address: will(dot)pearson(at)digital(dot)cabinet-office(dot)gov(dot)uk
PostgreSQL version: 9.6.1
Operating system: OSX El Capitan (also in Linux Containers/RDS)
Description:

Summary:

We had a problem in production where RDS failed over availability zones. We
later found this was due to a memory overflow. Doing more digging it seems
to due to large numbers of CREATE OR UPDATE functions done by sequelize (a
node ORM) during session creation. We've mitigated the problem.

We've now isolated the problem from our code and have created the following
python script doing standard sql. But we could do with more understanding
of postgresql internals how to dig further (and any tuning possibilities).

----
Python script to replicate the problems

import psycopg2
import sys

def create_or_replace (n, cur, conn):
print ("Doing action " + str(n))
cur.execute("CREATE OR REPLACE FUNCTION pg_temp.testfunc(OUT response
test, OUT sequelize_caught_exception text) RETURNS RECORD AS $func_a7" +
str(n) + "$ BEGIN INSERT INTO test (\"id\") VALUES (" + str(n) + ")
RETURNING * INTO response; EXCEPTION WHEN unique_violation THEN GET STACKED
DIAGNOSTICS sequelize_caught_exception = PG_EXCEPTION_DETAIL; END $func_a7"
+ str(n) + "$ LANGUAGE plpgsql;")
cur.execute("SELECT (testfunc.response).*,
testfunc.sequelize_caught_exception FROM pg_temp.testfunc();")
cur.execute("DROP FUNCTION IF EXISTS pg_temp.testfunc();")
conn.commit()

def lots_of_create_or_replace(t, cur, conn) :
print ("Doing lots of actions for t" + str(t))
for n in range(1, 100000000):
create_or_replace(n+t, cur, conn)
print "Finished"

conn = psycopg2.connect("dbname =testdb")

cur = conn.cursor()
cur.execute("DELETE FROM test;")
lots_of_create_or_replace(int(sys.argv[1]), cur,conn)

#Usage#
Create the required tables and database then run it with a starting point.
The code is designed so that you can run multiple copies at different
points.

Look at the memory usage go up on the python processes.

Killing the python process doing the updates means the memory is freed. On
some tests against our dev environments it appeared that simply stopping
doing the requests to create sessions allowed the system to recover the
memory.

----
More details:

Seen in RDS (9.4.4)
locally in Mac 9.5.4
On Linux in a container 9.4.4

Sequelize code that shows it in the wild:

https://github.com/sequelize/sequelize/blob/95d3a5713499ab90b19d7a312b8cb878b26eb1b4/lib/dialects/abstract/query-generator.js#L154

----

Hypotheses I've looked at - Reason I don't think that is the problem:

Functions aren't being cleaned up properly - \df doesn't show more functions
during the run. Although a colleague points out that these temp functions
are per connection, so wouldn't show up. We're not sure how to see them.
No vacuums happening - There are vacuums happening during memory usage
increase.

Current hypothesis:

The process that frees up memory for temporary functions doesn't have a
chance to act because of the consistent number of inserts and deletions
pre-empting it. It is not a normal vacuum to deal with it.

Thanks for any help,

Will Pearson
WebOps