Re: Unlimited memory consumption with long-lived connection

On Tue, Feb 21, 2023 at 04:32:02PM +0200, Heikki Linnakangas wrote:
> On 21/02/2023 14:57, Duncan Sands wrote:
> > If I execute the attached python script against a postgresql 15.2 (Ubuntu
> > 15.2-1.pgdg22.10+1) server, with the default configuration (eg shared_buffers =
> > 128M), then the server memory usage goes up and up, apparently endlessly.  After
> > about 10 minutes (on my laptop) pg_top shows RES memory usage for the back-end
> > handling the connection as greater than 1 gigabyte, which seems far too high
> > given the server configuration.  The script just performs the same SELECT
> > endlessly in a loop.  The memory is released when the script is killed.
> > 
> > Platform: Ubuntu 22.10; Linux version 5.19.0-31-generic; x86-64.
> > 
> > PS: The testcase was reduced from a script that kept a connection open for a
> > long time in order to LISTEN, and would execute a query using the same
> > connection every time there was a notification on the channel.  It consumed ever
> > more memory to the point of crashing the postgresql server.  Changing the script
> > to perform the query using a new short-lived connection was an effective workaround.

It sounds like the same as the issue here:
https://wiki.postgresql.org/wiki/PostgreSQL_13_Open_Items

There are patches proposed here, which fixed the issue for me.
But I've always been suspicious that there may be a 2nd, undiagnosed
issue lurking behind this one...
https://www.postgresql.org/message-id/20210417021602.7dilihkdc7oblrf7%40alap3.anarazel.de