Re: IDENTITY/GENERATED columns

Yes, I am not ready with it.

Bruce Momjian írta:
> 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
>>
>
>