Re: IDENTITY/GENERATED columns - Mailing list pgsql-patches

This is being done for 8.3, right?

---------------------------------------------------------------------------

Zoltan Boszormenyi wrote:
> Hi,
>
> here's the next version. Changes:
> - Extended documentation
> - Extending permissions to new sequences
>   ALTER TABLE tab ADD col type GENERATED  AS IDENTITY
>   didn't work as advertised, now it seems to.
> - Test case was also extended.
> - Previously introduced memory leaks were plugged. Really.
>
> Now the only feature missing is the previously discussed
> GENERATED ALWAYS AS ( expr ) so it can be used like this:
>
> CREATE TABLE tab (
>     c1 double,
>     c2 integer,
>     c3 double GENERATED ALWAYS AS ( col1 + col2),
>     c4 SMALLINT GENERATED ALWAYS AS
>                 (CASE WHEN c1 > c2 THEN 1 ELSE NULL END)
> );
>
> What should the following code produce as a result?
>
> INSERT INTO tab (c1, c2, c3, c4) VALUES (1.1, 2, 0, 0);
>
> This should insert (1.1, 2, 3.1, NULL)
>
> UPDATE tab SET c2 = 1;
>
> Only c2 changes, so: (1.1, 1, 3.1, NULL)
> Or should it change to (1.1, 1, 2.1, 1),
> e.g. recompute all columns that depend on
> changed columns?
>
> UPDATE tab SET c4 = DEFAULT, c3 = DEFAULT, c2 = 2, c1 = 3.5;
>
> Now what? It should be (3.5, 2, 5.5, 1)
> But based on current UPDATE behaviour,
> e.g. values gets computed based on previous
> values, it becomes (3.5, 2, 2.1, 1)
>
> That would really need changing the behaviour of UPDATE.
> Currently, if I do an
>
> UPDATE tab SET c1 = 3.5, c2 = 2, c3 = c1 + c2;
>
> then c3 gets its value based on the previous content
> of the record. For the above GENERATED ALWAYS
> AS (expr) construct to work, UPDATE have to compute
> the column values in multipass, something like this:
>
> constant values are computed;
> while (is there any non-computed columns)
> {
>     newly_computed = 0;
>     foreach (column, non-computed-columns)
>     {
>        if (column value depends only on computed columns)
>        {
>           compute it;
>           newly_computed++;
>        }
>     }
>     if (newly_computed == 0)
>        elog(ERROR, "circular dependency");
> }
>
> This behaviour change would enable something like this:
> CREATE tab2 (c1 integer, c2 integer, c3 integer);
> INSERT INTO tab2 (c1,c2,c3) VALUES (1, 2, c1 + c2);
>
> Does this described behaviour have any precedent or
> standard compliance?
>
> Best regards,
> Zolt?n B?sz?rm?nyi
>

[ application/x-tar is not supported, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +