Re: [HACKERS] generated columns

On 2019-03-20 03:51, Michael Paquier wrote:
> On Mon, Mar 18, 2019 at 03:14:09PM +0100, Pavel Stehule wrote:
>> postgres=# update foo set name = 'bbbxx' where id = 1; -- error
>> ERROR: no generation expression found for column number 3 of table
>> "foo"
>
> Yes I can see the problem after adding a generated column and dropping
> it on an INSERT query.

fixed

> + if (relid && attnum && get_attgenerated(relid, attnum))
> Better to use OidIsValid here?

fixed

> + (walrcv_server_version(wrconn) >= 120000 ? "AND a.attgenerated = ''" : ""),
> I think that it is better to always have version-related references
> stored as defines.

A valid idea, but I don't see it widely done (see psql, pg_dump).

> +CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED UNIQUE);
> +CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a * 2) STORED, PRIMARY KEY (a, b));
> Some tests for unique constraints with a generated column should be in
> place?

done

> It would be nice to have extra tests for forbidden expression types
> on generated columns especially SRF, subquery and aggregates/window
> functions.

done

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services