Subject: [BUG] Excessive memory usage with update on STORED generated columns.

From: "Anton A. Melnikov" <[email-masked]>
Date: Mon, 30 Mar 2026 17:25:29 +0300

Hi!

My colleagues found that a queries like that:

\timing

DROP TABLE IF EXISTS t;

CREATE TABLE t (
     id int,
     a int,
     b int,
     g text GENERATED ALWAYS AS (
         lpad((CASE WHEN a <> 0 THEN a ELSE b END)::text, 10, '0') ||
         lpad((CASE WHEN a <> 0 THEN a ELSE b END)::text, 10, '0') ||
         lpad((CASE WHEN a <> 0 THEN a ELSE b END)::text, 10, '0') ||
         lpad((CASE WHEN a <> 0 THEN a ELSE b END)::text, 10, '0') ||
         lpad((CASE WHEN a <> 0 THEN a ELSE b END)::text, 10, '0') ||
         lpad((CASE WHEN a <> 0 THEN a ELSE b END)::text, 10, '0') ||
         lpad((CASE WHEN a <> 0 THEN a ELSE b END)::text, 10, '0') ||
         lpad((CASE WHEN a <> 0 THEN a ELSE b END)::text, 10, '0') ||
         lpad((CASE WHEN a <> 0 THEN a ELSE b END)::text, 10, '0') ||
         lpad((CASE WHEN a <> 0 THEN a ELSE b END)::text, 10, '0')
     ) STORED
);

INSERT INTO t
SELECT 1, 100, 0
FROM generate_series(1, 1000000);

UPDATE t SET id = 2; -- < problem query

lead to excessive memory consumption up to 10Gb in this example and
query execution time up to ~1,5min.

Bisect shows that the problem appeared after commit 83ea6c540
(Virtual generated columns).

Before this commit the update query took only ~8s and the memory
consumption did not exceed 150Mb for this backend.
MemoryContextStats reports only a small amount of memory usage, while
malloc_stats() confirms large allocations outside PostgreSQL memory
contexts.

With help of massif tool i found repeated allocations originating from:

     ExecInitGenerated
       → build_column_default
         → stringToNode

This indicates that generated expressions are reparsed multiple times,
once per row to be updated instead of being reused.

There is a problem call stack during UPDATE t SET id = 2;
execution: see attached bt.txt, please.

Before the above-mentioned commit, ExecInitGenerated() was effectively
invoked once per ResultRelInfo, so this behavior was not observable.

I would like to propose a fix that add a caching of the the parsed
expression trees (Node *) in ResultRelInfo, so that build_column_default()
and stringToNode() are executed at most once per attribute per query.

With this fix, the query execution time
and memory consumption return to normal:

postgres=# UPDATE t SET id = 2;
UPDATE 1000000
Time: 11522,621 ms (00:11,523)

A patch for this approach for current master is attached here.

Would be glad for any feedback.

Best regards,

-- 
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

From fc0e4763fd44c1e1571387c39b9cb5a0c8167730 Mon Sep 17 00:00:00 2001
From: "Anton A. Melnikov" <[email-masked]>
Date: Fri, 27 Mar 2026 11:32:25 +0300
Subject: [PATCH] Avoid repeated parsing of generated column expressions.

After commit 83ea6c54, execution paths were introduced
where ExecInitGenerated() can be invoked per row
for the same ResultRelInfo during a single query.
Because the parsed expression trees are allocated
in es_query_cxt, each call results in additional
allocations that are not freed until query end.

Fix it by caching the parsed expression trees
(Node *) in ResultRelInfo,
ensuring that build_column_default() and stringToNode()
are executed only once per attribute per query
and subsequent calls reuse the cached parse trees.

This restores the old behavior where expression parsing
is done once per query and prevents unbounded memory growth.
---
 src/backend/executor/execMain.c        |  1 +
 src/backend/executor/nodeModifyTable.c | 14 +++++++++++++-
 src/include/nodes/execnodes.h          |  3 +++
 3 files changed, 17 insertions(+), 1 deletion(-)

diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 58b84955c2b..b3d2304624e 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1311,6 +1311,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
 	resultRelInfo->ri_GenVirtualNotNullConstraintExprs = NULL;
 	resultRelInfo->ri_GeneratedExprsI = NULL;
 	resultRelInfo->ri_GeneratedExprsU = NULL;
+	resultRelInfo->ri_GeneratedExprsParsed = NULL;
 	resultRelInfo->ri_projectReturning = NULL;
 	resultRelInfo->ri_onConflictArbiterIndexes = NIL;
 	resultRelInfo->ri_onConflict = NULL;
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 4cd5e262e0f..451a38bc7c9 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -485,7 +485,19 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
 			Expr	   *expr;
 
 			/* Fetch the GENERATED AS expression tree */
-			expr = (Expr *) build_column_default(rel, i + 1);
+			if (resultRelInfo->ri_GeneratedExprsParsed == NULL)
+				resultRelInfo->ri_GeneratedExprsParsed = palloc0(natts * sizeof(Node *));
+
+			if (resultRelInfo->ri_GeneratedExprsParsed[i] == NULL)
+			{
+				expr = (Expr *) build_column_default(rel, i + 1);
+				resultRelInfo->ri_GeneratedExprsParsed[i] = (Node *) expr;
+			}
+			else
+			{
+				expr = (Expr *) resultRelInfo->ri_GeneratedExprsParsed[i];
+			}
+
 			if (expr == NULL)
 				elog(ERROR, "no generation expression found for column number %d of table \"%s\"",
 					 i + 1, RelationGetRelationName(rel));
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 684e398f824..ca30137a308 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -582,6 +582,9 @@ typedef struct ResultRelInfo
 	int			ri_NumGeneratedNeededI;
 	int			ri_NumGeneratedNeededU;
 
+	/* Cached parsed GENERATED expressions */
+	Node      **ri_GeneratedExprsParsed;
+
 	/* list of RETURNING expressions */
 	List	   *ri_returningList;
 
-- 
2.50.1

---

From: Matthias van de Meent <[email-masked]>
Date: Mon, 30 Mar 2026 17:26:35 +0200

> My colleagues found that a queries like that:
[...]
> (Virtual generated columns).

Yep, that looks about accurate. Thanks for the report and initial triage!

> once per row to be updated instead of being reused.
[...]
> and memory consumption return to normal:

I think that fixes the wrong issue. Specifically, ExecInitGenerated
mentions that it expects to be called just once per ResultRelInfo, and
your code adds code to allow it to be called more than once without
causing more problems. While it does solve the complaint, it's still
executing more work than it should. Additionally, though it's not a
failing of the patch per se, it changes the layout of an existing
struct, and so it isn't backportable.

The actual issue is that ExecComputeStoredGenerated uses
ri_GeneratedExprsU's NULL-ness to check whether the generated columns'
expressions have been initialized, whilst for UPDATE ResultRelInfos
the initialized-indicator is stored in ri_extraUpdatedCols_valid.

So, I think the attached patch is a more appropriate fix, it avoids
calling into ExecInitGenerated at all when no column included in
expressions was updated. It also adds an assertion that the function
isn't called again once the field has been initialized. It also has
the benefit of being backportable.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

---

From: Matthias van de Meent <[email-masked]>
Date: Mon, 30 Mar 2026 20:50:37 +0200

On Mon, 30 Mar 2026 at 20:35, Tom Lane <[email-masked]> wrote:
>
> So I think the correct fix is that there needs to be a separate
> boolean tracking whether this work has been done, akin to
> ExecGetExtraUpdatedCols's use of ri_extraUpdatedCols_valid.

Why would it need a new boolean? ri_extraUpdatedCols_valid tracks
exactly whether we've already gone through ExecInitGenerated(...,
CMD_UPDATE), and in doing so if both ri_extraUpdatedCols,
ri_GeneratedExprsU, and ri_NumGeneratedNeededU are valid or whether
they still need to be populated. Adding a new boolean would therefore
be rather duplicative.

See also the patch of a few hours ago at [0].

Kind regards,

Matthias van de Meent

[0] https://postgr.es/m/CAEze2Wh%2B_C8LtmiMRb58df%3DA1PrBVmMnYMOfbBUk9c%3Dm99CN%2Bw%40mail.gmail.com

---

From: Matthias van de Meent <[email-masked]>
Date: Mon, 30 Mar 2026 22:14:25 +0200

On Mon, 30 Mar 2026 at 21:14, Tom Lane <[email-masked]> wrote:
>
> Matthias van de Meent <[email-masked]> writes:
> Sorry, I missed that you'd already responded.

That's allright.

> I don't like using ri_extraUpdatedCols_valid here: it requires callers
> to know more than they should about how ExecInitGenerated works, and
> it does not fix the comparable performance problem that probably
> exists in the INSERT path.

I'm not sure which comparable performance problem you're referring to;
I don't see one mentioned, and INSERT doesn't have the same issue
because we never call into ExecInitGenerated for inserts unless 1.)
there are any generated stored columns, and 2.) it hasn't been called
already for this ResultRelInfo (*) by checking
nonnull-after-initialization ri_GeneratedExprsI.

(*) the issue here, of course, being that we *do* call
ExecInitGenerated many times in the same query for the same RRI when
UPDATE only changes columns that aren't referenced in generated
columns, this caused due to an incorrect check which checks the wrong
field.

>  I think the right fix is to have three
> booleans specifically reflecting the validity of ri_GeneratedExprsU,
> ri_GeneratedExprsI, and ri_extraUpdatedCols.

I'll defer to Peter as primary author of this code, but personally I
think that it isn't needed:

In the insert case (ri_GeneratedExprsI) the field is always non-null
once the generated columns' exprstates are initialized, whilst in the
update case the current boolean is indicative of the fields having
been populated. Yes, it might benefit from better naming, but the
boolean itself is already sufficient to indicate that you can rely on
the update-related fields to be populated by ExecInitGenerated.

> There's at least three bytes free after ri_extraUpdatedCols_valid,
> so we could cram two more bools in there without an ABI break.
> Admittedly, it's not pretty that those bools wouldn't be close
> to the fields they describe.  But we could improve that in HEAD
> with some more-substantial reordering of the contents of
> ResultRelInfo; it's not like the current ordering has much rhyme
> or reason to it.

I personally try to avoid adding new fields in backbranches if that's
possible (even in alignment gaps), so that if we actually must add
data to the struct we still have space to pick from.

Kind regards,

Matthias van de Meent