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, 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