Hi, 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. With that said, we may want to look into the planner sometimes hoarding memory, especially when planning complex queries involving partitions. AFAIK, one of the reasons for partition-wise join, aggregate to be turned off by default is that its planning consumes a lot of CPU and memory, partly because of the fact that planner doesn't actively release the memory of its subsidiary structures, or maybe because of inferior ways in which partitions and partitioning properties are represented in the planner. Though if there's evidence that it's the latter, maybe we should fix that before pondering any sophisticated planner memory management. Thanks, Amit [1] https://www.postgresql.org/message-id/CAKJS1f-2rx%2BE9mG3xrCVHupefMjAp1%2BtpczQa9SEOZWyU7fjEA%40mail.gmail.com
