Re: Unlimited memory consumption with long-lived connection

Hi,

On 2023-02-22 08:53:41 -0600, Justin Pryzby wrote:
> 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...

Any more details?

> https://www.postgresql.org/message-id/20210417021602.7dilihkdc7oblrf7%40alap3.anarazel.de

A slightly edited / rebased version is at https://postgr.es/m/20221101055132.pjjsvlkeo4stbjkq%40awork3.anarazel.de

I'd feel a lot better applying the patch if there'd be a bit more review. If
none is forthcoming, I'm somewhat inclined to just apply it to HEAD and later
decide whether we'd want to backpatch.

Greetings,

Andres Freund