On 30 August 2017 at 23:16, Peter Eisentraut
<peter.eisentraut@2ndquadrant.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
