Horton

Re: Virtual generated columns

Peter Eisentraut <peter@eisentraut.org>

View thread
From: Peter Eisentraut <peter@eisentraut.org>
To: jian he <jian.universality@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>, Dean Rasheed <dean.a.rasheed@gmail.com>
Date: 2024-11-29T10:01:29Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Expand virtual generated columns for ALTER COLUMN TYPE

  2. Eliminate code duplication in replace_rte_variables callbacks

  3. Expand virtual generated columns in the planner

  4. Virtual generated columns

  5. Additional tests for stored generated columns

  6. Improve generated_stored test

  7. Fix handling of CREATE DOMAIN with GENERATED constraint syntax

  8. Add pg_constraint rows for not-null constraints

  9. Put generated_stored test objects in a schema

  10. Rename regress test generated to generated_stored

  11. Small code simplification

  12. Remove useless code

  13. Remove useless initializations

  14. doc: Clarify that pg_attrdef also stores generation expressions

  15. Clean out column-level pg_init_privs entries when dropping tables.

  16. Re-implement the ereport() macro using __VA_ARGS__.

On 14.11.24 09:48, jian he wrote:
> inspired by not-null and check check_modified_virtual_generated again.
> 
> in plpgsql_exec_trigger, we can:
>          /*
>           * In BEFORE trigger, stored generated columns are not computed yet,
>           * so make them null in the NEW row.  (Only needed in UPDATE branch;
>           * in the INSERT case, they are already null, but in UPDATE, the field
>           * still contains the old value.)  Alternatively, we could construct a
>           * whole new row structure without the generated columns, but this way
>           * seems more efficient and potentially less confusing.
>           */
>          if (tupdesc->constr && tupdesc->constr->has_generated_stored &&
>              TRIGGER_FIRED_BEFORE(trigdata->tg_event))
>          {
>              for (int i = 0; i < tupdesc->natts; i++)
>              {
>                  if (TupleDescAttr(tupdesc, i)->attgenerated ==
> ATTRIBUTE_GENERATED_STORED ||
>                      TupleDescAttr(tupdesc, i)->attgenerated ==
> ATTRIBUTE_GENERATED_VIRTUAL)
>                      expanded_record_set_field_internal(rec_new->erh,
>                                                         i + 1,
>                                                         (Datum) 0,
>                                                         true,    /* isnull */
>                                                         false, false);
>              }
>          }
> then we don't need check_modified_virtual_generated at all.
> 
> this will align with the stored generated column behave for
> BEFORE UPDATE/INSERT FOR EACH ROW trigger. that is
> you are free to assign the virtual generated column any value,
> but at the plpgsql_exec_trigger, we will rewrite it to null.
> 
> also i understand correctly.
> later if we want to implement virtual generated column with not-null then
> check_modified_virtual_generated needs to be removed?

The purpose of check_modified_virtual_generated() for trigger functions 
written in C.  The prevent someone from inserting real values into the 
trigger tuples, because they would then be processed by the rest of the 
system, which would be incorrect.

Higher-level languages such as plpgsql should handle that themselves, by 
preventing setting generated columns in trigger functions.  The presence 
of check_modified_virtual_generated() is still a backstop for those, but 
shouldn't really be necessary.