Date: Wed, 22 Feb 2023 08:53:41 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Duncan Sands <duncan.sands@deepbluecap.com>,
	pgsql-bugs@lists.postgresql.org, Andres Freund <andres@anarazel.de>
Subject: Re: Unlimited memory consumption with long-lived connection
Message-ID: <20230222145341.GT1653@telsasoft.com>
References: <a53cacb0-8835-57d6-31e4-4c5ef196de1a@deepbluecap.com>
 <7484b4bd-a808-ed9e-892c-933f7f24120f@iki.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <7484b4bd-a808-ed9e-892c-933f7f24120f@iki.fi>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20230222145341.GT1653%40telsasoft.com>
Precedence: bulk

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