Re: Excessive memory usage in multi-statement queries w/ partitioning

[Amit Langote]

On 2019/05/24 23:28, Tom Lane wrote:
> 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.

Maybe like the attached?  I'm not sure if we need to likewise be concerned
about exec_sql_string() being handed multi-query strings.

Thanks,
Amit

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 44a59e1d4f..0165f794b0 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -984,7 +984,8 @@ static void
 exec_simple_query(const char *query_string)
 {
        CommandDest dest = whereToSendOutput;
-       MemoryContext oldcontext;
+       MemoryContext oldcontext,
+                                 stmtcontext;
        List       *parsetree_list;
        ListCell   *parsetree_item;
        bool            save_log_statement_stats = log_statement_stats;
@@ -1132,10 +1133,14 @@ exec_simple_query(const char *query_string)
                /*
                 * OK to analyze, rewrite, and plan this query.
                 *
-                * Switch to appropriate context for constructing querytrees 
(again,
-                * these must outlive the execution context).
+                * Switch to appropriate context for constructing querytrees.
+                * Memory allocated during this construction is released before
+                * the generated plan is executed.
                 */
-               oldcontext = MemoryContextSwitchTo(MessageContext);
+               stmtcontext = AllocSetContextCreate(MessageContext,
+                                                                               
        "statement parse/plan context",
+                                                                               
        ALLOCSET_DEFAULT_SIZES);
+               oldcontext = MemoryContextSwitchTo(stmtcontext);
 
                querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
                                                                                
                NULL, 0, NULL);
@@ -1143,6 +1148,14 @@ exec_simple_query(const char *query_string)
                plantree_list = pg_plan_queries(querytree_list,
                                                                                
CURSOR_OPT_PARALLEL_OK, NULL);
 
+               /*
+                * Copy the plan trees into the longer-lived MessageContext and 
delete
+                * the temporary context used to generate them.
+                */
+               MemoryContextSwitchTo(MessageContext);
+               plantree_list = copyObject(plantree_list);
+               MemoryContextDelete(stmtcontext);
+
                /* Done with the snapshot used for parsing/planning */
                if (snapshot_set)
                        PopActiveSnapshot();