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