From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Sergei Kornilov <sk@zsrv.org>
To: Erik Rijkers <er@xs4all.nl>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: Jaime Casanova <jaime.casanova@2ndquadrant.com>
Subject: Re: [HACKERS] generated columns
Date: Tue, 30 Oct 2018 17:04:29 +0100
Message-ID: <b70b745d-dd1e-eab0-8290-99b8edc7ed74@2ndquadrant.com> (raw)
In-Reply-To: <24312231540910385@sas1-b3ec53dbc12b.qloud-c.yandex.net>
References: <b151f851-4019-bdb1-699e-ebab07d2f40a@2ndquadrant.com>
<CAJGNTeOxeQPFpEr35PME4yLayRzP_wwzt70kFrPztSTWHf-HDA@mail.gmail.com>
<CAJGNTeM+MCjA4dBhAdjJ-vR_CwY3u65cCcc+KvnahzLHZjxjbw@mail.gmail.com>
<1bc948f8-3bab-6c54-50af-c0723a75b85f@2ndquadrant.com>
<20180116063546.GA3679@paquier.xyz>
<48502d9b-60c4-5b10-40a2-e34f562132b4@2ndquadrant.com>
<20180119051804.GC12826@paquier.xyz>
<2e3d5147-16f8-af0f-00ab-4c72cafc896f@2ndquadrant.com>
<20180131131804.GB1586@paquier.xyz>
<bf9c8765-1aa0-432a-442e-45f715ebe4e7@2ndquadrant.com>
<20180202022532.GA84623@paquier.xyz>
<050e663d-d915-f61b-af96-5a09c10ade02@2ndquadrant.com>
<79e4de23-52e3-2c45-9ba0-5f6932f590aa@2ndquadrant.com>
<50c7c87f285dfcade272731d4399a6a3@xs4all.nl>
<24312231540910385@sas1-b3ec53dbc12b.qloud-c.yandex.net>
On 30/10/2018 15:39, Sergei Kornilov wrote:
>> patch --dry-run --ignore-whitespace -p 0 -F 5 <
>> v5-0001-Generated-columns.patch
>>
>> and varied both -p and -F paramaters to no avail. Am I doing it wrong?
> I am able apply patch by command
> patch -p1 < v5-0001-Generated-columns.patch
> or by "git apply v5-0001-Generated-columns.patch", but only till commit d5eec4eefde70414c9929b32c411cb4f0900a2a9 (Add pg_partition_tree to display information about partitions)
>
> Unfortunately patch does not applied to current HEAD. Cfbot noticed this too: http://cfbot.cputube.org/patch_20_1443.log
Here's another one.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:v6-0001-Generated-columns.patch (text/plain, 161.2K) download
From e2507d946da6ed9915030dabf3bec92817942e33 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Tue, 30 Oct 2018 16:59:49 +0100
Subject: [PATCH v6] Generated columns
This is an SQL-standard feature that allows creating columns that are
computed from expressions rather than assigned, similar to a view or
materialized view but on a column basis.
This implements two kinds of generated columns: virtual (computed on
read) and stored (computed on write).
---
doc/src/sgml/catalogs.sgml | 19 +-
doc/src/sgml/information_schema.sgml | 66 +-
doc/src/sgml/ref/copy.sgml | 3 +-
doc/src/sgml/ref/create_table.sgml | 49 +-
src/backend/access/common/tupdesc.c | 19 +-
src/backend/catalog/heap.c | 80 +-
src/backend/catalog/information_schema.sql | 30 +-
src/backend/commands/copy.c | 38 +-
src/backend/commands/indexcmds.c | 27 +-
src/backend/commands/tablecmds.c | 219 +++++-
src/backend/commands/trigger.c | 37 +
src/backend/commands/typecmds.c | 6 +-
src/backend/executor/execMain.c | 8 +-
src/backend/executor/nodeModifyTable.c | 99 +++
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/equalfuncs.c | 2 +
src/backend/nodes/outfuncs.c | 9 +
src/backend/parser/gram.y | 26 +-
src/backend/parser/parse_agg.c | 11 +
src/backend/parser/parse_expr.c | 5 +
src/backend/parser/parse_func.c | 12 +
src/backend/parser/parse_utilcmd.c | 115 ++-
src/backend/rewrite/rewriteHandler.c | 152 +++-
src/backend/utils/cache/lsyscache.c | 33 +
src/backend/utils/cache/partcache.c | 3 +
src/backend/utils/cache/relcache.c | 4 +
src/bin/pg_dump/pg_dump.c | 43 +-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/pg_dump_sort.c | 10 +
src/bin/pg_dump/t/002_pg_dump.pl | 17 +
src/bin/psql/describe.c | 25 +-
src/include/access/tupdesc.h | 1 +
src/include/catalog/heap.h | 3 +-
src/include/catalog/pg_attribute.h | 6 +
src/include/catalog/pg_class.dat | 2 +-
src/include/executor/nodeModifyTable.h | 2 +
src/include/nodes/execnodes.h | 3 +
src/include/nodes/parsenodes.h | 14 +-
src/include/parser/kwlist.h | 2 +
src/include/parser/parse_node.h | 3 +-
src/include/rewrite/rewriteHandler.h | 1 +
src/include/utils/lsyscache.h | 1 +
src/pl/plperl/expected/plperl_trigger.out | 93 +++
src/pl/plperl/plperl.c | 7 +-
src/pl/plperl/sql/plperl_trigger.sql | 34 +
src/pl/plpython/expected/plpython_trigger.out | 92 +++
src/pl/plpython/plpy_exec.c | 6 +
src/pl/plpython/plpy_typeio.c | 2 +-
src/pl/plpython/sql/plpython_trigger.sql | 35 +
src/pl/tcl/expected/pltcl_queries.out | 89 +++
src/pl/tcl/expected/pltcl_setup.out | 8 +
src/pl/tcl/pltcl.c | 6 +
src/pl/tcl/sql/pltcl_queries.sql | 25 +
src/pl/tcl/sql/pltcl_setup.sql | 10 +
.../regress/expected/create_table_like.out | 46 ++
src/test/regress/expected/generated.out | 737 ++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/create_table_like.sql | 14 +
src/test/regress/sql/generated.sql | 408 ++++++++++
60 files changed, 2731 insertions(+), 92 deletions(-)
create mode 100644 src/test/regress/expected/generated.out
create mode 100644 src/test/regress/sql/generated.sql
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgmlindex 4256516c08..27baa34b42 100644--- a/doc/src/sgml/catalogs.sgml+++ b/doc/src/sgml/catalogs.sgml@@ -1143,9 +1143,11 @@ <title><structname>pg_attribute</structname> Columns</title>
<entry><type>bool</type></entry>
<entry></entry>
<entry>
- This column has a default value, in which case there will be a- corresponding entry in the <structname>pg_attrdef</structname>- catalog that actually defines the value.+ This column has a default expression or generation expression, in which+ case there will be a corresponding entry in the+ <structname>pg_attrdef</structname> catalog that actually defines the+ expression. (Check <structfield>attgenerated</structfield> to+ determine whether this is a default or a generation expression.)
</entry>
</row>
@@ -1173,6 +1175,17 @@ <title><structname>pg_attribute</structname> Columns</title>
</entry>
</row>
+ <row>+ <entry><structfield>attgenerated</structfield></entry>+ <entry><type>char</type></entry>+ <entry></entry>+ <entry>+ If a zero byte (<literal>''</literal>), then not a generated column.+ Otherwise, <literal>s</literal> = stored, <literal>v</literal> =+ virtual.+ </entry>+ </row>+
<row>
<entry><structfield>attisdropped</structfield></entry>
<entry><type>bool</type></entry>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgmlindex b13700da92..1321ade44a 100644--- a/doc/src/sgml/information_schema.sgml+++ b/doc/src/sgml/information_schema.sgml@@ -952,6 +952,62 @@ <title><literal>collation_character_set_applicability</literal> Columns</title>
</table>
</sect1>
+ <sect1 id="infoschema-column-column-usage">+ <title><literal>column_column_usage</literal></title>++ <para>+ The view <literal>column_column_usage</literal> identifies all generated+ columns that depend on another base column in the same table. Only tables+ owned by a currently enabled role are included.+ </para>++ <table>+ <title><literal>column_column_usage</literal> Columns</title>++ <tgroup cols="3">+ <thead>+ <row>+ <entry>Name</entry>+ <entry>Data Type</entry>+ <entry>Description</entry>+ </row>+ </thead>++ <tbody>+ <row>+ <entry><literal>table_catalog</literal></entry>+ <entry><type>sql_identifier</type></entry>+ <entry>Name of the database containing the table (always the current database)</entry>+ </row>++ <row>+ <entry><literal>table_schema</literal></entry>+ <entry><type>sql_identifier</type></entry>+ <entry>Name of the schema containing the table</entry>+ </row>++ <row>+ <entry><literal>table_name</literal></entry>+ <entry><type>sql_identifier</type></entry>+ <entry>Name of the table</entry>+ </row>++ <row>+ <entry><literal>column_name</literal></entry>+ <entry><type>sql_identifier</type></entry>+ <entry>Name of the base column that a generated column depends on</entry>+ </row>++ <row>+ <entry><literal>dependent_column</literal></entry>+ <entry><type>sql_identifier</type></entry>+ <entry>Name of the generated column</entry>+ </row>+ </tbody>+ </tgroup>+ </table>+ </sect1>+
<sect1 id="infoschema-column-domain-usage">
<title><literal>column_domain_usage</literal></title>
@@ -1648,13 +1704,19 @@ <title><literal>columns</literal> Columns</title>
<row>
<entry><literal>is_generated</literal></entry>
<entry><type>character_data</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</productname></entry>+ <entry>+ If the column is a generated column, then <literal>ALWAYS</literal>,+ else <literal>NEVER</literal>.+ </entry>
</row>
<row>
<entry><literal>generation_expression</literal></entry>
<entry><type>character_data</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</productname></entry>+ <entry>+ If the column is a generated column, then the generation expression,+ else null.+ </entry>
</row>
<row>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgmlindex 13a8b68d95..b1e8214b81 100644--- a/doc/src/sgml/ref/copy.sgml+++ b/doc/src/sgml/ref/copy.sgml@@ -103,7 +103,8 @@ <title>Parameters</title>
<listitem>
<para>
An optional list of columns to be copied. If no column list is
- specified, all columns of the table will be copied.+ specified, all columns of the table except generated columns will be+ copied.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgmlindex 10428f8ff0..15b95a2992 100644--- a/doc/src/sgml/ref/create_table.sgml+++ b/doc/src/sgml/ref/create_table.sgml@@ -62,6 +62,7 @@
NULL |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
+ GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ VIRTUAL | STORED ] |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
@@ -82,7 +83,7 @@
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -629,6 +630,17 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>+ <term><literal>INCLUDING GENERATED</literal></term>+ <listitem>+ <para>+ Any generation expressions as well as the virtual/stored choice of+ copied column definitions will be copied. By default, new columns+ will be regular base columns.+ </para>+ </listitem>+ </varlistentry>+
<varlistentry>
<term><literal>INCLUDING IDENTITY</literal></term>
<listitem>
@@ -798,6 +810,31 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>+ <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ VIRTUAL | STORED ]</literal><indexterm><primary>generated column</primary></indexterm></term>+ <listitem>+ <para>+ This clause creates the column as a <firstterm>generated+ column</firstterm>. The column cannot be written to, and when read it+ will be computed from the specified expression.+ </para>++ <para>+ When <literal>VIRTUAL</literal> is specified, the column will be+ computed when it is read, and it will not occupy any storage.+ When <literal>STORED</literal> is specified, the column will be computed+ on write and will be stored on disk. <literal>VIRTUAL</literal> is the+ default.+ </para>++ <para>+ The generation expression can refer to other columns in the table, but+ not other generated columns. Any functions and operators used must be+ immutable. References to other tables are not allowed.+ </para>+ </listitem>+ </varlistentry>+
<varlistentry>
<term><literal>GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]</literal></term>
<listitem>
@@ -2070,6 +2107,16 @@ <title>Multiple Identity Columns</title>
</para>
</refsect2>
+ <refsect2>+ <title>Generated Columns</title>++ <para>+ The options <literal>VIRTUAL</literal> and <literal>STORED</literal> are+ not standard but are also used by other SQL implementations. The SQL+ standard does not specify the storage of generated columns.+ </para>+ </refsect2>+
<refsect2>
<title><literal>LIKE</literal> Clause</title>
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.cindex b0434b4672..21bd0d86cd 100644--- a/src/backend/access/common/tupdesc.c+++ b/src/backend/access/common/tupdesc.c@@ -133,6 +133,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
}
/* We can copy the tuple type identification, too */
@@ -167,6 +168,7 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
TupleConstr *cpy = (TupleConstr *) palloc0(sizeof(TupleConstr));
cpy->has_not_null = constr->has_not_null;
+ cpy->has_generated = constr->has_generated;
if ((cpy->num_defval = constr->num_defval) > 0)
{
@@ -249,6 +251,7 @@ TupleDescCopy(TupleDesc dst, TupleDesc src)
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
}
dst->constr = NULL;
@@ -302,6 +305,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
dstAtt->atthasdef = false;
dstAtt->atthasmissing = false;
dstAtt->attidentity = '\0';
+ dstAtt->attgenerated = '\0';
}
/*
@@ -460,6 +464,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (attr1->attidentity != attr2->attidentity)
return false;
+ if (attr1->attgenerated != attr2->attgenerated)+ return false;
if (attr1->attisdropped != attr2->attisdropped)
return false;
if (attr1->attislocal != attr2->attislocal)
@@ -480,6 +486,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (constr1->has_not_null != constr2->has_not_null)
return false;
+ if (constr1->has_generated != constr2->has_generated)+ return false;
n = constr1->num_defval;
if (n != (int) constr2->num_defval)
return false;
@@ -643,6 +651,7 @@ TupleDescInitEntry(TupleDesc desc,
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
@@ -702,6 +711,7 @@ TupleDescInitBuiltinEntry(TupleDesc desc,
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
@@ -855,17 +865,8 @@ BuildDescForRelation(List *schema)
TupleConstr *constr = (TupleConstr *) palloc0(sizeof(TupleConstr));
constr->has_not_null = true;
- constr->defval = NULL;- constr->missing = NULL;- constr->num_defval = 0;- constr->check = NULL;- constr->num_check = 0;
desc->constr = constr;
}
- else- {- desc->constr = NULL;- }
return desc;
}
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.cindex 3c9c03c997..b6946dac2a 100644--- a/src/backend/catalog/heap.c+++ b/src/backend/catalog/heap.c@@ -68,6 +68,7 @@
#include "parser/parse_collate.h"
#include "parser/parse_expr.h"
#include "parser/parse_relation.h"
+#include "parser/parsetree.h"
#include "storage/lmgr.h"
#include "storage/predicate.h"
#include "storage/smgr.h"
@@ -685,6 +686,7 @@ InsertPgAttributeTuple(Relation pg_attribute_rel,
values[Anum_pg_attribute_atthasdef - 1] = BoolGetDatum(new_attribute->atthasdef);
values[Anum_pg_attribute_atthasmissing - 1] = BoolGetDatum(new_attribute->atthasmissing);
values[Anum_pg_attribute_attidentity - 1] = CharGetDatum(new_attribute->attidentity);
+ values[Anum_pg_attribute_attgenerated - 1] = CharGetDatum(new_attribute->attgenerated);
values[Anum_pg_attribute_attisdropped - 1] = BoolGetDatum(new_attribute->attisdropped);
values[Anum_pg_attribute_attislocal - 1] = BoolGetDatum(new_attribute->attislocal);
values[Anum_pg_attribute_attinhcount - 1] = Int32GetDatum(new_attribute->attinhcount);
@@ -2160,6 +2162,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
Relation attrrel;
HeapTuple atttup;
Form_pg_attribute attStruct;
+ char attgenerated;
Oid attrdefOid;
ObjectAddress colobject,
defobject;
@@ -2215,6 +2218,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
elog(ERROR, "cache lookup failed for attribute %d of relation %u",
attnum, RelationGetRelid(rel));
attStruct = (Form_pg_attribute) GETSTRUCT(atttup);
+ attgenerated = attStruct->attgenerated;
if (!attStruct->atthasdef)
{
Form_pg_attribute defAttStruct;
@@ -2235,7 +2239,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
valuesAtt[Anum_pg_attribute_atthasdef - 1] = true;
replacesAtt[Anum_pg_attribute_atthasdef - 1] = true;
- if (add_column_mode)+ if (add_column_mode && !attgenerated)
{
expr2 = expression_planner(expr2);
estate = CreateExecutorState();
@@ -2297,7 +2301,26 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
/*
* Record dependencies on objects used in the expression, too.
*/
- recordDependencyOnExpr(&defobject, expr, NIL, DEPENDENCY_NORMAL);+ if (attgenerated)+ {+ /*+ * Generated column: Dropping anything that the generation expression+ * refers to automatically drops the generated column.+ */+ recordDependencyOnSingleRelExpr(&colobject, expr, RelationGetRelid(rel),+ DEPENDENCY_AUTO,+ DEPENDENCY_AUTO, false);+ }+ else+ {+ /*+ * Normal default: Dropping anything that the default refers to+ * requires CASCADE and drops the default only.+ */+ recordDependencyOnSingleRelExpr(&defobject, expr, RelationGetRelid(rel),+ DEPENDENCY_NORMAL,+ DEPENDENCY_NORMAL, false);+ }
/*
* Post creation hook for attribute defaults.
@@ -2566,7 +2589,8 @@ AddRelationNewConstraints(Relation rel,
expr = cookDefault(pstate, colDef->raw_default,
atp->atttypid, atp->atttypmod,
- NameStr(atp->attname));+ NameStr(atp->attname),+ atp->attgenerated);
/*
* If the expression is just a NULL constant, we do not bother to make
@@ -2937,6 +2961,46 @@ SetRelationNumChecks(Relation rel, int numchecks)
heap_close(relrel, RowExclusiveLock);
}
+/*+ * Check for references to generated columns+ */+static bool+check_nested_generated_walker(Node *node, void *context)+{+ ParseState *pstate = context;++ if (node == NULL)+ return false;+ else if (IsA(node, Var))+ {+ Var *var = (Var *) node;+ Oid relid;+ AttrNumber attnum;++ relid = rt_fetch(var->varno, pstate->p_rtable)->relid;+ attnum = var->varattno;++ if (relid && attnum && get_attgenerated(relid, attnum))+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("cannot use generated column \"%s\" in column generation expression",+ get_attname(relid, attnum, false)),+ errdetail("A generated column cannot reference another generated column."),+ parser_errposition(pstate, var->location)));++ return false;+ }+ else+ return expression_tree_walker(node, check_nested_generated_walker,+ (void *) context);+}++static void+check_nested_generated(ParseState *pstate, Node *node)+{+ check_nested_generated_walker(node, pstate);+}+
/*
* Take a raw default and convert it to a cooked format ready for
* storage.
@@ -2954,7 +3018,8 @@ cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
- const char *attname)+ const char *attname,+ char attgenerated)
{
Node *expr;
@@ -2963,17 +3028,20 @@ cookDefault(ParseState *pstate,
/*
* Transform raw parsetree to executable expression.
*/
- expr = transformExpr(pstate, raw_default, EXPR_KIND_COLUMN_DEFAULT);+ expr = transformExpr(pstate, raw_default, attgenerated ? EXPR_KIND_GENERATED_COLUMN : EXPR_KIND_COLUMN_DEFAULT);
/*
* Make sure default expr does not refer to any vars (we need this check
* since the pstate includes the target table).
*/
- if (contain_var_clause(expr))+ if (!attgenerated && contain_var_clause(expr))
ereport(ERROR,
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
errmsg("cannot use column references in default expression")));
+ if (attgenerated)+ check_nested_generated(pstate, expr);+
/*
* transformExpr() should have already rejected subqueries, aggregates,
* window functions, and SRFs, based on the EXPR_KIND_ for a default
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sqlindex f4e69f4a26..c3825b4ed1 100644--- a/src/backend/catalog/information_schema.sql+++ b/src/backend/catalog/information_schema.sql@@ -509,7 +509,29 @@ CREATE VIEW collation_character_set_applicability AS
* COLUMN_COLUMN_USAGE view
*/
--- feature not supported+CREATE VIEW column_column_usage AS+ SELECT CAST(current_database() AS sql_identifier) AS table_catalog,+ CAST(n.nspname AS sql_identifier) AS table_schema,+ CAST(c.relname AS sql_identifier) AS table_name,+ CAST(ac.attname AS sql_identifier) AS column_name,+ CAST(ad.attname AS sql_identifier) AS dependent_column++ FROM pg_namespace n, pg_class c, pg_depend d,+ pg_attribute ac, pg_attribute ad++ WHERE n.oid = c.relnamespace+ AND c.oid = ac.attrelid+ AND c.oid = ad.attrelid+ AND d.classid = 'pg_catalog.pg_class'::regclass+ AND d.refclassid = 'pg_catalog.pg_class'::regclass+ AND d.objid = d.refobjid+ AND c.oid = d.objid+ AND d.objsubid = ad.attnum+ AND d.refobjsubid = ac.attnum+ AND ad.attgenerated <> ''+ AND pg_has_role(c.relowner, 'USAGE');++GRANT SELECT ON column_column_usage TO PUBLIC;
/*
@@ -656,7 +678,7 @@ CREATE VIEW columns AS
CAST(c.relname AS sql_identifier) AS table_name,
CAST(a.attname AS sql_identifier) AS column_name,
CAST(a.attnum AS cardinal_number) AS ordinal_position,
- CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,+ CAST(CASE WHEN a.attgenerated = '' THEN pg_get_expr(ad.adbin, ad.adrelid) END AS character_data) AS column_default,
CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
AS yes_or_no)
AS is_nullable,
@@ -745,8 +767,8 @@ CREATE VIEW columns AS
CAST(seq.seqmin AS character_data) AS identity_minimum,
CAST(CASE WHEN seq.seqcycle THEN 'YES' ELSE 'NO' END AS yes_or_no) AS identity_cycle,
- CAST('NEVER' AS character_data) AS is_generated,- CAST(null AS character_data) AS generation_expression,+ CAST(CASE WHEN a.attgenerated <> '' THEN 'ALWAYS' ELSE 'NEVER' END AS character_data) AS is_generated,+ CAST(CASE WHEN a.attgenerated <> '' THEN pg_get_expr(ad.adbin, ad.adrelid) END AS character_data) AS generation_expression,
CAST(CASE WHEN c.relkind IN ('r', 'p') OR
(c.relkind IN ('v', 'f') AND
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.cindex b58a74f4e3..3a7b229bca 100644--- a/src/backend/commands/copy.c+++ b/src/backend/commands/copy.c@@ -31,6 +31,7 @@
#include "commands/trigger.h"
#include "executor/execPartition.h"
#include "executor/executor.h"
+#include "executor/nodeModifyTable.h"
#include "executor/tuptable.h"
#include "foreign/fdwapi.h"
#include "libpq/libpq.h"
@@ -2911,6 +2912,28 @@ CopyFrom(CopyState cstate)
}
else
{
+ /*+ * Normal case: insert tuple into table+ */++ HeapTuple newtuple;++ /*+ * Compute stored generated columns+ *+ * Switch memory context so that the new tuple is in the same+ * context as the old one. Note that we don't use the slot's+ * context.+ */+ if (resultRelInfo->ri_RelationDesc->rd_att->constr &&+ resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated)+ {+ MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));+ if ((newtuple = ExecComputeStoredGenerated(estate, slot)))+ tuple = newtuple;+ MemoryContextSwitchTo(oldcontext);+ }+
/*
* If the target is a plain table, check the constraints of
* the tuple.
@@ -3257,7 +3280,7 @@ BeginCopyFrom(ParseState *pstate,
fmgr_info(in_func_oid, &in_functions[attnum - 1]);
/* Get default info if needed */
- if (!list_member_int(cstate->attnumlist, attnum))+ if (!list_member_int(cstate->attnumlist, attnum) && !att->attgenerated)
{
/* attribute is NOT to be copied from input */
/* use default value if one exists */
@@ -4920,6 +4943,11 @@ CopyAttributeOutCSV(CopyState cstate, char *string,
* or NIL if there was none (in which case we want all the non-dropped
* columns).
*
+ * We don't include generated columns in the generated full list and we don't+ * allow them to be specified explicitly. They don't make sense for COPY+ * FROM, but we could possibly allow them for COPY TO. But this way it's at+ * least ensured that whatever we copy out can be copied back in.+ *
* rel can be NULL ... it's only used for error reports.
*/
static List *
@@ -4937,6 +4965,8 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, List *attnamelist)
{
if (TupleDescAttr(tupDesc, i)->attisdropped)
continue;
+ if (TupleDescAttr(tupDesc, i)->attgenerated)+ continue;
attnums = lappend_int(attnums, i + 1);
}
}
@@ -4961,6 +4991,12 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, List *attnamelist)
continue;
if (namestrcmp(&(att->attname), name) == 0)
{
+ if (att->attgenerated)+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),+ errmsg("column \"%s\" is a generated column",+ name),+ errdetail("Generated columns cannot be used in COPY.")));
attnum = att->attnum;
break;
}
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex 906d711378..c3342baacc 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -750,6 +750,9 @@ DefineIndex(Oid relationId,
/*
* We disallow indexes on system columns other than OID. They would not
* necessarily get updated correctly, and they don't seem useful anyway.
+ *+ * Also disallow virtual generated columns in indexes (use expression+ * index instead).
*/
for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
{
@@ -759,10 +762,16 @@ DefineIndex(Oid relationId,
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("index creation on system columns is not supported")));
++ if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == 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)
{
@@ -780,6 +789,22 @@ DefineIndex(Oid relationId,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("index creation on system columns is not supported")));
}
++ /*+ * XXX Virtual generated columns in index expressions or predicates+ * could be supported, but it needs support in+ * RelationGetIndexExpressions() and RelationGetIndexPredicate().+ */+ i = -1;+ while ((i = bms_next_member(indexattrs, i)) >= 0)+ {+ AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;++ if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("index creation on virtual generated columns is not supported")));+ }
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 153aec263e..7873a386a1 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -742,6 +742,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (colDef->identity)
attr->attidentity = colDef->identity;
++ if (colDef->generated)+ attr->attgenerated = colDef->generated;
}
/*
@@ -787,6 +790,27 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
*/
rel = relation_open(relationId, AccessExclusiveLock);
+ /*+ * Now add any newly specified column default and generation expressions+ * to the new relation. These are passed to us in the form of raw+ * parsetrees; we need to transform them to executable expression trees+ * before they can be added. The most convenient way to do that is to+ * apply the parser's transformExpr routine, but transformExpr doesn't+ * work unless we have a pre-existing relation. So, the transformation has+ * to be postponed to this final step of CREATE TABLE.+ *+ * This needs to be before processing the partitioning clauses because+ * those could refer to generated columns.+ */+ if (rawDefaults)+ AddRelationNewConstraints(rel, rawDefaults, NIL,+ true, true, false, queryString);++ /*+ * Make column generation expressions visible for use by partitioning.+ */+ CommandCounterIncrement();+
/* Process and store partition bound, if any. */
if (stmt->partbound)
{
@@ -979,16 +1003,12 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
/*
- * Now add any newly specified column default values and CHECK constraints- * to the new relation. These are passed to us in the form of raw- * parsetrees; we need to transform them to executable expression trees- * before they can be added. The most convenient way to do that is to- * apply the parser's transformExpr routine, but transformExpr doesn't- * work unless we have a pre-existing relation. So, the transformation has- * to be postponed to this final step of CREATE TABLE.+ * Now add any newly specified CHECK constraints to the new relation.+ * Same as for defaults above, but these need to come after partitioning+ * is set up.
*/
- if (rawDefaults || stmt->constraints)- AddRelationNewConstraints(rel, rawDefaults, stmt->constraints,+ if (stmt->constraints)+ AddRelationNewConstraints(rel, NIL, stmt->constraints,
true, true, false, queryString);
ObjectAddressSet(address, RelationRelationId, relationId);
@@ -2152,6 +2172,13 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
def->is_not_null |= attribute->attnotnull;
/* Default and other constraints are handled below */
newattno[parent_attno - 1] = exist_attno;
++ /* Check for GENERATED conflicts */+ if (def->generated != attribute->attgenerated)+ ereport(ERROR,+ (errcode(ERRCODE_DATATYPE_MISMATCH),+ errmsg("inherited column \"%s\" has a generation conflict",+ attributeName)));
}
else
{
@@ -2170,6 +2197,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
def->storage = attribute->attstorage;
def->raw_default = NULL;
def->cooked_default = NULL;
+ def->generated = attribute->attgenerated;
def->collClause = NULL;
def->collOid = attribute->attcollation;
def->constraints = NIL;
@@ -4637,7 +4665,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
{
case CONSTR_CHECK:
needscan = true;
- con->qualstate = ExecPrepareExpr((Expr *) con->qual, estate);+ con->qualstate = ExecPrepareExpr((Expr *) expand_generated_columns_in_expr(con->qual,+ newrel ? newrel : oldrel),+ estate);
break;
case CONSTR_FOREIGN:
/* Nothing to do here */
@@ -5536,6 +5566,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
attribute.atthasdef = false;
attribute.atthasmissing = false;
attribute.attidentity = colDef->identity;
+ attribute.attgenerated = colDef->generated;
attribute.attisdropped = false;
attribute.attislocal = colDef->is_local;
attribute.attinhcount = colDef->inhcount;
@@ -5945,6 +5976,18 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
errmsg("cannot alter system column \"%s\"",
colName)));
+ /*+ * Virtual generated columns don't use the attnotnull field but use a full+ * CHECK constraint instead. We could implement here that it finds that+ * CHECK constraint and drops it, which is kind of what the SQL standard+ * would require anyway, but that would be quite a bit more work.+ */+ if (attTup->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("cannot use DROP NOT NULL on virtual generated column \"%s\"",+ colName)));+
if (attTup->attidentity)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -6093,6 +6136,17 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
errmsg("cannot alter system column \"%s\"",
colName)));
+ /*+ * XXX We might want to convert this to a CHECK constraint like we do in+ * transformColumnDefinition().+ */+ if (((Form_pg_attribute) GETSTRUCT(tuple))->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("cannot use SET NOT NULL on virtual generated column \"%s\"",+ colName),+ errhint("Add a CHECK constraint instead.")));+
/*
* Okay, actually perform the catalog change ... if needed
*/
@@ -6156,6 +6210,12 @@ ATExecColumnDefault(Relation rel, const char *colName,
colName, RelationGetRelationName(rel)),
newDefault ? 0 : errhint("Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.")));
+ if (TupleDescAttr(tupdesc, attnum - 1)->attgenerated)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("column \"%s\" of relation \"%s\" is a generated column",+ colName, RelationGetRelationName(rel))));+
/*
* Remove any old default for the column. We use RESTRICT here for
* safety, but at present we do not expect anything to depend on the
@@ -7470,6 +7530,41 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
*/
checkFkeyPermissions(pkrel, pkattnum, numpks);
+ /*+ * Check some things for generated columns.+ */+ for (i = 0; i < numfks; i++)+ {+ char attgenerated = TupleDescAttr(RelationGetDescr(rel), fkattnum[i] - 1)->attgenerated;++ if (attgenerated)+ {+ /*+ * Check restrictions on UPDATE/DELETE actions, per SQL standard+ */+ if (fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETNULL ||+ fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETDEFAULT ||+ fkconstraint->fk_upd_action == FKCONSTR_ACTION_CASCADE)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("invalid ON UPDATE action for foreign key constraint containing generated column")));+ if (fkconstraint->fk_del_action == FKCONSTR_ACTION_SETNULL ||+ fkconstraint->fk_del_action == FKCONSTR_ACTION_SETDEFAULT)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("invalid ON DELETE action for foreign key constraint containing generated column")));+ }++ /*+ * FKs on virtual columns are not supported, does not have support in+ * ri_triggers.c+ */+ if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("foreign key constraints on virtual generated columns are not supported")));+ }+
/*
* Look up the equality operators to use in the constraint.
*
@@ -8475,7 +8570,7 @@ validateCheckConstraint(Relation rel, HeapTuple constrtup)
EState *estate;
Datum val;
char *conbin;
- Expr *origexpr;+ Node *origexpr;
ExprState *exprstate;
TupleDesc tupdesc;
HeapScanDesc scan;
@@ -8510,8 +8605,8 @@ validateCheckConstraint(Relation rel, HeapTuple constrtup)
elog(ERROR, "null conbin for constraint %u",
HeapTupleGetOid(constrtup));
conbin = TextDatumGetCString(val);
- origexpr = (Expr *) stringToNode(conbin);- exprstate = ExecPrepareExpr(origexpr, estate);+ origexpr = stringToNode(conbin);+ exprstate = ExecPrepareExpr((Expr *) expand_generated_columns_in_expr(origexpr, rel), estate);
econtext = GetPerTupleExprContext(estate);
tupdesc = RelationGetDescr(rel);
@@ -9160,8 +9255,9 @@ ATPrepAlterColumnType(List **wqueue,
list_make1_oid(rel->rd_rel->reltype),
false);
- if (tab->relkind == RELKIND_RELATION ||- tab->relkind == RELKIND_PARTITIONED_TABLE)+ if ((tab->relkind == RELKIND_RELATION ||+ tab->relkind == RELKIND_PARTITIONED_TABLE) &&+ attTup->attgenerated != ATTRIBUTE_GENERATED_VIRTUAL)
{
/*
* Set up an expression to transform the old data value to the new
@@ -9435,10 +9531,18 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
COERCE_IMPLICIT_CAST,
-1);
if (defaultexpr == NULL)
- ereport(ERROR,- (errcode(ERRCODE_DATATYPE_MISMATCH),- errmsg("default for column \"%s\" cannot be cast automatically to type %s",- colName, format_type_be(targettype))));+ {+ if (attTup->attgenerated)+ ereport(ERROR,+ (errcode(ERRCODE_DATATYPE_MISMATCH),+ errmsg("generation expression for column \"%s\" cannot be cast automatically to type %s",+ colName, format_type_be(targettype))));+ else+ ereport(ERROR,+ (errcode(ERRCODE_DATATYPE_MISMATCH),+ errmsg("default for column \"%s\" cannot be cast automatically to type %s",+ colName, format_type_be(targettype))));+ }
}
else
defaultexpr = NULL;
@@ -9514,6 +9618,21 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
*/
Assert(foundObject.objectSubId == 0);
}
+ else if (relKind == RELKIND_RELATION &&+ foundObject.objectSubId != 0 &&+ get_attgenerated(foundObject.objectId, foundObject.objectSubId))+ {+ /*+ * Changing the type of a column that is used by a+ * generated column is not allowed by SQL standard.+ * It might be doable with some thinking and effort.+ */+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("cannot alter type of a column used by a generated column"),+ errdetail("Column \"%s\" is used by generated column \"%s\".",+ colName, get_attname(foundObject.objectId, foundObject.objectSubId, false))));+ }
else
{
/* Not expecting any other direct dependencies... */
@@ -9658,7 +9777,8 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
/*
* Now scan for dependencies of this column on other things. The only
* thing we should find is the dependency on the column datatype, which we
- * want to remove, and possibly a collation dependency.+ * want to remove, possibly a collation dependency, and dependencies on+ * other columns if it is a generated column.
*/
ScanKeyInit(&key[0],
Anum_pg_depend_classid,
@@ -9679,15 +9799,26 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
while (HeapTupleIsValid(depTup = systable_getnext(scan)))
{
Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(depTup);
+ ObjectAddress foundObject;- if (foundDep->deptype != DEPENDENCY_NORMAL)+ foundObject.classId = foundDep->refclassid;+ foundObject.objectId = foundDep->refobjid;+ foundObject.objectSubId = foundDep->refobjsubid;++ if (foundDep->deptype != DEPENDENCY_NORMAL &&+ foundDep->deptype != DEPENDENCY_AUTO)
elog(ERROR, "found unexpected dependency type '%c'",
foundDep->deptype);
if (!(foundDep->refclassid == TypeRelationId &&
foundDep->refobjid == attTup->atttypid) &&
!(foundDep->refclassid == CollationRelationId &&
- foundDep->refobjid == attTup->attcollation))- elog(ERROR, "found unexpected dependency for column");+ foundDep->refobjid == attTup->attcollation) &&+ !(foundDep->refclassid == RelationRelationId &&+ foundDep->refobjid == RelationGetRelid(rel) &&+ foundDep->refobjsubid != 0)+ )+ elog(ERROR, "found unexpected dependency for column: %s",+ getObjectDescription(&foundObject));
CatalogTupleDelete(depRel, &depTup->t_self);
}
@@ -13716,6 +13847,18 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
pelem->name),
parser_errposition(pstate, pelem->location)));
+ /*+ * Some generated columns could perhaps be supported in partition+ * expressions instead; see below.+ */+ if (attform->attgenerated)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("using generated column in partition key is not supported"),+ errdetail("Column \"%s\" is a generated column.",+ pelem->name),+ parser_errposition(pstate, pelem->location)));+
partattrs[attn] = attform->attnum;
atttype = attform->atttypid;
attcollation = attform->attcollation;
@@ -13803,6 +13946,36 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
errmsg("partition key expressions cannot contain system column references")));
}
+ /*+ * Generated columns in partition key expressions:+ *+ * - Stored generated columns cannot work: They are computed+ * after BEFORE triggers, but partition routing is done+ * before all triggers.+ *+ * - Virtual generated columns could work. But there is a+ * problem when dropping such a table: Dropping a table+ * calls relation_open(), which causes partition keys to be+ * constructed for the partcache, but at that point the+ * generation expression is already deleted (through+ * dependencies), so this will fail. So if you remove the+ * restriction below, things will appear to work, but you+ * can't drop the table. :-(+ */+ i = -1;+ while ((i = bms_next_member(expr_attrs, i)) >= 0)+ {+ AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;++ if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("using generated column in partition key is not supported"),+ errdetail("Column \"%s\" is a generated column.",+ get_attname(RelationGetRelid(rel), attno, false)),+ parser_errposition(pstate, pelem->location)));+ }+
/*
* While it is not exactly *wrong* for a partition expression
* to be a constant, it seems better to reject such keys.
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.cindex 240e85e391..0d26282324 100644--- a/src/backend/commands/trigger.c+++ b/src/backend/commands/trigger.c@@ -44,6 +44,7 @@
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "pgstat.h"
+#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
@@ -102,6 +103,7 @@ static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
TransitionCaptureState *transition_capture);
static void AfterTriggerEnlargeQueryState(void);
static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
+static void check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple tuple);
/*
@@ -638,6 +640,11 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"),
parser_errposition(pstate, var->location)));
+ if (TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attgenerated && TRIGGER_FOR_BEFORE(tgtype))+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),+ parser_errposition(pstate, var->location)));
break;
default:
/* can't happen without add_missing_from, so just elog */
@@ -2571,6 +2578,8 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
TupleTableSlot *newslot = estate->es_trig_tuple_slot;
TupleDesc tupdesc = RelationGetDescr(relinfo->ri_RelationDesc);
+ check_modified_virtual_generated(tupdesc, newtuple);+
if (newslot->tts_tupleDescriptor != tupdesc)
ExecSetSlotDescriptor(newslot, tupdesc);
ExecStoreHeapTuple(newtuple, newslot, false);
@@ -3078,6 +3087,8 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
TupleTableSlot *newslot = estate->es_trig_tuple_slot;
TupleDesc tupdesc = RelationGetDescr(relinfo->ri_RelationDesc);
+ check_modified_virtual_generated(tupdesc, newtuple);+
if (newslot->tts_tupleDescriptor != tupdesc)
ExecSetSlotDescriptor(newslot, tupdesc);
ExecStoreHeapTuple(newtuple, newslot, false);
@@ -3484,6 +3495,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);
@@ -6159,3 +6171,28 @@ pg_trigger_depth(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(MyTriggerDepth);
}
++/*+ * Check whether a trigger modified a virtual generated column and error if+ * so.+ */+static void+check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple tuple)+{+ if (!(tupdesc->constr && tupdesc->constr->has_generated))+ return;++ for (int i = 0; i < tupdesc->natts; i++)+ {+ if (TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)+ {+ bool isnull;++ fastgetattr(tuple, i + 1, tupdesc, &isnull);+ if (!isnull)+ ereport(ERROR,+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),+ errmsg("trigger modified virtual generated column value")));+ }+ }+}diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.cindex 3271962a7a..e81294f278 100644--- a/src/backend/commands/typecmds.c+++ b/src/backend/commands/typecmds.c@@ -914,7 +914,8 @@ DefineDomain(CreateDomainStmt *stmt)
defaultExpr = cookDefault(pstate, constr->raw_expr,
basetypeoid,
basetypeMod,
- domainName);+ domainName,+ 0);
/*
* If the expression is just a NULL constant, we treat it
@@ -2219,7 +2220,8 @@ AlterDomainDefault(List *names, Node *defaultRaw)
defaultExpr = cookDefault(pstate, defaultRaw,
typTup->typbasetype,
typTup->typtypmod,
- NameStr(typTup->typname));+ NameStr(typTup->typname),+ 0);
/*
* If the expression is just a NULL constant, we treat the command
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.cindex ba156f8c5f..521db10d48 100644--- a/src/backend/executor/execMain.c+++ b/src/backend/executor/execMain.c@@ -53,7 +53,7 @@
#include "miscadmin.h"
#include "optimizer/clauses.h"
#include "parser/parsetree.h"
-#include "rewrite/rewriteManip.h"+#include "rewrite/rewriteHandler.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
#include "tcop/utility.h"
@@ -1321,6 +1321,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
resultRelInfo->ri_FdwState = NULL;
resultRelInfo->ri_usesFdwDirectModify = false;
resultRelInfo->ri_ConstraintExprs = NULL;
+ resultRelInfo->ri_GeneratedExprs = NULL;
resultRelInfo->ri_junkFilter = NULL;
resultRelInfo->ri_projectReturning = NULL;
resultRelInfo->ri_onConflictArbiterIndexes = NIL;
@@ -1809,6 +1810,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);
}
@@ -2297,6 +2299,10 @@ ExecBuildSlotValueDescription(Oid reloid,
if (att->attisdropped)
continue;
+ /* ignore virtual generated columns; they are always null here */+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)+ continue;+
if (!table_perm)
{
/*
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.cindex 528f58717e..73332eecab 100644--- a/src/backend/executor/nodeModifyTable.c+++ b/src/backend/executor/nodeModifyTable.c@@ -46,6 +46,7 @@
#include "foreign/fdwapi.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
+#include "rewrite/rewriteHandler.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
#include "utils/builtins.h"
@@ -250,6 +251,84 @@ ExecCheckTIDVisible(EState *estate,
ReleaseBuffer(buffer);
}
+HeapTuple+ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot)+{+ ResultRelInfo *resultRelInfo = estate->es_result_relation_info;+ Relation rel = resultRelInfo->ri_RelationDesc;+ TupleDesc tupdesc = RelationGetDescr(rel);+ int natts = tupdesc->natts;+ Datum *values;+ bool *nulls;+ bool *replaces;+ bool any_changes = false;++ values = palloc(sizeof(*values) * natts);+ nulls = palloc(sizeof(*nulls) * natts);+ replaces = palloc0(sizeof(*replaces) * natts);++ /*+ * If first time through for this result relation, build expression+ * nodetrees for rel's stored generation expressions. Keep them in the+ * per-query memory context so they'll survive throughout the query.+ */+ if (resultRelInfo->ri_GeneratedExprs == NULL)+ {+ MemoryContext oldContext;++ oldContext = MemoryContextSwitchTo(estate->es_query_cxt);+ resultRelInfo->ri_GeneratedExprs =+ (ExprState **) palloc(natts * sizeof(ExprState *));++ for (int i = 0; i < natts; i++)+ {+ if (TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_STORED)+ {+ Expr *expr;++ expr = (Expr *) build_column_default(rel, i + 1);+ Assert(expr);++ resultRelInfo->ri_GeneratedExprs[i] = ExecPrepareExpr(expr, estate);+ }+ }++ MemoryContextSwitchTo(oldContext);+ }++ for (int i = 0; i < natts; i++)+ {+ if (TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_STORED)+ {+ ExprContext *econtext;+ Datum val;+ bool isnull;++ econtext = GetPerTupleExprContext(estate);+ econtext->ecxt_scantuple = slot;++ val = ExecEvalExprSwitchContext(resultRelInfo->ri_GeneratedExprs[i], econtext, &isnull);++ values[i] = val;+ nulls[i] = isnull;+ replaces[i] = true;+ any_changes = true;+ }+ }++ if (any_changes)+ {+ HeapTuple tuple;++ tuple = ExecFetchSlotTuple(slot);+ tuple = heap_modify_tuple(tuple, tupdesc, values, nulls, replaces);+ ExecStoreHeapTuple(tuple, slot, false);+ return tuple;+ }++ return NULL;+}+
/* ----------------------------------------------------------------
* ExecInsert
*
@@ -372,6 +451,16 @@ ExecInsert(ModifyTableState *mtstate,
*/
tuple->t_tableOid = RelationGetRelid(resultRelationDesc);
+ /*+ * Compute stored generated columns+ */+ if (resultRelationDesc->rd_att->constr &&+ resultRelationDesc->rd_att->constr->has_generated)+ {+ ExecComputeStoredGenerated(estate, slot);+ tuple = ExecMaterializeSlot(slot);+ }+
/*
* Check any RLS WITH CHECK policies.
*
@@ -1027,6 +1116,16 @@ ExecUpdate(ModifyTableState *mtstate,
*/
tuple->t_tableOid = RelationGetRelid(resultRelationDesc);
+ /*+ * Compute stored generated columns+ */+ if (resultRelationDesc->rd_att->constr &&+ resultRelationDesc->rd_att->constr->has_generated)+ {+ ExecComputeStoredGenerated(estate, slot);+ tuple = ExecMaterializeSlot(slot);+ }+
/*
* Check any RLS UPDATE WITH CHECK policies
*
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.cindex e8ea59e34a..6b9752a020 100644--- a/src/backend/nodes/copyfuncs.c+++ b/src/backend/nodes/copyfuncs.c@@ -2879,6 +2879,7 @@ _copyColumnDef(const ColumnDef *from)
COPY_NODE_FIELD(cooked_default);
COPY_SCALAR_FIELD(identity);
COPY_NODE_FIELD(identitySequence);
+ COPY_SCALAR_FIELD(generated);
COPY_NODE_FIELD(collClause);
COPY_SCALAR_FIELD(collOid);
COPY_NODE_FIELD(constraints);
@@ -2902,6 +2903,7 @@ _copyConstraint(const Constraint *from)
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
COPY_SCALAR_FIELD(generated_when);
+ COPY_SCALAR_FIELD(generated_kind);
COPY_NODE_FIELD(keys);
COPY_NODE_FIELD(including);
COPY_NODE_FIELD(exclusions);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.cindex 3bb91c9595..0459dd344f 100644--- a/src/backend/nodes/equalfuncs.c+++ b/src/backend/nodes/equalfuncs.c@@ -2559,6 +2559,7 @@ _equalColumnDef(const ColumnDef *a, const ColumnDef *b)
COMPARE_NODE_FIELD(cooked_default);
COMPARE_SCALAR_FIELD(identity);
COMPARE_NODE_FIELD(identitySequence);
+ COMPARE_SCALAR_FIELD(generated);
COMPARE_NODE_FIELD(collClause);
COMPARE_SCALAR_FIELD(collOid);
COMPARE_NODE_FIELD(constraints);
@@ -2580,6 +2581,7 @@ _equalConstraint(const Constraint *a, const Constraint *b)
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
COMPARE_SCALAR_FIELD(generated_when);
+ COMPARE_SCALAR_FIELD(generated_kind);
COMPARE_NODE_FIELD(keys);
COMPARE_NODE_FIELD(including);
COMPARE_NODE_FIELD(exclusions);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.cindex 69731ccdea..14e2184f5a 100644--- a/src/backend/nodes/outfuncs.c+++ b/src/backend/nodes/outfuncs.c@@ -2880,6 +2880,7 @@ _outColumnDef(StringInfo str, const ColumnDef *node)
WRITE_NODE_FIELD(cooked_default);
WRITE_CHAR_FIELD(identity);
WRITE_NODE_FIELD(identitySequence);
+ WRITE_CHAR_FIELD(generated);
WRITE_NODE_FIELD(collClause);
WRITE_OID_FIELD(collOid);
WRITE_NODE_FIELD(constraints);
@@ -3551,6 +3552,14 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_CHAR_FIELD(generated_when);
break;
+ case CONSTR_GENERATED:+ appendStringInfoString(str, "GENERATED");+ WRITE_NODE_FIELD(raw_expr);+ WRITE_STRING_FIELD(cooked_expr);+ WRITE_CHAR_FIELD(generated_when);+ WRITE_CHAR_FIELD(generated_kind);+ break;+
case CONSTR_CHECK:
appendStringInfoString(str, "CHECK");
WRITE_BOOL_FIELD(is_no_inherit);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.yindex 6d23bfb0b3..bdc64d2f4f 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -575,7 +575,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
-%type <ival> generated_when override_kind+%type <ival> generated_when override_kind opt_virtual_or_stored
%type <partspec> PartitionSpec OptPartitionSpec
%type <str> part_strategy
%type <partelem> part_elem
@@ -676,7 +676,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
- START STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P+ START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P
SUBSCRIPTION SUBSTRING SYMMETRIC SYSID SYSTEM_P
TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
@@ -688,7 +688,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UNTIL UPDATE USER USING
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
- VERBOSE VERSION_P VIEW VIEWS VOLATILE+ VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -3553,6 +3553,17 @@ ColConstraintElem:
n->location = @1;
$$ = (Node *)n;
}
+ | GENERATED generated_when AS '(' a_expr ')' opt_virtual_or_stored+ {+ Constraint *n = makeNode(Constraint);+ n->contype = CONSTR_GENERATED;+ n->generated_when = $2;+ n->raw_expr = $5;+ n->cooked_expr = NULL;+ n->generated_kind = $7;+ n->location = @1;+ $$ = (Node *)n;+ }
| REFERENCES qualified_name opt_column_list key_match key_actions
{
Constraint *n = makeNode(Constraint);
@@ -3575,6 +3586,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
@@ -3643,6 +3660,7 @@ TableLikeOption:
| CONSTRAINTS { $$ = CREATE_TABLE_LIKE_CONSTRAINTS; }
| DEFAULTS { $$ = CREATE_TABLE_LIKE_DEFAULTS; }
| IDENTITY_P { $$ = CREATE_TABLE_LIKE_IDENTITY; }
+ | GENERATED { $$ = CREATE_TABLE_LIKE_GENERATED; }
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
@@ -15235,6 +15253,7 @@ unreserved_keyword:
| STDIN
| STDOUT
| STORAGE
+ | STORED
| STRICT_P
| STRIP_P
| SUBSCRIPTION
@@ -15271,6 +15290,7 @@ unreserved_keyword:
| VERSION_P
| VIEW
| VIEWS
+ | VIRTUAL
| VOLATILE
| WHITESPACE_P
| WITHIN
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.cindex 61727e1d71..fb3387b683 100644--- a/src/backend/parser/parse_agg.c+++ b/src/backend/parser/parse_agg.c@@ -514,6 +514,14 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
err = _("grouping operations are not allowed in partition key expressions");
break;
+ case EXPR_KIND_GENERATED_COLUMN:++ if (isAgg)+ err = _("aggregate functions are not allowed in column generation expressions");+ else+ err = _("grouping operations are not allowed in column generation expressions");++ break;
case EXPR_KIND_CALL_ARGUMENT:
if (isAgg)
@@ -902,6 +910,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_CALL_ARGUMENT:
err = _("window functions are not allowed in CALL arguments");
break;
+ case EXPR_KIND_GENERATED_COLUMN:+ err = _("window functions are not allowed in column generation expressions");+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.cindex 385e54a9b6..853deaa8d7 100644--- a/src/backend/parser/parse_expr.c+++ b/src/backend/parser/parse_expr.c@@ -1849,6 +1849,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_CALL_ARGUMENT:
err = _("cannot use subquery in CALL argument");
break;
+ case EXPR_KIND_GENERATED_COLUMN:+ err = _("cannot use subquery in column generation expression");+ break;
/*
* There is intentionally no default: case here, so that the
@@ -3475,6 +3478,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "PARTITION BY";
case EXPR_KIND_CALL_ARGUMENT:
return "CALL";
+ case EXPR_KIND_GENERATED_COLUMN:+ return "GENERATED AS";
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.cindex 44257154b8..8aeed63a69 100644--- a/src/backend/parser/parse_func.c+++ b/src/backend/parser/parse_func.c@@ -621,6 +621,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
parser_errposition(pstate, location)));
}
+ if (pstate->p_expr_kind == EXPR_KIND_GENERATED_COLUMN &&+ func_volatile(funcid) != PROVOLATILE_IMMUTABLE)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("cannot use function %s in column generation expression",+ func_signature_string(funcname, nargs, argnames, actual_arg_types)),+ errdetail("Functions used in a column generation expression must be immutable."),+ parser_errposition(pstate, location)));+
/*
* If there are default arguments, we have to include their types in
* actual_arg_types for the purpose of checking generic type consistency.
@@ -2370,6 +2379,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
case EXPR_KIND_CALL_ARGUMENT:
err = _("set-returning functions are not allowed in CALL arguments");
break;
+ case EXPR_KIND_GENERATED_COLUMN:+ err = _("set-returning functions are not allowed in column generation expressions");+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.cindex a6a2de94ea..b6f0e7d82c 100644--- a/src/backend/parser/parse_utilcmd.c+++ b/src/backend/parser/parse_utilcmd.c@@ -526,6 +526,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
bool saw_nullable;
bool saw_default;
bool saw_identity;
+ bool saw_generated;
ListCell *clist;
cxt->columns = lappend(cxt->columns, column);
@@ -633,6 +634,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
saw_nullable = false;
saw_default = false;
saw_identity = false;
+ saw_generated = false;
foreach(clist, column->constraints)
{
@@ -713,6 +715,50 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
break;
}
+ case CONSTR_GENERATED:+ if (cxt->ofType)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("generated colums are not supported on typed tables")));+ if (cxt->partbound)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("generated columns are not supported on partitions")));++ if (saw_generated)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("multiple generation clauses specified for column \"%s\" of table \"%s\"",+ column->colname, cxt->relation->relname),+ parser_errposition(cxt->pstate,+ constraint->location)));+ column->generated = constraint->generated_kind;+ column->raw_default = constraint->raw_expr;+ Assert(constraint->cooked_expr == NULL);+ saw_generated = true;++ /*+ * Prevent virtual generated columns from having a domain+ * type. We would have to enforce domain constraints when+ * columns underlying the generated column change. This could+ * possibly be implemented, but it's not.+ */+ if (column->generated == ATTRIBUTE_GENERATED_VIRTUAL)+ {+ Type ctype;++ ctype = typenameType(cxt->pstate, column->typeName, NULL);+ if (((Form_pg_type) GETSTRUCT(ctype))->typtype == TYPTYPE_DOMAIN)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("virtual generated column \"%s\" cannot have a domain type",+ column->colname),+ parser_errposition(cxt->pstate,+ column->location)));+ ReleaseSysCache(ctype);+ }+ break;+
case CONSTR_CHECK:
cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
break;
@@ -779,6 +825,50 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
++ if (saw_default && saw_generated)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("both default and generation expression specified for column \"%s\" of table \"%s\"",+ column->colname, cxt->relation->relname),+ parser_errposition(cxt->pstate,+ constraint->location)));++ if (saw_identity && saw_generated)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("both identity and generation expression specified for column \"%s\" of table \"%s\"",+ column->colname, cxt->relation->relname),+ parser_errposition(cxt->pstate,+ constraint->location)));++ /*+ * For a virtual generated column, convert the not-null constraint+ * into a full check constraint, so that the generation expression can+ * be expanded at check time.+ */+ if (column->is_not_null && column->generated == ATTRIBUTE_GENERATED_VIRTUAL)+ {+ Constraint *chk = makeNode(Constraint);+ NullTest *nt = makeNode(NullTest);+ ColumnRef *cr = makeNode(ColumnRef);++ cr->location = -1;+ cr->fields = list_make1(makeString(column->colname));++ nt->arg = (Expr *) cr;+ nt->nulltesttype = IS_NOT_NULL;+ nt->location = -1;++ chk->contype = CONSTR_CHECK;+ chk->location = -1;+ chk->initially_valid = true;+ chk->raw_expr = (Node *) nt;++ cxt->ckconstraints = lappend(cxt->ckconstraints, chk);++ column->is_not_null = false;+ }
}
/*
@@ -1008,11 +1098,13 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
* Copy default, if present and the default has been requested
*/
if (attribute->atthasdef &&
- (table_like_clause->options & CREATE_TABLE_LIKE_DEFAULTS))+ (table_like_clause->options & CREATE_TABLE_LIKE_DEFAULTS ||+ table_like_clause->options & CREATE_TABLE_LIKE_GENERATED))
{
Node *this_default = NULL;
AttrDefault *attrdef;
int i;
+ bool found_whole_row;
/* Find default in constraint structure */
Assert(constr != NULL);
@@ -1027,12 +1119,27 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
Assert(this_default != NULL);
+ def->cooked_default = map_variable_attnos(this_default,+ 1, 0,+ attmap, tupleDesc->natts,+ InvalidOid, &found_whole_row);+
/*
- * If default expr could contain any vars, we'd need to fix 'em,- * but it can't; so default is ready to apply to child.+ * Prevent this for the same reason as for constraints below.+ * Note that defaults cannot contain any vars, so it's OK that the+ * error message refers to generated columns.
*/
+ if (found_whole_row)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("cannot convert whole-row table reference"),+ errdetail("Generation expression for column \"%s\" contains a whole-row reference to table \"%s\".",+ attributeName,+ RelationGetRelationName(relation))));- def->cooked_default = this_default;+ if (attribute->attgenerated &&+ (table_like_clause->options & CREATE_TABLE_LIKE_GENERATED))+ def->generated = attribute->attgenerated;
}
/*
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.cindex 43815d26ff..467586d293 100644--- a/src/backend/rewrite/rewriteHandler.c+++ b/src/backend/rewrite/rewriteHandler.c@@ -20,6 +20,7 @@
*/
#include "postgres.h"
+#include "access/htup_details.h"
#include "access/sysattr.h"
#include "catalog/dependency.h"
#include "catalog/pg_type.h"
@@ -38,6 +39,7 @@
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
+#include "utils/syscache.h"
/* We use a list of these to detect recursion in RewriteQuery */
@@ -830,6 +832,13 @@ rewriteTargetListIU(List *targetList,
if (att_tup->attidentity == ATTRIBUTE_IDENTITY_BY_DEFAULT && override == OVERRIDING_USER_VALUE)
apply_default = true;
++ if (att_tup->attgenerated && !apply_default)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attname)),+ errdetail("Column \"%s\" is a generated column.",+ NameStr(att_tup->attname))));
}
if (commandType == CMD_UPDATE)
@@ -840,9 +849,24 @@ rewriteTargetListIU(List *targetList,
errmsg("column \"%s\" can only be updated to DEFAULT", NameStr(att_tup->attname)),
errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.",
NameStr(att_tup->attname))));
++ if (att_tup->attgenerated && new_tle && !apply_default)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("column \"%s\" can only be updated to DEFAULT", NameStr(att_tup->attname)),+ errdetail("Column \"%s\" is a generated column.",+ NameStr(att_tup->attname))));
}
- if (apply_default)+ if (att_tup->attgenerated)+ {+ /*+ * virtual generated column stores a null value; stored generated+ * column will be fixed in executor+ */+ new_tle = NULL;+ }+ else if (apply_default)
{
Node *new_expr;
@@ -1147,13 +1171,12 @@ build_column_default(Relation rel, int attrno)
}
}
- if (expr == NULL)- {- /*- * No per-column default, so look for a default for the type itself.- */+ /*+ * No per-column default, so look for a default for the type itself. But+ * not for generated columns.+ */+ if (expr == NULL && !att_tup->attgenerated)
expr = get_typdefault(atttype);
- }
if (expr == NULL)
return NULL; /* No default anywhere */
@@ -3676,6 +3699,103 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
}
+static Node *+expand_generated_columns_in_expr_mutator(Node *node, Relation rel)+{+ if (node == NULL)+ return NULL;++ if (IsA(node, Var))+ {+ Var *v = (Var *) node;+ AttrNumber attnum = v->varattno;++ if (attnum > 0 && TupleDescAttr(RelationGetDescr(rel), attnum - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)+ {+ node = build_column_default(rel, attnum);+ if (node == NULL)+ elog(ERROR, "no generation expression found for column number %d of table \"%s\"",+ attnum, RelationGetRelationName(rel));+ ChangeVarNodes(node, 1, v->varno, 0);+ }++ return node;+ }+ else+ return expression_tree_mutator(node, expand_generated_columns_in_expr_mutator, rel);+}+++Node *+expand_generated_columns_in_expr(Node *node, Relation rel)+{+ TupleDesc tupdesc = RelationGetDescr(rel);++ if (tupdesc->constr && tupdesc->constr->has_generated)+ return expression_tree_mutator(node,+ expand_generated_columns_in_expr_mutator,+ rel);+ else+ return node;+}++typedef struct+{+ /* list of range tables, innermost last */+ List *rtables;+} expand_generated_context;++static Node *+expand_generated_columns_in_query_mutator(Node *node, expand_generated_context *context)+{+ if (node == NULL)+ return NULL;++ if (IsA(node, Var))+ {+ Var *v = (Var *) node;+ Oid relid;+ AttrNumber attnum;+ List *rtable = list_nth_node(List,+ context->rtables,+ list_length(context->rtables) - v->varlevelsup - 1);++ relid = rt_fetch(v->varno, rtable)->relid;+ attnum = v->varattno;++ if (!relid || !attnum)+ return node;++ if (get_attgenerated(relid, attnum) == ATTRIBUTE_GENERATED_VIRTUAL)+ {+ Relation rt_entry_relation = heap_open(relid, NoLock);++ node = build_column_default(rt_entry_relation, attnum);+ ChangeVarNodes(node, 1, v->varno, v->varlevelsup);++ heap_close(rt_entry_relation, NoLock);+ }++ return node;+ }+ else if (IsA(node, Query))+ {+ Query *query = (Query *) node;+ Node *result;++ context->rtables = lappend(context->rtables, query->rtable);+ result = (Node *) query_tree_mutator(query,+ expand_generated_columns_in_query_mutator,+ context,+ QTW_DONT_COPY_QUERY);+ context->rtables = list_truncate(context->rtables, list_length(context->rtables) - 1);+ return result;+ }+ else+ return expression_tree_mutator(node, expand_generated_columns_in_query_mutator, context);+}++
/*
* QueryRewrite -
* Primary entry point to the query rewriter.
@@ -3731,6 +3851,24 @@ QueryRewrite(Query *parsetree)
/*
* Step 3
*
+ * Expand generated columns.+ */+ foreach(l, querylist)+ {+ Query *query = (Query *) lfirst(l);+ expand_generated_context context;++ context.rtables = list_make1(query->rtable);++ query = query_tree_mutator(query,+ expand_generated_columns_in_query_mutator,+ &context,+ QTW_DONT_COPY_QUERY);+ }++ /*+ * Step 4+ *
* Determine which, if any, of the resulting queries is supposed to set
* the command-result tag; and update the canSetTag fields accordingly.
*
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.cindex 892ddc0d48..d7db8aa5a3 100644--- a/src/backend/utils/cache/lsyscache.c+++ b/src/backend/utils/cache/lsyscache.c@@ -821,6 +821,39 @@ get_attnum(Oid relid, const char *attname)
return InvalidAttrNumber;
}
+/*+ * get_attgenerated+ *+ * Given the relation id and the attribute name,+ * return the "attgenerated" field from the attribute relation.+ *+ * Errors if not found.+ *+ * Since not generated is represented by '\0', this can also be used as a+ * Boolean test.+ */+char+get_attgenerated(Oid relid, AttrNumber attnum)+{+ HeapTuple tp;++ tp = SearchSysCache2(ATTNUM,+ ObjectIdGetDatum(relid),+ Int16GetDatum(attnum));+ if (HeapTupleIsValid(tp))+ {+ Form_pg_attribute att_tup = (Form_pg_attribute) GETSTRUCT(tp);+ char result;++ result = att_tup->attgenerated;+ ReleaseSysCache(tp);+ return result;+ }+ else+ elog(ERROR, "cache lookup failed for attribute %d of relation %u",+ attnum, relid);+}+
/*
* get_atttype
*
diff --git a/src/backend/utils/cache/partcache.c b/src/backend/utils/cache/partcache.cindex 5757301d05..31c7c27643 100644--- a/src/backend/utils/cache/partcache.c+++ b/src/backend/utils/cache/partcache.c@@ -28,6 +28,7 @@
#include "optimizer/clauses.h"
#include "optimizer/planner.h"
#include "partitioning/partbounds.h"
+#include "rewrite/rewriteHandler.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
@@ -133,6 +134,8 @@ RelationBuildPartitionKey(Relation relation)
expr = 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.cindex fd3d010b77..54b959cded 100644--- a/src/backend/utils/cache/relcache.c+++ b/src/backend/utils/cache/relcache.c@@ -510,6 +510,7 @@ RelationBuildTupleDesc(Relation relation)
constr = (TupleConstr *) MemoryContextAlloc(CacheMemoryContext,
sizeof(TupleConstr));
constr->has_not_null = false;
+ constr->has_generated = false;
/*
* Form a scan key that selects only user attributes (attnum > 0).
@@ -562,6 +563,8 @@ RelationBuildTupleDesc(Relation relation)
/* Update constraint/default info */
if (attp->attnotnull)
constr->has_not_null = true;
+ if (attp->attgenerated)+ constr->has_generated = true;
/* If the column has a default, fill it into the attrdef array */
if (attp->atthasdef)
@@ -3187,6 +3190,7 @@ RelationBuildLocalRelation(const char *relname,
Form_pg_attribute datt = TupleDescAttr(rel->rd_att, i);
datt->attidentity = satt->attidentity;
+ datt->attgenerated = satt->attgenerated;
datt->attnotnull = satt->attnotnull;
has_not_null |= satt->attnotnull;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.cindex c8d01ed4a4..7d6c7d3b20 100644--- a/src/bin/pg_dump/pg_dump.c+++ b/src/bin/pg_dump/pg_dump.c@@ -1987,6 +1987,11 @@ dumpTableData_insert(Archive *fout, void *dcontext)
{
if (field > 0)
archputs(", ", fout);
+ if (tbinfo->attgenerated[field])+ {+ archputs("DEFAULT", fout);+ continue;+ }
if (PQgetisnull(res, tuple, field))
{
archputs("NULL", fout);
@@ -8135,6 +8140,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
int i_attnotnull;
int i_atthasdef;
int i_attidentity;
+ int i_attgenerated;
int i_attisdropped;
int i_attlen;
int i_attalign;
@@ -8188,6 +8194,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
"a.attislocal,\n"
"pg_catalog.format_type(t.oid, a.atttypmod) AS atttypname,\n");
+ if (fout->remoteVersion >= 120000)+ appendPQExpBuffer(q,+ "a.attgenerated,\n");+ else+ appendPQExpBuffer(q,+ "'' AS attgenerated,\n");+
if (fout->remoteVersion >= 110000)
appendPQExpBuffer(q,
"CASE WHEN a.atthasmissing AND NOT a.attisdropped "
@@ -8260,6 +8273,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
i_attnotnull = PQfnumber(res, "attnotnull");
i_atthasdef = PQfnumber(res, "atthasdef");
i_attidentity = PQfnumber(res, "attidentity");
+ i_attgenerated = PQfnumber(res, "attgenerated");
i_attisdropped = PQfnumber(res, "attisdropped");
i_attlen = PQfnumber(res, "attlen");
i_attalign = PQfnumber(res, "attalign");
@@ -8277,6 +8291,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attstorage = (char *) pg_malloc(ntups * sizeof(char));
tbinfo->typstorage = (char *) pg_malloc(ntups * sizeof(char));
tbinfo->attidentity = (char *) pg_malloc(ntups * sizeof(char));
+ tbinfo->attgenerated = (char *) pg_malloc(ntups * sizeof(char));
tbinfo->attisdropped = (bool *) pg_malloc(ntups * sizeof(bool));
tbinfo->attlen = (int *) pg_malloc(ntups * sizeof(int));
tbinfo->attalign = (char *) pg_malloc(ntups * sizeof(char));
@@ -8303,6 +8318,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage));
tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage));
tbinfo->attidentity[j] = *(PQgetvalue(res, j, i_attidentity));
+ tbinfo->attgenerated[j] = *(PQgetvalue(res, j, i_attgenerated));
tbinfo->needs_override = tbinfo->needs_override || (tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS);
tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't');
tbinfo->attlen[j] = atoi(PQgetvalue(res, j, i_attlen));
@@ -15578,6 +15594,23 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
tbinfo->atttypnames[j]);
}
+ if (has_default)+ {+ if (tbinfo->attgenerated[j] == ATTRIBUTE_GENERATED_STORED)+ appendPQExpBuffer(q, " GENERATED ALWAYS AS (%s) STORED",+ tbinfo->attrdefs[j]->adef_expr);+ else if (tbinfo->attgenerated[j] == ATTRIBUTE_GENERATED_VIRTUAL)+ appendPQExpBuffer(q, " GENERATED ALWAYS AS (%s)",+ tbinfo->attrdefs[j]->adef_expr);+ else+ appendPQExpBuffer(q, " DEFAULT %s",+ tbinfo->attrdefs[j]->adef_expr);+ }+++ if (has_notnull)+ appendPQExpBufferStr(q, " NOT NULL");+
/* Add collation if not default for the type */
if (OidIsValid(tbinfo->attcollation[j]))
{
@@ -15588,13 +15621,6 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBuffer(q, " COLLATE %s",
fmtQualifiedDumpable(coll));
}
-- if (has_default)- appendPQExpBuffer(q, " DEFAULT %s",- tbinfo->attrdefs[j]->adef_expr);-- if (has_notnull)- appendPQExpBufferStr(q, " NOT NULL");
}
}
@@ -18141,6 +18167,7 @@ fmtCopyColumnList(const TableInfo *ti, PQExpBuffer buffer)
int numatts = ti->numatts;
char **attnames = ti->attnames;
bool *attisdropped = ti->attisdropped;
+ char *attgenerated = ti->attgenerated;
bool needComma;
int i;
@@ -18150,6 +18177,8 @@ fmtCopyColumnList(const TableInfo *ti, PQExpBuffer buffer)
{
if (attisdropped[i])
continue;
+ if (attgenerated[i])+ continue;
if (needComma)
appendPQExpBufferStr(buffer, ", ");
appendPQExpBufferStr(buffer, fmtId(attnames[i]));
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.hindex 685ad78669..d8c1c9927e 100644--- a/src/bin/pg_dump/pg_dump.h+++ b/src/bin/pg_dump/pg_dump.h@@ -310,6 +310,7 @@ typedef struct _tableInfo
char *typstorage; /* type storage scheme */
bool *attisdropped; /* true if attr is dropped; don't dump it */
char *attidentity;
+ char *attgenerated;
int *attlen; /* attribute length, used by binary_upgrade */
char *attalign; /* attribute align, used by binary_upgrade */
bool *attislocal; /* true if attr has local definition */
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.cindex a1d3ced318..b6f2a92396 100644--- a/src/bin/pg_dump/pg_dump_sort.c+++ b/src/bin/pg_dump/pg_dump_sort.c@@ -1102,6 +1102,16 @@ repairDependencyLoop(DumpableObject **loop,
}
}
+ /* Loop of table with itself, happens with generated columns */+ if (nLoop == 1)+ {+ if (loop[0]->objType == DO_TABLE)+ {+ removeObjectDependency(loop[0], loop[0]->dumpId);+ return;+ }+ }+
/*
* If all the objects are TABLE_DATA items, what we must have is a
* circular set of foreign key constraints (or a single self-referential
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.plindex ec751a7c23..1dd859f1c5 100644--- a/src/bin/pg_dump/t/002_pg_dump.pl+++ b/src/bin/pg_dump/t/002_pg_dump.pl@@ -2420,6 +2420,23 @@
unlike => { exclude_dump_test_schema => 1, },
},
+ 'CREATE TABLE test_table_generated' => {+ create_order => 3,+ create_sql => 'CREATE TABLE dump_test.test_table_generated (+ col1 int primary key,+ col2 int generated always as (col1 * 2)+ );',+ regexp => qr/^+ \QCREATE TABLE dump_test.test_table_generated (\E\n+ \s+\Qcol1 integer NOT NULL,\E\n+ \s+\Qcol2 integer GENERATED ALWAYS AS ((col1 * 2))\E\n+ \);+ /xms,+ like =>+ { %full_runs, %dump_test_schema_runs, section_pre_data => 1, },+ unlike => { exclude_dump_test_schema => 1, },+ },+
'CREATE STATISTICS extended_stats_no_options' => {
create_order => 97,
create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_no_options
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.cindex 4ca0db1d0c..bbdc4b9fa2 100644--- a/src/bin/psql/describe.c+++ b/src/bin/psql/describe.c@@ -1462,6 +1462,7 @@ describeOneTableDetails(const char *schemaname,
attnotnull_col = -1,
attcoll_col = -1,
attidentity_col = -1,
+ attgenerated_col = -1,
isindexkey_col = -1,
indexdef_col = -1,
fdwopts_col = -1,
@@ -1789,8 +1790,9 @@ describeOneTableDetails(const char *schemaname,
if (show_column_details)
{
+ /* use "pretty" mode for expression to avoid excessive parentheses */
appendPQExpBufferStr(&buf,
- ",\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"+ ",\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128)"
"\n FROM pg_catalog.pg_attrdef d"
"\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)"
",\n a.attnotnull");
@@ -1807,6 +1809,11 @@ describeOneTableDetails(const char *schemaname,
else
appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attidentity");
attidentity_col = cols++;
+ if (pset.sversion >= 120000)+ appendPQExpBufferStr(&buf, ",\n a.attgenerated");+ else+ appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attgenerated");+ attgenerated_col = cols++;
}
if (tableinfo.relkind == RELKIND_INDEX ||
tableinfo.relkind == RELKIND_PARTITIONED_INDEX)
@@ -1980,6 +1987,7 @@ describeOneTableDetails(const char *schemaname,
if (show_column_details)
{
char *identity;
+ char *generated;
char *default_str = "";
printTableAddCell(&cont, PQgetvalue(res, i, attcoll_col), false, false);
@@ -1989,16 +1997,21 @@ describeOneTableDetails(const char *schemaname,
false, false);
identity = PQgetvalue(res, i, attidentity_col);
+ generated = PQgetvalue(res, i, attgenerated_col);- if (!identity[0])- /* (note: above we cut off the 'default' string at 128) */- default_str = PQgetvalue(res, i, attrdef_col);- else if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS)+ if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS)
default_str = "generated always as identity";
else if (identity[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT)
default_str = "generated by default as identity";
+ else if (generated[0] == ATTRIBUTE_GENERATED_STORED)+ default_str = psprintf("generated always as (%s) stored", PQgetvalue(res, i, attrdef_col));+ else if (generated[0] == ATTRIBUTE_GENERATED_VIRTUAL)+ default_str = psprintf("generated always as (%s)", PQgetvalue(res, i, attrdef_col));+ else+ /* (note: above we cut off the 'default' string at 128) */+ default_str = PQgetvalue(res, i, attrdef_col);- printTableAddCell(&cont, default_str, false, false);+ printTableAddCell(&cont, default_str, false, generated[0] ? true : false);
}
/* Info for index columns */
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.hindex 708160f645..7a01d4ea50 100644--- a/src/include/access/tupdesc.h+++ b/src/include/access/tupdesc.h@@ -44,6 +44,7 @@ typedef struct tupleConstr
uint16 num_defval;
uint16 num_check;
bool has_not_null;
+ bool has_generated;
} TupleConstr;
/*
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.hindex 39f04b06ee..8be9423804 100644--- a/src/include/catalog/heap.h+++ b/src/include/catalog/heap.h@@ -116,7 +116,8 @@ extern Node *cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
- const char *attname);+ const char *attname,+ char attgenerated);
extern void DeleteRelationTuple(Oid relid);
extern void DeleteAttributeTuples(Oid relid);
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.hindex dc36753ede..cd42972c47 100644--- a/src/include/catalog/pg_attribute.h+++ b/src/include/catalog/pg_attribute.h@@ -140,6 +140,9 @@ CATALOG(pg_attribute,1249,AttributeRelationId) BKI_BOOTSTRAP BKI_WITHOUT_OIDS BK
/* One of the ATTRIBUTE_IDENTITY_* constants below, or '\0' */
char attidentity BKI_DEFAULT('\0');
+ /* One of the ATTRIBUTE_GENERATED_* constants below, or '\0' */+ char attgenerated BKI_DEFAULT('\0');+
/* Is dropped (ie, logically invisible) or not */
bool attisdropped BKI_DEFAULT(f);
@@ -201,6 +204,9 @@ typedef FormData_pg_attribute *Form_pg_attribute;
#define ATTRIBUTE_IDENTITY_ALWAYS 'a'
#define ATTRIBUTE_IDENTITY_BY_DEFAULT 'd'
+#define ATTRIBUTE_GENERATED_STORED 's'+#define ATTRIBUTE_GENERATED_VIRTUAL 'v'+
#endif /* EXPOSE_TO_CLIENT_CODE */
#endif /* PG_ATTRIBUTE_H */
diff --git a/src/include/catalog/pg_class.dat b/src/include/catalog/pg_class.datindex 9fffdef379..49643b934e 100644--- a/src/include/catalog/pg_class.dat+++ b/src/include/catalog/pg_class.dat@@ -36,7 +36,7 @@
reloftype => '0', relowner => 'PGUID', relam => '0', relfilenode => '0',
reltablespace => '0', relpages => '0', reltuples => '0', relallvisible => '0',
reltoastrelid => '0', relhasindex => 'f', relisshared => 'f',
- relpersistence => 'p', relkind => 'r', relnatts => '24', relchecks => '0',+ relpersistence => 'p', relkind => 'r', relnatts => '25', relchecks => '0',
relhasoids => 'f', relhasrules => 'f', relhastriggers => 'f',
relhassubclass => 'f', relrowsecurity => 'f', relforcerowsecurity => 'f',
relispopulated => 't', relreplident => 'n', relispartition => 'f',
diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.hindex 0d7e579e1c..64ce9ecaf0 100644--- a/src/include/executor/nodeModifyTable.h+++ b/src/include/executor/nodeModifyTable.h@@ -15,6 +15,8 @@
#include "nodes/execnodes.h"
+extern HeapTuple ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot);+
extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags);
extern void ExecEndModifyTable(ModifyTableState *node);
extern void ExecReScanModifyTable(ModifyTableState *node);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.hindex 880a03e4e4..ce4eb04c74 100644--- a/src/include/nodes/execnodes.h+++ b/src/include/nodes/execnodes.h@@ -445,6 +445,9 @@ typedef struct ResultRelInfo
/* array of constraint-checking expr states */
ExprState **ri_ConstraintExprs;
+ /* array of stored generated columns expr states */+ ExprState **ri_GeneratedExprs;+
/* for removing junk attributes from tuples */
JunkFilter *ri_junkFilter;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.hindex aa4a0dba2a..a56291b961 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -656,6 +656,7 @@ typedef struct ColumnDef
char identity; /* attidentity setting */
RangeVar *identitySequence; /* to store identity sequence name for
* ALTER TABLE ... ADD COLUMN */
+ char generated; /* attgenerated setting */
CollateClause *collClause; /* untransformed COLLATE spec, if any */
Oid collOid; /* collation OID (InvalidOid if not set) */
List *constraints; /* other constraints on column */
@@ -678,10 +679,11 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_COMMENTS = 1 << 0,
CREATE_TABLE_LIKE_CONSTRAINTS = 1 << 1,
CREATE_TABLE_LIKE_DEFAULTS = 1 << 2,
- CREATE_TABLE_LIKE_IDENTITY = 1 << 3,- CREATE_TABLE_LIKE_INDEXES = 1 << 4,- CREATE_TABLE_LIKE_STATISTICS = 1 << 5,- CREATE_TABLE_LIKE_STORAGE = 1 << 6,+ CREATE_TABLE_LIKE_GENERATED = 1 << 3,+ CREATE_TABLE_LIKE_IDENTITY = 1 << 4,+ CREATE_TABLE_LIKE_INDEXES = 1 << 5,+ CREATE_TABLE_LIKE_STATISTICS = 1 << 6,+ CREATE_TABLE_LIKE_STORAGE = 1 << 7,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -2076,6 +2078,7 @@ typedef enum ConstrType /* types of constraints */
CONSTR_NOTNULL,
CONSTR_DEFAULT,
CONSTR_IDENTITY,
+ CONSTR_GENERATED,
CONSTR_CHECK,
CONSTR_PRIMARY,
CONSTR_UNIQUE,
@@ -2114,7 +2117,8 @@ typedef struct Constraint
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
char *cooked_expr; /* expr, as nodeToString representation */
- char generated_when;+ char generated_when; /* ALWAYS or BY DEFAULT */+ char generated_kind; /* STORED or VIRTUAL */
/* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */
List *keys; /* String nodes naming referenced key
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.hindex 23db40147b..c6e5a1cbb8 100644--- a/src/include/parser/kwlist.h+++ b/src/include/parser/kwlist.h@@ -384,6 +384,7 @@ PG_KEYWORD("statistics", STATISTICS, UNRESERVED_KEYWORD)
PG_KEYWORD("stdin", STDIN, UNRESERVED_KEYWORD)
PG_KEYWORD("stdout", STDOUT, UNRESERVED_KEYWORD)
PG_KEYWORD("storage", STORAGE, UNRESERVED_KEYWORD)
+PG_KEYWORD("stored", STORED, UNRESERVED_KEYWORD)
PG_KEYWORD("strict", STRICT_P, UNRESERVED_KEYWORD)
PG_KEYWORD("strip", STRIP_P, UNRESERVED_KEYWORD)
PG_KEYWORD("subscription", SUBSCRIPTION, UNRESERVED_KEYWORD)
@@ -440,6 +441,7 @@ PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD)
PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD)
+PG_KEYWORD("virtual", VIRTUAL, UNRESERVED_KEYWORD)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD)
PG_KEYWORD("when", WHEN, RESERVED_KEYWORD)
PG_KEYWORD("where", WHERE, RESERVED_KEYWORD)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.hindex 0230543810..3248d93297 100644--- a/src/include/parser/parse_node.h+++ b/src/include/parser/parse_node.h@@ -69,7 +69,8 @@ typedef enum ParseExprKind
EXPR_KIND_TRIGGER_WHEN, /* WHEN condition in CREATE TRIGGER */
EXPR_KIND_POLICY, /* USING or WITH CHECK expr in policy */
EXPR_KIND_PARTITION_EXPRESSION, /* PARTITION BY expression */
- EXPR_KIND_CALL_ARGUMENT /* procedure argument in CALL */+ EXPR_KIND_CALL_ARGUMENT, /* procedure argument in CALL */+ EXPR_KIND_GENERATED_COLUMN, /* generation expression for a column */
} ParseExprKind;
diff --git a/src/include/rewrite/rewriteHandler.h b/src/include/rewrite/rewriteHandler.hindex 8128199fc3..f8017e423a 100644--- a/src/include/rewrite/rewriteHandler.h+++ b/src/include/rewrite/rewriteHandler.h@@ -32,5 +32,6 @@ extern const char *view_query_is_auto_updatable(Query *viewquery,
extern int relation_is_updatable(Oid reloid,
bool include_triggers,
Bitmapset *include_cols);
+extern Node *expand_generated_columns_in_expr(Node *node, Relation rel);
#endif /* REWRITEHANDLER_H */
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.hindex ff1705ad2b..cc96a61158 100644--- a/src/include/utils/lsyscache.h+++ b/src/include/utils/lsyscache.h@@ -85,6 +85,7 @@ extern Oid get_opfamily_proc(Oid opfamily, Oid lefttype, Oid righttype,
int16 procnum);
extern char *get_attname(Oid relid, AttrNumber attnum, bool missing_ok);
extern AttrNumber get_attnum(Oid relid, const char *attname);
+extern char get_attgenerated(Oid relid, AttrNumber attnum);
extern Oid get_atttype(Oid relid, AttrNumber attnum);
extern void get_atttypetypmodcoll(Oid relid, AttrNumber attnum,
Oid *typid, int32 *typmod, Oid *collid);
diff --git a/src/pl/plperl/expected/plperl_trigger.out b/src/pl/plperl/expected/plperl_trigger.outindex 28011cd9f6..a545506ec6 100644--- a/src/pl/plperl/expected/plperl_trigger.out+++ b/src/pl/plperl/expected/plperl_trigger.out@@ -6,6 +6,8 @@ CREATE TABLE trigger_test (
v varchar,
foo rowcompnest
);
+CREATE TABLE trigger_test_generated+ (i int, j int GENERATED ALWAYS AS (i * 2));
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plperl AS $$
# make sure keys are sorted for consistent results - perl no longer
@@ -98,6 +100,79 @@ NOTICE: $_TD->{table_name} = 'trigger_test'
NOTICE: $_TD->{table_schema} = 'public'
NOTICE: $_TD->{when} = 'BEFORE'
DROP TRIGGER show_trigger_data_trig on trigger_test;
+CREATE TRIGGER show_trigger_data_trig_before+BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();+CREATE TRIGGER show_trigger_data_trig_after+AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();+insert into trigger_test_generated (i) values (1);+NOTICE: $_TD->{argc} = '0'+NOTICE: $_TD->{event} = 'INSERT'+NOTICE: $_TD->{level} = 'ROW'+NOTICE: $_TD->{name} = 'show_trigger_data_trig_before'+NOTICE: $_TD->{new} = {'i' => '1'}+NOTICE: $_TD->{relid} = 'bogus:12345'+NOTICE: $_TD->{relname} = 'trigger_test_generated'+NOTICE: $_TD->{table_name} = 'trigger_test_generated'+NOTICE: $_TD->{table_schema} = 'public'+NOTICE: $_TD->{when} = 'BEFORE'+NOTICE: $_TD->{argc} = '0'+NOTICE: $_TD->{event} = 'INSERT'+NOTICE: $_TD->{level} = 'ROW'+NOTICE: $_TD->{name} = 'show_trigger_data_trig_after'+NOTICE: $_TD->{new} = {'i' => '1'}+NOTICE: $_TD->{relid} = 'bogus:12345'+NOTICE: $_TD->{relname} = 'trigger_test_generated'+NOTICE: $_TD->{table_name} = 'trigger_test_generated'+NOTICE: $_TD->{table_schema} = 'public'+NOTICE: $_TD->{when} = 'AFTER'+update trigger_test_generated set i = 11 where i = 1;+NOTICE: $_TD->{argc} = '0'+NOTICE: $_TD->{event} = 'UPDATE'+NOTICE: $_TD->{level} = 'ROW'+NOTICE: $_TD->{name} = 'show_trigger_data_trig_before'+NOTICE: $_TD->{new} = {'i' => '11'}+NOTICE: $_TD->{old} = {'i' => '1'}+NOTICE: $_TD->{relid} = 'bogus:12345'+NOTICE: $_TD->{relname} = 'trigger_test_generated'+NOTICE: $_TD->{table_name} = 'trigger_test_generated'+NOTICE: $_TD->{table_schema} = 'public'+NOTICE: $_TD->{when} = 'BEFORE'+NOTICE: $_TD->{argc} = '0'+NOTICE: $_TD->{event} = 'UPDATE'+NOTICE: $_TD->{level} = 'ROW'+NOTICE: $_TD->{name} = 'show_trigger_data_trig_after'+NOTICE: $_TD->{new} = {'i' => '11'}+NOTICE: $_TD->{old} = {'i' => '1'}+NOTICE: $_TD->{relid} = 'bogus:12345'+NOTICE: $_TD->{relname} = 'trigger_test_generated'+NOTICE: $_TD->{table_name} = 'trigger_test_generated'+NOTICE: $_TD->{table_schema} = 'public'+NOTICE: $_TD->{when} = 'AFTER'+delete from trigger_test_generated;+NOTICE: $_TD->{argc} = '0'+NOTICE: $_TD->{event} = 'DELETE'+NOTICE: $_TD->{level} = 'ROW'+NOTICE: $_TD->{name} = 'show_trigger_data_trig_before'+NOTICE: $_TD->{old} = {'i' => '11'}+NOTICE: $_TD->{relid} = 'bogus:12345'+NOTICE: $_TD->{relname} = 'trigger_test_generated'+NOTICE: $_TD->{table_name} = 'trigger_test_generated'+NOTICE: $_TD->{table_schema} = 'public'+NOTICE: $_TD->{when} = 'BEFORE'+NOTICE: $_TD->{argc} = '0'+NOTICE: $_TD->{event} = 'DELETE'+NOTICE: $_TD->{level} = 'ROW'+NOTICE: $_TD->{name} = 'show_trigger_data_trig_after'+NOTICE: $_TD->{old} = {'i' => '11'}+NOTICE: $_TD->{relid} = 'bogus:12345'+NOTICE: $_TD->{relname} = 'trigger_test_generated'+NOTICE: $_TD->{table_name} = 'trigger_test_generated'+NOTICE: $_TD->{table_schema} = 'public'+NOTICE: $_TD->{when} = 'AFTER'+DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated;+DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated;
insert into trigger_test values(1,'insert', '("(1)")');
CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test;
CREATE TRIGGER show_trigger_data_trig
@@ -295,3 +370,21 @@ NOTICE: perlsnitch: ddl_command_start DROP TABLE
NOTICE: perlsnitch: ddl_command_end DROP TABLE
drop event trigger perl_a_snitch;
drop event trigger perl_b_snitch;
+-- dealing with generated columns+CREATE FUNCTION generated_test_func1() RETURNS trigger+LANGUAGE plperl+AS $$+$_TD->{new}{j} = 5; # not allowed+return 'MODIFY';+$$;+CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE generated_test_func1();+TRUNCATE trigger_test_generated;+INSERT INTO trigger_test_generated (i) VALUES (1);+ERROR: cannot set generated column "j"+CONTEXT: PL/Perl function "generated_test_func1"+SELECT * FROM trigger_test_generated;+ i | j +---+---+(0 rows)+diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.cindex 4cfc506253..cb4f96aa2a 100644--- a/src/pl/plperl/plperl.c+++ b/src/pl/plperl/plperl.c@@ -1791,6 +1791,11 @@ plperl_modify_tuple(HV *hvTD, TriggerData *tdata, HeapTuple otup)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot set system attribute \"%s\"",
key)));
+ if (attr->attgenerated)+ ereport(ERROR,+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),+ errmsg("cannot set generated column \"%s\"",+ key)));
modvalues[attn - 1] = plperl_sv_to_datum(val,
attr->atttypid,
@@ -3041,7 +3046,7 @@ plperl_hash_from_tuple(HeapTuple tuple, TupleDesc tupdesc)
Oid typoutput;
Form_pg_attribute att = TupleDescAttr(tupdesc, i);
- if (att->attisdropped)+ if (att->attisdropped || 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.sqlindex 624193b9d0..a89545afd8 100644--- a/src/pl/plperl/sql/plperl_trigger.sql+++ b/src/pl/plperl/sql/plperl_trigger.sql@@ -8,6 +8,9 @@ CREATE TABLE trigger_test (
foo rowcompnest
);
+CREATE TABLE trigger_test_generated+ (i int, j int GENERATED ALWAYS AS (i * 2));+
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plperl AS $$
# make sure keys are sorted for consistent results - perl no longer
@@ -70,6 +73,21 @@ CREATE TRIGGER show_trigger_data_trig
DROP TRIGGER show_trigger_data_trig on trigger_test;
+CREATE TRIGGER show_trigger_data_trig_before+BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();++CREATE TRIGGER show_trigger_data_trig_after+AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();++insert into trigger_test_generated (i) values (1);+update trigger_test_generated set i = 11 where i = 1;+delete from trigger_test_generated;++DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated;+DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated;+
insert into trigger_test values(1,'insert', '("(1)")');
CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test;
@@ -221,3 +239,19 @@ CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
drop event trigger perl_a_snitch;
drop event trigger perl_b_snitch;
++-- dealing with generated columns++CREATE FUNCTION generated_test_func1() RETURNS trigger+LANGUAGE plperl+AS $$+$_TD->{new}{j} = 5; # not allowed+return 'MODIFY';+$$;++CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE generated_test_func1();++TRUNCATE trigger_test_generated;+INSERT INTO trigger_test_generated (i) VALUES (1);+SELECT * FROM trigger_test_generated;diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpython/expected/plpython_trigger.outindex d7ab8ac6b8..8f17858e90 100644--- a/src/pl/plpython/expected/plpython_trigger.out+++ b/src/pl/plpython/expected/plpython_trigger.out@@ -67,6 +67,8 @@ SELECT * FROM users;
-- dump trigger data
CREATE TABLE trigger_test
(i int, v text );
+CREATE TABLE trigger_test_generated+ (i int, j int GENERATED ALWAYS AS (i * 2));
CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpythonu AS $$
if 'relid' in TD:
@@ -203,6 +205,77 @@ NOTICE: TD[when] => BEFORE
DROP TRIGGER show_trigger_data_trig_stmt on trigger_test;
DROP TRIGGER show_trigger_data_trig_before on trigger_test;
DROP TRIGGER show_trigger_data_trig_after on trigger_test;
+CREATE TRIGGER show_trigger_data_trig_before+BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();+CREATE TRIGGER show_trigger_data_trig_after+AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();+insert into trigger_test_generated (i) values (1);+NOTICE: TD[args] => None+NOTICE: TD[event] => INSERT+NOTICE: TD[level] => ROW+NOTICE: TD[name] => show_trigger_data_trig_before+NOTICE: TD[new] => {'i': 1}+NOTICE: TD[old] => None+NOTICE: TD[relid] => bogus:12345+NOTICE: TD[table_name] => trigger_test_generated+NOTICE: TD[table_schema] => public+NOTICE: TD[when] => BEFORE+NOTICE: TD[args] => None+NOTICE: TD[event] => INSERT+NOTICE: TD[level] => ROW+NOTICE: TD[name] => show_trigger_data_trig_after+NOTICE: TD[new] => {'i': 1}+NOTICE: TD[old] => None+NOTICE: TD[relid] => bogus:12345+NOTICE: TD[table_name] => trigger_test_generated+NOTICE: TD[table_schema] => public+NOTICE: TD[when] => AFTER+update trigger_test_generated set i = 11 where i = 1;+NOTICE: TD[args] => None+NOTICE: TD[event] => UPDATE+NOTICE: TD[level] => ROW+NOTICE: TD[name] => show_trigger_data_trig_before+NOTICE: TD[new] => {'i': 11}+NOTICE: TD[old] => {'i': 1}+NOTICE: TD[relid] => bogus:12345+NOTICE: TD[table_name] => trigger_test_generated+NOTICE: TD[table_schema] => public+NOTICE: TD[when] => BEFORE+NOTICE: TD[args] => None+NOTICE: TD[event] => UPDATE+NOTICE: TD[level] => ROW+NOTICE: TD[name] => show_trigger_data_trig_after+NOTICE: TD[new] => {'i': 11}+NOTICE: TD[old] => {'i': 1}+NOTICE: TD[relid] => bogus:12345+NOTICE: TD[table_name] => trigger_test_generated+NOTICE: TD[table_schema] => public+NOTICE: TD[when] => AFTER+delete from trigger_test_generated;+NOTICE: TD[args] => None+NOTICE: TD[event] => DELETE+NOTICE: TD[level] => ROW+NOTICE: TD[name] => show_trigger_data_trig_before+NOTICE: TD[new] => None+NOTICE: TD[old] => {'i': 11}+NOTICE: TD[relid] => bogus:12345+NOTICE: TD[table_name] => trigger_test_generated+NOTICE: TD[table_schema] => public+NOTICE: TD[when] => BEFORE+NOTICE: TD[args] => None+NOTICE: TD[event] => DELETE+NOTICE: TD[level] => ROW+NOTICE: TD[name] => show_trigger_data_trig_after+NOTICE: TD[new] => None+NOTICE: TD[old] => {'i': 11}+NOTICE: TD[relid] => bogus:12345+NOTICE: TD[table_name] => trigger_test_generated+NOTICE: TD[table_schema] => public+NOTICE: TD[when] => AFTER+DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated;+DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated;
insert into trigger_test values(1,'insert');
CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test;
CREATE TRIGGER show_trigger_data_trig
@@ -524,3 +597,22 @@ INFO: old: 1 -> a
INFO: new: 1 -> b
DROP TABLE transition_table_test;
DROP FUNCTION transition_table_test_f();
+-- dealing with generated columns+CREATE FUNCTION generated_test_func1() RETURNS trigger+LANGUAGE plpythonu+AS $$+TD['new']['j'] = 5 # not allowed+return 'MODIFY'+$$;+CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE generated_test_func1();+TRUNCATE trigger_test_generated;+INSERT INTO trigger_test_generated (i) VALUES (1);+ERROR: cannot set generated column "j"+CONTEXT: while modifying trigger row+PL/Python function "generated_test_func1"+SELECT * FROM trigger_test_generated;+ i | j +---+---+(0 rows)+diff --git a/src/pl/plpython/plpy_exec.c b/src/pl/plpython/plpy_exec.cindex 47ed95dcc6..d9beb61cf2 100644--- a/src/pl/plpython/plpy_exec.c+++ b/src/pl/plpython/plpy_exec.c@@ -13,6 +13,7 @@
#include "executor/spi.h"
#include "funcapi.h"
#include "utils/builtins.h"
+#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/typcache.h"
@@ -952,6 +953,11 @@ PLy_modify_tuple(PLyProcedure *proc, PyObject *pltd, TriggerData *tdata,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot set system attribute \"%s\"",
plattstr)));
+ if (TupleDescAttr(tupdesc, attn - 1)->attgenerated)+ ereport(ERROR,+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),+ errmsg("cannot set generated column \"%s\"",+ plattstr)));
plval = PyDict_GetItem(plntup, platt);
if (plval == NULL)
diff --git a/src/pl/plpython/plpy_typeio.c b/src/pl/plpython/plpy_typeio.cindex d6a6a849c3..afd1a0a748 100644--- a/src/pl/plpython/plpy_typeio.c+++ b/src/pl/plpython/plpy_typeio.c@@ -839,7 +839,7 @@ PLyDict_FromTuple(PLyDatumToOb *arg, HeapTuple tuple, TupleDesc desc)
bool is_null;
PyObject *value;
- if (attr->attisdropped)+ if (attr->attisdropped || 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.sqlindex 79c24b714b..745e792bec 100644--- a/src/pl/plpython/sql/plpython_trigger.sql+++ b/src/pl/plpython/sql/plpython_trigger.sql@@ -67,6 +67,9 @@ CREATE TRIGGER users_delete_trig BEFORE DELETE ON users FOR EACH ROW
CREATE TABLE trigger_test
(i int, v text );
+CREATE TABLE trigger_test_generated+ (i int, j int GENERATED ALWAYS AS (i * 2));+
CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpythonu AS $$
if 'relid' in TD:
@@ -109,6 +112,21 @@ CREATE TRIGGER show_trigger_data_trig_stmt
DROP TRIGGER show_trigger_data_trig_before on trigger_test;
DROP TRIGGER show_trigger_data_trig_after on trigger_test;
+CREATE TRIGGER show_trigger_data_trig_before+BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();++CREATE TRIGGER show_trigger_data_trig_after+AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();++insert into trigger_test_generated (i) values (1);+update trigger_test_generated set i = 11 where i = 1;+delete from trigger_test_generated;++DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated;+DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated;+
insert into trigger_test values(1,'insert');
CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test;
@@ -430,3 +448,20 @@ CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
DROP TABLE transition_table_test;
DROP FUNCTION transition_table_test_f();
+++-- dealing with generated columns++CREATE FUNCTION generated_test_func1() RETURNS trigger+LANGUAGE plpythonu+AS $$+TD['new']['j'] = 5 # not allowed+return 'MODIFY'+$$;++CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE generated_test_func1();++TRUNCATE trigger_test_generated;+INSERT INTO trigger_test_generated (i) VALUES (1);+SELECT * FROM trigger_test_generated;diff --git a/src/pl/tcl/expected/pltcl_queries.out b/src/pl/tcl/expected/pltcl_queries.outindex 736671cc1b..1aac933b81 100644--- a/src/pl/tcl/expected/pltcl_queries.out+++ b/src/pl/tcl/expected/pltcl_queries.out@@ -207,6 +207,75 @@ NOTICE: TG_table_name: trigger_test
NOTICE: TG_table_schema: public
NOTICE: TG_when: BEFORE
NOTICE: args: {23 skidoo}
+insert into trigger_test_generated (i) values (1);+NOTICE: NEW: {i: 1}+NOTICE: OLD: {}+NOTICE: TG_level: ROW+NOTICE: TG_name: show_trigger_data_trig_before+NOTICE: TG_op: INSERT+NOTICE: TG_relatts: {{} i j}+NOTICE: TG_relid: bogus:12345+NOTICE: TG_table_name: trigger_test_generated+NOTICE: TG_table_schema: public+NOTICE: TG_when: BEFORE+NOTICE: args: {}+NOTICE: NEW: {i: 1}+NOTICE: OLD: {}+NOTICE: TG_level: ROW+NOTICE: TG_name: show_trigger_data_trig_after+NOTICE: TG_op: INSERT+NOTICE: TG_relatts: {{} i j}+NOTICE: TG_relid: bogus:12345+NOTICE: TG_table_name: trigger_test_generated+NOTICE: TG_table_schema: public+NOTICE: TG_when: AFTER+NOTICE: args: {}+update trigger_test_generated set i = 11 where i = 1;+NOTICE: NEW: {i: 11}+NOTICE: OLD: {i: 1}+NOTICE: TG_level: ROW+NOTICE: TG_name: show_trigger_data_trig_before+NOTICE: TG_op: UPDATE+NOTICE: TG_relatts: {{} i j}+NOTICE: TG_relid: bogus:12345+NOTICE: TG_table_name: trigger_test_generated+NOTICE: TG_table_schema: public+NOTICE: TG_when: BEFORE+NOTICE: args: {}+NOTICE: NEW: {i: 11}+NOTICE: OLD: {i: 1}+NOTICE: TG_level: ROW+NOTICE: TG_name: show_trigger_data_trig_after+NOTICE: TG_op: UPDATE+NOTICE: TG_relatts: {{} i j}+NOTICE: TG_relid: bogus:12345+NOTICE: TG_table_name: trigger_test_generated+NOTICE: TG_table_schema: public+NOTICE: TG_when: AFTER+NOTICE: args: {}+delete from trigger_test_generated;+NOTICE: NEW: {}+NOTICE: OLD: {i: 11}+NOTICE: TG_level: ROW+NOTICE: TG_name: show_trigger_data_trig_before+NOTICE: TG_op: DELETE+NOTICE: TG_relatts: {{} i j}+NOTICE: TG_relid: bogus:12345+NOTICE: TG_table_name: trigger_test_generated+NOTICE: TG_table_schema: public+NOTICE: TG_when: BEFORE+NOTICE: args: {}+NOTICE: NEW: {}+NOTICE: OLD: {i: 11}+NOTICE: TG_level: ROW+NOTICE: TG_name: show_trigger_data_trig_after+NOTICE: TG_op: DELETE+NOTICE: TG_relatts: {{} i j}+NOTICE: TG_relid: bogus:12345+NOTICE: TG_table_name: trigger_test_generated+NOTICE: TG_table_schema: public+NOTICE: TG_when: AFTER+NOTICE: args: {}
insert into trigger_test_view values(2,'insert');
NOTICE: NEW: {i: 2, v: insert}
NOTICE: OLD: {}
@@ -314,6 +383,8 @@ NOTICE: TG_table_name: trigger_test
NOTICE: TG_table_schema: public
NOTICE: TG_when: BEFORE
NOTICE: args: {42 {statement trigger}}
+DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated;+DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated;
-- Test composite-type arguments
select tcl_composite_arg_ref1(row('tkey', 42, 'ref2'));
tcl_composite_arg_ref1
@@ -775,3 +846,21 @@ INFO: old: 1 -> a
INFO: new: 1 -> b
drop table transition_table_test;
drop function transition_table_test_f();
+-- dealing with generated columns+CREATE FUNCTION generated_test_func1() RETURNS trigger+LANGUAGE pltcl+AS $$+# not allowed+set NEW(j) 5+return [array get NEW]+$$;+CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE generated_test_func1();+TRUNCATE trigger_test_generated;+INSERT INTO trigger_test_generated (i) VALUES (1);+ERROR: cannot set generated column "j"+SELECT * FROM trigger_test_generated;+ i | j +---+---+(0 rows)+diff --git a/src/pl/tcl/expected/pltcl_setup.out b/src/pl/tcl/expected/pltcl_setup.outindex f1958c3a98..910119e385 100644--- a/src/pl/tcl/expected/pltcl_setup.out+++ b/src/pl/tcl/expected/pltcl_setup.out@@ -59,6 +59,8 @@ CREATE TABLE trigger_test (
);
-- Make certain dropped attributes are handled correctly
ALTER TABLE trigger_test DROP dropme;
+CREATE TABLE trigger_test_generated+ (i int, j int GENERATED ALWAYS AS (i * 2));
CREATE VIEW trigger_test_view AS SELECT i, v FROM trigger_test;
CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$
if {$TG_table_name eq "trigger_test" && $TG_level eq "ROW" && $TG_op ne "DELETE"} {
@@ -110,6 +112,12 @@ FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
CREATE TRIGGER statement_trigger
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON trigger_test
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_data(42,'statement trigger');
+CREATE TRIGGER show_trigger_data_trig_before+BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();+CREATE TRIGGER show_trigger_data_trig_after+AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();
CREATE TRIGGER show_trigger_data_view_trig
INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view
FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view');
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.cindex e2fa43b890..c07fa82742 100644--- a/src/pl/tcl/pltcl.c+++ b/src/pl/tcl/pltcl.c@@ -3245,6 +3245,12 @@ pltcl_build_tuple_result(Tcl_Interp *interp, Tcl_Obj **kvObjv, int kvObjc,
errmsg("cannot set system attribute \"%s\"",
fieldName)));
+ if (TupleDescAttr(tupdesc, attn - 1)->attgenerated)+ ereport(ERROR,+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),+ errmsg("cannot set generated column \"%s\"",+ fieldName)));+
values[attn - 1] = utf_u2e(Tcl_GetString(kvObjv[i + 1]));
}
diff --git a/src/pl/tcl/sql/pltcl_queries.sql b/src/pl/tcl/sql/pltcl_queries.sqlindex 71c1238bd2..4a42853591 100644--- a/src/pl/tcl/sql/pltcl_queries.sql+++ b/src/pl/tcl/sql/pltcl_queries.sql@@ -76,6 +76,10 @@
-- show dump of trigger data
insert into trigger_test values(1,'insert');
+insert into trigger_test_generated (i) values (1);+update trigger_test_generated set i = 11 where i = 1;+delete from trigger_test_generated;+
insert into trigger_test_view values(2,'insert');
update trigger_test_view set v = 'update' where i=1;
delete from trigger_test_view;
@@ -85,6 +89,9 @@
delete from trigger_test;
truncate trigger_test;
+DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated;+DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated;+
-- Test composite-type arguments
select tcl_composite_arg_ref1(row('tkey', 42, 'ref2'));
select tcl_composite_arg_ref2(row('tkey', 42, 'ref2'));
@@ -279,3 +286,21 @@ CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
update transition_table_test set name = 'b';
drop table transition_table_test;
drop function transition_table_test_f();
+++-- dealing with generated columns++CREATE FUNCTION generated_test_func1() RETURNS trigger+LANGUAGE pltcl+AS $$+# not allowed+set NEW(j) 5+return [array get NEW]+$$;++CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE generated_test_func1();++TRUNCATE trigger_test_generated;+INSERT INTO trigger_test_generated (i) VALUES (1);+SELECT * FROM trigger_test_generated;diff --git a/src/pl/tcl/sql/pltcl_setup.sql b/src/pl/tcl/sql/pltcl_setup.sqlindex 56a90dc844..7e6ed699e3 100644--- a/src/pl/tcl/sql/pltcl_setup.sql+++ b/src/pl/tcl/sql/pltcl_setup.sql@@ -68,6 +68,9 @@ CREATE TABLE trigger_test (
-- Make certain dropped attributes are handled correctly
ALTER TABLE trigger_test DROP dropme;
+CREATE TABLE trigger_test_generated+ (i int, j int GENERATED ALWAYS AS (i * 2));+
CREATE VIEW trigger_test_view AS SELECT i, v FROM trigger_test;
CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$
@@ -122,6 +125,13 @@ CREATE TRIGGER statement_trigger
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON trigger_test
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_data(42,'statement trigger');
+CREATE TRIGGER show_trigger_data_trig_before+BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();+CREATE TRIGGER show_trigger_data_trig_after+AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated+FOR EACH ROW EXECUTE PROCEDURE trigger_data();+
CREATE TRIGGER show_trigger_data_view_trig
INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view
FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view');
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.outindex 8d4543bfe8..2006162694 100644--- a/src/test/regress/expected/create_table_like.out+++ b/src/test/regress/expected/create_table_like.out@@ -113,6 +113,52 @@ SELECT * FROM test_like_id_3; -- identity was copied and applied
(1 row)
DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
+CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2));+\d test_like_gen_1+ Table "public.test_like_gen_1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-----------------------------+ a | integer | | | + b | integer | | | generated always as (a * 2)++INSERT INTO test_like_gen_1 (a) VALUES (1);+SELECT * FROM test_like_gen_1;+ a | b +---+---+ 1 | 2+(1 row)++CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1);+\d test_like_gen_2+ Table "public.test_like_gen_2"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | | + b | integer | | | ++INSERT INTO test_like_gen_2 (a) VALUES (1);+SELECT * FROM test_like_gen_2;+ a | b +---+---+ 1 | +(1 row)++CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);+\d test_like_gen_3+ Table "public.test_like_gen_3"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-----------------------------+ a | integer | | | + b | integer | | | generated always as (a * 2)++INSERT INTO test_like_gen_3 (a) VALUES (1);+SELECT * FROM test_like_gen_3;+ a | b +---+---+ 1 | 2+(1 row)++DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.outnew file mode 100644index 0000000000..812a40d136--- /dev/null+++ b/src/test/regress/expected/generated.out@@ -0,0 +1,737 @@+-- sanity check of system catalog+SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');+ attrelid | attname | attgenerated +----------+---------+--------------+(0 rows)++CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55));+CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));+SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_name LIKE 'gtest_' ORDER BY 1, 2;+ table_name | column_name | column_default | is_nullable | is_generated | generation_expression +------------+-------------+----------------+-------------+--------------+-----------------------+ gtest0 | a | | NO | NEVER | + gtest0 | b | | YES | ALWAYS | 55+ gtest1 | a | | NO | NEVER | + gtest1 | b | | YES | ALWAYS | (a * 2)+(4 rows)++SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage ORDER BY 1, 2, 3;+ table_name | column_name | dependent_column +------------+-------------+------------------+ gtest1 | a | b+(1 row)++\d gtest1+ Table "public.gtest1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-----------------------------+ a | integer | | not null | + b | integer | | | generated always as (a * 2)+Indexes:+ "gtest1_pkey" PRIMARY KEY, btree (a)++-- duplicate generated+CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) GENERATED ALWAYS AS (a * 3));+ERROR: multiple generation clauses specified for column "b" of table "gtest_err_1"+LINE 1: ...nt PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) GENERATED ...+ ^+-- references to other generated columns, including self-references+CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2));+ERROR: cannot use generated column "b" in column generation expression+LINE 1: ...r_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2));+ ^+DETAIL: A generated column cannot reference another generated column.+CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2), c int GENERATED ALWAYS AS (b * 3));+ERROR: cannot use generated column "b" in column generation expression+LINE 1: ...RATED ALWAYS AS (a * 2), c int GENERATED ALWAYS AS (b * 3));+ ^+DETAIL: A generated column cannot reference another generated column.+-- invalid reference+CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2));+ERROR: column "c" does not exist+LINE 1: ...rr_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2));+ ^+-- functions must be immutable+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()));+ERROR: cannot use function random() in column generation expression+LINE 1: ...MARY KEY, b double precision GENERATED ALWAYS AS (random()))...+ ^+DETAIL: Functions used in a column generation expression must be immutable.+-- cannot have default/identity and generated+CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2));+ERROR: both default and generation expression specified for column "b" of table "gtest_err_5a"+LINE 1: ... gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ...+ ^+CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2));+ERROR: both identity and generation expression specified for column "b" of table "gtest_err_5b"+LINE 1: ...t PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ...+ ^+INSERT INTO gtest1 VALUES (1);+INSERT INTO gtest1 VALUES (2, DEFAULT);+INSERT INTO gtest1 VALUES (3, 33); -- error+ERROR: cannot insert into column "b"+DETAIL: Column "b" is a generated column.+SELECT * FROM gtest1 ORDER BY a;+ a | b +---+---+ 1 | 2+ 2 | 4+(2 rows)++UPDATE gtest1 SET b = DEFAULT WHERE a = 1;+UPDATE gtest1 SET b = 11 WHERE a = 1; -- error+ERROR: column "b" can only be updated to DEFAULT+DETAIL: Column "b" is a generated column.+SELECT * FROM gtest1 ORDER BY a;+ a | b +---+---+ 1 | 2+ 2 | 4+(2 rows)++SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a;+ a | b | b2 +---+---+----+ 1 | 2 | 4+ 2 | 4 | 8+(2 rows)++SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;+ a | b +---+---+ 2 | 4+(1 row)++-- test that overflow error happens on read+INSERT INTO gtest1 VALUES (2000000000);+SELECT * FROM gtest1;+ERROR: integer out of range+DELETE FROM gtest1 WHERE a = 2000000000;+-- test with joins+CREATE TABLE gtestx (x int, y int);+INSERT INTO gtestx VALUES (11, 1), (22, 2), (33, 3);+SELECT * FROM gtestx, gtest1 WHERE gtestx.y = gtest1.a;+ x | y | a | b +----+---+---+---+ 11 | 1 | 1 | 2+ 22 | 2 | 2 | 4+(2 rows)++DROP TABLE gtestx;+-- test UPDATE/DELETE quals+SELECT * FROM gtest1 ORDER BY a;+ a | b +---+---+ 1 | 2+ 2 | 4+(2 rows)++UPDATE gtest1 SET a = 3 WHERE b = 4;+SELECT * FROM gtest1 ORDER BY a;+ a | b +---+---+ 1 | 2+ 3 | 6+(2 rows)++DELETE FROM gtest1 WHERE b = 2;+SELECT * FROM gtest1 ORDER BY a;+ a | b +---+---+ 3 | 6+(1 row)++-- views+CREATE VIEW gtest1v AS SELECT * FROM gtest1;+SELECT * FROM gtest1v;+ a | b +---+---+ 3 | 6+(1 row)++INSERT INTO gtest1v VALUES (4, 8); -- fails+ERROR: cannot insert into column "b"+DETAIL: Column "b" is a generated column.+-- inheritance+CREATE TABLE gtest1_1 () INHERITS (gtest1);+SELECT * FROM gtest1_1;+ a | b +---+---+(0 rows)++\d gtest1_1+ Table "public.gtest1_1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-----------------------------+ a | integer | | not null | + b | integer | | | generated always as (a * 2)+Inherits: gtest1++INSERT INTO gtest1_1 VALUES (4);+SELECT * FROM gtest1_1;+ a | b +---+---+ 4 | 8+(1 row)++SELECT * FROM gtest1;+ a | b +---+---+ 3 | 6+ 4 | 8+(2 rows)++-- test inheritance mismatch+CREATE TABLE gtesty (x int, b int);+CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error+NOTICE: merging multiple inherited definitions of column "b"+ERROR: inherited column "b" has a generation conflict+DROP TABLE gtesty;+-- stored+CREATE TABLE gtest3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 3) STORED);+INSERT INTO gtest3 (a) VALUES (1), (2), (3);+SELECT * FROM gtest3 ORDER BY a;+ a | b +---+---+ 1 | 3+ 2 | 6+ 3 | 9+(3 rows)++UPDATE gtest3 SET a = 22 WHERE a = 2;+SELECT * FROM gtest3 ORDER BY a;+ a | b +----+----+ 1 | 3+ 3 | 9+ 22 | 66+(3 rows)++-- COPY+TRUNCATE gtest1;+INSERT INTO gtest1 (a) VALUES (1), (2);+COPY gtest1 TO stdout;+1+2+COPY gtest1 (a, b) TO stdout;+ERROR: column "b" is a generated column+DETAIL: Generated columns cannot be used in COPY.+COPY gtest1 FROM stdin;+COPY gtest1 (a, b) FROM stdin;+ERROR: column "b" is a generated column+DETAIL: Generated columns cannot be used in COPY.+SELECT * FROM gtest1 ORDER BY a;+ a | b +---+---+ 1 | 2+ 2 | 4+ 3 | 6+ 4 | 8+(4 rows)++TRUNCATE gtest3;+INSERT INTO gtest3 (a) VALUES (1), (2);+COPY gtest3 TO stdout;+1+2+COPY gtest3 (a, b) TO stdout;+ERROR: column "b" is a generated column+DETAIL: Generated columns cannot be used in COPY.+COPY gtest3 FROM stdin;+COPY gtest3 (a, b) FROM stdin;+ERROR: column "b" is a generated column+DETAIL: Generated columns cannot be used in COPY.+SELECT * FROM gtest3 ORDER BY a;+ a | b +---+----+ 1 | 3+ 2 | 6+ 3 | 9+ 4 | 12+(4 rows)++-- drop column behavior+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2));+ALTER TABLE gtest10 DROP COLUMN b;+\d gtest10+ Table "public.gtest10"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | not null | +Indexes:+ "gtest10_pkey" PRIMARY KEY, btree (a)++-- privileges+CREATE USER regress_user11;+CREATE TABLE gtest11v (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) VIRTUAL);+INSERT INTO gtest11v VALUES (1, 10), (2, 20);+GRANT SELECT (a, c) ON gtest11v TO regress_user11;+CREATE TABLE gtest11s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);+INSERT INTO gtest11s VALUES (1, 10), (2, 20);+GRANT SELECT (a, c) ON gtest11s TO regress_user11;+CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE SQL;+REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;+CREATE TABLE gtest12v (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) VIRTUAL);+INSERT INTO gtest12v VALUES (1, 10), (2, 20);+GRANT SELECT (a, c) ON gtest12v TO regress_user11;+CREATE TABLE gtest12s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);+INSERT INTO gtest12s VALUES (1, 10), (2, 20);+GRANT SELECT (a, c) ON gtest12s TO regress_user11;+SET ROLE regress_user11;+SELECT a, b FROM gtest11v; -- not allowed+ERROR: permission denied for table gtest11v+SELECT a, c FROM gtest11v; -- allowed+ a | c +---+----+ 1 | 20+ 2 | 40+(2 rows)++SELECT a, b FROM gtest11s; -- not allowed+ERROR: permission denied for table gtest11s+SELECT a, c FROM gtest11s; -- allowed+ a | c +---+----+ 1 | 20+ 2 | 40+(2 rows)++SELECT gf1(10); -- not allowed+ERROR: permission denied for function gf1+SELECT a, c FROM gtest12v; -- not allowed; TODO: ought to be allowed+ERROR: permission denied for function gf1+SELECT a, c FROM gtest12s; -- allowed+ a | c +---+----+ 1 | 30+ 2 | 60+(2 rows)++RESET ROLE;+DROP TABLE gtest11v, gtest11s, gtest12v, gtest12s;+DROP FUNCTION gf1(int);+DROP USER regress_user11;+-- check constraints+CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) CHECK (b < 50));+INSERT INTO gtest20 (a) VALUES (10); -- ok+INSERT INTO gtest20 (a) VALUES (30); -- violates constraint+ERROR: new row for relation "gtest20" violates check constraint "gtest20_b_check"+DETAIL: Failing row contains (30).+CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));+INSERT INTO gtest20a (a) VALUES (10);+INSERT INTO gtest20a (a) VALUES (30);+ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row+ERROR: check constraint "gtest20a_b_check" is violated by some row+CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));+INSERT INTO gtest20b (a) VALUES (10);+INSERT INTO gtest20b (a) VALUES (30);+ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID;+ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row+ERROR: check constraint "chk" is violated by some row+-- not-null constraints+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) NOT NULL);+INSERT INTO gtest21a (a) VALUES (1); -- ok+INSERT INTO gtest21a (a) VALUES (0); -- violates constraint+ERROR: new row for relation "gtest21a" violates check constraint "gtest21a_b_check"+DETAIL: Failing row contains (0).+CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)));+ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL; -- error+ERROR: cannot use SET NOT NULL on virtual generated column "b"+HINT: Add a CHECK constraint instead.+ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL; -- error+ERROR: cannot use DROP NOT NULL on virtual generated column "b"+CREATE TABLE gtest21c (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);+INSERT INTO gtest21c (a) VALUES (1); -- ok+INSERT INTO gtest21c (a) VALUES (0); -- violates constraint+ERROR: null value in column "b" violates not-null constraint+DETAIL: Failing row contains (0, null).+CREATE TABLE gtest21d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);+ALTER TABLE gtest21d ALTER COLUMN b SET NOT NULL;+INSERT INTO gtest21d (a) VALUES (1); -- ok+INSERT INTO gtest21d (a) VALUES (0); -- violates constraint+ERROR: null value in column "b" violates not-null constraint+DETAIL: Failing row contains (0, null).+ALTER TABLE gtest21d ALTER COLUMN b DROP NOT NULL;+INSERT INTO gtest21d (a) VALUES (0); -- ok now+-- index constraints+CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) UNIQUE); -- error+ERROR: index creation on virtual generated columns is not supported+CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a * 2), PRIMARY KEY (a, b)); -- error+ERROR: index creation on virtual generated columns is not supported+-- indexes+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2));+CREATE INDEX gtest22c_b_idx ON gtest22c (b); -- error+ERROR: index creation on virtual generated columns is not supported+CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));+ERROR: index creation on virtual generated columns is not supported+CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;+ERROR: index creation on virtual generated columns is not supported+\d gtest22c+ Table "public.gtest22c"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-----------------------------+ a | integer | | | + b | integer | | | generated always as (a * 2)++INSERT INTO gtest22c VALUES (1), (2), (3);+SET enable_seqscan TO off;+SET enable_bitmapscan TO off;+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;+ QUERY PLAN +-------------------------------+ Seq Scan on gtest22c+ Filter: (((a * 2) * 3) = 6)+(2 rows)++SELECT * FROM gtest22c WHERE b * 3 = 6;+ a | b +---+---+ 1 | 2+(1 row)++EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;+ QUERY PLAN +---------------------------------------+ Seq Scan on gtest22c+ Filter: ((a = 1) AND ((a * 2) > 0))+(2 rows)++SELECT * FROM gtest22c WHERE a = 1 AND b > 0;+ a | b +---+---+ 1 | 2+(1 row)++RESET enable_seqscan;+RESET enable_bitmapscan;+CREATE TABLE gtest22d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);+CREATE INDEX gtest22d_b_idx ON gtest22d (b);+CREATE INDEX gtest22d_expr_idx ON gtest22d ((b * 3));+CREATE INDEX gtest22d_pred_idx ON gtest22d (a) WHERE b > 0;+\d gtest22d+ Table "public.gtest22d"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------+ a | integer | | | + b | integer | | | generated always as (a * 2) stored+Indexes:+ "gtest22d_b_idx" btree (b)+ "gtest22d_expr_idx" btree ((b * 3))+ "gtest22d_pred_idx" btree (a) WHERE b > 0++INSERT INTO gtest22d VALUES (1), (2), (3);+SET enable_seqscan TO off;+SET enable_bitmapscan TO off;+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE b = 4;+ QUERY PLAN +---------------------------------------------+ Index Scan using gtest22d_b_idx on gtest22d+ Index Cond: (b = 4)+(2 rows)++SELECT * FROM gtest22d WHERE b = 4;+ a | b +---+---+ 2 | 4+(1 row)++EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE b * 3 = 6;+ QUERY PLAN +------------------------------------------------+ Index Scan using gtest22d_expr_idx on gtest22d+ Index Cond: ((b * 3) = 6)+(2 rows)++SELECT * FROM gtest22d WHERE b * 3 = 6;+ a | b +---+---+ 1 | 2+(1 row)++EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE a = 1 AND b > 0;+ QUERY PLAN +------------------------------------------------+ Index Scan using gtest22d_pred_idx on gtest22d+ Index Cond: (a = 1)+(2 rows)++SELECT * FROM gtest22d WHERE a = 1 AND b > 0;+ a | b +---+---+ 1 | 2+(1 row)++RESET enable_seqscan;+RESET enable_bitmapscan;+-- foreign keys+CREATE TABLE gtest23a (x int PRIMARY KEY, y int);+INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error+ERROR: invalid ON UPDATE action for foreign key constraint containing generated column+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) REFERENCES gtest23a (x) ON DELETE SET NULL); -- error+ERROR: invalid ON DELETE action for foreign key constraint containing generated column+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) REFERENCES gtest23a (x)); -- error+ERROR: foreign key constraints on virtual generated columns are not supported+CREATE TABLE gtest23c (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x));+\d gtest23c+ Table "public.gtest23c"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------+ a | integer | | not null | + b | integer | | | generated always as (a * 2) stored+Indexes:+ "gtest23c_pkey" PRIMARY KEY, btree (a)+Foreign-key constraints:+ "gtest23c_b_fkey" FOREIGN KEY (b) REFERENCES gtest23a(x)++INSERT INTO gtest23c VALUES (1); -- ok+INSERT INTO gtest23c VALUES (5); -- error+ERROR: insert or update on table "gtest23c" violates foreign key constraint "gtest23c_b_fkey"+DETAIL: Key (b)=(10) is not present in table "gtest23a".+DROP TABLE gtest23c;+DROP TABLE gtest23a;+CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) STORED, PRIMARY KEY (y));+INSERT INTO gtest23p VALUES (1), (2), (3);+CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));+INSERT INTO gtest23q VALUES (1, 2); -- ok+INSERT INTO gtest23q VALUES (2, 5); -- error+ERROR: insert or update on table "gtest23q" violates foreign key constraint "gtest23q_b_fkey"+DETAIL: Key (b)=(5) is not present in table "gtest23p".+-- no test for PK using virtual column, since such an index cannot be created+-- domains+CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);+CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2)); -- prohibited+ERROR: virtual generated column "b" cannot have a domain type+LINE 1: CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENE...+ ^+-- typed tables (currently not supported)+CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);+CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2));+ERROR: generated colums are not supported on typed tables+DROP TYPE gtest_type CASCADE;+-- table partitions (currently not supported)+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1);+CREATE TABLE gtest_child PARTITION OF gtest_parent (+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2)+) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error+ERROR: generated columns are not supported on partitions+DROP TABLE gtest_parent;+-- partitioned table+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f1);+CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);+SELECT * FROM gtest_parent;+ f1 | f2 | f3 +------------+----+----+ 07-15-2016 | 1 | 2+(1 row)++SELECT * FROM gtest_child;+ f1 | f2 | f3 +------------+----+----+ 07-15-2016 | 1 | 2+(1 row)++DROP TABLE gtest_parent;+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);+CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);+SELECT * FROM gtest_parent;+ f1 | f2 | f3 +------------+----+----+ 07-15-2016 | 1 | 2+(1 row)++SELECT * FROM gtest_child;+ f1 | f2 | f3 +------------+----+----+ 07-15-2016 | 1 | 2+(1 row)++DROP TABLE gtest_parent;+-- generated columns in partition key (currently not supported)+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f3);+ERROR: using generated column in partition key is not supported+LINE 1: ...igint GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f3);+ ^+DETAIL: Column "f3" is a generated column.+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE ((f3 * 3));+ERROR: using generated column in partition key is not supported+LINE 1: ...GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE ((f3 * 3));+ ^+DETAIL: Column "f3" is a generated column.+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));+ERROR: using generated column in partition key is not supported+LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));+ ^+DETAIL: Column "f3" is a generated column.+/*+CREATE TABLE gtest_child1 PARTITION OF gtest_parent FOR VALUES FROM (1) TO (10);+CREATE TABLE gtest_child2 PARTITION OF gtest_parent FOR VALUES FROM (11) TO (20);+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 3);+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 10); -- error+SELECT * FROM gtest_child1;+SELECT * FROM gtest_child2;+DROP TABLE gtest_parent;+*/+-- ALTER TABLE ... ADD COLUMN+CREATE TABLE gtest25 (a int PRIMARY KEY);+INSERT INTO gtest25 VALUES (3), (4);+ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 3);+SELECT * FROM gtest25 ORDER BY a;+ a | b +---+----+ 3 | 9+ 4 | 12+(2 rows)++ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4); -- error+ERROR: cannot use generated column "b" in column generation expression+DETAIL: A generated column cannot reference another generated column.+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4); -- error+ERROR: column "z" does not exist+-- ALTER TABLE ... ALTER COLUMN+CREATE TABLE gtest27 (a int, b int GENERATED ALWAYS AS (a * 2));+INSERT INTO gtest27 (a) VALUES (3), (4);+ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error+ERROR: cannot alter type of a column used by a generated column+DETAIL: Column "a" is used by generated column "b".+ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric;+\d gtest27+ Table "public.gtest27"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-------------------------------+ a | integer | | | + b | numeric | | | generated always as ((a * 2))++SELECT * FROM gtest27;+ a | b +---+---+ 3 | 6+ 4 | 8+(2 rows)++ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean; -- error+ERROR: generation expression for column "b" cannot be cast automatically to type boolean+ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- error+ERROR: column "b" of relation "gtest27" is a generated column+\d gtest27+ Table "public.gtest27"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-------------------------------+ a | integer | | | + b | numeric | | | generated always as ((a * 2))++-- triggers+CREATE TABLE gtest26 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));+CREATE FUNCTION gtest_trigger_func() RETURNS trigger+ LANGUAGE plpgsql+AS $$+BEGIN+ IF tg_op IN ('DELETE', 'UPDATE') THEN+ RAISE INFO '%: old = %', TG_NAME, OLD;+ END IF;+ IF tg_op IN ('INSERT', 'UPDATE') THEN+ RAISE INFO '%: new = %', TG_NAME, NEW;+ END IF;+ IF tg_op = 'DELETE' THEN+ RETURN OLD;+ ELSE+ RETURN NEW;+ END IF;+END+$$;+CREATE TRIGGER gtest1 BEFORE DELETE OR UPDATE ON gtest26+ FOR EACH ROW+ WHEN (OLD.b < 0) -- ok+ EXECUTE PROCEDURE gtest_trigger_func();+CREATE TRIGGER gtest2 BEFORE INSERT OR UPDATE ON gtest26+ FOR EACH ROW+ WHEN (NEW.b < 0) -- error+ EXECUTE PROCEDURE gtest_trigger_func();+ERROR: BEFORE trigger's WHEN condition cannot reference NEW generated columns+LINE 3: WHEN (NEW.b < 0) + ^+CREATE TRIGGER gtest3 AFTER DELETE OR UPDATE ON gtest26+ FOR EACH ROW+ WHEN (OLD.b < 0) -- ok+ EXECUTE PROCEDURE gtest_trigger_func();+CREATE TRIGGER gtest4 AFTER INSERT OR UPDATE ON gtest26+ FOR EACH ROW+ WHEN (NEW.b < 0) -- ok+ EXECUTE PROCEDURE gtest_trigger_func();+INSERT INTO gtest26 (a) VALUES (-2), (0), (3);+INFO: gtest4: new = (-2,)+SELECT * FROM gtest26 ORDER BY a;+ a | b +----+----+ -2 | -4+ 0 | 0+ 3 | 6+(3 rows)++UPDATE gtest26 SET a = a * -2;+INFO: gtest1: old = (-2,)+INFO: gtest1: new = (4,)+INFO: gtest3: old = (-2,)+INFO: gtest3: new = (4,)+INFO: gtest4: old = (3,)+INFO: gtest4: new = (-6,)+SELECT * FROM gtest26 ORDER BY a;+ a | b +----+-----+ -6 | -12+ 0 | 0+ 4 | 8+(3 rows)++DELETE FROM gtest26 WHERE a = -6;+INFO: gtest1: old = (-6,)+INFO: gtest3: old = (-6,)+SELECT * FROM gtest26 ORDER BY a;+ a | b +---+---+ 0 | 0+ 4 | 8+(2 rows)++CREATE FUNCTION gtest_trigger_func2() RETURNS trigger+ LANGUAGE plpgsql+AS $$+BEGIN+ NEW.b = 5;+ RETURN NEW;+END+$$;+CREATE TRIGGER gtest10 BEFORE INSERT OR UPDATE ON gtest26+ FOR EACH ROW+ EXECUTE PROCEDURE gtest_trigger_func2();+INSERT INTO gtest26 (a) VALUES (10);+ERROR: trigger modified virtual generated column value+UPDATE gtest26 SET a = 1 WHERE a = 0;+ERROR: trigger modified virtual generated column value+-- LIKE INCLUDING GENERATED and dropped column handling+CREATE TABLE gtest28a (+ a int,+ b int,+ c int,+ x int GENERATED ALWAYS AS (b * 2)+);+ALTER TABLE gtest28a DROP COLUMN a;+CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);+\d gtest28*+ Table "public.gtest28a"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-----------------------------+ b | integer | | | + c | integer | | | + x | integer | | | generated always as (b * 2)++ Table "public.gtest28b"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-----------------------------+ b | integer | | | + c | integer | | | + x | integer | | | generated always as (b * 2)+diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_scheduleindex b5e15501dd..2f13b6823c 100644--- a/src/test/regress/parallel_schedule+++ b/src/test/regress/parallel_schedule@@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c
# ----------
# Another group of parallel tests
# ----------
-test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info+test: identity generated partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info
# event triggers cannot run concurrently with any test that runs DDL
test: event_trigger
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_scheduleindex 49329ffbb6..640b119018 100644--- a/src/test/regress/serial_schedule+++ b/src/test/regress/serial_schedule@@ -179,6 +179,7 @@ test: largeobject
test: with
test: xml
test: identity
+test: generated
test: partition_join
test: partition_prune
test: reloptions
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sqlindex 42cad6826b..c461ea7904 100644--- a/src/test/regress/sql/create_table_like.sql+++ b/src/test/regress/sql/create_table_like.sql@@ -51,6 +51,20 @@ CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY);
SELECT * FROM test_like_id_3; -- identity was copied and applied
DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
+CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2));+\d test_like_gen_1+INSERT INTO test_like_gen_1 (a) VALUES (1);+SELECT * FROM test_like_gen_1;+CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1);+\d test_like_gen_2+INSERT INTO test_like_gen_2 (a) VALUES (1);+SELECT * FROM test_like_gen_2;+CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);+\d test_like_gen_3+INSERT INTO test_like_gen_3 (a) VALUES (1);+SELECT * FROM test_like_gen_3;+DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3;+
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sqlnew file mode 100644index 0000000000..8f6609da23--- /dev/null+++ b/src/test/regress/sql/generated.sql@@ -0,0 +1,408 @@+-- sanity check of system catalog+SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');+++CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55));+CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));++SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_name LIKE 'gtest_' ORDER BY 1, 2;++SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage ORDER BY 1, 2, 3;++\d gtest1++-- duplicate generated+CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) GENERATED ALWAYS AS (a * 3));++-- references to other generated columns, including self-references+CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2));+CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2), c int GENERATED ALWAYS AS (b * 3));++-- invalid reference+CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2));++-- functions must be immutable+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()));++-- cannot have default/identity and generated+CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2));+CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2));++INSERT INTO gtest1 VALUES (1);+INSERT INTO gtest1 VALUES (2, DEFAULT);+INSERT INTO gtest1 VALUES (3, 33); -- error++SELECT * FROM gtest1 ORDER BY a;++UPDATE gtest1 SET b = DEFAULT WHERE a = 1;+UPDATE gtest1 SET b = 11 WHERE a = 1; -- error++SELECT * FROM gtest1 ORDER BY a;++SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a;+SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;++-- test that overflow error happens on read+INSERT INTO gtest1 VALUES (2000000000);+SELECT * FROM gtest1;+DELETE FROM gtest1 WHERE a = 2000000000;++-- test with joins+CREATE TABLE gtestx (x int, y int);+INSERT INTO gtestx VALUES (11, 1), (22, 2), (33, 3);+SELECT * FROM gtestx, gtest1 WHERE gtestx.y = gtest1.a;+DROP TABLE gtestx;++-- test UPDATE/DELETE quals+SELECT * FROM gtest1 ORDER BY a;+UPDATE gtest1 SET a = 3 WHERE b = 4;+SELECT * FROM gtest1 ORDER BY a;+DELETE FROM gtest1 WHERE b = 2;+SELECT * FROM gtest1 ORDER BY a;++-- views+CREATE VIEW gtest1v AS SELECT * FROM gtest1;+SELECT * FROM gtest1v;+INSERT INTO gtest1v VALUES (4, 8); -- fails++-- inheritance+CREATE TABLE gtest1_1 () INHERITS (gtest1);+SELECT * FROM gtest1_1;+\d gtest1_1+INSERT INTO gtest1_1 VALUES (4);+SELECT * FROM gtest1_1;+SELECT * FROM gtest1;++-- test inheritance mismatch+CREATE TABLE gtesty (x int, b int);+CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error+DROP TABLE gtesty;++-- stored+CREATE TABLE gtest3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 3) STORED);+INSERT INTO gtest3 (a) VALUES (1), (2), (3);+SELECT * FROM gtest3 ORDER BY a;+UPDATE gtest3 SET a = 22 WHERE a = 2;+SELECT * FROM gtest3 ORDER BY a;++-- COPY+TRUNCATE gtest1;+INSERT INTO gtest1 (a) VALUES (1), (2);++COPY gtest1 TO stdout;++COPY gtest1 (a, b) TO stdout;++COPY gtest1 FROM stdin;+3+4+\.++COPY gtest1 (a, b) FROM stdin;++SELECT * FROM gtest1 ORDER BY a;++TRUNCATE gtest3;+INSERT INTO gtest3 (a) VALUES (1), (2);++COPY gtest3 TO stdout;++COPY gtest3 (a, b) TO stdout;++COPY gtest3 FROM stdin;+3+4+\.++COPY gtest3 (a, b) FROM stdin;++SELECT * FROM gtest3 ORDER BY a;++-- drop column behavior+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2));+ALTER TABLE gtest10 DROP COLUMN b;++\d gtest10++-- privileges+CREATE USER regress_user11;++CREATE TABLE gtest11v (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) VIRTUAL);+INSERT INTO gtest11v VALUES (1, 10), (2, 20);+GRANT SELECT (a, c) ON gtest11v TO regress_user11;++CREATE TABLE gtest11s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);+INSERT INTO gtest11s VALUES (1, 10), (2, 20);+GRANT SELECT (a, c) ON gtest11s TO regress_user11;++CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE SQL;+REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;++CREATE TABLE gtest12v (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) VIRTUAL);+INSERT INTO gtest12v VALUES (1, 10), (2, 20);+GRANT SELECT (a, c) ON gtest12v TO regress_user11;++CREATE TABLE gtest12s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);+INSERT INTO gtest12s VALUES (1, 10), (2, 20);+GRANT SELECT (a, c) ON gtest12s TO regress_user11;++SET ROLE regress_user11;+SELECT a, b FROM gtest11v; -- not allowed+SELECT a, c FROM gtest11v; -- allowed+SELECT a, b FROM gtest11s; -- not allowed+SELECT a, c FROM gtest11s; -- allowed+SELECT gf1(10); -- not allowed+SELECT a, c FROM gtest12v; -- not allowed; TODO: ought to be allowed+SELECT a, c FROM gtest12s; -- allowed+RESET ROLE;++DROP TABLE gtest11v, gtest11s, gtest12v, gtest12s;+DROP FUNCTION gf1(int);+DROP USER regress_user11;++-- check constraints+CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) CHECK (b < 50));+INSERT INTO gtest20 (a) VALUES (10); -- ok+INSERT INTO gtest20 (a) VALUES (30); -- violates constraint++CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));+INSERT INTO gtest20a (a) VALUES (10);+INSERT INTO gtest20a (a) VALUES (30);+ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row++CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));+INSERT INTO gtest20b (a) VALUES (10);+INSERT INTO gtest20b (a) VALUES (30);+ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID;+ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row++-- not-null constraints+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) NOT NULL);+INSERT INTO gtest21a (a) VALUES (1); -- ok+INSERT INTO gtest21a (a) VALUES (0); -- violates constraint++CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)));+ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL; -- error+ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL; -- error++CREATE TABLE gtest21c (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);+INSERT INTO gtest21c (a) VALUES (1); -- ok+INSERT INTO gtest21c (a) VALUES (0); -- violates constraint++CREATE TABLE gtest21d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);+ALTER TABLE gtest21d ALTER COLUMN b SET NOT NULL;+INSERT INTO gtest21d (a) VALUES (1); -- ok+INSERT INTO gtest21d (a) VALUES (0); -- violates constraint+ALTER TABLE gtest21d ALTER COLUMN b DROP NOT NULL;+INSERT INTO gtest21d (a) VALUES (0); -- ok now++-- index constraints+CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) UNIQUE); -- error+CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a * 2), PRIMARY KEY (a, b)); -- error++-- indexes+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2));+CREATE INDEX gtest22c_b_idx ON gtest22c (b); -- error+CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));+CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;+\d gtest22c++INSERT INTO gtest22c VALUES (1), (2), (3);+SET enable_seqscan TO off;+SET enable_bitmapscan TO off;+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;+SELECT * FROM gtest22c WHERE b * 3 = 6;+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;+SELECT * FROM gtest22c WHERE a = 1 AND b > 0;+RESET enable_seqscan;+RESET enable_bitmapscan;++CREATE TABLE gtest22d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);+CREATE INDEX gtest22d_b_idx ON gtest22d (b);+CREATE INDEX gtest22d_expr_idx ON gtest22d ((b * 3));+CREATE INDEX gtest22d_pred_idx ON gtest22d (a) WHERE b > 0;+\d gtest22d++INSERT INTO gtest22d VALUES (1), (2), (3);+SET enable_seqscan TO off;+SET enable_bitmapscan TO off;+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE b = 4;+SELECT * FROM gtest22d WHERE b = 4;+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE b * 3 = 6;+SELECT * FROM gtest22d WHERE b * 3 = 6;+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE a = 1 AND b > 0;+SELECT * FROM gtest22d WHERE a = 1 AND b > 0;+RESET enable_seqscan;+RESET enable_bitmapscan;++-- foreign keys+CREATE TABLE gtest23a (x int PRIMARY KEY, y int);+INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);++CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) REFERENCES gtest23a (x) ON DELETE SET NULL); -- error++CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) REFERENCES gtest23a (x)); -- error++CREATE TABLE gtest23c (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x));+\d gtest23c++INSERT INTO gtest23c VALUES (1); -- ok+INSERT INTO gtest23c VALUES (5); -- error++DROP TABLE gtest23c;+DROP TABLE gtest23a;++CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) STORED, PRIMARY KEY (y));+INSERT INTO gtest23p VALUES (1), (2), (3);++CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));+INSERT INTO gtest23q VALUES (1, 2); -- ok+INSERT INTO gtest23q VALUES (2, 5); -- error++-- no test for PK using virtual column, since such an index cannot be created++-- domains+CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);+CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2)); -- prohibited++-- typed tables (currently not supported)+CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);+CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2));+DROP TYPE gtest_type CASCADE;++-- table partitions (currently not supported)+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1);+CREATE TABLE gtest_child PARTITION OF gtest_parent (+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2)+) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error+DROP TABLE gtest_parent;++-- partitioned table+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f1);+CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);+SELECT * FROM gtest_parent;+SELECT * FROM gtest_child;+DROP TABLE gtest_parent;++CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);+CREATE TABLE gtest_child PARTITION OF gtest_parent FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);+SELECT * FROM gtest_parent;+SELECT * FROM gtest_child;+DROP TABLE gtest_parent;++-- generated columns in partition key (currently not supported)+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE (f3);+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2)) PARTITION BY RANGE ((f3 * 3));+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));+/*+CREATE TABLE gtest_child1 PARTITION OF gtest_parent FOR VALUES FROM (1) TO (10);+CREATE TABLE gtest_child2 PARTITION OF gtest_parent FOR VALUES FROM (11) TO (20);+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 3);+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 10); -- error+SELECT * FROM gtest_child1;+SELECT * FROM gtest_child2;+DROP TABLE gtest_parent;+*/++-- ALTER TABLE ... ADD COLUMN+CREATE TABLE gtest25 (a int PRIMARY KEY);+INSERT INTO gtest25 VALUES (3), (4);+ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 3);+SELECT * FROM gtest25 ORDER BY a;+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4); -- error+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4); -- error++-- ALTER TABLE ... ALTER COLUMN+CREATE TABLE gtest27 (a int, b int GENERATED ALWAYS AS (a * 2));+INSERT INTO gtest27 (a) VALUES (3), (4);+ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error+ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric;+\d gtest27+SELECT * FROM gtest27;+ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean; -- error++ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- error+\d gtest27++-- triggers+CREATE TABLE gtest26 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));++CREATE FUNCTION gtest_trigger_func() RETURNS trigger+ LANGUAGE plpgsql+AS $$+BEGIN+ IF tg_op IN ('DELETE', 'UPDATE') THEN+ RAISE INFO '%: old = %', TG_NAME, OLD;+ END IF;+ IF tg_op IN ('INSERT', 'UPDATE') THEN+ RAISE INFO '%: new = %', TG_NAME, NEW;+ END IF;+ IF tg_op = 'DELETE' THEN+ RETURN OLD;+ ELSE+ RETURN NEW;+ END IF;+END+$$;++CREATE TRIGGER gtest1 BEFORE DELETE OR UPDATE ON gtest26+ FOR EACH ROW+ WHEN (OLD.b < 0) -- ok+ EXECUTE PROCEDURE gtest_trigger_func();++CREATE TRIGGER gtest2 BEFORE INSERT OR UPDATE ON gtest26+ FOR EACH ROW+ WHEN (NEW.b < 0) -- error+ EXECUTE PROCEDURE gtest_trigger_func();++CREATE TRIGGER gtest3 AFTER DELETE OR UPDATE ON gtest26+ FOR EACH ROW+ WHEN (OLD.b < 0) -- ok+ EXECUTE PROCEDURE gtest_trigger_func();++CREATE TRIGGER gtest4 AFTER INSERT OR UPDATE ON gtest26+ FOR EACH ROW+ WHEN (NEW.b < 0) -- ok+ EXECUTE PROCEDURE gtest_trigger_func();++INSERT INTO gtest26 (a) VALUES (-2), (0), (3);+SELECT * FROM gtest26 ORDER BY a;+UPDATE gtest26 SET a = a * -2;+SELECT * FROM gtest26 ORDER BY a;+DELETE FROM gtest26 WHERE a = -6;+SELECT * FROM gtest26 ORDER BY a;+++CREATE FUNCTION gtest_trigger_func2() RETURNS trigger+ LANGUAGE plpgsql+AS $$+BEGIN+ NEW.b = 5;+ RETURN NEW;+END+$$;++CREATE TRIGGER gtest10 BEFORE INSERT OR UPDATE ON gtest26+ FOR EACH ROW+ EXECUTE PROCEDURE gtest_trigger_func2();++INSERT INTO gtest26 (a) VALUES (10);+UPDATE gtest26 SET a = 1 WHERE a = 0;++-- LIKE INCLUDING GENERATED and dropped column handling+CREATE TABLE gtest28a (+ a int,+ b int,+ c int,+ x int GENERATED ALWAYS AS (b * 2)+);++ALTER TABLE gtest28a DROP COLUMN a;++CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);++\d gtest28*
base-commit: c2c7c263af00a94bbc1a15461215f29280a9ddf0
--
2.19.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter.eisentraut@2ndquadrant.com, sk@zsrv.org, er@xs4all.nl, michael.paquier@gmail.com, jaime.casanova@2ndquadrant.com
Subject: Re: [HACKERS] generated columns
In-Reply-To: <b70b745d-dd1e-eab0-8290-99b8edc7ed74@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox