CODEWITHSUNDEEP
postgresqlpostgresql-15
springy76
springy76

Reputation: 3786

when does a (stored) GENERATED COLUMN get regenerated?

On any update to the row (which would be somehow dumb and I would expect a performance warning on the documentation page then) or is it smart enough of analyzing the generation expression and only regenerate the computed column when the input column(s) have changed?

Upvotes: 1

Views: 1635

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21085

From the documentation it's rather clear

A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read. Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically).

So it seams that the generated always column is generated always.

Below a small test case to verify

We define a immutable function used in the formula with pg_sleepinside to see if the function was called

create or replace function wait10s(x numeric)
returns int
as $$
  SELECT pg_sleep(10);
  select x as result;
$$ language sql IMMUTABLE;

Table DDL

create table t 
(col1 numeric,
col2 numeric,
gen_col numeric generated always as ( wait10s(col2) ) STORED
);

Insert

as expected we wait 10 seconds

 insert into t (col1, col2) values (1,1);

Update of column used in formula

update t set col2 = 2

Again expected wait

Update of column NOT used in formula

update t set col1 = 2

No wait so it seems that there is an optimizing step calling the formula only in case of necessity.

This makes perfect sense, but of course you should take it with care as this behavior is not documented and may change...

Upvotes: 5

Related Questions