v4-0001-misc.no-cfbot
application/octet-stream
Filename: v4-0001-misc.no-cfbot
Type: application/octet-stream
Part: 0
Message:
Re: Virtual generated columns
From 0169f9e1cb3f83d75f1697f47825977a36afa63e Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 29 Aug 2024 16:37:27 +0800
Subject: [PATCH v4 1/1] misc
---
contrib/pageinspect/expected/page.out | 35 +
contrib/pageinspect/sql/page.sql | 17 +
.../postgres_fdw/expected/postgres_fdw.out | 81 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 9 +-
doc/src/sgml/catalogs.sgml | 4 +-
doc/src/sgml/ddl.sgml | 25 +-
doc/src/sgml/ref/alter_table.sgml | 14 +-
doc/src/sgml/ref/create_foreign_table.sgml | 11 +-
doc/src/sgml/ref/create_table.sgml | 22 +-
doc/src/sgml/ref/create_trigger.sgml | 2 +-
doc/src/sgml/trigger.sgml | 4 +
src/backend/access/common/tupdesc.c | 3 +
src/backend/access/heap/heapam_handler.c | 2 +
src/backend/catalog/heap.c | 13 +-
src/backend/commands/analyze.c | 4 +
src/backend/commands/functioncmds.c | 35 +-
src/backend/commands/indexcmds.c | 29 +-
src/backend/commands/statscmds.c | 20 +-
src/backend/commands/tablecmds.c | 118 +-
src/backend/commands/trigger.c | 48 +-
src/backend/commands/view.c | 3 +
src/backend/executor/execExprInterp.c | 4 +
src/backend/executor/execMain.c | 16 +-
src/backend/parser/analyze.c | 8 +
src/backend/parser/gram.y | 15 +-
src/backend/parser/parse_clause.c | 4 +
src/backend/parser/parse_merge.c | 1 +
src/backend/parser/parse_relation.c | 9 +-
src/backend/parser/parse_utilcmd.c | 14 +-
src/backend/replication/pgoutput/pgoutput.c | 3 +-
src/backend/rewrite/rewriteHandler.c | 316 +++-
src/backend/utils/adt/varlena.c | 18 +-
src/backend/utils/cache/partcache.c | 3 +
src/backend/utils/cache/relcache.c | 2 +
src/bin/pg_dump/pg_dump.c | 3 +
src/bin/pg_dump/t/002_pg_dump.pl | 6 +-
src/bin/psql/describe.c | 6 +
src/include/access/tupdesc.h | 1 +
src/include/catalog/heap.h | 1 +
src/include/catalog/pg_attribute.h | 1 +
src/include/catalog/pg_extension.h | 3 -
src/include/nodes/parsenodes.h | 3 +
src/include/parser/kwlist.h | 1 +
src/include/parser/parse_node.h | 1 +
src/include/rewrite/rewriteHandler.h | 2 +
src/pl/plperl/expected/plperl_trigger.out | 7 +-
src/pl/plperl/plperl.c | 3 +
src/pl/plperl/sql/plperl_trigger.sql | 3 +-
src/pl/plpython/expected/plpython_trigger.out | 7 +-
src/pl/plpython/plpy_typeio.c | 3 +
src/pl/plpython/sql/plpython_trigger.sql | 3 +-
src/pl/tcl/expected/pltcl_trigger.out | 19 +-
src/pl/tcl/pltcl.c | 3 +
src/pl/tcl/sql/pltcl_trigger.sql | 3 +-
.../regress/expected/create_table_like.out | 23 +-
.../{generated.out => generated_stored.out} | 103 +-
.../regress/expected/generated_virtual.out | 1363 +++++++++++++++++
src/test/regress/parallel_schedule | 5 +-
src/test/regress/sql/create_table_like.sql | 2 +-
.../{generated.sql => generated_stored.sql} | 22 +-
src/test/regress/sql/generated_virtual.sql | 726 +++++++++
src/test/subscription/t/011_generated.pl | 38 +-
62 files changed, 3052 insertions(+), 221 deletions(-)
rename src/test/regress/expected/{generated.out => generated_stored.out} (94%)
create mode 100644 src/test/regress/expected/generated_virtual.out
rename src/test/regress/sql/{generated.sql => generated_stored.sql} (96%)
create mode 100644 src/test/regress/sql/generated_virtual.sql
diff --git a/contrib/pageinspect/expected/page.out b/contrib/pageinspect/expected/page.out
index 80ddb45a60..7e0b09e279 100644
--- a/contrib/pageinspect/expected/page.out
+++ b/contrib/pageinspect/expected/page.out
@@ -208,6 +208,41 @@ select tuple_data_split('test8'::regclass, t_data, t_infomask, t_infomask2, t_bi
(1 row)
drop table test8;
+-- check storage of generated columns
+-- stored
+create table test9s (a int not null, b int generated always as (a * 2) stored);
+insert into test9s values (131584);
+select t_infomask, t_bits, t_data from heap_page_items(get_raw_page('test9s', 0));
+ t_infomask | t_bits | t_data
+------------+--------+--------------------
+ 2048 | | \x0002020000040400
+(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
+-------------------------------
+ {"\\x00020200","\\x00040400"}
+(1 row)
+
+drop table test9s;
+-- virtual
+create table test9v (a int not null, b int generated always as (a * 2) virtual);
+insert into test9v values (131584);
+select t_infomask, t_bits, t_data from heap_page_items(get_raw_page('test9v', 0));
+ t_infomask | t_bits | t_data
+------------+----------+------------
+ 2049 | 10000000 | \x00020200
+(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
+----------------------
+ {"\\x00020200",NULL}
+(1 row)
+
+drop table test9v;
-- Failure with incorrect page size
-- Suppress the DETAIL message, to allow the tests to work across various
-- page sizes.
diff --git a/contrib/pageinspect/sql/page.sql b/contrib/pageinspect/sql/page.sql
index 5bff568d3b..186dda1e8d 100644
--- a/contrib/pageinspect/sql/page.sql
+++ b/contrib/pageinspect/sql/page.sql
@@ -84,6 +84,23 @@ select tuple_data_split('test8'::regclass, t_data, t_infomask, t_infomask2, t_bi
from heap_page_items(get_raw_page('test8', 0));
drop table test8;
+-- check storage of generated columns
+-- stored
+create table test9s (a int not null, b int generated always as (a * 2) stored);
+insert into test9s values (131584);
+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;
+
+-- virtual
+create table test9v (a int not null, b int generated always as (a * 2) virtual);
+insert into test9v values (131584);
+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;
+
-- Failure with incorrect page size
-- Suppress the DETAIL message, to allow the tests to work across various
-- page sizes.
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index f3eb055e2c..926c1fddbe 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7283,65 +7283,68 @@ select * from rem1;
-- ===================================================================
create table gloc1 (
a int,
- b int generated always as (a * 2) stored);
+ b int generated always as (a * 2) stored,
+ c int
+);
alter table gloc1 set (autovacuum_enabled = 'false');
create foreign table grem1 (
a int,
- b int generated always as (a * 2) stored)
- server loopback options(table_name 'gloc1');
+ b int generated always as (a * 2) stored,
+ c int generated always as (a * 3) virtual
+) server loopback options(table_name 'gloc1');
explain (verbose, costs off)
insert into grem1 (a) values (1), (2);
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Insert on public.grem1
- Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
+ Remote SQL: INSERT INTO public.gloc1(a, b, c) VALUES ($1, DEFAULT, DEFAULT)
Batch Size: 1
-> Values Scan on "*VALUES*"
- Output: "*VALUES*".column1, NULL::integer
+ Output: "*VALUES*".column1, NULL::integer, NULL::integer
(5 rows)
insert into grem1 (a) values (1), (2);
explain (verbose, costs off)
update grem1 set a = 22 where a = 2;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------
Update on public.grem1
- Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT WHERE ctid = $1
+ Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT, c = DEFAULT WHERE ctid = $1
-> Foreign Scan on public.grem1
Output: 22, ctid, grem1.*
- Remote SQL: SELECT a, b, ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE
+ Remote SQL: SELECT a, b, c, ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE
(5 rows)
update grem1 set a = 22 where a = 2;
select * from gloc1;
- a | b
-----+----
- 1 | 2
- 22 | 44
+ a | b | c
+----+----+---
+ 1 | 2 |
+ 22 | 44 |
(2 rows)
select * from grem1;
- a | b
-----+----
- 1 | 2
- 22 | 44
+ a | b | c
+----+----+----
+ 1 | 2 | 3
+ 22 | 44 | 66
(2 rows)
delete from grem1;
-- test copy from
copy grem1 from stdin;
select * from gloc1;
- a | b
----+---
- 1 | 2
- 2 | 4
+ a | b | c
+---+---+---
+ 1 | 2 |
+ 2 | 4 |
(2 rows)
select * from grem1;
- a | b
----+---
- 1 | 2
- 2 | 4
+ a | b | c
+---+---+---
+ 1 | 2 | 3
+ 2 | 4 | 6
(2 rows)
delete from grem1;
@@ -7349,28 +7352,28 @@ delete from grem1;
alter server loopback options (add batch_size '10');
explain (verbose, costs off)
insert into grem1 (a) values (1), (2);
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Insert on public.grem1
- Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
+ Remote SQL: INSERT INTO public.gloc1(a, b, c) VALUES ($1, DEFAULT, DEFAULT)
Batch Size: 10
-> Values Scan on "*VALUES*"
- Output: "*VALUES*".column1, NULL::integer
+ Output: "*VALUES*".column1, NULL::integer, NULL::integer
(5 rows)
insert into grem1 (a) values (1), (2);
select * from gloc1;
- a | b
----+---
- 1 | 2
- 2 | 4
+ a | b | c
+---+---+---
+ 1 | 2 |
+ 2 | 4 |
(2 rows)
select * from grem1;
- a | b
----+---
- 1 | 2
- 2 | 4
+ a | b | c
+---+---+---
+ 1 | 2 | 3
+ 2 | 4 | 6
(2 rows)
delete from grem1;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 0734716ad9..058d53364d 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1836,12 +1836,15 @@ select * from rem1;
-- ===================================================================
create table gloc1 (
a int,
- b int generated always as (a * 2) stored);
+ b int generated always as (a * 2) stored,
+ c int
+);
alter table gloc1 set (autovacuum_enabled = 'false');
create foreign table grem1 (
a int,
- b int generated always as (a * 2) stored)
- server loopback options(table_name 'gloc1');
+ b int generated always as (a * 2) stored,
+ c int generated always as (a * 3) virtual
+) server loopback options(table_name 'gloc1');
explain (verbose, costs off)
insert into grem1 (a) values (1), (2);
insert into grem1 (a) values (1), (2);
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index b654fae1b2..579b1d6166 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1318,8 +1318,8 @@
</para>
<para>
If a zero byte (<literal>''</literal>), then not a generated column.
- Otherwise, <literal>s</literal> = stored. (Other values might be added
- in the future.)
+ Otherwise, <literal>s</literal> = stored, <literal>v</literal> =
+ virtual.
</para></entry>
</row>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b671858627..378ae934ba 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -361,7 +361,6 @@ INSERT INTO people (id, name, address) VALUE (<emphasis>DEFAULT</emphasis>, 'C',
storage and is computed when it is read. 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).
- <productname>PostgreSQL</productname> currently implements only stored generated columns.
</para>
<para>
@@ -371,12 +370,12 @@ INSERT INTO people (id, name, address) VALUE (<emphasis>DEFAULT</emphasis>, 'C',
CREATE TABLE people (
...,
height_cm numeric,
- height_in numeric <emphasis>GENERATED ALWAYS AS (height_cm / 2.54) STORED</emphasis>
+ height_in numeric <emphasis>GENERATED ALWAYS AS (height_cm / 2.54)</emphasis>
);
</programlisting>
- The keyword <literal>STORED</literal> must be specified to choose the
- stored kind of generated column. See <xref linkend="sql-createtable"/> for
- more details.
+ 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>
@@ -442,12 +441,18 @@ CREATE TABLE people (
<listitem>
<para>
If a parent column is a generated column, its child column must also
- be a generated column; however, the child column can have a
- different generation expression. The generation expression that is
+ be a generated column of the same kind (stored or virtual); however,
+ the child column can have a different generation expression.
+ </para>
+
+ <para>
+ For stored generated columns, the generation expression that is
actually applied during insert or update of a row is the one
- associated with the table that the row is physically in.
- (This is unlike the behavior for column defaults: for those, the
- default value associated with the table named in the query applies.)
+ associated with the table that the row is physically in. (This is
+ unlike the behavior for column defaults: for those, the default value
+ associated with the table named in the query applies.) For virtual
+ generated columns, the generation expression of the table named in the
+ query applies when a table is read.
</para>
</listitem>
<listitem>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1a49f321cf..c5aaf76544 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -264,6 +264,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in the column is rewritten and all the future changes will apply the new
generation expression.
</para>
+
+ <para>
+ This form is currently only supported for stored generated columns (not
+ virtual ones).
+ </para>
</listitem>
</varlistentry>
@@ -276,10 +281,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
longer apply the generation expression.
</para>
+ <para>
+ This form is currently only supported for stored generated columns (not
+ virtual ones).
+ </para>
+
<para>
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
- column is not a stored generated column, no error is thrown. In this
- case a notice is issued instead.
+ column is not a generated column, no error is thrown. In this case a
+ notice is issued instead.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index dc4b907599..21b5d6a14d 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -47,7 +47,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
NULL |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
- GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED }
+ GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ] }
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
@@ -275,7 +275,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry>
- <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED</literal><indexterm><primary>generated column</primary></indexterm></term>
+ <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ]</literal><indexterm><primary>generated column</primary></indexterm></term>
<listitem>
<para>
This clause creates the column as a <firstterm>generated
@@ -284,10 +284,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- The keyword <literal>STORED</literal> is required to signify that the
+ 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.)
+ reading.) <literal>VIRTUAL</literal> is the default.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 93b3f664f2..ee79867d96 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -65,7 +65,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
NULL |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
- GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
+ GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ] |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
@@ -720,8 +720,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<term><literal>INCLUDING GENERATED</literal></term>
<listitem>
<para>
- Any generation expressions of copied column definitions will be
- copied. By default, new columns will be regular base columns.
+ Any generation expressions as well as the stored/virtual choice of
+ copied column definitions will be copied. By default, new columns
+ will be regular base columns.
</para>
</listitem>
</varlistentry>
@@ -897,7 +898,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry id="sql-createtable-parms-generated-stored">
- <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED</literal><indexterm><primary>generated column</primary></indexterm></term>
+ <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ]</literal><indexterm><primary>generated column</primary></indexterm></term>
<listitem>
<para>
This clause creates the column as a <firstterm>generated
@@ -906,8 +907,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- The keyword <literal>STORED</literal> is required to signify that the
- column will be computed on write and will be stored on disk.
+ 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>
@@ -2380,9 +2384,9 @@ CREATE TABLE cities_partdef
<title>Generated Columns</title>
<para>
- The option <literal>STORED</literal> is not standard but is also used by
- other SQL implementations. The SQL standard does not specify the storage
- of generated columns.
+ The options <literal>STORED</literal> and <literal>VIRTUAL</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>
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 982ab6f3ee..752fe50860 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -279,7 +279,7 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
<para>
<literal>INSTEAD OF UPDATE</literal> events do not allow a list of columns.
A column list cannot be specified when requesting transition relations,
- either.
+ either. Virtual generated columns are not supported in the column list.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index 31626536a2..5953d24ae8 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -289,6 +289,10 @@
<literal>BEFORE</literal> trigger. Changes to the value of a generated
column in a <literal>BEFORE</literal> trigger are ignored and will be
overwritten.
+ Virtual generated columns are never computed when triggers fire. In the C
+ language interface, their content is undefined in a trigger function.
+ Higher-level programming languages should prevent access to virtual
+ generated columns in triggers.
</para>
<para>
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index 47379fef22..13840ba533 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -190,6 +190,7 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
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)
{
@@ -494,6 +495,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
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;
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 1c6da286d4..0c95a11f04 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -2048,6 +2048,8 @@ heapam_relation_needs_toast_table(Relation rel)
if (att->attisdropped)
continue;
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ continue;
data_length = att_align_nominal(data_length, att->attalign);
if (att->attlen > 0)
{
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 01b43cc6a8..29358f3730 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -512,7 +512,7 @@ CheckAttributeNamesTypes(TupleDesc tupdesc, char relkind,
TupleDescAttr(tupdesc, i)->atttypid,
TupleDescAttr(tupdesc, i)->attcollation,
NIL, /* assume we're creating a new rowtype */
- flags);
+ flags | (TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL ? CHKATYPE_IS_VIRTUAL : 0));
}
}
@@ -587,6 +587,17 @@ CheckAttributeType(const char *attname,
}
else if (att_typtype == TYPTYPE_DOMAIN)
{
+ /*
+ * 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 (flags & CHKATYPE_IS_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("virtual generated column \"%s\" cannot have a domain type", attname)));
+
/*
* If it's a domain, recurse to check its base type.
*/
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 902eb1a450..f79ff40c3e 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -1030,6 +1030,10 @@ examine_attribute(Relation onerel, int attnum, Node *index_expr)
if (attr->attisdropped)
return NULL;
+ /* Don't analyze virtual generated columns */
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ return NULL;
+
/*
* Get attstattarget value. Set to -1 if null. (Analyze functions expect
* -1 to mean use default_statistics_target; see for example
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index d43b89d3ef..6593fd7d81 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -1689,18 +1689,13 @@ CreateCast(CreateCastStmt *stmt)
errmsg("source and target data types are not physically compatible")));
/*
- * We know that composite, array, range and enum types are never
- * binary-compatible with each other. They all have OIDs embedded in
- * them.
+ * We know that composite, enum and array types are never binary-
+ * compatible with each other. They all have OIDs embedded in them.
*
* Theoretically you could build a user-defined base type that is
- * binary-compatible with such a type. But we disallow it anyway, as
- * in practice such a cast is surely a mistake. You can always work
- * around that by writing a cast function.
- *
- * NOTE: if we ever have a kind of container type that doesn't need to
- * be rejected for this reason, we'd likely need to recursively apply
- * all of these same checks to the contained type(s).
+ * binary-compatible with a composite, enum, or array type. But we
+ * disallow that too, as in practice such a cast is surely a mistake.
+ * You can always work around that by writing a cast function.
*/
if (sourcetyptype == TYPTYPE_COMPOSITE ||
targettyptype == TYPTYPE_COMPOSITE)
@@ -1708,26 +1703,18 @@ CreateCast(CreateCastStmt *stmt)
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("composite data types are not binary-compatible")));
- if (OidIsValid(get_element_type(sourcetypeid)) ||
- OidIsValid(get_element_type(targettypeid)))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("array data types are not binary-compatible")));
-
- if (sourcetyptype == TYPTYPE_RANGE ||
- targettyptype == TYPTYPE_RANGE ||
- sourcetyptype == TYPTYPE_MULTIRANGE ||
- targettyptype == TYPTYPE_MULTIRANGE)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("range data types are not binary-compatible")));
-
if (sourcetyptype == TYPTYPE_ENUM ||
targettyptype == TYPTYPE_ENUM)
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("enum data types are not binary-compatible")));
+ if (OidIsValid(get_element_type(sourcetypeid)) ||
+ OidIsValid(get_element_type(targettypeid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("array data types are not binary-compatible")));
+
/*
* We also disallow creating binary-compatibility casts involving
* domains. Casting from a domain to its base type is already
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index c5a56c75f6..ec4e8bc390 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1085,6 +1085,9 @@ DefineIndex(Oid tableId,
/*
* 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 (int i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
{
@@ -1094,14 +1097,22 @@ DefineIndex(Oid tableId,
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)
{
Bitmapset *indexattrs = NULL;
+ int j;
pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs);
pull_varattnos((Node *) indexInfo->ii_Predicate, 1, &indexattrs);
@@ -1114,6 +1125,22 @@ DefineIndex(Oid tableId,
(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().
+ */
+ j = -1;
+ while ((j = bms_next_member(indexattrs, j)) >= 0)
+ {
+ AttrNumber attno = j + 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")));
+ }
}
/* Is index safe for others to ignore? See set_indexsafe_procflags() */
diff --git a/src/backend/commands/statscmds.c b/src/backend/commands/statscmds.c
index 1db3ef69d2..744b3508c2 100644
--- a/src/backend/commands/statscmds.c
+++ b/src/backend/commands/statscmds.c
@@ -246,6 +246,12 @@ CreateStatistics(CreateStatsStmt *stmt)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("statistics creation on system columns is not supported")));
+ /* Disallow use of virtual generated columns in extended stats */
+ if (attForm->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("statistics creation on virtual generated columns is not supported")));
+
/* Disallow data types without a less-than operator */
type = lookup_type_cache(attForm->atttypid, TYPECACHE_LT_OPR);
if (type->lt_opr == InvalidOid)
@@ -269,6 +275,12 @@ CreateStatistics(CreateStatsStmt *stmt)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("statistics creation on system columns is not supported")));
+ /* Disallow use of virtual generated columns in extended stats */
+ if (get_attgenerated(relid, var->varattno) == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("statistics creation on virtual generated columns is not supported")));
+
/* Disallow data types without a less-than operator */
type = lookup_type_cache(var->vartype, TYPECACHE_LT_OPR);
if (type->lt_opr == InvalidOid)
@@ -290,7 +302,6 @@ CreateStatistics(CreateStatsStmt *stmt)
Assert(expr != NULL);
- /* Disallow expressions referencing system attributes. */
pull_varattnos(expr, 1, &attnums);
k = -1;
@@ -298,10 +309,17 @@ CreateStatistics(CreateStatsStmt *stmt)
{
AttrNumber attnum = k + FirstLowInvalidHeapAttributeNumber;
+ /* Disallow expressions referencing system attributes. */
if (attnum <= 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("statistics creation on system columns is not supported")));
+
+ /* Disallow use of virtual generated columns in extended stats */
+ if (get_attgenerated(relid, attnum) == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("statistics creation on virtual generated columns is not supported")));
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 70cd266916..4227a955ca 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2936,6 +2936,15 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
errhint("A child table column cannot be generated unless its parent column is.")));
}
+ if (coldef->generated && restdef->generated && coldef->generated != restdef->generated)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
+ errmsg("column \"%s\" inherits from generated column of different kind",
+ restdef->colname),
+ errdetail("Parent column is %s, child column is %s.",
+ coldef->generated == ATTRIBUTE_GENERATED_STORED ? "STORED" : "VIRTUAL",
+ restdef->generated == ATTRIBUTE_GENERATED_STORED ? "STORED" : "VIRTUAL")));
+
/*
* Override the parent's default value for this column
* (coldef->cooked_default) with the partition's local
@@ -3207,6 +3216,15 @@ MergeChildAttribute(List *inh_columns, int exist_attno, int newcol_attno, const
errhint("A child table column cannot be generated unless its parent column is.")));
}
+ if (inhdef->generated && newdef->generated && newdef->generated != inhdef->generated)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
+ errmsg("column \"%s\" inherits from generated column of different kind",
+ inhdef->colname),
+ errdetail("Parent column is %s, child column is %s.",
+ inhdef->generated == ATTRIBUTE_GENERATED_STORED ? "STORED" : "VIRTUAL",
+ newdef->generated == ATTRIBUTE_GENERATED_STORED ? "STORED" : "VIRTUAL")));
+
/*
* If new def has a default, override previous default
*/
@@ -5986,7 +6004,7 @@ 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 */
@@ -7703,6 +7721,13 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot alter system column \"%s\"",
colName)));
+ /* TODO: see transformColumnDefinition() */
+ if (attTup->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("not-null constraints are not supported on virtual generated columns"),
+ errdetail("Column \"%s\" of relation \"%s\" is a virtual generated column.",
+ colName, RelationGetRelationName(rel))));
/*
* Okay, actually perform the catalog change ... if needed
@@ -8301,7 +8326,18 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
errmsg("cannot alter system column \"%s\"",
colName)));
- if (attTup->attgenerated != ATTRIBUTE_GENERATED_STORED)
+ /*
+ * TODO: This could be done, but it would need a different implementation:
+ * no rewriting, but still need to recheck any constraints.
+ */
+ if (attTup->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns"),
+ errdetail("Column \"%s\" of relation \"%s\" is a virtual generated column.",
+ colName, RelationGetRelationName(rel))));
+
+ if (!attTup->attgenerated)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("column \"%s\" of relation \"%s\" is not a generated column",
@@ -8458,17 +8494,30 @@ ATExecDropExpression(Relation rel, const char *colName, bool missing_ok, LOCKMOD
errmsg("cannot alter system column \"%s\"",
colName)));
- if (attTup->attgenerated != ATTRIBUTE_GENERATED_STORED)
+ /*
+ * TODO: This could be done, but it would need a table rewrite to
+ * materialize the generated values. Note that for the time being, we
+ * still error with missing_ok, so that we don't silently leave the column
+ * as generated.
+ */
+ if (attTup->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ALTER TABLE / DROP EXPRESSION is not supported for virtual generated columns"),
+ errdetail("Column \"%s\" of relation \"%s\" is a virtual generated column.",
+ colName, RelationGetRelationName(rel))));
+
+ if (!attTup->attgenerated)
{
if (!missing_ok)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
- errmsg("column \"%s\" of relation \"%s\" is not a stored generated column",
+ errmsg("column \"%s\" of relation \"%s\" is not a generated column",
colName, RelationGetRelationName(rel))));
else
{
ereport(NOTICE,
- (errmsg("column \"%s\" of relation \"%s\" is not a stored generated column, skipping",
+ (errmsg("column \"%s\" of relation \"%s\" is not a generated column, skipping",
colName, RelationGetRelationName(rel))));
heap_freetuple(tuple);
table_close(attrelation, RowExclusiveLock);
@@ -8611,6 +8660,16 @@ ATExecSetStatistics(Relation rel, const char *colName, int16 colNum, Node *newVa
errmsg("cannot alter system column \"%s\"",
colName)));
+ /*
+ * Prevent this as long as the ANALYZE code skips virtual generated
+ * columns.
+ */
+ if (attrtuple->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter statistics on virtual generated column \"%s\"",
+ colName)));
+
if (rel->rd_rel->relkind == RELKIND_INDEX ||
rel->rd_rel->relkind == RELKIND_PARTITIONED_INDEX)
{
@@ -9690,6 +9749,19 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
errmsg("invalid %s action for foreign key constraint containing generated column",
"ON DELETE")));
}
+
+ /*
+ * 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")));
}
/*
@@ -11671,7 +11743,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
val = SysCacheGetAttrNotNull(CONSTROID, tuple,
Anum_pg_constraint_conbin);
conbin = TextDatumGetCString(val);
- newcon->qual = (Node *) stringToNode(conbin);
+ newcon->qual = (Node *) expand_generated_columns_in_expr(stringToNode(conbin), rel);
/* Find or create work queue entry for this table */
tab = ATGetQueueEntry(wqueue, rel);
@@ -12677,6 +12749,16 @@ ATPrepAlterColumnType(List **wqueue,
errmsg("cannot alter system column \"%s\"",
colName)));
+ /*
+ * Cannot specify USING when altering type of a generated column, because
+ * that would violate the generation expression.
+ */
+ if (attTup->attgenerated && def->cooked_default)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot specify USING when altering type of generated column"),
+ errdetail("Column \"%s\" is a generated column.", colName)));
+
/*
* Cannot specify USING when altering type of a generated column, because
* that would violate the generation expression.
@@ -12722,8 +12804,12 @@ ATPrepAlterColumnType(List **wqueue,
list_make1_oid(rel->rd_rel->reltype),
0);
- if (tab->relkind == RELKIND_RELATION ||
- tab->relkind == RELKIND_PARTITIONED_TABLE)
+ if (attTup->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ /* do nothing */
+ }
+ else if (tab->relkind == RELKIND_RELATION ||
+ tab->relkind == RELKIND_PARTITIONED_TABLE)
{
/*
* Set up an expression to transform the old data value to the new
@@ -17884,8 +17970,11 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
parser_errposition(pstate, pelem->location)));
/*
- * Generated columns cannot work: They are computed after BEFORE
- * triggers, but partition routing is done before all triggers.
+ * Stored generated columns cannot work: They are computed after
+ * BEFORE triggers, but partition routing is done before all
+ * triggers. Maybe virtual generated columns could be made to
+ * work, but then they would need to be handled as an expression
+ * below.
*/
if (attform->attgenerated)
ereport(ERROR,
@@ -17967,9 +18056,12 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
}
/*
- * Generated columns cannot work: They are computed after
- * BEFORE triggers, but partition routing is done before all
- * triggers.
+ * Stored generated columns cannot work: They are computed
+ * after BEFORE triggers, but partition routing is done before
+ * all triggers. Virtual generated columns could probably
+ * work, but it would require more work elsewhere (for example
+ * SET EXPRESSION would need to check whether the column is
+ * used in partition keys). Seems safer to prohibit for now.
*/
i = -1;
while ((i = bms_next_member(expr_attrs, i)) >= 0)
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 170360edda..021328746a 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -43,6 +43,7 @@
#include "parser/parse_relation.h"
#include "partitioning/partdesc.h"
#include "pgstat.h"
+#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "storage/lmgr.h"
#include "utils/acl.h"
@@ -101,6 +102,7 @@ static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
bool is_crosspart_update);
static void AfterTriggerEnlargeQueryState(void);
static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
+static void check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple tuple);
/*
@@ -641,7 +643,8 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
if (TRIGGER_FOR_BEFORE(tgtype) &&
var->varattno == 0 &&
RelationGetDescr(rel)->constr &&
- RelationGetDescr(rel)->constr->has_generated_stored)
+ (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"),
@@ -942,6 +945,13 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
errmsg("column \"%s\" of relation \"%s\" does not exist",
name, RelationGetRelationName(rel))));
+ /* Currently doesn't work. */
+ if (TupleDescAttr(RelationGetDescr(rel), attnum - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("virtual generated columns are not supported as trigger columns"),
+ errdetail("Column \"%s\" is a virtual generated column.", name));
+
/* Check for duplicates */
for (j = i - 1; j >= 0; j--)
{
@@ -2502,6 +2512,8 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
}
else if (newtuple != oldtuple)
{
+ check_modified_virtual_generated(RelationGetDescr(relinfo->ri_RelationDesc), newtuple);
+
ExecForceStoreHeapTuple(newtuple, slot, false);
/*
@@ -3059,6 +3071,8 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
}
else if (newtuple != oldtuple)
{
+ check_modified_virtual_generated(RelationGetDescr(relinfo->ri_RelationDesc), newtuple);
+
ExecForceStoreHeapTuple(newtuple, newslot, false);
/*
@@ -3489,6 +3503,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);
@@ -6596,3 +6611,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/view.c b/src/backend/commands/view.c
index fdad833832..150115c13b 100644
--- a/src/backend/commands/view.c
+++ b/src/backend/commands/view.c
@@ -278,6 +278,9 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc)
Form_pg_attribute newattr = TupleDescAttr(newdesc, i);
Form_pg_attribute oldattr = TupleDescAttr(olddesc, i);
+ Assert(newattr->attgenerated == '\0');
+ Assert(oldattr->attgenerated == '\0');
+
/* XXX msg not right, but we don't support DROP COL on view anyway */
if (newattr->attisdropped != oldattr->attisdropped)
ereport(ERROR,
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 77394e76c3..58b0f2f76d 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -2119,6 +2119,10 @@ CheckVarSlotCompatibility(TupleTableSlot *slot, int attnum, Oid vartype)
attr = TupleDescAttr(slot_tupdesc, attnum - 1);
+ /* Internal error: somebody forgot to expand it. */
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ elog(ERROR, "unexpected virtual generated column reference");
+
if (attr->attisdropped)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 29e186fa73..898f99a48c 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -255,6 +255,17 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflags)
*/
InitPlan(queryDesc, eflags);
+ {
+ int i;
+ for (i = 0; i < queryDesc->tupDesc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(queryDesc->tupDesc, i);
+ if (att->attisdropped)
+ continue;
+ Assert(att->attgenerated != ATTRIBUTE_GENERATED_VIRTUAL);
+ Assert(att->attgenerated != ATTRIBUTE_GENERATED_STORED);
+ }
+ }
MemoryContextSwitchTo(oldcontext);
}
@@ -1743,6 +1754,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);
}
@@ -2290,7 +2302,9 @@ ExecBuildSlotValueDescription(Oid reloid,
if (table_perm || column_perm)
{
- if (slot->tts_isnull[i])
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ val = "virtual";
+ else if (slot->tts_isnull[i])
val = "null";
else
{
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index e901203424..e3936a0b8e 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -568,6 +568,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);
@@ -994,6 +995,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);
@@ -1459,6 +1461,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)
{
@@ -1685,6 +1688,7 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
qry->hasSubLinks = pstate->p_hasSubLinks;
+ qry->hasGeneratedVirtual = pstate->p_hasGeneratedVirtual;
assign_query_collations(pstate, qry);
@@ -1936,6 +1940,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)
{
@@ -2410,6 +2415,7 @@ transformReturnStmt(ParseState *pstate, ReturnStmt *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);
@@ -2477,6 +2483,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);
@@ -2843,6 +2850,7 @@ transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt)
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasAggs = pstate->p_hasAggs;
+ qry->hasGeneratedVirtual = pstate->p_hasGeneratedVirtual;
foreach(l, sstmt->lockingClause)
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 84cef57a70..18ce875c40 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -640,7 +640,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
%type <boolean> opt_unique_null_treatment
-%type <ival> generated_when override_kind
+%type <ival> generated_when override_kind opt_virtual_or_stored
%type <partspec> PartitionSpec OptPartitionSpec
%type <partelem> part_elem
%type <list> part_params
@@ -788,7 +788,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UNLISTEN UNLOGGED 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
@@ -3973,7 +3973,7 @@ ColConstraintElem:
n->location = @1;
$$ = (Node *) n;
}
- | GENERATED generated_when AS '(' a_expr ')' STORED
+ | GENERATED generated_when AS '(' a_expr ')' opt_virtual_or_stored
{
Constraint *n = makeNode(Constraint);
@@ -3981,6 +3981,7 @@ ColConstraintElem:
n->generated_when = $2;
n->raw_expr = $5;
n->cooked_expr = NULL;
+ n->generated_kind = $7;
n->location = @1;
/*
@@ -4027,6 +4028,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
@@ -17809,6 +17816,7 @@ unreserved_keyword:
| VERSION_P
| VIEW
| VIEWS
+ | VIRTUAL
| VOLATILE
| WHITESPACE_P
| WITHIN
@@ -18462,6 +18470,7 @@ bare_label_keyword:
| VERSION_P
| VIEW
| VIEWS
+ | VIRTUAL
| VOLATILE
| WHEN
| WHITESPACE_P
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 8118036495..48524ac3fc 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -207,6 +207,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 and a ParseNamespaceItem.
*/
diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c
index 87df79027d..350e9e1888 100644
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -405,6 +405,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
qry->hasTargetSRFs = false;
qry->hasSubLinks = pstate->p_hasSubLinks;
+ qry->hasGeneratedVirtual = pstate->p_hasGeneratedVirtual;
assign_query_collations(pstate, qry);
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 2f64eaf0e3..b864749f09 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -704,7 +704,11 @@ scanNSItemForColumn(ParseState *pstate, ParseNamespaceItem *nsitem,
colname),
parser_errposition(pstate, location)));
- /* In generated column, no system column is allowed except tableOid */
+ /*
+ * 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,
@@ -1511,6 +1515,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;
+
/*
* Set flags and initialize access permissions.
*
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 79cad4ab30..03301f9c53 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -751,7 +751,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
- column->generated = ATTRIBUTE_GENERATED_STORED;
+ column->generated = constraint->generated_kind;
column->raw_default = constraint->raw_expr;
Assert(constraint->cooked_expr == NULL);
saw_generated = true;
@@ -839,6 +839,18 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
+
+ /*
+ * TODO: Straightforward not-null constraints won't work on virtual
+ * generated columns, because there is no support for expanding the
+ * column when the constraint is checked. Maybe we could 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)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("not-null constraints are not supported on virtual generated columns")));
}
/*
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index abef4eaf68..52dc9b5fca 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -27,6 +27,7 @@
#include "replication/logicalproto.h"
#include "replication/origin.h"
#include "replication/pgoutput.h"
+#include "rewrite/rewriteHandler.h"
#include "utils/builtins.h"
#include "utils/inval.h"
#include "utils/lsyscache.h"
@@ -996,7 +997,7 @@ pgoutput_row_filter_init(PGOutputData *data, List *publications,
continue;
foreach(lc, rfnodes[idx])
- filters = lappend(filters, stringToNode((char *) lfirst(lc)));
+ filters = lappend(filters, expand_generated_columns_in_expr(stringToNode((char *) lfirst(lc)), relation));
/* combine the row filter and cache the ExprState */
rfnode = make_orclause(filters);
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index c223a2c50a..598ca75fcc 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -30,6 +30,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/print.h"
#include "optimizer/optimizer.h"
#include "parser/analyze.h"
#include "parser/parse_coerce.h"
@@ -44,6 +45,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 */
@@ -90,8 +92,20 @@ static List *matchLocks(CmdType event, Relation relation,
int varno, Query *parsetree, bool *hasUpdate);
static Query *fireRIRrules(Query *parsetree, List *activeRIRs);
static Bitmapset *adjust_view_column_set(Bitmapset *cols, List *targetlist);
+struct expand_generated_context;
-
+typedef struct virtual_generated_context
+{
+ int ancestor_has_virtual;
+ List *rtables;
+ List *virtual_gen_tlist;
+ List *virtual_gen_varno;
+ List *virtual_gen_rte;
+} virtual_generated_context;
+static Query *expand_generated_columns_in_query(Query *query, struct expand_generated_context *context);
+static Node *
+get_generated_columns_tlelist_mutator(Node *node, virtual_generated_context *context);
+static Query *make_generated_columns_targetlist_query(Query *query, virtual_generated_context *context);
/*
* AcquireRewriteLocks -
* Acquire suitable locks on all the relations mentioned in the Query.
@@ -974,7 +988,8 @@ rewriteTargetListIU(List *targetList,
if (att_tup->attgenerated)
{
/*
- * stored generated column will be fixed in executor
+ * virtual generated column stores a null value; stored generated
+ * column will be fixed in executor
*/
new_tle = NULL;
}
@@ -4365,6 +4380,284 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length)
}
+/*
+ * Virtual generated columns support
+ */
+struct expand_generated_context
+{
+ /* list of range tables, innermost last */
+ List *rtables;
+
+ /* incremented for every level where it's true */
+ int ancestor_has_virtual;
+};
+
+static Node *
+expand_generated_columns_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 = table_open(relid, NoLock);
+ Oid attcollid;
+
+ 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));
+
+ /*
+ * If the column definition has a collation and it is different
+ * from the collation of the generation expression, put a COLLATE
+ * clause around the expression.
+ */
+ attcollid = GetSysCacheOid(ATTNUM, Anum_pg_attribute_attcollation, relid, attnum, 0, 0);
+ if (attcollid && attcollid != exprCollation(node))
+ {
+ CollateExpr *ce = makeNode(CollateExpr);
+
+ ce->arg = (Expr *) node;
+ ce->collOid = attcollid;
+ ce->location = -1;
+
+ node = (Node *) ce;
+ }
+
+ IncrementVarSublevelsUp(node, v->varlevelsup, 0);
+ ChangeVarNodes(node, 1, v->varno, v->varlevelsup);
+
+ table_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_mutator, context);
+}
+
+/*
+ * Expand virtual generated columns in a Query. We do some optimizations here
+ * to avoid digging through the whole Query unless necessary.
+ */
+pg_attribute_unused()
+static Query *
+expand_generated_columns_in_query(Query *query, struct expand_generated_context *context)
+{
+ context->rtables = lappend(context->rtables, query->rtable);
+ if (query->hasGeneratedVirtual)
+ context->ancestor_has_virtual++;
+
+ /*
+ * 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 || context->ancestor_has_virtual)
+ {
+ query = query_tree_mutator(query,
+ expand_generated_columns_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);
+ }
+ }
+
+ if (query->hasGeneratedVirtual)
+ context->ancestor_has_virtual--;
+ context->rtables = list_truncate(context->rtables, list_length(context->rtables) - 1);
+
+ return query;
+}
+
+
+Node *
+expand_generated_columns_in_expr(Node *node, Relation rel)
+{
+ TupleDesc tupdesc = RelationGetDescr(rel);
+
+ if (tupdesc->constr && tupdesc->constr->has_generated_virtual)
+ {
+ RangeTblEntry *rte;
+ List *rtable;
+ struct expand_generated_context context;
+
+ /*
+ * Make a dummy range table for a single relation. For the benefit of
+ * triggers, add the same entry twice, so it covers PRS2_OLD_VARNO and
+ * PRS2_NEW_VARNO.
+ */
+ rte = makeNode(RangeTblEntry);
+ rte->relid = RelationGetRelid(rel);
+ rtable = list_make2(rte, rte);
+ context.rtables = list_make1(rtable);
+
+ return expression_tree_mutator(node, expand_generated_columns_mutator, &context);
+ }
+ else
+ return node;
+}
+
+static Query *
+make_generated_columns_targetlist_query(Query *query, virtual_generated_context *context)
+{
+ context->rtables = lappend(context->rtables, query->rtable);
+
+ if (query->hasGeneratedVirtual)
+ context->ancestor_has_virtual++;
+
+ if (context->ancestor_has_virtual)
+ query = query_tree_mutator(query,
+ get_generated_columns_tlelist_mutator,
+ context,
+ QTW_DONT_COPY_QUERY);
+ else
+ {
+ foreach_node(RangeTblEntry, rte, query->rtable)
+ {
+ if (rte->rtekind == RTE_SUBQUERY)
+ rte->subquery = make_generated_columns_targetlist_query(rte->subquery, context);
+ }
+
+ foreach_node(CommonTableExpr, cte, query->cteList)
+ cte->ctequery = (Node *) make_generated_columns_targetlist_query(castNode(Query, cte->ctequery), context);
+ }
+
+ if (query->hasGeneratedVirtual)
+ context->ancestor_has_virtual--;
+
+ context->rtables = list_truncate(context->rtables, list_length(context->rtables) - 1);
+ return query;
+}
+
+static Node *
+get_generated_columns_tlelist_mutator(Node *node, virtual_generated_context *context)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, Var))
+ {
+ Var *v = (Var *) node;
+ Oid relid;
+ Relation rt_entry_relation;
+ RangeTblEntry *rte;
+ TupleDesc tupdesc;
+ AttrNumber attnum;
+ int i;
+ List *rtable = list_nth_node(List,
+ context->rtables,
+ list_length(context->rtables) - v->varlevelsup - 1);
+ if (IS_SPECIAL_VARNO(v->varno))
+ return node;
+ rte = rt_fetch(v->varno, rtable);
+ if (rte->rtekind != RTE_RELATION)
+ return node;
+
+ relid = rte->relid;
+ Assert(OidIsValid(relid));
+
+ attnum = v->varattno;
+ rt_entry_relation = table_open(relid, NoLock);
+ tupdesc = RelationGetDescr(rt_entry_relation);
+ if (tupdesc->constr && tupdesc->constr->has_generated_virtual)
+ {
+ Oid attcollid;
+ Node *virtual_expr;
+ TargetEntry *tle;
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+ if (att->attisdropped)
+ continue;
+
+ if (att->attgenerated == 'v' && attnum == att->attnum)
+ {
+ virtual_expr = build_column_default(rt_entry_relation, attnum);
+ attcollid = GetSysCacheOid(ATTNUM, Anum_pg_attribute_attcollation, relid, attnum, 0, 0);
+ if (attcollid && attcollid != exprCollation(virtual_expr))
+ {
+ CollateExpr *ce = makeNode(CollateExpr);
+
+ ce->arg = (Expr *) virtual_expr;
+ ce->collOid = attcollid;
+ ce->location = -1;
+
+ virtual_expr = (Node *) ce;
+ }
+ ChangeVarNodes(virtual_expr, 1, v->varno, v->varlevelsup);
+ context->virtual_gen_varno = list_append_unique_int(context->virtual_gen_varno, v->varno);
+ context->virtual_gen_rte = list_append_unique_ptr(context->virtual_gen_rte, rte);
+
+ tle = makeTargetEntry((Expr *) virtual_expr, att->attnum,
+ NULL,
+ false);
+ tle->resorigtbl = relid;
+ tle->resorigcol = attnum;
+ context->virtual_gen_tlist = lappend(context->virtual_gen_tlist, tle);
+ }
+ }
+ }
+ table_close(rt_entry_relation, NoLock);
+ return node;
+ }
+ else if (IsA(node, Query))
+ {
+ Query *newnode = (Query *) node;
+ context->rtables = lappend(context->rtables, newnode->rtable);
+ newnode = query_tree_mutator((Query *) node,
+ get_generated_columns_tlelist_mutator,
+ (void *) context,
+ 0);
+ return (Node *) newnode;
+ }
+ else
+ return expression_tree_mutator(node, get_generated_columns_tlelist_mutator, (void *) context);
+}
+
/*
* QueryRewrite -
* Primary entry point to the query rewriter.
@@ -4409,9 +4702,26 @@ QueryRewrite(Query *parsetree)
foreach(l, querylist)
{
Query *query = (Query *) lfirst(l);
-
+ virtual_generated_context gen_context = {0};
+ ListCell *lc,
+ *lc2;
query = fireRIRrules(query, NIL);
+ query = make_generated_columns_targetlist_query(query, &gen_context);
+ forboth(lc, gen_context.virtual_gen_varno, lc2, gen_context.virtual_gen_rte)
+ {
+ int result_varno = lfirst_int(lc);
+ RangeTblEntry *virtual_rte = lfirst_node(RangeTblEntry, lc2);
+ query = (Query *)
+ ReplaceVarsFromTargetList((Node *)query,
+ result_varno,
+ 0,
+ virtual_rte,
+ gen_context.virtual_gen_tlist,
+ REPLACEVARS_CHANGE_VARNO,
+ result_varno,
+ &query->hasSubLinks);
+ }
query->queryId = input_query_id;
results = lappend(results, query);
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 7c6391a276..f2ecef000a 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -1521,12 +1521,12 @@ check_collation_set(Oid collid)
}
}
-/*
- * varstr_cmp()
- *
- * Comparison function for text strings with given lengths, using the
- * appropriate locale. Returns an integer less than, equal to, or greater than
- * zero, indicating whether arg1 is less than, equal to, or greater than arg2.
+/* varstr_cmp()
+ * Comparison function for text strings with given lengths.
+ * Includes locale support, but must copy strings to temporary memory
+ * to allow null-termination for inputs to strcoll().
+ * Returns an integer less than, equal to, or greater than zero, indicating
+ * whether arg1 is less than, equal to, or greater than arg2.
*
* Note: many functions that depend on this are marked leakproof; therefore,
* avoid reporting the actual contents of the input when throwing errors.
@@ -1541,6 +1541,12 @@ varstr_cmp(const char *arg1, int len1, const char *arg2, int len2, Oid collid)
check_collation_set(collid);
+ /*
+ * Unfortunately, there is no strncoll(), so in the non-C locale case we
+ * have to do some memory copying. This turns out to be significantly
+ * slower, so we optimize the case where LC_COLLATE is C. We also try to
+ * optimize relatively-short strings by avoiding palloc/pfree overhead.
+ */
if (lc_collate_is_c(collid))
{
result = memcmp(arg1, arg2, Min(len1, len2));
diff --git a/src/backend/utils/cache/partcache.c b/src/backend/utils/cache/partcache.c
index beec6cddbc..1dee7c1e89 100644
--- a/src/backend/utils/cache/partcache.c
+++ b/src/backend/utils/cache/partcache.c
@@ -26,6 +26,7 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/optimizer.h"
#include "partitioning/partbounds.h"
+#include "rewrite/rewriteHandler.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
@@ -148,6 +149,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 66ed24e401..4f756acd39 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -589,6 +589,8 @@ RelationBuildTupleDesc(Relation relation)
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 (attp->atthasdef)
ndef++;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b6e01d3d29..4b2aac8cd3 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15998,6 +15998,9 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
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);
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 5bcc2244d5..c3d6022015 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3595,12 +3595,14 @@ my %tests = (
create_order => 3,
create_sql => 'CREATE TABLE dump_test.test_table_generated (
col1 int primary key,
- col2 int generated always as (col1 * 2) stored
+ col2 int generated always as (col1 * 2) stored,
+ col3 int generated always as (col1 * 3) virtual
);',
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)) STORED\E\n
+ \s+\Qcol2 integer GENERATED ALWAYS AS ((col1 * 2)) STORED,\E\n
+ \s+\Qcol3 integer GENERATED ALWAYS AS ((col1 * 3))\E\n
\);
/xms,
like =>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 7c9a1f234c..4309d3e757 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2089,6 +2089,12 @@ describeOneTableDetails(const char *schemaname,
PQgetvalue(res, i, attrdef_col));
mustfree = true;
}
+ else if (generated[0] == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ default_str = psprintf("generated always as (%s)",
+ PQgetvalue(res, i, attrdef_col));
+ mustfree = true;
+ }
else
default_str = PQgetvalue(res, i, attrdef_col);
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index 8930a28d66..6ef4024958 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -43,6 +43,7 @@ typedef struct TupleConstr
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 c512824cd1..235ad73eed 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -23,6 +23,7 @@
#define CHKATYPE_ANYARRAY 0x01 /* allow ANYARRAY */
#define CHKATYPE_ANYRECORD 0x02 /* allow RECORD and RECORD[] */
#define CHKATYPE_IS_PARTKEY 0x04 /* attname is part key # not column */
+#define CHKATYPE_IS_VIRTUAL 0x08 /* is virtual generated column */
typedef struct RawColumnDefault
{
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index 1c62b8bfcb..8f158dc608 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -234,6 +234,7 @@ MAKE_SYSCACHE(ATTNUM, pg_attribute_relid_attnum_index, 128);
#define ATTRIBUTE_IDENTITY_BY_DEFAULT 'd'
#define ATTRIBUTE_GENERATED_STORED 's'
+#define ATTRIBUTE_GENERATED_VIRTUAL 'v'
#endif /* EXPOSE_TO_CLIENT_CODE */
diff --git a/src/include/catalog/pg_extension.h b/src/include/catalog/pg_extension.h
index 673181b39a..cdfacc0930 100644
--- a/src/include/catalog/pg_extension.h
+++ b/src/include/catalog/pg_extension.h
@@ -56,7 +56,4 @@ DECLARE_TOAST(pg_extension, 4147, 4148);
DECLARE_UNIQUE_INDEX_PKEY(pg_extension_oid_index, 3080, ExtensionOidIndexId, pg_extension, btree(oid oid_ops));
DECLARE_UNIQUE_INDEX(pg_extension_name_index, 3081, ExtensionNameIndexId, pg_extension, btree(extname name_ops));
-MAKE_SYSCACHE(EXTENSIONOID, pg_extension_oid_index, 2);
-MAKE_SYSCACHE(EXTENSIONNAME, pg_extension_name_index, 2);
-
#endif /* PG_EXTENSION_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 124d853e49..355f52199f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -160,6 +160,8 @@ typedef struct Query
bool hasForUpdate pg_node_attr(query_jumble_ignore);
/* rewriter has applied some RLS policy */
bool hasRowSecurity pg_node_attr(query_jumble_ignore);
+ /* some table has a virtual generated column */
+ bool hasGeneratedVirtual pg_node_attr(query_jumble_ignore);
/* is a RETURN statement */
bool isReturn pg_node_attr(query_jumble_ignore);
@@ -2730,6 +2732,7 @@ typedef struct Constraint
char *cooked_expr; /* CHECK or DEFAULT expression, as
* nodeToString representation */
char generated_when; /* ALWAYS or BY DEFAULT */
+ char generated_kind; /* STORED or VIRTUAL */
int inhcount; /* initial inheritance count to apply, for
* "raw" NOT NULL constraints */
bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f8659078ce..6416f9b110 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -489,6 +489,7 @@ PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("virtual", VIRTUAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("when", WHEN, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("where", WHERE, RESERVED_KEYWORD, AS_LABEL)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 5b781d87a9..1b02128548 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -225,6 +225,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/rewrite/rewriteHandler.h b/src/include/rewrite/rewriteHandler.h
index 1b65cda71c..a646a20675 100644
--- a/src/include/rewrite/rewriteHandler.h
+++ b/src/include/rewrite/rewriteHandler.h
@@ -38,4 +38,6 @@ extern void error_view_not_updatable(Relation view,
List *mergeActionList,
const char *detail);
+extern Node *expand_generated_columns_in_expr(Node *node, Relation rel);
+
#endif /* REWRITEHANDLER_H */
diff --git a/src/pl/plperl/expected/plperl_trigger.out b/src/pl/plperl/expected/plperl_trigger.out
index d4879e2f03..42c52ecbba 100644
--- a/src/pl/plperl/expected/plperl_trigger.out
+++ b/src/pl/plperl/expected/plperl_trigger.out
@@ -8,7 +8,8 @@ CREATE TABLE trigger_test (
);
CREATE TABLE trigger_test_generated (
i int,
- j int GENERATED ALWAYS AS (i * 2) STORED
+ j int GENERATED ALWAYS AS (i * 2) STORED,
+ k int GENERATED ALWAYS AS (i * 3) VIRTUAL
);
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plperl AS $$
@@ -386,7 +387,7 @@ 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
----+---
+ i | j | k
+---+---+---
(0 rows)
diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index d68ad7be34..9611aa8235 100644
--- a/src/pl/plperl/plperl.c
+++ b/src/pl/plperl/plperl.c
@@ -3052,6 +3052,9 @@ plperl_hash_from_tuple(HeapTuple tuple, TupleDesc tupdesc, bool include_generate
/* don't include unless requested */
if (!include_generated)
continue;
+ /* never include virtual columns */
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ continue;
}
attname = NameStr(att->attname);
diff --git a/src/pl/plperl/sql/plperl_trigger.sql b/src/pl/plperl/sql/plperl_trigger.sql
index 4adddeb80a..2798a02fa1 100644
--- a/src/pl/plperl/sql/plperl_trigger.sql
+++ b/src/pl/plperl/sql/plperl_trigger.sql
@@ -10,7 +10,8 @@ CREATE TABLE trigger_test (
CREATE TABLE trigger_test_generated (
i int,
- j int GENERATED ALWAYS AS (i * 2) STORED
+ j int GENERATED ALWAYS AS (i * 2) STORED,
+ k int GENERATED ALWAYS AS (i * 3) VIRTUAL
);
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plperl AS $$
diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpython/expected/plpython_trigger.out
index 4cb90cb520..64eab2fa3f 100644
--- a/src/pl/plpython/expected/plpython_trigger.out
+++ b/src/pl/plpython/expected/plpython_trigger.out
@@ -69,7 +69,8 @@ CREATE TABLE trigger_test
(i int, v text );
CREATE TABLE trigger_test_generated (
i int,
- j int GENERATED ALWAYS AS (i * 2) STORED
+ j int GENERATED ALWAYS AS (i * 2) STORED,
+ k int GENERATED ALWAYS AS (i * 3) VIRTUAL
);
CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpython3u AS $$
@@ -614,8 +615,8 @@ ERROR: cannot set generated column "j"
CONTEXT: while modifying trigger row
PL/Python function "generated_test_func1"
SELECT * FROM trigger_test_generated;
- i | j
----+---
+ i | j | k
+---+---+---
(0 rows)
-- recursive call of a trigger mustn't corrupt TD (bug #18456)
diff --git a/src/pl/plpython/plpy_typeio.c b/src/pl/plpython/plpy_typeio.c
index db14c5f8da..51e1d61025 100644
--- a/src/pl/plpython/plpy_typeio.c
+++ b/src/pl/plpython/plpy_typeio.c
@@ -844,6 +844,9 @@ PLyDict_FromTuple(PLyDatumToOb *arg, HeapTuple tuple, TupleDesc desc, bool inclu
/* don't include unless requested */
if (!include_generated)
continue;
+ /* never include virtual columns */
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ continue;
}
key = NameStr(attr->attname);
diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql
index f6c2ef8d6a..440549c078 100644
--- a/src/pl/plpython/sql/plpython_trigger.sql
+++ b/src/pl/plpython/sql/plpython_trigger.sql
@@ -69,7 +69,8 @@ CREATE TABLE trigger_test
CREATE TABLE trigger_test_generated (
i int,
- j int GENERATED ALWAYS AS (i * 2) STORED
+ j int GENERATED ALWAYS AS (i * 2) STORED,
+ k int GENERATED ALWAYS AS (i * 3) VIRTUAL
);
CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpython3u AS $$
diff --git a/src/pl/tcl/expected/pltcl_trigger.out b/src/pl/tcl/expected/pltcl_trigger.out
index 129abd5ba6..5298e50a5e 100644
--- a/src/pl/tcl/expected/pltcl_trigger.out
+++ b/src/pl/tcl/expected/pltcl_trigger.out
@@ -63,7 +63,8 @@ CREATE TABLE trigger_test (
ALTER TABLE trigger_test DROP dropme;
CREATE TABLE trigger_test_generated (
i int,
- j int GENERATED ALWAYS AS (i * 2) STORED
+ j int GENERATED ALWAYS AS (i * 2) STORED,
+ k int GENERATED ALWAYS AS (i * 3) VIRTUAL
);
CREATE VIEW trigger_test_view AS SELECT i, v FROM trigger_test;
CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$
@@ -647,7 +648,7 @@ NOTICE: OLD: {}
NOTICE: TG_level: ROW
NOTICE: TG_name: show_trigger_data_trig_before
NOTICE: TG_op: INSERT
-NOTICE: TG_relatts: {{} i j}
+NOTICE: TG_relatts: {{} i j k}
NOTICE: TG_relid: bogus:12345
NOTICE: TG_table_name: trigger_test_generated
NOTICE: TG_table_schema: public
@@ -658,7 +659,7 @@ NOTICE: OLD: {}
NOTICE: TG_level: ROW
NOTICE: TG_name: show_trigger_data_trig_after
NOTICE: TG_op: INSERT
-NOTICE: TG_relatts: {{} i j}
+NOTICE: TG_relatts: {{} i j k}
NOTICE: TG_relid: bogus:12345
NOTICE: TG_table_name: trigger_test_generated
NOTICE: TG_table_schema: public
@@ -670,7 +671,7 @@ NOTICE: OLD: {i: 1, j: 2}
NOTICE: TG_level: ROW
NOTICE: TG_name: show_trigger_data_trig_before
NOTICE: TG_op: UPDATE
-NOTICE: TG_relatts: {{} i j}
+NOTICE: TG_relatts: {{} i j k}
NOTICE: TG_relid: bogus:12345
NOTICE: TG_table_name: trigger_test_generated
NOTICE: TG_table_schema: public
@@ -681,7 +682,7 @@ NOTICE: OLD: {i: 1, j: 2}
NOTICE: TG_level: ROW
NOTICE: TG_name: show_trigger_data_trig_after
NOTICE: TG_op: UPDATE
-NOTICE: TG_relatts: {{} i j}
+NOTICE: TG_relatts: {{} i j k}
NOTICE: TG_relid: bogus:12345
NOTICE: TG_table_name: trigger_test_generated
NOTICE: TG_table_schema: public
@@ -693,7 +694,7 @@ NOTICE: OLD: {i: 11, j: 22}
NOTICE: TG_level: ROW
NOTICE: TG_name: show_trigger_data_trig_before
NOTICE: TG_op: DELETE
-NOTICE: TG_relatts: {{} i j}
+NOTICE: TG_relatts: {{} i j k}
NOTICE: TG_relid: bogus:12345
NOTICE: TG_table_name: trigger_test_generated
NOTICE: TG_table_schema: public
@@ -704,7 +705,7 @@ NOTICE: OLD: {i: 11, j: 22}
NOTICE: TG_level: ROW
NOTICE: TG_name: show_trigger_data_trig_after
NOTICE: TG_op: DELETE
-NOTICE: TG_relatts: {{} i j}
+NOTICE: TG_relatts: {{} i j k}
NOTICE: TG_relid: bogus:12345
NOTICE: TG_table_name: trigger_test_generated
NOTICE: TG_table_schema: public
@@ -882,7 +883,7 @@ 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
----+---
+ i | j | k
+---+---+---
(0 rows)
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c
index 21b2b04593..cb715ad08b 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -3206,6 +3206,9 @@ pltcl_build_tuple_argument(HeapTuple tuple, TupleDesc tupdesc, bool include_gene
/* don't include unless requested */
if (!include_generated)
continue;
+ /* never include virtual columns */
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ continue;
}
/************************************************************
diff --git a/src/pl/tcl/sql/pltcl_trigger.sql b/src/pl/tcl/sql/pltcl_trigger.sql
index 2a244de83b..0ed00f4952 100644
--- a/src/pl/tcl/sql/pltcl_trigger.sql
+++ b/src/pl/tcl/sql/pltcl_trigger.sql
@@ -73,7 +73,8 @@ ALTER TABLE trigger_test DROP dropme;
CREATE TABLE trigger_test_generated (
i int,
- j int GENERATED ALWAYS AS (i * 2) STORED
+ j int GENERATED ALWAYS AS (i * 2) STORED,
+ k int GENERATED ALWAYS AS (i * 3) VIRTUAL
);
CREATE VIEW trigger_test_view AS SELECT i, v FROM trigger_test;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 6bfc6d040f..97157dc635 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -113,19 +113,20 @@ 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) STORED);
+CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (a * 3) VIRTUAL);
\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) stored
+ c | integer | | | generated always as (a * 3)
INSERT INTO test_like_gen_1 (a) VALUES (1);
SELECT * FROM test_like_gen_1;
- a | b
----+---
- 1 | 2
+ a | b | c
+---+---+---
+ 1 | 2 | 3
(1 row)
CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1);
@@ -135,12 +136,13 @@ CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1);
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
+ c | integer | | |
INSERT INTO test_like_gen_2 (a) VALUES (1);
SELECT * FROM test_like_gen_2;
- a | b
----+---
- 1 |
+ a | b | c
+---+---+---
+ 1 | |
(1 row)
CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);
@@ -150,12 +152,13 @@ CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);
--------+---------+-----------+----------+------------------------------------
a | integer | | |
b | integer | | | generated always as (a * 2) stored
+ c | integer | | | generated always as (a * 3)
INSERT INTO test_like_gen_3 (a) VALUES (1);
SELECT * FROM test_like_gen_3;
- a | b
----+---
- 1 | 2
+ a | b | c
+---+---+---
+ 1 | 2 | 3
(1 row)
DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3;
diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated_stored.out
similarity index 94%
rename from src/test/regress/expected/generated.out
rename to src/test/regress/expected/generated_stored.out
index 499072e14c..8f0951e26a 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -1,12 +1,15 @@
-- sanity check of system catalog
-SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's');
+SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
attrelid | attname | attgenerated
----------+---------+--------------
(0 rows)
+CREATE SCHEMA generated_stored_tests;
+GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
+SET search_path = generated_stored_tests;
CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
-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, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2;
table_name | column_name | column_default | is_nullable | is_generated | generation_expression
------------+-------------+----------------+-------------+--------------+-----------------------
gtest0 | a | | NO | NEVER |
@@ -15,14 +18,14 @@ SELECT table_name, column_name, column_default, is_nullable, is_generated, gener
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;
+SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
table_name | column_name | dependent_column
------------+-------------+------------------
gtest1 | a | b
(1 row)
\d gtest1
- Table "public.gtest1"
+ Table "generated_stored_tests.gtest1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
@@ -214,6 +217,27 @@ SELECT * FROM gtestm ORDER BY id;
2 | 20 | 200 | 40 | 400
(2 rows)
+DROP TABLE gtestm;
+CREATE TABLE gtestm (
+ a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (a * 2) STORED
+);
+INSERT INTO gtestm (a) SELECT g FROM generate_series(1, 10) g;
+MERGE INTO gtestm t USING gtestm AS s ON 2 * t.a = s.b WHEN MATCHED THEN DELETE RETURNING *;
+ a | b | a | b
+----+----+----+----
+ 1 | 2 | 1 | 2
+ 2 | 4 | 2 | 4
+ 3 | 6 | 3 | 6
+ 4 | 8 | 4 | 8
+ 5 | 10 | 5 | 10
+ 6 | 12 | 6 | 12
+ 7 | 14 | 7 | 14
+ 8 | 16 | 8 | 16
+ 9 | 18 | 9 | 18
+ 10 | 20 | 10 | 20
+(10 rows)
+
DROP TABLE gtestm;
-- views
CREATE VIEW gtest1v AS SELECT * FROM gtest1;
@@ -270,7 +294,7 @@ SELECT * FROM gtest1_1;
(0 rows)
\d gtest1_1
- Table "public.gtest1_1"
+ Table "generated_stored_tests.gtest1_1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
@@ -312,7 +336,7 @@ ERROR: column "b" inherits from generated column but specifies identity
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- ok, overrides parent
NOTICE: merging column "b" with inherited definition
\d+ gtestx
- Table "public.gtestx"
+ Table "generated_stored_tests.gtestx"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+-------------------------------------+---------+--------------+-------------
a | integer | | not null | | plain | |
@@ -348,7 +372,7 @@ NOTICE: merging multiple inherited definitions of column "b"
NOTICE: moving and merging column "b" with inherited definition
DETAIL: User-specified column moved to the position of the inherited column.
\d gtest1_y
- Table "public.gtest1_y"
+ Table "generated_stored_tests.gtest1_y"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
@@ -523,7 +547,7 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
ALTER TABLE gtest10 DROP COLUMN b CASCADE; -- drops c too
NOTICE: drop cascades to column c of table gtest10
\d gtest10
- Table "public.gtest10"
+ Table "generated_stored_tests.gtest10"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
@@ -622,7 +646,7 @@ CREATE INDEX gtest22c_b_idx ON gtest22c (b);
CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
\d gtest22c
- Table "public.gtest22c"
+ Table "generated_stored_tests.gtest22c"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
@@ -726,7 +750,7 @@ CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STOR
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) STORED REFERENCES gtest23a (x));
\d gtest23b
- Table "public.gtest23b"
+ Table "generated_stored_tests.gtest23b"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
@@ -759,6 +783,11 @@ CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a *
INSERT INTO gtest24 (a) VALUES (4); -- ok
INSERT INTO gtest24 (a) VALUES (6); -- error
ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check"
+CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
+CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) STORED);
+INSERT INTO gtest24r (a) VALUES (4); -- ok
+INSERT INTO gtest24r (a) VALUES (6); -- error
+ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check"
-- 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) STORED);
@@ -805,7 +834,7 @@ DROP TABLE gtest_child3;
CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) STORED);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
\d gtest_child
- Table "public.gtest_child"
+ Table "generated_stored_tests.gtest_child"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -814,7 +843,7 @@ ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
\d gtest_child2
- Table "public.gtest_child2"
+ Table "generated_stored_tests.gtest_child2"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
@@ -823,7 +852,7 @@ Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
\d gtest_child3
- Table "public.gtest_child3"
+ Table "generated_stored_tests.gtest_child3"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
@@ -855,7 +884,7 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
\d gtest_parent
- Partitioned table "public.gtest_parent"
+ Partitioned table "generated_stored_tests.gtest_parent"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -865,7 +894,7 @@ Partition key: RANGE (f1)
Number of partitions: 3 (Use \d+ to list them.)
\d gtest_child
- Table "public.gtest_child"
+ Table "generated_stored_tests.gtest_child"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
@@ -874,7 +903,7 @@ Number of partitions: 3 (Use \d+ to list them.)
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
\d gtest_child2
- Table "public.gtest_child2"
+ Table "generated_stored_tests.gtest_child2"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
@@ -883,7 +912,7 @@ Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
\d gtest_child3
- Table "public.gtest_child3"
+ Table "generated_stored_tests.gtest_child3"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
@@ -902,7 +931,7 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
-- alter generation expression of parent and all its children altogether
ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
\d gtest_parent
- Partitioned table "public.gtest_parent"
+ Partitioned table "generated_stored_tests.gtest_parent"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -912,7 +941,7 @@ Partition key: RANGE (f1)
Number of partitions: 3 (Use \d+ to list them.)
\d gtest_child
- Table "public.gtest_child"
+ Table "generated_stored_tests.gtest_child"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -921,7 +950,7 @@ Number of partitions: 3 (Use \d+ to list them.)
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
\d gtest_child2
- Table "public.gtest_child2"
+ Table "generated_stored_tests.gtest_child2"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -930,7 +959,7 @@ Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
\d gtest_child3
- Table "public.gtest_child3"
+ Table "generated_stored_tests.gtest_child3"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -987,7 +1016,7 @@ SELECT * FROM gtest25 ORDER BY a;
(2 rows)
\d gtest25
- Table "public.gtest25"
+ Table "generated_stored_tests.gtest25"
Column | Type | Collation | Nullable | Default
--------+------------------+-----------+----------+------------------------------------------------------
a | integer | | not null |
@@ -1011,7 +1040,7 @@ ERROR: cannot alter type of a column used by a generated column
DETAIL: Column "a" is used by generated column "x".
ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
\d gtest27
- Table "public.gtest27"
+ Table "generated_stored_tests.gtest27"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+--------------------------------------------
a | integer | | |
@@ -1038,7 +1067,7 @@ ALTER TABLE gtest27
ALTER COLUMN b TYPE bigint,
ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
\d gtest27
- Table "public.gtest27"
+ Table "generated_stored_tests.gtest27"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+------------------------------------------
a | bigint | | |
@@ -1052,7 +1081,7 @@ ALTER TABLE gtest27
ERROR: cannot alter type of a column used by a generated column
DETAIL: Column "a" is used by generated column "x".
\d gtest27
- Table "public.gtest27"
+ Table "generated_stored_tests.gtest27"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+------------------------------------------
a | bigint | | |
@@ -1080,7 +1109,7 @@ SELECT * FROM gtest29;
(2 rows)
\d gtest29
- Table "public.gtest29"
+ Table "generated_stored_tests.gtest29"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
@@ -1089,9 +1118,9 @@ SELECT * FROM gtest29;
ALTER TABLE gtest29 ALTER COLUMN a SET EXPRESSION AS (a * 3); -- error
ERROR: column "a" of relation "gtest29" is not a generated column
ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION; -- error
-ERROR: column "a" of relation "gtest29" is not a stored generated column
+ERROR: column "a" of relation "gtest29" is not a generated column
ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION IF EXISTS; -- notice
-NOTICE: column "a" of relation "gtest29" is not a stored generated column, skipping
+NOTICE: column "a" of relation "gtest29" is not a generated column, skipping
-- Change the expression
ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
SELECT * FROM gtest29;
@@ -1102,7 +1131,7 @@ SELECT * FROM gtest29;
(2 rows)
\d gtest29
- Table "public.gtest29"
+ Table "generated_stored_tests.gtest29"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
@@ -1121,7 +1150,7 @@ SELECT * FROM gtest29;
(4 rows)
\d gtest29
- Table "public.gtest29"
+ Table "generated_stored_tests.gtest29"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
@@ -1130,7 +1159,7 @@ SELECT * FROM gtest29;
-- check that dependencies between columns have also been removed
ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
\d gtest29
- Table "public.gtest29"
+ Table "generated_stored_tests.gtest29"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
b | integer | | |
@@ -1143,7 +1172,7 @@ CREATE TABLE gtest30 (
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
\d gtest30
- Table "public.gtest30"
+ Table "generated_stored_tests.gtest30"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
@@ -1151,7 +1180,7 @@ ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
Number of child tables: 1 (Use \d+ to list them.)
\d gtest30_1
- Table "public.gtest30_1"
+ Table "generated_stored_tests.gtest30_1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
@@ -1168,7 +1197,7 @@ CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
\d gtest30
- Table "public.gtest30"
+ Table "generated_stored_tests.gtest30"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
@@ -1176,7 +1205,7 @@ ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
Number of child tables: 1 (Use \d+ to list them.)
\d gtest30_1
- Table "public.gtest30_1"
+ Table "generated_stored_tests.gtest30_1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
@@ -1337,14 +1366,14 @@ CREATE TABLE gtest28a (
ALTER TABLE gtest28a DROP COLUMN a;
CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
\d gtest28*
- Table "public.gtest28a"
+ Table "generated_stored_tests.gtest28a"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
b | integer | | |
c | integer | | |
x | integer | | | generated always as (b * 2) stored
- Table "public.gtest28b"
+ Table "generated_stored_tests.gtest28b"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
b | integer | | |
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
new file mode 100644
index 0000000000..597ac4d941
--- /dev/null
+++ b/src/test/regress/expected/generated_virtual.out
@@ -0,0 +1,1363 @@
+-- sanity check of system catalog
+SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
+ attrelid | attname | attgenerated
+----------+---------+--------------
+(0 rows)
+
+CREATE SCHEMA generated_virtual_tests;
+GRANT USAGE ON SCHEMA generated_virtual_tests TO PUBLIC;
+SET search_path = generated_virtual_tests;
+CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) VIRTUAL);
+CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_virtual_tests' 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 WHERE table_schema = 'generated_virtual_tests' ORDER BY 1, 2, 3;
+ table_name | column_name | dependent_column
+------------+-------------+------------------
+ gtest1 | a | b
+(1 row)
+
+\d gtest1
+ Table "generated_virtual_tests.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) VIRTUAL GENERATED ALWAYS AS (a * 3) VIRTUAL);
+ERROR: multiple generation clauses specified for column "b" of table "gtest_err_1"
+LINE 1: ...RY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL 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) VIRTUAL);
+ERROR: cannot use generated column "b" in column generation expression
+LINE 1: ...2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) VIR...
+ ^
+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) VIRTUAL, c int GENERATED ALWAYS AS (b * 3) VIRTUAL);
+ERROR: cannot use generated column "b" in column generation expression
+LINE 1: ...YS AS (a * 2) VIRTUAL, c int GENERATED ALWAYS AS (b * 3) VIR...
+ ^
+DETAIL: A generated column cannot reference another generated column.
+-- a whole-row var is a self-reference on steroids, so disallow that too
+CREATE TABLE gtest_err_2c (a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) VIRTUAL);
+ERROR: cannot use whole-row variable in column generation expression
+LINE 2: b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) VIRT...
+ ^
+DETAIL: This would cause the generated column to depend on its own value.
+-- invalid reference
+CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) VIRTUAL);
+ERROR: column "c" does not exist
+LINE 1: ..._3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) VIR...
+ ^
+-- generation expression must be immutable
+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) VIRTUAL);
+ERROR: generation expression is not immutable
+-- ... but be sure that the immutability test is accurate
+CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') VIRTUAL);
+DROP TABLE gtest2;
+-- cannot have default/identity and generated
+CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) VIRTUAL);
+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) VIRTUAL);
+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
+-- (except tableoid, which we test below)
+CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) VIRTUAL);
+ERROR: cannot use system column "xmin" in column generation expression
+LINE 1: ...a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37...
+ ^
+-- various prohibited constructs
+CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) VIRTUAL);
+ERROR: aggregate functions are not allowed in column generation expressions
+LINE 1: ...7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) VI...
+ ^
+CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) VIRTUAL);
+ERROR: window functions are not allowed in column generation expressions
+LINE 1: ...7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number...
+ ^
+CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) VIRTUAL);
+ERROR: cannot use subquery in column generation expression
+LINE 1: ...7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)...
+ ^
+CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) VIRTUAL);
+ERROR: set-returning functions are not allowed in column generation expressions
+LINE 1: ...7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_s...
+ ^
+-- GENERATED BY DEFAULT not allowed
+CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) VIRTUAL);
+ERROR: for a generated column, GENERATED ALWAYS must be specified
+LINE 1: ...E gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT...
+ ^
+INSERT INTO gtest1 VALUES (1);
+INSERT INTO gtest1 VALUES (2, DEFAULT); -- ok
+INSERT INTO gtest1 VALUES (3, 33); -- error
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+INSERT INTO gtest1 VALUES (3, 33), (4, 44); -- error
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+INSERT INTO gtest1 VALUES (3, DEFAULT), (4, 44); -- error
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+INSERT INTO gtest1 VALUES (3, 33), (4, DEFAULT); -- error
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+INSERT INTO gtest1 VALUES (3, DEFAULT), (4, DEFAULT); -- ok
+SELECT * FROM gtest1 ORDER BY a;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+ 3 | 6
+ 4 | 8
+(4 rows)
+
+DELETE FROM gtest1 WHERE a >= 3;
+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)
+
+-- test MERGE
+CREATE TABLE gtestm (
+ id int PRIMARY KEY,
+ f1 int,
+ f2 int,
+ f3 int GENERATED ALWAYS AS (f1 * 2) VIRTUAL,
+ f4 int GENERATED ALWAYS AS (f2 * 2) VIRTUAL
+);
+INSERT INTO gtestm VALUES (1, 5, 100);
+MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
+ WHEN MATCHED THEN UPDATE SET f1 = v.f1
+ WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200);
+SELECT * FROM gtestm ORDER BY id;
+ id | f1 | f2 | f3 | f4
+----+----+-----+----+-----
+ 1 | 10 | 100 | 20 | 200
+ 2 | 20 | 200 | 40 | 400
+(2 rows)
+
+DROP TABLE gtestm;
+CREATE TABLE gtestm (
+ a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+);
+INSERT INTO gtestm (a) SELECT g FROM generate_series(1, 10) g;
+MERGE INTO gtestm t USING gtestm AS s ON 2 * t.a = s.b WHEN MATCHED THEN DELETE RETURNING *;
+ a | b | a | b
+----+----+----+----
+ 1 | 2 | 1 | 2
+ 2 | 4 | 2 | 4
+ 3 | 6 | 3 | 6
+ 4 | 8 | 4 | 8
+ 5 | 10 | 5 | 10
+ 6 | 12 | 6 | 12
+ 7 | 14 | 7 | 14
+ 8 | 16 | 8 | 16
+ 9 | 18 | 9 | 18
+ 10 | 20 | 10 | 20
+(10 rows)
+
+DROP TABLE gtestm;
+-- views
+CREATE VIEW gtest1v AS SELECT * FROM gtest1;
+SELECT * FROM gtest1v;
+ a | b
+---+---
+ 3 | 6
+(1 row)
+
+INSERT INTO gtest1v VALUES (4, 8); -- error
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+INSERT INTO gtest1v VALUES (5, DEFAULT); -- ok
+INSERT INTO gtest1v VALUES (6, 66), (7, 77); -- error
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+INSERT INTO gtest1v VALUES (6, DEFAULT), (7, 77); -- error
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok
+ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
+INSERT INTO gtest1v VALUES (8, DEFAULT); -- error
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+SELECT * FROM gtest1v;
+ a | b
+---+----
+ 3 | 6
+ 5 | 10
+ 6 | 12
+ 7 | 14
+(4 rows)
+
+DELETE FROM gtest1v WHERE a >= 5;
+DROP VIEW gtest1v;
+-- 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 "generated_virtual_tests.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)
+
+-- can't have generated column that is a child of normal column
+CREATE TABLE gtest_normal (a int, b int);
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL) INHERITS (gtest_normal); -- error
+NOTICE: merging column "a" with inherited definition
+NOTICE: merging column "b" with inherited definition
+ERROR: child column "b" specifies generation expression
+HINT: A child table column cannot be generated unless its parent column is.
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+ALTER TABLE gtest_normal_child INHERIT gtest_normal; -- error
+ERROR: column "b" in child table must not be a generated column
+DROP TABLE gtest_normal, gtest_normal_child;
+-- test inheritance mismatches between parent and child
+CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error
+NOTICE: merging column "b" with inherited definition
+ERROR: column "b" inherits from generated column but specifies default
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
+NOTICE: merging column "b" with inherited definition
+ERROR: column "b" inherits from generated column but specifies identity
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
+NOTICE: merging column "b" with inherited definition
+ERROR: column "b" inherits from generated column of different kind
+DETAIL: Parent column is VIRTUAL, child column is STORED.
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) VIRTUAL) INHERITS (gtest1); -- ok, overrides parent
+NOTICE: merging column "b" with inherited definition
+\d+ gtestx
+ Table "generated_virtual_tests.gtestx"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+------------------------------+---------+--------------+-------------
+ a | integer | | not null | | plain | |
+ b | integer | | | generated always as (a * 22) | plain | |
+ x | integer | | | | plain | |
+Inherits: gtest1
+
+INSERT INTO gtestx (a, x) VALUES (11, 22);
+SELECT * FROM gtest1;
+ a | b
+----+----
+ 3 | 6
+ 4 | 8
+ 11 | 22
+(3 rows)
+
+SELECT * FROM gtestx;
+ a | b | x
+----+-----+----
+ 11 | 242 | 22
+(1 row)
+
+CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
+ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
+ERROR: column "b" in child table must be a generated column
+CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
+CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) VIRTUAL, a int NOT NULL);
+ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
+-- test multiple inheritance mismatches
+CREATE TABLE gtesty (x int, b int DEFAULT 55);
+CREATE TABLE gtest1_y () INHERITS (gtest0, gtesty); -- error
+NOTICE: merging multiple inherited definitions of column "b"
+ERROR: inherited column "b" has a generation conflict
+DROP TABLE gtesty;
+CREATE TABLE gtesty (x int, b int);
+CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
+NOTICE: merging multiple inherited definitions of column "b"
+ERROR: inherited column "b" has a generation conflict
+DROP TABLE gtesty;
+CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) VIRTUAL);
+CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
+NOTICE: merging multiple inherited definitions of column "b"
+ERROR: column "b" inherits conflicting generation expressions
+HINT: To resolve the conflict, specify a generation expression explicitly.
+CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) VIRTUAL) INHERITS (gtest1, gtesty); -- ok
+NOTICE: merging multiple inherited definitions of column "b"
+NOTICE: moving and merging column "b" with inherited definition
+DETAIL: User-specified column moved to the position of the inherited column.
+\d gtest1_y
+ Table "generated_virtual_tests.gtest1_y"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | not null |
+ b | integer | | | generated always as (x + 1)
+ x | integer | | |
+Inherits: gtest1,
+ gtesty
+
+-- test correct handling of GENERATED column that's only in child
+CREATE TABLE gtestp (f1 int);
+CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) VIRTUAL) INHERITS(gtestp);
+INSERT INTO gtestc values(42);
+TABLE gtestc;
+ f1 | f2
+----+----
+ 42 | 43
+(1 row)
+
+UPDATE gtestp SET f1 = f1 * 10;
+TABLE gtestc;
+ f1 | f2
+-----+-----
+ 420 | 421
+(1 row)
+
+DROP TABLE gtestp CASCADE;
+NOTICE: drop cascades to table gtestc
+-- test update XXX
+CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) VIRTUAL);
+INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
+SELECT * FROM gtest3 ORDER BY a;
+ a | b
+---+---
+ 1 | 3
+ 2 | 6
+ 3 | 9
+ |
+(4 rows)
+
+UPDATE gtest3 SET a = 22 WHERE a = 2;
+SELECT * FROM gtest3 ORDER BY a;
+ a | b
+----+----
+ 1 | 3
+ 3 | 9
+ 22 | 66
+ |
+(4 rows)
+
+CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) VIRTUAL);
+INSERT INTO gtest3a (a) VALUES ('a'), ('b'), ('c'), (NULL);
+SELECT * FROM gtest3a ORDER BY a;
+ a | b
+---+-----
+ a | a+a
+ b | b+b
+ c | c+c
+ |
+(4 rows)
+
+UPDATE gtest3a SET a = 'bb' WHERE a = 'b';
+SELECT * FROM gtest3a ORDER BY a;
+ a | b
+----+-------
+ a | a+a
+ bb | bb+bb
+ c | c+c
+ |
+(4 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) VIRTUAL);
+INSERT INTO gtest2 VALUES (1);
+SELECT * FROM gtest2;
+ a | b
+---+---
+ 1 |
+(1 row)
+
+-- simple column reference for varlena types
+CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) VIRTUAL);
+INSERT INTO gtest_varlena (a) VALUES('01234567890123456789');
+INSERT INTO gtest_varlena (a) VALUES(NULL);
+SELECT * FROM gtest_varlena ORDER BY a;
+ a | b
+----------------------+----------------------
+ 01234567890123456789 | 01234567890123456789
+ |
+(2 rows)
+
+DROP TABLE gtest_varlena;
+-- 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)) VIRTUAL
+);
+INSERT INTO gtest4 VALUES (1), (6);
+SELECT * FROM gtest4;
+ a | b
+---+---------
+ 1 | (2,3)
+ 6 | (12,18)
+(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 = 'gtest_tableoid'::regclass) VIRTUAL
+);
+INSERT INTO gtest_tableoid VALUES (1), (2);
+ALTER TABLE gtest_tableoid ADD COLUMN
+ c regclass GENERATED ALWAYS AS (tableoid) VIRTUAL;
+SELECT * FROM gtest_tableoid;
+ a | b | c
+---+---+----------------
+ 1 | t | gtest_tableoid
+ 2 | t | gtest_tableoid
+(2 rows)
+
+-- drop column behavior
+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) VIRTUAL);
+ALTER TABLE gtest10 DROP COLUMN b; -- fails
+ERROR: cannot drop column b of table gtest10 because other objects depend on it
+DETAIL: column c of table gtest10 depends on column b of table gtest10
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE gtest10 DROP COLUMN b CASCADE; -- drops c too
+NOTICE: drop cascades to column c of table gtest10
+\d gtest10
+ Table "generated_virtual_tests.gtest10"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+Indexes:
+ "gtest10_pkey" PRIMARY KEY, btree (a)
+
+CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+ALTER TABLE gtest10a DROP COLUMN b;
+INSERT INTO gtest10a (a) VALUES (1);
+-- 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 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 gtest12s TO regress_user11;
+ERROR: relation "gtest12s" does not exist
+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 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 table gtest12v
+RESET ROLE;
+DROP FUNCTION gf1(int); -- fail
+ERROR: cannot drop function gf1(integer) because other objects depend on it
+DETAIL: column c of table gtest12v depends on function gf1(integer)
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+DROP TABLE gtest11v, gtest12v;
+DROP FUNCTION gf1(int);
+DROP USER regress_user11;
+-- check constraints
+CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL 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, virtual).
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint
+ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns
+DETAIL: Column "b" of relation "gtest20" is a virtual generated column.
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
+ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns
+DETAIL: Column "b" of relation "gtest20" is a virtual generated column.
+CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+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" of relation "gtest20a" is violated by some row
+CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+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" of relation "gtest20b" is violated by some row
+-- not-null constraints (currently not supported)
+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
+ERROR: not-null constraints are not supported on virtual generated columns
+--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)) VIRTUAL);
+ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
+ERROR: not-null constraints are not supported on virtual generated columns
+DETAIL: Column "b" of relation "gtest21b" is a virtual generated column.
+--INSERT INTO gtest21b (a) VALUES (1); -- ok
+--INSERT INTO gtest21b (a) VALUES (0); -- violates constraint
+ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
+--INSERT INTO gtest21b (a) VALUES (0); -- ok now
+-- index constraints
+CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE);
+ERROR: index creation on virtual generated columns is not supported
+--INSERT INTO gtest22a VALUES (2);
+--INSERT INTO gtest22a VALUES (3);
+--INSERT INTO gtest22a VALUES (4);
+CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY KEY (a, b));
+ERROR: index creation on virtual generated columns is not supported
+--INSERT INTO gtest22b VALUES (2);
+--INSERT INTO gtest22b VALUES (2);
+-- indexes
+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+--CREATE INDEX gtest22c_b_idx ON gtest22c (b);
+--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 = 4;
+--SELECT * FROM gtest22c WHERE b = 4;
+--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;
+--ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
+--ANALYZE gtest22c;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
+--SELECT * FROM gtest22c WHERE b = 8;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
+--SELECT * FROM gtest22c WHERE b * 3 = 12;
+--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;
+-- 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) VIRTUAL 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) VIRTUAL 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) VIRTUAL REFERENCES gtest23a (x));
+ERROR: foreign key constraints on virtual generated columns are not supported
+--\d gtest23b
+--INSERT INTO gtest23b VALUES (1); -- ok
+--INSERT INTO gtest23b VALUES (5); -- error
+--ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
+--ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
+--DROP TABLE gtest23b;
+--DROP TABLE gtest23a;
+CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, PRIMARY KEY (y));
+ERROR: index creation on virtual generated columns is not supported
+--INSERT INTO gtest23p VALUES (1), (2), (3);
+CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
+ERROR: relation "gtest23p" does not exist
+--INSERT INTO gtest23q VALUES (1, 2); -- ok
+--INSERT INTO gtest23q VALUES (2, 5); -- error
+-- domains
+CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
+CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL);
+ERROR: virtual generated column "b" cannot have a domain type
+--INSERT INTO gtest24 (a) VALUES (4); -- ok
+--INSERT INTO gtest24 (a) VALUES (6); -- error
+CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
+CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) VIRTUAL);
+ERROR: virtual generated column "b" cannot have a domain type
+--INSERT INTO gtest24r (a) VALUES (4); -- ok
+--INSERT INTO gtest24r (a) VALUES (6); -- error
+-- 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) VIRTUAL);
+ERROR: generated columns are not supported on typed tables
+DROP TYPE gtest_type CASCADE;
+-- partitioning cases
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_child PARTITION OF gtest_parent (
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) VIRTUAL
+) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
+ERROR: child column "f3" specifies generation expression
+HINT: A child table column cannot be generated unless its parent column is.
+CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
+ERROR: column "f3" in child table must not be a generated column
+DROP TABLE gtest_parent, gtest_child;
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_child PARTITION OF gtest_parent
+ FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- inherits gen expr
+CREATE TABLE gtest_child2 PARTITION OF gtest_parent (
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) VIRTUAL -- overrides gen expr
+) FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
+CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
+ f3 DEFAULT 42 -- error
+) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
+ERROR: column "f3" inherits from generated column but specifies default
+CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
+ f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY -- error
+) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
+ERROR: identity columns are not supported on partitions
+CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
+ f3 GENERATED ALWAYS AS (f2 * 2) STORED -- error
+) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
+ERROR: column "f3" inherits from generated column of different kind
+DETAIL: Parent column is VIRTUAL, child column is STORED.
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
+ERROR: column "f3" in child table must be a generated column
+DROP TABLE gtest_child3;
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint DEFAULT 42);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
+ERROR: column "f3" in child table must be a generated column
+DROP TABLE gtest_child3;
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS IDENTITY);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
+ERROR: table "gtest_child3" being attached contains an identity column "f3"
+DETAIL: The new partition may not contain an identity column.
+DROP TABLE gtest_child3;
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) VIRTUAL);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
+\d gtest_child
+ Table "generated_virtual_tests.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 2)
+Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
+
+\d gtest_child2
+ Table "generated_virtual_tests.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 22)
+Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
+
+\d gtest_child3
+ Table "generated_virtual_tests.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 33)
+Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
+
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 2);
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-08-15', 3);
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 1 | 2
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child2 | 08-15-2016 | 3 | 6
+(3 rows)
+
+UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child2 | 08-15-2016 | 3 | 6
+ gtest_child3 | 09-13-2016 | 1 | 2
+(3 rows)
+
+-- alter only parent's and one child's generation expression
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
+ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns
+DETAIL: Column "f3" of relation "gtest_parent" is a virtual generated column.
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
+ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns
+DETAIL: Column "f3" of relation "gtest_child" is a virtual generated column.
+\d gtest_parent
+ Partitioned table "generated_virtual_tests.gtest_parent"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 2)
+Partition key: RANGE (f1)
+Number of partitions: 3 (Use \d+ to list them.)
+
+\d gtest_child
+ Table "generated_virtual_tests.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 2)
+Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
+
+\d gtest_child2
+ Table "generated_virtual_tests.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 22)
+Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
+
+\d gtest_child3
+ Table "generated_virtual_tests.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 33)
+Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
+
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child2 | 08-15-2016 | 3 | 6
+ gtest_child3 | 09-13-2016 | 1 | 2
+(3 rows)
+
+-- alter generation expression of parent and all its children altogether
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
+ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns
+DETAIL: Column "f3" of relation "gtest_parent" is a virtual generated column.
+\d gtest_parent
+ Partitioned table "generated_virtual_tests.gtest_parent"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 2)
+Partition key: RANGE (f1)
+Number of partitions: 3 (Use \d+ to list them.)
+
+\d gtest_child
+ Table "generated_virtual_tests.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 2)
+Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
+
+\d gtest_child2
+ Table "generated_virtual_tests.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 22)
+Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
+
+\d gtest_child3
+ Table "generated_virtual_tests.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 33)
+Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
+
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child2 | 08-15-2016 | 3 | 6
+ gtest_child3 | 09-13-2016 | 1 | 2
+(3 rows)
+
+-- we leave these tables around for purposes of testing dump/reload/upgrade
+-- generated columns in partition key (not allowed)
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3);
+ERROR: cannot use generated column in partition key
+LINE 1: ...NERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3);
+ ^
+DETAIL: Column "f3" is a generated column.
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3));
+ERROR: cannot use generated column in partition key
+LINE 1: ...D ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3));
+ ^
+DETAIL: Column "f3" is a generated column.
+-- 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 * 2) VIRTUAL, ALTER COLUMN b SET EXPRESSION AS (a * 3);
+ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns
+DETAIL: Column "b" of relation "gtest25" is a virtual generated column.
+SELECT * FROM gtest25 ORDER BY a;
+ a
+---
+ 3
+ 4
+(2 rows)
+
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) VIRTUAL; -- error
+ERROR: column "b" does not exist
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) VIRTUAL; -- error
+ERROR: column "z" does not exist
+ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42,
+ ADD COLUMN x int GENERATED ALWAYS AS (c * 4) VIRTUAL;
+ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101;
+ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
+ ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) VIRTUAL;
+SELECT * FROM gtest25 ORDER BY a;
+ a | c | x | d | y
+---+----+-----+-----+-----
+ 3 | 42 | 168 | 101 | 404
+ 4 | 42 | 168 | 101 | 404
+(2 rows)
+
+\d gtest25
+ Table "generated_virtual_tests.gtest25"
+ Column | Type | Collation | Nullable | Default
+--------+------------------+-----------+----------+-----------------------------------------------
+ a | integer | | not null |
+ c | integer | | | 42
+ x | integer | | | generated always as (c * 4)
+ d | double precision | | | 101
+ y | double precision | | | generated always as (d * 4::double precision)
+Indexes:
+ "gtest25_pkey" PRIMARY KEY, btree (a)
+
+-- ALTER TABLE ... ALTER COLUMN
+CREATE TABLE gtest27 (
+ a int,
+ b int,
+ x int GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL
+);
+INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
+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 "x".
+ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
+\d gtest27
+ Table "generated_virtual_tests.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
+ a | integer | | |
+ b | integer | | |
+ x | numeric | | | generated always as (((a + b) * 2))
+
+SELECT * FROM gtest27;
+ a | b | x
+---+----+----
+ 3 | 7 | 20
+ 4 | 11 | 30
+(2 rows)
+
+ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0; -- error
+ERROR: cannot specify USING when altering type of generated column
+DETAIL: Column "x" is a generated column.
+ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error
+ERROR: column "x" of relation "gtest27" is a generated column
+-- It's possible to alter the column types this way:
+ALTER TABLE gtest27
+ DROP COLUMN x,
+ ALTER COLUMN a TYPE bigint,
+ ALTER COLUMN b TYPE bigint,
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL;
+\d gtest27
+ Table "generated_virtual_tests.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-----------------------------------
+ a | bigint | | |
+ b | bigint | | |
+ x | bigint | | | generated always as ((a + b) * 2)
+
+-- Ideally you could just do this, but not today (and should x change type?):
+ALTER TABLE gtest27
+ ALTER COLUMN a TYPE float8,
+ ALTER COLUMN b TYPE float8; -- error
+ERROR: cannot alter type of a column used by a generated column
+DETAIL: Column "a" is used by generated column "x".
+\d gtest27
+ Table "generated_virtual_tests.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-----------------------------------
+ a | bigint | | |
+ b | bigint | | |
+ x | bigint | | | generated always as ((a + b) * 2)
+
+SELECT * FROM gtest27;
+ a | b | x
+---+----+----
+ 3 | 7 | 20
+ 4 | 11 | 30
+(2 rows)
+
+-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
+CREATE TABLE gtest29 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+);
+INSERT INTO gtest29 (a) VALUES (3), (4);
+SELECT * FROM gtest29;
+ a | b
+---+---
+ 3 | 6
+ 4 | 8
+(2 rows)
+
+\d gtest29
+ Table "generated_virtual_tests.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+
+ALTER TABLE gtest29 ALTER COLUMN a SET EXPRESSION AS (a * 3); -- error
+ERROR: column "a" of relation "gtest29" is not a generated column
+ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION; -- error
+ERROR: column "a" of relation "gtest29" is not a generated column
+ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION IF EXISTS; -- notice
+NOTICE: column "a" of relation "gtest29" is not a generated column, skipping
+-- Change the expression
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
+ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns
+DETAIL: Column "b" of relation "gtest29" is a virtual generated column.
+SELECT * FROM gtest29;
+ a | b
+---+---
+ 3 | 6
+ 4 | 8
+(2 rows)
+
+\d gtest29
+ Table "generated_virtual_tests.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+
+ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;
+ERROR: ALTER TABLE / DROP EXPRESSION is not supported for virtual generated columns
+DETAIL: Column "b" of relation "gtest29" is a virtual generated column.
+INSERT INTO gtest29 (a) VALUES (5);
+INSERT INTO gtest29 (a, b) VALUES (6, 66);
+ERROR: cannot insert a non-DEFAULT value into column "b"
+DETAIL: Column "b" is a generated column.
+SELECT * FROM gtest29;
+ a | b
+---+----
+ 3 | 6
+ 4 | 8
+ 5 | 10
+(3 rows)
+
+\d gtest29
+ Table "generated_virtual_tests.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+
+-- check that dependencies between columns have also been removed
+ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
+ERROR: cannot drop column a of table gtest29 because other objects depend on it
+DETAIL: column b of table gtest29 depends on column a of table gtest29
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+\d gtest29
+ Table "generated_virtual_tests.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+
+-- with inheritance
+CREATE TABLE gtest30 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+);
+CREATE TABLE gtest30_1 () INHERITS (gtest30);
+ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
+ERROR: ALTER TABLE / DROP EXPRESSION is not supported for virtual generated columns
+DETAIL: Column "b" of relation "gtest30" is a virtual generated column.
+\d gtest30
+ Table "generated_virtual_tests.gtest30"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d gtest30_1
+ Table "generated_virtual_tests.gtest30_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+Inherits: gtest30
+
+DROP TABLE gtest30 CASCADE;
+NOTICE: drop cascades to table gtest30_1
+CREATE TABLE gtest30 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+);
+CREATE TABLE gtest30_1 () INHERITS (gtest30);
+ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
+ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
+\d gtest30
+ Table "generated_virtual_tests.gtest30"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d gtest30_1
+ Table "generated_virtual_tests.gtest30_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+Inherits: gtest30
+
+ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
+ERROR: cannot drop generation expression from inherited column
+-- triggers
+CREATE TABLE gtest26 (
+ a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+);
+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) -- error
+ ^
+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) -- error
+ ^
+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,)
+SELECT * FROM gtest26 ORDER BY a;
+ a | b
+----+----
+ -2 | -4
+ 0 | 0
+ 3 | 6
+(3 rows)
+
+UPDATE gtest26 SET a = a * -2;
+INFO: gtest1: BEFORE: old = (-2,)
+INFO: gtest1: BEFORE: new = (4,)
+INFO: gtest3: AFTER: old = (-2,)
+INFO: gtest3: AFTER: new = (4,)
+INFO: gtest4: AFTER: old = (3,)
+INFO: gtest4: AFTER: new = (-6,)
+SELECT * FROM gtest26 ORDER BY a;
+ a | b
+----+-----
+ -6 | -12
+ 0 | 0
+ 4 | 8
+(3 rows)
+
+DELETE FROM gtest26 WHERE a = -6;
+INFO: gtest1: BEFORE: old = (-6,)
+INFO: gtest3: AFTER: old = (-6,)
+SELECT * FROM gtest26 ORDER BY a;
+ a | b
+---+---
+ 0 | 0
+ 4 | 8
+(2 rows)
+
+DROP TRIGGER gtest1 ON gtest26;
+DROP TRIGGER gtest2 ON gtest26;
+DROP TRIGGER gtest3 ON gtest26;
+-- check disallowed modification of virtual columns
+-- TODO
+-- 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();
+ERROR: virtual generated columns are not supported as trigger columns
+DETAIL: Column "b" is a virtual generated column.
+UPDATE gtest26 SET a = 1 WHERE a = 0;
+DROP TRIGGER gtest11 ON gtest26;
+ERROR: trigger "gtest11" for table "gtest26" does not exist
+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.b = 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,)
+INFO: gtest12_01: BEFORE: new = (11,)
+ERROR: trigger modified virtual generated column value
+SELECT * FROM gtest26 ORDER BY a;
+ a | b
+---+---
+ 1 | 2
+(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) VIRTUAL
+);
+ALTER TABLE gtest28a DROP COLUMN a;
+CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
+\d gtest28*
+ Table "generated_virtual_tests.gtest28a"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ b | integer | | |
+ c | integer | | |
+ x | integer | | | generated always as (b * 2)
+
+ Table "generated_virtual_tests.gtest28b"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ b | integer | | |
+ c | integer | | |
+ x | integer | | | generated always as (b * 2)
+
+-- TODO: extra tests to weave into the right places
+-- sublinks
+CREATE TABLE t2 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2),
+ d int DEFAULT 22
+);
+INSERT INTO t2 (a) SELECT g FROM generate_series(1, 10) g;
+SELECT a, b, d, (SELECT t2.b) FROM t2;
+ a | b | d | b
+----+----+----+----
+ 1 | 2 | 22 | 2
+ 2 | 4 | 22 | 4
+ 3 | 6 | 22 | 6
+ 4 | 8 | 22 | 8
+ 5 | 10 | 22 | 10
+ 6 | 12 | 22 | 12
+ 7 | 14 | 22 | 14
+ 8 | 16 | 22 | 16
+ 9 | 18 | 22 | 18
+ 10 | 20 | 22 | 20
+(10 rows)
+
+DROP TABLE t2;
+-- collate
+/*
+CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
+CREATE TABLE t5 (
+ a text collate "C",
+ b text collate "C" GENERATED ALWAYS AS (a collate case_insensitive) ,
+ d int DEFAULT 22
+);
+INSERT INTO t5 (a, d) values ('d1', 28), ('D2', 27), ('D1', 26);
+SELECT * FROM t5 ORDER BY b ASC, d ASC;
+DROP TABLE t5;
+DROP COLLATION case_insensitive;
+*/
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f53a526f7c..2e56537f19 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -66,7 +66,10 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
# ----------
# Another group of parallel tests
# ----------
-test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password identity generated join_hash
+test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password identity generated_stored join_hash
+
+# TODO: find a place for this (above group is full)
+test: generated_virtual
# ----------
# Additional BRIN tests
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 04008a027b..ddf893f7ec 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -51,7 +51,7 @@ INSERT INTO test_like_id_3 (b) VALUES ('b3');
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) STORED);
+CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (a * 3) VIRTUAL);
\d test_like_gen_1
INSERT INTO test_like_gen_1 (a) VALUES (1);
SELECT * FROM test_like_gen_1;
diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated_stored.sql
similarity index 96%
rename from src/test/regress/sql/generated.sql
rename to src/test/regress/sql/generated_stored.sql
index cb55d77821..36a6c2bddb 100644
--- a/src/test/regress/sql/generated.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -1,13 +1,17 @@
-- sanity check of system catalog
-SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's');
+SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
+CREATE SCHEMA generated_stored_tests;
+GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
+SET search_path = generated_stored_tests;
+
CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
-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, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2;
-SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage ORDER BY 1, 2, 3;
+SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
\d gtest1
@@ -99,6 +103,14 @@ MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
SELECT * FROM gtestm ORDER BY id;
DROP TABLE gtestm;
+CREATE TABLE gtestm (
+ a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (a * 2) STORED
+);
+INSERT INTO gtestm (a) SELECT g FROM generate_series(1, 10) g;
+MERGE INTO gtestm t USING gtestm AS s ON 2 * t.a = s.b WHEN MATCHED THEN DELETE RETURNING *;
+DROP TABLE gtestm;
+
-- views
CREATE VIEW gtest1v AS SELECT * FROM gtest1;
SELECT * FROM gtest1v;
@@ -386,6 +398,10 @@ CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) STORED);
INSERT INTO gtest24 (a) VALUES (4); -- ok
INSERT INTO gtest24 (a) VALUES (6); -- error
+CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
+CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) STORED);
+INSERT INTO gtest24r (a) VALUES (4); -- ok
+INSERT INTO gtest24r (a) VALUES (6); -- error
-- typed tables (currently not supported)
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
new file mode 100644
index 0000000000..2bb9e1b45f
--- /dev/null
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -0,0 +1,726 @@
+-- sanity check of system catalog
+SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
+
+
+CREATE SCHEMA generated_virtual_tests;
+GRANT USAGE ON SCHEMA generated_virtual_tests TO PUBLIC;
+SET search_path = generated_virtual_tests;
+
+CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) VIRTUAL);
+CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+
+SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_virtual_tests' ORDER BY 1, 2;
+
+SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_virtual_tests' 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) VIRTUAL GENERATED ALWAYS AS (a * 3) VIRTUAL);
+
+-- references to other generated columns, including self-references
+CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) VIRTUAL);
+CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c int GENERATED ALWAYS AS (b * 3) VIRTUAL);
+-- a whole-row var is a self-reference on steroids, so disallow that too
+CREATE TABLE gtest_err_2c (a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) VIRTUAL);
+
+-- invalid reference
+CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) VIRTUAL);
+
+-- generation expression must be immutable
+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) VIRTUAL);
+-- ... but be sure that the immutability test is accurate
+CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') VIRTUAL);
+DROP TABLE gtest2;
+
+-- cannot have default/identity and generated
+CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) VIRTUAL);
+
+-- reference to system column not allowed in generated column
+-- (except tableoid, which we test below)
+CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) VIRTUAL);
+
+-- various prohibited constructs
+CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) VIRTUAL);
+CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) VIRTUAL);
+CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) VIRTUAL);
+CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) VIRTUAL);
+
+-- GENERATED BY DEFAULT not allowed
+CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) VIRTUAL);
+
+INSERT INTO gtest1 VALUES (1);
+INSERT INTO gtest1 VALUES (2, DEFAULT); -- ok
+INSERT INTO gtest1 VALUES (3, 33); -- error
+INSERT INTO gtest1 VALUES (3, 33), (4, 44); -- error
+INSERT INTO gtest1 VALUES (3, DEFAULT), (4, 44); -- error
+INSERT INTO gtest1 VALUES (3, 33), (4, DEFAULT); -- error
+INSERT INTO gtest1 VALUES (3, DEFAULT), (4, DEFAULT); -- ok
+
+SELECT * FROM gtest1 ORDER BY a;
+DELETE FROM gtest1 WHERE a >= 3;
+
+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;
+
+-- test MERGE
+CREATE TABLE gtestm (
+ id int PRIMARY KEY,
+ f1 int,
+ f2 int,
+ f3 int GENERATED ALWAYS AS (f1 * 2) VIRTUAL,
+ f4 int GENERATED ALWAYS AS (f2 * 2) VIRTUAL
+);
+INSERT INTO gtestm VALUES (1, 5, 100);
+MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
+ WHEN MATCHED THEN UPDATE SET f1 = v.f1
+ WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200);
+SELECT * FROM gtestm ORDER BY id;
+DROP TABLE gtestm;
+
+CREATE TABLE gtestm (
+ a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+);
+INSERT INTO gtestm (a) SELECT g FROM generate_series(1, 10) g;
+MERGE INTO gtestm t USING gtestm AS s ON 2 * t.a = s.b WHEN MATCHED THEN DELETE RETURNING *;
+DROP TABLE gtestm;
+
+-- views
+CREATE VIEW gtest1v AS SELECT * FROM gtest1;
+SELECT * FROM gtest1v;
+INSERT INTO gtest1v VALUES (4, 8); -- error
+INSERT INTO gtest1v VALUES (5, DEFAULT); -- ok
+INSERT INTO gtest1v VALUES (6, 66), (7, 77); -- error
+INSERT INTO gtest1v VALUES (6, DEFAULT), (7, 77); -- error
+INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error
+INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok
+
+ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
+INSERT INTO gtest1v VALUES (8, DEFAULT); -- error
+INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error
+
+SELECT * FROM gtest1v;
+DELETE FROM gtest1v WHERE a >= 5;
+DROP VIEW gtest1v;
+
+-- 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;
+
+-- can't have generated column that is a child of normal column
+CREATE TABLE gtest_normal (a int, b int);
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL) INHERITS (gtest_normal); -- error
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+ALTER TABLE gtest_normal_child INHERIT gtest_normal; -- error
+DROP TABLE gtest_normal, gtest_normal_child;
+
+-- test inheritance mismatches between parent and child
+CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) VIRTUAL) INHERITS (gtest1); -- ok, overrides parent
+\d+ gtestx
+INSERT INTO gtestx (a, x) VALUES (11, 22);
+SELECT * FROM gtest1;
+SELECT * FROM gtestx;
+
+CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
+ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
+CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
+CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) VIRTUAL, a int NOT NULL);
+ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
+
+-- test multiple inheritance mismatches
+CREATE TABLE gtesty (x int, b int DEFAULT 55);
+CREATE TABLE gtest1_y () INHERITS (gtest0, gtesty); -- error
+DROP TABLE gtesty;
+
+CREATE TABLE gtesty (x int, b int);
+CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
+DROP TABLE gtesty;
+
+CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) VIRTUAL);
+CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
+CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) VIRTUAL) INHERITS (gtest1, gtesty); -- ok
+\d gtest1_y
+
+-- test correct handling of GENERATED column that's only in child
+CREATE TABLE gtestp (f1 int);
+CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) VIRTUAL) INHERITS(gtestp);
+INSERT INTO gtestc values(42);
+TABLE gtestc;
+UPDATE gtestp SET f1 = f1 * 10;
+TABLE gtestc;
+DROP TABLE gtestp CASCADE;
+
+-- test update XXX
+CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) VIRTUAL);
+INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
+SELECT * FROM gtest3 ORDER BY a;
+UPDATE gtest3 SET a = 22 WHERE a = 2;
+SELECT * FROM gtest3 ORDER BY a;
+
+CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) VIRTUAL);
+INSERT INTO gtest3a (a) VALUES ('a'), ('b'), ('c'), (NULL);
+SELECT * FROM gtest3a ORDER BY a;
+UPDATE gtest3a SET a = 'bb' WHERE a = 'b';
+SELECT * FROM gtest3a 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) VIRTUAL);
+INSERT INTO gtest2 VALUES (1);
+SELECT * FROM gtest2;
+
+-- simple column reference for varlena types
+CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) VIRTUAL);
+INSERT INTO gtest_varlena (a) VALUES('01234567890123456789');
+INSERT INTO gtest_varlena (a) VALUES(NULL);
+SELECT * FROM gtest_varlena ORDER BY a;
+DROP TABLE gtest_varlena;
+
+-- 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)) 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 = 'gtest_tableoid'::regclass) VIRTUAL
+);
+INSERT INTO gtest_tableoid VALUES (1), (2);
+ALTER TABLE gtest_tableoid ADD COLUMN
+ c regclass GENERATED ALWAYS AS (tableoid) VIRTUAL;
+SELECT * FROM gtest_tableoid;
+
+-- drop column behavior
+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) VIRTUAL);
+ALTER TABLE gtest10 DROP COLUMN b; -- fails
+ALTER TABLE gtest10 DROP COLUMN b CASCADE; -- drops c too
+
+\d gtest10
+
+CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+ALTER TABLE gtest10a DROP COLUMN b;
+INSERT INTO gtest10a (a) VALUES (1);
+
+-- 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 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 gtest12s TO regress_user11;
+
+SET ROLE regress_user11;
+SELECT a, b FROM gtest11v; -- not allowed
+SELECT a, c FROM gtest11v; -- allowed
+SELECT gf1(10); -- not allowed
+SELECT a, c FROM gtest12v; -- not allowed; TODO: ought to be allowed
+RESET ROLE;
+
+DROP FUNCTION gf1(int); -- fail
+DROP TABLE gtest11v, gtest12v;
+DROP FUNCTION gf1(int);
+DROP USER regress_user11;
+
+-- check constraints
+CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL CHECK (b < 50));
+INSERT INTO gtest20 (a) VALUES (10); -- ok
+INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
+
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
+
+CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+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) VIRTUAL);
+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 (currently not supported)
+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL 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)) VIRTUAL);
+ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
+--INSERT INTO gtest21b (a) VALUES (1); -- ok
+--INSERT INTO gtest21b (a) VALUES (0); -- violates constraint
+ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
+--INSERT INTO gtest21b (a) VALUES (0); -- ok now
+
+-- index constraints
+CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE);
+--INSERT INTO gtest22a VALUES (2);
+--INSERT INTO gtest22a VALUES (3);
+--INSERT INTO gtest22a VALUES (4);
+CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY KEY (a, b));
+--INSERT INTO gtest22b VALUES (2);
+--INSERT INTO gtest22b VALUES (2);
+
+-- indexes
+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+--CREATE INDEX gtest22c_b_idx ON gtest22c (b);
+--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 = 4;
+--SELECT * FROM gtest22c WHERE b = 4;
+--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;
+
+--ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
+--ANALYZE gtest22c;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
+--SELECT * FROM gtest22c WHERE b = 8;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
+--SELECT * FROM gtest22c WHERE b * 3 = 12;
+--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;
+
+-- 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) VIRTUAL REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
+
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x));
+--\d gtest23b
+
+--INSERT INTO gtest23b VALUES (1); -- ok
+--INSERT INTO gtest23b VALUES (5); -- error
+--ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
+--ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
+
+--DROP TABLE gtest23b;
+--DROP TABLE gtest23a;
+
+CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, 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
+
+-- domains
+CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
+CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL);
+--INSERT INTO gtest24 (a) VALUES (4); -- ok
+--INSERT INTO gtest24 (a) VALUES (6); -- error
+CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
+CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) VIRTUAL);
+--INSERT INTO gtest24r (a) VALUES (4); -- ok
+--INSERT INTO gtest24r (a) VALUES (6); -- error
+
+-- 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) VIRTUAL);
+DROP TYPE gtest_type CASCADE;
+
+-- partitioning cases
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_child PARTITION OF gtest_parent (
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) VIRTUAL
+) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
+CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
+DROP TABLE gtest_parent, gtest_child;
+
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_child PARTITION OF gtest_parent
+ FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- inherits gen expr
+CREATE TABLE gtest_child2 PARTITION OF gtest_parent (
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) VIRTUAL -- overrides gen expr
+) FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
+CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
+ f3 DEFAULT 42 -- error
+) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
+CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
+ f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY -- error
+) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
+CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
+ f3 GENERATED ALWAYS AS (f2 * 2) STORED -- error
+) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
+DROP TABLE gtest_child3;
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint DEFAULT 42);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
+DROP TABLE gtest_child3;
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS IDENTITY);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
+DROP TABLE gtest_child3;
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) VIRTUAL);
+ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
+\d gtest_child
+\d gtest_child2
+\d gtest_child3
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 2);
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-08-15', 3);
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
+-- alter only parent's and one child's generation expression
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
+\d gtest_parent
+\d gtest_child
+\d gtest_child2
+\d gtest_child3
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
+-- alter generation expression of parent and all its children altogether
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
+\d gtest_parent
+\d gtest_child
+\d gtest_child2
+\d gtest_child3
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+-- we leave these tables around for purposes of testing dump/reload/upgrade
+
+-- generated columns in partition key (not allowed)
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3);
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3));
+
+-- 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 * 2) VIRTUAL, ALTER COLUMN b SET EXPRESSION AS (a * 3);
+SELECT * FROM gtest25 ORDER BY a;
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) VIRTUAL; -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) VIRTUAL; -- error
+ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42,
+ ADD COLUMN x int GENERATED ALWAYS AS (c * 4) VIRTUAL;
+ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101;
+ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
+ ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) VIRTUAL;
+SELECT * FROM gtest25 ORDER BY a;
+\d gtest25
+
+-- ALTER TABLE ... ALTER COLUMN
+CREATE TABLE gtest27 (
+ a int,
+ b int,
+ x int GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL
+);
+INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
+ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
+ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
+\d gtest27
+SELECT * FROM gtest27;
+ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0; -- error
+ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error
+-- It's possible to alter the column types this way:
+ALTER TABLE gtest27
+ DROP COLUMN x,
+ ALTER COLUMN a TYPE bigint,
+ ALTER COLUMN b TYPE bigint,
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL;
+\d gtest27
+-- Ideally you could just do this, but not today (and should x change type?):
+ALTER TABLE gtest27
+ ALTER COLUMN a TYPE float8,
+ ALTER COLUMN b TYPE float8; -- error
+\d gtest27
+SELECT * FROM gtest27;
+
+-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
+CREATE TABLE gtest29 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+);
+INSERT INTO gtest29 (a) VALUES (3), (4);
+SELECT * FROM gtest29;
+\d gtest29
+ALTER TABLE gtest29 ALTER COLUMN a SET EXPRESSION AS (a * 3); -- error
+ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION; -- error
+ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION IF EXISTS; -- notice
+
+-- Change the expression
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
+SELECT * FROM gtest29;
+\d gtest29
+
+ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;
+INSERT INTO gtest29 (a) VALUES (5);
+INSERT INTO gtest29 (a, b) VALUES (6, 66);
+SELECT * FROM gtest29;
+\d gtest29
+
+-- check that dependencies between columns have also been removed
+ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
+\d gtest29
+
+-- with inheritance
+CREATE TABLE gtest30 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+);
+CREATE TABLE gtest30_1 () INHERITS (gtest30);
+ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
+\d gtest30
+\d gtest30_1
+DROP TABLE gtest30 CASCADE;
+CREATE TABLE gtest30 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+);
+CREATE TABLE gtest30_1 () INHERITS (gtest30);
+ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
+\d gtest30
+\d gtest30_1
+ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
+
+-- triggers
+CREATE TABLE gtest26 (
+ a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+);
+
+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
+-- TODO
+
+-- 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.b = 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) VIRTUAL
+);
+
+ALTER TABLE gtest28a DROP COLUMN a;
+
+CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
+
+\d gtest28*
+
+
+-- TODO: extra tests to weave into the right places
+
+-- sublinks
+CREATE TABLE t2 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2),
+ d int DEFAULT 22
+);
+INSERT INTO t2 (a) SELECT g FROM generate_series(1, 10) g;
+SELECT a, b, d, (SELECT t2.b) FROM t2;
+DROP TABLE t2;
+
+-- collate
+/*
+CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
+CREATE TABLE t5 (
+ a text collate "C",
+ b text collate "C" GENERATED ALWAYS AS (a collate case_insensitive) ,
+ d int DEFAULT 22
+);
+INSERT INTO t5 (a, d) values ('d1', 28), ('D2', 27), ('D1', 26);
+SELECT * FROM t5 ORDER BY b ASC, d ASC;
+DROP TABLE t5;
+DROP COLLATION case_insensitive;
+*/
diff --git a/src/test/subscription/t/011_generated.pl b/src/test/subscription/t/011_generated.pl
index 8b2e5f4708..faee155daa 100644
--- a/src/test/subscription/t/011_generated.pl
+++ b/src/test/subscription/t/011_generated.pl
@@ -21,11 +21,11 @@ $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) STORED)"
+ "CREATE TABLE tab1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (a * 3) VIRTUAL)"
);
$node_subscriber->safe_psql('postgres',
- "CREATE TABLE tab1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 22) STORED, c int)"
+ "CREATE TABLE tab1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 22) STORED, c int GENERATED ALWAYS AS (a * 33) VIRTUAL, d int)"
);
# data for initial sync
@@ -42,10 +42,10 @@ $node_subscriber->safe_psql('postgres',
# Wait for initial sync of all subscriptions
$node_subscriber->wait_for_subscription_sync;
-my $result = $node_subscriber->safe_psql('postgres', "SELECT a, b FROM tab1");
-is( $result, qq(1|22
-2|44
-3|66), 'generated columns initial sync');
+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
@@ -56,11 +56,11 @@ $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 * FROM tab1");
-is( $result, qq(1|22|
-2|44|
-3|66|
-4|88|
-6|132|), 'generated columns replicated');
+is( $result, qq(1|22|33|
+2|44|66|
+3|66|99|
+4|88|132|
+6|132|198|), 'generated columns replicated');
# try it with a subscriber-side trigger
@@ -69,7 +69,7 @@ $node_subscriber->safe_psql(
CREATE FUNCTION tab1_trigger_func() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
- NEW.c := NEW.a + 10;
+ NEW.d := NEW.a + 10;
RETURN NEW;
END $$;
@@ -88,12 +88,12 @@ $node_publisher->wait_for_catchup('sub1');
$result =
$node_subscriber->safe_psql('postgres', "SELECT * FROM tab1 ORDER BY 1");
-is( $result, qq(1|22|
-2|44|
-3|66|
-4|88|
-6|132|
-8|176|18
-9|198|19), 'generated columns replicated with trigger');
+is( $result, qq(1|22|33|
+2|44|66|
+3|66|99|
+4|88|132|
+6|132|198|
+8|176|264|18
+9|198|297|19), 'generated columns replicated with trigger');
done_testing();
--
2.34.1