v8-0001-Generated-columns.patch
text/plain
Filename: v8-0001-Generated-columns.patch
Type: text/plain
Part: 0
Message:
Re: [HACKERS] generated columns
Patch
Format: format-patch
Series: patch v8-0001
Subject: Generated columns
| File | + | − |
|---|---|---|
| contrib/pageinspect/expected/page.out | 35 | 0 |
| contrib/pageinspect/sql/page.sql | 17 | 0 |
| contrib/postgres_fdw/expected/postgres_fdw.out | 26 | 0 |
| contrib/postgres_fdw/postgres_fdw.c | 2 | 1 |
| contrib/postgres_fdw/sql/postgres_fdw.sql | 15 | 0 |
| doc/src/sgml/catalogs.sgml | 16 | 3 |
| doc/src/sgml/ddl.sgml | 117 | 0 |
| doc/src/sgml/information_schema.sgml | 64 | 2 |
| doc/src/sgml/protocol.sgml | 2 | 2 |
| doc/src/sgml/ref/copy.sgml | 2 | 1 |
| doc/src/sgml/ref/create_foreign_table.sgml | 29 | 1 |
| doc/src/sgml/ref/create_table.sgml | 48 | 1 |
| doc/src/sgml/ref/create_trigger.sgml | 3 | 1 |
| doc/src/sgml/textsearch.sgml | 14 | 12 |
| doc/src/sgml/trigger.sgml | 20 | 0 |
| src/backend/access/common/tupdesc.c | 15 | 0 |
| src/backend/catalog/heap.c | 84 | 8 |
| src/backend/catalog/information_schema.sql | 26 | 4 |
| src/backend/commands/copy.c | 34 | 1 |
| src/backend/commands/indexcmds.c | 26 | 1 |
| src/backend/commands/tablecmds.c | 205 | 24 |
| src/backend/commands/trigger.c | 62 | 6 |
| src/backend/commands/typecmds.c | 4 | 2 |
| src/backend/executor/execMain.c | 12 | 3 |
| src/backend/executor/execReplication.c | 17 | 0 |
| src/backend/executor/nodeModifyTable.c | 121 | 0 |
| src/backend/nodes/copyfuncs.c | 4 | 0 |
| src/backend/nodes/equalfuncs.c | 4 | 0 |
| src/backend/nodes/outfuncs.c | 11 | 0 |
| src/backend/nodes/readfuncs.c | 2 | 0 |
| src/backend/optimizer/plan/createplan.c | 5 | 3 |
| src/backend/optimizer/util/inherit.c | 6 | 0 |
| src/backend/optimizer/util/plancat.c | 19 | 0 |
| src/backend/parser/analyze.c | 33 | 0 |
| src/backend/parser/gram.y | 23 | 3 |
| src/backend/parser/parse_agg.c | 11 | 0 |
| src/backend/parser/parse_clause.c | 4 | 0 |
| src/backend/parser/parse_expr.c | 5 | 0 |
| src/backend/parser/parse_func.c | 3 | 0 |
| src/backend/parser/parse_relation.c | 24 | 0 |
| src/backend/parser/parse_utilcmd.c | 111 | 4 |
| src/backend/replication/libpqwalreceiver/libpqwalreceiver.c | 13 | 6 |
| src/backend/replication/logical/proto.c | 4 | 5 |
| src/backend/replication/logical/relation.c | 1 | 1 |
| src/backend/replication/logical/tablesync.c | 4 | 2 |
| src/backend/replication/logical/worker.c | 2 | 4 |
| src/backend/replication/pgoutput/pgoutput.c | 1 | 1 |
| src/backend/replication/walreceiver.c | 1 | 3 |
| src/backend/rewrite/rewriteHandler.c | 190 | 7 |
| src/backend/utils/cache/lsyscache.c | 33 | 0 |
| src/backend/utils/cache/partcache.c | 3 | 0 |
| src/backend/utils/cache/relcache.c | 7 | 0 |
| src/bin/pg_dump/pg_dump.c | 36 | 7 |
| src/bin/pg_dump/pg_dump.h | 1 | 0 |
| src/bin/pg_dump/pg_dump_sort.c | 10 | 0 |
| src/bin/pg_dump/t/002_pg_dump.pl | 17 | 0 |
| src/bin/psql/describe.c | 19 | 6 |
| src/include/access/tupdesc.h | 2 | 0 |
| src/include/catalog/heap.h | 3 | 1 |
| src/include/catalog/pg_attribute.h | 6 | 0 |
| src/include/catalog/pg_class.dat | 5 | 5 |
| src/include/executor/nodeModifyTable.h | 2 | 0 |
| src/include/nodes/execnodes.h | 3 | 0 |
| src/include/nodes/parsenodes.h | 20 | 5 |
| src/include/optimizer/plancat.h | 2 | 0 |
| src/include/parser/kwlist.h | 2 | 0 |
| src/include/parser/parse_node.h | 3 | 1 |
| src/include/replication/walreceiver.h | 7 | 4 |
| src/include/rewrite/rewriteHandler.h | 1 | 0 |
| src/include/utils/lsyscache.h | 1 | 0 |
| src/pl/plperl/expected/plperl_trigger.out | 96 | 0 |
| src/pl/plperl/plperl.c | 34 | 9 |
| src/pl/plperl/sql/plperl_trigger.sql | 37 | 0 |
| src/pl/plpgsql/src/pl_exec.c | 20 | 0 |
| src/pl/plpython/expected/plpython_trigger.out | 95 | 0 |
| src/pl/plpython/plpy_cursorobject.c | 3 | 2 |
| src/pl/plpython/plpy_exec.c | 19 | 4 |
| src/pl/plpython/plpy_spi.c | 2 | 1 |
| src/pl/plpython/plpy_typeio.c | 15 | 5 |
| src/pl/plpython/plpy_typeio.h | 1 | 1 |
| src/pl/plpython/sql/plpython_trigger.sql | 38 | 0 |
| src/pl/tcl/expected/pltcl_queries.out | 89 | 0 |
| src/pl/tcl/expected/pltcl_setup.out | 11 | 0 |
| src/pl/tcl/pltcl.c | 38 | 15 |
| src/pl/tcl/sql/pltcl_queries.sql | 25 | 0 |
| src/pl/tcl/sql/pltcl_setup.sql | 13 | 0 |
| src/test/regress/expected/create_table_like.out | 46 | 0 |
| src/test/regress/expected/generated.out | 873 | 0 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/serial_schedule | 1 | 0 |
| src/test/regress/sql/create_table_like.sql | 14 | 0 |
| src/test/regress/sql/generated.sql | 517 | 0 |
| src/test/subscription/t/011_generated.pl | 65 | 0 |
From dbf72ab2c4479ef2e70088b7d4a0d67da2592bb4 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Mon, 25 Feb 2019 21:33:56 +0100
Subject: [PATCH v8] 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).
---
contrib/pageinspect/expected/page.out | 35 +
contrib/pageinspect/sql/page.sql | 17 +
.../postgres_fdw/expected/postgres_fdw.out | 26 +
contrib/postgres_fdw/postgres_fdw.c | 3 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 15 +
doc/src/sgml/catalogs.sgml | 19 +-
doc/src/sgml/ddl.sgml | 117 +++
doc/src/sgml/information_schema.sgml | 66 +-
doc/src/sgml/protocol.sgml | 4 +-
doc/src/sgml/ref/copy.sgml | 3 +-
doc/src/sgml/ref/create_foreign_table.sgml | 30 +-
doc/src/sgml/ref/create_table.sgml | 49 +-
doc/src/sgml/ref/create_trigger.sgml | 4 +-
doc/src/sgml/textsearch.sgml | 26 +-
doc/src/sgml/trigger.sgml | 20 +
src/backend/access/common/tupdesc.c | 15 +
src/backend/catalog/heap.c | 92 +-
src/backend/catalog/information_schema.sql | 30 +-
src/backend/commands/copy.c | 35 +-
src/backend/commands/indexcmds.c | 27 +-
src/backend/commands/tablecmds.c | 229 ++++-
src/backend/commands/trigger.c | 68 +-
src/backend/commands/typecmds.c | 6 +-
src/backend/executor/execMain.c | 15 +-
src/backend/executor/execReplication.c | 17 +
src/backend/executor/nodeModifyTable.c | 121 +++
src/backend/nodes/copyfuncs.c | 4 +
src/backend/nodes/equalfuncs.c | 4 +
src/backend/nodes/outfuncs.c | 11 +
src/backend/nodes/readfuncs.c | 2 +
src/backend/optimizer/plan/createplan.c | 8 +-
src/backend/optimizer/util/inherit.c | 6 +
src/backend/optimizer/util/plancat.c | 19 +
src/backend/parser/analyze.c | 33 +
src/backend/parser/gram.y | 26 +-
src/backend/parser/parse_agg.c | 11 +
src/backend/parser/parse_clause.c | 4 +
src/backend/parser/parse_expr.c | 5 +
src/backend/parser/parse_func.c | 3 +
src/backend/parser/parse_relation.c | 24 +
src/backend/parser/parse_utilcmd.c | 115 ++-
.../libpqwalreceiver/libpqwalreceiver.c | 19 +-
src/backend/replication/logical/proto.c | 9 +-
src/backend/replication/logical/relation.c | 2 +-
src/backend/replication/logical/tablesync.c | 6 +-
src/backend/replication/logical/worker.c | 6 +-
src/backend/replication/pgoutput/pgoutput.c | 2 +-
src/backend/replication/walreceiver.c | 4 +-
src/backend/rewrite/rewriteHandler.c | 197 +++-
src/backend/utils/cache/lsyscache.c | 33 +
src/backend/utils/cache/partcache.c | 3 +
src/backend/utils/cache/relcache.c | 7 +
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 | 2 +
src/include/catalog/heap.h | 4 +-
src/include/catalog/pg_attribute.h | 6 +
src/include/catalog/pg_class.dat | 10 +-
src/include/executor/nodeModifyTable.h | 2 +
src/include/nodes/execnodes.h | 3 +
src/include/nodes/parsenodes.h | 25 +-
src/include/optimizer/plancat.h | 2 +
src/include/parser/kwlist.h | 2 +
src/include/parser/parse_node.h | 4 +-
src/include/replication/walreceiver.h | 11 +-
src/include/rewrite/rewriteHandler.h | 1 +
src/include/utils/lsyscache.h | 1 +
src/pl/plperl/expected/plperl_trigger.out | 96 ++
src/pl/plperl/plperl.c | 43 +-
src/pl/plperl/sql/plperl_trigger.sql | 37 +
src/pl/plpgsql/src/pl_exec.c | 20 +
src/pl/plpython/expected/plpython_trigger.out | 95 ++
src/pl/plpython/plpy_cursorobject.c | 5 +-
src/pl/plpython/plpy_exec.c | 23 +-
src/pl/plpython/plpy_spi.c | 3 +-
src/pl/plpython/plpy_typeio.c | 20 +-
src/pl/plpython/plpy_typeio.h | 2 +-
src/pl/plpython/sql/plpython_trigger.sql | 38 +
src/pl/tcl/expected/pltcl_queries.out | 89 ++
src/pl/tcl/expected/pltcl_setup.out | 11 +
src/pl/tcl/pltcl.c | 53 +-
src/pl/tcl/sql/pltcl_queries.sql | 25 +
src/pl/tcl/sql/pltcl_setup.sql | 13 +
.../regress/expected/create_table_like.out | 46 +
src/test/regress/expected/generated.out | 873 ++++++++++++++++++
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 | 517 +++++++++++
src/test/subscription/t/011_generated.pl | 65 ++
93 files changed, 3733 insertions(+), 179 deletions(-)
create mode 100644 src/test/regress/expected/generated.out
create mode 100644 src/test/regress/sql/generated.sql
create mode 100644 src/test/subscription/t/011_generated.pl
diff --git a/contrib/pageinspect/expected/page.out b/contrib/pageinspect/expected/page.out
index 74454801f5..c4e83f1d65 100644
--- a/contrib/pageinspect/expected/page.out
+++ b/contrib/pageinspect/expected/page.out
@@ -101,3 +101,38 @@ select tuple_data_split('test8'::regclass, t_data, t_infomask, t_infomask2, t_bi
(1 row)
drop table test8;
+-- check storage of generated columns
+-- virtual
+create table test9v (a int not null, b int generated always as (a * 2) virtual);
+insert into test9v values (1);
+select t_infomask, t_bits, t_data from heap_page_items(get_raw_page('test9v', 0));
+ t_infomask | t_bits | t_data
+------------+----------+------------
+ 2049 | 10000000 | \x01000000
+(1 row)
+
+select tuple_data_split('test9v'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ from heap_page_items(get_raw_page('test9v', 0));
+ tuple_data_split
+----------------------
+ {"\\x01000000",NULL}
+(1 row)
+
+drop table test9v;
+-- stored
+create table test9s (a int not null, b int generated always as (a * 2) stored);
+insert into test9s values (1);
+select t_infomask, t_bits, t_data from heap_page_items(get_raw_page('test9s', 0));
+ t_infomask | t_bits | t_data
+------------+--------+--------------------
+ 2048 | | \x0100000002000000
+(1 row)
+
+select tuple_data_split('test9s'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ from heap_page_items(get_raw_page('test9s', 0));
+ tuple_data_split
+-------------------------------
+ {"\\x01000000","\\x02000000"}
+(1 row)
+
+drop table test9s;
diff --git a/contrib/pageinspect/sql/page.sql b/contrib/pageinspect/sql/page.sql
index e88598a48e..26698025bd 100644
--- a/contrib/pageinspect/sql/page.sql
+++ b/contrib/pageinspect/sql/page.sql
@@ -60,3 +60,20 @@ CREATE TABLE test1 (a int, b int);
select tuple_data_split('test8'::regclass, t_data, t_infomask, t_infomask2, t_bits)
from heap_page_items(get_raw_page('test8', 0));
drop table test8;
+
+-- check storage of generated columns
+-- virtual
+create table test9v (a int not null, b int generated always as (a * 2) virtual);
+insert into test9v values (1);
+select t_infomask, t_bits, t_data from heap_page_items(get_raw_page('test9v', 0));
+select tuple_data_split('test9v'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ from heap_page_items(get_raw_page('test9v', 0));
+drop table test9v;
+
+-- stored
+create table test9s (a int not null, b int generated always as (a * 2) stored);
+insert into test9s values (1);
+select t_infomask, t_bits, t_data from heap_page_items(get_raw_page('test9s', 0));
+select tuple_data_split('test9s'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ from heap_page_items(get_raw_page('test9s', 0));
+drop table test9s;
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 42108bd3d4..0a609df9ba 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -6431,6 +6431,32 @@ select * from rem1;
11 | bye remote
(4 rows)
+-- ===================================================================
+-- test generated columns
+-- ===================================================================
+create table gloc1 (a int, b int, c int);
+alter table gloc1 set (autovacuum_enabled = 'false');
+create foreign table grem1 (
+ a int,
+ b int generated always as (a * 2) virtual,
+ c int generated always as (a * 3) stored)
+ server loopback options(table_name 'gloc1');
+insert into grem1 (a) values (1), (2);
+update grem1 set a = 22 where a = 2;
+select * from gloc1;
+ a | b | c
+----+---+----
+ 1 | | 3
+ 22 | | 66
+(2 rows)
+
+select * from grem1;
+ a | b | c
+----+----+----
+ 1 | 2 | 3
+ 22 | 44 | 66
+(2 rows)
+
-- ===================================================================
-- test local triggers
-- ===================================================================
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 6b96e7de0a..9fb34592d2 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -1644,9 +1644,10 @@ postgresPlanForeignModify(PlannerInfo *root,
else if (operation == CMD_UPDATE)
{
int col;
+ Bitmapset *allUpdatedCols = bms_union(rte->updatedCols, rte->extraUpdatedCols);
col = -1;
- while ((col = bms_next_member(rte->updatedCols, col)) >= 0)
+ while ((col = bms_next_member(allUpdatedCols, col)) >= 0)
{
/* bit numbers are offset by FirstLowInvalidHeapAttributeNumber */
AttrNumber attno = col + FirstLowInvalidHeapAttributeNumber;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index eb9d1ad59d..5f6d892eed 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1363,6 +1363,21 @@ CREATE VIEW rw_view AS SELECT * FROM parent_tbl
select * from loc1;
select * from rem1;
+-- ===================================================================
+-- test generated columns
+-- ===================================================================
+create table gloc1 (a int, b int, c int);
+alter table gloc1 set (autovacuum_enabled = 'false');
+create foreign table grem1 (
+ a int,
+ b int generated always as (a * 2) virtual,
+ c int generated always as (a * 3) stored)
+ server loopback options(table_name 'gloc1');
+insert into grem1 (a) values (1), (2);
+update grem1 set a = 22 where a = 2;
+select * from gloc1;
+select * from grem1;
+
-- ===================================================================
-- test local triggers
-- ===================================================================
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 0fd792ff1a..ff9cb7ef41 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1129,9 +1129,11 @@ <title><structname>pg_attribute</structname> Columns</title>
<entry><type>bool</type></entry>
<entry></entry>
<entry>
- This column has a default value, in which case there will be a
- corresponding entry in the <structname>pg_attrdef</structname>
- 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
+ <structname>pg_attrdef</structname> catalog that actually defines the
+ expression. (Check <structfield>attgenerated</structfield> to
+ determine whether this is a default or a generation expression.)
</entry>
</row>
@@ -1159,6 +1161,17 @@ <title><structname>pg_attribute</structname> Columns</title>
</entry>
</row>
+ <row>
+ <entry><structfield>attgenerated</structfield></entry>
+ <entry><type>char</type></entry>
+ <entry></entry>
+ <entry>
+ If a zero byte (<literal>''</literal>), then not a generated column.
+ Otherwise, <literal>s</literal> = stored, <literal>v</literal> =
+ virtual.
+ </entry>
+ </row>
+
<row>
<entry><structfield>attisdropped</structfield></entry>
<entry><type>bool</type></entry>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 8314fce78f..5f153531cb 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -233,6 +233,123 @@ <title>Default Values</title>
</para>
</sect1>
+ <sect1 id="ddl-generated-columns">
+ <title>Generated Columns</title>
+
+ <indexterm zone="ddl-generated-columns">
+ <primary>generated column</primary>
+ </indexterm>
+
+ <para>
+ A generated column is a special column that is always computed from other
+ columns. Thus, it is for columns what a view is for tables. There are two
+ kinds of generated columns: virtual and stored. A virtual generated column
+ occupies no storage and is computed when it is read. A stored generated
+ column is computed when it is written (inserted or updated) and occupies
+ storage as if it were a normal column. Thus, a virtual generated column is
+ similar to a view and a stored generated column is similar to a
+ materialized view (except that it is always updated automatically).
+ </para>
+
+ <para>
+ To create a generated column, use the <literal>GENERATED ALWAYS
+ AS</literal> clause in <command>CREATE TABLE</command>, for example:
+<programlisting>
+CREATE TABLE people (
+ ...,
+ height_cm numeric,
+ height_in numeric <emphasis>GENERATED ALWAYS AS (height_cm * 2.54)</emphasis>
+);
+</programlisting>
+ A generated column is by default of the virtual kind. Use the keywords
+ <literal>VIRTUAL</literal> or <literal>STORED</literal> to make the choice
+ explicit. See <xref linkend="sql-createtable"/> for more details.
+ </para>
+
+ <para>
+ A generated column cannot be written to directly. In
+ <command>INSERT</command> or <command>UPDATE</command> commands, a value
+ cannot be specified for a generated column, but the keyword
+ <literal>DEFAULT</literal> may be specified.
+ </para>
+
+ <para>
+ Consider the differences between a column with a default and a generated
+ column. The column default is evaluated once when the row is first
+ inserted if no other value was provided; a generated column is updated
+ whenever the row changes and cannot be overridden. A column default may
+ not refer to other columns of the table; a generation expression would
+ normally do so. A column default can use volatile functions, for example
+ <literal>random()</literal> or functions referring to the current time;
+ this is not allowed for generated columns.
+ </para>
+
+ <para>
+ Several restrictions apply to the definition of generated columns and
+ tables involving generated columns:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ The generation expression can only use immutable functions and cannot
+ use subqueries or reference anything other than the current row in any
+ way.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ A generation expression cannot reference another generated column.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ A generation expression cannot reference a system column, except
+ <varname>tableoid</varname>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ A generated column cannot have a column default or an identity definition.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ A generated column cannot be part of a partition key.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Foreign tables can have generated columns. See <xref
+ linkend="sql-createforeigntable"/> for details.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Additional considerations apply to the use of generated columns.
+ <itemizedlist>
+ <listitem>
+ <para>
+ Generated columns maintain access privileges separately from their
+ underlying base columns. So, it is possible to arrange it so that a
+ particular role can read from a generated column but not from the
+ underlying base columns.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Generated columns are, conceptually, updated after
+ <literal>BEFORE</literal> triggers have run. Therefore, changes made to
+ base columns in a <literal>BEFORE</literal> trigger will be reflected in
+ generated columns. But conversely, it is not allowed to access
+ generated columns in <literal>BEFORE</literal> triggers.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect1>
+
<sect1 id="ddl-constraints">
<title>Constraints</title>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index b13700da92..1321ade44a 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -952,6 +952,62 @@ <title><literal>collation_character_set_applicability</literal> Columns</title>
</table>
</sect1>
+ <sect1 id="infoschema-column-column-usage">
+ <title><literal>column_column_usage</literal></title>
+
+ <para>
+ The view <literal>column_column_usage</literal> identifies all generated
+ columns that depend on another base column in the same table. Only tables
+ owned by a currently enabled role are included.
+ </para>
+
+ <table>
+ <title><literal>column_column_usage</literal> Columns</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>table_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the database containing the table (always the current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>table_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the schema containing the table</entry>
+ </row>
+
+ <row>
+ <entry><literal>table_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the table</entry>
+ </row>
+
+ <row>
+ <entry><literal>column_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the base column that a generated column depends on</entry>
+ </row>
+
+ <row>
+ <entry><literal>dependent_column</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the generated column</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="infoschema-column-domain-usage">
<title><literal>column_domain_usage</literal></title>
@@ -1648,13 +1704,19 @@ <title><literal>columns</literal> Columns</title>
<row>
<entry><literal>is_generated</literal></entry>
<entry><type>character_data</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</productname></entry>
+ <entry>
+ If the column is a generated column, then <literal>ALWAYS</literal>,
+ else <literal>NEVER</literal>.
+ </entry>
</row>
<row>
<entry><literal>generation_expression</literal></entry>
<entry><type>character_data</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</productname></entry>
+ <entry>
+ If the column is a generated column, then the generation expression,
+ else null.
+ </entry>
</row>
<row>
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index d66b860cbd..a0e1f78bfc 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -6450,7 +6450,7 @@ <title>Logical Replication Message Formats</title>
</listitem>
</varlistentry>
</variablelist>
- Next, the following message part appears for each column:
+ Next, the following message part appears for each column (except generated columns):
<variablelist>
<varlistentry>
<term>
@@ -6875,7 +6875,7 @@ <title>Logical Replication Message Formats</title>
</listitem>
</varlistentry>
</variablelist>
- Next, one of the following submessages appears for each column:
+ Next, one of the following submessages appears for each column (except generated columns):
<variablelist>
<varlistentry>
<term>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 254d3ab8eb..5e2992ddac 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -103,7 +103,8 @@ <title>Parameters</title>
<listitem>
<para>
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.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 37a45b26db..84c48f2965 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -42,7 +42,8 @@
{ NOT NULL |
NULL |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- DEFAULT <replaceable>default_expr</replaceable> }
+ DEFAULT <replaceable>default_expr</replaceable> |
+ GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ VIRTUAL | STORED ] }
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
@@ -246,6 +247,33 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ VIRTUAL | STORED ]</literal><indexterm><primary>generated column</primary></indexterm></term>
+ <listitem>
+ <para>
+ This clause creates the column as a <firstterm>generated
+ column</firstterm>. The column cannot be written to, and when read it
+ will be computed from the specified expression.
+ </para>
+
+ <para>
+ When <literal>VIRTUAL</literal> is specified, the column will be
+ computed when it is read. (The foreign-data wrapper will see it as a
+ null value in new rows and may choose to store it as a null value or
+ ignore it altogether.) When <literal>STORED</literal> is specified, the
+ column will be computed on write. (The computed value will be presented
+ to the foreign-data wrapper for storage and must be returned on
+ reading.) <literal>VIRTUAL</literal> is the default.
+ </para>
+
+ <para>
+ The generation expression can refer to other columns in the table, but
+ not other generated columns. Any functions and operators used must be
+ immutable. References to other tables are not allowed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">server_name</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 22dbc07b23..f188bcb7f9 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -62,6 +62,7 @@
NULL |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
+ GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ VIRTUAL | STORED ] |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
@@ -83,7 +84,7 @@
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -627,6 +628,17 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>INCLUDING GENERATED</literal></term>
+ <listitem>
+ <para>
+ Any generation expressions as well as the virtual/stored choice of
+ copied column definitions will be copied. By default, new columns
+ will be regular base columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>INCLUDING IDENTITY</literal></term>
<listitem>
@@ -797,6 +809,31 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ VIRTUAL | STORED ]</literal><indexterm><primary>generated column</primary></indexterm></term>
+ <listitem>
+ <para>
+ This clause creates the column as a <firstterm>generated
+ column</firstterm>. The column cannot be written to, and when read it
+ will be computed from the specified expression.
+ </para>
+
+ <para>
+ When <literal>VIRTUAL</literal> is specified, the column will be
+ computed when it is read, and it will not occupy any storage.
+ When <literal>STORED</literal> is specified, the column will be computed
+ on write and will be stored on disk. <literal>VIRTUAL</literal> is the
+ default.
+ </para>
+
+ <para>
+ The generation expression can refer to other columns in the table, but
+ not other generated columns. Any functions and operators used must be
+ immutable. References to other tables are not allowed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]</literal></term>
<listitem>
@@ -2028,6 +2065,16 @@ <title>Multiple Identity Columns</title>
</para>
</refsect2>
+ <refsect2>
+ <title>Generated Columns</title>
+
+ <para>
+ The options <literal>VIRTUAL</literal> and <literal>STORED</literal> are
+ not standard but are also used by other SQL implementations. The SQL
+ standard does not specify the storage of generated columns.
+ </para>
+ </refsect2>
+
<refsect2>
<title><literal>LIKE</literal> Clause</title>
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 6514ffc6ae..6456105de6 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -261,7 +261,9 @@ <title>Parameters</title>
UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</replaceable> ... ]
</synopsis>
The trigger will only fire if at least one of the listed columns
- is mentioned as a target of the <command>UPDATE</command> command.
+ is mentioned as a target of the <command>UPDATE</command> command
+ or if one of the listed columns is a generated column that depends on a
+ column that is the target of the <command>UPDATE</command>.
</para>
<para>
diff --git a/doc/src/sgml/textsearch.sgml b/doc/src/sgml/textsearch.sgml
index ecebade767..64dba886bf 100644
--- a/doc/src/sgml/textsearch.sgml
+++ b/doc/src/sgml/textsearch.sgml
@@ -620,15 +620,17 @@ <title>Creating Indexes</title>
<para>
Another approach is to create a separate <type>tsvector</type> column
- to hold the output of <function>to_tsvector</function>. This example is a
+ to hold the output of <function>to_tsvector</function>. To keep this
+ column automatically up to date with its source data, use a stored
+ generated column. This example is a
concatenation of <literal>title</literal> and <literal>body</literal>,
using <function>coalesce</function> to ensure that one field will still be
indexed when the other is <literal>NULL</literal>:
<programlisting>
-ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
-UPDATE pgweb SET textsearchable_index_col =
- to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
+ALTER TABLE pgweb
+ ADD COLUMN textsearchable_index_col tsvector
+ GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
</programlisting>
Then we create a <acronym>GIN</acronym> index to speed up the search:
@@ -648,14 +650,6 @@ <title>Creating Indexes</title>
</programlisting>
</para>
- <para>
- When using a separate column to store the <type>tsvector</type>
- representation,
- it is necessary to create a trigger to keep the <type>tsvector</type>
- column current anytime <literal>title</literal> or <literal>body</literal> changes.
- <xref linkend="textsearch-update-triggers"/> explains how to do that.
- </para>
-
<para>
One advantage of the separate-column approach over an expression index
is that it is not necessary to explicitly specify the text search
@@ -1857,6 +1851,14 @@ <title>Triggers for Automatic Updates</title>
<secondary>for updating a derived tsvector column</secondary>
</indexterm>
+ <note>
+ <para>
+ The method described in this section has been obsoleted by the use of
+ stored generated columns, as described in <xref
+ linkend="textsearch-tables-index"/>.
+ </para>
+ </note>
+
<para>
When using a separate column to store the <type>tsvector</type> representation
of your documents, it is necessary to create a trigger to update the
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index be9c228448..384845ff76 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -243,6 +243,26 @@ <title>Overview of Trigger Behavior</title>
operation, and so they can return <symbol>NULL</symbol>.
</para>
+ <para>
+ Some considerations apply for generated
+ columns.<indexterm><primary>generated column</primary><secondary>in
+ triggers</secondary></indexterm> Virtual generated columns are never
+ computed when triggers fire; they will always appear as null inside a
+ trigger function. Stored generated columns are computed after
+ <literal>BEFORE</literal> triggers and before <literal>AFTER</literal>
+ triggers. Therefore, the generated value can be inspected in
+ <literal>AFTER</literal> triggers. In <literal>BEFORE</literal> triggers,
+ the <literal>OLD</literal> row contains the old generated value, as one
+ would expect, but the <literal>NEW</literal> row does not yet contain the
+ new generated value and should not be accessed. In the C language
+ interface, the content of the column is undefined at this point; a
+ higher-level programming language should prevent access to a stored
+ generated column in the <literal>NEW</literal> row in a
+ <literal>BEFORE</literal> trigger. Changes to the value of a generated
+ column in a <literal>BEFORE</literal> trigger are ignored and will be
+ overwritten.
+ </para>
+
<para>
If more than one trigger is defined for the same event on the same
relation, the triggers will be fired in alphabetical order by
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index 47e80ae186..c25e65a45e 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -132,6 +132,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
}
/* We can copy the tuple type identification, too */
@@ -166,6 +167,8 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
TupleConstr *cpy = (TupleConstr *) palloc0(sizeof(TupleConstr));
cpy->has_not_null = constr->has_not_null;
+ cpy->has_generated_stored = constr->has_generated_stored;
+ cpy->has_generated_virtual = constr->has_generated_virtual;
if ((cpy->num_defval = constr->num_defval) > 0)
{
@@ -248,6 +251,7 @@ TupleDescCopy(TupleDesc dst, TupleDesc src)
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
}
dst->constr = NULL;
@@ -301,6 +305,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
dstAtt->atthasdef = false;
dstAtt->atthasmissing = false;
dstAtt->attidentity = '\0';
+ dstAtt->attgenerated = '\0';
}
/*
@@ -457,6 +462,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (attr1->attidentity != attr2->attidentity)
return false;
+ if (attr1->attgenerated != attr2->attgenerated)
+ return false;
if (attr1->attisdropped != attr2->attisdropped)
return false;
if (attr1->attislocal != attr2->attislocal)
@@ -477,6 +484,10 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (constr1->has_not_null != constr2->has_not_null)
return false;
+ if (constr1->has_generated_stored != constr2->has_generated_stored)
+ return false;
+ if (constr1->has_generated_virtual != constr2->has_generated_virtual)
+ return false;
n = constr1->num_defval;
if (n != (int) constr2->num_defval)
return false;
@@ -639,6 +650,7 @@ TupleDescInitEntry(TupleDesc desc,
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
@@ -698,6 +710,7 @@ TupleDescInitBuiltinEntry(TupleDesc desc,
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
@@ -854,6 +867,8 @@ BuildDescForRelation(List *schema)
TupleConstr *constr = (TupleConstr *) palloc0(sizeof(TupleConstr));
constr->has_not_null = true;
+ constr->has_generated_stored = false;
+ constr->has_generated_virtual = false;
constr->defval = NULL;
constr->missing = NULL;
constr->num_defval = 0;
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 7dba4e50dd..68ed9c3769 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -69,6 +69,7 @@
#include "parser/parse_collate.h"
#include "parser/parse_expr.h"
#include "parser/parse_relation.h"
+#include "parser/parsetree.h"
#include "partitioning/partdesc.h"
#include "storage/lmgr.h"
#include "storage/predicate.h"
@@ -684,6 +685,7 @@ InsertPgAttributeTuple(Relation pg_attribute_rel,
values[Anum_pg_attribute_atthasdef - 1] = BoolGetDatum(new_attribute->atthasdef);
values[Anum_pg_attribute_atthasmissing - 1] = BoolGetDatum(new_attribute->atthasmissing);
values[Anum_pg_attribute_attidentity - 1] = CharGetDatum(new_attribute->attidentity);
+ values[Anum_pg_attribute_attgenerated - 1] = CharGetDatum(new_attribute->attgenerated);
values[Anum_pg_attribute_attisdropped - 1] = BoolGetDatum(new_attribute->attisdropped);
values[Anum_pg_attribute_attislocal - 1] = BoolGetDatum(new_attribute->attislocal);
values[Anum_pg_attribute_attinhcount - 1] = Int32GetDatum(new_attribute->attinhcount);
@@ -2131,6 +2133,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
Relation attrrel;
HeapTuple atttup;
Form_pg_attribute attStruct;
+ char attgenerated;
Oid attrdefOid;
ObjectAddress colobject,
defobject;
@@ -2178,6 +2181,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
elog(ERROR, "cache lookup failed for attribute %d of relation %u",
attnum, RelationGetRelid(rel));
attStruct = (Form_pg_attribute) GETSTRUCT(atttup);
+ attgenerated = attStruct->attgenerated;
if (!attStruct->atthasdef)
{
Form_pg_attribute defAttStruct;
@@ -2198,7 +2202,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
valuesAtt[Anum_pg_attribute_atthasdef - 1] = true;
replacesAtt[Anum_pg_attribute_atthasdef - 1] = true;
- if (add_column_mode)
+ if (add_column_mode && !attgenerated)
{
expr2 = expression_planner(expr2);
estate = CreateExecutorState();
@@ -2260,7 +2264,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);
+ }
/*
* Post creation hook for attribute defaults.
@@ -2518,12 +2541,14 @@ AddRelationNewConstraints(Relation rel,
expr = cookDefault(pstate, colDef->raw_default,
atp->atttypid, atp->atttypmod,
- NameStr(atp->attname));
+ NameStr(atp->attname),
+ atp->attgenerated);
/*
* If the expression is just a NULL constant, we do not bother to make
* an explicit pg_attrdef entry, since the default behavior is
- * equivalent.
+ * equivalent. This applies to column defaults, but not for generation
+ * expressions.
*
* Note a nonobvious property of this test: if the column is of a
* domain type, what we'll get is not a bare null Const but a
@@ -2532,7 +2557,9 @@ AddRelationNewConstraints(Relation rel,
* override any default that the domain might have.
*/
if (expr == NULL ||
- (IsA(expr, Const) &&((Const *) expr)->constisnull))
+ (!colDef->generated &&
+ IsA(expr, Const) &&
+ castNode(Const, expr)->constisnull))
continue;
/* If the DEFAULT is volatile we cannot use a missing value */
@@ -2889,6 +2916,46 @@ SetRelationNumChecks(Relation rel, int numchecks)
table_close(relrel, RowExclusiveLock);
}
+/*
+ * Check for references to generated columns
+ */
+static bool
+check_nested_generated_walker(Node *node, void *context)
+{
+ ParseState *pstate = context;
+
+ if (node == NULL)
+ return false;
+ else if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+ Oid relid;
+ AttrNumber attnum;
+
+ relid = rt_fetch(var->varno, pstate->p_rtable)->relid;
+ attnum = var->varattno;
+
+ if (relid && attnum && get_attgenerated(relid, attnum))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot use generated column \"%s\" in column generation expression",
+ get_attname(relid, attnum, false)),
+ errdetail("A generated column cannot reference another generated column."),
+ 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.
@@ -2906,7 +2973,8 @@ cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
- const char *attname)
+ const char *attname,
+ char attgenerated)
{
Node *expr;
@@ -2915,17 +2983,25 @@ cookDefault(ParseState *pstate,
/*
* Transform raw parsetree to executable expression.
*/
- expr = transformExpr(pstate, raw_default, EXPR_KIND_COLUMN_DEFAULT);
+ expr = transformExpr(pstate, raw_default, attgenerated ? EXPR_KIND_GENERATED_COLUMN : EXPR_KIND_COLUMN_DEFAULT);
/*
* Make sure default expr does not refer to any vars (we need this check
* 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")));
+ if (attgenerated)
+ check_nested_generated(pstate, expr);
+
+ if (attgenerated && contain_mutable_functions(expr))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("generation expression is not immutable")));
+
/*
* 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/catalog/information_schema.sql
index 94e482596f..16677e78d6 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
*/
--- 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 = c.relnamespace
+ AND c.oid = ac.attrelid
+ AND c.oid = ad.attrelid
+ AND d.classid = 'pg_catalog.pg_class'::regclass
+ AND d.refclassid = 'pg_catalog.pg_class'::regclass
+ AND d.objid = d.refobjid
+ AND c.oid = d.objid
+ AND d.objsubid = ad.attnum
+ AND d.refobjsubid = ac.attnum
+ AND ad.attgenerated <> ''
+ AND pg_has_role(c.relowner, 'USAGE');
+
+GRANT SELECT ON column_column_usage TO PUBLIC;
/*
@@ -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 = '' THEN pg_get_expr(ad.adbin, ad.adrelid) END AS character_data) AS column_default,
CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) 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_or_no) AS identity_cycle,
- 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,
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 dbb06397e6..efcdc31da9 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"
@@ -2924,6 +2925,25 @@ CopyFrom(CopyState cstate)
}
else
{
+ /*
+ * Normal case: insert tuple into table
+ */
+
+ /*
+ * Compute stored generated columns
+ *
+ * Switch memory context so that the new tuple is in the same
+ * context as the old one.
+ */
+ if (resultRelInfo->ri_RelationDesc->rd_att->constr &&
+ resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated_stored)
+ {
+ ExecComputeStoredGenerated(estate, slot);
+ MemoryContextSwitchTo(batchcontext);
+ tuple = ExecCopySlotHeapTuple(slot);
+ MemoryContextSwitchTo(oldcontext);
+ }
+
/*
* If the target is a plain table, check the constraints of
* the tuple.
@@ -3271,7 +3291,7 @@ BeginCopyFrom(ParseState *pstate,
fmgr_info(in_func_oid, &in_functions[attnum - 1]);
/* 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 */
@@ -4876,6 +4896,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 COPY
+ * 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 *
@@ -4893,6 +4918,8 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, List *attnamelist)
{
if (TupleDescAttr(tupDesc, i)->attisdropped)
continue;
+ if (TupleDescAttr(tupDesc, i)->attgenerated)
+ continue;
attnums = lappend_int(attnums, i + 1);
}
}
@@ -4917,6 +4944,12 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, List *attnamelist)
continue;
if (namestrcmp(&(att->attname), name) == 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 = att->attnum;
break;
}
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 5dcedc337a..196d9896d3 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -747,6 +747,9 @@ DefineIndex(Oid relationId,
/*
* We disallow indexes on system columns. They would not necessarily get
* updated correctly, and they don't seem useful anyway.
+ *
+ * Also disallow virtual generated columns in indexes (use expression
+ * index instead).
*/
for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
{
@@ -756,10 +759,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 == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("index creation on virtual generated columns is not supported")));
}
/*
- * 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)
{
@@ -776,6 +785,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 = -1;
+ while ((i = bms_next_member(indexattrs, i)) >= 0)
+ {
+ AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;
+
+ if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("index creation on virtual generated columns is not supported")));
+ }
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 35bdb0e0c6..133deb446e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -752,6 +752,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
rawEnt->attnum = attnum;
rawEnt->raw_default = colDef->raw_default;
rawEnt->missingMode = false;
+ rawEnt->generated = colDef->generated;
rawDefaults = lappend(rawDefaults, rawEnt);
attr->atthasdef = true;
}
@@ -775,6 +776,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (colDef->identity)
attr->attidentity = colDef->identity;
+
+ if (colDef->generated)
+ attr->attgenerated = colDef->generated;
}
/*
@@ -818,6 +822,27 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
*/
rel = relation_open(relationId, AccessExclusiveLock);
+ /*
+ * Now add any newly specified column default and generation expressions
+ * 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)
{
@@ -1019,16 +1044,12 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
/*
- * Now add any newly specified column default values and CHECK constraints
- * 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 partitioning
+ * is set up.
*/
- if (rawDefaults || stmt->constraints)
- AddRelationNewConstraints(rel, rawDefaults, stmt->constraints,
+ if (stmt->constraints)
+ AddRelationNewConstraints(rel, NIL, stmt->constraints,
true, true, false, queryString);
ObjectAddressSet(address, RelationRelationId, relationId);
@@ -2187,6 +2208,13 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
def->is_not_null |= attribute->attnotnull;
/* Default and other constraints are handled below */
newattno[parent_attno - 1] = exist_attno;
+
+ /* Check for GENERATED conflicts */
+ if (def->generated != attribute->attgenerated)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("inherited column \"%s\" has a generation conflict",
+ attributeName)));
}
else
{
@@ -2204,6 +2232,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
def->storage = attribute->attstorage;
def->raw_default = NULL;
def->cooked_default = NULL;
+ def->generated = attribute->attgenerated;
def->collClause = NULL;
def->collOid = attribute->attcollation;
def->constraints = NIL;
@@ -4643,7 +4672,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
{
case CONSTR_CHECK:
needscan = true;
- con->qualstate = ExecPrepareExpr((Expr *) con->qual, estate);
+ con->qualstate = ExecPrepareExpr((Expr *) expand_generated_columns_in_expr(con->qual,
+ newrel ? newrel : oldrel),
+ estate);
break;
case CONSTR_FOREIGN:
/* Nothing to do here */
@@ -5524,6 +5555,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
attribute.atthasdef = false;
attribute.atthasmissing = false;
attribute.attidentity = colDef->identity;
+ attribute.attgenerated = colDef->generated;
attribute.attisdropped = false;
attribute.attislocal = colDef->is_local;
attribute.attinhcount = colDef->inhcount;
@@ -5569,7 +5601,9 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
* DEFAULT value outside of the heap. This may be disabled inside
* AddRelationNewConstraints if the optimization cannot be applied.
*/
- rawEnt->missingMode = true;
+ rawEnt->missingMode = (!colDef->generated);
+
+ rawEnt->generated = colDef->generated;
/*
* This function is intended for CREATE TABLE, so it processes a
@@ -5894,6 +5928,18 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
errmsg("cannot alter system column \"%s\"",
colName)));
+ /*
+ * Virtual generated columns don't use the attnotnull field but use a full
+ * CHECK constraint instead. We could implement here that it finds that
+ * CHECK constraint and drops it, which is kind of what the SQL standard
+ * would require anyway, but that would be quite a bit more work.
+ */
+ if (attTup->attgenerated == 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),
@@ -6042,6 +6088,17 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
errmsg("cannot alter system column \"%s\"",
colName)));
+ /*
+ * XXX We might want to convert this to a CHECK constraint like we do in
+ * transformColumnDefinition().
+ */
+ if (((Form_pg_attribute) GETSTRUCT(tuple))->attgenerated == ATTRIBUTE_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
*/
@@ -6105,6 +6162,12 @@ ATExecColumnDefault(Relation rel, const char *colName,
colName, RelationGetRelationName(rel)),
newDefault ? 0 : errhint("Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.")));
+ 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
@@ -6126,6 +6189,7 @@ ATExecColumnDefault(Relation rel, const char *colName,
rawEnt->attnum = attnum;
rawEnt->raw_default = newDefault;
rawEnt->missingMode = false;
+ rawEnt->generated = '\0';
/*
* This function is intended for CREATE TABLE, so it processes a
@@ -7387,6 +7451,45 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
*/
checkFkeyPermissions(pkrel, pkattnum, numpks);
+ /*
+ * Check some things for generated columns.
+ */
+ for (i = 0; i < numfks; i++)
+ {
+ char attgenerated = TupleDescAttr(RelationGetDescr(rel), fkattnum[i] - 1)->attgenerated;
+
+ if (attgenerated)
+ {
+ /*
+ * Check restrictions on UPDATE/DELETE actions, per SQL standard
+ */
+ if (fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETNULL ||
+ fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETDEFAULT ||
+ fkconstraint->fk_upd_action == FKCONSTR_ACTION_CASCADE)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid ON UPDATE action for foreign key constraint containing generated column")));
+ if (fkconstraint->fk_del_action == FKCONSTR_ACTION_SETNULL ||
+ fkconstraint->fk_del_action == FKCONSTR_ACTION_SETDEFAULT)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid ON DELETE action for foreign key constraint containing generated column")));
+ }
+
+ /*
+ * FKs on virtual columns are not supported. This would require
+ * various additional support in ri_triggers.c, including special
+ * handling in ri_NullCheck(), ri_KeysEqual(),
+ * RI_FKey_fk_upd_check_required() (since all virtual columns appear
+ * as NULL there). Also not really practical as long as you can't
+ * index virtual columns.
+ */
+ if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("foreign key constraints on virtual generated columns are not supported")));
+ }
+
/*
* Look up the equality operators to use in the constraint.
*
@@ -8773,7 +8876,7 @@ validateCheckConstraint(Relation rel, HeapTuple constrtup)
EState *estate;
Datum val;
char *conbin;
- Expr *origexpr;
+ Node *origexpr;
ExprState *exprstate;
TupleDesc tupdesc;
HeapScanDesc scan;
@@ -8808,8 +8911,8 @@ validateCheckConstraint(Relation rel, HeapTuple constrtup)
elog(ERROR, "null conbin for constraint %u",
constrForm->oid);
conbin = TextDatumGetCString(val);
- origexpr = (Expr *) stringToNode(conbin);
- exprstate = ExecPrepareExpr(origexpr, estate);
+ origexpr = stringToNode(conbin);
+ exprstate = ExecPrepareExpr((Expr *) expand_generated_columns_in_expr(origexpr, rel), estate);
econtext = GetPerTupleExprContext(estate);
tupdesc = RelationGetDescr(rel);
@@ -9471,8 +9574,9 @@ ATPrepAlterColumnType(List **wqueue,
list_make1_oid(rel->rd_rel->reltype),
0);
- if (tab->relkind == RELKIND_RELATION ||
- tab->relkind == RELKIND_PARTITIONED_TABLE)
+ if ((tab->relkind == RELKIND_RELATION ||
+ tab->relkind == RELKIND_PARTITIONED_TABLE) &&
+ attTup->attgenerated != ATTRIBUTE_GENERATED_VIRTUAL)
{
/*
* Set up an expression to transform the old data value to the new
@@ -9761,10 +9865,18 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
COERCE_IMPLICIT_CAST,
-1);
if (defaultexpr == 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 automatically 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 = NULL;
@@ -9840,6 +9952,21 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
*/
Assert(foundObject.objectSubId == 0);
}
+ else if (relKind == RELKIND_RELATION &&
+ foundObject.objectSubId != 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 column"),
+ errdetail("Column \"%s\" is used by generated column \"%s\".",
+ colName, get_attname(foundObject.objectId, foundObject.objectSubId, false))));
+ }
else
{
/* Not expecting any other direct dependencies... */
@@ -9984,7 +10111,8 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation 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,
@@ -10005,15 +10133,26 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
while (HeapTupleIsValid(depTup = systable_getnext(scan)))
{
Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(depTup);
+ ObjectAddress foundObject;
+
+ foundObject.classId = foundDep->refclassid;
+ foundObject.objectId = foundDep->refobjid;
+ foundObject.objectSubId = foundDep->refobjsubid;
- if (foundDep->deptype != DEPENDENCY_NORMAL)
+ if (foundDep->deptype != DEPENDENCY_NORMAL &&
+ foundDep->deptype != DEPENDENCY_AUTO)
elog(ERROR, "found unexpected dependency type '%c'",
foundDep->deptype);
if (!(foundDep->refclassid == TypeRelationId &&
foundDep->refobjid == attTup->atttypid) &&
!(foundDep->refclassid == CollationRelationId &&
- foundDep->refobjid == attTup->attcollation))
- elog(ERROR, "found unexpected dependency for column");
+ foundDep->refobjid == attTup->attcollation) &&
+ !(foundDep->refclassid == RelationRelationId &&
+ foundDep->refobjid == RelationGetRelid(rel) &&
+ foundDep->refobjsubid != 0)
+ )
+ elog(ERROR, "found unexpected dependency for column: %s",
+ getObjectDescription(&foundObject));
CatalogTupleDelete(depRel, &depTup->t_self);
}
@@ -14145,6 +14284,18 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
pelem->name),
parser_errposition(pstate, pelem->location)));
+ /*
+ * 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] = attform->attnum;
atttype = attform->atttypid;
attcollation = attform->attcollation;
@@ -14232,6 +14383,36 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
errmsg("partition key expressions cannot contain system column references")));
}
+ /*
+ * 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 = -1;
+ while ((i = bms_next_member(expr_attrs, i)) >= 0)
+ {
+ AttrNumber attno = 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 supported"),
+ 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/trigger.c
index 7e5bf0d27f..7c00a8aeef 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -44,6 +44,7 @@
#include "parser/parsetree.h"
#include "partitioning/partdesc.h"
#include "pgstat.h"
+#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
@@ -73,8 +74,9 @@ static int MyTriggerDepth = 0;
* they use, so we let them be duplicated. Be sure to update all if one needs
* to be changed, however.
*/
-#define GetUpdatedColumns(relinfo, estate) \
- (exec_rt_fetch((relinfo)->ri_RangeTableIndex, estate)->updatedCols)
+#define GetAllUpdatedColumns(relinfo, estate) \
+ (bms_union(exec_rt_fetch((relinfo)->ri_RangeTableIndex, estate)->updatedCols, \
+ exec_rt_fetch((relinfo)->ri_RangeTableIndex, estate)->extraUpdatedCols))
/* Local function prototypes */
static void ConvertTriggerToFK(CreateTrigStmt *stmt, Oid funcoid);
@@ -101,6 +103,7 @@ static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *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, HeapTuple tuple);
/*
@@ -637,6 +640,25 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"),
parser_errposition(pstate, var->location)));
+ if (TRIGGER_FOR_BEFORE(tgtype) &&
+ var->varattno == 0 &&
+ RelationGetDescr(rel)->constr &&
+ (RelationGetDescr(rel)->constr->has_generated_stored ||
+ RelationGetDescr(rel)->constr->has_generated_virtual))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
+ errdetail("A whole-row reference is used and the table contains generated columns."),
+ parser_errposition(pstate, var->location)));
+ if (TRIGGER_FOR_BEFORE(tgtype) &&
+ var->varattno > 0 &&
+ TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attgenerated)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
+ errdetail("Column \"%s\" is a generated column.",
+ NameStr(TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attname)),
+ parser_errposition(pstate, var->location)));
break;
default:
/* can't happen without add_missing_from, so just elog */
@@ -2560,6 +2582,7 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
heap_freetuple(slottuple);
return NULL; /* "do nothing" */
}
+ check_modified_virtual_generated(RelationGetDescr(relinfo->ri_RelationDesc), newtuple);
}
if (newtuple != slottuple)
@@ -2929,7 +2952,7 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo)
CMD_UPDATE))
return;
- updatedCols = GetUpdatedColumns(relinfo, estate);
+ updatedCols = GetAllUpdatedColumns(relinfo, estate);
LocTriggerData.type = T_TriggerData;
LocTriggerData.tg_event = TRIGGER_EVENT_UPDATE |
@@ -2978,7 +3001,7 @@ ExecASUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
if (trigdesc && trigdesc->trig_update_after_statement)
AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE,
false, NULL, NULL, NIL,
- GetUpdatedColumns(relinfo, estate),
+ GetAllUpdatedColumns(relinfo, estate),
transition_capture);
}
@@ -3044,7 +3067,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
LocTriggerData.tg_relation = relinfo->ri_RelationDesc;
LocTriggerData.tg_oldtable = NULL;
LocTriggerData.tg_newtable = NULL;
- updatedCols = GetUpdatedColumns(relinfo, estate);
+ updatedCols = GetAllUpdatedColumns(relinfo, estate);
for (i = 0; i < trigdesc->numtriggers; i++)
{
Trigger *trigger = &trigdesc->triggers[i];
@@ -3076,6 +3099,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
heap_freetuple(trigtuple);
return NULL; /* "do nothing" */
}
+ check_modified_virtual_generated(RelationGetDescr(relinfo->ri_RelationDesc), newtuple);
}
if (trigtuple != fdw_trigtuple && trigtuple != newtuple)
heap_freetuple(trigtuple);
@@ -3134,7 +3158,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE,
true, trigtuple, newtuple, recheckIndexes,
- GetUpdatedColumns(relinfo, estate),
+ GetAllUpdatedColumns(relinfo, estate),
transition_capture);
if (trigtuple != fdw_trigtuple)
heap_freetuple(trigtuple);
@@ -3494,6 +3518,7 @@ TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
tgqual = stringToNode(trigger->tgqual);
+ tgqual = (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);
@@ -6173,3 +6198,34 @@ pg_trigger_depth(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(MyTriggerDepth);
}
+
+/*
+ * Check whether a trigger modified a virtual generated column and error if
+ * so.
+ *
+ * We need to check this so that we don't end up storing a non-null value in a
+ * virtual generated column.
+ *
+ * We don't need to check for stored generated columns, since those will be
+ * overwritten later anyway.
+ */
+static void
+check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple tuple)
+{
+ if (!(tupdesc->constr && tupdesc->constr->has_generated_virtual))
+ return;
+
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ if (TupleDescAttr(tupdesc, i)->attgenerated == 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/typecmds.c
index 448926db12..4989f85d39 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -917,7 +917,8 @@ DefineDomain(CreateDomainStmt *stmt)
defaultExpr = cookDefault(pstate, constr->raw_expr,
basetypeoid,
basetypeMod,
- domainName);
+ domainName,
+ 0);
/*
* If the expression is just a NULL constant, we treat it
@@ -2227,7 +2228,8 @@ AlterDomainDefault(List *names, Node *defaultRaw)
defaultExpr = cookDefault(pstate, defaultRaw,
typTup->typbasetype,
typTup->typtypmod,
- NameStr(typTup->typname));
+ NameStr(typTup->typname),
+ 0);
/*
* If the expression is just a NULL constant, we treat the command
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 9a20460e76..b5d7976cf1 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -53,7 +53,7 @@
#include "mb/pg_wchar.h"
#include "miscadmin.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"
@@ -102,7 +102,7 @@ static void EvalPlanQualStart(EPQState *epqstate, EState *parentestate,
Plan *planTree);
/*
- * Note that GetUpdatedColumns() also exists in commands/trigger.c. There does
+ * Note that GetAllUpdatedColumns() also exists in commands/trigger.c. There does
* not appear to be any good header to put it into, given the structures that
* it uses, so we let them be duplicated. Be sure to update both if one needs
* to be changed, however.
@@ -111,6 +111,9 @@ static void EvalPlanQualStart(EPQState *epqstate, EState *parentestate,
(exec_rt_fetch((relinfo)->ri_RangeTableIndex, estate)->insertedCols)
#define GetUpdatedColumns(relinfo, estate) \
(exec_rt_fetch((relinfo)->ri_RangeTableIndex, estate)->updatedCols)
+#define GetAllUpdatedColumns(relinfo, estate) \
+ (bms_union(exec_rt_fetch((relinfo)->ri_RangeTableIndex, estate)->updatedCols, \
+ exec_rt_fetch((relinfo)->ri_RangeTableIndex, estate)->extraUpdatedCols))
/* end of local decls */
@@ -1320,6 +1323,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
resultRelInfo->ri_FdwState = NULL;
resultRelInfo->ri_usesFdwDirectModify = false;
resultRelInfo->ri_ConstraintExprs = NULL;
+ resultRelInfo->ri_GeneratedExprs = NULL;
resultRelInfo->ri_junkFilter = NULL;
resultRelInfo->ri_projectReturning = NULL;
resultRelInfo->ri_onConflictArbiterIndexes = NIL;
@@ -1746,6 +1750,7 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
Expr *checkconstr;
checkconstr = stringToNode(check[i].ccbin);
+ checkconstr = (Expr *) expand_generated_columns_in_expr((Node *) checkconstr, rel);
resultRelInfo->ri_ConstraintExprs[i] =
ExecPrepareExpr(checkconstr, estate);
}
@@ -2240,6 +2245,10 @@ ExecBuildSlotValueDescription(Oid reloid,
if (att->attisdropped)
continue;
+ /* ignore virtual generated columns; they are always null here */
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ continue;
+
if (!table_perm)
{
/*
@@ -2329,7 +2338,7 @@ ExecUpdateLockMode(EState *estate, ResultRelInfo *relinfo)
* been modified, then we can use a weaker lock, allowing for better
* concurrency.
*/
- updatedCols = GetUpdatedColumns(relinfo, estate);
+ updatedCols = GetAllUpdatedColumns(relinfo, estate);
keyCols = RelationGetIndexAttrBitmap(relinfo->ri_RelationDesc,
INDEX_ATTR_BITMAP_KEY);
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index 589573b879..b7d83f9660 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -21,6 +21,7 @@
#include "access/xact.h"
#include "commands/trigger.h"
#include "executor/executor.h"
+#include "executor/nodeModifyTable.h"
#include "nodes/nodeFuncs.h"
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
@@ -413,6 +414,14 @@ ExecSimpleRelationInsert(EState *estate, TupleTableSlot *slot)
{
List *recheckIndexes = NIL;
+ /* Compute stored generated columns */
+ if (rel->rd_att->constr &&
+ rel->rd_att->constr->has_generated_stored)
+ {
+ ExecComputeStoredGenerated(estate, slot);
+ tuple = ExecFetchSlotHeapTuple(slot, true, NULL);
+ }
+
/* Check the constraints of the tuple */
if (rel->rd_att->constr)
ExecConstraints(resultRelInfo, slot, estate);
@@ -485,6 +494,14 @@ ExecSimpleRelationUpdate(EState *estate, EPQState *epqstate,
{
List *recheckIndexes = NIL;
+ /* Compute stored generated columns */
+ if (rel->rd_att->constr &&
+ rel->rd_att->constr->has_generated_stored)
+ {
+ ExecComputeStoredGenerated(estate, slot);
+ tuple = ExecFetchSlotHeapTuple(slot, true, NULL);
+ }
+
/* Check the constraints of the tuple */
if (rel->rd_att->constr)
ExecConstraints(resultRelInfo, slot, estate);
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 566858c19b..a7916dbee1 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -48,6 +48,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"
@@ -244,6 +245,86 @@ ExecCheckTIDVisible(EState *estate,
ReleaseBuffer(buffer);
}
+/*
+ * Compute stored generated columns for a tuple
+ */
+void
+ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot)
+{
+ ResultRelInfo *resultRelInfo = estate->es_result_relation_info;
+ Relation rel = resultRelInfo->ri_RelationDesc;
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int natts = tupdesc->natts;
+ MemoryContext oldContext;
+ Datum *values;
+ bool *nulls;
+ bool *replaces;
+ HeapTuple tuple;
+
+ Assert(tupdesc->constr && tupdesc->constr->has_generated_stored);
+
+ /*
+ * 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 == NULL)
+ {
+ oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ resultRelInfo->ri_GeneratedExprs =
+ (ExprState **) palloc(natts * sizeof(ExprState *));
+
+ for (int i = 0; i < natts; i++)
+ {
+ if (TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_STORED)
+ {
+ Expr *expr;
+
+ expr = (Expr *) build_column_default(rel, i + 1);
+ if (expr == NULL)
+ elog(ERROR, "no generation expression found for column number %d of table \"%s\"",
+ i + 1, RelationGetRelationName(rel));
+
+ resultRelInfo->ri_GeneratedExprs[i] = ExecPrepareExpr(expr, estate);
+ }
+ }
+
+ MemoryContextSwitchTo(oldContext);
+ }
+
+ oldContext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
+
+ values = palloc(sizeof(*values) * natts);
+ nulls = palloc(sizeof(*nulls) * natts);
+ replaces = palloc0(sizeof(*replaces) * natts);
+
+ for (int i = 0; i < natts; i++)
+ {
+ if (TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_STORED)
+ {
+ ExprContext *econtext;
+ Datum val;
+ bool isnull;
+
+ econtext = GetPerTupleExprContext(estate);
+ econtext->ecxt_scantuple = slot;
+
+ val = ExecEvalExpr(resultRelInfo->ri_GeneratedExprs[i], econtext, &isnull);
+
+ values[i] = val;
+ nulls[i] = isnull;
+ replaces[i] = true;
+ }
+ }
+
+ tuple = ExecFetchSlotHeapTuple(slot, true, NULL);
+ tuple = heap_modify_tuple(tuple, tupdesc, values, nulls, replaces);
+ ExecStoreHeapTuple(tuple, slot, false);
+
+ MemoryContextSwitchTo(oldContext);
+}
+
/* ----------------------------------------------------------------
* ExecInsert
*
@@ -316,6 +397,16 @@ ExecInsert(ModifyTableState *mtstate,
}
else if (resultRelInfo->ri_FdwRoutine)
{
+ /*
+ * Compute stored generated columns
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->has_generated_stored)
+ {
+ ExecComputeStoredGenerated(estate, slot);
+ tuple = ExecFetchSlotHeapTuple(slot, true, NULL);
+ }
+
/*
* insert into foreign table: let the FDW do it
*/
@@ -346,6 +437,16 @@ ExecInsert(ModifyTableState *mtstate,
*/
tuple->t_tableOid = RelationGetRelid(resultRelationDesc);
+ /*
+ * Compute stored generated columns
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->has_generated_stored)
+ {
+ ExecComputeStoredGenerated(estate, slot);
+ tuple = ExecFetchSlotHeapTuple(slot, true, NULL);
+ }
+
/*
* Check any RLS WITH CHECK policies.
*
@@ -969,6 +1070,16 @@ ExecUpdate(ModifyTableState *mtstate,
}
else if (resultRelInfo->ri_FdwRoutine)
{
+ /*
+ * Compute stored generated columns
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->has_generated_stored)
+ {
+ ExecComputeStoredGenerated(estate, slot);
+ ExecFetchSlotHeapTuple(slot, true, NULL);
+ }
+
/*
* update in foreign table: let the FDW do it
*/
@@ -1000,6 +1111,16 @@ ExecUpdate(ModifyTableState *mtstate,
*/
tuple->t_tableOid = RelationGetRelid(resultRelationDesc);
+ /*
+ * Compute stored generated columns
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->has_generated_stored)
+ {
+ ExecComputeStoredGenerated(estate, slot);
+ tuple = ExecFetchSlotHeapTuple(slot, true, NULL);
+ }
+
/*
* Check any RLS UPDATE WITH CHECK policies
*
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index e15724bb0e..cfc83d9d30 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2381,6 +2381,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
COPY_BITMAPSET_FIELD(selectedCols);
COPY_BITMAPSET_FIELD(insertedCols);
COPY_BITMAPSET_FIELD(updatedCols);
+ COPY_BITMAPSET_FIELD(extraUpdatedCols);
COPY_NODE_FIELD(securityQuals);
return newnode;
@@ -2879,6 +2880,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 +2904,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);
@@ -3007,6 +3010,7 @@ _copyQuery(const Query *from)
COPY_SCALAR_FIELD(hasModifyingCTE);
COPY_SCALAR_FIELD(hasForUpdate);
COPY_SCALAR_FIELD(hasRowSecurity);
+ COPY_SCALAR_FIELD(hasGeneratedVirtual);
COPY_NODE_FIELD(cteList);
COPY_NODE_FIELD(rtable);
COPY_NODE_FIELD(jointree);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 31499eb798..39c225af82 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -959,6 +959,7 @@ _equalQuery(const Query *a, const Query *b)
COMPARE_SCALAR_FIELD(hasModifyingCTE);
COMPARE_SCALAR_FIELD(hasForUpdate);
COMPARE_SCALAR_FIELD(hasRowSecurity);
+ COMPARE_SCALAR_FIELD(hasGeneratedVirtual);
COMPARE_NODE_FIELD(cteList);
COMPARE_NODE_FIELD(rtable);
COMPARE_NODE_FIELD(jointree);
@@ -2559,6 +2560,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 +2582,7 @@ _equalConstraint(const Constraint *a, const Constraint *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);
@@ -2658,6 +2661,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
COMPARE_BITMAPSET_FIELD(selectedCols);
COMPARE_BITMAPSET_FIELD(insertedCols);
COMPARE_BITMAPSET_FIELD(updatedCols);
+ COMPARE_BITMAPSET_FIELD(extraUpdatedCols);
COMPARE_NODE_FIELD(securityQuals);
return true;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 65302fe65b..f13883892a 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2782,6 +2782,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);
@@ -2883,6 +2884,7 @@ _outQuery(StringInfo str, const Query *node)
WRITE_BOOL_FIELD(hasModifyingCTE);
WRITE_BOOL_FIELD(hasForUpdate);
WRITE_BOOL_FIELD(hasRowSecurity);
+ WRITE_BOOL_FIELD(hasGeneratedVirtual);
WRITE_NODE_FIELD(cteList);
WRITE_NODE_FIELD(rtable);
WRITE_NODE_FIELD(jointree);
@@ -3086,6 +3088,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
WRITE_BITMAPSET_FIELD(selectedCols);
WRITE_BITMAPSET_FIELD(insertedCols);
WRITE_BITMAPSET_FIELD(updatedCols);
+ WRITE_BITMAPSET_FIELD(extraUpdatedCols);
WRITE_NODE_FIELD(securityQuals);
}
@@ -3457,6 +3460,14 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_CHAR_FIELD(generated_when);
break;
+ 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/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 5aa42242a9..e120b19abe 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -263,6 +263,7 @@ _readQuery(void)
READ_BOOL_FIELD(hasModifyingCTE);
READ_BOOL_FIELD(hasForUpdate);
READ_BOOL_FIELD(hasRowSecurity);
+ READ_BOOL_FIELD(hasGeneratedVirtual);
READ_NODE_FIELD(cteList);
READ_NODE_FIELD(rtable);
READ_NODE_FIELD(jointree);
@@ -1429,6 +1430,7 @@ _readRangeTblEntry(void)
READ_BITMAPSET_FIELD(selectedCols);
READ_BITMAPSET_FIELD(insertedCols);
READ_BITMAPSET_FIELD(updatedCols);
+ READ_BITMAPSET_FIELD(extraUpdatedCols);
READ_NODE_FIELD(securityQuals);
READ_DONE();
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 236f506cfb..20dd72f56b 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -6530,8 +6530,9 @@ make_modifytable(PlannerInfo *root,
/*
* Try to modify the foreign table directly if (1) the FDW provides
- * callback functions needed for that, (2) there are no row-level
- * triggers on the foreign table, and (3) there are no WITH CHECK
+ * callback functions needed for that and (2) there are no local
+ * structures that need to be run for each modified row: row-level
+ * triggers on the foreign table, stored generated columns, WITH CHECK
* OPTIONs from parent views.
*/
direct_modify = false;
@@ -6541,7 +6542,8 @@ make_modifytable(PlannerInfo *root,
fdwroutine->IterateDirectModify != NULL &&
fdwroutine->EndDirectModify != NULL &&
withCheckOptionLists == NIL &&
- !has_row_triggers(subroot, rti, operation))
+ !has_row_triggers(subroot, rti, operation) &&
+ !has_stored_generated_columns(subroot, rti))
direct_modify = fdwroutine->PlanDirectModify(subroot, node, rti, i);
if (direct_modify)
direct_modify_plans = bms_add_member(direct_modify_plans, i);
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.c
index faba493200..06b82422a5 100644
--- a/src/backend/optimizer/util/inherit.c
+++ b/src/backend/optimizer/util/inherit.c
@@ -268,6 +268,10 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
if (!root->partColsUpdated)
root->partColsUpdated =
has_partition_attrs(parentrel, parentrte->updatedCols, NULL);
+ /*
+ * There shouldn't be any generated columns in the partition key.
+ */
+ Assert(!has_partition_attrs(parentrel, parentrte->extraUpdatedCols, NULL));
/* First expand the partitioned table itself. */
expand_single_inheritance_child(root, parentrte, parentRTindex, parentrel,
@@ -408,6 +412,8 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
appinfo->translated_vars);
childrte->updatedCols = translate_col_privs(parentrte->updatedCols,
appinfo->translated_vars);
+ childrte->extraUpdatedCols = translate_col_privs(parentrte->extraUpdatedCols,
+ appinfo->translated_vars);
}
}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 78a96b4ee2..36f5879047 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -2072,6 +2072,25 @@ has_row_triggers(PlannerInfo *root, Index rti, CmdType event)
return result;
}
+bool
+has_stored_generated_columns(PlannerInfo *root, Index rti)
+{
+ RangeTblEntry *rte = planner_rt_fetch(rti, root);
+ Relation relation;
+ TupleDesc tupdesc;
+ bool result = false;
+
+ /* Assume we already have adequate lock */
+ relation = heap_open(rte->relid, NoLock);
+
+ tupdesc = RelationGetDescr(relation);
+ result = tupdesc->constr && tupdesc->constr->has_generated_stored;
+
+ heap_close(relation, NoLock);
+
+ return result;
+}
+
/*
* set_relation_partition_info
*
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index e3544efb6f..6988226563 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -451,6 +451,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasAggs = pstate->p_hasAggs;
+ qry->hasGeneratedVirtual = pstate->p_hasGeneratedVirtual;
assign_query_collations(pstate, qry);
@@ -882,6 +883,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasSubLinks = pstate->p_hasSubLinks;
+ qry->hasGeneratedVirtual = pstate->p_hasGeneratedVirtual;
assign_query_collations(pstate, qry);
@@ -1322,6 +1324,7 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasAggs = pstate->p_hasAggs;
+ qry->hasGeneratedVirtual = pstate->p_hasGeneratedVirtual;
foreach(l, stmt->lockingClause)
{
@@ -1796,6 +1799,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasAggs = pstate->p_hasAggs;
+ qry->hasGeneratedVirtual = pstate->p_hasGeneratedVirtual;
foreach(l, lockingClause)
{
@@ -2286,6 +2290,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasSubLinks = pstate->p_hasSubLinks;
+ qry->hasGeneratedVirtual = pstate->p_hasGeneratedVirtual;
assign_query_collations(pstate, qry);
@@ -2303,6 +2308,7 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist)
RangeTblEntry *target_rte;
ListCell *orig_tl;
ListCell *tl;
+ TupleDesc tupdesc = pstate->p_target_relation->rd_att;
tlist = transformTargetList(pstate, origTlist,
EXPR_KIND_UPDATE_SOURCE);
@@ -2361,6 +2367,33 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist)
if (orig_tl != NULL)
elog(ERROR, "UPDATE target count mismatch --- internal error");
+ /*
+ * Record in extraUpdatedCols generated columns referencing updated base
+ * columns.
+ */
+ if (tupdesc->constr &&
+ (tupdesc->constr->has_generated_stored ||
+ tupdesc->constr->has_generated_virtual))
+ {
+ for (int i = 0; i < tupdesc->constr->num_defval; i++)
+ {
+ AttrDefault defval = tupdesc->constr->defval[i];
+ Node *expr;
+ Bitmapset *attrs_used = NULL;
+
+ /* skip if not generated column */
+ if (!TupleDescAttr(tupdesc, defval.adnum - 1)->attgenerated)
+ continue;
+
+ expr = stringToNode(defval.adbin);
+ pull_varattnos(expr, 1, &attrs_used);
+
+ if (bms_overlap(target_rte->updatedCols, attrs_used))
+ target_rte->extraUpdatedCols = bms_add_member(target_rte->extraUpdatedCols,
+ defval.adnum - FirstLowInvalidHeapAttributeNumber);
+ }
+ }
+
return tlist;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0279013120..a3cb12692d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -575,7 +575,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
-%type <ival> generated_when override_kind
+%type <ival> generated_when override_kind opt_virtual_or_stored
%type <partspec> PartitionSpec OptPartitionSpec
%type <str> part_strategy
%type <partelem> part_elem
@@ -675,7 +675,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE 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 SUPPORT SYMMETRIC SYSID SYSTEM_P
TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
@@ -687,7 +687,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UNTIL UPDATE USER USING
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
- VERBOSE VERSION_P VIEW VIEWS VOLATILE
+ VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -3484,6 +3484,17 @@ ColConstraintElem:
n->location = @1;
$$ = (Node *)n;
}
+ | GENERATED generated_when AS '(' a_expr ')' opt_virtual_or_stored
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_GENERATED;
+ n->generated_when = $2;
+ n->raw_expr = $5;
+ n->cooked_expr = NULL;
+ n->generated_kind = $7;
+ n->location = @1;
+ $$ = (Node *)n;
+ }
| REFERENCES qualified_name opt_column_list key_match key_actions
{
Constraint *n = makeNode(Constraint);
@@ -3506,6 +3517,12 @@ generated_when:
| BY DEFAULT { $$ = ATTRIBUTE_IDENTITY_BY_DEFAULT; }
;
+opt_virtual_or_stored:
+ STORED { $$ = ATTRIBUTE_GENERATED_STORED; }
+ | VIRTUAL { $$ = ATTRIBUTE_GENERATED_VIRTUAL; }
+ | /*EMPTY*/ { $$ = ATTRIBUTE_GENERATED_VIRTUAL; }
+ ;
+
/*
* ConstraintAttr represents constraint attributes, which we parse as if
* they were independent constraint clauses, in order to avoid shift/reduce
@@ -3574,6 +3591,7 @@ TableLikeOption:
| CONSTRAINTS { $$ = CREATE_TABLE_LIKE_CONSTRAINTS; }
| DEFAULTS { $$ = CREATE_TABLE_LIKE_DEFAULTS; }
| IDENTITY_P { $$ = CREATE_TABLE_LIKE_IDENTITY; }
+ | GENERATED { $$ = CREATE_TABLE_LIKE_GENERATED; }
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
@@ -15192,6 +15210,7 @@ unreserved_keyword:
| STDIN
| STDOUT
| STORAGE
+ | STORED
| STRICT_P
| STRIP_P
| SUBSCRIPTION
@@ -15229,6 +15248,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_agg.c
index 183ea0f2c4..c745fcdd2b 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -520,6 +520,14 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
err = _("grouping operations are not allowed in partition key expressions");
break;
+ case EXPR_KIND_GENERATED_COLUMN:
+
+ if (isAgg)
+ err = _("aggregate functions are not allowed in column generation expressions");
+ else
+ err = _("grouping operations are not allowed in column generation expressions");
+
+ break;
case EXPR_KIND_CALL_ARGUMENT:
if (isAgg)
@@ -922,6 +930,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_COPY_WHERE:
err = _("window functions are not allowed in COPY FROM WHERE conditions");
break;
+ case EXPR_KIND_GENERATED_COLUMN:
+ err = _("window functions are not allowed in column generation expressions");
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index c6ce1011e2..a632e020fe 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -212,6 +212,10 @@ setTargetTable(ParseState *pstate, RangeVar *relation,
pstate->p_target_relation = parserOpenTable(pstate, relation,
RowExclusiveLock);
+ if (pstate->p_target_relation->rd_att->constr &&
+ pstate->p_target_relation->rd_att->constr->has_generated_virtual)
+ pstate->p_hasGeneratedVirtual = true;
+
/*
* Now build an RTE.
*/
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index e559353529..0c88d0b2ee 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1854,6 +1854,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_COPY_WHERE:
err = _("cannot use subquery in COPY FROM WHERE condition");
break;
+ case EXPR_KIND_GENERATED_COLUMN:
+ err = _("cannot use subquery in column generation expression");
+ break;
/*
* There is intentionally no default: case here, so that the
@@ -3484,6 +3487,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "CALL";
case EXPR_KIND_COPY_WHERE:
return "WHERE";
+ case EXPR_KIND_GENERATED_COLUMN:
+ return "GENERATED AS";
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 5222231b51..dfe678025e 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2376,6 +2376,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
case EXPR_KIND_COPY_WHERE:
err = _("set-returning functions are not allowed in COPY FROM WHERE conditions");
break;
+ case EXPR_KIND_GENERATED_COLUMN:
+ err = _("set-returning functions are not allowed in column generation expressions");
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index f3b6d193aa..c699b55345 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -731,6 +731,19 @@ scanRTEForColumn(ParseState *pstate, RangeTblEntry *rte, const char *colname,
colname),
parser_errposition(pstate, location)));
+ /*
+ * In generated column, no system column is allowed except tableOid.
+ * (Required for stored generated, but we also do it for virtual
+ * generated for now for consistency.)
+ */
+ if (pstate->p_expr_kind == EXPR_KIND_GENERATED_COLUMN &&
+ attnum < InvalidAttrNumber && attnum != TableOidAttributeNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("cannot use system column \"%s\" in column generation expression",
+ colname),
+ parser_errposition(pstate, location)));
+
if (attnum != InvalidAttrNumber)
{
/* now check to see if column actually is defined */
@@ -1235,6 +1248,9 @@ addRangeTableEntry(ParseState *pstate,
rte->eref = makeAlias(refname, NIL);
buildRelationAliases(rel->rd_att, alias, rte->eref);
+ if (rel->rd_att->constr && rel->rd_att->constr->has_generated_virtual)
+ pstate->p_hasGeneratedVirtual = true;
+
/*
* Drop the rel refcount, but keep the access lock till end of transaction
* so that the table can't be deleted or have its schema modified
@@ -1257,6 +1273,7 @@ addRangeTableEntry(ParseState *pstate,
rte->selectedCols = NULL;
rte->insertedCols = NULL;
rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
/*
* Add completed RTE to pstate's range table list, but not to join list
@@ -1328,6 +1345,7 @@ addRangeTableEntryForRelation(ParseState *pstate,
rte->selectedCols = NULL;
rte->insertedCols = NULL;
rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
/*
* Add completed RTE to pstate's range table list, but not to join list
@@ -1407,6 +1425,7 @@ addRangeTableEntryForSubquery(ParseState *pstate,
rte->selectedCols = NULL;
rte->insertedCols = NULL;
rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
/*
* Add completed RTE to pstate's range table list, but not to join list
@@ -1670,6 +1689,7 @@ addRangeTableEntryForFunction(ParseState *pstate,
rte->selectedCols = NULL;
rte->insertedCols = NULL;
rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
/*
* Add completed RTE to pstate's range table list, but not to join list
@@ -1733,6 +1753,7 @@ addRangeTableEntryForTableFunc(ParseState *pstate,
rte->selectedCols = NULL;
rte->insertedCols = NULL;
rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
/*
* Add completed RTE to pstate's range table list, but not to join list
@@ -1811,6 +1832,7 @@ addRangeTableEntryForValues(ParseState *pstate,
rte->selectedCols = NULL;
rte->insertedCols = NULL;
rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
/*
* Add completed RTE to pstate's range table list, but not to join list
@@ -1881,6 +1903,7 @@ addRangeTableEntryForJoin(ParseState *pstate,
rte->selectedCols = NULL;
rte->insertedCols = NULL;
rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
/*
* Add completed RTE to pstate's range table list, but not to join list
@@ -1983,6 +2006,7 @@ addRangeTableEntryForCTE(ParseState *pstate,
rte->selectedCols = NULL;
rte->insertedCols = NULL;
rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
/*
* Add completed RTE to pstate's range table list, but not to join list
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index a37d1f18be..82d3e342ca 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -502,6 +502,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
bool saw_nullable;
bool saw_default;
bool saw_identity;
+ bool saw_generated;
ListCell *clist;
cxt->columns = lappend(cxt->columns, column);
@@ -609,6 +610,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
saw_nullable = false;
saw_default = false;
saw_identity = false;
+ saw_generated = false;
foreach(clist, column->constraints)
{
@@ -689,6 +691,50 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
break;
}
+ case CONSTR_GENERATED:
+ if (cxt->ofType)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("generated columns 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 = constraint->generated_kind;
+ column->raw_default = constraint->raw_expr;
+ Assert(constraint->cooked_expr == NULL);
+ saw_generated = 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 == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ Type ctype;
+
+ ctype = typenameType(cxt->pstate, column->typeName, NULL);
+ if (((Form_pg_type) GETSTRUCT(ctype))->typtype == TYPTYPE_DOMAIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("virtual generated column \"%s\" cannot have a domain type",
+ column->colname),
+ parser_errposition(cxt->pstate,
+ column->location)));
+ ReleaseSysCache(ctype);
+ }
+ break;
+
case CONSTR_CHECK:
cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
break;
@@ -755,6 +801,50 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *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 column \"%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 column \"%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 == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ Constraint *chk = makeNode(Constraint);
+ NullTest *nt = makeNode(NullTest);
+ ColumnRef *cr = makeNode(ColumnRef);
+
+ cr->location = -1;
+ cr->fields = list_make1(makeString(column->colname));
+
+ nt->arg = (Expr *) cr;
+ nt->nulltesttype = IS_NOT_NULL;
+ nt->location = -1;
+
+ chk->contype = CONSTR_CHECK;
+ chk->location = -1;
+ chk->initially_valid = true;
+ chk->raw_expr = (Node *) nt;
+
+ cxt->ckconstraints = lappend(cxt->ckconstraints, chk);
+
+ column->is_not_null = false;
+ }
}
/*
@@ -983,11 +1073,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 = NULL;
AttrDefault *attrdef;
int i;
+ bool found_whole_row;
/* Find default in constraint structure */
Assert(constr != NULL);
@@ -1002,12 +1094,27 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
Assert(this_default != NULL);
+ def->cooked_default = 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 whole-row reference to table \"%s\".",
+ attributeName,
+ RelationGetRelationName(relation))));
- def->cooked_default = this_default;
+ if (attribute->attgenerated &&
+ (table_like_clause->options & CREATE_TABLE_LIKE_GENERATED))
+ def->generated = attribute->attgenerated;
}
/*
diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
index 7027737e67..7123d4169d 100644
--- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
+++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
@@ -56,8 +56,8 @@ static char *libpqrcv_get_conninfo(WalReceiverConn *conn);
static void libpqrcv_get_senderinfo(WalReceiverConn *conn,
char **sender_host, int *sender_port);
static char *libpqrcv_identify_system(WalReceiverConn *conn,
- TimeLineID *primary_tli,
- int *server_version);
+ TimeLineID *primary_tli);
+static int libpqrcv_server_version(WalReceiverConn *conn);
static void libpqrcv_readtimelinehistoryfile(WalReceiverConn *conn,
TimeLineID tli, char **filename,
char **content, int *len);
@@ -86,6 +86,7 @@ static WalReceiverFunctionsType PQWalReceiverFunctions = {
libpqrcv_get_conninfo,
libpqrcv_get_senderinfo,
libpqrcv_identify_system,
+ libpqrcv_server_version,
libpqrcv_readtimelinehistoryfile,
libpqrcv_startstreaming,
libpqrcv_endstreaming,
@@ -309,8 +310,7 @@ libpqrcv_get_senderinfo(WalReceiverConn *conn, char **sender_host,
* timeline ID of the primary.
*/
static char *
-libpqrcv_identify_system(WalReceiverConn *conn, TimeLineID *primary_tli,
- int *server_version)
+libpqrcv_identify_system(WalReceiverConn *conn, TimeLineID *primary_tli)
{
PGresult *res;
char *primary_sysid;
@@ -343,11 +343,18 @@ libpqrcv_identify_system(WalReceiverConn *conn, TimeLineID *primary_tli,
*primary_tli = pg_strtoint32(PQgetvalue(res, 0, 1));
PQclear(res);
- *server_version = PQserverVersion(conn->streamConn);
-
return primary_sysid;
}
+/*
+ * Thin wrapper around libpq to obtain server version.
+ */
+static int
+libpqrcv_server_version(WalReceiverConn *conn)
+{
+ return PQserverVersion(conn->streamConn);
+}
+
/*
* Start streaming WAL data from given streaming options.
*
diff --git a/src/backend/replication/logical/proto.c b/src/backend/replication/logical/proto.c
index dffb6cd9fd..0411963f93 100644
--- a/src/backend/replication/logical/proto.c
+++ b/src/backend/replication/logical/proto.c
@@ -453,7 +453,7 @@ logicalrep_write_tuple(StringInfo out, Relation rel, HeapTuple tuple)
for (i = 0; i < desc->natts; i++)
{
- if (TupleDescAttr(desc, i)->attisdropped)
+ if (TupleDescAttr(desc, i)->attisdropped || TupleDescAttr(desc, i)->attgenerated)
continue;
nliveatts++;
}
@@ -473,8 +473,7 @@ logicalrep_write_tuple(StringInfo out, Relation rel, HeapTuple tuple)
Form_pg_attribute att = TupleDescAttr(desc, i);
char *outputstr;
- /* skip dropped columns */
- if (att->attisdropped)
+ if (att->attisdropped || att->attgenerated)
continue;
if (isnull[i])
@@ -573,7 +572,7 @@ logicalrep_write_attrs(StringInfo out, Relation rel)
/* send number of live attributes */
for (i = 0; i < desc->natts; i++)
{
- if (TupleDescAttr(desc, i)->attisdropped)
+ if (TupleDescAttr(desc, i)->attisdropped || TupleDescAttr(desc, i)->attgenerated)
continue;
nliveatts++;
}
@@ -591,7 +590,7 @@ logicalrep_write_attrs(StringInfo out, Relation rel)
Form_pg_attribute att = TupleDescAttr(desc, i);
uint8 flags = 0;
- if (att->attisdropped)
+ if (att->attisdropped || att->attgenerated)
continue;
/* REPLICA IDENTITY FULL means all columns are sent as part of key. */
diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index 1d918d2c42..5aee4b80e6 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -276,7 +276,7 @@ logicalrep_rel_open(LogicalRepRelId remoteid, LOCKMODE lockmode)
int attnum;
Form_pg_attribute attr = TupleDescAttr(desc, i);
- if (attr->attisdropped)
+ if (attr->attisdropped || attr->attgenerated)
{
entry->attrmap[i] = -1;
continue;
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index 28f5fc23aa..7881079e96 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -697,10 +697,12 @@ fetch_remote_table_info(char *nspname, char *relname,
" LEFT JOIN pg_catalog.pg_index i"
" ON (i.indexrelid = pg_get_replica_identity_index(%u))"
" WHERE a.attnum > 0::pg_catalog.int2"
- " AND NOT a.attisdropped"
+ " AND NOT a.attisdropped %s"
" AND a.attrelid = %u"
" ORDER BY a.attnum",
- lrel->remoteid, lrel->remoteid);
+ lrel->remoteid,
+ (walrcv_server_version(wrconn) >= 120000 ? "AND a.attgenerated = ''" : ""),
+ lrel->remoteid);
res = walrcv_exec(wrconn, cmd.data, 4, attrRow);
if (res->status != WALRCV_OK_TUPLES)
diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c
index f9516515bc..fd89f34153 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -240,7 +240,7 @@ slot_fill_defaults(LogicalRepRelMapEntry *rel, EState *estate,
{
Expr *defexpr;
- if (TupleDescAttr(desc, attnum)->attisdropped)
+ if (TupleDescAttr(desc, attnum)->attisdropped || TupleDescAttr(desc, attnum)->attgenerated)
continue;
if (rel->attrmap[attnum] >= 0)
@@ -1680,7 +1680,6 @@ ApplyWorkerMain(Datum main_arg)
RepOriginId originid;
TimeLineID startpointTLI;
char *err;
- int server_version;
myslotname = MySubscription->slotname;
@@ -1714,8 +1713,7 @@ ApplyWorkerMain(Datum main_arg)
* We don't really use the output identify_system for anything but it
* does some initializations on the upstream so let's still call it.
*/
- (void) walrcv_identify_system(wrconn, &startpointTLI,
- &server_version);
+ (void) walrcv_identify_system(wrconn, &startpointTLI);
}
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 5511957516..bf64c8e4a4 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -276,7 +276,7 @@ maybe_send_schema(LogicalDecodingContext *ctx,
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped)
+ if (att->attisdropped || att->attgenerated)
continue;
if (att->atttypid < FirstNormalObjectId)
diff --git a/src/backend/replication/walreceiver.c b/src/backend/replication/walreceiver.c
index 2e90944ad5..7dd41791e6 100644
--- a/src/backend/replication/walreceiver.c
+++ b/src/backend/replication/walreceiver.c
@@ -330,7 +330,6 @@ WalReceiverMain(void)
{
char *primary_sysid;
char standby_sysid[32];
- int server_version;
WalRcvStreamOptions options;
/*
@@ -338,8 +337,7 @@ WalReceiverMain(void)
* IDENTIFY_SYSTEM replication command.
*/
EnableWalRcvImmediateExit();
- primary_sysid = walrcv_identify_system(wrconn, &primaryTLI,
- &server_version);
+ primary_sysid = walrcv_identify_system(wrconn, &primaryTLI);
snprintf(standby_sysid, sizeof(standby_sysid), UINT64_FORMAT,
GetSystemIdentifier());
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 7eb41ff026..59b02e52e4 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -40,6 +40,7 @@
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
+#include "utils/syscache.h"
/* We use a list of these to detect recursion in RewriteQuery */
@@ -83,6 +84,8 @@ static List *matchLocks(CmdType event, RuleLock *rulelocks,
static Query *fireRIRrules(Query *parsetree, List *activeRIRs);
static bool view_has_instead_trigger(Relation view, CmdType event);
static Bitmapset *adjust_view_column_set(Bitmapset *cols, List *targetlist);
+struct expand_generated_context;
+static Query *expand_generated_columns_in_query(Query *query, struct expand_generated_context *context);
/*
@@ -832,6 +835,13 @@ rewriteTargetListIU(List *targetList,
if (att_tup->attidentity == ATTRIBUTE_IDENTITY_BY_DEFAULT && override == OVERRIDING_USER_VALUE)
apply_default = true;
+
+ if (att_tup->attgenerated && !apply_default)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attname)),
+ errdetail("Column \"%s\" is a generated column.",
+ NameStr(att_tup->attname))));
}
if (commandType == CMD_UPDATE)
@@ -842,9 +852,24 @@ rewriteTargetListIU(List *targetList,
errmsg("column \"%s\" can only be updated to DEFAULT", NameStr(att_tup->attname)),
errdetail("Column \"%s\" is an identity column defined as GENERATED 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(att_tup->attname)),
+ errdetail("Column \"%s\" is a generated column.",
+ NameStr(att_tup->attname))));
}
- if (apply_default)
+ if (att_tup->attgenerated)
+ {
+ /*
+ * virtual generated column stores a null value; stored generated
+ * column will be fixed in executor
+ */
+ new_tle = NULL;
+ }
+ else if (apply_default)
{
Node *new_expr;
@@ -1151,13 +1176,12 @@ build_column_default(Relation rel, int attrno)
}
}
- if (expr == 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. But
+ * not for generated columns.
+ */
+ if (expr == NULL && !att_tup->attgenerated)
expr = get_typdefault(atttype);
- }
if (expr == NULL)
return NULL; /* No default anywhere */
@@ -1698,12 +1722,14 @@ ApplyRetrieveRule(Query *parsetree,
subrte->selectedCols = rte->selectedCols;
subrte->insertedCols = rte->insertedCols;
subrte->updatedCols = rte->updatedCols;
+ subrte->extraUpdatedCols = rte->extraUpdatedCols;
rte->requiredPerms = 0; /* no permission check on subquery itself */
rte->checkAsUser = InvalidOid;
rte->selectedCols = NULL;
rte->insertedCols = NULL;
rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
return parsetree;
}
@@ -3797,6 +3823,148 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
}
+static Node *
+expand_generated_columns_in_expr_mutator(Node *node, Relation rel)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, Var))
+ {
+ Var *v = (Var *) node;
+ AttrNumber attnum = v->varattno;
+
+ if (attnum > 0 && TupleDescAttr(RelationGetDescr(rel), attnum - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ node = build_column_default(rel, attnum);
+ if (node == 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 = RelationGetDescr(rel);
+
+ if (tupdesc->constr && tupdesc->constr->has_generated_virtual)
+ return expression_tree_mutator(node,
+ expand_generated_columns_in_expr_mutator,
+ rel);
+ else
+ return node;
+}
+
+struct expand_generated_context
+{
+ /* list of range tables, innermost last */
+ List *rtables;
+};
+
+static Node *
+expand_generated_columns_in_query_mutator(Node *node, struct expand_generated_context *context)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, Var))
+ {
+ Var *v = (Var *) node;
+ Oid relid;
+ AttrNumber attnum;
+ List *rtable = list_nth_node(List,
+ context->rtables,
+ list_length(context->rtables) - v->varlevelsup - 1);
+
+ relid = rt_fetch(v->varno, rtable)->relid;
+ attnum = v->varattno;
+
+ if (!relid || !attnum)
+ return node;
+
+ if (get_attgenerated(relid, attnum) == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ Relation rt_entry_relation = heap_open(relid, NoLock);
+
+ node = build_column_default(rt_entry_relation, attnum);
+ if (node == NULL)
+ elog(ERROR, "no generation expression found for column number %d of table \"%s\"",
+ attnum, RelationGetRelationName(rt_entry_relation));
+ ChangeVarNodes(node, 1, v->varno, v->varlevelsup);
+
+ heap_close(rt_entry_relation, NoLock);
+ }
+
+ return node;
+ }
+ else if (IsA(node, Query))
+ {
+ Query *query = (Query *) node;
+
+ query = expand_generated_columns_in_query(query, context);
+
+ return (Node *) query;
+ }
+ else
+ return expression_tree_mutator(node, expand_generated_columns_in_query_mutator, context);
+}
+
+/*
+ * Expand virtual generated columns in a Query. We do some optimizations here
+ * to avoid digging through the whole Query unless necessary.
+ */
+static Query *
+expand_generated_columns_in_query(Query *query, struct expand_generated_context *context)
+{
+ context->rtables = lappend(context->rtables, query->rtable);
+
+ /*
+ * If any table in the query has a virtual column or there is a sublink,
+ * then we need to do the whole walk.
+ */
+ if (query->hasGeneratedVirtual || query->hasSubLinks)
+ {
+ query = query_tree_mutator(query,
+ expand_generated_columns_in_query_mutator,
+ context,
+ QTW_DONT_COPY_QUERY);
+ }
+ /*
+ * Else we only need to process subqueries.
+ */
+ else
+ {
+ ListCell *lc;
+
+ foreach (lc, query->rtable)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+
+ if (rte->rtekind == RTE_SUBQUERY)
+ rte->subquery = expand_generated_columns_in_query(rte->subquery, context);
+ }
+
+ foreach(lc, query->cteList)
+ {
+ CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
+
+ cte->ctequery = (Node *) expand_generated_columns_in_query(castNode(Query, cte->ctequery), context);
+ }
+ }
+
+ context->rtables = list_truncate(context->rtables, list_length(context->rtables) - 1);
+
+ return query;
+}
+
/*
* QueryRewrite -
* Primary entry point to the query rewriter.
@@ -3852,6 +4020,21 @@ QueryRewrite(Query *parsetree)
/*
* Step 3
*
+ * Expand generated columns.
+ */
+ foreach(l, querylist)
+ {
+ Query *query = (Query *) lfirst(l);
+ struct expand_generated_context context;
+
+ context.rtables = NIL;
+
+ query = expand_generated_columns_in_query(query, &context);
+ }
+
+ /*
+ * 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/cache/lsyscache.c
index e88c45d268..d98646d0e6 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;
}
+/*
+ * 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 = SearchSysCache2(ATTNUM,
+ ObjectIdGetDatum(relid),
+ Int16GetDatum(attnum));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_attribute att_tup = (Form_pg_attribute) GETSTRUCT(tp);
+ char result;
+
+ result = 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/cache/partcache.c
index 2b55f25e75..72ae2298ab 100644
--- a/src/backend/utils/cache/partcache.c
+++ b/src/backend/utils/cache/partcache.c
@@ -27,6 +27,7 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/optimizer.h"
#include "partitioning/partbounds.h"
+#include "rewrite/rewriteHandler.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
@@ -126,6 +127,8 @@ RelationBuildPartitionKey(Relation relation)
expr = stringToNode(exprString);
pfree(exprString);
+ expr = 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 54a40ef00b..b1d7fb2ab8 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -502,6 +502,8 @@ RelationBuildTupleDesc(Relation relation)
constr = (TupleConstr *) MemoryContextAlloc(CacheMemoryContext,
sizeof(TupleConstr));
constr->has_not_null = false;
+ constr->has_generated_stored = false;
+ constr->has_generated_virtual = false;
/*
* Form a scan key that selects only user attributes (attnum > 0).
@@ -554,6 +556,10 @@ RelationBuildTupleDesc(Relation relation)
/* Update constraint/default info */
if (attp->attnotnull)
constr->has_not_null = true;
+ if (attp->attgenerated == ATTRIBUTE_GENERATED_STORED)
+ constr->has_generated_stored = true;
+ if (attp->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ constr->has_generated_virtual = true;
/* If the column has a default, fill it into the attrdef array */
if (attp->atthasdef)
@@ -3124,6 +3130,7 @@ RelationBuildLocalRelation(const char *relname,
Form_pg_attribute datt = TupleDescAttr(rel->rd_att, i);
datt->attidentity = satt->attidentity;
+ datt->attgenerated = satt->attgenerated;
datt->attnotnull = satt->attnotnull;
has_not_null |= satt->attnotnull;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a08bc4ecae..2c1424a6ce 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1991,6 +1991,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);
@@ -8107,6 +8112,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
int i_attnotnull;
int i_atthasdef;
int i_attidentity;
+ int i_attgenerated;
int i_attisdropped;
int i_attlen;
int i_attalign;
@@ -8160,6 +8166,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
"a.attislocal,\n"
"pg_catalog.format_type(t.oid, a.atttypmod) AS atttypname,\n");
+ if (fout->remoteVersion >= 120000)
+ appendPQExpBuffer(q,
+ "a.attgenerated,\n");
+ else
+ appendPQExpBuffer(q,
+ "'' AS attgenerated,\n");
+
if (fout->remoteVersion >= 110000)
appendPQExpBuffer(q,
"CASE WHEN a.atthasmissing AND NOT a.attisdropped "
@@ -8232,6 +8245,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
i_attnotnull = PQfnumber(res, "attnotnull");
i_atthasdef = PQfnumber(res, "atthasdef");
i_attidentity = PQfnumber(res, "attidentity");
+ i_attgenerated = PQfnumber(res, "attgenerated");
i_attisdropped = PQfnumber(res, "attisdropped");
i_attlen = PQfnumber(res, "attlen");
i_attalign = PQfnumber(res, "attalign");
@@ -8249,6 +8263,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attstorage = (char *) pg_malloc(ntups * sizeof(char));
tbinfo->typstorage = (char *) pg_malloc(ntups * sizeof(char));
tbinfo->attidentity = (char *) pg_malloc(ntups * sizeof(char));
+ tbinfo->attgenerated = (char *) pg_malloc(ntups * sizeof(char));
tbinfo->attisdropped = (bool *) pg_malloc(ntups * sizeof(bool));
tbinfo->attlen = (int *) pg_malloc(ntups * sizeof(int));
tbinfo->attalign = (char *) pg_malloc(ntups * sizeof(char));
@@ -8275,6 +8290,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage));
tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage));
tbinfo->attidentity[j] = *(PQgetvalue(res, j, i_attidentity));
+ tbinfo->attgenerated[j] = *(PQgetvalue(res, j, i_attgenerated));
tbinfo->needs_override = tbinfo->needs_override || (tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS);
tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't');
tbinfo->attlen[j] = atoi(PQgetvalue(res, j, i_attlen));
@@ -15564,6 +15580,23 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
tbinfo->atttypnames[j]);
}
+ if (has_default)
+ {
+ if (tbinfo->attgenerated[j] == ATTRIBUTE_GENERATED_STORED)
+ appendPQExpBuffer(q, " GENERATED ALWAYS AS (%s) STORED",
+ tbinfo->attrdefs[j]->adef_expr);
+ else if (tbinfo->attgenerated[j] == ATTRIBUTE_GENERATED_VIRTUAL)
+ 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]))
{
@@ -15574,13 +15607,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");
}
}
@@ -18147,6 +18173,7 @@ fmtCopyColumnList(const TableInfo *ti, PQExpBuffer buffer)
int numatts = ti->numatts;
char **attnames = ti->attnames;
bool *attisdropped = ti->attisdropped;
+ char *attgenerated = ti->attgenerated;
bool needComma;
int i;
@@ -18156,6 +18183,8 @@ fmtCopyColumnList(const TableInfo *ti, PQExpBuffer 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 21d2ab05b0..928ed12d04 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_sort.c
index bb128c89f3..243dca7264 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -1108,6 +1108,16 @@ repairDependencyLoop(DumpableObject **loop,
}
}
+ /* Loop of table with itself, happens with generated columns */
+ if (nLoop == 1)
+ {
+ if (loop[0]->objType == 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-referential
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 0233fcb47f..7a8f2c6295 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2357,6 +2357,23 @@
unlike => { exclude_dump_test_schema => 1, },
},
+ 'CREATE TABLE test_table_generated' => {
+ create_order => 3,
+ create_sql => 'CREATE TABLE dump_test.test_table_generated (
+ col1 int primary key,
+ col2 int generated always as (col1 * 2)
+ );',
+ regexp => 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 =>
+ { %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
+ unlike => { exclude_dump_test_schema => 1, },
+ },
+
'CREATE TABLE table_with_stats' => {
create_order => 98,
create_sql => 'CREATE TABLE dump_test.table_index_stats (
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4da6719ce7..90f3f4a995 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1462,6 +1462,7 @@ describeOneTableDetails(const char *schemaname,
attnotnull_col = -1,
attcoll_col = -1,
attidentity_col = -1,
+ attgenerated_col = -1,
isindexkey_col = -1,
indexdef_col = -1,
fdwopts_col = -1,
@@ -1806,8 +1807,9 @@ describeOneTableDetails(const char *schemaname,
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.adrelid) for 128)"
+ ",\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128)"
"\n FROM pg_catalog.pg_attrdef d"
"\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)"
",\n a.attnotnull");
@@ -1824,6 +1826,11 @@ describeOneTableDetails(const char *schemaname,
else
appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attidentity");
attidentity_col = cols++;
+ if (pset.sversion >= 120000)
+ appendPQExpBufferStr(&buf, ",\n a.attgenerated");
+ else
+ appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attgenerated");
+ attgenerated_col = cols++;
}
if (tableinfo.relkind == RELKIND_INDEX ||
tableinfo.relkind == RELKIND_PARTITIONED_INDEX)
@@ -2004,6 +2011,7 @@ describeOneTableDetails(const char *schemaname,
if (show_column_details)
{
char *identity;
+ char *generated;
char *default_str = "";
printTableAddCell(&cont, PQgetvalue(res, i, attcoll_col), false, false);
@@ -2013,16 +2021,21 @@ describeOneTableDetails(const char *schemaname,
false, false);
identity = PQgetvalue(res, i, attidentity_col);
+ generated = PQgetvalue(res, i, attgenerated_col);
- if (!identity[0])
- /* (note: above we cut off the 'default' string at 128) */
- default_str = PQgetvalue(res, i, attrdef_col);
- else if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS)
+ if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS)
default_str = "generated always as identity";
else if (identity[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT)
default_str = "generated by default as identity";
+ else if (generated[0] == ATTRIBUTE_GENERATED_STORED)
+ default_str = psprintf("generated always as (%s) stored", PQgetvalue(res, i, attrdef_col));
+ else if (generated[0] == ATTRIBUTE_GENERATED_VIRTUAL)
+ default_str = psprintf("generated always as (%s)", PQgetvalue(res, i, attrdef_col));
+ else
+ /* (note: above we cut off the 'default' string at 128) */
+ default_str = PQgetvalue(res, i, attrdef_col);
- printTableAddCell(&cont, default_str, false, false);
+ printTableAddCell(&cont, default_str, false, generated[0] ? true : false);
}
/* Info for index columns */
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index 66d1b2fc40..c33fc1ed88 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -42,6 +42,8 @@ typedef struct TupleConstr
uint16 num_defval;
uint16 num_check;
bool has_not_null;
+ bool has_generated_stored;
+ bool has_generated_virtual;
} TupleConstr;
/*
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 50fb62be9d..9f20927528 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -28,6 +28,7 @@ typedef struct RawColumnDefault
AttrNumber attnum; /* attribute to attach default to */
Node *raw_default; /* default value (untransformed parse tree) */
bool missingMode; /* true if part of add column processing */
+ char generated; /* attgenerated setting */
} RawColumnDefault;
typedef struct CookedConstraint
@@ -118,7 +119,8 @@ extern Node *cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
- const char *attname);
+ const char *attname,
+ char attgenerated);
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 a6ec122389..5662d92ea6 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_BOOTSTRAP BKI_ROWTYPE_OID(75,
/* One of the ATTRIBUTE_IDENTITY_* constants below, or '\0' */
char attidentity BKI_DEFAULT('\0');
+ /* 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);
@@ -201,6 +204,9 @@ typedef FormData_pg_attribute *Form_pg_attribute;
#define ATTRIBUTE_IDENTITY_ALWAYS 'a'
#define ATTRIBUTE_IDENTITY_BY_DEFAULT 'd'
+#define ATTRIBUTE_GENERATED_STORED 's'
+#define ATTRIBUTE_GENERATED_VIRTUAL 'v'
+
#endif /* EXPOSE_TO_CLIENT_CODE */
#endif /* PG_ATTRIBUTE_H */
diff --git a/src/include/catalog/pg_class.dat b/src/include/catalog/pg_class.dat
index cccad25c14..57dbfb6495 100644
--- a/src/include/catalog/pg_class.dat
+++ b/src/include/catalog/pg_class.dat
@@ -36,11 +36,11 @@
reloftype => '0', relowner => 'PGUID', relam => '0', relfilenode => '0',
reltablespace => '0', relpages => '0', reltuples => '0', relallvisible => '0',
reltoastrelid => '0', relhasindex => 'f', relisshared => 'f',
- relpersistence => 'p', relkind => 'r', relnatts => '24', relchecks => '0',
- relhasrules => 'f', relhastriggers => 'f', relhassubclass => 'f',
- relrowsecurity => 'f', relforcerowsecurity => 'f', relispopulated => 't',
- relreplident => 'n', relispartition => 'f', relrewrite => '0',
- relfrozenxid => '3', relminmxid => '1', relacl => '_null_',
+ relpersistence => 'p', relkind => 'r', relnatts => '25', relchecks => '0',
+ relhasrules => 'f', relhastriggers => 'f',
+ relhassubclass => 'f', relrowsecurity => 'f', relforcerowsecurity => 'f',
+ relispopulated => 't', relreplident => 'n', relispartition => 'f',
+ relrewrite => '0', relfrozenxid => '3', relminmxid => '1', relacl => '_null_',
reloptions => '_null_', relpartbound => '_null_' },
{ oid => '1255',
relname => 'pg_proc', relnamespace => 'PGNSP', reltype => '81',
diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h
index b8b289efc0..891b119608 100644
--- a/src/include/executor/nodeModifyTable.h
+++ b/src/include/executor/nodeModifyTable.h
@@ -15,6 +15,8 @@
#include "nodes/execnodes.h"
+extern void ExecComputeStoredGenerated(EState *estate, TupleTableSlot *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 3b789ee7cf..c7bc0a88ff 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;
+ /* array of stored generated columns expr states */
+ ExprState **ri_GeneratedExprs;
+
/* for removing junk attributes from tuples */
JunkFilter *ri_junkFilter;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index a7e859dc90..a44e26d338 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -131,6 +131,7 @@ typedef struct Query
bool hasModifyingCTE; /* has INSERT/UPDATE/DELETE in WITH */
bool hasForUpdate; /* FOR [KEY] UPDATE/SHARE was specified */
bool hasRowSecurity; /* rewriter has applied some RLS policy */
+ bool hasGeneratedVirtual; /* some table has a virtual generated column */
List *cteList; /* WITH list (of CommonTableExpr's) */
@@ -655,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 */
@@ -677,10 +679,11 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_COMMENTS = 1 << 0,
CREATE_TABLE_LIKE_CONSTRAINTS = 1 << 1,
CREATE_TABLE_LIKE_DEFAULTS = 1 << 2,
- CREATE_TABLE_LIKE_IDENTITY = 1 << 3,
- CREATE_TABLE_LIKE_INDEXES = 1 << 4,
- CREATE_TABLE_LIKE_STATISTICS = 1 << 5,
- CREATE_TABLE_LIKE_STORAGE = 1 << 6,
+ CREATE_TABLE_LIKE_GENERATED = 1 << 3,
+ CREATE_TABLE_LIKE_IDENTITY = 1 << 4,
+ CREATE_TABLE_LIKE_INDEXES = 1 << 5,
+ CREATE_TABLE_LIKE_STATISTICS = 1 << 6,
+ CREATE_TABLE_LIKE_STORAGE = 1 << 7,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -933,6 +936,15 @@ typedef struct PartitionCmd
* them in these fields. A whole-row Var reference is represented by
* setting the bit for InvalidAttrNumber.
*
+ * updatedCols is also used in some other places, for example, to determine
+ * which triggers to fire and in FDWs to know which changed columns they
+ * need to ship off. Generated columns that are caused to be updated by an
+ * update to a base column are collected in extraUpdatedCols. This is not
+ * considered for permission checking, but it is useful in those places
+ * that want to know the full set of columns being updated as opposed to
+ * only the ones the user explicitly mentioned in the query. (There is
+ * currently no need for an extraInsertedCols, but it could exist.)
+ *
* securityQuals is a list of security barrier quals (boolean expressions),
* to be tested in the listed order before returning a row from the
* relation. It is always NIL in parser output. Entries are added by the
@@ -1087,6 +1099,7 @@ typedef struct RangeTblEntry
Bitmapset *selectedCols; /* columns needing SELECT permission */
Bitmapset *insertedCols; /* columns needing INSERT permission */
Bitmapset *updatedCols; /* columns needing UPDATE permission */
+ Bitmapset *extraUpdatedCols; /* generated columns being updated */
List *securityQuals; /* security barrier quals to apply, if any */
} RangeTblEntry;
@@ -2085,6 +2098,7 @@ typedef enum ConstrType /* types of constraints */
CONSTR_NOTNULL,
CONSTR_DEFAULT,
CONSTR_IDENTITY,
+ CONSTR_GENERATED,
CONSTR_CHECK,
CONSTR_PRIMARY,
CONSTR_UNIQUE,
@@ -2123,7 +2137,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 */
/* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */
List *keys; /* String nodes naming referenced key
diff --git a/src/include/optimizer/plancat.h b/src/include/optimizer/plancat.h
index c337f047cb..c556e0f258 100644
--- a/src/include/optimizer/plancat.h
+++ b/src/include/optimizer/plancat.h
@@ -71,4 +71,6 @@ extern double get_function_rows(PlannerInfo *root, Oid funcid, Node *node);
extern bool has_row_triggers(PlannerInfo *root, Index rti, CmdType event);
+extern bool has_stored_generated_columns(PlannerInfo *root, Index rti);
+
#endif /* PLANCAT_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f05444008c..3c56369b75 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -383,6 +383,7 @@ PG_KEYWORD("statistics", STATISTICS, UNRESERVED_KEYWORD)
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_node.h
index ea99a0954b..08146db965 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -71,7 +71,8 @@ typedef enum ParseExprKind
EXPR_KIND_PARTITION_BOUND, /* partition bound expression */
EXPR_KIND_PARTITION_EXPRESSION, /* PARTITION BY expression */
EXPR_KIND_CALL_ARGUMENT, /* procedure argument in CALL */
- EXPR_KIND_COPY_WHERE /* WHERE condition in COPY FROM */
+ EXPR_KIND_COPY_WHERE, /* WHERE condition in COPY FROM */
+ EXPR_KIND_GENERATED_COLUMN, /* generation expression for a column */
} ParseExprKind;
@@ -204,6 +205,7 @@ struct ParseState
bool p_hasTargetSRFs;
bool p_hasSubLinks;
bool p_hasModifyingCTE;
+ bool p_hasGeneratedVirtual;
Node *p_last_srf; /* most recent set-returning func/op found */
diff --git a/src/include/replication/walreceiver.h b/src/include/replication/walreceiver.h
index e04d725ff5..33e89cae36 100644
--- a/src/include/replication/walreceiver.h
+++ b/src/include/replication/walreceiver.h
@@ -209,8 +209,8 @@ typedef void (*walrcv_get_senderinfo_fn) (WalReceiverConn *conn,
char **sender_host,
int *sender_port);
typedef char *(*walrcv_identify_system_fn) (WalReceiverConn *conn,
- TimeLineID *primary_tli,
- int *server_version);
+ TimeLineID *primary_tli);
+typedef int (*walrcv_server_version_fn) (WalReceiverConn *conn);
typedef void (*walrcv_readtimelinehistoryfile_fn) (WalReceiverConn *conn,
TimeLineID tli,
char **filename,
@@ -240,6 +240,7 @@ typedef struct WalReceiverFunctionsType
walrcv_get_conninfo_fn walrcv_get_conninfo;
walrcv_get_senderinfo_fn walrcv_get_senderinfo;
walrcv_identify_system_fn walrcv_identify_system;
+ walrcv_server_version_fn walrcv_server_version;
walrcv_readtimelinehistoryfile_fn walrcv_readtimelinehistoryfile;
walrcv_startstreaming_fn walrcv_startstreaming;
walrcv_endstreaming_fn walrcv_endstreaming;
@@ -260,8 +261,10 @@ extern PGDLLIMPORT WalReceiverFunctionsType *WalReceiverFunctions;
WalReceiverFunctions->walrcv_get_conninfo(conn)
#define walrcv_get_senderinfo(conn, sender_host, sender_port) \
WalReceiverFunctions->walrcv_get_senderinfo(conn, sender_host, sender_port)
-#define walrcv_identify_system(conn, primary_tli, server_version) \
- WalReceiverFunctions->walrcv_identify_system(conn, primary_tli, server_version)
+#define walrcv_identify_system(conn, primary_tli) \
+ WalReceiverFunctions->walrcv_identify_system(conn, primary_tli)
+#define walrcv_server_version(conn) \
+ WalReceiverFunctions->walrcv_server_version(conn)
#define walrcv_readtimelinehistoryfile(conn, tli, filename, content, size) \
WalReceiverFunctions->walrcv_readtimelinehistoryfile(conn, tli, filename, content, size)
#define walrcv_startstreaming(conn, options) \
diff --git a/src/include/rewrite/rewriteHandler.h b/src/include/rewrite/rewriteHandler.h
index daeaa373ad..3763504a01 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);
#endif /* REWRITEHANDLER_H */
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 16b0b1d2dc..22a38e189e 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -86,6 +86,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/expected/plperl_trigger.out
index 28011cd9f6..b5fc51a835 100644
--- a/src/pl/plperl/expected/plperl_trigger.out
+++ b/src/pl/plperl/expected/plperl_trigger.out
@@ -6,6 +6,11 @@ CREATE TABLE trigger_test (
v varchar,
foo rowcompnest
);
+CREATE TABLE trigger_test_generated (
+ i int,
+ j int GENERATED ALWAYS AS (i * 2) VIRTUAL,
+ k int GENERATED ALWAYS AS (i * 3) STORED
+);
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plperl AS $$
# make sure keys are sorted for consistent results - perl no longer
@@ -98,6 +103,79 @@ NOTICE: $_TD->{table_name} = 'trigger_test'
NOTICE: $_TD->{table_schema} = 'public'
NOTICE: $_TD->{when} = '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} = '0'
+NOTICE: $_TD->{event} = 'INSERT'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_before'
+NOTICE: $_TD->{new} = {'i' => '1'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'BEFORE'
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'INSERT'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_after'
+NOTICE: $_TD->{new} = {'i' => '1', 'k' => '3'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'AFTER'
+update trigger_test_generated set i = 11 where i = 1;
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'UPDATE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_before'
+NOTICE: $_TD->{new} = {'i' => '11'}
+NOTICE: $_TD->{old} = {'i' => '1', 'k' => '3'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'BEFORE'
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'UPDATE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_after'
+NOTICE: $_TD->{new} = {'i' => '11', 'k' => '33'}
+NOTICE: $_TD->{old} = {'i' => '1', 'k' => '3'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'AFTER'
+delete from trigger_test_generated;
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'DELETE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_before'
+NOTICE: $_TD->{old} = {'i' => '11', 'k' => '33'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'BEFORE'
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'DELETE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_after'
+NOTICE: $_TD->{old} = {'i' => '11', 'k' => '33'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = '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 +373,21 @@ NOTICE: perlsnitch: ddl_command_start DROP TABLE
NOTICE: perlsnitch: ddl_command_end DROP TABLE
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} = 5; # not allowed
+return 'MODIFY';
+$$;
+CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated
+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 | k
+---+---+---
+(0 rows)
+
diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index 35d5d121a0..b156baaa21 100644
--- a/src/pl/plperl/plperl.c
+++ b/src/pl/plperl/plperl.c
@@ -266,7 +266,7 @@ static plperl_proc_desc *compile_plperl_function(Oid fn_oid,
bool is_trigger,
bool is_event_trigger);
-static SV *plperl_hash_from_tuple(HeapTuple tuple, TupleDesc tupdesc);
+static SV *plperl_hash_from_tuple(HeapTuple tuple, TupleDesc tupdesc, bool include_generated);
static SV *plperl_hash_from_datum(Datum attr);
static SV *plperl_ref_from_pg_array(Datum arg, Oid typid);
static SV *split_array(plperl_array_info *info, int first, int last, int nest);
@@ -1644,13 +1644,19 @@ plperl_trigger_build_args(FunctionCallInfo fcinfo)
hv_store_string(hv, "name", cstr2sv(tdata->tg_trigger->tgname));
hv_store_string(hv, "relid", cstr2sv(relid));
+ /*
+ * Note: In BEFORE trigger, stored generated columns are not computed yet,
+ * so don't make them accessible in NEW row.
+ */
+
if (TRIGGER_FIRED_BY_INSERT(tdata->tg_event))
{
event = "INSERT";
if (TRIGGER_FIRED_FOR_ROW(tdata->tg_event))
hv_store_string(hv, "new",
plperl_hash_from_tuple(tdata->tg_trigtuple,
- tupdesc));
+ tupdesc,
+ !TRIGGER_FIRED_BEFORE(tdata->tg_event)));
}
else if (TRIGGER_FIRED_BY_DELETE(tdata->tg_event))
{
@@ -1658,7 +1664,8 @@ plperl_trigger_build_args(FunctionCallInfo fcinfo)
if (TRIGGER_FIRED_FOR_ROW(tdata->tg_event))
hv_store_string(hv, "old",
plperl_hash_from_tuple(tdata->tg_trigtuple,
- tupdesc));
+ tupdesc,
+ true));
}
else if (TRIGGER_FIRED_BY_UPDATE(tdata->tg_event))
{
@@ -1667,10 +1674,12 @@ plperl_trigger_build_args(FunctionCallInfo fcinfo)
{
hv_store_string(hv, "old",
plperl_hash_from_tuple(tdata->tg_trigtuple,
- tupdesc));
+ tupdesc,
+ true));
hv_store_string(hv, "new",
plperl_hash_from_tuple(tdata->tg_newtuple,
- tupdesc));
+ tupdesc,
+ !TRIGGER_FIRED_BEFORE(tdata->tg_event)));
}
}
else if (TRIGGER_FIRED_BY_TRUNCATE(tdata->tg_event))
@@ -1791,6 +1800,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)));
modvalues[attn - 1] = plperl_sv_to_datum(val,
attr->atttypid,
@@ -3012,7 +3026,7 @@ plperl_hash_from_datum(Datum attr)
tmptup.t_len = HeapTupleHeaderGetDatumLength(td);
tmptup.t_data = td;
- sv = plperl_hash_from_tuple(&tmptup, tupdesc);
+ sv = plperl_hash_from_tuple(&tmptup, tupdesc, true);
ReleaseTupleDesc(tupdesc);
return sv;
@@ -3020,7 +3034,7 @@ plperl_hash_from_datum(Datum attr)
/* Build a hash from all attributes of a given tuple. */
static SV *
-plperl_hash_from_tuple(HeapTuple tuple, TupleDesc tupdesc)
+plperl_hash_from_tuple(HeapTuple tuple, TupleDesc tupdesc, bool include_generated)
{
dTHX;
HV *hv;
@@ -3044,6 +3058,16 @@ plperl_hash_from_tuple(HeapTuple tuple, TupleDesc tupdesc)
if (att->attisdropped)
continue;
+ if (att->attgenerated)
+ {
+ /* don't include unless requested */
+ if (!include_generated)
+ continue;
+ /* never include virtual columns */
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ continue;
+ }
+
attname = NameStr(att->attname);
attr = heap_getattr(tuple, i + 1, tupdesc, &isnull);
@@ -3198,7 +3222,7 @@ plperl_spi_execute_fetch_result(SPITupleTable *tuptable, uint64 processed,
av_extend(rows, processed);
for (i = 0; i < processed; i++)
{
- row = plperl_hash_from_tuple(tuptable->vals[i], tuptable->tupdesc);
+ row = plperl_hash_from_tuple(tuptable->vals[i], tuptable->tupdesc, true);
av_push(rows, row);
}
hv_store_string(result, "rows",
@@ -3484,7 +3508,8 @@ plperl_spi_fetchrow(char *cursor)
else
{
row = plperl_hash_from_tuple(SPI_tuptable->vals[0],
- SPI_tuptable->tupdesc);
+ SPI_tuptable->tupdesc,
+ true);
}
SPI_freetuptable(SPI_tuptable);
}
diff --git a/src/pl/plperl/sql/plperl_trigger.sql b/src/pl/plperl/sql/plperl_trigger.sql
index 624193b9d0..7fa4a06ff5 100644
--- a/src/pl/plperl/sql/plperl_trigger.sql
+++ b/src/pl/plperl/sql/plperl_trigger.sql
@@ -8,6 +8,12 @@ CREATE TABLE trigger_test (
foo rowcompnest
);
+CREATE TABLE trigger_test_generated (
+ i int,
+ j int GENERATED ALWAYS AS (i * 2) VIRTUAL,
+ k int GENERATED ALWAYS AS (i * 3) STORED
+);
+
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plperl AS $$
# make sure keys are sorted for consistent results - perl no longer
@@ -70,6 +76,21 @@ CREATE TRIGGER show_trigger_data_trig
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);
+update trigger_test_generated set i = 11 where i = 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;
@@ -221,3 +242,19 @@ CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
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} = 5; # not allowed
+return 'MODIFY';
+$$;
+
+CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated
+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/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index a5aafa8c09..3ef4745fd1 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -924,6 +924,26 @@ plpgsql_exec_trigger(PLpgSQL_function *func,
false, false);
expanded_record_set_tuple(rec_old->erh, trigdata->tg_trigtuple,
false, false);
+
+ /*
+ * In BEFORE trigger, stored generated columns are not computed yet,
+ * so make them null in the NEW row. (Only needed in UPDATE branch;
+ * in the INSERT case, they are already null, but in UPDATE, the field
+ * still contains the old value.) Alternatively, we could construct a
+ * whole new row structure without the generated columns, but this way
+ * seems more efficient and potentially less confusing.
+ */
+ if (tupdesc->constr && tupdesc->constr->has_generated_stored &&
+ TRIGGER_FIRED_BEFORE(trigdata->tg_event))
+ {
+ for (int i = 0; i < tupdesc->natts; i++)
+ if (TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_STORED)
+ expanded_record_set_field_internal(rec_new->erh,
+ i + 1,
+ (Datum) 0,
+ true, /*isnull*/
+ false, false);
+ }
}
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
{
diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpython/expected/plpython_trigger.out
index d7ab8ac6b8..3e4f510d57 100644
--- a/src/pl/plpython/expected/plpython_trigger.out
+++ b/src/pl/plpython/expected/plpython_trigger.out
@@ -67,6 +67,11 @@ 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) VIRTUAL,
+ k int GENERATED ALWAYS AS (i * 3) STORED
+);
CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpythonu AS $$
if 'relid' in TD:
@@ -203,6 +208,77 @@ NOTICE: TD[when] => 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] => None
+NOTICE: TD[event] => INSERT
+NOTICE: TD[level] => ROW
+NOTICE: TD[name] => show_trigger_data_trig_before
+NOTICE: TD[new] => {'i': 1}
+NOTICE: TD[old] => None
+NOTICE: TD[relid] => bogus:12345
+NOTICE: TD[table_name] => trigger_test_generated
+NOTICE: TD[table_schema] => public
+NOTICE: TD[when] => BEFORE
+NOTICE: TD[args] => None
+NOTICE: TD[event] => INSERT
+NOTICE: TD[level] => ROW
+NOTICE: TD[name] => show_trigger_data_trig_after
+NOTICE: TD[new] => {'i': 1, 'k': 3}
+NOTICE: TD[old] => None
+NOTICE: TD[relid] => bogus:12345
+NOTICE: TD[table_name] => trigger_test_generated
+NOTICE: TD[table_schema] => public
+NOTICE: TD[when] => AFTER
+update trigger_test_generated set i = 11 where i = 1;
+NOTICE: TD[args] => None
+NOTICE: TD[event] => UPDATE
+NOTICE: TD[level] => ROW
+NOTICE: TD[name] => show_trigger_data_trig_before
+NOTICE: TD[new] => {'i': 11}
+NOTICE: TD[old] => {'i': 1, 'k': 3}
+NOTICE: TD[relid] => bogus:12345
+NOTICE: TD[table_name] => trigger_test_generated
+NOTICE: TD[table_schema] => public
+NOTICE: TD[when] => BEFORE
+NOTICE: TD[args] => None
+NOTICE: TD[event] => UPDATE
+NOTICE: TD[level] => ROW
+NOTICE: TD[name] => show_trigger_data_trig_after
+NOTICE: TD[new] => {'i': 11, 'k': 33}
+NOTICE: TD[old] => {'i': 1, 'k': 3}
+NOTICE: TD[relid] => bogus:12345
+NOTICE: TD[table_name] => trigger_test_generated
+NOTICE: TD[table_schema] => public
+NOTICE: TD[when] => AFTER
+delete from trigger_test_generated;
+NOTICE: TD[args] => None
+NOTICE: TD[event] => DELETE
+NOTICE: TD[level] => ROW
+NOTICE: TD[name] => show_trigger_data_trig_before
+NOTICE: TD[new] => None
+NOTICE: TD[old] => {'i': 11, 'k': 33}
+NOTICE: TD[relid] => bogus:12345
+NOTICE: TD[table_name] => trigger_test_generated
+NOTICE: TD[table_schema] => public
+NOTICE: TD[when] => BEFORE
+NOTICE: TD[args] => None
+NOTICE: TD[event] => DELETE
+NOTICE: TD[level] => ROW
+NOTICE: TD[name] => show_trigger_data_trig_after
+NOTICE: TD[new] => None
+NOTICE: TD[old] => {'i': 11, 'k': 33}
+NOTICE: TD[relid] => bogus:12345
+NOTICE: TD[table_name] => trigger_test_generated
+NOTICE: TD[table_schema] => public
+NOTICE: TD[when] => 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 +600,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'] = 5 # not allowed
+return 'MODIFY'
+$$;
+CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated
+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 | k
+---+---+---
+(0 rows)
+
diff --git a/src/pl/plpython/plpy_cursorobject.c b/src/pl/plpython/plpy_cursorobject.c
index 45ac25b2ae..e4d543a4d4 100644
--- a/src/pl/plpython/plpy_cursorobject.c
+++ b/src/pl/plpython/plpy_cursorobject.c
@@ -357,7 +357,7 @@ PLy_cursor_iternext(PyObject *self)
exec_ctx->curr_proc);
ret = PLy_input_from_tuple(&cursor->result, SPI_tuptable->vals[0],
- SPI_tuptable->tupdesc);
+ SPI_tuptable->tupdesc, true);
}
SPI_freetuptable(SPI_tuptable);
@@ -453,7 +453,8 @@ PLy_cursor_fetch(PyObject *self, PyObject *args)
{
PyObject *row = PLy_input_from_tuple(&cursor->result,
SPI_tuptable->vals[i],
- SPI_tuptable->tupdesc);
+ SPI_tuptable->tupdesc,
+ true);
PyList_SetItem(ret->rows, i, row);
}
diff --git a/src/pl/plpython/plpy_exec.c b/src/pl/plpython/plpy_exec.c
index 2137186241..fd6cdc4ce5 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"
@@ -751,6 +752,11 @@ PLy_trigger_build_args(FunctionCallInfo fcinfo, PLyProcedure *proc, HeapTuple *r
PyDict_SetItemString(pltdata, "level", pltlevel);
Py_DECREF(pltlevel);
+ /*
+ * Note: In BEFORE trigger, stored generated columns are not computed yet,
+ * so don't make them accessible in NEW row.
+ */
+
if (TRIGGER_FIRED_BY_INSERT(tdata->tg_event))
{
pltevent = PyString_FromString("INSERT");
@@ -758,7 +764,8 @@ PLy_trigger_build_args(FunctionCallInfo fcinfo, PLyProcedure *proc, HeapTuple *r
PyDict_SetItemString(pltdata, "old", Py_None);
pytnew = PLy_input_from_tuple(&proc->result_in,
tdata->tg_trigtuple,
- rel_descr);
+ rel_descr,
+ !TRIGGER_FIRED_BEFORE(tdata->tg_event));
PyDict_SetItemString(pltdata, "new", pytnew);
Py_DECREF(pytnew);
*rv = tdata->tg_trigtuple;
@@ -770,7 +777,8 @@ PLy_trigger_build_args(FunctionCallInfo fcinfo, PLyProcedure *proc, HeapTuple *r
PyDict_SetItemString(pltdata, "new", Py_None);
pytold = PLy_input_from_tuple(&proc->result_in,
tdata->tg_trigtuple,
- rel_descr);
+ rel_descr,
+ true);
PyDict_SetItemString(pltdata, "old", pytold);
Py_DECREF(pytold);
*rv = tdata->tg_trigtuple;
@@ -781,12 +789,14 @@ PLy_trigger_build_args(FunctionCallInfo fcinfo, PLyProcedure *proc, HeapTuple *r
pytnew = PLy_input_from_tuple(&proc->result_in,
tdata->tg_newtuple,
- rel_descr);
+ rel_descr,
+ !TRIGGER_FIRED_BEFORE(tdata->tg_event));
PyDict_SetItemString(pltdata, "new", pytnew);
Py_DECREF(pytnew);
pytold = PLy_input_from_tuple(&proc->result_in,
tdata->tg_trigtuple,
- rel_descr);
+ rel_descr,
+ true);
PyDict_SetItemString(pltdata, "old", pytold);
Py_DECREF(pytold);
*rv = tdata->tg_newtuple;
@@ -952,6 +962,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)));
plval = PyDict_GetItem(plntup, platt);
if (plval == NULL)
diff --git a/src/pl/plpython/plpy_spi.c b/src/pl/plpython/plpy_spi.c
index 41155fc81e..fb23a7b3a4 100644
--- a/src/pl/plpython/plpy_spi.c
+++ b/src/pl/plpython/plpy_spi.c
@@ -419,7 +419,8 @@ PLy_spi_execute_fetch_result(SPITupleTable *tuptable, uint64 rows, int status)
{
PyObject *row = PLy_input_from_tuple(&ininfo,
tuptable->vals[i],
- tuptable->tupdesc);
+ tuptable->tupdesc,
+ true);
PyList_SetItem(result->rows, i, row);
}
diff --git a/src/pl/plpython/plpy_typeio.c b/src/pl/plpython/plpy_typeio.c
index d6a6a849c3..eab7396398 100644
--- a/src/pl/plpython/plpy_typeio.c
+++ b/src/pl/plpython/plpy_typeio.c
@@ -41,7 +41,7 @@ static PyObject *PLyList_FromArray(PLyDatumToOb *arg, Datum d);
static PyObject *PLyList_FromArray_recurse(PLyDatumToOb *elm, int *dims, int ndim, int dim,
char **dataptr_p, bits8 **bitmap_p, int *bitmask_p);
static PyObject *PLyDict_FromComposite(PLyDatumToOb *arg, Datum d);
-static PyObject *PLyDict_FromTuple(PLyDatumToOb *arg, HeapTuple tuple, TupleDesc desc);
+static PyObject *PLyDict_FromTuple(PLyDatumToOb *arg, HeapTuple tuple, TupleDesc desc, bool include_generated);
/* conversion from Python objects to Datums */
static Datum PLyObject_ToBool(PLyObToDatum *arg, PyObject *plrv,
@@ -134,7 +134,7 @@ PLy_output_convert(PLyObToDatum *arg, PyObject *val, bool *isnull)
* but in practice all callers have the right tupdesc available.
*/
PyObject *
-PLy_input_from_tuple(PLyDatumToOb *arg, HeapTuple tuple, TupleDesc desc)
+PLy_input_from_tuple(PLyDatumToOb *arg, HeapTuple tuple, TupleDesc desc, bool include_generated)
{
PyObject *dict;
PLyExecutionContext *exec_ctx = PLy_current_execution_context();
@@ -148,7 +148,7 @@ PLy_input_from_tuple(PLyDatumToOb *arg, HeapTuple tuple, TupleDesc desc)
oldcontext = MemoryContextSwitchTo(scratch_context);
- dict = PLyDict_FromTuple(arg, tuple, desc);
+ dict = PLyDict_FromTuple(arg, tuple, desc, include_generated);
MemoryContextSwitchTo(oldcontext);
@@ -804,7 +804,7 @@ PLyDict_FromComposite(PLyDatumToOb *arg, Datum d)
tmptup.t_len = HeapTupleHeaderGetDatumLength(td);
tmptup.t_data = td;
- dict = PLyDict_FromTuple(arg, &tmptup, tupdesc);
+ dict = PLyDict_FromTuple(arg, &tmptup, tupdesc, true);
ReleaseTupleDesc(tupdesc);
@@ -815,7 +815,7 @@ PLyDict_FromComposite(PLyDatumToOb *arg, Datum d)
* Transform a tuple into a Python dict object.
*/
static PyObject *
-PLyDict_FromTuple(PLyDatumToOb *arg, HeapTuple tuple, TupleDesc desc)
+PLyDict_FromTuple(PLyDatumToOb *arg, HeapTuple tuple, TupleDesc desc, bool include_generated)
{
PyObject *volatile dict;
@@ -842,6 +842,16 @@ PLyDict_FromTuple(PLyDatumToOb *arg, HeapTuple tuple, TupleDesc desc)
if (attr->attisdropped)
continue;
+ if (attr->attgenerated)
+ {
+ /* don't include unless requested */
+ if (!include_generated)
+ continue;
+ /* never include virtual columns */
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ continue;
+ }
+
key = NameStr(attr->attname);
vattr = heap_getattr(tuple, (i + 1), desc, &is_null);
diff --git a/src/pl/plpython/plpy_typeio.h b/src/pl/plpython/plpy_typeio.h
index 82bdfae548..f210178238 100644
--- a/src/pl/plpython/plpy_typeio.h
+++ b/src/pl/plpython/plpy_typeio.h
@@ -151,7 +151,7 @@ extern Datum PLy_output_convert(PLyObToDatum *arg, PyObject *val,
bool *isnull);
extern PyObject *PLy_input_from_tuple(PLyDatumToOb *arg, HeapTuple tuple,
- TupleDesc desc);
+ TupleDesc desc, bool include_generated);
extern void PLy_input_setup_func(PLyDatumToOb *arg, MemoryContext arg_mcxt,
Oid typeOid, int32 typmod,
diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql
index 79c24b714b..07d49547d0 100644
--- a/src/pl/plpython/sql/plpython_trigger.sql
+++ b/src/pl/plpython/sql/plpython_trigger.sql
@@ -67,6 +67,12 @@ CREATE TRIGGER users_delete_trig BEFORE DELETE ON users FOR EACH ROW
CREATE TABLE trigger_test
(i int, v text );
+CREATE TABLE trigger_test_generated (
+ i int,
+ j int GENERATED ALWAYS AS (i * 2) VIRTUAL,
+ k int GENERATED ALWAYS AS (i * 3) STORED
+);
+
CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpythonu AS $$
if 'relid' in TD:
@@ -109,6 +115,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;
+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 = 11 where i = 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;
@@ -430,3 +451,20 @@ CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
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'] = 5 # not allowed
+return 'MODIFY'
+$$;
+
+CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated
+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 17e821bb4c..cb8937078a 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 k}
+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, k: 2}
+NOTICE: OLD: {}
+NOTICE: TG_level: ROW
+NOTICE: TG_name: show_trigger_data_trig_after
+NOTICE: TG_op: INSERT
+NOTICE: TG_relatts: {{} i j k}
+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 = 11 where i = 1;
+NOTICE: NEW: {i: 11}
+NOTICE: OLD: {i: 1, k: 2}
+NOTICE: TG_level: ROW
+NOTICE: TG_name: show_trigger_data_trig_before
+NOTICE: TG_op: UPDATE
+NOTICE: TG_relatts: {{} i j k}
+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, k: 22}
+NOTICE: OLD: {i: 1, k: 2}
+NOTICE: TG_level: ROW
+NOTICE: TG_name: show_trigger_data_trig_after
+NOTICE: TG_op: UPDATE
+NOTICE: TG_relatts: {{} i j k}
+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, k: 22}
+NOTICE: TG_level: ROW
+NOTICE: TG_name: show_trigger_data_trig_before
+NOTICE: TG_op: DELETE
+NOTICE: TG_relatts: {{} i j k}
+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, k: 22}
+NOTICE: TG_level: ROW
+NOTICE: TG_name: show_trigger_data_trig_after
+NOTICE: TG_op: DELETE
+NOTICE: TG_relatts: {{} i j k}
+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
@@ -760,3 +831,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_generated
+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 | k
+---+---+---
+(0 rows)
+
diff --git a/src/pl/tcl/expected/pltcl_setup.out b/src/pl/tcl/expected/pltcl_setup.out
index b10cf4e47d..06e985fb16 100644
--- a/src/pl/tcl/expected/pltcl_setup.out
+++ b/src/pl/tcl/expected/pltcl_setup.out
@@ -59,6 +59,11 @@ 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) VIRTUAL,
+ k int GENERATED ALWAYS AS (i * 2) STORED
+);
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 +115,12 @@ FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
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 76c9afc339..9e5c199179 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -324,7 +324,7 @@ static void pltcl_subtrans_abort(Tcl_Interp *interp,
static void pltcl_set_tuple_values(Tcl_Interp *interp, const char *arrayname,
uint64 tupno, HeapTuple tuple, TupleDesc tupdesc);
-static Tcl_Obj *pltcl_build_tuple_argument(HeapTuple tuple, TupleDesc tupdesc);
+static Tcl_Obj *pltcl_build_tuple_argument(HeapTuple tuple, TupleDesc tupdesc, bool include_generated);
static HeapTuple pltcl_build_tuple_result(Tcl_Interp *interp,
Tcl_Obj **kvObjv, int kvObjc,
pltcl_call_state *call_state);
@@ -889,7 +889,7 @@ pltcl_func_handler(PG_FUNCTION_ARGS, pltcl_call_state *call_state,
tmptup.t_len = HeapTupleHeaderGetDatumLength(td);
tmptup.t_data = td;
- list_tmp = pltcl_build_tuple_argument(&tmptup, tupdesc);
+ list_tmp = pltcl_build_tuple_argument(&tmptup, tupdesc, true);
Tcl_ListObjAppendElement(NULL, tcl_cmd, list_tmp);
ReleaseTupleDesc(tupdesc);
@@ -1060,7 +1060,6 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, pltcl_call_state *call_state,
volatile HeapTuple rettup;
Tcl_Obj *tcl_cmd;
Tcl_Obj *tcl_trigtup;
- Tcl_Obj *tcl_newtup;
int tcl_rc;
int i;
const char *result;
@@ -1162,20 +1161,22 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, pltcl_call_state *call_state,
Tcl_ListObjAppendElement(NULL, tcl_cmd,
Tcl_NewStringObj("ROW", -1));
- /* Build the data list for the trigtuple */
- tcl_trigtup = pltcl_build_tuple_argument(trigdata->tg_trigtuple,
- tupdesc);
-
/*
* Now the command part of the event for TG_op and data for NEW
* and OLD
+ *
+ * Note: In BEFORE trigger, stored generated columns are not computed yet,
+ * so don't make them accessible in NEW row.
*/
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
{
Tcl_ListObjAppendElement(NULL, tcl_cmd,
Tcl_NewStringObj("INSERT", -1));
- Tcl_ListObjAppendElement(NULL, tcl_cmd, tcl_trigtup);
+ Tcl_ListObjAppendElement(NULL, tcl_cmd,
+ pltcl_build_tuple_argument(trigdata->tg_trigtuple,
+ tupdesc,
+ !TRIGGER_FIRED_BEFORE(trigdata->tg_event)));
Tcl_ListObjAppendElement(NULL, tcl_cmd, Tcl_NewObj());
rettup = trigdata->tg_trigtuple;
@@ -1186,7 +1187,10 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, pltcl_call_state *call_state,
Tcl_NewStringObj("DELETE", -1));
Tcl_ListObjAppendElement(NULL, tcl_cmd, Tcl_NewObj());
- Tcl_ListObjAppendElement(NULL, tcl_cmd, tcl_trigtup);
+ Tcl_ListObjAppendElement(NULL, tcl_cmd,
+ pltcl_build_tuple_argument(trigdata->tg_trigtuple,
+ tupdesc,
+ true));
rettup = trigdata->tg_trigtuple;
}
@@ -1195,11 +1199,14 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, pltcl_call_state *call_state,
Tcl_ListObjAppendElement(NULL, tcl_cmd,
Tcl_NewStringObj("UPDATE", -1));
- tcl_newtup = pltcl_build_tuple_argument(trigdata->tg_newtuple,
- tupdesc);
-
- Tcl_ListObjAppendElement(NULL, tcl_cmd, tcl_newtup);
- Tcl_ListObjAppendElement(NULL, tcl_cmd, tcl_trigtup);
+ Tcl_ListObjAppendElement(NULL, tcl_cmd,
+ pltcl_build_tuple_argument(trigdata->tg_newtuple,
+ tupdesc,
+ !TRIGGER_FIRED_BEFORE(trigdata->tg_event)));
+ Tcl_ListObjAppendElement(NULL, tcl_cmd,
+ pltcl_build_tuple_argument(trigdata->tg_trigtuple,
+ tupdesc,
+ true));
rettup = trigdata->tg_newtuple;
}
@@ -3091,7 +3098,7 @@ pltcl_set_tuple_values(Tcl_Interp *interp, const char *arrayname,
* from all attributes of a given tuple
**********************************************************************/
static Tcl_Obj *
-pltcl_build_tuple_argument(HeapTuple tuple, TupleDesc tupdesc)
+pltcl_build_tuple_argument(HeapTuple tuple, TupleDesc tupdesc, bool include_generated)
{
Tcl_Obj *retobj = Tcl_NewObj();
int i;
@@ -3110,6 +3117,16 @@ pltcl_build_tuple_argument(HeapTuple tuple, TupleDesc tupdesc)
if (att->attisdropped)
continue;
+ if (att->attgenerated)
+ {
+ /* don't include unless requested */
+ if (!include_generated)
+ continue;
+ /* never include virtual columns */
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ continue;
+ }
+
/************************************************************
* Get the attribute name
************************************************************/
@@ -3219,6 +3236,12 @@ pltcl_build_tuple_result(Tcl_Interp *interp, Tcl_Obj **kvObjv, int kvObjc,
errmsg("cannot set system attribute \"%s\"",
fieldName)));
+ 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] = utf_u2e(Tcl_GetString(kvObjv[i + 1]));
}
diff --git a/src/pl/tcl/sql/pltcl_queries.sql b/src/pl/tcl/sql/pltcl_queries.sql
index 7390de6bd6..e977e11a76 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');
+insert into trigger_test_generated (i) values (1);
+update trigger_test_generated set i = 11 where i = 1;
+delete from trigger_test_generated;
+
insert into trigger_test_view values(2,'insert');
update trigger_test_view set v = 'update' where i=1;
delete from trigger_test_view;
@@ -85,6 +89,9 @@
delete from trigger_test;
truncate trigger_test;
+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'));
@@ -273,3 +280,21 @@ CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
update transition_table_test set name = '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_generated
+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 0ea46134c7..6344447b47 100644
--- a/src/pl/tcl/sql/pltcl_setup.sql
+++ b/src/pl/tcl/sql/pltcl_setup.sql
@@ -68,6 +68,12 @@ 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) VIRTUAL,
+ k int GENERATED ALWAYS AS (i * 2) STORED
+);
+
CREATE VIEW trigger_test_view AS SELECT i, v FROM trigger_test;
CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$
@@ -122,6 +128,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');
+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/regress/expected/create_table_like.out
index b582211270..df5c3695ed 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 copied and applied
(1 row)
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
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+
+INSERT INTO test_like_gen_1 (a) VALUES (1);
+SELECT * FROM test_like_gen_1;
+ a | b
+---+---
+ 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
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+
+INSERT INTO test_like_gen_2 (a) VALUES (1);
+SELECT * FROM test_like_gen_2;
+ a | b
+---+---
+ 1 |
+(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
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+
+INSERT INTO test_like_gen_3 (a) VALUES (1);
+SELECT * FROM test_like_gen_3;
+ a | b
+---+---
+ 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); /* copies 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/expected/generated.out
new file mode 100644
index 0000000000..59596f5017
--- /dev/null
+++ b/src/test/regress/expected/generated.out
@@ -0,0 +1,873 @@
+-- sanity check of system catalog
+SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
+ attrelid | attname | attgenerated
+----------+---------+--------------
+(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_generated, 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
+------------+-------------+----------------+-------------+--------------+-----------------------
+ gtest0 | a | | NO | NEVER |
+ gtest0 | b | | YES | ALWAYS | 55
+ gtest1 | a | | NO | NEVER |
+ gtest1 | b | | YES | ALWAYS | (a * 2)
+(4 rows)
+
+SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage ORDER BY 1, 2, 3;
+ table_name | column_name | dependent_column
+------------+-------------+------------------
+ gtest1 | a | b
+(1 row)
+
+\d gtest1
+ Table "public.gtest1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | not null |
+ 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 "gtest_err_1"
+LINE 1: ...nt PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) GENERATED ...
+ ^
+-- 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));
+ ^
+-- generation expression must be immutable
+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()));
+ERROR: generation expression is not immutable
+-- 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 ...
+ ^
+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 ...
+ ^
+-- reference to system column not allowed in generated column
+CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) STORED);
+ERROR: cannot use system column "xmin" in column generation expression
+LINE 1: ...a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37...
+ ^
+CREATE TABLE gtest_err_6b (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) VIRTUAL);
+ERROR: cannot use system column "xmin" in column generation expression
+LINE 1: ...b (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37...
+ ^
+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
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+UPDATE gtest1 SET b = DEFAULT WHERE a = 1;
+UPDATE gtest1 SET b = 11 WHERE a = 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
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a;
+ a | b | b2
+---+---+----
+ 1 | 2 | 4
+ 2 | 4 | 8
+(2 rows)
+
+SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;
+ a | b
+---+---
+ 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 = 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 = gtest1.a;
+ x | y | a | b
+----+---+---+---
+ 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
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+UPDATE gtest1 SET a = 3 WHERE b = 4;
+SELECT * FROM gtest1 ORDER BY a;
+ a | b
+---+---
+ 1 | 2
+ 3 | 6
+(2 rows)
+
+DELETE FROM gtest1 WHERE b = 2;
+SELECT * FROM gtest1 ORDER BY a;
+ a | b
+---+---
+ 3 | 6
+(1 row)
+
+-- views
+CREATE VIEW gtest1v AS SELECT * FROM gtest1;
+SELECT * FROM gtest1v;
+ a | b
+---+---
+ 3 | 6
+(1 row)
+
+INSERT INTO gtest1v VALUES (4, 8); -- fails
+ERROR: cannot insert into column "b"
+DETAIL: Column "b" is a generated column.
+-- CTEs
+WITH foo AS (SELECT * FROM gtest1) SELECT * FROM foo;
+ a | b
+---+---
+ 3 | 6
+(1 row)
+
+-- inheritance
+CREATE TABLE gtest1_1 () INHERITS (gtest1);
+SELECT * FROM gtest1_1;
+ a | b
+---+---
+(0 rows)
+
+\d gtest1_1
+ Table "public.gtest1_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | not null |
+ b | integer | | | generated always as (a * 2)
+Inherits: gtest1
+
+INSERT INTO gtest1_1 VALUES (4);
+SELECT * FROM gtest1_1;
+ a | b
+---+---
+ 4 | 8
+(1 row)
+
+SELECT * FROM gtest1;
+ a | b
+---+---
+ 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
+---+---
+ 1 | 3
+ 2 | 6
+ 3 | 9
+(3 rows)
+
+UPDATE gtest3 SET a = 22 WHERE a = 2;
+SELECT * FROM gtest3 ORDER BY a;
+ a | b
+----+----
+ 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
+---+---
+ 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
+---+----
+ 1 | 3
+ 2 | 6
+ 3 | 9
+ 4 | 12
+(4 rows)
+
+-- null values
+CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL));
+INSERT INTO gtest2 VALUES (1);
+SELECT * FROM gtest2;
+ a | b
+---+---
+ 1 |
+(1 row)
+
+-- composite types
+CREATE TYPE double_int as (a int, b int);
+CREATE TABLE gtest4 (
+ a int,
+ b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) STORED,
+ c double_int GENERATED ALWAYS AS ((a * 4, a * 5)) VIRTUAL
+);
+INSERT INTO gtest4 VALUES (1), (6);
+SELECT * FROM gtest4;
+ a | b | c
+---+---------+---------
+ 1 | (2,3) | (4,5)
+ 6 | (12,18) | (24,30)
+(2 rows)
+
+DROP TABLE gtest4;
+DROP TYPE double_int;
+-- using tableoid is allowed
+CREATE TABLE gtest_tableoid (
+ a int PRIMARY KEY,
+ b bool GENERATED ALWAYS AS (tableoid <> 0) STORED,
+ c bool GENERATED ALWAYS AS (tableoid <> 0) VIRTUAL
+);
+INSERT INTO gtest_tableoid VALUES (1), (2);
+SELECT * FROM gtest_tableoid;
+ a | b | c
+---+---+---
+ 1 | t | t
+ 2 | t | t
+(2 rows)
+
+-- drop column behavior
+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2));
+ALTER TABLE gtest10 DROP COLUMN b;
+\d gtest10
+ Table "public.gtest10"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+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 LANGUAGE 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
+---+----
+ 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
+---+----
+ 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
+---+----
+ 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 "gtest20_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 (nullif(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 "gtest21a_b_check"
+DETAIL: Failing row contains (0).
+CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(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 (nullif(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 (nullif(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
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ 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 = 6;
+ QUERY PLAN
+-------------------------------
+ Seq Scan on gtest22c
+ Filter: (((a * 2) * 3) = 6)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE b * 3 = 6;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+ QUERY PLAN
+---------------------------------------
+ Seq Scan on gtest22c
+ Filter: ((a = 1) AND ((a * 2) > 0))
+(2 rows)
+
+SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+ a | b
+---+---
+ 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
+--------+---------+-----------+----------+------------------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2) stored
+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 = 4;
+ QUERY PLAN
+---------------------------------------------
+ Index Scan using gtest22d_b_idx on gtest22d
+ Index Cond: (b = 4)
+(2 rows)
+
+SELECT * FROM gtest22d WHERE b = 4;
+ a | b
+---+---
+ 2 | 4
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE b * 3 = 6;
+ QUERY PLAN
+------------------------------------------------
+ Index Scan using gtest22d_expr_idx on gtest22d
+ Index Cond: ((b * 3) = 6)
+(2 rows)
+
+SELECT * FROM gtest22d WHERE b * 3 = 6;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE a = 1 AND b > 0;
+ QUERY PLAN
+------------------------------------------------
+ Index Scan using gtest22d_pred_idx on gtest22d
+ Index Cond: (a = 1)
+(2 rows)
+
+SELECT * FROM gtest22d WHERE a = 1 AND b > 0;
+ a | b
+---+---
+ 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 generated 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 generated 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 supported
+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
+--------+---------+-----------+----------+------------------------------------
+ a | integer | | not null |
+ b | integer | | | generated always as (a * 2) stored
+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 constraint "gtest23c_b_fkey"
+DETAIL: Key (b)=(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 constraint "gtest23q_b_fkey"
+DETAIL: Key (b)=(5) is not present in table "gtest23p".
+-- no test for PK using virtual column, since such an index cannot be created
+-- 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 columns 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) PARTITION 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 GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
+SELECT * FROM gtest_parent;
+ f1 | f2 | f3
+------------+----+----
+ 07-15-2016 | 1 | 2
+(1 row)
+
+SELECT * FROM gtest_child;
+ f1 | f2 | f3
+------------+----+----
+ 07-15-2016 | 1 | 2
+(1 row)
+
+DROP TABLE gtest_parent;
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
+SELECT * FROM gtest_parent;
+ f1 | f2 | f3
+------------+----+----
+ 07-15-2016 | 1 | 2
+(1 row)
+
+SELECT * FROM gtest_child;
+ f1 | f2 | f3
+------------+----+----
+ 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 GENERATED 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 GENERATED 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 GENERATED 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);
+ALTER TABLE gtest25 ADD COLUMN c int GENERATED ALWAYS AS (a * 5) STORED;
+SELECT * FROM gtest25 ORDER BY a;
+ a | b | c
+---+----+----
+ 3 | 9 | 15
+ 4 | 12 | 20
+(2 rows)
+
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4); -- error
+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 (c * 4); -- error
+ERROR: cannot use generated column "c" 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); -- error
+ERROR: column "z" does not exist
+-- ALTER TABLE ... ALTER COLUMN
+CREATE TABLE gtest27 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2),
+ c int GENERATED ALWAYS AS (a * 3) STORED
+);
+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 "c".
+ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric;
+ALTER TABLE gtest27 ALTER COLUMN c TYPE numeric;
+\d gtest27
+ Table "public.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+--------------------------------------
+ a | integer | | |
+ b | numeric | | | generated always as ((a * 2))
+ c | numeric | | | generated always as ((a * 3)) stored
+
+SELECT * FROM gtest27;
+ a | b | c
+---+---+----
+ 3 | 6 | 9
+ 4 | 8 | 12
+(2 rows)
+
+ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean; -- error
+ERROR: generation expression for column "b" cannot be cast automatically 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
+--------+---------+-----------+----------+--------------------------------------
+ a | integer | | |
+ b | numeric | | | generated always as ((a * 2))
+ c | numeric | | | generated always as ((a * 3)) stored
+
+-- triggers
+CREATE TABLE gtest26 (
+ a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL,
+ c int GENERATED ALWAYS AS (a * 3) STORED
+);
+CREATE FUNCTION gtest_trigger_func() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ IF tg_op IN ('DELETE', 'UPDATE') THEN
+ RAISE INFO '%: %: old = %', TG_NAME, TG_WHEN, OLD;
+ END IF;
+ IF tg_op IN ('INSERT', 'UPDATE') THEN
+ RAISE INFO '%: %: new = %', TG_NAME, TG_WHEN, NEW;
+ END IF;
+ IF tg_op = '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 gtest2a 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 columns
+LINE 3: WHEN (NEW.b < 0)
+ ^
+DETAIL: Column "b" is a generated column.
+CREATE TRIGGER gtest2b BEFORE INSERT OR UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (NEW.* IS NOT NULL) -- error
+ EXECUTE PROCEDURE gtest_trigger_func();
+ERROR: BEFORE trigger's WHEN condition cannot reference NEW generated columns
+LINE 3: WHEN (NEW.* IS NOT NULL)
+ ^
+DETAIL: A whole-row reference is used and the table contains generated columns.
+CREATE TRIGGER gtest2 BEFORE INSERT ON gtest26
+ FOR EACH ROW
+ WHEN (NEW.a < 0)
+ 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);
+INFO: gtest2: BEFORE: new = (-2,,)
+INFO: gtest4: AFTER: new = (-2,,-6)
+SELECT * FROM gtest26 ORDER BY a;
+ a | b | c
+----+----+----
+ -2 | -4 | -6
+ 0 | 0 | 0
+ 3 | 6 | 9
+(3 rows)
+
+UPDATE gtest26 SET a = a * -2;
+INFO: gtest1: BEFORE: old = (-2,,-6)
+INFO: gtest1: BEFORE: new = (4,,)
+INFO: gtest3: AFTER: old = (-2,,-6)
+INFO: gtest3: AFTER: new = (4,,12)
+INFO: gtest4: AFTER: old = (3,,9)
+INFO: gtest4: AFTER: new = (-6,,-18)
+SELECT * FROM gtest26 ORDER BY a;
+ a | b | c
+----+-----+-----
+ -6 | -12 | -18
+ 0 | 0 | 0
+ 4 | 8 | 12
+(3 rows)
+
+DELETE FROM gtest26 WHERE a = -6;
+INFO: gtest1: BEFORE: old = (-6,,-18)
+INFO: gtest3: AFTER: old = (-6,,-18)
+SELECT * FROM gtest26 ORDER BY a;
+ a | b | c
+---+---+----
+ 0 | 0 | 0
+ 4 | 8 | 12
+(2 rows)
+
+DROP TRIGGER gtest1 ON gtest26;
+DROP TRIGGER gtest2 ON gtest26;
+DROP TRIGGER gtest3 ON gtest26;
+-- check disallowed modification of virtual columns
+CREATE FUNCTION gtest_trigger_func2() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ NEW.b := 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 = 1 WHERE a = 0;
+ERROR: trigger modified virtual generated column value
+DROP TRIGGER gtest10 ON gtest26;
+-- Check that an UPDATE of "a" fires the trigger for UPDATE OF b, per
+-- SQL standard.
+CREATE FUNCTION gtest_trigger_func3() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ RAISE NOTICE 'OK';
+ RETURN NEW;
+END
+$$;
+CREATE TRIGGER gtest11 BEFORE UPDATE OF b ON gtest26
+ FOR EACH ROW
+ EXECUTE PROCEDURE gtest_trigger_func3();
+UPDATE gtest26 SET a = 1 WHERE a = 0;
+NOTICE: OK
+DROP TRIGGER gtest11 ON gtest26;
+TRUNCATE gtest26;
+-- check that modifications of stored generated columns in triggers do
+-- not get propagated
+CREATE FUNCTION gtest_trigger_func4() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ NEW.a = 10;
+ NEW.c = 300;
+ RETURN NEW;
+END;
+$$;
+CREATE TRIGGER gtest12_01 BEFORE UPDATE ON gtest26
+ FOR EACH ROW
+ EXECUTE PROCEDURE gtest_trigger_func();
+CREATE TRIGGER gtest12_02 BEFORE UPDATE ON gtest26
+ FOR EACH ROW
+ EXECUTE PROCEDURE gtest_trigger_func4();
+CREATE TRIGGER gtest12_03 BEFORE UPDATE ON gtest26
+ FOR EACH ROW
+ EXECUTE PROCEDURE gtest_trigger_func();
+INSERT INTO gtest26 (a) VALUES (1);
+UPDATE gtest26 SET a = 11 WHERE a = 1;
+INFO: gtest12_01: BEFORE: old = (1,,3)
+INFO: gtest12_01: BEFORE: new = (11,,)
+INFO: gtest12_03: BEFORE: old = (1,,3)
+INFO: gtest12_03: BEFORE: new = (10,,)
+SELECT * FROM gtest26 ORDER BY a;
+ a | b | c
+----+----+----
+ 10 | 20 | 30
+(1 row)
+
+-- 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
+--------+---------+-----------+----------+-----------------------------
+ b | integer | | |
+ c | integer | | |
+ x | integer | | | generated always as (b * 2)
+
+ Table "public.gtest28b"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ b | integer | | |
+ c | integer | | |
+ x | integer | | | generated always as (b * 2)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4051a4ad4e..2f97856d66 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -122,7 +122,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr
# ----------
# Another group of parallel tests
# ----------
-test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info
+test: identity generated partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info
# 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_schedule
index ac1ea622d6..274279eb00 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -178,6 +178,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/regress/sql/create_table_like.sql
index 65c3880792..d002cb07b3 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 INCLUDING 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;
+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); /* copies 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/generated.sql
new file mode 100644
index 0000000000..1f18a594e4
--- /dev/null
+++ b/src/test/regress/sql/generated.sql
@@ -0,0 +1,517 @@
+-- sanity check of system catalog
+SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated 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_generated, 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.column_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));
+
+-- generation expression must be immutable
+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED 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));
+
+-- reference to system column not allowed in generated column
+CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) STORED);
+CREATE TABLE gtest_err_6b (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) VIRTUAL);
+
+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 = DEFAULT WHERE a = 1;
+UPDATE gtest1 SET b = 11 WHERE a = 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 = 4 ORDER BY a;
+
+-- test that overflow error happens on read
+INSERT INTO gtest1 VALUES (2000000000);
+SELECT * FROM gtest1;
+DELETE FROM gtest1 WHERE a = 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 = gtest1.a;
+DROP TABLE gtestx;
+
+-- test UPDATE/DELETE quals
+SELECT * FROM gtest1 ORDER BY a;
+UPDATE gtest1 SET a = 3 WHERE b = 4;
+SELECT * FROM gtest1 ORDER BY a;
+DELETE FROM gtest1 WHERE b = 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
+
+-- CTEs
+WITH foo AS (SELECT * FROM gtest1) SELECT * FROM foo;
+
+-- 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 = 22 WHERE a = 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;
+
+-- null values
+CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL));
+INSERT INTO gtest2 VALUES (1);
+SELECT * FROM gtest2;
+
+-- composite types
+CREATE TYPE double_int as (a int, b int);
+CREATE TABLE gtest4 (
+ a int,
+ b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) STORED,
+ c double_int GENERATED ALWAYS AS ((a * 4, a * 5)) VIRTUAL
+);
+INSERT INTO gtest4 VALUES (1), (6);
+SELECT * FROM gtest4;
+
+DROP TABLE gtest4;
+DROP TYPE double_int;
+
+-- using tableoid is allowed
+CREATE TABLE gtest_tableoid (
+ a int PRIMARY KEY,
+ b bool GENERATED ALWAYS AS (tableoid <> 0) STORED,
+ c bool GENERATED ALWAYS AS (tableoid <> 0) VIRTUAL
+);
+INSERT INTO gtest_tableoid VALUES (1), (2);
+SELECT * FROM gtest_tableoid;
+
+-- drop column behavior
+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (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 LANGUAGE 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 (nullif(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 (nullif(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 (nullif(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 (nullif(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 = 6;
+SELECT * FROM gtest22c WHERE b * 3 = 6;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+SELECT * FROM gtest22c WHERE a = 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 = 4;
+SELECT * FROM gtest22d WHERE b = 4;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE b * 3 = 6;
+SELECT * FROM gtest22d WHERE b * 3 = 6;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE a = 1 AND b > 0;
+SELECT * FROM gtest22d WHERE a = 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 created
+
+-- 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) PARTITION 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 GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('2016-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 GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('2016-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 GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f3);
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE ((f3 * 3));
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED 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);
+ALTER TABLE gtest25 ADD COLUMN c int GENERATED ALWAYS AS (a * 5) STORED;
+SELECT * FROM gtest25 ORDER BY a;
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4); -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (c * 4); -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4); -- error
+
+-- ALTER TABLE ... ALTER COLUMN
+CREATE TABLE gtest27 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2),
+ c int GENERATED ALWAYS AS (a * 3) STORED
+);
+INSERT INTO gtest27 (a) VALUES (3), (4);
+ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
+ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric;
+ALTER TABLE gtest27 ALTER COLUMN c 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) VIRTUAL,
+ c int GENERATED ALWAYS AS (a * 3) STORED
+);
+
+CREATE FUNCTION gtest_trigger_func() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ IF tg_op IN ('DELETE', 'UPDATE') THEN
+ RAISE INFO '%: %: old = %', TG_NAME, TG_WHEN, OLD;
+ END IF;
+ IF tg_op IN ('INSERT', 'UPDATE') THEN
+ RAISE INFO '%: %: new = %', TG_NAME, TG_WHEN, NEW;
+ END IF;
+ IF tg_op = '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 gtest2a BEFORE INSERT OR UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (NEW.b < 0) -- error
+ EXECUTE PROCEDURE gtest_trigger_func();
+
+CREATE TRIGGER gtest2b BEFORE INSERT OR UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (NEW.* IS NOT NULL) -- error
+ EXECUTE PROCEDURE gtest_trigger_func();
+
+CREATE TRIGGER gtest2 BEFORE INSERT ON gtest26
+ FOR EACH ROW
+ WHEN (NEW.a < 0)
+ 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 = a * -2;
+SELECT * FROM gtest26 ORDER BY a;
+DELETE FROM gtest26 WHERE a = -6;
+SELECT * FROM gtest26 ORDER BY a;
+
+DROP TRIGGER gtest1 ON gtest26;
+DROP TRIGGER gtest2 ON gtest26;
+DROP TRIGGER gtest3 ON gtest26;
+
+-- check disallowed modification of virtual columns
+CREATE FUNCTION gtest_trigger_func2() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ NEW.b := 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 = 1 WHERE a = 0;
+
+DROP TRIGGER gtest10 ON gtest26;
+
+-- Check that an UPDATE of "a" fires the trigger for UPDATE OF b, per
+-- SQL standard.
+CREATE FUNCTION gtest_trigger_func3() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ RAISE NOTICE 'OK';
+ RETURN NEW;
+END
+$$;
+
+CREATE TRIGGER gtest11 BEFORE UPDATE OF b ON gtest26
+ FOR EACH ROW
+ EXECUTE PROCEDURE gtest_trigger_func3();
+
+UPDATE gtest26 SET a = 1 WHERE a = 0;
+
+DROP TRIGGER gtest11 ON gtest26;
+TRUNCATE gtest26;
+
+-- check that modifications of stored generated columns in triggers do
+-- not get propagated
+CREATE FUNCTION gtest_trigger_func4() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ NEW.a = 10;
+ NEW.c = 300;
+ RETURN NEW;
+END;
+$$;
+
+CREATE TRIGGER gtest12_01 BEFORE UPDATE ON gtest26
+ FOR EACH ROW
+ EXECUTE PROCEDURE gtest_trigger_func();
+
+CREATE TRIGGER gtest12_02 BEFORE UPDATE ON gtest26
+ FOR EACH ROW
+ EXECUTE PROCEDURE gtest_trigger_func4();
+
+CREATE TRIGGER gtest12_03 BEFORE UPDATE ON gtest26
+ FOR EACH ROW
+ EXECUTE PROCEDURE gtest_trigger_func();
+
+INSERT INTO gtest26 (a) VALUES (1);
+UPDATE gtest26 SET a = 11 WHERE a = 1;
+SELECT * FROM gtest26 ORDER BY a;
+
+-- 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*
diff --git a/src/test/subscription/t/011_generated.pl b/src/test/subscription/t/011_generated.pl
new file mode 100644
index 0000000000..7bd1e88317
--- /dev/null
+++ b/src/test/subscription/t/011_generated.pl
@@ -0,0 +1,65 @@
+# Test generated columns
+use strict;
+use warnings;
+use PostgresNode;
+use TestLib;
+use Test::More tests => 2;
+
+# setup
+
+my $node_publisher = get_new_node('publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+my $node_subscriber = get_new_node('subscriber');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->start;
+
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c int GENERATED ALWAYS AS (a * 3) STORED)");
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE tab1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 22) VIRTUAL, c int GENERATED ALWAYS AS (a * 33) STORED)");
+
+# data for initial sync
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab1 (a) VALUES (1), (2), (3)");
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION pub1 FOR ALL TABLES");
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr application_name=sub1' PUBLICATION pub1"
+);
+
+# Wait for initial sync of all subscriptions
+my $synced_query =
+ "SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's');";
+$node_subscriber->poll_query_until('postgres', $synced_query)
+ or die "Timed out while waiting for subscriber to synchronize data";
+
+my $result = $node_subscriber->safe_psql('postgres',
+ "SELECT a, b, c FROM tab1");
+is($result, qq(1|22|33
+2|44|66
+3|66|99), 'generated columns initial sync');
+
+# data to replicate
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab1 VALUES (4), (5)");
+
+$node_publisher->safe_psql('postgres',
+ "UPDATE tab1 SET a = 6 WHERE a = 5");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT a, b, c FROM tab1");
+is($result, qq(1|22|33
+2|44|66
+3|66|99
+4|88|132
+6|132|198), 'generated columns replicated');
base-commit: bc09d5e4cc1813c9af60c4537fe7d70ed1baae11
--
2.20.1