Skip site navigation (1) Skip section navigation (2)

Re: IDENTITY/GENERATED columns

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Zoltan Boszormenyi <zboszor(at)dunaweb(dot)hu>
Cc: pgsql-patches(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Rod Taylor <pg(at)rbt(dot)ca>
Subject: Re: IDENTITY/GENERATED columns
Date: 2006-08-26 03:44:52
Message-ID: 200608260344.k7Q3iqT06695@momjian.us (view raw or flat)
Thread:
Lists: 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(at)momjian(dot)us
  EnterpriseDB    http://www.enterprisedb.com

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

In response to

Responses

pgsql-patches by date

Next:From: Bruce MomjianDate: 2006-08-26 03:46:02
Subject: Re: CSStorm occurred again by postgreSQL8.2
Previous:From: Bruce MomjianDate: 2006-08-26 03:35:40
Subject: Re: VALUES clause memory optimization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group