Re: Server side backend permanent session memory usage ?

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, July 27, 2016 4:50 PM
To: Day, David
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Server side backend permanent session memory usage ?

"Day, David" <dday(at)redcom(dot)com> writes:
> We have a server side function written in plpgsql called by the client
> side application. That fx does work that includes Invoking a server side plpython2u function.
> If I repeat calling this severer side logic/function outside our app
> from a a psql invoked connection and I monitor the memory growth on the backend side I see top reporting a continual growth trend after each burst of invocations.
> The premise was that the issue of the repeated invocation of the python interpreter and/or the python function use of a "subprocess" method
> does not cleanup correctly and this accounts for the memory growth and system performance degradation.
> FreeBSD 10.1, postgres 9.5.3 or postgres 9.3.11, running on VM, no relevant problems indicated in log files.

It'd be very useful to find out whether you can reproduce the leakage in 9.6beta (any beta release will do). There are a couple of significant fixes in plpython that might be relevant --- I'm looking at these in
particular:

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=8c75ad436
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=1d2fe56e4

We did not back-patch those changes because they seemed rather invasive/risky compared to the value for average users.

If you still see misbehavior in 9.6, it'd be worth trying to extract a self-contained example.

regards, tom lane

Tom,

I would venture that the first item you listed probably explains our observations.

I plan to write up an internal trouble report on our end so that when we start using 9.6 we will disable the connection
refresh workaround and re-check the issue and report back at that time. ( Not soon unfortunately )

I guess then it is a word of caution in the meantime to 9.5 and earlier version users who use the python extension and have long lived connections.

Thanks so much for your assistance.

Best Regards

Dave Day