Subject: Re: [HACKERS] generated columns
To: Michael Paquier <michael@paquier.xyz>,
 Pavel Stehule <pavel.stehule@gmail.com>
References: <bf9c8765-1aa0-432a-442e-45f715ebe4e7@2ndquadrant.com>
 <20180202022532.GA84623@paquier.xyz>
 <050e663d-d915-f61b-af96-5a09c10ade02@2ndquadrant.com>
 <79e4de23-52e3-2c45-9ba0-5f6932f590aa@2ndquadrant.com>
 <28574501540912440@iva5-750e13568e4d.qloud-c.yandex.net>
 <2ddd0bfa-0fcd-35ec-266c-f03cf72fc581@2ndquadrant.com>
 <26129f50-cf47-2013-8f9b-e7a98853aa69@2ndquadrant.com>
 <CAFj8pRCnXSjxvDqDgruAbf36TEdZZHmV5=xAUbn8Pc2=wHX84A@mail.gmail.com>
 <acb103e7-a1c6-8e53-ec63-0ee02ea8dd73@2ndquadrant.com>
 <CAFj8pRB=L3At5R=DXWRw7OFCdaeZ_+3S5e==DbGgD_7POsqngA@mail.gmail.com>
 <20190115071359.GF1433@paquier.xyz>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>,
 Sergei Kornilov <sk@zsrv.org>, Michael Paquier <michael.paquier@gmail.com>,
 Jaime Casanova <jaime.casanova@2ndquadrant.com>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Openpgp: preference=signencrypt
Autocrypt: addr=peter.eisentraut@2ndquadrant.com; prefer-encrypt=mutual;
 keydata=
 xsFNBFUUt6kBEADZd9NNTBJbHmAXXJgPGOdd6Q73l80oifIVgn2tRxP9dYkCfTlo/yLqbKTl
 JNqTcyM6XrJDnB2sMEUGYCewYZXl+9C4bBJWyNqcEI1ae9u71vEq/fknFLArJ+2wN+f7QuRx
 Q8d94ZJAiJQ5g3fNk2HVeedCQCKP5dCzU+ZLt2dbsx1PDZLjTO9gf+JswWuGFb5wY5PZVM5V
 hq66Xk7ZJzHuARacza2RT7J2fkMmQBY+kr7L98Perb0GhafnlhXB0PvUB+N3xpmggMfwuhTu
 BngS/G/r3GvemxJuFxouw8xsMqrsfdksJXA2gLXYtwRCssdPV3PO0rlD9twqDo8URfIJ5fb6
 1g/MV8aTOKjg3Q8MpJPtkwJqN4o3PvbwYdedeFnOVkkYAJdu/aX1v1BeCBDu7Hy3sF52scD3
 pQbyQSl9vUDpTLarASAxNbAV0NRJE8dp4vfW6AH1ccryGiFqw/czPK/iN0+MT8wsjAW6WW4w
 hvjm5UPA1KTwi3ZT8eT5uycuH7QpuNk7LnGGXQ6KaRALFywzkr0A0V0AvFNa+l4wstMaXaB6
 dmMXyF0/hfryqyOeu1gAdq4q0/PNC2dPgb/6uoh4eMjN6A5GXcprilhz5DulmsilY21hfSoU
 S64ODed96pKhprsOxjZiDRsN1QM5MwB+uwVWUNfsX7fIxE0BrwARAQABzSdQZXRlciBFaXNl
 bnRyYXV0IDxwZXRlckBlaXNlbnRyYXV0Lm9yZz7CwXoEEwEIACQCGwMFCwkIBwMFFQoJCAsF
 FgIDAQACHgECF4AFAlUUuKQCGQEACgkQoZ8SQ+xBJp8llw//RbGoXnF72FZTlOFsnNyxnWbF
 vViPCfdAlZl1GGK9OYCvV1dp/956HuhB0wpaGQHiBmQfOPJf/jLOPjXFYm2iEChEycrBChuE
 j4TSYemMp5BSPILYk9Uwd16kVD/+U6fQfK6H5pOhfBvbM6asMnmpY2wQuvktxHvaGlytFw84
 EzFWNZtAZfxkw28w3AhIQkKe9hJanfyDL750RcbyEr8AAHty5IErrvS7tM5vQwIeh5BF26ND
 CyKPRdZgOgMpcwFfROKBDpAt052CgWIpNdhBdfIKlkCs4/ILxmC3rT/Mb/qZiHcoMMwEu6k+
 T7vRAFZZAk62Yrr/U8/vUGKlzRZQoo86fyojCfI0ibCIc0z8rwCVY9P40G9wD9BTCZS/z8Vh
 jjpBlNnIKw2hM6hbxavKndCJjFFJD08rXrTyrLF/of18xD37vNdYooP9VATB5QjOWFRrscSZ
 4a89e+to3m7YVnd4xdXwGp47dYusoax84C8tg1gZrH38ol9dPmnKX6GZrXeS7tH9k8VeUpJ8
 HQbRThQRWAT936QpYbLConBmOtmbLmjQOGtiXPo1oK57X/7U/WBja5vWmm2q+Yxtt+3uHGVg
 DdDU4CJpXo4lX0v2n3QV5EBSW9hjdF3bNfl6GFBb1pPEhqNcMDUc7zkx/2uENdAdk/BnqMgH
 umip7b0pVfvOwU0EVRS3qQEQAJf6p0P7D+hMYUgCPkvgrcz6OJYsAYdUMUmokfqmkKwK0oJM
 ew8TNGM53ocKAgQGma5f10rwbZNPlpVPNRX5hwUmwVlcwSut0oE+JfUBH7mpysWvpF05RKIy
 q9RCoJZrAbXS7Tig56A1QD8IcM00rERZMcCOlI1+q9m4u9AOI+0LyGGSH+VvoyDHD/Jew4R2
 TmPEF8Pu7lYiNYK88Q6u0kBkc+Tz3BNlyc6fi3HxnFfp0b75HLXPSTu2SwA/0Q31wHZXwrEU
 BR9KgSn/qbgy8ydPc97i82fhiFE3cbGexfPrQSMYmDGi+dcQ0s+AMQwC3XyMC0JkHpYUuFkR
 QdlIvaIhgFUUN2Vr4xF2ADaNTNFc4DYaiPHFAgHZw+3pZWqS4uIiWgJynrEaHw4F3QWU8/JB
 iyEWDhNq4WBrRA0u914TCCAoFOOKMnRdl8eJrRQKM/5/lXbHFjjlI/LDKhLRFsuQsnJJ3331
 RjCzpHPZG5/p5KR1RHiAXa0p/DW5lXyq21hS3kjDlleoHqOxCN/cy0Jgz7qLDq1vZoCl67Sy
 RAH60c2Xu4may9SVltmot0OzWVHdrfS+85FQGp9580DaeLio7YMFKy2bfEvwQJ/c9xD2QPB+
 oSb3V8ueSznXBG5+dyfOsZFRPxceA0wA2rSWZSjxLdKxvyGTj9vsCjaDu13rABEBAAHCwV8E
 GAEIAAkFAlUUt6kCGwwACgkQoZ8SQ+xBJp+HJhAAxmSTsC5y7p9Zu+nnLpucv/h88oEx985e
 qWeXzQiwM3pfRZS+PdTH9TyzHK0KOgKoAiubk0vt+BrT91D9d0G5kVQaTctAErGBoMkTaZJT
 XVEcNphiPzysdSqvprjojMp1y7rG8AP+Xd6m0rE1LRnkRRm8HyI8vdXsuLQPITGWXDLMjaNq
 AOV3BUnzkgtjMuIYw7SCr+12ZOb5dEKvaG4kjC4kaJ/TLbuBhVANcXkC90yp0AOZLs/cB1Dj
 OAXno/RUUruJ3r27a+pFv768UAi9QWcdw58yPxreMNMLqAgsS9oTHwHfGNWcB2dJ4UW+GMLJ
 hTf9TjQ2f+N/sIx5XMQ9pYw6CAJMK29qPx6/ZayrPxf22EXf0D3zTwIEZgwfSkynFVwV25L7
 awQq60fifdMYheSxq7GbhpsixmcD+e161cCmLIt75YDt5bQ0B1yf7EmxG8n7pGZAOM6LoNsU
 fkSldba5XVwh8LuN5mK0nQBELGW6Os4TDq4REggLpfcvFC215zpuo0iCGnmE/Bns4nowqUSP
 u4tkWL+nxW4qgv8wo8HJ52jd79EJ8iqfIBoidBfy9AIUiEUApGQzWPu6yWcI8fY0rN5D+tc+
 Yw1Zt6Mm+huYJpgCRsTcfzEcahnBn521CWcWvhW1ff3KFileFMtNmnBBhMncmRKHphtTKEpN
 3jI=
Organization: 2ndQuadrant
Message-ID: <039d8e26-af6a-9658-2188-33c201f16690@2ndquadrant.com>
Date: Mon, 25 Feb 2019 21:51:52 +0100
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <20190115071359.GF1433@paquier.xyz>
Content-Type: text/plain; charset=windows-1252
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 2019-01-15 08:13, Michael Paquier wrote:
>>> Ah, the volatility checking needs some improvements.  I'll address that
>>> in the next patch version.
>>
>> ok
> 
> The same problem happens for stored and virtual columns.

This is fixed in v8.

> It would be nice to add a test with composite types, say something
> like:
> =# create type double_int as (a int, b int);
> CREATE TYPE
> =# create table double_tab (a int,
>   b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) stored,
>   c double_int GENERATED ALWAYS AS ((a * 4, a * 5)) virtual);
> CREATE TABLE
> =# insert into double_tab values (1), (6);
> INSERT 0 2
> =# select * from double_tab ;
>  a |    b    |    c
> ---+---------+---------
>  1 | (2,3)   | (4,5)
>  6 | (12,18) | (24,30)
> (2 rows)

I added that.

> +   bool        has_generated_stored;
> +   bool        has_generated_virtual;
>  } TupleConstr;
> Could have been more simple to use a char as representation here.

Seems confusing if both apply at the same time.

> Using NULL as generation expression results in a crash when selecting
> the relation created:
> =# CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS
> AS (NULL));
> CREATE TABLE
> =# select * from gtest_err_1;
> server closed the connection unexpectedly

This is fixed in v8.

> +   The view <literal>column_column_usage</literal> identifies all
> generated
> "column_column_usage" is redundant.  Could it be possible to come up
> with a better name?

This is specified in the SQL stnadard.

> When testing a bulk INSERT into a table which has a stored generated
> column, memory keeps growing in size linearly, which does not seem
> normal to me.

This was a silly coding error.  It's fixed in v8.

> +/*
> + * Thin wrapper around libpq to obtain server version.
> + */
> +static int
> +libpqrcv_server_version(WalReceiverConn *conn)
> This should be introduced in separate patch in my opinion (needed
> afterwards for logirep).

Yes, it could be committed separately.

> What about the catalog representation of attgenerated?  Would it merge
> with attidentity & co?  Or not?

I think the way it is now seems best.  The other options that were
discussed are also plausible, but that the discussions did not reveal
any overwhelming arguments for a a change.

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