On 5/24/19 1:47 AM, Amit Langote wrote: > On 2019/05/23 4:15, Andreas Seltenreich wrote: >> …but when doing it on the parent relation, even 100 statements are >> enough to exceed the limit: >> >> ,---- >> | $ psql -c "$(yes update t set c=c where c=6 \; | head -n 100)" >> | FEHLER: Speicher aufgebraucht >> | DETAIL: Failed on request of size 200 in memory context "MessageContext". >> `---- >> >> The memory context dump shows plausible values except for the MessageContext: >> >> TopMemoryContext: 124336 total in 8 blocks; 18456 free (11 chunks); 105880 >> used >> [...] >> MessageContext: 264241152 total in 42 blocks; 264 free (0 chunks); >> 264240888 used >> [...] > > As David Rowley said, planning that query hundreds of times under a single > MessageContext is not something that will end well on 11.3, because even a > single instance takes up tons of memory that's only released when > MessageContext is reset. > >> Maybe some tactically placed pfrees or avoiding putting redundant stuff >> into MessageContext can relax the situation? > > I too have had similar thoughts on the matter. If the planner had built > all its subsidiary data structures in its own private context (or tree of > contexts) which is reset once a plan for a given query is built and passed > on, then there wouldn't be an issue of all of that subsidiary memory > leaking into MessageContext. However, the problem may really be that > we're subjecting the planner to use cases that it wasn't perhaps designed > to perform equally well under -- running it many times while handling the > same message. It is worsened by the fact that the query in question is > something that ought to have been documented as not well supported by the > planner; David has posted a documentation patch for that [1]. PG 12 has > alleviated the situation to a large degree, so you won't see the OOM > occurring for this query, but not for all queries unfortunately.
I admittedly haven't followed this thread too closely, but if having 100 partitions causes out of memory on pg11, that sounds like a massive regression to me. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
signature.asc
Description: OpenPGP digital signature
