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

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