From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jean-Christophe Boggio <postgresql@thefreecat.org>
cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: One PG process eating more than 40GB of RAM and getting killed by
 OOM
In-reply-to: <416d1607-4650-4624-b8e5-46f8a674aebf@thefreecat.org>
References: <1018f9c0-9d14-4146-b7e9-24c48ea29e1d@thefreecat.org>
 <897a43b3-60d0-2417-f821-9299fd204c1d@sqlexec.com>
 <b9a4bf45-65ca-4598-beae-84e269331fbb@thefreecat.org>
 <CAMkU=1wx43ex_RsQAH+zaTE5kCo1okoYsEWUaDOkHSJ87iD7Hg@mail.gmail.com>
 <416d1607-4650-4624-b8e5-46f8a674aebf@thefreecat.org>
Comments: In-reply-to Jean-Christophe Boggio <postgresql@thefreecat.org>
	message dated "Fri, 13 Oct 2023 21:12:02 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1598051.1697236773.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 13 Oct 2023 18:39:33 -0400
Message-ID: <1598052.1697236773@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1598052.1697236773%40sss.pgh.pa.us>
Precedence: bulk

Jean-Christophe Boggio <postgresql@thefreecat.org> writes:
> I have no problem disclosing this code and data to the PG dev team (this=
 =

> is client data though so please keep it for yourselves). Where can I =

> send you a link to the dump ?

Thanks for sending the data.  I'm not observing any leak on current
Postgres, and after checking the commit log I realized that your
symptoms look mighty like this previous report:

https://www.postgresql.org/message-id/b2bd02dff61af15e3526293e2771f874cf2a=
3be7.camel%40cybertec.at

which was fixed here:

Author: Tomas Vondra <tomas.vondra@postgresql.org>
Branch: master [98640f960] 2023-07-02 20:03:30 +0200
Branch: REL_16_STABLE Release: REL_16_0 [9ae7b5d1f] 2023-07-02 20:04:16 +0=
200
Branch: REL_15_STABLE Release: REL_15_4 [0c5fe4ff6] 2023-07-02 20:04:40 +0=
200
Branch: REL_14_STABLE Release: REL_14_9 [c1affa38c] 2023-07-02 20:05:14 +0=
200
Branch: REL_13_STABLE Release: REL_13_12 [3ce761d5c] 2023-07-02 20:05:35 +=
0200

    Fix memory leak in Incremental Sort rescans
    =

    The Incremental Sort had a couple issues, resulting in leaking memory
    during rescans, possibly triggering OOM. The code had a couple of
    related flaws:
    =

    1. During rescans, the sort states were reset but then also set to NUL=
L
       (despite the comment saying otherwise). ExecIncrementalSort then
       sees NULL and initializes a new sort state, leaking the memory used
       by the old one.
    =

    2. Initializing the sort state also automatically rebuilt the info abo=
ut
       presorted keys, leaking the already initialized info. presorted_key=
s
       was also unnecessarily reset to NULL.
    =

    Patch by James Coleman, based on patches by Laurenz Albe and Tom Lane.
    Backpatch to 13, where Incremental Sort was introduced.
    =

    Author: James Coleman, Laurenz Albe, Tom Lane
    Reported-by: Laurenz Albe, Zu-Ming Jiang
    Backpatch-through: 13
    Discussion: https://postgr.es/m/b2bd02dff61af15e3526293e2771f874cf2a3b=
e7.camel%40cybertec.at
    Discussion: https://postgr.es/m/db03c582-086d-e7cd-d4a1-3bc722f81765%4=
0inf.ethz.ch


So I think the answer for you is "update to Postgres 14.9".

			regards, tom lane