v2-0003-Virtual-generated-columns.patch
text/plain
Filename: v2-0003-Virtual-generated-columns.patch
Type: text/plain
Part: 2
Message:
Re: Virtual generated columns
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v2-0003
Subject: Virtual generated columns
| File | + | − |
|---|---|---|
| contrib/pageinspect/expected/page.out | 35 | 0 |
| contrib/pageinspect/sql/page.sql | 17 | 0 |
| contrib/postgres_fdw/expected/postgres_fdw.out | 42 | 39 |
| contrib/postgres_fdw/sql/postgres_fdw.sql | 6 | 3 |
| doc/src/sgml/catalogs.sgml | 2 | 2 |
| doc/src/sgml/ddl.sgml | 15 | 10 |
| doc/src/sgml/ref/alter_table.sgml | 12 | 2 |
| doc/src/sgml/ref/create_foreign_table.sgml | 7 | 4 |
| doc/src/sgml/ref/create_table.sgml | 13 | 9 |
| doc/src/sgml/ref/create_trigger.sgml | 1 | 1 |
| doc/src/sgml/trigger.sgml | 4 | 0 |
| src/backend/access/common/tupdesc.c | 3 | 0 |
| src/backend/commands/analyze.c | 4 | 0 |
| src/backend/commands/indexcmds.c | 28 | 1 |
| src/backend/commands/statscmds.c | 19 | 1 |
| src/backend/commands/tablecmds.c | 86 | 18 |
| src/backend/commands/trigger.c | 47 | 1 |
| src/backend/executor/execExprInterp.c | 4 | 0 |
| src/backend/executor/execMain.c | 4 | 1 |
| src/backend/parser/analyze.c | 8 | 0 |
| src/backend/parser/gram.y | 12 | 3 |
| src/backend/parser/parse_clause.c | 4 | 0 |
| src/backend/parser/parse_merge.c | 1 | 0 |
| src/backend/parser/parse_relation.c | 8 | 1 |
| src/backend/parser/parse_utilcmd.c | 39 | 1 |
| src/backend/replication/pgoutput/pgoutput.c | 2 | 1 |
| src/backend/rewrite/rewriteHandler.c | 150 | 1 |
| src/backend/utils/cache/partcache.c | 3 | 0 |
| src/backend/utils/cache/relcache.c | 2 | 0 |
| src/bin/pg_dump/pg_dump.c | 3 | 0 |
| src/bin/pg_dump/t/002_pg_dump.pl | 4 | 2 |
| src/bin/psql/describe.c | 6 | 0 |
| src/include/access/tupdesc.h | 1 | 0 |
| src/include/catalog/pg_attribute.h | 1 | 0 |
| src/include/nodes/parsenodes.h | 3 | 0 |
| src/include/parser/kwlist.h | 1 | 0 |
| src/include/parser/parse_node.h | 1 | 0 |
| src/include/rewrite/rewriteHandler.h | 2 | 0 |
| src/pl/plperl/expected/plperl_trigger.out | 4 | 3 |
| src/pl/plperl/plperl.c | 3 | 0 |
| src/pl/plperl/sql/plperl_trigger.sql | 2 | 1 |
| src/pl/plpython/expected/plpython_trigger.out | 4 | 3 |
| src/pl/plpython/plpy_typeio.c | 3 | 0 |
| src/pl/plpython/sql/plpython_trigger.sql | 2 | 1 |
| src/pl/tcl/expected/pltcl_trigger.out | 10 | 9 |
| src/pl/tcl/pltcl.c | 3 | 0 |
| src/pl/tcl/sql/pltcl_trigger.sql | 2 | 1 |
| src/test/regress/expected/create_table_like.out | 13 | 10 |
| src/test/regress/expected/generated_stored.out | 24 | 3 |
| src/test/regress/expected/generated_virtual.out | 412 | 423 |
| src/test/regress/parallel_schedule | 3 | 0 |
| src/test/regress/sql/create_table_like.sql | 1 | 1 |
| src/test/regress/sql/generated_stored.sql | 9 | 1 |
| src/test/regress/sql/generated_virtual.sql | 166 | 135 |
| src/test/subscription/t/011_generated.pl | 19 | 19 |
From 71bafa088c96746820876e9934658044d11826c5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Thu, 8 Aug 2024 08:13:48 +0200
Subject: [PATCH v2 3/3] Virtual generated columns
This adds a new variant of generated columns that are computed on read
(like a view, unlike the existing stored generated columns, which are
computed on write, like a materialized view).
Some functionality is currently not supported (but could possibly be
added as incremental features, some easier than others):
- index on virtual column
- expression index using a virtual column
- hence also no unique constraints on virtual columns
- not-null constraints on virtual columns
- (check constraints are supported)
- foreign key constraints on virtual columns
- extended statistics on virtual columns
- ALTER TABLE / SET EXPRESSION
- ALTER TABLE / DROP EXPRESSION
- virtual columns as trigger columns
- virtual column cannot have domain type
TODO:
- analysis of access control
- check FDW/foreign table behavior
Discussion: https://www.postgresql.org/message-id/flat/a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org
---
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/commands/analyze.c | 4 +
src/backend/commands/indexcmds.c | 29 +-
src/backend/commands/statscmds.c | 20 +-
src/backend/commands/tablecmds.c | 104 ++-
src/backend/commands/trigger.c | 48 +-
src/backend/executor/execExprInterp.c | 4 +
src/backend/executor/execMain.c | 5 +-
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 | 40 +-
src/backend/replication/pgoutput/pgoutput.c | 3 +-
src/backend/rewrite/rewriteHandler.c | 151 +++-
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/pg_attribute.h | 1 +
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 +-
.../regress/expected/generated_stored.out | 27 +-
...rated_stored.out => generated_virtual.out} | 835 +++++++++---------
src/test/regress/parallel_schedule | 3 +
src/test/regress/sql/create_table_like.sql | 2 +-
src/test/regress/sql/generated_stored.sql | 10 +-
...rated_stored.sql => generated_virtual.sql} | 301 ++++---
src/test/subscription/t/011_generated.pl | 38 +-
55 files changed, 1280 insertions(+), 711 deletions(-)
copy src/test/regress/expected/{generated_stored.out => generated_virtual.out} (69%)
copy src/test/regress/sql/{generated_stored.sql => generated_virtual.sql} (72%)
diff --git a/contrib/pageinspect/expected/page.out b/contrib/pageinspect/expected/page.out
index 80ddb45a60a..7e0b09e279b 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 5bff568d3b5..186dda1e8d0 100644
--- a/contrib/pageinspect/sql/page.sql
+++ b/contrib/pageinspect/sql/page.sql
@@ -84,6 +84,23 @@ CREATE TEMP TABLE test1 (a int, b int);
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 f3eb055e2c7..926c1fddbe3 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 0734716ad90..058d53364db 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1836,12 +1836,15 @@ CREATE VIEW rw_view AS SELECT * FROM parent_tbl WHERE a < 5 WITH CHECK OPTION;
-- ===================================================================
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 b654fae1b2f..579b1d61660 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1318,8 +1318,8 @@ <title><structname>pg_attribute</structname> Columns</title>
</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 626d35514cc..785ae6aa04f 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -361,7 +361,6 @@ <title>Generated Columns</title>
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 @@ <title>Generated Columns</title>
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 @@ <title>Generated Columns</title>
<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 6a2822adad7..10647332622 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -271,6 +271,11 @@ <title>Description</title>
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>
@@ -283,10 +288,15 @@ <title>Description</title>
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 dc4b9075990..21b5d6a14d0 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -47,7 +47,7 @@
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 @@ <title>Parameters</title>
</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 @@ <title>Parameters</title>
</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 93b3f664f21..ee79867d969 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -65,7 +65,7 @@
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 @@ <title>Parameters</title>
<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 @@ <title>Parameters</title>
</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 @@ <title>Parameters</title>
</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 @@ <title>Multiple Identity Columns</title>
<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 982ab6f3ee4..752fe50860a 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -279,7 +279,7 @@ <title>Parameters</title>
<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 31626536a2e..5953d24ae8f 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -289,6 +289,10 @@ <title>Overview of Trigger Behavior</title>
<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 47379fef220..13840ba533c 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/commands/analyze.c b/src/backend/commands/analyze.c
index c590a2adc35..89d6d2025fa 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -1012,6 +1012,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/indexcmds.c b/src/backend/commands/indexcmds.c
index c5a56c75f69..ec4e8bc3902 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 1db3ef69d22..744b3508c24 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 1f94f4fdbbc..f0049d742f3 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2940,6 +2940,12 @@ 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)));
+
/*
* Override the parent's default value for this column
* (coldef->cooked_default) with the partition's local
@@ -3211,6 +3217,12 @@ 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)));
+
/*
* If new def has a default, override previous default
*/
@@ -6044,7 +6056,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 */
@@ -7736,6 +7748,7 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
const char *colName, LOCKMODE lockmode)
{
HeapTuple tuple;
+ Form_pg_attribute attTup;
AttrNumber attnum;
Relation attr_rel;
ObjectAddress address;
@@ -7752,8 +7765,8 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("column \"%s\" of relation \"%s\" does not exist",
colName, RelationGetRelationName(rel))));
-
- attnum = ((Form_pg_attribute) GETSTRUCT(tuple))->attnum;
+ attTup = (Form_pg_attribute) GETSTRUCT(tuple);
+ attnum = attTup->attnum;
/* Prevent them from altering a system attribute */
if (attnum <= 0)
@@ -7762,12 +7775,20 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
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
*/
- if (!((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull)
+ if (!attTup->attnotnull)
{
- ((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull = true;
+ attTup->attnotnull = true;
CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
@@ -7778,7 +7799,7 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
* already found that we must verify some other not-null constraint.
*/
if (!tab->verify_new_notnull &&
- !NotNullImpliedByRelConstraints(rel, (Form_pg_attribute) GETSTRUCT(tuple)))
+ !NotNullImpliedByRelConstraints(rel, attTup))
{
/* Tell Phase 3 it needs to test the constraint */
tab->verify_new_notnull = true;
@@ -8360,7 +8381,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",
@@ -8517,17 +8549,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);
@@ -9749,6 +9794,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")));
}
/*
@@ -11713,7 +11771,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);
@@ -12754,8 +12812,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
@@ -17912,8 +17974,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,
@@ -17995,9 +18060,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 170360edda8..021328746a1 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/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index ea47c4d6f9c..0ffcff3afe7 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -2009,6 +2009,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 4d7c92d63c1..d82170785bf 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1748,6 +1748,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);
}
@@ -2295,7 +2296,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 e901203424d..e3936a0b8ea 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 a043fd4c669..16d847fa3fe 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -641,7 +641,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
@@ -791,7 +791,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
@@ -4025,7 +4025,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);
@@ -4033,6 +4033,7 @@ ColConstraintElem:
n->generated_when = $2;
n->raw_expr = $5;
n->cooked_expr = NULL;
+ n->generated_kind = $7;
n->location = @1;
/*
@@ -4079,6 +4080,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
@@ -17882,6 +17889,7 @@ unreserved_keyword:
| VERSION_P
| VIEW
| VIEWS
+ | VIRTUAL
| VOLATILE
| WHITESPACE_P
| WITHIN
@@ -18538,6 +18546,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 8118036495b..48524ac3fc2 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 87df79027d7..350e9e18885 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 2f64eaf0e37..b864749f09c 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 d5c2b2ff0b0..b445387b853 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -754,10 +754,36 @@ 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;
+
+ /*
+ * TODO: 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.
+ *
+ * XXX If column->typeName is not set, then this column
+ * definition is probably a partition definition and will
+ * presumably get its pre-vetted type from elsewhere. If that
+ * doesn't hold, maybe this check needs to be moved elsewhere.
+ */
+ if (column->generated == ATTRIBUTE_GENERATED_VIRTUAL && column->typeName)
+ {
+ Type ctype;
+
+ ctype = typenameType(cxt->pstate, column->typeName, NULL);
+ if (((Form_pg_type) GETSTRUCT(ctype))->typtype == TYPTYPE_DOMAIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("virtual generated column \"%s\" cannot have a domain type",
+ column->colname),
+ parser_errposition(cxt->pstate,
+ column->location)));
+ ReleaseSysCache(ctype);
+ }
break;
case CONSTR_CHECK:
@@ -842,6 +868,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 abef4eaf68b..52dc9b5fcac 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 c223a2c50af..7662810e9e2 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -90,6 +90,8 @@ 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;
+static Query *expand_generated_columns_in_query(Query *query, struct expand_generated_context *context);
/*
@@ -974,7 +976,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 +4368,149 @@ 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);
+
+ 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));
+ 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);
+}
+
+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;
+}
+
+/*
+ * Expand virtual generated columns in a Query. We do some optimizations here
+ * to avoid digging through the whole Query unless necessary.
+ */
+static Query *
+expand_generated_columns_in_query(Query *query, struct expand_generated_context *context)
+{
+ context->rtables = lappend(context->rtables, query->rtable);
+ if (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;
+}
+
+
/*
* QueryRewrite -
* Primary entry point to the query rewriter.
@@ -4409,9 +4555,12 @@ QueryRewrite(Query *parsetree)
foreach(l, querylist)
{
Query *query = (Query *) lfirst(l);
+ struct expand_generated_context context = {0};
query = fireRIRrules(query, NIL);
+ query = expand_generated_columns_in_query(query, &context);
+
query->queryId = input_query_id;
results = lappend(results, query);
diff --git a/src/backend/utils/cache/partcache.c b/src/backend/utils/cache/partcache.c
index beec6cddbc4..1dee7c1e899 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 66ed24e4012..4f756acd39d 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 b6e01d3d292..4b2aac8cd33 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 5bcc2244d58..c3d6022015c 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 @@
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 7c9a1f234c6..4309d3e757d 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 8930a28d660..6ef40249583 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/pg_attribute.h b/src/include/catalog/pg_attribute.h
index 1c62b8bfcb5..8f158dc6083 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/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 85a62b538e5..7fabdc0cd97 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);
@@ -2745,6 +2747,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 f7fe834cf45..96167877b75 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -492,6 +492,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 5b781d87a9d..1b02128548b 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 1b65cda71cf..a646a20675a 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 d4879e2f03b..42c52ecbba8 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 d68ad7be345..9611aa82352 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 4adddeb80ac..2798a02fa12 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 4cb90cb5204..64eab2fa3f4 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 db14c5f8dae..51e1d610259 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 f6c2ef8d6a0..440549c0785 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 129abd5ba67..5298e50a5ec 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 21b2b045933..cb715ad08b6 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 2a244de83bc..0ed00f49526 100644
--- a/src/pl/tcl/sql/pltcl_trigger.sql
+++ b/src/pl/tcl/sql/pltcl_trigger.sql
@@ -73,7 +73,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 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 6bfc6d040ff..97157dc635b 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_stored.out b/src/test/regress/expected/generated_stored.out
index dd74a1a9f0b..be53a9f8837 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -1,5 +1,5 @@
-- 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)
@@ -217,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;
@@ -1091,9 +1112,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;
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_virtual.out
similarity index 69%
copy from src/test/regress/expected/generated_stored.out
copy to src/test/regress/expected/generated_virtual.out
index dd74a1a9f0b..3e90132edad 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1,15 +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_schema = 'generated_stored_tests' ORDER BY 1, 2;
+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 |
@@ -18,89 +18,89 @@ 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 WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
+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_stored_tests.gtest1"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Table "generated_virtual_tests.gtest1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
a | integer | | not null |
- b | integer | | | generated always as (a * 2) stored
+ 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) STORED GENERATED ALWAYS AS (a * 3) STORED);
+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: ...ARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED GENERATED ...
+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) STORED);
+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) STO...
+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) STORED, c int GENERATED ALWAYS AS (b * 3) STORED);
+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: ...AYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (b * 3) STO...
+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)) STORED);
+ 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)) STOR...
+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) STORED);
+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) STO...
+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()) STORED);
+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') STORED);
+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) STORED);
+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) STORED);
+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) STORED);
+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)) STORED);
+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)) ST...
+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)) STORED);
+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)) STORED);
+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)) STORED);
+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) STORED);
+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...
^
@@ -153,16 +153,10 @@ SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;
2 | 4
(1 row)
--- test that overflow error happens on write
+-- test that overflow error happens on read
INSERT INTO gtest1 VALUES (2000000000);
-ERROR: integer out of range
SELECT * FROM gtest1;
- a | b
----+---
- 2 | 4
- 1 | 2
-(2 rows)
-
+ERROR: integer out of range
DELETE FROM gtest1 WHERE a = 2000000000;
-- test with joins
CREATE TABLE gtestx (x int, y int);
@@ -203,8 +197,8 @@ CREATE TABLE gtestm (
id int PRIMARY KEY,
f1 int,
f2 int,
- f3 int GENERATED ALWAYS AS (f1 * 2) STORED,
- f4 int GENERATED ALWAYS AS (f2 * 2) STORED
+ 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
@@ -217,6 +211,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) 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;
@@ -273,11 +288,11 @@ SELECT * FROM gtest1_1;
(0 rows)
\d gtest1_1
- Table "generated_stored_tests.gtest1_1"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Table "generated_virtual_tests.gtest1_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
a | integer | | not null |
- b | integer | | | generated always as (a * 2) stored
+ b | integer | | | generated always as (a * 2)
Inherits: gtest1
INSERT INTO gtest1_1 VALUES (4);
@@ -296,12 +311,12 @@ 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) STORED) INHERITS (gtest_normal); -- error
+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) STORED);
+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;
@@ -312,23 +327,41 @@ 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); -- ok, overrides parent
+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
+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_stored_tests.gtestx"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+-------------------------------------+---------+--------------+-------------
- a | integer | | not null | | plain | |
- b | integer | | | generated always as (a * 22) stored | plain | |
- x | integer | | | | plain | |
+ 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) STORED);
+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) STORED, a int NOT NULL);
+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);
@@ -341,28 +374,28 @@ 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) STORED);
+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) STORED) INHERITS (gtest1, gtesty); -- ok
+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_stored_tests.gtest1_y"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Table "generated_virtual_tests.gtest1_y"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
a | integer | | not null |
- b | integer | | | generated always as (x + 1) stored
+ 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) STORED) INHERITS(gtestp);
+CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) VIRTUAL) INHERITS(gtestp);
INSERT INTO gtestc values(42);
TABLE gtestc;
f1 | f2
@@ -379,8 +412,8 @@ TABLE gtestc;
DROP TABLE gtestp CASCADE;
NOTICE: drop cascades to table gtestc
--- test stored update
-CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
+-- 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
@@ -401,7 +434,7 @@ SELECT * FROM gtest3 ORDER BY a;
|
(4 rows)
-CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) STORED);
+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
@@ -466,7 +499,7 @@ SELECT * FROM gtest3 ORDER BY a;
(4 rows)
-- null values
-CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED);
+CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) VIRTUAL);
INSERT INTO gtest2 VALUES (1);
SELECT * FROM gtest2;
a | b
@@ -475,7 +508,7 @@ SELECT * FROM gtest2;
(1 row)
-- simple column reference for varlena types
-CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) STORED);
+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;
@@ -490,7 +523,7 @@ DROP TABLE gtest_varlena;
CREATE TYPE double_int as (a int, b int);
CREATE TABLE gtest4 (
a int,
- b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) STORED
+ b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) VIRTUAL
);
INSERT INTO gtest4 VALUES (1), (6);
SELECT * FROM gtest4;
@@ -505,11 +538,11 @@ 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) STORED
+ 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) STORED;
+ c regclass GENERATED ALWAYS AS (tableoid) VIRTUAL;
SELECT * FROM gtest_tableoid;
a | b | c
---+---+----------------
@@ -518,7 +551,7 @@ SELECT * FROM gtest_tableoid;
(2 rows)
-- drop column behavior
-CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);
+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
@@ -526,30 +559,31 @@ 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_stored_tests.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) STORED);
+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 gtest11s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);
-INSERT INTO gtest11s VALUES (1, 10), (2, 20);
-GRANT SELECT (a, c) ON gtest11s TO regress_user11;
+CREATE 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 gtest12s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);
-INSERT INTO gtest12s VALUES (1, 10), (2, 20);
+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 gtest11s; -- not allowed
-ERROR: permission denied for table gtest11s
-SELECT a, c FROM gtest11s; -- allowed
+SELECT a, b FROM gtest11v; -- not allowed
+ERROR: permission denied for table gtest11v
+SELECT a, c FROM gtest11v; -- allowed
a | c
---+----
1 | 20
@@ -558,231 +592,139 @@ SELECT a, c FROM gtest11s; -- allowed
SELECT gf1(10); -- not allowed
ERROR: permission denied for function gf1
-SELECT a, c FROM gtest12s; -- allowed
- a | c
----+----
- 1 | 30
- 2 | 60
-(2 rows)
-
+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 gtest12s depends on function gf1(integer)
+DETAIL: column c of table gtest12v depends on function gf1(integer)
HINT: Use DROP ... CASCADE to drop the dependent objects too.
-DROP TABLE gtest11s, gtest12s;
+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) STORED CHECK (b < 50));
+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, 60).
+DETAIL: Failing row contains (30, virtual).
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint
-ERROR: check constraint "gtest20_b_check" of relation "gtest20" is violated by some row
+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
-CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+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) STORED);
+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
-CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
-INSERT INTO gtest21a (a) VALUES (1); -- ok
-INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
-ERROR: null value in column "b" of relation "gtest21a" violates not-null constraint
-DETAIL: Failing row contains (0, null).
-CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+-- 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;
-INSERT INTO gtest21b (a) VALUES (1); -- ok
-INSERT INTO gtest21b (a) VALUES (0); -- violates constraint
-ERROR: null value in column "b" of relation "gtest21b" violates not-null constraint
-DETAIL: Failing row contains (0, 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
+--INSERT INTO gtest21b (a) VALUES (0); -- ok now
-- index constraints
-CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED UNIQUE);
-INSERT INTO gtest22a VALUES (2);
-INSERT INTO gtest22a VALUES (3);
-ERROR: duplicate key value violates unique constraint "gtest22a_b_key"
-DETAIL: Key (b)=(1) already exists.
-INSERT INTO gtest22a VALUES (4);
-CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) STORED, PRIMARY KEY (a, b));
-INSERT INTO gtest22b VALUES (2);
-INSERT INTO gtest22b VALUES (2);
-ERROR: duplicate key value violates unique constraint "gtest22b_pkey"
-DETAIL: Key (a, b)=(2, 1) already exists.
+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) STORED);
-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 "generated_stored_tests.gtest22c"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
- a | integer | | |
- b | integer | | | generated always as (a * 2) stored
-Indexes:
- "gtest22c_b_idx" btree (b)
- "gtest22c_expr_idx" btree ((b * 3))
- "gtest22c_pred_idx" btree (a) WHERE b > 0
-
-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;
- QUERY PLAN
----------------------------------------------
- Index Scan using gtest22c_b_idx on gtest22c
- Index Cond: (b = 4)
-(2 rows)
-
-SELECT * FROM gtest22c WHERE b = 4;
- a | b
----+---
- 2 | 4
-(1 row)
-
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
- QUERY PLAN
-------------------------------------------------
- Index Scan using gtest22c_expr_idx on gtest22c
- Index Cond: ((b * 3) = 6)
-(2 rows)
-
-SELECT * FROM gtest22c WHERE b * 3 = 6;
- a | b
----+---
- 1 | 2
-(1 row)
-
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
- QUERY PLAN
-------------------------------------------------
- Index Scan using gtest22c_pred_idx on gtest22c
- Index Cond: (a = 1)
-(2 rows)
-
-SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
- a | b
----+---
- 1 | 2
-(1 row)
-
-ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
-ANALYZE gtest22c;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
- QUERY PLAN
----------------------------------------------
- Index Scan using gtest22c_b_idx on gtest22c
- Index Cond: (b = 8)
-(2 rows)
-
-SELECT * FROM gtest22c WHERE b = 8;
- a | b
----+---
- 2 | 8
-(1 row)
-
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
- QUERY PLAN
-------------------------------------------------
- Index Scan using gtest22c_expr_idx on gtest22c
- Index Cond: ((b * 3) = 12)
-(2 rows)
-
-SELECT * FROM gtest22c WHERE b * 3 = 12;
- a | b
----+---
- 1 | 4
-(1 row)
-
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
- QUERY PLAN
-------------------------------------------------
- Index Scan using gtest22c_pred_idx on gtest22c
- Index Cond: (a = 1)
-(2 rows)
-
-SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
- a | b
----+---
- 1 | 4
-(1 row)
-
-RESET enable_seqscan;
-RESET enable_bitmapscan;
+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) STORED REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
+--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) STORED REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
+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) STORED REFERENCES gtest23a (x));
-\d gtest23b
- Table "generated_stored_tests.gtest23b"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
- a | integer | | not null |
- b | integer | | | generated always as (a * 2) stored
-Indexes:
- "gtest23b_pkey" PRIMARY KEY, btree (a)
-Foreign-key constraints:
- "gtest23b_b_fkey" FOREIGN KEY (b) REFERENCES gtest23a(x)
-
-INSERT INTO gtest23b VALUES (1); -- ok
-INSERT INTO gtest23b VALUES (5); -- error
-ERROR: insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
-DETAIL: Key (b)=(10) is not present in table "gtest23a".
-ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
-ERROR: insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
-DETAIL: Key (b)=(5) is not present in table "gtest23a".
-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) STORED, PRIMARY KEY (y));
-INSERT INTO gtest23p VALUES (1), (2), (3);
+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));
-INSERT INTO gtest23q VALUES (1, 2); -- ok
-INSERT INTO gtest23q VALUES (2, 5); -- error
-ERROR: insert or update on table "gtest23q" violates foreign key constraint "gtest23q_b_fkey"
-DETAIL: Key (b)=(5) is not present in table "gtest23p".
+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) STORED);
-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 TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL);
+ERROR: virtual generated column "b" cannot have a domain type
+LINE 1: CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENE...
+ ^
+--INSERT INTO gtest24 (a) VALUES (4); -- ok
+--INSERT INTO gtest24 (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) STORED);
+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) STORED
+ 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) STORED);
+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) STORED) PARTITION BY RANGE (f1);
+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) STORED -- overrides gen expr
+ 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
@@ -792,6 +734,10 @@ 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
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
@@ -805,33 +751,33 @@ ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09
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) STORED);
+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_stored_tests.gtest_child"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------------
+ Table "generated_virtual_tests.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 2) stored
+ 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_stored_tests.gtest_child2"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+--------------------------------------
+ Table "generated_virtual_tests.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 22) stored
+ 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_stored_tests.gtest_child3"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+--------------------------------------
+ Table "generated_virtual_tests.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 33) stored
+ 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);
@@ -842,7 +788,7 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
--------------+------------+----+----
gtest_child | 07-15-2016 | 1 | 2
gtest_child | 07-15-2016 | 2 | 4
- gtest_child2 | 08-15-2016 | 3 | 66
+ gtest_child2 | 08-15-2016 | 3 | 6
(3 rows)
UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
@@ -850,95 +796,101 @@ 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 | 66
- gtest_child3 | 09-13-2016 | 1 | 33
+ 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_stored_tests.gtest_parent"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------------
+ Partitioned table "generated_virtual_tests.gtest_parent"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 4) stored
+ 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_stored_tests.gtest_child"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+--------------------------------------
+ Table "generated_virtual_tests.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 10) stored
+ 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_stored_tests.gtest_child2"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+--------------------------------------
+ Table "generated_virtual_tests.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 22) stored
+ 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_stored_tests.gtest_child3"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+--------------------------------------
+ Table "generated_virtual_tests.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 33) stored
+ 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 | 20
- gtest_child2 | 08-15-2016 | 3 | 66
- gtest_child3 | 09-13-2016 | 1 | 33
+ 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_stored_tests.gtest_parent"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------------
+ 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) stored
+ 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_stored_tests.gtest_child"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------------
+ Table "generated_virtual_tests.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 2) stored
+ 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_stored_tests.gtest_child2"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------------
+ Table "generated_virtual_tests.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 2) stored
+ 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_stored_tests.gtest_child3"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------------
+ Table "generated_virtual_tests.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 2) stored
+ 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;
@@ -951,54 +903,54 @@ 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) STORED) 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);
ERROR: cannot use generated column in partition key
-LINE 1: ...ENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
+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) STORED) PARTITION BY RANGE ((f3 * 3));
+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: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
+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) STORED, ALTER COLUMN b SET EXPRESSION AS (a * 3);
+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 | b
----+----
- 3 | 9
- 4 | 12
+ a
+---
+ 3
+ 4
(2 rows)
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) STORED; -- error
-ERROR: cannot use generated column "b" in column generation expression
-DETAIL: A generated column cannot reference another generated column.
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error
+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) STORED;
+ 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) STORED;
+ ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) VIRTUAL;
SELECT * FROM gtest25 ORDER BY a;
- a | b | c | x | d | y
----+----+----+-----+-----+-----
- 3 | 9 | 42 | 168 | 101 | 404
- 4 | 12 | 42 | 168 | 101 | 404
+ a | c | x | d | y
+---+----+-----+-----+-----
+ 3 | 42 | 168 | 101 | 404
+ 4 | 42 | 168 | 101 | 404
(2 rows)
\d gtest25
- Table "generated_stored_tests.gtest25"
- Column | Type | Collation | Nullable | Default
---------+------------------+-----------+----------+------------------------------------------------------
+ Table "generated_virtual_tests.gtest25"
+ Column | Type | Collation | Nullable | Default
+--------+------------------+-----------+----------+-----------------------------------------------
a | integer | | not null |
- b | integer | | | generated always as (a * 3) stored
c | integer | | | 42
- x | integer | | | generated always as (c * 4) stored
+ x | integer | | | generated always as (c * 4)
d | double precision | | | 101
- y | double precision | | | generated always as (d * 4::double precision) stored
+ y | double precision | | | generated always as (d * 4::double precision)
Indexes:
"gtest25_pkey" PRIMARY KEY, btree (a)
@@ -1006,7 +958,7 @@ Indexes:
CREATE TABLE gtest27 (
a int,
b int,
- x int GENERATED ALWAYS AS ((a + b) * 2) STORED
+ 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
@@ -1014,12 +966,12 @@ 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_stored_tests.gtest27"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+--------------------------------------------
+ Table "generated_virtual_tests.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | |
b | integer | | |
- x | numeric | | | generated always as (((a + b) * 2)) stored
+ x | numeric | | | generated always as (((a + b) * 2))
SELECT * FROM gtest27;
a | b | x
@@ -1032,20 +984,19 @@ ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0; -- error
ERROR: generation expression for column "x" cannot be cast automatically to type boolean
ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error
ERROR: column "x" of relation "gtest27" is a generated column
-HINT: Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead.
-- 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) STORED;
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL;
\d gtest27
- Table "generated_stored_tests.gtest27"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+------------------------------------------
+ Table "generated_virtual_tests.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-----------------------------------
a | bigint | | |
b | bigint | | |
- x | bigint | | | generated always as ((a + b) * 2) stored
+ 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
@@ -1054,12 +1005,12 @@ 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 "generated_stored_tests.gtest27"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+------------------------------------------
+ Table "generated_virtual_tests.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-----------------------------------
a | bigint | | |
b | bigint | | |
- x | bigint | | | generated always as ((a + b) * 2) stored
+ x | bigint | | | generated always as ((a + b) * 2)
SELECT * FROM gtest27;
a | b | x
@@ -1071,7 +1022,7 @@ SELECT * FROM gtest27;
-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
CREATE TABLE gtest29 (
a int,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
);
INSERT INTO gtest29 (a) VALUES (3), (4);
SELECT * FROM gtest29;
@@ -1082,107 +1033,118 @@ SELECT * FROM gtest29;
(2 rows)
\d gtest29
- Table "generated_stored_tests.gtest29"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Table "generated_virtual_tests.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
a | integer | | |
- b | integer | | | generated always as (a * 2) stored
+ 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 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);
+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 | 9
- 4 | 12
+ a | b
+---+---
+ 3 | 6
+ 4 | 8
(2 rows)
\d gtest29
- Table "generated_stored_tests.gtest29"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Table "generated_virtual_tests.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
a | integer | | |
- b | integer | | | generated always as (a * 3) stored
+ 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 | 9
- 4 | 12
- 5 |
- 6 | 66
-(4 rows)
+ 3 | 6
+ 4 | 8
+ 5 | 10
+(3 rows)
\d gtest29
- Table "generated_stored_tests.gtest29"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
+ Table "generated_virtual_tests.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
a | integer | | |
- b | 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_stored_tests.gtest29"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
- b | integer | | |
+ 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) STORED
+ 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_stored_tests.gtest30"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
+ Table "generated_virtual_tests.gtest30"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
a | integer | | |
- b | integer | | |
+ b | integer | | | generated always as (a * 2)
Number of child tables: 1 (Use \d+ to list them.)
\d gtest30_1
- Table "generated_stored_tests.gtest30_1"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
+ Table "generated_virtual_tests.gtest30_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
a | integer | | |
- b | 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) STORED
+ 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_stored_tests.gtest30"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Table "generated_virtual_tests.gtest30"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
a | integer | | |
- b | integer | | | generated always as (a * 2) stored
+ b | integer | | | generated always as (a * 2)
Number of child tables: 1 (Use \d+ to list them.)
\d gtest30_1
- Table "generated_stored_tests.gtest30_1"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Table "generated_virtual_tests.gtest30_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
a | integer | | |
- b | integer | | | generated always as (a * 2) stored
+ b | integer | | | generated always as (a * 2)
Inherits: gtest30
ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
@@ -1190,7 +1152,7 @@ ERROR: cannot drop generation expression from inherited column
-- triggers
CREATE TABLE gtest26 (
a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
);
CREATE FUNCTION gtest_trigger_func() RETURNS trigger
LANGUAGE plpgsql
@@ -1243,7 +1205,7 @@ CREATE TRIGGER gtest4 AFTER INSERT OR UPDATE ON gtest26
EXECUTE PROCEDURE gtest_trigger_func();
INSERT INTO gtest26 (a) VALUES (-2), (0), (3);
INFO: gtest2: BEFORE: new = (-2,)
-INFO: gtest4: AFTER: new = (-2,-4)
+INFO: gtest4: AFTER: new = (-2,)
SELECT * FROM gtest26 ORDER BY a;
a | b
----+----
@@ -1253,12 +1215,12 @@ SELECT * FROM gtest26 ORDER BY a;
(3 rows)
UPDATE gtest26 SET a = a * -2;
-INFO: gtest1: BEFORE: old = (-2,-4)
+INFO: gtest1: BEFORE: old = (-2,)
INFO: gtest1: BEFORE: new = (4,)
-INFO: gtest3: AFTER: old = (-2,-4)
-INFO: gtest3: AFTER: new = (4,8)
-INFO: gtest4: AFTER: old = (3,6)
-INFO: gtest4: AFTER: new = (-6,-12)
+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
----+-----
@@ -1268,8 +1230,8 @@ SELECT * FROM gtest26 ORDER BY a;
(3 rows)
DELETE FROM gtest26 WHERE a = -6;
-INFO: gtest1: BEFORE: old = (-6,-12)
-INFO: gtest3: AFTER: old = (-6,-12)
+INFO: gtest1: BEFORE: old = (-6,)
+INFO: gtest3: AFTER: old = (-6,)
SELECT * FROM gtest26 ORDER BY a;
a | b
---+---
@@ -1280,6 +1242,8 @@ 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
@@ -1293,9 +1257,11 @@ $$;
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;
-NOTICE: OK
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
@@ -1319,14 +1285,13 @@ CREATE TRIGGER gtest12_03 BEFORE UPDATE ON gtest26
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,2)
+INFO: gtest12_01: BEFORE: old = (1,)
INFO: gtest12_01: BEFORE: new = (11,)
-INFO: gtest12_03: BEFORE: old = (1,2)
-INFO: gtest12_03: BEFORE: new = (10,)
+ERROR: trigger modified virtual generated column value
SELECT * FROM gtest26 ORDER BY a;
- a | b
-----+----
- 10 | 20
+ a | b
+---+---
+ 1 | 2
(1 row)
-- LIKE INCLUDING GENERATED and dropped column handling
@@ -1334,22 +1299,46 @@ CREATE TABLE gtest28a (
a int,
b int,
c int,
- x int GENERATED ALWAYS AS (b * 2) STORED
+ 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_stored_tests.gtest28a"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Table "generated_virtual_tests.gtest28a"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
b | integer | | |
c | integer | | |
- x | integer | | | generated always as (b * 2) stored
+ x | integer | | | generated always as (b * 2)
- Table "generated_stored_tests.gtest28b"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Table "generated_virtual_tests.gtest28b"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
b | integer | | |
c | integer | | |
- x | integer | | | generated always as (b * 2) stored
+ 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;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index b0804691f5d..31e5d151051 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -68,6 +68,9 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
# ----------
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 04008a027b8..ddf893f7ec3 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -51,7 +51,7 @@ CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY);
SELECT * FROM test_like_id_3; -- identity was copied and applied
DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
-CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2) 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_stored.sql b/src/test/regress/sql/generated_stored.sql
index c18e0e1f655..46fef4aa2f4 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -1,5 +1,5 @@
-- 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;
@@ -103,6 +103,14 @@ CREATE TABLE gtestm (
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;
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_virtual.sql
similarity index 72%
copy from src/test/regress/sql/generated_stored.sql
copy to src/test/regress/sql/generated_virtual.sql
index c18e0e1f655..574a176e399 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -1,55 +1,55 @@
-- 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 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) STORED);
-CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+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_stored_tests' 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_virtual_tests' ORDER BY 1, 2;
-SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
+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) STORED GENERATED ALWAYS AS (a * 3) STORED);
+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) STORED);
-CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (b * 3) STORED);
+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)) STORED);
+ 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) STORED);
+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()) STORED);
+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') STORED);
+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) STORED);
-CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) STORED);
+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) STORED);
+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)) STORED);
-CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) STORED);
-CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) STORED);
-CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) STORED);
+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) STORED);
+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
@@ -70,7 +70,7 @@ CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (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 write
+-- test that overflow error happens on read
INSERT INTO gtest1 VALUES (2000000000);
SELECT * FROM gtest1;
DELETE FROM gtest1 WHERE a = 2000000000;
@@ -93,8 +93,8 @@ CREATE TABLE gtestm (
id int PRIMARY KEY,
f1 int,
f2 int,
- f3 int GENERATED ALWAYS AS (f1 * 2) STORED,
- f4 int GENERATED ALWAYS AS (f2 * 2) STORED
+ 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
@@ -103,6 +103,14 @@ CREATE TABLE gtestm (
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;
@@ -134,22 +142,26 @@ CREATE TABLE gtest1_1 () INHERITS (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) STORED) INHERITS (gtest_normal); -- error
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+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); -- ok, overrides parent
+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) STORED);
+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) STORED, a int NOT NULL);
+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
@@ -161,28 +173,28 @@ 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) STORED);
+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) STORED) INHERITS (gtest1, gtesty); -- ok
+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) STORED) INHERITS(gtestp);
+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 stored update
-CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
+-- 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) STORED);
+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';
@@ -222,12 +234,12 @@ CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) STORED)
SELECT * FROM gtest3 ORDER BY a;
-- null values
-CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED);
+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) STORED);
+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;
@@ -237,7 +249,7 @@ CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) STORED)
CREATE TYPE double_int as (a int, b int);
CREATE TABLE gtest4 (
a int,
- b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) STORED
+ b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) VIRTUAL
);
INSERT INTO gtest4 VALUES (1), (6);
SELECT * FROM gtest4;
@@ -248,168 +260,168 @@ CREATE TABLE gtest4 (
-- using tableoid is allowed
CREATE TABLE gtest_tableoid (
a int PRIMARY KEY,
- b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) STORED
+ 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) STORED;
+ 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) STORED);
+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) STORED);
+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 gtest11s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);
-INSERT INTO gtest11s VALUES (1, 10), (2, 20);
-GRANT SELECT (a, c) ON gtest11s TO regress_user11;
+CREATE 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 gtest12s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);
-INSERT INTO gtest12s VALUES (1, 10), (2, 20);
+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 gtest11s; -- not allowed
-SELECT a, c FROM gtest11s; -- allowed
+SELECT a, b FROM gtest11v; -- not allowed
+SELECT a, c FROM gtest11v; -- allowed
SELECT gf1(10); -- not allowed
-SELECT a, c FROM gtest12s; -- allowed
+SELECT a, c FROM gtest12v; -- not allowed; TODO: ought to be allowed
RESET ROLE;
DROP FUNCTION gf1(int); -- fail
-DROP TABLE gtest11s, gtest12s;
+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) STORED CHECK (b < 50));
+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) STORED);
+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) STORED);
+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
-CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
-INSERT INTO gtest21a (a) VALUES (1); -- ok
-INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
+-- 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)) STORED);
+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
+--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
+--INSERT INTO gtest21b (a) VALUES (0); -- ok now
-- index constraints
-CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED 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) STORED, PRIMARY KEY (a, b));
-INSERT INTO gtest22b VALUES (2);
-INSERT INTO gtest22b VALUES (2);
+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) STORED);
-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;
+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);
+--INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
-CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
-CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
+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) STORED REFERENCES gtest23a (x));
-\d gtest23b
+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
+--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;
+--DROP TABLE gtest23b;
+--DROP TABLE gtest23a;
-CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) STORED, PRIMARY KEY (y));
-INSERT INTO gtest23p VALUES (1), (2), (3);
+CREATE TABLE 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
+--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) STORED);
-INSERT INTO gtest24 (a) VALUES (4); -- ok
-INSERT INTO gtest24 (a) VALUES (6); -- error
+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
-- 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);
+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) STORED
+ 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) STORED);
+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) STORED) PARTITION BY RANGE (f1);
+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) STORED -- overrides gen expr
+ 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
@@ -417,6 +429,9 @@ CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
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;
@@ -426,7 +441,7 @@ CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint DEFAULT 42);
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) STORED);
+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
@@ -457,21 +472,21 @@ CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWA
-- 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) STORED) PARTITION BY RANGE (f3);
-CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
+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) STORED, ALTER COLUMN b SET EXPRESSION AS (a * 3);
+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) STORED; -- error
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error
+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) STORED;
+ 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) STORED;
+ ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) VIRTUAL;
SELECT * FROM gtest25 ORDER BY a;
\d gtest25
@@ -479,7 +494,7 @@ CREATE TABLE gtest25 (a int PRIMARY KEY);
CREATE TABLE gtest27 (
a int,
b int,
- x int GENERATED ALWAYS AS ((a + b) * 2) STORED
+ 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
@@ -493,7 +508,7 @@ CREATE 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) STORED;
+ 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
@@ -505,7 +520,7 @@ CREATE TABLE gtest27 (
-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
CREATE TABLE gtest29 (
a int,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
);
INSERT INTO gtest29 (a) VALUES (3), (4);
SELECT * FROM gtest29;
@@ -532,7 +547,7 @@ CREATE TABLE gtest29 (
-- with inheritance
CREATE TABLE gtest30 (
a int,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
@@ -541,7 +556,7 @@ CREATE TABLE gtest30_1 () INHERITS (gtest30);
DROP TABLE gtest30 CASCADE;
CREATE TABLE gtest30 (
a int,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
@@ -552,7 +567,7 @@ CREATE TABLE gtest30_1 () INHERITS (gtest30);
-- triggers
CREATE TABLE gtest26 (
a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) VIRTUAL
);
CREATE FUNCTION gtest_trigger_func() RETURNS trigger
@@ -614,6 +629,9 @@ CREATE TRIGGER gtest4 AFTER INSERT OR UPDATE 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
@@ -667,7 +685,7 @@ CREATE TABLE gtest28a (
a int,
b int,
c int,
- x int GENERATED ALWAYS AS (b * 2) STORED
+ x int GENERATED ALWAYS AS (b * 2) VIRTUAL
);
ALTER TABLE gtest28a DROP COLUMN a;
@@ -675,3 +693,16 @@ CREATE TABLE gtest28a (
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;
diff --git a/src/test/subscription/t/011_generated.pl b/src/test/subscription/t/011_generated.pl
index 8b2e5f4708e..faee155daa7 100644
--- a/src/test/subscription/t/011_generated.pl
+++ b/src/test/subscription/t/011_generated.pl
@@ -21,11 +21,11 @@
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 @@
# 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->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 @@
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 @@ BEGIN
$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.46.0