Re: [HACKERS] generated columns

On 2018-10-30 16:14, Sergei Kornilov wrote:
> Hi
> 
> I applied this patch on top 2fe42baf7c1ad96b5f9eb898161e258315298351
> commit and found a bug while adding STORED column:
> 
> postgres=# create table test(i int);
> CREATE TABLE
> postgres=# insert into test values (1),(2);
> INSERT 0 2
> postgres=# alter table test add column gen_stored integer GENERATED
> ALWAYS AS ((i * 2)) STORED;
> ALTER TABLE
> postgres=# alter table test add column gen_virt integer GENERATED
> ALWAYS AS ((i * 2));
> ALTER TABLE
> postgres=# table test;
>  i | gen_stored | gen_virt
> ---+------------+----------
>  1 |            |        2
>  2 |            |        4
> 
> Virtual columns was calculated on table read and its ok, but stored
> column does not update table data.

This workaround is possible:

update test set i = i where gen_stored is null returning *;
  i | gen_stored | gen_virt
---+------------+----------
  1 |          2 |        2
  2 |          4 |        4
(2 rows)

table test ;
  i | gen_stored | gen_virt
---+------------+----------
  3 |          6 |        6
  4 |          8 |        8
  1 |          2 |        2
  2 |          4 |        4
(4 rows)


Hm, well, I suppose it's still a bug...


I have also noticed that logical replication isn't possible on tables 
with a generated column.  That's a shame but I suppsoe that is as 
expected.


Erik Rijkers

> 
> regards, Sergei