From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joe Conway <mail@joeconway.com>
cc: David Rowley <david.rowley@2ndquadrant.com>,
        Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
        Andreas Seltenreich <andreas.seltenreich@credativ.de>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Excessive memory usage in multi-statement queries w/ partitioning
In-reply-to: <db7efe35-03eb-e715-9766-d564dc479f0b@joeconway.com>
References: <87ftp6l2qr.fsf@credativ.de>
 <e0ec915a-ebd3-ee9d-144b-e24bc3fae3c4@lab.ntt.co.jp>
 <0e717312-3a02-781b-7dfc-e0cc395e92d8@joeconway.com>
 <CAKJS1f-StHT8ZKyC8rsK=nJim13qDWDiNO8NMirTBo_jk=z5Aw@mail.gmail.com>
 <db7efe35-03eb-e715-9766-d564dc479f0b@joeconway.com>
Comments: In-reply-to Joe Conway <mail@joeconway.com>
	message dated "Fri, 24 May 2019 10:17:21 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <6013.1558708103.1@sss.pgh.pa.us>
Date: Fri, 24 May 2019 10:28:23 -0400
Message-ID: <6014.1558708103@sss.pgh.pa.us>
Precedence: bulk

Joe Conway <mail@joeconway.com> writes:
> On 5/24/19 9:33 AM, David Rowley wrote:
>> For it to have regressed it would have had to once have been better,
>> but where was that mentioned?  The only thing I saw was
>> non-partitioned tables compared to partitioned tables, but you can't
>> really say it's a regression if you're comparing apples to oranges.

> I have very successfully used multiple hundreds and even low thousands
> of partitions without running out of memory under the older inheritance
> based "partitioning", and declarative partitioning is supposed to be
> (and we have advertised it to be) better, not worse, isn't it?

Have you done the exact thing described in the test case?  I think
that's going to be quite unpleasantly memory-intensive in any version.

The real issue here is that we have designed around the assumption
that MessageContext will be used to parse and plan one single statement
before being reset.  The described usage breaks that assumption.
No matter how memory-efficient any one statement is or isn't,
if you throw enough of them at the backend without giving it a chance
to reset MessageContext, it won't end well.

So my thought, if we want to do something about this, is not "find
some things we can pfree at the end of planning" but "find a way
to use a separate context for each statement in the query string".
Maybe multi-query strings could be handled by setting up a child
context of MessageContext (after we've done the raw parsing there
and determined that indeed there are multiple queries), running
parse analysis and planning in that context, and resetting that
context after each query.

			regards, tom lane