Re: memory problems and crash of db when deleting data from table with thousands of partitions

Here are scripts which you can use to simulate problem:

- create_tables.sql - creates all partitions
- generate_data.sql - generates some data (technically you need only a few
records, delete command will fail anyway)

and try command:
DELETE FROM bi.test_multilevel WHERE period_name = '....';

PostgreSQL 12 will start to use more and more memory and will stop
operation with "out of memory" (PostgreSQL 11 would crash)

Regards

Josef Machytka

On Mon, 28 Oct 2019 at 17:24, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

> On Mon, Oct 28, 2019 at 04:18:59PM +0100, Josef Machytka wrote:
> >Thank you for your email, FYI - we now did tests with PostgreSQL 12 and
> >unfortunately it is also not able to handle to our case. Only difference
> is
> >that PG 12 is not killed by OOM killer and even does not crash - which is
> >good. But it reports error "out of memory" and stops the statement. So at
> >least it looks like much more stable then PG 11.
> >
>
> Hmmm, this seems a bit weird to me:
>
> MessageContext: 52197329840 total in 8274 blocks; 75904 free (9 chunks);
> 52197253936 used
>
> That context is generally meant for parse trees and other long-lived
> stuff, and I wouldn't expect it to grow to 52GB of data, even if there
> are many many partitions.
>
> I wonder if this might be just another manifestation of the memory leak
> from [1]. Can you provide a self-contained reproducer, i.e. a script I
> could use to reproduce the issue?
>
>
> [1]
> https://www.postgresql.org/message-id/20191024221758.vfv2enubnwmy3deu@development
>
>
> regards
>
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>