Re: generated columns

On 30 August 2017 at 23:16, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> Here is another attempt to implement generated columns. This is a
> well-known SQL-standard feature, also available for instance in DB2,
> MySQL, Oracle.
>
[...]
>
> In previous discussions, it has often been a source of confusion whether
> these generated columns are supposed to be computed on insert/update and
> stored, or computed when read. The SQL standard is not explicit, but
> appears to lean toward stored. DB2 stores. Oracle computes on read.
> MySQL supports both. So I target implementing both. This makes sense:
> Both regular views and materialized views have their uses, too. For the
> syntax, I use the MySQL/Oracle syntax of appending [VIRTUAL|STORED]. In
> this patch, only VIRTUAL is fully implemented. I also have STORED kind
> of working, but it wasn't fully baked, so I haven't included it here.
>

Hi,

It applies and compiles without problems, it passes regression tests
and it does what it claims to do:

During my own tests, though, i found some problems:

-- UPDATEing the column, this is at least weird

postgres=# update t1 set height_in = 15;
ERROR: column "height_in" can only be updated to DEFAULT
DETAIL: Column "height_in" is a generated column.
postgres=# update t1 set height_in = default;
UPDATE 1

-- In a view it doesn't show any value

postgres=# create view v1 as select * from t1;
CREATE VIEW
postgres=# insert into t1(height_cm) values (10);
INSERT 0 1
postgres=# select * from t1;
id | height_cm | height_in
--------+-----------+-----------
198000 | 10 | 25.40
(1 row)

postgres=# select * from v1;
id | height_cm | height_in
--------+-----------+-----------
198000 | 10 |
(1 row)

-- In a inherits/partition tree, the default gets malformed

postgres=# create table t1_1 () inherits (t1);
CREATE TABLE
postgres=# \d t1_1
Table "public.t1_1"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+--------------------------------
id | integer | | not null | nextval('t1_id_seq'::regclass)
height_cm | numeric | | |
height_in | numeric | | | height_cm * 2.54
Inherits: t1

postgres=# insert into t1_1 values (11);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q

--
Jaime Casanova www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services