Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHPUt-0005Vq-0H for pgsql-hackers@arkaria.postgresql.org; Tue, 30 Oct 2018 08:35:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gHPUr-0004Gm-D0 for pgsql-hackers@arkaria.postgresql.org; Tue, 30 Oct 2018 08:35:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHPUp-0004Ge-Np for pgsql-hackers@lists.postgresql.org; Tue, 30 Oct 2018 08:35:33 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHPUg-0001pp-TP for pgsql-hackers@postgresql.org; Tue, 30 Oct 2018 08:35:30 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id DD42622317; Tue, 30 Oct 2018 04:35:21 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute7.internal (MEProxy); Tue, 30 Oct 2018 04:35:21 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=R03wly ZTOpSmTohGGKH1ZosocRjU/bW3ngNXWrHBahQ=; b=qXVKIl8TeB1+enk4jbOVNw cuG6ZRL4CYKD70praSvJB6QNInv/vJkYT18RdOvg5zFfn66jXJaiR4A30hoqgIvE +XiMZ7+JOAC3xH0XDlIdR5GvJO5WlI8b9/g+Dk8MA9xLIUBi6DVmiIO8cbjWbCFB ADyjXV7ZLfcrW2Z6FBFiBD/J/HpVhHn7XwsC9CUvz0ANB6pZjj6TihYCkuTJfOwm VAWi6alku9+eBfN3YHAvFiRfI6i7Tra0HHEdMEp6D3fZ7BnsbXFMJUCoudLARLq1 fXZRmhNeNfAOybM1ckUkTAcXIZhPInC6iN/zgl/cOkOzXRAem6moRLI5dh3lfEPg == X-ME-Sender: X-ME-Proxy: Received: from april.pezone.net (p5df295f3.dip0.t-ipconnect.de [93.242.149.243]) by mail.messagingengine.com (Postfix) with ESMTPA id D6E9CE4511; Tue, 30 Oct 2018 04:35:19 -0400 (EDT) Subject: Re: [HACKERS] generated columns From: Peter Eisentraut To: pgsql-hackers References: <1bc948f8-3bab-6c54-50af-c0723a75b85f@2ndquadrant.com> <20180116063546.GA3679@paquier.xyz> <48502d9b-60c4-5b10-40a2-e34f562132b4@2ndquadrant.com> <20180119051804.GC12826@paquier.xyz> <2e3d5147-16f8-af0f-00ab-4c72cafc896f@2ndquadrant.com> <20180131131804.GB1586@paquier.xyz> <20180202022532.GA84623@paquier.xyz> <050e663d-d915-f61b-af96-5a09c10ade02@2ndquadrant.com> Cc: Michael Paquier , Jaime Casanova 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: <79e4de23-52e3-2c45-9ba0-5f6932f590aa@2ndquadrant.com> Date: Tue, 30 Oct 2018 09:35:18 +0100 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: <050e663d-d915-f61b-af96-5a09c10ade02@2ndquadrant.com> Content-Type: multipart/mixed; boundary="------------FDB6F0AD6C33236D71A9A554" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------FDB6F0AD6C33236D71A9A554 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit On 05/03/2018 20:46, Peter Eisentraut wrote: > On 2/1/18 21:25, Michael Paquier wrote: >> On Thu, Feb 01, 2018 at 09:29:09AM -0500, Peter Eisentraut wrote: >>> That would be nice. I'm going to study this some more to see what can >>> be done. >> >> Thanks for the update. Note: Peter has moved the patch to next CF. > > I didn't get to updating this patch, so I'm closing it in this CF. Attached is a new version of this patch. Old news: This is a well-known SQL-standard feature, also available for instance in DB2, MySQL, Oracle. A quick example: CREATE TABLE t1 ( ..., height_cm numeric, height_in numeric GENERATED ALWAYS AS (height_cm * 2.54) ); It supports both computed-on-write and computed-on-read variants, using the keywords STORED and VIRTUAL respectively. New news: Everything works more or less. Both STORED and VIRTUAL fully work now. I've done some refactoring to reduce the surface area of the patch. I also added some caching in the tuple descriptor's "const" area to avoid some performance overhead if no generated columns are used. There are some open questions about which I'll start separate subthreads for discussion. One thing I'd like reviewed now is the catalog representation. There are a couple of possible options, but changing them would have fairly deep code impact so it would help to get that settled soon. The general idea is that a generation expression is similar to a default, just applied at different times. So the actual generation expression is stored in pg_attrdef. The actual question is the representation in pg_attribute. Options: 1. (current implementation) New column attgenerated contains 's' for STORED, 'v' for VIRTUAL, '\0' for nothing. atthasdef means "there is something in pg_attrdef for this column". So a generated column would have atthasdef = true, and attgenerated = s/v. A traditional default would have atthasdef = true and attgenerated = '\0'. The advantage is that this is easiest to implement and the internal representation is the most useful and straightforward. The disadvantage is that old client code that wants to detect whether a column has a default would need to be changed (otherwise it would interpret a generated column as having a default value instead). 2. Alternative: A generated column has attgenerated = s/v but atthasdef = false, so that atthasdef means specifically "column has a default". Then a column would have a pg_attrdef entry for either attgenerated != '\0' or atthasdef = true. (Both couldn't be the case at the same time.) The advantage is that client code wouldn't need to be changed. But it's also possible that there is client code that just does a left join of pg_attribute and pg_attrdef without looking at atthasdef, so that would still be broken. The disadvantage is that the internal implementation would get considerably ugly. Most notably, the tuple descriptor would probably still look like #1, so there would have to be a conversion somewhere between variant #1 and #2. Or we'd have to duplicate all the tuple descriptor access code to keep that separate. There would be a lot of redundancy. 3. Radical alternative: Collapse everything into one new column. We could combine atthasdef and attgenerated and even attidentity into a new column. (Only one of the three can be the case.) This would give client code a clean break, which may or may not be good. The implementation would be uglier than #1 but probably cleaner than #2. We could also get 4 bytes back per pg_attribute row. I'm happy with the current choice #1, but it's worth thinking about. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --------------FDB6F0AD6C33236D71A9A554 Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0"; name="v5-0001-Generated-columns.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="v5-0001-Generated-columns.patch" =46rom dae07c731d80021bf78b8d89a8eb14408dbd023a Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 29 Oct 2018 17:46:12 +0100 Subject: [PATCH v5] Generated columns This is an SQL-standard feature that allows creating columns that are computed from expressions rather than assigned, similar to a view or materialized view but on a column basis. This implements two kinds of generated columns: virtual (computed on read) and stored (computed on write). --- doc/src/sgml/catalogs.sgml | 19 +- doc/src/sgml/information_schema.sgml | 66 +- doc/src/sgml/ref/copy.sgml | 3 +- doc/src/sgml/ref/create_table.sgml | 49 +- src/backend/access/common/tupdesc.c | 19 +- src/backend/catalog/heap.c | 80 +- src/backend/catalog/information_schema.sql | 30 +- src/backend/commands/copy.c | 38 +- src/backend/commands/indexcmds.c | 27 +- src/backend/commands/tablecmds.c | 219 +++++- src/backend/commands/trigger.c | 37 + src/backend/commands/typecmds.c | 6 +- src/backend/executor/execMain.c | 8 +- src/backend/executor/nodeModifyTable.c | 99 +++ src/backend/nodes/copyfuncs.c | 2 + src/backend/nodes/equalfuncs.c | 2 + src/backend/nodes/outfuncs.c | 9 + src/backend/parser/gram.y | 26 +- src/backend/parser/parse_agg.c | 11 + src/backend/parser/parse_expr.c | 5 + src/backend/parser/parse_func.c | 12 + src/backend/parser/parse_utilcmd.c | 115 ++- src/backend/rewrite/rewriteHandler.c | 152 +++- src/backend/utils/cache/lsyscache.c | 33 + src/backend/utils/cache/partcache.c | 3 + src/backend/utils/cache/relcache.c | 4 + src/bin/pg_dump/pg_dump.c | 43 +- src/bin/pg_dump/pg_dump.h | 1 + src/bin/pg_dump/pg_dump_sort.c | 10 + src/bin/pg_dump/t/002_pg_dump.pl | 17 + src/bin/psql/describe.c | 25 +- src/include/access/tupdesc.h | 1 + src/include/catalog/heap.h | 3 +- src/include/catalog/pg_attribute.h | 6 + src/include/catalog/pg_class.dat | 2 +- src/include/executor/nodeModifyTable.h | 2 + src/include/nodes/execnodes.h | 3 + src/include/nodes/parsenodes.h | 14 +- src/include/parser/kwlist.h | 2 + src/include/parser/parse_node.h | 3 +- src/include/rewrite/rewriteHandler.h | 1 + src/include/utils/lsyscache.h | 1 + src/pl/plperl/expected/plperl_trigger.out | 93 +++ src/pl/plperl/plperl.c | 7 +- src/pl/plperl/sql/plperl_trigger.sql | 34 + src/pl/plpython/expected/plpython_trigger.out | 92 +++ src/pl/plpython/plpy_exec.c | 6 + src/pl/plpython/plpy_typeio.c | 2 +- src/pl/plpython/sql/plpython_trigger.sql | 35 + src/pl/tcl/expected/pltcl_queries.out | 89 +++ src/pl/tcl/expected/pltcl_setup.out | 8 + src/pl/tcl/pltcl.c | 6 + src/pl/tcl/sql/pltcl_queries.sql | 25 + src/pl/tcl/sql/pltcl_setup.sql | 10 + .../regress/expected/create_table_like.out | 46 ++ src/test/regress/expected/generated.out | 737 ++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/create_table_like.sql | 14 + src/test/regress/sql/generated.sql | 408 ++++++++++ 60 files changed, 2731 insertions(+), 92 deletions(-) create mode 100644 src/test/regress/expected/generated.out create mode 100644 src/test/regress/sql/generated.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 9edba96fab..567913c3b6 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1143,9 +1143,11 @@ <structname>pg_attribute</structname> Colum= ns bool - This column has a default value, in which case there will be a - corresponding entry in the pg_attrdef - catalog that actually defines the value. + This column has a default expression or generation expression, in= which + case there will be a corresponding entry in the + pg_attrdef catalog that actually defines= the + expression. (Check attgenerated to + determine whether this is a default or a generation expression.) =20 @@ -1173,6 +1175,17 @@ <structname>pg_attribute</structname> Colum= ns =20 + + attgenerated + char + + + If a zero byte (''), then not a generated colu= mn. + Otherwise, s =3D stored, v = =3D + virtual. + + + attisdropped bool diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/informat= ion_schema.sgml index b13700da92..1321ade44a 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -952,6 +952,62 @@ <literal>collation_character_set_applicabilit= y</literal> Columns =20 + + <literal>column_column_usage</literal> + + + The view column_column_usage identifies all genera= ted + columns that depend on another base column in the same table. Only t= ables + owned by a currently enabled role are included. + + + + <literal>column_column_usage</literal> Columns + + + + + Name + Data Type + Description + + + + + + table_catalog + sql_identifier + Name of the database containing the table (always the curre= nt database) + + + + table_schema + sql_identifier + Name of the schema containing the table + + + + table_name + sql_identifier + Name of the table + + + + column_name + sql_identifier + Name of the base column that a generated column depends on<= /entry> + + + + dependent_column + sql_identifier + Name of the generated column + + + +
+ + <literal>column_domain_usage</literal> =20 @@ -1648,13 +1704,19 @@ <literal>columns</literal> Columns= is_generated character_data - Applies to a feature not available in PostgreS= QL + + If the column is a generated column, then ALWAYS, + else NEVER. + =20 generation_expression character_data - Applies to a feature not available in PostgreS= QL + + If the column is a generated column, then the generation expressi= on, + else null. + =20 diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 13a8b68d95..b1e8214b81 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -103,7 +103,8 @@ Parameters An optional list of columns to be copied. If no column list is - specified, all columns of the table will be copied. + specified, all columns of the table except generated columns will = be + copied. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create= _table.sgml index 10428f8ff0..15b95a2992 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -62,6 +62,7 @@ NULL | CHECK ( expression ) [ = NO INHERIT ] | DEFAULT default_expr | + GENERATED ALWAYS AS ( generation_expr ) [ V= IRTUAL | STORED ] | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequenc= e_options ) ] | UNIQUE index_parameters= | PRIMARY KEY index_parameters | @@ -82,7 +83,7 @@ =20 and like_option i= s: =20 -{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | IDENTITY= | INDEXES | STATISTICS | STORAGE | ALL } +{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATE= D | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL } =20 and partition_bound_spec is: =20 @@ -629,6 +630,17 @@ Parameters =20 + + INCLUDING GENERATED + + + Any generation expressions as well as the virtual/stored choic= e of + copied column definitions will be copied. By default, new col= umns + will be regular base columns. + + + + INCLUDING IDENTITY @@ -798,6 +810,31 @@ Parameters =20 + + GENERATED ALWAYS AS ( generation_expr ) [ VIRTUAL | STORED ]generated= column + + + This clause creates the column as a generated + column. The column cannot be written to, and when rea= d it + will be computed from the specified expression. + + + + When VIRTUAL is specified, the column will be + computed when it is read, and it will not occupy any storage. + When STORED is specified, the column will be co= mputed + on write and will be stored on disk. VIRTUAL i= s the + default. + + + + The generation expression can refer to other columns in the table,= but + not other generated columns. Any functions and operators used mus= t be + immutable. References to other tables are not allowed. + + + + GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] @@ -2070,6 +2107,16 @@ Multiple Identity Columns =20 + + Generated Columns + + + The options VIRTUAL and STORED= are + not standard but are also used by other SQL implementations. The SQ= L + standard does not specify the storage of generated columns. + + + <literal>LIKE</literal> Clause =20 diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/com= mon/tupdesc.c index b0434b4672..21bd0d86cd 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -133,6 +133,7 @@ CreateTupleDescCopy(TupleDesc tupdesc) att->atthasdef =3D false; att->atthasmissing =3D false; att->attidentity =3D '\0'; + att->attgenerated =3D '\0'; } =20 /* We can copy the tuple type identification, too */ @@ -167,6 +168,7 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc) TupleConstr *cpy =3D (TupleConstr *) palloc0(sizeof(TupleConstr)); =20 cpy->has_not_null =3D constr->has_not_null; + cpy->has_generated =3D constr->has_generated; =20 if ((cpy->num_defval =3D constr->num_defval) > 0) { @@ -249,6 +251,7 @@ TupleDescCopy(TupleDesc dst, TupleDesc src) att->atthasdef =3D false; att->atthasmissing =3D false; att->attidentity =3D '\0'; + att->attgenerated =3D '\0'; } dst->constr =3D NULL; =20 @@ -302,6 +305,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno= , dstAtt->atthasdef =3D false; dstAtt->atthasmissing =3D false; dstAtt->attidentity =3D '\0'; + dstAtt->attgenerated =3D '\0'; } =20 /* @@ -460,6 +464,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc= 2) return false; if (attr1->attidentity !=3D attr2->attidentity) return false; + if (attr1->attgenerated !=3D attr2->attgenerated) + return false; if (attr1->attisdropped !=3D attr2->attisdropped) return false; if (attr1->attislocal !=3D attr2->attislocal) @@ -480,6 +486,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc= 2) return false; if (constr1->has_not_null !=3D constr2->has_not_null) return false; + if (constr1->has_generated !=3D constr2->has_generated) + return false; n =3D constr1->num_defval; if (n !=3D (int) constr2->num_defval) return false; @@ -643,6 +651,7 @@ TupleDescInitEntry(TupleDesc desc, att->atthasdef =3D false; att->atthasmissing =3D false; att->attidentity =3D '\0'; + att->attgenerated =3D '\0'; att->attisdropped =3D false; att->attislocal =3D true; att->attinhcount =3D 0; @@ -702,6 +711,7 @@ TupleDescInitBuiltinEntry(TupleDesc desc, att->atthasdef =3D false; att->atthasmissing =3D false; att->attidentity =3D '\0'; + att->attgenerated =3D '\0'; att->attisdropped =3D false; att->attislocal =3D true; att->attinhcount =3D 0; @@ -855,17 +865,8 @@ BuildDescForRelation(List *schema) TupleConstr *constr =3D (TupleConstr *) palloc0(sizeof(TupleConstr)); =20 constr->has_not_null =3D true; - constr->defval =3D NULL; - constr->missing =3D NULL; - constr->num_defval =3D 0; - constr->check =3D NULL; - constr->num_check =3D 0; desc->constr =3D constr; } - else - { - desc->constr =3D NULL; - } =20 return desc; } diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 3c9c03c997..b6946dac2a 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -68,6 +68,7 @@ #include "parser/parse_collate.h" #include "parser/parse_expr.h" #include "parser/parse_relation.h" +#include "parser/parsetree.h" #include "storage/lmgr.h" #include "storage/predicate.h" #include "storage/smgr.h" @@ -685,6 +686,7 @@ InsertPgAttributeTuple(Relation pg_attribute_rel, values[Anum_pg_attribute_atthasdef - 1] =3D BoolGetDatum(new_attribute-= >atthasdef); values[Anum_pg_attribute_atthasmissing - 1] =3D BoolGetDatum(new_attrib= ute->atthasmissing); values[Anum_pg_attribute_attidentity - 1] =3D CharGetDatum(new_attribut= e->attidentity); + values[Anum_pg_attribute_attgenerated - 1] =3D CharGetDatum(new_attribu= te->attgenerated); values[Anum_pg_attribute_attisdropped - 1] =3D BoolGetDatum(new_attribu= te->attisdropped); values[Anum_pg_attribute_attislocal - 1] =3D BoolGetDatum(new_attribute= ->attislocal); values[Anum_pg_attribute_attinhcount - 1] =3D Int32GetDatum(new_attribu= te->attinhcount); @@ -2160,6 +2162,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum, Relation attrrel; HeapTuple atttup; Form_pg_attribute attStruct; + char attgenerated; Oid attrdefOid; ObjectAddress colobject, defobject; @@ -2215,6 +2218,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum, elog(ERROR, "cache lookup failed for attribute %d of relation %u", attnum, RelationGetRelid(rel)); attStruct =3D (Form_pg_attribute) GETSTRUCT(atttup); + attgenerated =3D attStruct->attgenerated; if (!attStruct->atthasdef) { Form_pg_attribute defAttStruct; @@ -2235,7 +2239,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum, valuesAtt[Anum_pg_attribute_atthasdef - 1] =3D true; replacesAtt[Anum_pg_attribute_atthasdef - 1] =3D true; =20 - if (add_column_mode) + if (add_column_mode && !attgenerated) { expr2 =3D expression_planner(expr2); estate =3D CreateExecutorState(); @@ -2297,7 +2301,26 @@ StoreAttrDefault(Relation rel, AttrNumber attnum, /* * Record dependencies on objects used in the expression, too. */ - recordDependencyOnExpr(&defobject, expr, NIL, DEPENDENCY_NORMAL); + if (attgenerated) + { + /* + * Generated column: Dropping anything that the generation expression + * refers to automatically drops the generated column. + */ + recordDependencyOnSingleRelExpr(&colobject, expr, RelationGetRelid(rel= ), + DEPENDENCY_AUTO, + DEPENDENCY_AUTO, false); + } + else + { + /* + * Normal default: Dropping anything that the default refers to + * requires CASCADE and drops the default only. + */ + recordDependencyOnSingleRelExpr(&defobject, expr, RelationGetRelid(rel= ), + DEPENDENCY_NORMAL, + DEPENDENCY_NORMAL, false); + } =20 /* * Post creation hook for attribute defaults. @@ -2566,7 +2589,8 @@ AddRelationNewConstraints(Relation rel, =20 expr =3D cookDefault(pstate, colDef->raw_default, atp->atttypid, atp->atttypmod, - NameStr(atp->attname)); + NameStr(atp->attname), + atp->attgenerated); =20 /* * If the expression is just a NULL constant, we do not bother to make= @@ -2937,6 +2961,46 @@ SetRelationNumChecks(Relation rel, int numchecks) heap_close(relrel, RowExclusiveLock); } =20 +/* + * Check for references to generated columns + */ +static bool +check_nested_generated_walker(Node *node, void *context) +{ + ParseState *pstate =3D context; + + if (node =3D=3D NULL) + return false; + else if (IsA(node, Var)) + { + Var *var =3D (Var *) node; + Oid relid; + AttrNumber attnum; + + relid =3D rt_fetch(var->varno, pstate->p_rtable)->relid; + attnum =3D var->varattno; + + if (relid && attnum && get_attgenerated(relid, attnum)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("cannot use generated column \"%s\" in column generation ex= pression", + get_attname(relid, attnum, false)), + errdetail("A generated column cannot reference another generated c= olumn."), + parser_errposition(pstate, var->location))); + + return false; + } + else + return expression_tree_walker(node, check_nested_generated_walker, + (void *) context); +} + +static void +check_nested_generated(ParseState *pstate, Node *node) +{ + check_nested_generated_walker(node, pstate); +} + /* * Take a raw default and convert it to a cooked format ready for * storage. @@ -2954,7 +3018,8 @@ cookDefault(ParseState *pstate, Node *raw_default, Oid atttypid, int32 atttypmod, - const char *attname) + const char *attname, + char attgenerated) { Node *expr; =20 @@ -2963,17 +3028,20 @@ cookDefault(ParseState *pstate, /* * Transform raw parsetree to executable expression. */ - expr =3D transformExpr(pstate, raw_default, EXPR_KIND_COLUMN_DEFAULT); + expr =3D transformExpr(pstate, raw_default, attgenerated ? EXPR_KIND_GE= NERATED_COLUMN : EXPR_KIND_COLUMN_DEFAULT); =20 /* * Make sure default expr does not refer to any vars (we need this chec= k * since the pstate includes the target table). */ - if (contain_var_clause(expr)) + if (!attgenerated && contain_var_clause(expr)) ereport(ERROR, (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), errmsg("cannot use column references in default expression"))); =20 + if (attgenerated) + check_nested_generated(pstate, expr); + /* * transformExpr() should have already rejected subqueries, aggregates,= * window functions, and SRFs, based on the EXPR_KIND_ for a default diff --git a/src/backend/catalog/information_schema.sql b/src/backend/cat= alog/information_schema.sql index f4e69f4a26..c3825b4ed1 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -509,7 +509,29 @@ CREATE VIEW collation_character_set_applicability AS= * COLUMN_COLUMN_USAGE view */ =20 --- feature not supported +CREATE VIEW column_column_usage AS + SELECT CAST(current_database() AS sql_identifier) AS table_catalog, + CAST(n.nspname AS sql_identifier) AS table_schema, + CAST(c.relname AS sql_identifier) AS table_name, + CAST(ac.attname AS sql_identifier) AS column_name, + CAST(ad.attname AS sql_identifier) AS dependent_column + + FROM pg_namespace n, pg_class c, pg_depend d, + pg_attribute ac, pg_attribute ad + + WHERE n.oid =3D c.relnamespace + AND c.oid =3D ac.attrelid + AND c.oid =3D ad.attrelid + AND d.classid =3D 'pg_catalog.pg_class'::regclass + AND d.refclassid =3D 'pg_catalog.pg_class'::regclass + AND d.objid =3D d.refobjid + AND c.oid =3D d.objid + AND d.objsubid =3D ad.attnum + AND d.refobjsubid =3D ac.attnum + AND ad.attgenerated <> '' + AND pg_has_role(c.relowner, 'USAGE'); + +GRANT SELECT ON column_column_usage TO PUBLIC; =20 =20 /* @@ -656,7 +678,7 @@ CREATE VIEW columns AS CAST(c.relname AS sql_identifier) AS table_name, CAST(a.attname AS sql_identifier) AS column_name, CAST(a.attnum AS cardinal_number) AS ordinal_position, - CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS = column_default, + CAST(CASE WHEN a.attgenerated =3D '' THEN pg_get_expr(ad.adbi= n, ad.adrelid) END AS character_data) AS column_default, CAST(CASE WHEN a.attnotnull OR (t.typtype =3D 'd' AND t.typno= tnull) THEN 'NO' ELSE 'YES' END AS yes_or_no) AS is_nullable, @@ -745,8 +767,8 @@ CREATE VIEW columns AS CAST(seq.seqmin AS character_data) AS identity_minimum, CAST(CASE WHEN seq.seqcycle THEN 'YES' ELSE 'NO' END AS yes_o= r_no) AS identity_cycle, =20 - CAST('NEVER' AS character_data) AS is_generated, - CAST(null AS character_data) AS generation_expression, + CAST(CASE WHEN a.attgenerated <> '' THEN 'ALWAYS' ELSE 'NEVER= ' END AS character_data) AS is_generated, + CAST(CASE WHEN a.attgenerated <> '' THEN pg_get_expr(ad.adbin= , ad.adrelid) END AS character_data) AS generation_expression, =20 CAST(CASE WHEN c.relkind IN ('r', 'p') OR (c.relkind IN ('v', 'f') AND diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index b58a74f4e3..3a7b229bca 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -31,6 +31,7 @@ #include "commands/trigger.h" #include "executor/execPartition.h" #include "executor/executor.h" +#include "executor/nodeModifyTable.h" #include "executor/tuptable.h" #include "foreign/fdwapi.h" #include "libpq/libpq.h" @@ -2911,6 +2912,28 @@ CopyFrom(CopyState cstate) } else { + /* + * Normal case: insert tuple into table + */ + + HeapTuple newtuple; + + /* + * Compute stored generated columns + * + * Switch memory context so that the new tuple is in the same + * context as the old one. Note that we don't use the slot's + * context. + */ + if (resultRelInfo->ri_RelationDesc->rd_att->constr && + resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated) + { + MemoryContextSwitchTo(GetPerTupleMemoryContext(estate)); + if ((newtuple =3D ExecComputeStoredGenerated(estate, slot))) + tuple =3D newtuple; + MemoryContextSwitchTo(oldcontext); + } + /* * If the target is a plain table, check the constraints of * the tuple. @@ -3257,7 +3280,7 @@ BeginCopyFrom(ParseState *pstate, fmgr_info(in_func_oid, &in_functions[attnum - 1]); =20 /* Get default info if needed */ - if (!list_member_int(cstate->attnumlist, attnum)) + if (!list_member_int(cstate->attnumlist, attnum) && !att->attgenerated= ) { /* attribute is NOT to be copied from input */ /* use default value if one exists */ @@ -4920,6 +4943,11 @@ CopyAttributeOutCSV(CopyState cstate, char *string= , * or NIL if there was none (in which case we want all the non-dropped * columns). * + * We don't include generated columns in the generated full list and we = don't + * allow them to be specified explicitly. They don't make sense for COP= Y + * FROM, but we could possibly allow them for COPY TO. But this way it'= s at + * least ensured that whatever we copy out can be copied back in. + * * rel can be NULL ... it's only used for error reports. */ static List * @@ -4937,6 +4965,8 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, Lis= t *attnamelist) { if (TupleDescAttr(tupDesc, i)->attisdropped) continue; + if (TupleDescAttr(tupDesc, i)->attgenerated) + continue; attnums =3D lappend_int(attnums, i + 1); } } @@ -4961,6 +4991,12 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, Li= st *attnamelist) continue; if (namestrcmp(&(att->attname), name) =3D=3D 0) { + if (att->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("column \"%s\" is a generated column", + name), + errdetail("Generated columns cannot be used in COPY."))); attnum =3D att->attnum; break; } diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/inde= xcmds.c index 906d711378..c3342baacc 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -750,6 +750,9 @@ DefineIndex(Oid relationId, /* * We disallow indexes on system columns other than OID. They would no= t * necessarily get updated correctly, and they don't seem useful anyway= =2E + * + * Also disallow virtual generated columns in indexes (use expression + * index instead). */ for (i =3D 0; i < indexInfo->ii_NumIndexAttrs; i++) { @@ -759,10 +762,16 @@ DefineIndex(Oid relationId, ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("index creation on system columns is not supported"))); + + if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated =3D=3D= ATTRIBUTE_GENERATED_VIRTUAL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("index creation on virtual generated columns is not support= ed"))); } =20 /* - * Also check for system columns used in expressions or predicates. + * Also check for system and generated columns used in expressions or + * predicates. */ if (indexInfo->ii_Expressions || indexInfo->ii_Predicate) { @@ -780,6 +789,22 @@ DefineIndex(Oid relationId, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("index creation on system columns is not supported"))); } + + /* + * XXX Virtual generated columns in index expressions or predicates + * could be supported, but it needs support in + * RelationGetIndexExpressions() and RelationGetIndexPredicate(). + */ + i =3D -1; + while ((i =3D bms_next_member(indexattrs, i)) >=3D 0) + { + AttrNumber attno =3D i + FirstLowInvalidHeapAttributeNumber; + + if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated =3D= =3D ATTRIBUTE_GENERATED_VIRTUAL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("index creation on virtual generated columns is not suppor= ted"))); + } } =20 /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tabl= ecmds.c index 153aec263e..7873a386a1 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -742,6 +742,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ow= nerId, =20 if (colDef->identity) attr->attidentity =3D colDef->identity; + + if (colDef->generated) + attr->attgenerated =3D colDef->generated; } =20 /* @@ -787,6 +790,27 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid o= wnerId, */ rel =3D relation_open(relationId, AccessExclusiveLock); =20 + /* + * Now add any newly specified column default and generation expression= s + * to the new relation. These are passed to us in the form of raw + * parsetrees; we need to transform them to executable expression trees= + * before they can be added. The most convenient way to do that is to + * apply the parser's transformExpr routine, but transformExpr doesn't + * work unless we have a pre-existing relation. So, the transformation = has + * to be postponed to this final step of CREATE TABLE. + * + * This needs to be before processing the partitioning clauses because + * those could refer to generated columns. + */ + if (rawDefaults) + AddRelationNewConstraints(rel, rawDefaults, NIL, + true, true, false, queryString); + + /* + * Make column generation expressions visible for use by partitioning. + */ + CommandCounterIncrement(); + /* Process and store partition bound, if any. */ if (stmt->partbound) { @@ -979,16 +1003,12 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid= ownerId, } =20 /* - * Now add any newly specified column default values and CHECK constrai= nts - * to the new relation. These are passed to us in the form of raw - * parsetrees; we need to transform them to executable expression trees= - * before they can be added. The most convenient way to do that is to - * apply the parser's transformExpr routine, but transformExpr doesn't - * work unless we have a pre-existing relation. So, the transformation = has - * to be postponed to this final step of CREATE TABLE. + * Now add any newly specified CHECK constraints to the new relation. + * Same as for defaults above, but these need to come after partitionin= g + * is set up. */ - if (rawDefaults || stmt->constraints) - AddRelationNewConstraints(rel, rawDefaults, stmt->constraints, + if (stmt->constraints) + AddRelationNewConstraints(rel, NIL, stmt->constraints, true, true, false, queryString); =20 ObjectAddressSet(address, RelationRelationId, relationId); @@ -2152,6 +2172,13 @@ MergeAttributes(List *schema, List *supers, char r= elpersistence, def->is_not_null |=3D attribute->attnotnull; /* Default and other constraints are handled below */ newattno[parent_attno - 1] =3D exist_attno; + + /* Check for GENERATED conflicts */ + if (def->generated !=3D attribute->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("inherited column \"%s\" has a generation conflict", + attributeName))); } else { @@ -2170,6 +2197,7 @@ MergeAttributes(List *schema, List *supers, char re= lpersistence, def->storage =3D attribute->attstorage; def->raw_default =3D NULL; def->cooked_default =3D NULL; + def->generated =3D attribute->attgenerated; def->collClause =3D NULL; def->collOid =3D attribute->attcollation; def->constraints =3D NIL; @@ -4637,7 +4665,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHea= p, LOCKMODE lockmode) { case CONSTR_CHECK: needscan =3D true; - con->qualstate =3D ExecPrepareExpr((Expr *) con->qual, estate); + con->qualstate =3D ExecPrepareExpr((Expr *) expand_generated_columns= _in_expr(con->qual, + newrel ? newrel : oldrel), + estate); break; case CONSTR_FOREIGN: /* Nothing to do here */ @@ -5536,6 +5566,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *ta= b, Relation rel, attribute.atthasdef =3D false; attribute.atthasmissing =3D false; attribute.attidentity =3D colDef->identity; + attribute.attgenerated =3D colDef->generated; attribute.attisdropped =3D false; attribute.attislocal =3D colDef->is_local; attribute.attinhcount =3D colDef->inhcount; @@ -5945,6 +5976,18 @@ ATExecDropNotNull(Relation rel, const char *colNam= e, LOCKMODE lockmode) errmsg("cannot alter system column \"%s\"", colName))); =20 + /* + * Virtual generated columns don't use the attnotnull field but use a f= ull + * CHECK constraint instead. We could implement here that it finds tha= t + * CHECK constraint and drops it, which is kind of what the SQL standar= d + * would require anyway, but that would be quite a bit more work. + */ + if (attTup->attgenerated =3D=3D ATTRIBUTE_GENERATED_VIRTUAL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use DROP NOT NULL on virtual generated column \"%s\"= ", + colName))); + if (attTup->attidentity) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -6093,6 +6136,17 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation r= el, errmsg("cannot alter system column \"%s\"", colName))); =20 + /* + * XXX We might want to convert this to a CHECK constraint like we do i= n + * transformColumnDefinition(). + */ + if (((Form_pg_attribute) GETSTRUCT(tuple))->attgenerated =3D=3D ATTRIBU= TE_GENERATED_VIRTUAL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use SET NOT NULL on virtual generated column \"%s\""= , + colName), + errhint("Add a CHECK constraint instead."))); + /* * Okay, actually perform the catalog change ... if needed */ @@ -6156,6 +6210,12 @@ ATExecColumnDefault(Relation rel, const char *colN= ame, colName, RelationGetRelationName(rel)), newDefault ? 0 : errhint("Use ALTER TABLE ... ALTER COLUMN ... DROP= IDENTITY instead."))); =20 + if (TupleDescAttr(tupdesc, attnum - 1)->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("column \"%s\" of relation \"%s\" is a generated column", + colName, RelationGetRelationName(rel)))); + /* * Remove any old default for the column. We use RESTRICT here for * safety, but at present we do not expect anything to depend on the @@ -7470,6 +7530,41 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTa= bleInfo *tab, Relation rel, */ checkFkeyPermissions(pkrel, pkattnum, numpks); =20 + /* + * Check some things for generated columns. + */ + for (i =3D 0; i < numfks; i++) + { + char attgenerated =3D TupleDescAttr(RelationGetDescr(rel), fkattnum[i= ] - 1)->attgenerated; + + if (attgenerated) + { + /* + * Check restrictions on UPDATE/DELETE actions, per SQL standard + */ + if (fkconstraint->fk_upd_action =3D=3D FKCONSTR_ACTION_SETNULL || + fkconstraint->fk_upd_action =3D=3D FKCONSTR_ACTION_SETDEFAULT || + fkconstraint->fk_upd_action =3D=3D FKCONSTR_ACTION_CASCADE) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid ON UPDATE action for foreign key constraint conta= ining generated column"))); + if (fkconstraint->fk_del_action =3D=3D FKCONSTR_ACTION_SETNULL || + fkconstraint->fk_del_action =3D=3D FKCONSTR_ACTION_SETDEFAULT) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid ON DELETE action for foreign key constraint conta= ining generated column"))); + } + + /* + * FKs on virtual columns are not supported, does not have support in + * ri_triggers.c + */ + if (attgenerated =3D=3D ATTRIBUTE_GENERATED_VIRTUAL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("foreign key constraints on virtual generated columns are n= ot supported"))); + } + /* * Look up the equality operators to use in the constraint. * @@ -8475,7 +8570,7 @@ validateCheckConstraint(Relation rel, HeapTuple con= strtup) EState *estate; Datum val; char *conbin; - Expr *origexpr; + Node *origexpr; ExprState *exprstate; TupleDesc tupdesc; HeapScanDesc scan; @@ -8510,8 +8605,8 @@ validateCheckConstraint(Relation rel, HeapTuple con= strtup) elog(ERROR, "null conbin for constraint %u", HeapTupleGetOid(constrtup)); conbin =3D TextDatumGetCString(val); - origexpr =3D (Expr *) stringToNode(conbin); - exprstate =3D ExecPrepareExpr(origexpr, estate); + origexpr =3D stringToNode(conbin); + exprstate =3D ExecPrepareExpr((Expr *) expand_generated_columns_in_expr= (origexpr, rel), estate); =20 econtext =3D GetPerTupleExprContext(estate); tupdesc =3D RelationGetDescr(rel); @@ -9160,8 +9255,9 @@ ATPrepAlterColumnType(List **wqueue, list_make1_oid(rel->rd_rel->reltype), false); =20 - if (tab->relkind =3D=3D RELKIND_RELATION || - tab->relkind =3D=3D RELKIND_PARTITIONED_TABLE) + if ((tab->relkind =3D=3D RELKIND_RELATION || + tab->relkind =3D=3D RELKIND_PARTITIONED_TABLE) && + attTup->attgenerated !=3D ATTRIBUTE_GENERATED_VIRTUAL) { /* * Set up an expression to transform the old data value to the new @@ -9435,10 +9531,18 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Rela= tion rel, COERCE_IMPLICIT_CAST, -1); if (defaultexpr =3D=3D NULL) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("default for column \"%s\" cannot be cast automatically to = type %s", - colName, format_type_be(targettype)))); + { + if (attTup->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("generation expression for column \"%s\" cannot be cast au= tomatically to type %s", + colName, format_type_be(targettype)))); + else + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("default for column \"%s\" cannot be cast automatically to= type %s", + colName, format_type_be(targettype)))); + } } else defaultexpr =3D NULL; @@ -9514,6 +9618,21 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relat= ion rel, */ Assert(foundObject.objectSubId =3D=3D 0); } + else if (relKind =3D=3D RELKIND_RELATION && + foundObject.objectSubId !=3D 0 && + get_attgenerated(foundObject.objectId, foundObject.objectSubId))= + { + /* + * Changing the type of a column that is used by a + * generated column is not allowed by SQL standard. + * It might be doable with some thinking and effort. + */ + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("cannot alter type of a column used by a generated colum= n"), + errdetail("Column \"%s\" is used by generated column \"%s\".", + colName, get_attname(foundObject.objectId, foundObject.obje= ctSubId, false)))); + } else { /* Not expecting any other direct dependencies... */ @@ -9658,7 +9777,8 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relati= on rel, /* * Now scan for dependencies of this column on other things. The only * thing we should find is the dependency on the column datatype, which= we - * want to remove, and possibly a collation dependency. + * want to remove, possibly a collation dependency, and dependencies on= + * other columns if it is a generated column. */ ScanKeyInit(&key[0], Anum_pg_depend_classid, @@ -9679,15 +9799,26 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Rela= tion rel, while (HeapTupleIsValid(depTup =3D systable_getnext(scan))) { Form_pg_depend foundDep =3D (Form_pg_depend) GETSTRUCT(depTup); + ObjectAddress foundObject; =20 - if (foundDep->deptype !=3D DEPENDENCY_NORMAL) + foundObject.classId =3D foundDep->refclassid; + foundObject.objectId =3D foundDep->refobjid; + foundObject.objectSubId =3D foundDep->refobjsubid; + + if (foundDep->deptype !=3D DEPENDENCY_NORMAL && + foundDep->deptype !=3D DEPENDENCY_AUTO) elog(ERROR, "found unexpected dependency type '%c'", foundDep->deptype); if (!(foundDep->refclassid =3D=3D TypeRelationId && foundDep->refobjid =3D=3D attTup->atttypid) && !(foundDep->refclassid =3D=3D CollationRelationId && - foundDep->refobjid =3D=3D attTup->attcollation)) - elog(ERROR, "found unexpected dependency for column"); + foundDep->refobjid =3D=3D attTup->attcollation) && + !(foundDep->refclassid =3D=3D RelationRelationId && + foundDep->refobjid =3D=3D RelationGetRelid(rel) && + foundDep->refobjsubid !=3D 0) + ) + elog(ERROR, "found unexpected dependency for column: %s", + getObjectDescription(&foundObject)); =20 CatalogTupleDelete(depRel, &depTup->t_self); } @@ -13716,6 +13847,18 @@ ComputePartitionAttrs(ParseState *pstate, Relati= on rel, List *partParams, AttrNu pelem->name), parser_errposition(pstate, pelem->location))); =20 + /* + * Some generated columns could perhaps be supported in partition + * expressions instead; see below. + */ + if (attform->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("using generated column in partition key is not supported"= ), + errdetail("Column \"%s\" is a generated column.", + pelem->name), + parser_errposition(pstate, pelem->location))); + partattrs[attn] =3D attform->attnum; atttype =3D attform->atttypid; attcollation =3D attform->attcollation; @@ -13803,6 +13946,36 @@ ComputePartitionAttrs(ParseState *pstate, Relati= on rel, List *partParams, AttrNu errmsg("partition key expressions cannot contain system column = references"))); } =20 + /* + * Generated columns in partition key expressions: + * + * - Stored generated columns cannot work: They are computed + * after BEFORE triggers, but partition routing is done + * before all triggers. + * + * - Virtual generated columns could work. But there is a + * problem when dropping such a table: Dropping a table + * calls relation_open(), which causes partition keys to be + * constructed for the partcache, but at that point the + * generation expression is already deleted (through + * dependencies), so this will fail. So if you remove the + * restriction below, things will appear to work, but you + * can't drop the table. :-( + */ + i =3D -1; + while ((i =3D bms_next_member(expr_attrs, i)) >=3D 0) + { + AttrNumber attno =3D i + FirstLowInvalidHeapAttributeNumber; + + if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("using generated column in partition key is not supporte= d"), + errdetail("Column \"%s\" is a generated column.", + get_attname(RelationGetRelid(rel), attno, false)), + parser_errposition(pstate, pelem->location))); + } + /* * While it is not exactly *wrong* for a partition expression * to be a constant, it seems better to reject such keys. diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigge= r.c index 240e85e391..0d26282324 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -44,6 +44,7 @@ #include "parser/parse_relation.h" #include "parser/parsetree.h" #include "pgstat.h" +#include "rewrite/rewriteHandler.h" #include "rewrite/rewriteManip.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" @@ -102,6 +103,7 @@ static void AfterTriggerSaveEvent(EState *estate, Res= ultRelInfo *relinfo, TransitionCaptureState *transition_capture); static void AfterTriggerEnlargeQueryState(void); static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType); +static void check_modified_virtual_generated(TupleDesc tupdesc, HeapTupl= e tuple); =20 =20 /* @@ -638,6 +640,11 @@ CreateTrigger(CreateTrigStmt *stmt, const char *quer= yString, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("BEFORE trigger's WHEN condition cannot reference NEW sy= stem columns"), parser_errposition(pstate, var->location))); + if (TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attgen= erated && TRIGGER_FOR_BEFORE(tgtype)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("BEFORE trigger's WHEN condition cannot reference NEW ge= nerated columns"), + parser_errposition(pstate, var->location))); break; default: /* can't happen without add_missing_from, so just elog */ @@ -2571,6 +2578,8 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo = *relinfo, TupleTableSlot *newslot =3D estate->es_trig_tuple_slot; TupleDesc tupdesc =3D RelationGetDescr(relinfo->ri_RelationDesc); =20 + check_modified_virtual_generated(tupdesc, newtuple); + if (newslot->tts_tupleDescriptor !=3D tupdesc) ExecSetSlotDescriptor(newslot, tupdesc); ExecStoreHeapTuple(newtuple, newslot, false); @@ -3078,6 +3087,8 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqs= tate, TupleTableSlot *newslot =3D estate->es_trig_tuple_slot; TupleDesc tupdesc =3D RelationGetDescr(relinfo->ri_RelationDesc); =20 + check_modified_virtual_generated(tupdesc, newtuple); + if (newslot->tts_tupleDescriptor !=3D tupdesc) ExecSetSlotDescriptor(newslot, tupdesc); ExecStoreHeapTuple(newtuple, newslot, false); @@ -3484,6 +3495,7 @@ TriggerEnabled(EState *estate, ResultRelInfo *relin= fo, =20 oldContext =3D MemoryContextSwitchTo(estate->es_query_cxt); tgqual =3D stringToNode(trigger->tgqual); + tgqual =3D (Node *) expand_generated_columns_in_expr(tgqual, relinfo-= >ri_RelationDesc); /* Change references to OLD and NEW to INNER_VAR and OUTER_VAR */ ChangeVarNodes(tgqual, PRS2_OLD_VARNO, INNER_VAR, 0); ChangeVarNodes(tgqual, PRS2_NEW_VARNO, OUTER_VAR, 0); @@ -6159,3 +6171,28 @@ pg_trigger_depth(PG_FUNCTION_ARGS) { PG_RETURN_INT32(MyTriggerDepth); } + +/* + * Check whether a trigger modified a virtual generated column and error= if + * so. + */ +static void +check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple tuple) +{ + if (!(tupdesc->constr && tupdesc->constr->has_generated)) + return; + + for (int i =3D 0; i < tupdesc->natts; i++) + { + if (TupleDescAttr(tupdesc, i)->attgenerated =3D=3D ATTRIBUTE_GENERATED= _VIRTUAL) + { + bool isnull; + + fastgetattr(tuple, i + 1, tupdesc, &isnull); + if (!isnull) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("trigger modified virtual generated column value"))); + } + } +} diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typec= mds.c index 3271962a7a..e81294f278 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -914,7 +914,8 @@ DefineDomain(CreateDomainStmt *stmt) defaultExpr =3D cookDefault(pstate, constr->raw_expr, basetypeoid, basetypeMod, - domainName); + domainName, + 0); =20 /* * If the expression is just a NULL constant, we treat it @@ -2219,7 +2220,8 @@ AlterDomainDefault(List *names, Node *defaultRaw) defaultExpr =3D cookDefault(pstate, defaultRaw, typTup->typbasetype, typTup->typtypmod, - NameStr(typTup->typname)); + NameStr(typTup->typname), + 0); =20 /* * If the expression is just a NULL constant, we treat the command diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execM= ain.c index ba156f8c5f..521db10d48 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -53,7 +53,7 @@ #include "miscadmin.h" #include "optimizer/clauses.h" #include "parser/parsetree.h" -#include "rewrite/rewriteManip.h" +#include "rewrite/rewriteHandler.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "tcop/utility.h" @@ -1321,6 +1321,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_FdwState =3D NULL; resultRelInfo->ri_usesFdwDirectModify =3D false; resultRelInfo->ri_ConstraintExprs =3D NULL; + resultRelInfo->ri_GeneratedExprs =3D NULL; resultRelInfo->ri_junkFilter =3D NULL; resultRelInfo->ri_projectReturning =3D NULL; resultRelInfo->ri_onConflictArbiterIndexes =3D NIL; @@ -1809,6 +1810,7 @@ ExecRelCheck(ResultRelInfo *resultRelInfo, Expr *checkconstr; =20 checkconstr =3D stringToNode(check[i].ccbin); + checkconstr =3D (Expr *) expand_generated_columns_in_expr((Node *) ch= eckconstr, rel); resultRelInfo->ri_ConstraintExprs[i] =3D ExecPrepareExpr(checkconstr, estate); } @@ -2297,6 +2299,10 @@ ExecBuildSlotValueDescription(Oid reloid, if (att->attisdropped) continue; =20 + /* ignore virtual generated columns; they are always null here */ + if (att->attgenerated =3D=3D ATTRIBUTE_GENERATED_VIRTUAL) + continue; + if (!table_perm) { /* diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executo= r/nodeModifyTable.c index 528f58717e..73332eecab 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -46,6 +46,7 @@ #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" +#include "rewrite/rewriteHandler.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "utils/builtins.h" @@ -250,6 +251,84 @@ ExecCheckTIDVisible(EState *estate, ReleaseBuffer(buffer); } =20 +HeapTuple +ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot) +{ + ResultRelInfo *resultRelInfo =3D estate->es_result_relation_info; + Relation rel =3D resultRelInfo->ri_RelationDesc; + TupleDesc tupdesc =3D RelationGetDescr(rel); + int natts =3D tupdesc->natts; + Datum *values; + bool *nulls; + bool *replaces; + bool any_changes =3D false; + + values =3D palloc(sizeof(*values) * natts); + nulls =3D palloc(sizeof(*nulls) * natts); + replaces =3D palloc0(sizeof(*replaces) * natts); + + /* + * If first time through for this result relation, build expression + * nodetrees for rel's stored generation expressions. Keep them in the= + * per-query memory context so they'll survive throughout the query. + */ + if (resultRelInfo->ri_GeneratedExprs =3D=3D NULL) + { + MemoryContext oldContext; + + oldContext =3D MemoryContextSwitchTo(estate->es_query_cxt); + resultRelInfo->ri_GeneratedExprs =3D + (ExprState **) palloc(natts * sizeof(ExprState *)); + + for (int i =3D 0; i < natts; i++) + { + if (TupleDescAttr(tupdesc, i)->attgenerated =3D=3D ATTRIBUTE_GENERATE= D_STORED) + { + Expr *expr; + + expr =3D (Expr *) build_column_default(rel, i + 1); + Assert(expr); + + resultRelInfo->ri_GeneratedExprs[i] =3D ExecPrepareExpr(expr, estate= ); + } + } + + MemoryContextSwitchTo(oldContext); + } + + for (int i =3D 0; i < natts; i++) + { + if (TupleDescAttr(tupdesc, i)->attgenerated =3D=3D ATTRIBUTE_GENERATED= _STORED) + { + ExprContext *econtext; + Datum val; + bool isnull; + + econtext =3D GetPerTupleExprContext(estate); + econtext->ecxt_scantuple =3D slot; + + val =3D ExecEvalExprSwitchContext(resultRelInfo->ri_GeneratedExprs[i]= , econtext, &isnull); + + values[i] =3D val; + nulls[i] =3D isnull; + replaces[i] =3D true; + any_changes =3D true; + } + } + + if (any_changes) + { + HeapTuple tuple; + + tuple =3D ExecFetchSlotTuple(slot); + tuple =3D heap_modify_tuple(tuple, tupdesc, values, nulls, replaces); + ExecStoreHeapTuple(tuple, slot, false); + return tuple; + } + + return NULL; +} + /* ---------------------------------------------------------------- * ExecInsert * @@ -372,6 +451,16 @@ ExecInsert(ModifyTableState *mtstate, */ tuple->t_tableOid =3D RelationGetRelid(resultRelationDesc); =20 + /* + * Compute stored generated columns + */ + if (resultRelationDesc->rd_att->constr && + resultRelationDesc->rd_att->constr->has_generated) + { + ExecComputeStoredGenerated(estate, slot); + tuple =3D ExecMaterializeSlot(slot); + } + /* * Check any RLS WITH CHECK policies. * @@ -1027,6 +1116,16 @@ ExecUpdate(ModifyTableState *mtstate, */ tuple->t_tableOid =3D RelationGetRelid(resultRelationDesc); =20 + /* + * Compute stored generated columns + */ + if (resultRelationDesc->rd_att->constr && + resultRelationDesc->rd_att->constr->has_generated) + { + ExecComputeStoredGenerated(estate, slot); + tuple =3D ExecMaterializeSlot(slot); + } + /* * Check any RLS UPDATE WITH CHECK policies * diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.= c index e8ea59e34a..6b9752a020 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2879,6 +2879,7 @@ _copyColumnDef(const ColumnDef *from) COPY_NODE_FIELD(cooked_default); COPY_SCALAR_FIELD(identity); COPY_NODE_FIELD(identitySequence); + COPY_SCALAR_FIELD(generated); COPY_NODE_FIELD(collClause); COPY_SCALAR_FIELD(collOid); COPY_NODE_FIELD(constraints); @@ -2902,6 +2903,7 @@ _copyConstraint(const Constraint *from) COPY_NODE_FIELD(raw_expr); COPY_STRING_FIELD(cooked_expr); COPY_SCALAR_FIELD(generated_when); + COPY_SCALAR_FIELD(generated_kind); COPY_NODE_FIELD(keys); COPY_NODE_FIELD(including); COPY_NODE_FIELD(exclusions); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfunc= s.c index 3bb91c9595..0459dd344f 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2559,6 +2559,7 @@ _equalColumnDef(const ColumnDef *a, const ColumnDef= *b) COMPARE_NODE_FIELD(cooked_default); COMPARE_SCALAR_FIELD(identity); COMPARE_NODE_FIELD(identitySequence); + COMPARE_SCALAR_FIELD(generated); COMPARE_NODE_FIELD(collClause); COMPARE_SCALAR_FIELD(collOid); COMPARE_NODE_FIELD(constraints); @@ -2580,6 +2581,7 @@ _equalConstraint(const Constraint *a, const Constra= int *b) COMPARE_NODE_FIELD(raw_expr); COMPARE_STRING_FIELD(cooked_expr); COMPARE_SCALAR_FIELD(generated_when); + COMPARE_SCALAR_FIELD(generated_kind); COMPARE_NODE_FIELD(keys); COMPARE_NODE_FIELD(including); COMPARE_NODE_FIELD(exclusions); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 69731ccdea..14e2184f5a 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2880,6 +2880,7 @@ _outColumnDef(StringInfo str, const ColumnDef *node= ) WRITE_NODE_FIELD(cooked_default); WRITE_CHAR_FIELD(identity); WRITE_NODE_FIELD(identitySequence); + WRITE_CHAR_FIELD(generated); WRITE_NODE_FIELD(collClause); WRITE_OID_FIELD(collOid); WRITE_NODE_FIELD(constraints); @@ -3551,6 +3552,14 @@ _outConstraint(StringInfo str, const Constraint *n= ode) WRITE_CHAR_FIELD(generated_when); break; =20 + case CONSTR_GENERATED: + appendStringInfoString(str, "GENERATED"); + WRITE_NODE_FIELD(raw_expr); + WRITE_STRING_FIELD(cooked_expr); + WRITE_CHAR_FIELD(generated_when); + WRITE_CHAR_FIELD(generated_kind); + break; + case CONSTR_CHECK: appendStringInfoString(str, "CHECK"); WRITE_BOOL_FIELD(is_no_inherit); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 6d23bfb0b3..bdc64d2f4f 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -575,7 +575,7 @@ static Node *makeRecursiveViewSelect(char *relname, L= ist *aliases, Node *query); %type opt_window_exclusion_clause %type opt_existing_window_name %type opt_if_not_exists -%type generated_when override_kind +%type generated_when override_kind opt_virtual_or_stored %type PartitionSpec OptPartitionSpec %type part_strategy %type part_elem @@ -676,7 +676,7 @@ static Node *makeRecursiveViewSelect(char *relname, L= ist *aliases, Node *query); SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENC= E SEQUENCES SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P - START STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P + START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P= SUBSCRIPTION SUBSTRING SYMMETRIC SYSID SYSTEM_P =20 TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN= @@ -688,7 +688,7 @@ static Node *makeRecursiveViewSelect(char *relname, L= ist *aliases, Node *query); UNTIL UPDATE USER USING =20 VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING= - VERBOSE VERSION_P VIEW VIEWS VOLATILE + VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE =20 WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE =20 @@ -3553,6 +3553,17 @@ ColConstraintElem: n->location =3D @1; $$ =3D (Node *)n; } + | GENERATED generated_when AS '(' a_expr ')' opt_virtual_or_stored + { + Constraint *n =3D makeNode(Constraint); + n->contype =3D CONSTR_GENERATED; + n->generated_when =3D $2; + n->raw_expr =3D $5; + n->cooked_expr =3D NULL; + n->generated_kind =3D $7; + n->location =3D @1; + $$ =3D (Node *)n; + } | REFERENCES qualified_name opt_column_list key_match key_actions { Constraint *n =3D makeNode(Constraint); @@ -3575,6 +3586,12 @@ generated_when: | BY DEFAULT { $$ =3D ATTRIBUTE_IDENTITY_BY_DEFAULT; } ; =20 +opt_virtual_or_stored: + STORED { $$ =3D ATTRIBUTE_GENERATED_STORED; } + | VIRTUAL { $$ =3D ATTRIBUTE_GENERATED_VIRTUAL; } + | /*EMPTY*/ { $$ =3D ATTRIBUTE_GENERATED_VIRTUAL; } + ; + /* * ConstraintAttr represents constraint attributes, which we parse as if= * they were independent constraint clauses, in order to avoid shift/red= uce @@ -3643,6 +3660,7 @@ TableLikeOption: | CONSTRAINTS { $$ =3D CREATE_TABLE_LIKE_CONSTRAINTS; } | DEFAULTS { $$ =3D CREATE_TABLE_LIKE_DEFAULTS; } | IDENTITY_P { $$ =3D CREATE_TABLE_LIKE_IDENTITY; } + | GENERATED { $$ =3D CREATE_TABLE_LIKE_GENERATED; } | INDEXES { $$ =3D CREATE_TABLE_LIKE_INDEXES; } | STATISTICS { $$ =3D CREATE_TABLE_LIKE_STATISTICS; } | STORAGE { $$ =3D CREATE_TABLE_LIKE_STORAGE; } @@ -15235,6 +15253,7 @@ unreserved_keyword: | STDIN | STDOUT | STORAGE + | STORED | STRICT_P | STRIP_P | SUBSCRIPTION @@ -15271,6 +15290,7 @@ unreserved_keyword: | VERSION_P | VIEW | VIEWS + | VIRTUAL | VOLATILE | WHITESPACE_P | WITHIN diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_ag= g.c index 61727e1d71..fb3387b683 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -514,6 +514,14 @@ check_agglevels_and_constraints(ParseState *pstate, = Node *expr) err =3D _("grouping operations are not allowed in partition key expr= essions"); =20 break; + case EXPR_KIND_GENERATED_COLUMN: + + if (isAgg) + err =3D _("aggregate functions are not allowed in column generation = expressions"); + else + err =3D _("grouping operations are not allowed in column generation = expressions"); + + break; =20 case EXPR_KIND_CALL_ARGUMENT: if (isAgg) @@ -902,6 +910,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFun= c *wfunc, case EXPR_KIND_CALL_ARGUMENT: err =3D _("window functions are not allowed in CALL arguments"); break; + case EXPR_KIND_GENERATED_COLUMN: + err =3D _("window functions are not allowed in column generation expr= essions"); + break; =20 /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_e= xpr.c index 385e54a9b6..853deaa8d7 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -1849,6 +1849,9 @@ transformSubLink(ParseState *pstate, SubLink *subli= nk) case EXPR_KIND_CALL_ARGUMENT: err =3D _("cannot use subquery in CALL argument"); break; + case EXPR_KIND_GENERATED_COLUMN: + err =3D _("cannot use subquery in column generation expression"); + break; =20 /* * There is intentionally no default: case here, so that the @@ -3475,6 +3478,8 @@ ParseExprKindName(ParseExprKind exprKind) return "PARTITION BY"; case EXPR_KIND_CALL_ARGUMENT: return "CALL"; + case EXPR_KIND_GENERATED_COLUMN: + return "GENERATED AS"; =20 /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_f= unc.c index 44257154b8..8aeed63a69 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -621,6 +621,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname= , List *fargs, parser_errposition(pstate, location))); } =20 + if (pstate->p_expr_kind =3D=3D EXPR_KIND_GENERATED_COLUMN && + func_volatile(funcid) !=3D PROVOLATILE_IMMUTABLE) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("cannot use function %s in column generation expression", + func_signature_string(funcname, nargs, argnames, actual_arg_types)= ), + errdetail("Functions used in a column generation expression must be= immutable."), + parser_errposition(pstate, location))); + /* * If there are default arguments, we have to include their types in * actual_arg_types for the purpose of checking generic type consistenc= y. @@ -2370,6 +2379,9 @@ check_srf_call_placement(ParseState *pstate, Node *= last_srf, int location) case EXPR_KIND_CALL_ARGUMENT: err =3D _("set-returning functions are not allowed in CALL arguments"= ); break; + case EXPR_KIND_GENERATED_COLUMN: + err =3D _("set-returning functions are not allowed in column generati= on expressions"); + break; =20 /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/pars= e_utilcmd.c index a6a2de94ea..b6f0e7d82c 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -526,6 +526,7 @@ transformColumnDefinition(CreateStmtContext *cxt, Col= umnDef *column) bool saw_nullable; bool saw_default; bool saw_identity; + bool saw_generated; ListCell *clist; =20 cxt->columns =3D lappend(cxt->columns, column); @@ -633,6 +634,7 @@ transformColumnDefinition(CreateStmtContext *cxt, Col= umnDef *column) saw_nullable =3D false; saw_default =3D false; saw_identity =3D false; + saw_generated =3D false; =20 foreach(clist, column->constraints) { @@ -713,6 +715,50 @@ transformColumnDefinition(CreateStmtContext *cxt, Co= lumnDef *column) break; } =20 + case CONSTR_GENERATED: + if (cxt->ofType) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("generated colums are not supported on typed tables"))); + if (cxt->partbound) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("generated columns are not supported on partitions"))); + + if (saw_generated) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("multiple generation clauses specified for column \"%s\" = of table \"%s\"", + column->colname, cxt->relation->relname), + parser_errposition(cxt->pstate, + constraint->location))); + column->generated =3D constraint->generated_kind; + column->raw_default =3D constraint->raw_expr; + Assert(constraint->cooked_expr =3D=3D NULL); + saw_generated =3D true; + + /* + * Prevent virtual generated columns from having a domain + * type. We would have to enforce domain constraints when + * columns underlying the generated column change. This could + * possibly be implemented, but it's not. + */ + if (column->generated =3D=3D ATTRIBUTE_GENERATED_VIRTUAL) + { + Type ctype; + + ctype =3D typenameType(cxt->pstate, column->typeName, NULL); + if (((Form_pg_type) GETSTRUCT(ctype))->typtype =3D=3D TYPTYPE_DOMAI= N) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("virtual generated column \"%s\" cannot have a domain ty= pe", + column->colname), + parser_errposition(cxt->pstate, + column->location))); + ReleaseSysCache(ctype); + } + break; + case CONSTR_CHECK: cxt->ckconstraints =3D lappend(cxt->ckconstraints, constraint); break; @@ -779,6 +825,50 @@ transformColumnDefinition(CreateStmtContext *cxt, Co= lumnDef *column) column->colname, cxt->relation->relname), parser_errposition(cxt->pstate, constraint->location))); + + if (saw_default && saw_generated) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("both default and generation expression specified for colum= n \"%s\" of table \"%s\"", + column->colname, cxt->relation->relname), + parser_errposition(cxt->pstate, + constraint->location))); + + if (saw_identity && saw_generated) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("both identity and generation expression specified for colu= mn \"%s\" of table \"%s\"", + column->colname, cxt->relation->relname), + parser_errposition(cxt->pstate, + constraint->location))); + + /* + * For a virtual generated column, convert the not-null constraint + * into a full check constraint, so that the generation expression can= + * be expanded at check time. + */ + if (column->is_not_null && column->generated =3D=3D ATTRIBUTE_GENERATE= D_VIRTUAL) + { + Constraint *chk =3D makeNode(Constraint); + NullTest *nt =3D makeNode(NullTest); + ColumnRef *cr =3D makeNode(ColumnRef); + + cr->location =3D -1; + cr->fields =3D list_make1(makeString(column->colname)); + + nt->arg =3D (Expr *) cr; + nt->nulltesttype =3D IS_NOT_NULL; + nt->location =3D -1; + + chk->contype =3D CONSTR_CHECK; + chk->location =3D -1; + chk->initially_valid =3D true; + chk->raw_expr =3D (Node *) nt; + + cxt->ckconstraints =3D lappend(cxt->ckconstraints, chk); + + column->is_not_null =3D false; + } } =20 /* @@ -1008,11 +1098,13 @@ transformTableLikeClause(CreateStmtContext *cxt, = TableLikeClause *table_like_cla * Copy default, if present and the default has been requested */ if (attribute->atthasdef && - (table_like_clause->options & CREATE_TABLE_LIKE_DEFAULTS)) + (table_like_clause->options & CREATE_TABLE_LIKE_DEFAULTS || + table_like_clause->options & CREATE_TABLE_LIKE_GENERATED)) { Node *this_default =3D NULL; AttrDefault *attrdef; int i; + bool found_whole_row; =20 /* Find default in constraint structure */ Assert(constr !=3D NULL); @@ -1027,12 +1119,27 @@ transformTableLikeClause(CreateStmtContext *cxt, = TableLikeClause *table_like_cla } Assert(this_default !=3D NULL); =20 + def->cooked_default =3D map_variable_attnos(this_default, + 1, 0, + attmap, tupleDesc->natts, + InvalidOid, &found_whole_row); + /* - * If default expr could contain any vars, we'd need to fix 'em, - * but it can't; so default is ready to apply to child. + * Prevent this for the same reason as for constraints below. + * Note that defaults cannot contain any vars, so it's OK that the + * error message refers to generated columns. */ + if (found_whole_row) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot convert whole-row table reference"), + errdetail("Generation expression for column \"%s\" contains a who= le-row reference to table \"%s\".", + attributeName, + RelationGetRelationName(relation)))); =20 - def->cooked_default =3D this_default; + if (attribute->attgenerated && + (table_like_clause->options & CREATE_TABLE_LIKE_GENERATED)) + def->generated =3D attribute->attgenerated; } =20 /* diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/r= ewriteHandler.c index 43815d26ff..467586d293 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -20,6 +20,7 @@ */ #include "postgres.h" =20 +#include "access/htup_details.h" #include "access/sysattr.h" #include "catalog/dependency.h" #include "catalog/pg_type.h" @@ -38,6 +39,7 @@ #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/rel.h" +#include "utils/syscache.h" =20 =20 /* We use a list of these to detect recursion in RewriteQuery */ @@ -830,6 +832,13 @@ rewriteTargetListIU(List *targetList, =20 if (att_tup->attidentity =3D=3D ATTRIBUTE_IDENTITY_BY_DEFAULT && over= ride =3D=3D OVERRIDING_USER_VALUE) apply_default =3D true; + + if (att_tup->attgenerated && !apply_default) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attna= me)), + errdetail("Column \"%s\" is a generated column.", + NameStr(att_tup->attname)))); } =20 if (commandType =3D=3D CMD_UPDATE) @@ -840,9 +849,24 @@ rewriteTargetListIU(List *targetList, errmsg("column \"%s\" can only be updated to DEFAULT", NameStr(at= t_tup->attname)), errdetail("Column \"%s\" is an identity column defined as GENERAT= ED ALWAYS.", NameStr(att_tup->attname)))); + + if (att_tup->attgenerated && new_tle && !apply_default) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("column \"%s\" can only be updated to DEFAULT", NameStr(at= t_tup->attname)), + errdetail("Column \"%s\" is a generated column.", + NameStr(att_tup->attname)))); } =20 - if (apply_default) + if (att_tup->attgenerated) + { + /* + * virtual generated column stores a null value; stored generated + * column will be fixed in executor + */ + new_tle =3D NULL; + } + else if (apply_default) { Node *new_expr; =20 @@ -1147,13 +1171,12 @@ build_column_default(Relation rel, int attrno) } } =20 - if (expr =3D=3D NULL) - { - /* - * No per-column default, so look for a default for the type itself. - */ + /* + * No per-column default, so look for a default for the type itself. B= ut + * not for generated columns. + */ + if (expr =3D=3D NULL && !att_tup->attgenerated) expr =3D get_typdefault(atttype); - } =20 if (expr =3D=3D NULL) return NULL; /* No default anywhere */ @@ -3676,6 +3699,103 @@ RewriteQuery(Query *parsetree, List *rewrite_even= ts) } =20 =20 +static Node * +expand_generated_columns_in_expr_mutator(Node *node, Relation rel) +{ + if (node =3D=3D NULL) + return NULL; + + if (IsA(node, Var)) + { + Var *v =3D (Var *) node; + AttrNumber attnum =3D v->varattno; + + if (attnum > 0 && TupleDescAttr(RelationGetDescr(rel), attnum - 1)->at= tgenerated =3D=3D ATTRIBUTE_GENERATED_VIRTUAL) + { + node =3D build_column_default(rel, attnum); + if (node =3D=3D NULL) + elog(ERROR, "no generation expression found for column number %d of = table \"%s\"", + attnum, RelationGetRelationName(rel)); + ChangeVarNodes(node, 1, v->varno, 0); + } + + return node; + } + else + return expression_tree_mutator(node, expand_generated_columns_in_expr_= mutator, rel); +} + + +Node * +expand_generated_columns_in_expr(Node *node, Relation rel) +{ + TupleDesc tupdesc =3D RelationGetDescr(rel); + + if (tupdesc->constr && tupdesc->constr->has_generated) + return expression_tree_mutator(node, + expand_generated_columns_in_expr_mutator, + rel); + else + return node; +} + +typedef struct +{ + /* list of range tables, innermost last */ + List *rtables; +} expand_generated_context; + +static Node * +expand_generated_columns_in_query_mutator(Node *node, expand_generated_c= ontext *context) +{ + if (node =3D=3D NULL) + return NULL; + + if (IsA(node, Var)) + { + Var *v =3D (Var *) node; + Oid relid; + AttrNumber attnum; + List *rtable =3D list_nth_node(List, + context->rtables, + list_length(context->rtables) - v->varlevelsup - 1); + + relid =3D rt_fetch(v->varno, rtable)->relid; + attnum =3D v->varattno; + + if (!relid || !attnum) + return node; + + if (get_attgenerated(relid, attnum) =3D=3D ATTRIBUTE_GENERATED_VIRTUAL= ) + { + Relation rt_entry_relation =3D heap_open(relid, NoLock); + + node =3D build_column_default(rt_entry_relation, attnum); + ChangeVarNodes(node, 1, v->varno, v->varlevelsup); + + heap_close(rt_entry_relation, NoLock); + } + + return node; + } + else if (IsA(node, Query)) + { + Query *query =3D (Query *) node; + Node *result; + + context->rtables =3D lappend(context->rtables, query->rtable); + result =3D (Node *) query_tree_mutator(query, + expand_generated_columns_in_query_mutator, + context, + QTW_DONT_COPY_QUERY); + context->rtables =3D list_truncate(context->rtables, list_length(conte= xt->rtables) - 1); + return result; + } + else + return expression_tree_mutator(node, expand_generated_columns_in_query= _mutator, context); +} + + /* * QueryRewrite - * Primary entry point to the query rewriter. @@ -3731,6 +3851,24 @@ QueryRewrite(Query *parsetree) /* * Step 3 * + * Expand generated columns. + */ + foreach(l, querylist) + { + Query *query =3D (Query *) lfirst(l); + expand_generated_context context; + + context.rtables =3D list_make1(query->rtable); + + query =3D query_tree_mutator(query, + expand_generated_columns_in_query_mutator, + &context, + QTW_DONT_COPY_QUERY); + } + + /* + * Step 4 + * * Determine which, if any, of the resulting queries is supposed to set= * the command-result tag; and update the canSetTag fields accordingly.= * diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cach= e/lsyscache.c index 892ddc0d48..d7db8aa5a3 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -821,6 +821,39 @@ get_attnum(Oid relid, const char *attname) return InvalidAttrNumber; } =20 +/* + * get_attgenerated + * + * Given the relation id and the attribute name, + * return the "attgenerated" field from the attribute relation. + * + * Errors if not found. + * + * Since not generated is represented by '\0', this can also be used as= a + * Boolean test. + */ +char +get_attgenerated(Oid relid, AttrNumber attnum) +{ + HeapTuple tp; + + tp =3D SearchSysCache2(ATTNUM, + ObjectIdGetDatum(relid), + Int16GetDatum(attnum)); + if (HeapTupleIsValid(tp)) + { + Form_pg_attribute att_tup =3D (Form_pg_attribute) GETSTRUCT(tp); + char result; + + result =3D att_tup->attgenerated; + ReleaseSysCache(tp); + return result; + } + else + elog(ERROR, "cache lookup failed for attribute %d of relation %u", + attnum, relid); +} + /* * get_atttype * diff --git a/src/backend/utils/cache/partcache.c b/src/backend/utils/cach= e/partcache.c index 5757301d05..31c7c27643 100644 --- a/src/backend/utils/cache/partcache.c +++ b/src/backend/utils/cache/partcache.c @@ -28,6 +28,7 @@ #include "optimizer/clauses.h" #include "optimizer/planner.h" #include "partitioning/partbounds.h" +#include "rewrite/rewriteHandler.h" #include "utils/builtins.h" #include "utils/datum.h" #include "utils/lsyscache.h" @@ -133,6 +134,8 @@ RelationBuildPartitionKey(Relation relation) expr =3D stringToNode(exprString); pfree(exprString); =20 + expr =3D expand_generated_columns_in_expr(expr, relation); + /* * Run the expressions through const-simplification since the planner * will be comparing them to similarly-processed qual clause operands,= diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache= /relcache.c index fd3d010b77..54b959cded 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -510,6 +510,7 @@ RelationBuildTupleDesc(Relation relation) constr =3D (TupleConstr *) MemoryContextAlloc(CacheMemoryContext, sizeof(TupleConstr)); constr->has_not_null =3D false; + constr->has_generated =3D false; =20 /* * Form a scan key that selects only user attributes (attnum > 0). @@ -562,6 +563,8 @@ RelationBuildTupleDesc(Relation relation) /* Update constraint/default info */ if (attp->attnotnull) constr->has_not_null =3D true; + if (attp->attgenerated) + constr->has_generated =3D true; =20 /* If the column has a default, fill it into the attrdef array */ if (attp->atthasdef) @@ -3187,6 +3190,7 @@ RelationBuildLocalRelation(const char *relname, Form_pg_attribute datt =3D TupleDescAttr(rel->rd_att, i); =20 datt->attidentity =3D satt->attidentity; + datt->attgenerated =3D satt->attgenerated; datt->attnotnull =3D satt->attnotnull; has_not_null |=3D satt->attnotnull; } diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index c8d01ed4a4..7d6c7d3b20 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -1987,6 +1987,11 @@ dumpTableData_insert(Archive *fout, void *dcontext= ) { if (field > 0) archputs(", ", fout); + if (tbinfo->attgenerated[field]) + { + archputs("DEFAULT", fout); + continue; + } if (PQgetisnull(res, tuple, field)) { archputs("NULL", fout); @@ -8135,6 +8140,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, in= t numTables) int i_attnotnull; int i_atthasdef; int i_attidentity; + int i_attgenerated; int i_attisdropped; int i_attlen; int i_attalign; @@ -8188,6 +8194,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, i= nt numTables) "a.attislocal,\n" "pg_catalog.format_type(t.oid, a.atttypmod) AS atttypname,\n"); =20 + if (fout->remoteVersion >=3D 120000) + appendPQExpBuffer(q, + "a.attgenerated,\n"); + else + appendPQExpBuffer(q, + "'' AS attgenerated,\n"); + if (fout->remoteVersion >=3D 110000) appendPQExpBuffer(q, "CASE WHEN a.atthasmissing AND NOT a.attisdropped " @@ -8260,6 +8273,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, in= t numTables) i_attnotnull =3D PQfnumber(res, "attnotnull"); i_atthasdef =3D PQfnumber(res, "atthasdef"); i_attidentity =3D PQfnumber(res, "attidentity"); + i_attgenerated =3D PQfnumber(res, "attgenerated"); i_attisdropped =3D PQfnumber(res, "attisdropped"); i_attlen =3D PQfnumber(res, "attlen"); i_attalign =3D PQfnumber(res, "attalign"); @@ -8277,6 +8291,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, in= t numTables) tbinfo->attstorage =3D (char *) pg_malloc(ntups * sizeof(char)); tbinfo->typstorage =3D (char *) pg_malloc(ntups * sizeof(char)); tbinfo->attidentity =3D (char *) pg_malloc(ntups * sizeof(char)); + tbinfo->attgenerated =3D (char *) pg_malloc(ntups * sizeof(char)); tbinfo->attisdropped =3D (bool *) pg_malloc(ntups * sizeof(bool)); tbinfo->attlen =3D (int *) pg_malloc(ntups * sizeof(int)); tbinfo->attalign =3D (char *) pg_malloc(ntups * sizeof(char)); @@ -8303,6 +8318,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, in= t numTables) tbinfo->attstorage[j] =3D *(PQgetvalue(res, j, i_attstorage)); tbinfo->typstorage[j] =3D *(PQgetvalue(res, j, i_typstorage)); tbinfo->attidentity[j] =3D *(PQgetvalue(res, j, i_attidentity)); + tbinfo->attgenerated[j] =3D *(PQgetvalue(res, j, i_attgenerated)); tbinfo->needs_override =3D tbinfo->needs_override || (tbinfo->attiden= tity[j] =3D=3D ATTRIBUTE_IDENTITY_ALWAYS); tbinfo->attisdropped[j] =3D (PQgetvalue(res, j, i_attisdropped)[0] =3D= =3D 't'); tbinfo->attlen[j] =3D atoi(PQgetvalue(res, j, i_attlen)); @@ -15578,6 +15594,23 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo= ) tbinfo->atttypnames[j]); } =20 + if (has_default) + { + if (tbinfo->attgenerated[j] =3D=3D ATTRIBUTE_GENERATED_STORED) + appendPQExpBuffer(q, " GENERATED ALWAYS AS (%s) STORED", + tbinfo->attrdefs[j]->adef_expr); + else if (tbinfo->attgenerated[j] =3D=3D ATTRIBUTE_GENERATED_VIRTUA= L) + appendPQExpBuffer(q, " GENERATED ALWAYS AS (%s)", + tbinfo->attrdefs[j]->adef_expr); + else + appendPQExpBuffer(q, " DEFAULT %s", + tbinfo->attrdefs[j]->adef_expr); + } + + + if (has_notnull) + appendPQExpBufferStr(q, " NOT NULL"); + /* Add collation if not default for the type */ if (OidIsValid(tbinfo->attcollation[j])) { @@ -15588,13 +15621,6 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo= ) appendPQExpBuffer(q, " COLLATE %s", fmtQualifiedDumpable(coll)); } - - if (has_default) - appendPQExpBuffer(q, " DEFAULT %s", - tbinfo->attrdefs[j]->adef_expr); - - if (has_notnull) - appendPQExpBufferStr(q, " NOT NULL"); } } =20 @@ -18141,6 +18167,7 @@ fmtCopyColumnList(const TableInfo *ti, PQExpBuffe= r buffer) int numatts =3D ti->numatts; char **attnames =3D ti->attnames; bool *attisdropped =3D ti->attisdropped; + char *attgenerated =3D ti->attgenerated; bool needComma; int i; =20 @@ -18150,6 +18177,8 @@ fmtCopyColumnList(const TableInfo *ti, PQExpBuffe= r buffer) { if (attisdropped[i]) continue; + if (attgenerated[i]) + continue; if (needComma) appendPQExpBufferStr(buffer, ", "); appendPQExpBufferStr(buffer, fmtId(attnames[i])); diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 685ad78669..d8c1c9927e 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -310,6 +310,7 @@ typedef struct _tableInfo char *typstorage; /* type storage scheme */ bool *attisdropped; /* true if attr is dropped; don't dump it */ char *attidentity; + char *attgenerated; int *attlen; /* attribute length, used by binary_upgrade */ char *attalign; /* attribute align, used by binary_upgrade */ bool *attislocal; /* true if attr has local definition */ diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sor= t.c index a1d3ced318..b6f2a92396 100644 --- a/src/bin/pg_dump/pg_dump_sort.c +++ b/src/bin/pg_dump/pg_dump_sort.c @@ -1102,6 +1102,16 @@ repairDependencyLoop(DumpableObject **loop, } } =20 + /* Loop of table with itself, happens with generated columns */ + if (nLoop =3D=3D 1) + { + if (loop[0]->objType =3D=3D DO_TABLE) + { + removeObjectDependency(loop[0], loop[0]->dumpId); + return; + } + } + /* * If all the objects are TABLE_DATA items, what we must have is a * circular set of foreign key constraints (or a single self-referentia= l diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_= dump.pl index ec751a7c23..1dd859f1c5 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -2420,6 +2420,23 @@ unlike =3D> { exclude_dump_test_schema =3D> 1, }, }, =20 + 'CREATE TABLE test_table_generated' =3D> { + create_order =3D> 3, + create_sql =3D> 'CREATE TABLE dump_test.test_table_generated ( + col1 int primary key, + col2 int generated always as (col1 * 2) + );', + regexp =3D> qr/^ + \QCREATE TABLE dump_test.test_table_generated (\E\n + \s+\Qcol1 integer NOT NULL,\E\n + \s+\Qcol2 integer GENERATED ALWAYS AS ((col1 * 2))\E\n + \); + /xms, + like =3D> + { %full_runs, %dump_test_schema_runs, section_pre_data =3D> 1, }, + unlike =3D> { exclude_dump_test_schema =3D> 1, }, + }, + 'CREATE STATISTICS extended_stats_no_options' =3D> { create_order =3D> 97, create_sql =3D> 'CREATE STATISTICS dump_test.test_ext_stats_no_optio= ns diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 4ca0db1d0c..bbdc4b9fa2 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1462,6 +1462,7 @@ describeOneTableDetails(const char *schemaname, attnotnull_col =3D -1, attcoll_col =3D -1, attidentity_col =3D -1, + attgenerated_col =3D -1, isindexkey_col =3D -1, indexdef_col =3D -1, fdwopts_col =3D -1, @@ -1789,8 +1790,9 @@ describeOneTableDetails(const char *schemaname, =20 if (show_column_details) { + /* use "pretty" mode for expression to avoid excessive parentheses */ appendPQExpBufferStr(&buf, - ",\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adreli= d) for 128)" + ",\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adreli= d, true) for 128)" "\n FROM pg_catalog.pg_attrdef d" "\n WHERE d.adrelid =3D a.attrelid AND d.adnum =3D a.attnum AN= D a.atthasdef)" ",\n a.attnotnull"); @@ -1807,6 +1809,11 @@ describeOneTableDetails(const char *schemaname, else appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attidentity")= ; attidentity_col =3D cols++; + if (pset.sversion >=3D 120000) + appendPQExpBufferStr(&buf, ",\n a.attgenerated"); + else + appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attgenerated"= ); + attgenerated_col =3D cols++; } if (tableinfo.relkind =3D=3D RELKIND_INDEX || tableinfo.relkind =3D=3D RELKIND_PARTITIONED_INDEX) @@ -1980,6 +1987,7 @@ describeOneTableDetails(const char *schemaname, if (show_column_details) { char *identity; + char *generated; char *default_str =3D ""; =20 printTableAddCell(&cont, PQgetvalue(res, i, attcoll_col), false, fals= e); @@ -1989,16 +1997,21 @@ describeOneTableDetails(const char *schemaname, false, false); =20 identity =3D PQgetvalue(res, i, attidentity_col); + generated =3D PQgetvalue(res, i, attgenerated_col); =20 - if (!identity[0]) - /* (note: above we cut off the 'default' string at 128) */ - default_str =3D PQgetvalue(res, i, attrdef_col); - else if (identity[0] =3D=3D ATTRIBUTE_IDENTITY_ALWAYS) + if (identity[0] =3D=3D ATTRIBUTE_IDENTITY_ALWAYS) default_str =3D "generated always as identity"; else if (identity[0] =3D=3D ATTRIBUTE_IDENTITY_BY_DEFAULT) default_str =3D "generated by default as identity"; + else if (generated[0] =3D=3D ATTRIBUTE_GENERATED_STORED) + default_str =3D psprintf("generated always as (%s) stored", PQgetval= ue(res, i, attrdef_col)); + else if (generated[0] =3D=3D ATTRIBUTE_GENERATED_VIRTUAL) + default_str =3D psprintf("generated always as (%s)", PQgetvalue(res,= i, attrdef_col)); + else + /* (note: above we cut off the 'default' string at 128) */ + default_str =3D PQgetvalue(res, i, attrdef_col); =20 - printTableAddCell(&cont, default_str, false, false); + printTableAddCell(&cont, default_str, false, generated[0] ? true : fa= lse); } =20 /* Info for index columns */ diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h index 708160f645..7a01d4ea50 100644 --- a/src/include/access/tupdesc.h +++ b/src/include/access/tupdesc.h @@ -44,6 +44,7 @@ typedef struct tupleConstr uint16 num_defval; uint16 num_check; bool has_not_null; + bool has_generated; } TupleConstr; =20 /* diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index 39f04b06ee..8be9423804 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -116,7 +116,8 @@ extern Node *cookDefault(ParseState *pstate, Node *raw_default, Oid atttypid, int32 atttypmod, - const char *attname); + const char *attname, + char attgenerated); =20 extern void DeleteRelationTuple(Oid relid); extern void DeleteAttributeTuples(Oid relid); diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_= attribute.h index dc36753ede..cd42972c47 100644 --- a/src/include/catalog/pg_attribute.h +++ b/src/include/catalog/pg_attribute.h @@ -140,6 +140,9 @@ CATALOG(pg_attribute,1249,AttributeRelationId) BKI_BO= OTSTRAP BKI_WITHOUT_OIDS BK /* One of the ATTRIBUTE_IDENTITY_* constants below, or '\0' */ char attidentity BKI_DEFAULT('\0'); =20 + /* One of the ATTRIBUTE_GENERATED_* constants below, or '\0' */ + char attgenerated BKI_DEFAULT('\0'); + /* Is dropped (ie, logically invisible) or not */ bool attisdropped BKI_DEFAULT(f); =20 @@ -201,6 +204,9 @@ typedef FormData_pg_attribute *Form_pg_attribute; #define ATTRIBUTE_IDENTITY_ALWAYS 'a' #define ATTRIBUTE_IDENTITY_BY_DEFAULT 'd' =20 +#define ATTRIBUTE_GENERATED_STORED 's' +#define ATTRIBUTE_GENERATED_VIRTUAL 'v' + #endif /* EXPOSE_TO_CLIENT_CODE */ =20 #endif /* PG_ATTRIBUTE_H */ diff --git a/src/include/catalog/pg_class.dat b/src/include/catalog/pg_cl= ass.dat index 9fffdef379..49643b934e 100644 --- a/src/include/catalog/pg_class.dat +++ b/src/include/catalog/pg_class.dat @@ -36,7 +36,7 @@ reloftype =3D> '0', relowner =3D> 'PGUID', relam =3D> '0', relfilenode= =3D> '0', reltablespace =3D> '0', relpages =3D> '0', reltuples =3D> '0', relallv= isible =3D> '0', reltoastrelid =3D> '0', relhasindex =3D> 'f', relisshared =3D> 'f', - relpersistence =3D> 'p', relkind =3D> 'r', relnatts =3D> '24', relchec= ks =3D> '0', + relpersistence =3D> 'p', relkind =3D> 'r', relnatts =3D> '25', relchec= ks =3D> '0', relhasoids =3D> 'f', relhasrules =3D> 'f', relhastriggers =3D> 'f', relhassubclass =3D> 'f', relrowsecurity =3D> 'f', relforcerowsecurity = =3D> 'f', relispopulated =3D> 't', relreplident =3D> 'n', relispartition =3D> 'f= ', diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executo= r/nodeModifyTable.h index 0d7e579e1c..64ce9ecaf0 100644 --- a/src/include/executor/nodeModifyTable.h +++ b/src/include/executor/nodeModifyTable.h @@ -15,6 +15,8 @@ =20 #include "nodes/execnodes.h" =20 +extern HeapTuple ExecComputeStoredGenerated(EState *estate, TupleTableSl= ot *slot); + extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState *= estate, int eflags); extern void ExecEndModifyTable(ModifyTableState *node); extern void ExecReScanModifyTable(ModifyTableState *node); diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.= h index 880a03e4e4..ce4eb04c74 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -445,6 +445,9 @@ typedef struct ResultRelInfo /* array of constraint-checking expr states */ ExprState **ri_ConstraintExprs; =20 + /* array of stored generated columns expr states */ + ExprState **ri_GeneratedExprs; + /* for removing junk attributes from tuples */ JunkFilter *ri_junkFilter; =20 diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenode= s.h index aa4a0dba2a..a56291b961 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -656,6 +656,7 @@ typedef struct ColumnDef char identity; /* attidentity setting */ RangeVar *identitySequence; /* to store identity sequence name for * ALTER TABLE ... ADD COLUMN */ + char generated; /* attgenerated setting */ CollateClause *collClause; /* untransformed COLLATE spec, if any */ Oid collOid; /* collation OID (InvalidOid if not set) */ List *constraints; /* other constraints on column */ @@ -678,10 +679,11 @@ typedef enum TableLikeOption CREATE_TABLE_LIKE_COMMENTS =3D 1 << 0, CREATE_TABLE_LIKE_CONSTRAINTS =3D 1 << 1, CREATE_TABLE_LIKE_DEFAULTS =3D 1 << 2, - CREATE_TABLE_LIKE_IDENTITY =3D 1 << 3, - CREATE_TABLE_LIKE_INDEXES =3D 1 << 4, - CREATE_TABLE_LIKE_STATISTICS =3D 1 << 5, - CREATE_TABLE_LIKE_STORAGE =3D 1 << 6, + CREATE_TABLE_LIKE_GENERATED =3D 1 << 3, + CREATE_TABLE_LIKE_IDENTITY =3D 1 << 4, + CREATE_TABLE_LIKE_INDEXES =3D 1 << 5, + CREATE_TABLE_LIKE_STATISTICS =3D 1 << 6, + CREATE_TABLE_LIKE_STORAGE =3D 1 << 7, CREATE_TABLE_LIKE_ALL =3D PG_INT32_MAX } TableLikeOption; =20 @@ -2076,6 +2078,7 @@ typedef enum ConstrType /* types of constraints *= / CONSTR_NOTNULL, CONSTR_DEFAULT, CONSTR_IDENTITY, + CONSTR_GENERATED, CONSTR_CHECK, CONSTR_PRIMARY, CONSTR_UNIQUE, @@ -2114,7 +2117,8 @@ typedef struct Constraint bool is_no_inherit; /* is constraint non-inheritable? */ Node *raw_expr; /* expr, as untransformed parse tree */ char *cooked_expr; /* expr, as nodeToString representation */ - char generated_when; + char generated_when; /* ALWAYS or BY DEFAULT */ + char generated_kind; /* STORED or VIRTUAL */ =20 /* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */ List *keys; /* String nodes naming referenced key diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 23db40147b..c6e5a1cbb8 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -384,6 +384,7 @@ PG_KEYWORD("statistics", STATISTICS, UNRESERVED_KEYWO= RD) PG_KEYWORD("stdin", STDIN, UNRESERVED_KEYWORD) PG_KEYWORD("stdout", STDOUT, UNRESERVED_KEYWORD) PG_KEYWORD("storage", STORAGE, UNRESERVED_KEYWORD) +PG_KEYWORD("stored", STORED, UNRESERVED_KEYWORD) PG_KEYWORD("strict", STRICT_P, UNRESERVED_KEYWORD) PG_KEYWORD("strip", STRIP_P, UNRESERVED_KEYWORD) PG_KEYWORD("subscription", SUBSCRIPTION, UNRESERVED_KEYWORD) @@ -440,6 +441,7 @@ PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD= ) PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD) PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD) PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD) +PG_KEYWORD("virtual", VIRTUAL, UNRESERVED_KEYWORD) PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD) PG_KEYWORD("when", WHEN, RESERVED_KEYWORD) PG_KEYWORD("where", WHERE, RESERVED_KEYWORD) diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_n= ode.h index 0230543810..3248d93297 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -69,7 +69,8 @@ typedef enum ParseExprKind EXPR_KIND_TRIGGER_WHEN, /* WHEN condition in CREATE TRIGGER */ EXPR_KIND_POLICY, /* USING or WITH CHECK expr in policy */ EXPR_KIND_PARTITION_EXPRESSION, /* PARTITION BY expression */ - EXPR_KIND_CALL_ARGUMENT /* procedure argument in CALL */ + EXPR_KIND_CALL_ARGUMENT, /* procedure argument in CALL */ + EXPR_KIND_GENERATED_COLUMN, /* generation expression for a column */ } ParseExprKind; =20 =20 diff --git a/src/include/rewrite/rewriteHandler.h b/src/include/rewrite/r= ewriteHandler.h index 8128199fc3..f8017e423a 100644 --- a/src/include/rewrite/rewriteHandler.h +++ b/src/include/rewrite/rewriteHandler.h @@ -32,5 +32,6 @@ extern const char *view_query_is_auto_updatable(Query *= viewquery, extern int relation_is_updatable(Oid reloid, bool include_triggers, Bitmapset *include_cols); +extern Node *expand_generated_columns_in_expr(Node *node, Relation rel);= =20 #endif /* REWRITEHANDLER_H */ diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.= h index ff1705ad2b..cc96a61158 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -85,6 +85,7 @@ extern Oid get_opfamily_proc(Oid opfamily, Oid lefttype= , Oid righttype, int16 procnum); extern char *get_attname(Oid relid, AttrNumber attnum, bool missing_ok);= extern AttrNumber get_attnum(Oid relid, const char *attname); +extern char get_attgenerated(Oid relid, AttrNumber attnum); extern Oid get_atttype(Oid relid, AttrNumber attnum); extern void get_atttypetypmodcoll(Oid relid, AttrNumber attnum, Oid *typid, int32 *typmod, Oid *collid); diff --git a/src/pl/plperl/expected/plperl_trigger.out b/src/pl/plperl/ex= pected/plperl_trigger.out index 28011cd9f6..a545506ec6 100644 --- a/src/pl/plperl/expected/plperl_trigger.out +++ b/src/pl/plperl/expected/plperl_trigger.out @@ -6,6 +6,8 @@ CREATE TABLE trigger_test ( v varchar, foo rowcompnest ); +CREATE TABLE trigger_test_generated + (i int, j int GENERATED ALWAYS AS (i * 2)); CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plper= l AS $$ =20 # make sure keys are sorted for consistent results - perl no longer @@ -98,6 +100,79 @@ NOTICE: $_TD->{table_name} =3D 'trigger_test' NOTICE: $_TD->{table_schema} =3D 'public' NOTICE: $_TD->{when} =3D 'BEFORE' DROP TRIGGER show_trigger_data_trig on trigger_test; +CREATE TRIGGER show_trigger_data_trig_before +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); +CREATE TRIGGER show_trigger_data_trig_after +AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); +insert into trigger_test_generated (i) values (1); +NOTICE: $_TD->{argc} =3D '0' +NOTICE: $_TD->{event} =3D 'INSERT' +NOTICE: $_TD->{level} =3D 'ROW' +NOTICE: $_TD->{name} =3D 'show_trigger_data_trig_before' +NOTICE: $_TD->{new} =3D {'i' =3D> '1'} +NOTICE: $_TD->{relid} =3D 'bogus:12345' +NOTICE: $_TD->{relname} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_name} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_schema} =3D 'public' +NOTICE: $_TD->{when} =3D 'BEFORE' +NOTICE: $_TD->{argc} =3D '0' +NOTICE: $_TD->{event} =3D 'INSERT' +NOTICE: $_TD->{level} =3D 'ROW' +NOTICE: $_TD->{name} =3D 'show_trigger_data_trig_after' +NOTICE: $_TD->{new} =3D {'i' =3D> '1'} +NOTICE: $_TD->{relid} =3D 'bogus:12345' +NOTICE: $_TD->{relname} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_name} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_schema} =3D 'public' +NOTICE: $_TD->{when} =3D 'AFTER' +update trigger_test_generated set i =3D 11 where i =3D 1; +NOTICE: $_TD->{argc} =3D '0' +NOTICE: $_TD->{event} =3D 'UPDATE' +NOTICE: $_TD->{level} =3D 'ROW' +NOTICE: $_TD->{name} =3D 'show_trigger_data_trig_before' +NOTICE: $_TD->{new} =3D {'i' =3D> '11'} +NOTICE: $_TD->{old} =3D {'i' =3D> '1'} +NOTICE: $_TD->{relid} =3D 'bogus:12345' +NOTICE: $_TD->{relname} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_name} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_schema} =3D 'public' +NOTICE: $_TD->{when} =3D 'BEFORE' +NOTICE: $_TD->{argc} =3D '0' +NOTICE: $_TD->{event} =3D 'UPDATE' +NOTICE: $_TD->{level} =3D 'ROW' +NOTICE: $_TD->{name} =3D 'show_trigger_data_trig_after' +NOTICE: $_TD->{new} =3D {'i' =3D> '11'} +NOTICE: $_TD->{old} =3D {'i' =3D> '1'} +NOTICE: $_TD->{relid} =3D 'bogus:12345' +NOTICE: $_TD->{relname} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_name} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_schema} =3D 'public' +NOTICE: $_TD->{when} =3D 'AFTER' +delete from trigger_test_generated; +NOTICE: $_TD->{argc} =3D '0' +NOTICE: $_TD->{event} =3D 'DELETE' +NOTICE: $_TD->{level} =3D 'ROW' +NOTICE: $_TD->{name} =3D 'show_trigger_data_trig_before' +NOTICE: $_TD->{old} =3D {'i' =3D> '11'} +NOTICE: $_TD->{relid} =3D 'bogus:12345' +NOTICE: $_TD->{relname} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_name} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_schema} =3D 'public' +NOTICE: $_TD->{when} =3D 'BEFORE' +NOTICE: $_TD->{argc} =3D '0' +NOTICE: $_TD->{event} =3D 'DELETE' +NOTICE: $_TD->{level} =3D 'ROW' +NOTICE: $_TD->{name} =3D 'show_trigger_data_trig_after' +NOTICE: $_TD->{old} =3D {'i' =3D> '11'} +NOTICE: $_TD->{relid} =3D 'bogus:12345' +NOTICE: $_TD->{relname} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_name} =3D 'trigger_test_generated' +NOTICE: $_TD->{table_schema} =3D 'public' +NOTICE: $_TD->{when} =3D 'AFTER' +DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated; +DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated; insert into trigger_test values(1,'insert', '("(1)")'); CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; CREATE TRIGGER show_trigger_data_trig @@ -295,3 +370,21 @@ NOTICE: perlsnitch: ddl_command_start DROP TABLE NOTICE: perlsnitch: ddl_command_end DROP TABLE=20 drop event trigger perl_a_snitch; drop event trigger perl_b_snitch; +-- dealing with generated columns +CREATE FUNCTION generated_test_func1() RETURNS trigger +LANGUAGE plperl +AS $$ +$_TD->{new}{j} =3D 5; # not allowed +return 'MODIFY'; +$$; +CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_gen= erated +FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); +TRUNCATE trigger_test_generated; +INSERT INTO trigger_test_generated (i) VALUES (1); +ERROR: cannot set generated column "j" +CONTEXT: PL/Perl function "generated_test_func1" +SELECT * FROM trigger_test_generated; + i | j=20 +---+--- +(0 rows) + diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c index 4cfc506253..cb4f96aa2a 100644 --- a/src/pl/plperl/plperl.c +++ b/src/pl/plperl/plperl.c @@ -1791,6 +1791,11 @@ plperl_modify_tuple(HV *hvTD, TriggerData *tdata, = HeapTuple otup) (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot set system attribute \"%s\"", key))); + if (attr->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("cannot set generated column \"%s\"", + key))); =20 modvalues[attn - 1] =3D plperl_sv_to_datum(val, attr->atttypid, @@ -3041,7 +3046,7 @@ plperl_hash_from_tuple(HeapTuple tuple, TupleDesc t= updesc) Oid typoutput; Form_pg_attribute att =3D TupleDescAttr(tupdesc, i); =20 - if (att->attisdropped) + if (att->attisdropped || att->attgenerated =3D=3D ATTRIBUTE_GENERATED_= VIRTUAL) continue; =20 attname =3D NameStr(att->attname); diff --git a/src/pl/plperl/sql/plperl_trigger.sql b/src/pl/plperl/sql/plp= erl_trigger.sql index 624193b9d0..a89545afd8 100644 --- a/src/pl/plperl/sql/plperl_trigger.sql +++ b/src/pl/plperl/sql/plperl_trigger.sql @@ -8,6 +8,9 @@ CREATE TABLE trigger_test ( foo rowcompnest ); =20 +CREATE TABLE trigger_test_generated + (i int, j int GENERATED ALWAYS AS (i * 2)); + CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plper= l AS $$ =20 # make sure keys are sorted for consistent results - perl no longer @@ -70,6 +73,21 @@ CREATE TRIGGER show_trigger_data_trig =20 DROP TRIGGER show_trigger_data_trig on trigger_test; =20 +CREATE TRIGGER show_trigger_data_trig_before +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); + +CREATE TRIGGER show_trigger_data_trig_after +AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); + +insert into trigger_test_generated (i) values (1); +update trigger_test_generated set i =3D 11 where i =3D 1; +delete from trigger_test_generated; + +DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated; +DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated; + insert into trigger_test values(1,'insert', '("(1)")'); CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; =20 @@ -221,3 +239,19 @@ CREATE TRIGGER a_t AFTER UPDATE ON transition_table_= test =20 drop event trigger perl_a_snitch; drop event trigger perl_b_snitch; + +-- dealing with generated columns + +CREATE FUNCTION generated_test_func1() RETURNS trigger +LANGUAGE plperl +AS $$ +$_TD->{new}{j} =3D 5; # not allowed +return 'MODIFY'; +$$; + +CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_gen= erated +FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); + +TRUNCATE trigger_test_generated; +INSERT INTO trigger_test_generated (i) VALUES (1); +SELECT * FROM trigger_test_generated; diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpyt= hon/expected/plpython_trigger.out index d7ab8ac6b8..8f17858e90 100644 --- a/src/pl/plpython/expected/plpython_trigger.out +++ b/src/pl/plpython/expected/plpython_trigger.out @@ -67,6 +67,8 @@ SELECT * FROM users; -- dump trigger data CREATE TABLE trigger_test (i int, v text ); +CREATE TABLE trigger_test_generated + (i int, j int GENERATED ALWAYS AS (i * 2)); CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpythonu AS $$ =20 if 'relid' in TD: @@ -203,6 +205,77 @@ NOTICE: TD[when] =3D> BEFORE DROP TRIGGER show_trigger_data_trig_stmt on trigger_test; DROP TRIGGER show_trigger_data_trig_before on trigger_test; DROP TRIGGER show_trigger_data_trig_after on trigger_test; +CREATE TRIGGER show_trigger_data_trig_before +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); +CREATE TRIGGER show_trigger_data_trig_after +AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); +insert into trigger_test_generated (i) values (1); +NOTICE: TD[args] =3D> None +NOTICE: TD[event] =3D> INSERT +NOTICE: TD[level] =3D> ROW +NOTICE: TD[name] =3D> show_trigger_data_trig_before +NOTICE: TD[new] =3D> {'i': 1} +NOTICE: TD[old] =3D> None +NOTICE: TD[relid] =3D> bogus:12345 +NOTICE: TD[table_name] =3D> trigger_test_generated +NOTICE: TD[table_schema] =3D> public +NOTICE: TD[when] =3D> BEFORE +NOTICE: TD[args] =3D> None +NOTICE: TD[event] =3D> INSERT +NOTICE: TD[level] =3D> ROW +NOTICE: TD[name] =3D> show_trigger_data_trig_after +NOTICE: TD[new] =3D> {'i': 1} +NOTICE: TD[old] =3D> None +NOTICE: TD[relid] =3D> bogus:12345 +NOTICE: TD[table_name] =3D> trigger_test_generated +NOTICE: TD[table_schema] =3D> public +NOTICE: TD[when] =3D> AFTER +update trigger_test_generated set i =3D 11 where i =3D 1; +NOTICE: TD[args] =3D> None +NOTICE: TD[event] =3D> UPDATE +NOTICE: TD[level] =3D> ROW +NOTICE: TD[name] =3D> show_trigger_data_trig_before +NOTICE: TD[new] =3D> {'i': 11} +NOTICE: TD[old] =3D> {'i': 1} +NOTICE: TD[relid] =3D> bogus:12345 +NOTICE: TD[table_name] =3D> trigger_test_generated +NOTICE: TD[table_schema] =3D> public +NOTICE: TD[when] =3D> BEFORE +NOTICE: TD[args] =3D> None +NOTICE: TD[event] =3D> UPDATE +NOTICE: TD[level] =3D> ROW +NOTICE: TD[name] =3D> show_trigger_data_trig_after +NOTICE: TD[new] =3D> {'i': 11} +NOTICE: TD[old] =3D> {'i': 1} +NOTICE: TD[relid] =3D> bogus:12345 +NOTICE: TD[table_name] =3D> trigger_test_generated +NOTICE: TD[table_schema] =3D> public +NOTICE: TD[when] =3D> AFTER +delete from trigger_test_generated; +NOTICE: TD[args] =3D> None +NOTICE: TD[event] =3D> DELETE +NOTICE: TD[level] =3D> ROW +NOTICE: TD[name] =3D> show_trigger_data_trig_before +NOTICE: TD[new] =3D> None +NOTICE: TD[old] =3D> {'i': 11} +NOTICE: TD[relid] =3D> bogus:12345 +NOTICE: TD[table_name] =3D> trigger_test_generated +NOTICE: TD[table_schema] =3D> public +NOTICE: TD[when] =3D> BEFORE +NOTICE: TD[args] =3D> None +NOTICE: TD[event] =3D> DELETE +NOTICE: TD[level] =3D> ROW +NOTICE: TD[name] =3D> show_trigger_data_trig_after +NOTICE: TD[new] =3D> None +NOTICE: TD[old] =3D> {'i': 11} +NOTICE: TD[relid] =3D> bogus:12345 +NOTICE: TD[table_name] =3D> trigger_test_generated +NOTICE: TD[table_schema] =3D> public +NOTICE: TD[when] =3D> AFTER +DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated; +DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated; insert into trigger_test values(1,'insert'); CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; CREATE TRIGGER show_trigger_data_trig @@ -524,3 +597,22 @@ INFO: old: 1 -> a INFO: new: 1 -> b DROP TABLE transition_table_test; DROP FUNCTION transition_table_test_f(); +-- dealing with generated columns +CREATE FUNCTION generated_test_func1() RETURNS trigger +LANGUAGE plpythonu +AS $$ +TD['new']['j'] =3D 5 # not allowed +return 'MODIFY' +$$; +CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_gen= erated +FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); +TRUNCATE trigger_test_generated; +INSERT INTO trigger_test_generated (i) VALUES (1); +ERROR: cannot set generated column "j" +CONTEXT: while modifying trigger row +PL/Python function "generated_test_func1" +SELECT * FROM trigger_test_generated; + i | j=20 +---+--- +(0 rows) + diff --git a/src/pl/plpython/plpy_exec.c b/src/pl/plpython/plpy_exec.c index 47ed95dcc6..d9beb61cf2 100644 --- a/src/pl/plpython/plpy_exec.c +++ b/src/pl/plpython/plpy_exec.c @@ -13,6 +13,7 @@ #include "executor/spi.h" #include "funcapi.h" #include "utils/builtins.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/typcache.h" =20 @@ -952,6 +953,11 @@ PLy_modify_tuple(PLyProcedure *proc, PyObject *pltd,= TriggerData *tdata, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot set system attribute \"%s\"", plattstr))); + if (TupleDescAttr(tupdesc, attn - 1)->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("cannot set generated column \"%s\"", + plattstr))); =20 plval =3D PyDict_GetItem(plntup, platt); if (plval =3D=3D NULL) diff --git a/src/pl/plpython/plpy_typeio.c b/src/pl/plpython/plpy_typeio.= c index d6a6a849c3..afd1a0a748 100644 --- a/src/pl/plpython/plpy_typeio.c +++ b/src/pl/plpython/plpy_typeio.c @@ -839,7 +839,7 @@ PLyDict_FromTuple(PLyDatumToOb *arg, HeapTuple tuple,= TupleDesc desc) bool is_null; PyObject *value; =20 - if (attr->attisdropped) + if (attr->attisdropped || attr->attgenerated =3D=3D ATTRIBUTE_GENERAT= ED_VIRTUAL) continue; =20 key =3D NameStr(attr->attname); diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/s= ql/plpython_trigger.sql index 79c24b714b..745e792bec 100644 --- a/src/pl/plpython/sql/plpython_trigger.sql +++ b/src/pl/plpython/sql/plpython_trigger.sql @@ -67,6 +67,9 @@ CREATE TRIGGER users_delete_trig BEFORE DELETE ON users= FOR EACH ROW CREATE TABLE trigger_test (i int, v text ); =20 +CREATE TABLE trigger_test_generated + (i int, j int GENERATED ALWAYS AS (i * 2)); + CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpythonu AS $$ =20 if 'relid' in TD: @@ -109,6 +112,21 @@ CREATE TRIGGER show_trigger_data_trig_stmt DROP TRIGGER show_trigger_data_trig_before on trigger_test; DROP TRIGGER show_trigger_data_trig_after on trigger_test; =20 +CREATE TRIGGER show_trigger_data_trig_before +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); + +CREATE TRIGGER show_trigger_data_trig_after +AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); + +insert into trigger_test_generated (i) values (1); +update trigger_test_generated set i =3D 11 where i =3D 1; +delete from trigger_test_generated; + +DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated; +DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated; + insert into trigger_test values(1,'insert'); CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; =20 @@ -430,3 +448,20 @@ CREATE TRIGGER a_t AFTER UPDATE ON transition_table_= test =20 DROP TABLE transition_table_test; DROP FUNCTION transition_table_test_f(); + + +-- dealing with generated columns + +CREATE FUNCTION generated_test_func1() RETURNS trigger +LANGUAGE plpythonu +AS $$ +TD['new']['j'] =3D 5 # not allowed +return 'MODIFY' +$$; + +CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_gen= erated +FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); + +TRUNCATE trigger_test_generated; +INSERT INTO trigger_test_generated (i) VALUES (1); +SELECT * FROM trigger_test_generated; diff --git a/src/pl/tcl/expected/pltcl_queries.out b/src/pl/tcl/expected/= pltcl_queries.out index 736671cc1b..1aac933b81 100644 --- a/src/pl/tcl/expected/pltcl_queries.out +++ b/src/pl/tcl/expected/pltcl_queries.out @@ -207,6 +207,75 @@ NOTICE: TG_table_name: trigger_test NOTICE: TG_table_schema: public NOTICE: TG_when: BEFORE NOTICE: args: {23 skidoo} +insert into trigger_test_generated (i) values (1); +NOTICE: NEW: {i: 1} +NOTICE: OLD: {} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_trig_before +NOTICE: TG_op: INSERT +NOTICE: TG_relatts: {{} i j} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_generated +NOTICE: TG_table_schema: public +NOTICE: TG_when: BEFORE +NOTICE: args: {} +NOTICE: NEW: {i: 1} +NOTICE: OLD: {} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_trig_after +NOTICE: TG_op: INSERT +NOTICE: TG_relatts: {{} i j} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_generated +NOTICE: TG_table_schema: public +NOTICE: TG_when: AFTER +NOTICE: args: {} +update trigger_test_generated set i =3D 11 where i =3D 1; +NOTICE: NEW: {i: 11} +NOTICE: OLD: {i: 1} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_trig_before +NOTICE: TG_op: UPDATE +NOTICE: TG_relatts: {{} i j} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_generated +NOTICE: TG_table_schema: public +NOTICE: TG_when: BEFORE +NOTICE: args: {} +NOTICE: NEW: {i: 11} +NOTICE: OLD: {i: 1} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_trig_after +NOTICE: TG_op: UPDATE +NOTICE: TG_relatts: {{} i j} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_generated +NOTICE: TG_table_schema: public +NOTICE: TG_when: AFTER +NOTICE: args: {} +delete from trigger_test_generated; +NOTICE: NEW: {} +NOTICE: OLD: {i: 11} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_trig_before +NOTICE: TG_op: DELETE +NOTICE: TG_relatts: {{} i j} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_generated +NOTICE: TG_table_schema: public +NOTICE: TG_when: BEFORE +NOTICE: args: {} +NOTICE: NEW: {} +NOTICE: OLD: {i: 11} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_trig_after +NOTICE: TG_op: DELETE +NOTICE: TG_relatts: {{} i j} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_generated +NOTICE: TG_table_schema: public +NOTICE: TG_when: AFTER +NOTICE: args: {} insert into trigger_test_view values(2,'insert'); NOTICE: NEW: {i: 2, v: insert} NOTICE: OLD: {} @@ -314,6 +383,8 @@ NOTICE: TG_table_name: trigger_test NOTICE: TG_table_schema: public NOTICE: TG_when: BEFORE NOTICE: args: {42 {statement trigger}} +DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated; +DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated; -- Test composite-type arguments select tcl_composite_arg_ref1(row('tkey', 42, 'ref2')); tcl_composite_arg_ref1=20 @@ -775,3 +846,21 @@ INFO: old: 1 -> a INFO: new: 1 -> b drop table transition_table_test; drop function transition_table_test_f(); +-- dealing with generated columns +CREATE FUNCTION generated_test_func1() RETURNS trigger +LANGUAGE pltcl +AS $$ +# not allowed +set NEW(j) 5 +return [array get NEW] +$$; +CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_gen= erated +FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); +TRUNCATE trigger_test_generated; +INSERT INTO trigger_test_generated (i) VALUES (1); +ERROR: cannot set generated column "j" +SELECT * FROM trigger_test_generated; + i | j=20 +---+--- +(0 rows) + diff --git a/src/pl/tcl/expected/pltcl_setup.out b/src/pl/tcl/expected/pl= tcl_setup.out index f1958c3a98..910119e385 100644 --- a/src/pl/tcl/expected/pltcl_setup.out +++ b/src/pl/tcl/expected/pltcl_setup.out @@ -59,6 +59,8 @@ CREATE TABLE trigger_test ( ); -- Make certain dropped attributes are handled correctly ALTER TABLE trigger_test DROP dropme; +CREATE TABLE trigger_test_generated + (i int, j int GENERATED ALWAYS AS (i * 2)); CREATE VIEW trigger_test_view AS SELECT i, v FROM trigger_test; CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$ if {$TG_table_name eq "trigger_test" && $TG_level eq "ROW" && $TG_op ne= "DELETE"} { @@ -110,6 +112,12 @@ FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skid= oo'); CREATE TRIGGER statement_trigger BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trigger_data(42,'statement trigger'= ); +CREATE TRIGGER show_trigger_data_trig_before +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); +CREATE TRIGGER show_trigger_data_trig_after +AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); CREATE TRIGGER show_trigger_data_view_trig INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c index e2fa43b890..c07fa82742 100644 --- a/src/pl/tcl/pltcl.c +++ b/src/pl/tcl/pltcl.c @@ -3245,6 +3245,12 @@ pltcl_build_tuple_result(Tcl_Interp *interp, Tcl_O= bj **kvObjv, int kvObjc, errmsg("cannot set system attribute \"%s\"", fieldName))); =20 + if (TupleDescAttr(tupdesc, attn - 1)->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("cannot set generated column \"%s\"", + fieldName))); + values[attn - 1] =3D utf_u2e(Tcl_GetString(kvObjv[i + 1])); } =20 diff --git a/src/pl/tcl/sql/pltcl_queries.sql b/src/pl/tcl/sql/pltcl_quer= ies.sql index 71c1238bd2..4a42853591 100644 --- a/src/pl/tcl/sql/pltcl_queries.sql +++ b/src/pl/tcl/sql/pltcl_queries.sql @@ -76,6 +76,10 @@ -- show dump of trigger data insert into trigger_test values(1,'insert'); =20 +insert into trigger_test_generated (i) values (1); +update trigger_test_generated set i =3D 11 where i =3D 1; +delete from trigger_test_generated; + insert into trigger_test_view values(2,'insert'); update trigger_test_view set v =3D 'update' where i=3D1; delete from trigger_test_view; @@ -85,6 +89,9 @@ delete from trigger_test; truncate trigger_test; =20 +DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated; +DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated; + -- Test composite-type arguments select tcl_composite_arg_ref1(row('tkey', 42, 'ref2')); select tcl_composite_arg_ref2(row('tkey', 42, 'ref2')); @@ -279,3 +286,21 @@ CREATE TRIGGER a_t AFTER UPDATE ON transition_table_= test update transition_table_test set name =3D 'b'; drop table transition_table_test; drop function transition_table_test_f(); + + +-- dealing with generated columns + +CREATE FUNCTION generated_test_func1() RETURNS trigger +LANGUAGE pltcl +AS $$ +# not allowed +set NEW(j) 5 +return [array get NEW] +$$; + +CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_gen= erated +FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); + +TRUNCATE trigger_test_generated; +INSERT INTO trigger_test_generated (i) VALUES (1); +SELECT * FROM trigger_test_generated; diff --git a/src/pl/tcl/sql/pltcl_setup.sql b/src/pl/tcl/sql/pltcl_setup.= sql index 56a90dc844..7e6ed699e3 100644 --- a/src/pl/tcl/sql/pltcl_setup.sql +++ b/src/pl/tcl/sql/pltcl_setup.sql @@ -68,6 +68,9 @@ CREATE TABLE trigger_test ( -- Make certain dropped attributes are handled correctly ALTER TABLE trigger_test DROP dropme; =20 +CREATE TABLE trigger_test_generated + (i int, j int GENERATED ALWAYS AS (i * 2)); + CREATE VIEW trigger_test_view AS SELECT i, v FROM trigger_test; =20 CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$ @@ -122,6 +125,13 @@ CREATE TRIGGER statement_trigger BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trigger_data(42,'statement trigger'= ); =20 +CREATE TRIGGER show_trigger_data_trig_before +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); +CREATE TRIGGER show_trigger_data_trig_after +AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated +FOR EACH ROW EXECUTE PROCEDURE trigger_data(); + CREATE TRIGGER show_trigger_data_view_trig INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); diff --git a/src/test/regress/expected/create_table_like.out b/src/test/r= egress/expected/create_table_like.out index 8d4543bfe8..2006162694 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -113,6 +113,52 @@ SELECT * FROM test_like_id_3; -- identity was copie= d and applied (1 row) =20 DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3; +CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2));= +\d test_like_gen_1 + Table "public.test_like_gen_1" + Column | Type | Collation | Nullable | Default =20 +--------+---------+-----------+----------+----------------------------- + a | integer | | |=20 + b | integer | | | generated always as (a * 2) + +INSERT INTO test_like_gen_1 (a) VALUES (1); +SELECT * FROM test_like_gen_1; + a | b=20 +---+--- + 1 | 2 +(1 row) + +CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1); +\d test_like_gen_2 + Table "public.test_like_gen_2" + Column | Type | Collation | Nullable | Default=20 +--------+---------+-----------+----------+--------- + a | integer | | |=20 + b | integer | | |=20 + +INSERT INTO test_like_gen_2 (a) VALUES (1); +SELECT * FROM test_like_gen_2; + a | b=20 +---+--- + 1 | =20 +(1 row) + +CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);= +\d test_like_gen_3 + Table "public.test_like_gen_3" + Column | Type | Collation | Nullable | Default =20 +--------+---------+-----------+----------+----------------------------- + a | integer | | |=20 + b | integer | | | generated always as (a * 2) + +INSERT INTO test_like_gen_3 (a) VALUES (1); +SELECT * FROM test_like_gen_3; + a | b=20 +---+--- + 1 | 2 +(1 row) + +DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3; CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copi= es indexes */ INSERT INTO inhg VALUES (5, 10); INSERT INTO inhg VALUES (20, 10); -- should fail diff --git a/src/test/regress/expected/generated.out b/src/test/regress/e= xpected/generated.out new file mode 100644 index 0000000000..812a40d136 --- /dev/null +++ b/src/test/regress/expected/generated.out @@ -0,0 +1,737 @@ +-- sanity check of system catalog +SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenera= ted NOT IN ('', 's', 'v'); + attrelid | attname | attgenerated=20 +----------+---------+-------------- +(0 rows) + +CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55));= +CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2= )); +SELECT table_name, column_name, column_default, is_nullable, is_generate= d, generation_expression FROM information_schema.columns WHERE table_name= LIKE 'gtest_' ORDER BY 1, 2; + table_name | column_name | column_default | is_nullable | is_generated = | generation_expression=20 +------------+-------------+----------------+-------------+--------------= +----------------------- + gtest0 | a | | NO | NEVER = |=20 + gtest0 | b | | YES | ALWAYS = | 55 + gtest1 | a | | NO | NEVER = |=20 + gtest1 | b | | YES | ALWAYS = | (a * 2) +(4 rows) + +SELECT table_name, column_name, dependent_column FROM information_schema= =2Ecolumn_column_usage ORDER BY 1, 2, 3; + table_name | column_name | dependent_column=20 +------------+-------------+------------------ + gtest1 | a | b +(1 row) + +\d gtest1 + Table "public.gtest1" + Column | Type | Collation | Nullable | Default =20 +--------+---------+-----------+----------+----------------------------- + a | integer | | not null |=20 + b | integer | | | generated always as (a * 2) +Indexes: + "gtest1_pkey" PRIMARY KEY, btree (a) + +-- duplicate generated +CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (= a * 2) GENERATED ALWAYS AS (a * 3)); +ERROR: multiple generation clauses specified for column "b" of table "g= test_err_1" +LINE 1: ...nt PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) GENERATED .= =2E. + ^ +-- references to other generated columns, including self-references +CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS = (b * 2)); +ERROR: cannot use generated column "b" in column generation expression +LINE 1: ...r_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2)); + ^ +DETAIL: A generated column cannot reference another generated column. +CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS = (a * 2), c int GENERATED ALWAYS AS (b * 3)); +ERROR: cannot use generated column "b" in column generation expression +LINE 1: ...RATED ALWAYS AS (a * 2), c int GENERATED ALWAYS AS (b * 3)); + ^ +DETAIL: A generated column cannot reference another generated column. +-- invalid reference +CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (= c * 2)); +ERROR: column "c" does not exist +LINE 1: ...rr_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2)); + ^ +-- functions must be immutable +CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATE= D ALWAYS AS (random())); +ERROR: cannot use function random() in column generation expression +LINE 1: ...MARY KEY, b double precision GENERATED ALWAYS AS (random())).= =2E. + ^ +DETAIL: Functions used in a column generation expression must be immuta= ble. +-- cannot have default/identity and generated +CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED = ALWAYS AS (a * 2)); +ERROR: both default and generation expression specified for column "b" = of table "gtest_err_5a" +LINE 1: ... gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED .= =2E. + ^ +CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS = identity GENERATED ALWAYS AS (a * 2)); +ERROR: both identity and generation expression specified for column "b"= of table "gtest_err_5b" +LINE 1: ...t PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED .= =2E. + ^ +INSERT INTO gtest1 VALUES (1); +INSERT INTO gtest1 VALUES (2, DEFAULT); +INSERT INTO gtest1 VALUES (3, 33); -- error +ERROR: cannot insert into column "b" +DETAIL: Column "b" is a generated column. +SELECT * FROM gtest1 ORDER BY a; + a | b=20 +---+--- + 1 | 2 + 2 | 4 +(2 rows) + +UPDATE gtest1 SET b =3D DEFAULT WHERE a =3D 1; +UPDATE gtest1 SET b =3D 11 WHERE a =3D 1; -- error +ERROR: column "b" can only be updated to DEFAULT +DETAIL: Column "b" is a generated column. +SELECT * FROM gtest1 ORDER BY a; + a | b=20 +---+--- + 1 | 2 + 2 | 4 +(2 rows) + +SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a; + a | b | b2=20 +---+---+---- + 1 | 2 | 4 + 2 | 4 | 8 +(2 rows) + +SELECT a, b FROM gtest1 WHERE b =3D 4 ORDER BY a; + a | b=20 +---+--- + 2 | 4 +(1 row) + +-- test that overflow error happens on read +INSERT INTO gtest1 VALUES (2000000000); +SELECT * FROM gtest1; +ERROR: integer out of range +DELETE FROM gtest1 WHERE a =3D 2000000000; +-- test with joins +CREATE TABLE gtestx (x int, y int); +INSERT INTO gtestx VALUES (11, 1), (22, 2), (33, 3); +SELECT * FROM gtestx, gtest1 WHERE gtestx.y =3D gtest1.a; + x | y | a | b=20 +----+---+---+--- + 11 | 1 | 1 | 2 + 22 | 2 | 2 | 4 +(2 rows) + +DROP TABLE gtestx; +-- test UPDATE/DELETE quals +SELECT * FROM gtest1 ORDER BY a; + a | b=20 +---+--- + 1 | 2 + 2 | 4 +(2 rows) + +UPDATE gtest1 SET a =3D 3 WHERE b =3D 4; +SELECT * FROM gtest1 ORDER BY a; + a | b=20 +---+--- + 1 | 2 + 3 | 6 +(2 rows) + +DELETE FROM gtest1 WHERE b =3D 2; +SELECT * FROM gtest1 ORDER BY a; + a | b=20 +---+--- + 3 | 6 +(1 row) + +-- views +CREATE VIEW gtest1v AS SELECT * FROM gtest1; +SELECT * FROM gtest1v; + a | b=20 +---+--- + 3 | 6 +(1 row) + +INSERT INTO gtest1v VALUES (4, 8); -- fails +ERROR: cannot insert into column "b" +DETAIL: Column "b" is a generated column. +-- inheritance +CREATE TABLE gtest1_1 () INHERITS (gtest1); +SELECT * FROM gtest1_1; + a | b=20 +---+--- +(0 rows) + +\d gtest1_1 + Table "public.gtest1_1" + Column | Type | Collation | Nullable | Default =20 +--------+---------+-----------+----------+----------------------------- + a | integer | | not null |=20 + b | integer | | | generated always as (a * 2) +Inherits: gtest1 + +INSERT INTO gtest1_1 VALUES (4); +SELECT * FROM gtest1_1; + a | b=20 +---+--- + 4 | 8 +(1 row) + +SELECT * FROM gtest1; + a | b=20 +---+--- + 3 | 6 + 4 | 8 +(2 rows) + +-- test inheritance mismatch +CREATE TABLE gtesty (x int, b int); +CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error +NOTICE: merging multiple inherited definitions of column "b" +ERROR: inherited column "b" has a generation conflict +DROP TABLE gtesty; +-- stored +CREATE TABLE gtest3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 3= ) STORED); +INSERT INTO gtest3 (a) VALUES (1), (2), (3); +SELECT * FROM gtest3 ORDER BY a; + a | b=20 +---+--- + 1 | 3 + 2 | 6 + 3 | 9 +(3 rows) + +UPDATE gtest3 SET a =3D 22 WHERE a =3D 2; +SELECT * FROM gtest3 ORDER BY a; + a | b =20 +----+---- + 1 | 3 + 3 | 9 + 22 | 66 +(3 rows) + +-- COPY +TRUNCATE gtest1; +INSERT INTO gtest1 (a) VALUES (1), (2); +COPY gtest1 TO stdout; +1 +2 +COPY gtest1 (a, b) TO stdout; +ERROR: column "b" is a generated column +DETAIL: Generated columns cannot be used in COPY. +COPY gtest1 FROM stdin; +COPY gtest1 (a, b) FROM stdin; +ERROR: column "b" is a generated column +DETAIL: Generated columns cannot be used in COPY. +SELECT * FROM gtest1 ORDER BY a; + a | b=20 +---+--- + 1 | 2 + 2 | 4 + 3 | 6 + 4 | 8 +(4 rows) + +TRUNCATE gtest3; +INSERT INTO gtest3 (a) VALUES (1), (2); +COPY gtest3 TO stdout; +1 +2 +COPY gtest3 (a, b) TO stdout; +ERROR: column "b" is a generated column +DETAIL: Generated columns cannot be used in COPY. +COPY gtest3 FROM stdin; +COPY gtest3 (a, b) FROM stdin; +ERROR: column "b" is a generated column +DETAIL: Generated columns cannot be used in COPY. +SELECT * FROM gtest3 ORDER BY a; + a | b =20 +---+---- + 1 | 3 + 2 | 6 + 3 | 9 + 4 | 12 +(4 rows) + +-- drop column behavior +CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS A= S (b * 2)); +ALTER TABLE gtest10 DROP COLUMN b; +\d gtest10 + Table "public.gtest10" + Column | Type | Collation | Nullable | Default=20 +--------+---------+-----------+----------+--------- + a | integer | | not null |=20 +Indexes: + "gtest10_pkey" PRIMARY KEY, btree (a) + +-- privileges +CREATE USER regress_user11; +CREATE TABLE gtest11v (a int PRIMARY KEY, b int, c int GENERATED ALWAYS = AS (b * 2) VIRTUAL); +INSERT INTO gtest11v VALUES (1, 10), (2, 20); +GRANT SELECT (a, c) ON gtest11v TO regress_user11; +CREATE TABLE gtest11s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS = AS (b * 2) STORED); +INSERT INTO gtest11s VALUES (1, 10), (2, 20); +GRANT SELECT (a, c) ON gtest11s TO regress_user11; +CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE L= ANGUAGE SQL; +REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC; +CREATE TABLE gtest12v (a int PRIMARY KEY, b int, c int GENERATED ALWAYS = AS (gf1(b)) VIRTUAL); +INSERT INTO gtest12v VALUES (1, 10), (2, 20); +GRANT SELECT (a, c) ON gtest12v TO regress_user11; +CREATE TABLE gtest12s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS = AS (gf1(b)) STORED); +INSERT INTO gtest12s VALUES (1, 10), (2, 20); +GRANT SELECT (a, c) ON gtest12s TO regress_user11; +SET ROLE regress_user11; +SELECT a, b FROM gtest11v; -- not allowed +ERROR: permission denied for table gtest11v +SELECT a, c FROM gtest11v; -- allowed + a | c =20 +---+---- + 1 | 20 + 2 | 40 +(2 rows) + +SELECT a, b FROM gtest11s; -- not allowed +ERROR: permission denied for table gtest11s +SELECT a, c FROM gtest11s; -- allowed + a | c =20 +---+---- + 1 | 20 + 2 | 40 +(2 rows) + +SELECT gf1(10); -- not allowed +ERROR: permission denied for function gf1 +SELECT a, c FROM gtest12v; -- not allowed; TODO: ought to be allowed +ERROR: permission denied for function gf1 +SELECT a, c FROM gtest12s; -- allowed + a | c =20 +---+---- + 1 | 30 + 2 | 60 +(2 rows) + +RESET ROLE; +DROP TABLE gtest11v, gtest11s, gtest12v, gtest12s; +DROP FUNCTION gf1(int); +DROP USER regress_user11; +-- check constraints +CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * = 2) CHECK (b < 50)); +INSERT INTO gtest20 (a) VALUES (10); -- ok +INSERT INTO gtest20 (a) VALUES (30); -- violates constraint +ERROR: new row for relation "gtest20" violates check constraint "gtest2= 0_b_check" +DETAIL: Failing row contains (30). +CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2)); +INSERT INTO gtest20a (a) VALUES (10); +INSERT INTO gtest20a (a) VALUES (30); +ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row +ERROR: check constraint "gtest20a_b_check" is violated by some row +CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2)); +INSERT INTO gtest20b (a) VALUES (10); +INSERT INTO gtest20b (a) VALUES (30); +ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID; +ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row +ERROR: check constraint "chk" is violated by some row +-- not-null constraints +CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nul= lif(a, 0)) NOT NULL); +INSERT INTO gtest21a (a) VALUES (1); -- ok +INSERT INTO gtest21a (a) VALUES (0); -- violates constraint +ERROR: new row for relation "gtest21a" violates check constraint "gtest= 21a_b_check" +DETAIL: Failing row contains (0). +CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nul= lif(a, 0))); +ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL; -- error +ERROR: cannot use SET NOT NULL on virtual generated column "b" +HINT: Add a CHECK constraint instead. +ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL; -- error +ERROR: cannot use DROP NOT NULL on virtual generated column "b" +CREATE TABLE gtest21c (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nul= lif(a, 0)) STORED NOT NULL); +INSERT INTO gtest21c (a) VALUES (1); -- ok +INSERT INTO gtest21c (a) VALUES (0); -- violates constraint +ERROR: null value in column "b" violates not-null constraint +DETAIL: Failing row contains (0, null). +CREATE TABLE gtest21d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nul= lif(a, 0)) STORED); +ALTER TABLE gtest21d ALTER COLUMN b SET NOT NULL; +INSERT INTO gtest21d (a) VALUES (1); -- ok +INSERT INTO gtest21d (a) VALUES (0); -- violates constraint +ERROR: null value in column "b" violates not-null constraint +DETAIL: Failing row contains (0, null). +ALTER TABLE gtest21d ALTER COLUMN b DROP NOT NULL; +INSERT INTO gtest21d (a) VALUES (0); -- ok now +-- index constraints +CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2) UNIQUE); -- error +ERROR: index creation on virtual generated columns is not supported +CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a * 2), PRIMARY= KEY (a, b)); -- error +ERROR: index creation on virtual generated columns is not supported +-- indexes +CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2)); +CREATE INDEX gtest22c_b_idx ON gtest22c (b); -- error +ERROR: index creation on virtual generated columns is not supported +CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3)); +ERROR: index creation on virtual generated columns is not supported +CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0; +ERROR: index creation on virtual generated columns is not supported +\d gtest22c + Table "public.gtest22c" + Column | Type | Collation | Nullable | Default =20 +--------+---------+-----------+----------+----------------------------- + a | integer | | |=20 + b | integer | | | generated always as (a * 2) + +INSERT INTO gtest22c VALUES (1), (2), (3); +SET enable_seqscan TO off; +SET enable_bitmapscan TO off; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 =3D 6; + QUERY PLAN =20 +------------------------------- + Seq Scan on gtest22c + Filter: (((a * 2) * 3) =3D 6) +(2 rows) + +SELECT * FROM gtest22c WHERE b * 3 =3D 6; + a | b=20 +---+--- + 1 | 2 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a =3D 1 AND b > 0; + QUERY PLAN =20 +--------------------------------------- + Seq Scan on gtest22c + Filter: ((a =3D 1) AND ((a * 2) > 0)) +(2 rows) + +SELECT * FROM gtest22c WHERE a =3D 1 AND b > 0; + a | b=20 +---+--- + 1 | 2 +(1 row) + +RESET enable_seqscan; +RESET enable_bitmapscan; +CREATE TABLE gtest22d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);= +CREATE INDEX gtest22d_b_idx ON gtest22d (b); +CREATE INDEX gtest22d_expr_idx ON gtest22d ((b * 3)); +CREATE INDEX gtest22d_pred_idx ON gtest22d (a) WHERE b > 0; +\d gtest22d + Table "public.gtest22d" + Column | Type | Collation | Nullable | Default = =20 +--------+---------+-----------+----------+------------------------------= ------ + a | integer | | |=20 + b | integer | | | generated always as (a * 2) s= tored +Indexes: + "gtest22d_b_idx" btree (b) + "gtest22d_expr_idx" btree ((b * 3)) + "gtest22d_pred_idx" btree (a) WHERE b > 0 + +INSERT INTO gtest22d VALUES (1), (2), (3); +SET enable_seqscan TO off; +SET enable_bitmapscan TO off; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE b =3D 4; + QUERY PLAN =20 +--------------------------------------------- + Index Scan using gtest22d_b_idx on gtest22d + Index Cond: (b =3D 4) +(2 rows) + +SELECT * FROM gtest22d WHERE b =3D 4; + a | b=20 +---+--- + 2 | 4 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE b * 3 =3D 6; + QUERY PLAN =20 +------------------------------------------------ + Index Scan using gtest22d_expr_idx on gtest22d + Index Cond: ((b * 3) =3D 6) +(2 rows) + +SELECT * FROM gtest22d WHERE b * 3 =3D 6; + a | b=20 +---+--- + 1 | 2 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE a =3D 1 AND b > 0; + QUERY PLAN =20 +------------------------------------------------ + Index Scan using gtest22d_pred_idx on gtest22d + Index Cond: (a =3D 1) +(2 rows) + +SELECT * FROM gtest22d WHERE a =3D 1 AND b > 0; + a | b=20 +---+--- + 1 | 2 +(1 row) + +RESET enable_seqscan; +RESET enable_bitmapscan; +-- foreign keys +CREATE TABLE gtest23a (x int PRIMARY KEY, y int); +INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33); +CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2) REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error +ERROR: invalid ON UPDATE action for foreign key constraint containing g= enerated column +CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2) REFERENCES gtest23a (x) ON DELETE SET NULL); -- error +ERROR: invalid ON DELETE action for foreign key constraint containing g= enerated column +CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2) REFERENCES gtest23a (x)); -- error +ERROR: foreign key constraints on virtual generated columns are not sup= ported +CREATE TABLE gtest23c (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2) STORED REFERENCES gtest23a (x)); +\d gtest23c + Table "public.gtest23c" + Column | Type | Collation | Nullable | Default = =20 +--------+---------+-----------+----------+------------------------------= ------ + a | integer | | not null |=20 + b | integer | | | generated always as (a * 2) s= tored +Indexes: + "gtest23c_pkey" PRIMARY KEY, btree (a) +Foreign-key constraints: + "gtest23c_b_fkey" FOREIGN KEY (b) REFERENCES gtest23a(x) + +INSERT INTO gtest23c VALUES (1); -- ok +INSERT INTO gtest23c VALUES (5); -- error +ERROR: insert or update on table "gtest23c" violates foreign key constr= aint "gtest23c_b_fkey" +DETAIL: Key (b)=3D(10) is not present in table "gtest23a". +DROP TABLE gtest23c; +DROP TABLE gtest23a; +CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) STORED, = PRIMARY KEY (y)); +INSERT INTO gtest23p VALUES (1), (2), (3); +CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y))= ; +INSERT INTO gtest23q VALUES (1, 2); -- ok +INSERT INTO gtest23q VALUES (2, 5); -- error +ERROR: insert or update on table "gtest23q" violates foreign key constr= aint "gtest23q_b_fkey" +DETAIL: Key (b)=3D(5) is not present in table "gtest23p". +-- no test for PK using virtual column, since such an index cannot be cr= eated +-- domains +CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10); +CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS= AS (a * 2)); -- prohibited +ERROR: virtual generated column "b" cannot have a domain type +LINE 1: CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENE... + ^ +-- typed tables (currently not supported) +CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint); +CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS = (f2 *2)); +ERROR: generated colums are not supported on typed tables +DROP TYPE gtest_type CASCADE; +-- table partitions (currently not supported) +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITI= ON BY RANGE (f1); +CREATE TABLE gtest_child PARTITION OF gtest_parent ( + f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) +) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error +ERROR: generated columns are not supported on partitions +DROP TABLE gtest_parent; +-- partitioned table +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERA= TED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f1); +CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('201= 6-07-01') TO ('2016-08-01'); +INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1); +SELECT * FROM gtest_parent; + f1 | f2 | f3=20 +------------+----+---- + 07-15-2016 | 1 | 2 +(1 row) + +SELECT * FROM gtest_child; + f1 | f2 | f3=20 +------------+----+---- + 07-15-2016 | 1 | 2 +(1 row) + +DROP TABLE gtest_parent; +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERA= TED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1); +CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('201= 6-07-01') TO ('2016-08-01'); +INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1); +SELECT * FROM gtest_parent; + f1 | f2 | f3=20 +------------+----+---- + 07-15-2016 | 1 | 2 +(1 row) + +SELECT * FROM gtest_child; + f1 | f2 | f3=20 +------------+----+---- + 07-15-2016 | 1 | 2 +(1 row) + +DROP TABLE gtest_parent; +-- generated columns in partition key (currently not supported) +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERA= TED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f3); +ERROR: using generated column in partition key is not supported +LINE 1: ...igint GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f3); + ^ +DETAIL: Column "f3" is a generated column. +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERA= TED ALWAYS AS (f2 * 2)) PARTITION BY RANGE ((f3 * 3)); +ERROR: using generated column in partition key is not supported +LINE 1: ...GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE ((f3 * 3)); + ^ +DETAIL: Column "f3" is a generated column. +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERA= TED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3)); +ERROR: using generated column in partition key is not supported +LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3)); + ^ +DETAIL: Column "f3" is a generated column. +/* +CREATE TABLE gtest_child1 PARTITION OF gtest_parent FOR VALUES FROM (1) = TO (10); +CREATE TABLE gtest_child2 PARTITION OF gtest_parent FOR VALUES FROM (11)= TO (20); +INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1); +INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 3); +INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 10); -- error +SELECT * FROM gtest_child1; +SELECT * FROM gtest_child2; +DROP TABLE gtest_parent; +*/ +-- ALTER TABLE ... ADD COLUMN +CREATE TABLE gtest25 (a int PRIMARY KEY); +INSERT INTO gtest25 VALUES (3), (4); +ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 3); +SELECT * FROM gtest25 ORDER BY a; + a | b =20 +---+---- + 3 | 9 + 4 | 12 +(2 rows) + +ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4); -- er= ror +ERROR: cannot use generated column "b" in column generation expression +DETAIL: A generated column cannot reference another generated column. +ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4); -- er= ror +ERROR: column "z" does not exist +-- ALTER TABLE ... ALTER COLUMN +CREATE TABLE gtest27 (a int, b int GENERATED ALWAYS AS (a * 2)); +INSERT INTO gtest27 (a) VALUES (3), (4); +ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error +ERROR: cannot alter type of a column used by a generated column +DETAIL: Column "a" is used by generated column "b". +ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric; +\d gtest27 + Table "public.gtest27" + Column | Type | Collation | Nullable | Default = =20 +--------+---------+-----------+----------+------------------------------= - + a | integer | | |=20 + b | numeric | | | generated always as ((a * 2))= + +SELECT * FROM gtest27; + a | b=20 +---+--- + 3 | 6 + 4 | 8 +(2 rows) + +ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean; -- error +ERROR: generation expression for column "b" cannot be cast automaticall= y to type boolean +ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- error +ERROR: column "b" of relation "gtest27" is a generated column +\d gtest27 + Table "public.gtest27" + Column | Type | Collation | Nullable | Default = =20 +--------+---------+-----------+----------+------------------------------= - + a | integer | | |=20 + b | numeric | | | generated always as ((a * 2))= + +-- triggers +CREATE TABLE gtest26 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * = 2)); +CREATE FUNCTION gtest_trigger_func() RETURNS trigger + LANGUAGE plpgsql +AS $$ +BEGIN + IF tg_op IN ('DELETE', 'UPDATE') THEN + RAISE INFO '%: old =3D %', TG_NAME, OLD; + END IF; + IF tg_op IN ('INSERT', 'UPDATE') THEN + RAISE INFO '%: new =3D %', TG_NAME, NEW; + END IF; + IF tg_op =3D 'DELETE' THEN + RETURN OLD; + ELSE + RETURN NEW; + END IF; +END +$$; +CREATE TRIGGER gtest1 BEFORE DELETE OR UPDATE ON gtest26 + FOR EACH ROW + WHEN (OLD.b < 0) -- ok + EXECUTE PROCEDURE gtest_trigger_func(); +CREATE TRIGGER gtest2 BEFORE INSERT OR UPDATE ON gtest26 + FOR EACH ROW + WHEN (NEW.b < 0) -- error + EXECUTE PROCEDURE gtest_trigger_func(); +ERROR: BEFORE trigger's WHEN condition cannot reference NEW generated c= olumns +LINE 3: WHEN (NEW.b < 0) =20 + ^ +CREATE TRIGGER gtest3 AFTER DELETE OR UPDATE ON gtest26 + FOR EACH ROW + WHEN (OLD.b < 0) -- ok + EXECUTE PROCEDURE gtest_trigger_func(); +CREATE TRIGGER gtest4 AFTER INSERT OR UPDATE ON gtest26 + FOR EACH ROW + WHEN (NEW.b < 0) -- ok + EXECUTE PROCEDURE gtest_trigger_func(); +INSERT INTO gtest26 (a) VALUES (-2), (0), (3); +INFO: gtest4: new =3D (-2,) +SELECT * FROM gtest26 ORDER BY a; + a | b =20 +----+---- + -2 | -4 + 0 | 0 + 3 | 6 +(3 rows) + +UPDATE gtest26 SET a =3D a * -2; +INFO: gtest1: old =3D (-2,) +INFO: gtest1: new =3D (4,) +INFO: gtest3: old =3D (-2,) +INFO: gtest3: new =3D (4,) +INFO: gtest4: old =3D (3,) +INFO: gtest4: new =3D (-6,) +SELECT * FROM gtest26 ORDER BY a; + a | b =20 +----+----- + -6 | -12 + 0 | 0 + 4 | 8 +(3 rows) + +DELETE FROM gtest26 WHERE a =3D -6; +INFO: gtest1: old =3D (-6,) +INFO: gtest3: old =3D (-6,) +SELECT * FROM gtest26 ORDER BY a; + a | b=20 +---+--- + 0 | 0 + 4 | 8 +(2 rows) + +CREATE FUNCTION gtest_trigger_func2() RETURNS trigger + LANGUAGE plpgsql +AS $$ +BEGIN + NEW.b =3D 5; + RETURN NEW; +END +$$; +CREATE TRIGGER gtest10 BEFORE INSERT OR UPDATE ON gtest26 + FOR EACH ROW + EXECUTE PROCEDURE gtest_trigger_func2(); +INSERT INTO gtest26 (a) VALUES (10); +ERROR: trigger modified virtual generated column value +UPDATE gtest26 SET a =3D 1 WHERE a =3D 0; +ERROR: trigger modified virtual generated column value +-- LIKE INCLUDING GENERATED and dropped column handling +CREATE TABLE gtest28a ( + a int, + b int, + c int, + x int GENERATED ALWAYS AS (b * 2) +); +ALTER TABLE gtest28a DROP COLUMN a; +CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); +\d gtest28* + Table "public.gtest28a" + Column | Type | Collation | Nullable | Default =20 +--------+---------+-----------+----------+----------------------------- + b | integer | | |=20 + c | integer | | |=20 + x | integer | | | generated always as (b * 2) + + Table "public.gtest28b" + Column | Type | Collation | Nullable | Default =20 +--------+---------+-----------+----------+----------------------------- + b | integer | | |=20 + c | integer | | |=20 + x | integer | | | generated always as (b * 2) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parall= el_schedule index 8f07343c1a..bbd6a4a85c 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain range= funcs prepare without_oid c # ---------- # Another group of parallel tests # ---------- -test: identity partition_join partition_prune reloptions hash_part index= ing partition_aggregate +test: identity generated partition_join partition_prune reloptions hash_= part indexing partition_aggregate =20 # event triggers cannot run concurrently with any test that runs DDL test: event_trigger diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_s= chedule index 265e2cda50..330d28ac8d 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -179,6 +179,7 @@ test: largeobject test: with test: xml test: identity +test: generated test: partition_join test: partition_prune test: reloptions diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regres= s/sql/create_table_like.sql index 42cad6826b..c461ea7904 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -51,6 +51,20 @@ CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLU= DING IDENTITY); SELECT * FROM test_like_id_3; -- identity was copied and applied DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3; =20 +CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2));= +\d test_like_gen_1 +INSERT INTO test_like_gen_1 (a) VALUES (1); +SELECT * FROM test_like_gen_1; +CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1); +\d test_like_gen_2 +INSERT INTO test_like_gen_2 (a) VALUES (1); +SELECT * FROM test_like_gen_2; +CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);= +\d test_like_gen_3 +INSERT INTO test_like_gen_3 (a) VALUES (1); +SELECT * FROM test_like_gen_3; +DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3; + CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copi= es indexes */ INSERT INTO inhg VALUES (5, 10); INSERT INTO inhg VALUES (20, 10); -- should fail diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/ge= nerated.sql new file mode 100644 index 0000000000..8f6609da23 --- /dev/null +++ b/src/test/regress/sql/generated.sql @@ -0,0 +1,408 @@ +-- sanity check of system catalog +SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenera= ted NOT IN ('', 's', 'v'); + + +CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55));= +CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2= )); + +SELECT table_name, column_name, column_default, is_nullable, is_generate= d, generation_expression FROM information_schema.columns WHERE table_name= LIKE 'gtest_' ORDER BY 1, 2; + +SELECT table_name, column_name, dependent_column FROM information_schema= =2Ecolumn_column_usage ORDER BY 1, 2, 3; + +\d gtest1 + +-- duplicate generated +CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (= a * 2) GENERATED ALWAYS AS (a * 3)); + +-- references to other generated columns, including self-references +CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS = (b * 2)); +CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS = (a * 2), c int GENERATED ALWAYS AS (b * 3)); + +-- invalid reference +CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (= c * 2)); + +-- functions must be immutable +CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATE= D ALWAYS AS (random())); + +-- cannot have default/identity and generated +CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED = ALWAYS AS (a * 2)); +CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS = identity GENERATED ALWAYS AS (a * 2)); + +INSERT INTO gtest1 VALUES (1); +INSERT INTO gtest1 VALUES (2, DEFAULT); +INSERT INTO gtest1 VALUES (3, 33); -- error + +SELECT * FROM gtest1 ORDER BY a; + +UPDATE gtest1 SET b =3D DEFAULT WHERE a =3D 1; +UPDATE gtest1 SET b =3D 11 WHERE a =3D 1; -- error + +SELECT * FROM gtest1 ORDER BY a; + +SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a; +SELECT a, b FROM gtest1 WHERE b =3D 4 ORDER BY a; + +-- test that overflow error happens on read +INSERT INTO gtest1 VALUES (2000000000); +SELECT * FROM gtest1; +DELETE FROM gtest1 WHERE a =3D 2000000000; + +-- test with joins +CREATE TABLE gtestx (x int, y int); +INSERT INTO gtestx VALUES (11, 1), (22, 2), (33, 3); +SELECT * FROM gtestx, gtest1 WHERE gtestx.y =3D gtest1.a; +DROP TABLE gtestx; + +-- test UPDATE/DELETE quals +SELECT * FROM gtest1 ORDER BY a; +UPDATE gtest1 SET a =3D 3 WHERE b =3D 4; +SELECT * FROM gtest1 ORDER BY a; +DELETE FROM gtest1 WHERE b =3D 2; +SELECT * FROM gtest1 ORDER BY a; + +-- views +CREATE VIEW gtest1v AS SELECT * FROM gtest1; +SELECT * FROM gtest1v; +INSERT INTO gtest1v VALUES (4, 8); -- fails + +-- inheritance +CREATE TABLE gtest1_1 () INHERITS (gtest1); +SELECT * FROM gtest1_1; +\d gtest1_1 +INSERT INTO gtest1_1 VALUES (4); +SELECT * FROM gtest1_1; +SELECT * FROM gtest1; + +-- test inheritance mismatch +CREATE TABLE gtesty (x int, b int); +CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error +DROP TABLE gtesty; + +-- stored +CREATE TABLE gtest3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 3= ) STORED); +INSERT INTO gtest3 (a) VALUES (1), (2), (3); +SELECT * FROM gtest3 ORDER BY a; +UPDATE gtest3 SET a =3D 22 WHERE a =3D 2; +SELECT * FROM gtest3 ORDER BY a; + +-- COPY +TRUNCATE gtest1; +INSERT INTO gtest1 (a) VALUES (1), (2); + +COPY gtest1 TO stdout; + +COPY gtest1 (a, b) TO stdout; + +COPY gtest1 FROM stdin; +3 +4 +\. + +COPY gtest1 (a, b) FROM stdin; + +SELECT * FROM gtest1 ORDER BY a; + +TRUNCATE gtest3; +INSERT INTO gtest3 (a) VALUES (1), (2); + +COPY gtest3 TO stdout; + +COPY gtest3 (a, b) TO stdout; + +COPY gtest3 FROM stdin; +3 +4 +\. + +COPY gtest3 (a, b) FROM stdin; + +SELECT * FROM gtest3 ORDER BY a; + +-- drop column behavior +CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS A= S (b * 2)); +ALTER TABLE gtest10 DROP COLUMN b; + +\d gtest10 + +-- privileges +CREATE USER regress_user11; + +CREATE TABLE gtest11v (a int PRIMARY KEY, b int, c int GENERATED ALWAYS = AS (b * 2) VIRTUAL); +INSERT INTO gtest11v VALUES (1, 10), (2, 20); +GRANT SELECT (a, c) ON gtest11v TO regress_user11; + +CREATE TABLE gtest11s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS = AS (b * 2) STORED); +INSERT INTO gtest11s VALUES (1, 10), (2, 20); +GRANT SELECT (a, c) ON gtest11s TO regress_user11; + +CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE L= ANGUAGE SQL; +REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC; + +CREATE TABLE gtest12v (a int PRIMARY KEY, b int, c int GENERATED ALWAYS = AS (gf1(b)) VIRTUAL); +INSERT INTO gtest12v VALUES (1, 10), (2, 20); +GRANT SELECT (a, c) ON gtest12v TO regress_user11; + +CREATE TABLE gtest12s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS = AS (gf1(b)) STORED); +INSERT INTO gtest12s VALUES (1, 10), (2, 20); +GRANT SELECT (a, c) ON gtest12s TO regress_user11; + +SET ROLE regress_user11; +SELECT a, b FROM gtest11v; -- not allowed +SELECT a, c FROM gtest11v; -- allowed +SELECT a, b FROM gtest11s; -- not allowed +SELECT a, c FROM gtest11s; -- allowed +SELECT gf1(10); -- not allowed +SELECT a, c FROM gtest12v; -- not allowed; TODO: ought to be allowed +SELECT a, c FROM gtest12s; -- allowed +RESET ROLE; + +DROP TABLE gtest11v, gtest11s, gtest12v, gtest12s; +DROP FUNCTION gf1(int); +DROP USER regress_user11; + +-- check constraints +CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * = 2) CHECK (b < 50)); +INSERT INTO gtest20 (a) VALUES (10); -- ok +INSERT INTO gtest20 (a) VALUES (30); -- violates constraint + +CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2)); +INSERT INTO gtest20a (a) VALUES (10); +INSERT INTO gtest20a (a) VALUES (30); +ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row + +CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2)); +INSERT INTO gtest20b (a) VALUES (10); +INSERT INTO gtest20b (a) VALUES (30); +ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID; +ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row + +-- not-null constraints +CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nul= lif(a, 0)) NOT NULL); +INSERT INTO gtest21a (a) VALUES (1); -- ok +INSERT INTO gtest21a (a) VALUES (0); -- violates constraint + +CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nul= lif(a, 0))); +ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL; -- error +ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL; -- error + +CREATE TABLE gtest21c (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nul= lif(a, 0)) STORED NOT NULL); +INSERT INTO gtest21c (a) VALUES (1); -- ok +INSERT INTO gtest21c (a) VALUES (0); -- violates constraint + +CREATE TABLE gtest21d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nul= lif(a, 0)) STORED); +ALTER TABLE gtest21d ALTER COLUMN b SET NOT NULL; +INSERT INTO gtest21d (a) VALUES (1); -- ok +INSERT INTO gtest21d (a) VALUES (0); -- violates constraint +ALTER TABLE gtest21d ALTER COLUMN b DROP NOT NULL; +INSERT INTO gtest21d (a) VALUES (0); -- ok now + +-- index constraints +CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2) UNIQUE); -- error +CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a * 2), PRIMARY= KEY (a, b)); -- error + +-- indexes +CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2)); +CREATE INDEX gtest22c_b_idx ON gtest22c (b); -- error +CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3)); +CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0; +\d gtest22c + +INSERT INTO gtest22c VALUES (1), (2), (3); +SET enable_seqscan TO off; +SET enable_bitmapscan TO off; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 =3D 6; +SELECT * FROM gtest22c WHERE b * 3 =3D 6; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a =3D 1 AND b > 0; +SELECT * FROM gtest22c WHERE a =3D 1 AND b > 0; +RESET enable_seqscan; +RESET enable_bitmapscan; + +CREATE TABLE gtest22d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);= +CREATE INDEX gtest22d_b_idx ON gtest22d (b); +CREATE INDEX gtest22d_expr_idx ON gtest22d ((b * 3)); +CREATE INDEX gtest22d_pred_idx ON gtest22d (a) WHERE b > 0; +\d gtest22d + +INSERT INTO gtest22d VALUES (1), (2), (3); +SET enable_seqscan TO off; +SET enable_bitmapscan TO off; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE b =3D 4; +SELECT * FROM gtest22d WHERE b =3D 4; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE b * 3 =3D 6; +SELECT * FROM gtest22d WHERE b * 3 =3D 6; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE a =3D 1 AND b > 0; +SELECT * FROM gtest22d WHERE a =3D 1 AND b > 0; +RESET enable_seqscan; +RESET enable_bitmapscan; + +-- foreign keys +CREATE TABLE gtest23a (x int PRIMARY KEY, y int); +INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33); + +CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2) REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error +CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2) REFERENCES gtest23a (x) ON DELETE SET NULL); -- error + +CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2) REFERENCES gtest23a (x)); -- error + +CREATE TABLE gtest23c (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *= 2) STORED REFERENCES gtest23a (x)); +\d gtest23c + +INSERT INTO gtest23c VALUES (1); -- ok +INSERT INTO gtest23c VALUES (5); -- error + +DROP TABLE gtest23c; +DROP TABLE gtest23a; + +CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) STORED, = PRIMARY KEY (y)); +INSERT INTO gtest23p VALUES (1), (2), (3); + +CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y))= ; +INSERT INTO gtest23q VALUES (1, 2); -- ok +INSERT INTO gtest23q VALUES (2, 5); -- error + +-- no test for PK using virtual column, since such an index cannot be cr= eated + +-- domains +CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10); +CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS= AS (a * 2)); -- prohibited + +-- typed tables (currently not supported) +CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint); +CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS = (f2 *2)); +DROP TYPE gtest_type CASCADE; + +-- table partitions (currently not supported) +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITI= ON BY RANGE (f1); +CREATE TABLE gtest_child PARTITION OF gtest_parent ( + f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) +) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error +DROP TABLE gtest_parent; + +-- partitioned table +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERA= TED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f1); +CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('201= 6-07-01') TO ('2016-08-01'); +INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1); +SELECT * FROM gtest_parent; +SELECT * FROM gtest_child; +DROP TABLE gtest_parent; + +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERA= TED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1); +CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('201= 6-07-01') TO ('2016-08-01'); +INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1); +SELECT * FROM gtest_parent; +SELECT * FROM gtest_child; +DROP TABLE gtest_parent; + +-- generated columns in partition key (currently not supported) +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERA= TED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f3); +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERA= TED ALWAYS AS (f2 * 2)) PARTITION BY RANGE ((f3 * 3)); +CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERA= TED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3)); +/* +CREATE TABLE gtest_child1 PARTITION OF gtest_parent FOR VALUES FROM (1) = TO (10); +CREATE TABLE gtest_child2 PARTITION OF gtest_parent FOR VALUES FROM (11)= TO (20); +INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1); +INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 3); +INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 10); -- error +SELECT * FROM gtest_child1; +SELECT * FROM gtest_child2; +DROP TABLE gtest_parent; +*/ + +-- ALTER TABLE ... ADD COLUMN +CREATE TABLE gtest25 (a int PRIMARY KEY); +INSERT INTO gtest25 VALUES (3), (4); +ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 3); +SELECT * FROM gtest25 ORDER BY a; +ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4); -- er= ror +ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4); -- er= ror + +-- ALTER TABLE ... ALTER COLUMN +CREATE TABLE gtest27 (a int, b int GENERATED ALWAYS AS (a * 2)); +INSERT INTO gtest27 (a) VALUES (3), (4); +ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error +ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric; +\d gtest27 +SELECT * FROM gtest27; +ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean; -- error + +ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- error +\d gtest27 + +-- triggers +CREATE TABLE gtest26 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * = 2)); + +CREATE FUNCTION gtest_trigger_func() RETURNS trigger + LANGUAGE plpgsql +AS $$ +BEGIN + IF tg_op IN ('DELETE', 'UPDATE') THEN + RAISE INFO '%: old =3D %', TG_NAME, OLD; + END IF; + IF tg_op IN ('INSERT', 'UPDATE') THEN + RAISE INFO '%: new =3D %', TG_NAME, NEW; + END IF; + IF tg_op =3D 'DELETE' THEN + RETURN OLD; + ELSE + RETURN NEW; + END IF; +END +$$; + +CREATE TRIGGER gtest1 BEFORE DELETE OR UPDATE ON gtest26 + FOR EACH ROW + WHEN (OLD.b < 0) -- ok + EXECUTE PROCEDURE gtest_trigger_func(); + +CREATE TRIGGER gtest2 BEFORE INSERT OR UPDATE ON gtest26 + FOR EACH ROW + WHEN (NEW.b < 0) -- error + EXECUTE PROCEDURE gtest_trigger_func(); + +CREATE TRIGGER gtest3 AFTER DELETE OR UPDATE ON gtest26 + FOR EACH ROW + WHEN (OLD.b < 0) -- ok + EXECUTE PROCEDURE gtest_trigger_func(); + +CREATE TRIGGER gtest4 AFTER INSERT OR UPDATE ON gtest26 + FOR EACH ROW + WHEN (NEW.b < 0) -- ok + EXECUTE PROCEDURE gtest_trigger_func(); + +INSERT INTO gtest26 (a) VALUES (-2), (0), (3); +SELECT * FROM gtest26 ORDER BY a; +UPDATE gtest26 SET a =3D a * -2; +SELECT * FROM gtest26 ORDER BY a; +DELETE FROM gtest26 WHERE a =3D -6; +SELECT * FROM gtest26 ORDER BY a; + + +CREATE FUNCTION gtest_trigger_func2() RETURNS trigger + LANGUAGE plpgsql +AS $$ +BEGIN + NEW.b =3D 5; + RETURN NEW; +END +$$; + +CREATE TRIGGER gtest10 BEFORE INSERT OR UPDATE ON gtest26 + FOR EACH ROW + EXECUTE PROCEDURE gtest_trigger_func2(); + +INSERT INTO gtest26 (a) VALUES (10); +UPDATE gtest26 SET a =3D 1 WHERE a =3D 0; + +-- LIKE INCLUDING GENERATED and dropped column handling +CREATE TABLE gtest28a ( + a int, + b int, + c int, + x int GENERATED ALWAYS AS (b * 2) +); + +ALTER TABLE gtest28a DROP COLUMN a; + +CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); + +\d gtest28* base-commit: 5953c99697621174f50aa219a3cd457212968268 --=20 2.19.1 --------------FDB6F0AD6C33236D71A9A554--