Horton

Re: Virtual generated columns

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>, Dean Rasheed <dean.a.rasheed@gmail.com>
Date: 2024-12-16T14:34:00Z
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__.

Attachments

hi. some minor issues...

   <varlistentry id="sql-altertable-desc-set-expression">
    <term><literal>SET EXPRESSION AS</literal></term>
    <listitem>
     <para>
      This form replaces the expression of a generated column.  Existing data
      in the column is rewritten and all the future changes will apply the new
      generation expression.
     </para>
    </listitem>
   </varlistentry>
the second sentence seems not to apply to a virtual generated column?


doc/src/sgml/ref/alter_table.sgml
seems does not explicitly mention the difference of
ALTER TABLE tp ALTER COLUMN b SET EXPRESSION AS (a * 3);
ALTER TABLE ONLY tp ALTER COLUMN b SET EXPRESSION AS (a * 3);
?
the first one will recurse to the child tables and replace any
generated expression in the child table
for the to be altered column, the latter won't.


CheckAttributeType, we can change it to
<<<
else if (att_typtype == TYPTYPE_DOMAIN)
{
        if ((flags & CHKATYPE_IS_VIRTUAL) && DomainHasConstraints(atttypid))
            ereport(ERROR,
                    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                     errmsg("virtual generated column \"%s\" cannot
have a domain type", attname)));
}
<<<
so we can support the domain without any constraints for now.
(I don't have a huge opinion though).

I played around with the privilege tests,
it works fine with INSERT, UPDATE specific columns.
------------------------------------------------------------
ALTER COLUMN SET NOT NULL, if already not-null, then it will become a no-op.
Similarly if old and new generated expressions are the same,
ATExecSetExpression can return  InvalidObjectAddress, making it a no-op.

For example, in ATExecSetExpression, can we make the following ALTER
TABLE a no-op?
CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a
* 3) VIRTUAL );
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3);

since ATExecSetExpression is not recursive,
Each input argument (AlteredTableInfo *tab) is separated for
partitioned tables and partitions.
so does AlteredTableInfo->newvals, AlteredTableInfo->rewrite information.
so for no-op ATExecSetExpression return InvalidObjectAddress
will also work for partitioned tables, inheritance.

attached file trying to do that. While testing it,
I found out there is no test case for ALTER COLUMN SET EXPRESSION
for inheritance cases. even though it works.
in src/test/regress/sql/generated_virtual.sql
after line 161, we can add following tests:

<<<
ALTER TABLE ONLY gtest1 ALTER COLUMN b SET EXPRESSION AS (a * 10);
select tableoid::regclass, * from gtest1;
ALTER TABLE gtest1 ALTER COLUMN b SET EXPRESSION AS (a * 100);
select tableoid::regclass, * from gtest1;
<<<