On 1/19/18 00:18, Michael Paquier wrote:
> Instead of leaving bits for a feature that may or may not be
> implemented, have you considered just blocking STORED at parsing level
> and remove those bits? There is little point in keeping the equivalent
> of dead code in the tree. So I would suggest a patch simplification:
> - Drop the VIRTUAL/STORED parsing from the grammar for now.
> - Define VIRTUAL as the default for the future.
fixed
> =# CREATE TABLE gen_1 (a int, b int GENERATED ALWAYS AS (a * 2)
> VIRTUAL);
> CREATE TABLE
> =# insert into gen_1 values (2000000000);
> INSERT 0 1
> =# select * from gen_1 ;
> ERROR: 22003: integer out of range
> Overflow checks do not happen when inserting, which makes the following
> SELECT to fail. This could be confusing for the user because the row has
> been inserted. Perhaps some evaluation of virtual tuples at insert phase
> should happen. The existing behavior makes sense as well as virtual
> values are only evaluated when read, so a test would be at least
> welcome.
added test
> Does the SQL spec mention the matter? How do other systems
> handle such cases?
In Oracle you get the same overflow error.
> CHECK constraints can be combined, still..
I think you can compare this to a view. A view can produce overflow
errors on read. But a CHECK constraint is more like a CHECK option on a
view that checks as values are put into the view.
> The last patch crashes for typed tables:
> =# CREATE TYPE itest_type AS (f1 integer, f2 text, f3 bigint);
> CREATE TYPE
> =# CREATE TABLE itest12 OF itest_type (f1 WITH OPTIONS GENERATED ALWAYS
> AS (f2 *2));
> [... boom ...]
> And for partitions:
> =# CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint)
> PARTITION BY RANGE (f1);
> CREATE TABLE
> =# CREATE TABLE itest_child PARTITION OF itest_parent (f3 WITH OPTIONS
> GENERATED ALWAYS AS (f3)) FOR VALUES FROM ('2016-07-01') TO
> ('2016-08-01');
> [... boom ...]
> Like what we did in 005ac298, I would suggest to throw
> ERRCODE_FEATURE_NOT_SUPPORTED. Please also add some tests.
done
> +SELECT a, c FROM gtest12; -- FIXME: should be allowed
> +ERROR: permission denied for function gf1
This is quite hard to fix and I would like to leave this for a future
release.
> +ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- FIXME
> +ERROR: column "b" of relation "gtest27" is a generated column
That FIXME seems to have been a mistake. I have removed it.
> + if (get_attgenerated(relationId, attno))
> + ereport(ERROR,
> + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("index creation on generated columns is not
> supported")));
> Shouldn't such messages mention explicitely "virtually"-generated
> columns? For stored columns the support would not be complicated in this
> case.
done
> +-- 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
> CHECK constraints can be used, so I find this restriction confusing.
We currently don't have infrastructure to support this. We run all
CHECK constraints whenever a row is changed, so that is easy. But we
don't have facilities to recheck the domain constraint in column b when
column a is updated. This could be done, but it's extra work.
> No test coverage for DELETE triggers?
done
> +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,)
> OLD and NEW values for generated columns don't show up. Am I missing
> something or they should be available?
This was already discussed a few times in the thread. I don't know what
a good solution is.
I have in this patch added facilties to handle this better in other PLs.
So the virtual generated column doesn't show up there in the trigger
data. This is possible because we copy the trigger data from the
internal structures into language-specific hashes/dictionaries/etc.
In PL/pgSQL, this is a bit more difficult, because we handle the table's
row type there. We can't just "hide" the generated column when looking
at the row type. Actually, we could do it quite easily, but that would
probably raise other weirdnesses.
This also raises a question how a row type with generated columns should
behave. I think a generation expression is a property of a table, so it
does not apply in a row type. (Just like a default is a property of a
table and does not apply in row types.)
> Please use brackers here if you use a comment in the if() block...
> [/nit_mode]
done
> COPY as you are proposing looks sensible to me. I am not sure about any
> options though as it is possible to enforce the selection of generated
> columns using COPY (SELECT).
removed that comment
> Per my tests, generated columns can work with column system attributes
> (xmax, xmin, etc.). Some tests could be added.
Hard to test that, because the results would be nondeterministic.
> - if (tab->relkind == RELKIND_RELATION ||
> - tab->relkind == RELKIND_PARTITIONED_TABLE)
> + if ((tab->relkind == RELKIND_RELATION ||
> + tab->relkind == RELKIND_PARTITIONED_TABLE) &&
> + get_attgenerated(RelationGetRelid(rel), attnum) !=
> ATTRIBUTE_GENERATE
> I think that you should store the result of get_attgenerated() and reuse
> it multiple times.
I don't see where that would apply. I think the hunks you are seeing
belong to different functions.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From b328f313822e85f8971c11222a932263202ac2cb Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Thu, 25 Jan 2018 22:09:54 -0500
Subject: [PATCH v4] 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.
The plan to is implement two kinds of generated columns:
virtual (computed on read) and stored (computed on write). This patch
only implements the virtual kind, leaving stubs to implement the stored
kind later.
---
doc/src/sgml/catalogs.sgml | 11 +
doc/src/sgml/information_schema.sgml | 10 +-
doc/src/sgml/ref/copy.sgml | 3 +-
doc/src/sgml/ref/create_table.sgml | 34 +-
src/backend/access/common/tupdesc.c | 7 +
src/backend/catalog/heap.c | 92 ++++-
src/backend/catalog/index.c | 1 +
src/backend/catalog/information_schema.sql | 8 +-
src/backend/commands/copy.c | 10 +-
src/backend/commands/indexcmds.c | 24 +-
src/backend/commands/tablecmds.c | 164 ++++++++-
src/backend/commands/trigger.c | 36 ++
src/backend/commands/typecmds.c | 6 +-
src/backend/executor/execMain.c | 7 +-
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/equalfuncs.c | 2 +
src/backend/nodes/outfuncs.c | 9 +
src/backend/parser/gram.y | 12 +
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 | 96 ++++-
src/backend/rewrite/rewriteHandler.c | 144 +++++++-
src/backend/utils/cache/lsyscache.c | 32 ++
src/backend/utils/cache/relcache.c | 1 +
src/bin/pg_dump/pg_dump.c | 36 +-
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 | 39 ++
src/bin/psql/describe.c | 26 +-
src/include/catalog/heap.h | 3 +-
src/include/catalog/pg_attribute.h | 23 +-
src/include/catalog/pg_class.h | 2 +-
src/include/nodes/parsenodes.h | 12 +-
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 | 7 +
src/pl/tcl/sql/pltcl_queries.sql | 25 ++
src/pl/tcl/sql/pltcl_setup.sql | 10 +
src/test/regress/expected/create_table_like.out | 46 +++
src/test/regress/expected/generated.out | 462 ++++++++++++++++++++++++
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 | 277 ++++++++++++++
55 files changed, 2018 insertions(+), 88 deletions(-)
create mode 100644 src/test/regress/expected/generated.out
create mode 100644 src/test/regress/sql/generated.sql
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 71e20f2740..2ebd9aab9d 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1160,6 +1160,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>v</literal> (for virtual, the only option
+ currently).
+ </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.sgml
index 0faa72f1d3..6edf04cc69 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -1648,13 +1648,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.sgml
index af2a0e91b9..1ee0304888 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.sgml
index a0c9a6d257..0c471cc598 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> ) |
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 } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES |
STORAGE | COMMENTS | ALL }
+{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | GENERATED |
INDEXES | STORAGE | COMMENTS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable>
is:</phrase>
@@ -585,6 +586,12 @@ <title>Parameters</title>
sequence is created for each identity column of the new table, separate
from the sequences associated with the old table.
</para>
+ <para>
+ Generated columns will only become generated columns in the new table if
+ <literal>INCLUDING GENERATED</literal> is specified, which will copy the
+ generation expression. Otherwise, the new column will be a regular base
+ column.
+ </para>
<para>
Not-null constraints are always copied to the new table.
<literal>CHECK</literal> constraints will be copied only if
@@ -617,7 +624,7 @@ <title>Parameters</title>
</para>
<para>
<literal>INCLUDING ALL</literal> is an abbreviated form of
- <literal>INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS
INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS</literal>.
+ <literal>INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING GENERATED
INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING
COMMENTS</literal>.
</para>
<para>
Note that unlike <literal>INHERITS</literal>, columns and
@@ -731,6 +738,29 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>GENERATED ALWAYS AS (
<replaceable>generation_expr</replaceable>
)</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>
+ The current implementation creates a <firstterm>virtual</firstterm>
+ generated column, which means the column will be computed when it is
+ read, and it will not occupy any storage.
+ </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>
diff --git a/src/backend/access/common/tupdesc.c
b/src/backend/access/common/tupdesc.c
index f1f44230cd..31146eadd3 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -130,6 +130,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
att->attnotnull = false;
att->atthasdef = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
}
/* We can copy the tuple type identification, too */
@@ -228,6 +229,7 @@ TupleDescCopy(TupleDesc dst, TupleDesc src)
att->attnotnull = false;
att->atthasdef = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
}
dst->constr = NULL;
@@ -280,6 +282,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
dstAtt->attnotnull = false;
dstAtt->atthasdef = false;
dstAtt->attidentity = '\0';
+ dstAtt->attgenerated = '\0';
}
/*
@@ -426,6 +429,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)
@@ -585,6 +590,7 @@ TupleDescInitEntry(TupleDesc desc,
att->attnotnull = false;
att->atthasdef = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
@@ -643,6 +649,7 @@ TupleDescInitBuiltinEntry(TupleDesc desc,
att->attnotnull = false;
att->atthasdef = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 774c07b03a..ec394372e3 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -67,6 +67,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"
@@ -144,37 +145,37 @@ static List *insert_ordered_unique_oid(List *list, Oid
datum);
static FormData_pg_attribute a1 = {
0, {"ctid"}, TIDOID, 0, sizeof(ItemPointerData),
SelfItemPointerAttributeNumber, 0, -1, -1,
- false, 'p', 's', true, false, '\0', false, true, 0
+ false, 'p', 's', true, false, '\0', '\0', false, true, 0
};
static FormData_pg_attribute a2 = {
0, {"oid"}, OIDOID, 0, sizeof(Oid),
ObjectIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
static FormData_pg_attribute a3 = {
0, {"xmin"}, XIDOID, 0, sizeof(TransactionId),
MinTransactionIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
static FormData_pg_attribute a4 = {
0, {"cmin"}, CIDOID, 0, sizeof(CommandId),
MinCommandIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
static FormData_pg_attribute a5 = {
0, {"xmax"}, XIDOID, 0, sizeof(TransactionId),
MaxTransactionIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
static FormData_pg_attribute a6 = {
0, {"cmax"}, CIDOID, 0, sizeof(CommandId),
MaxCommandIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
/*
@@ -186,7 +187,7 @@ static FormData_pg_attribute a6 = {
static FormData_pg_attribute a7 = {
0, {"tableoid"}, OIDOID, 0, sizeof(Oid),
TableOidAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
static const Form_pg_attribute SysAtt[] = {&a1, &a2, &a3, &a4, &a5, &a6, &a7};
@@ -625,6 +626,7 @@ InsertPgAttributeTuple(Relation pg_attribute_rel,
values[Anum_pg_attribute_attnotnull - 1] =
BoolGetDatum(new_attribute->attnotnull);
values[Anum_pg_attribute_atthasdef - 1] =
BoolGetDatum(new_attribute->atthasdef);
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);
@@ -1939,6 +1941,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
Relation attrrel;
HeapTuple atttup;
Form_pg_attribute attStruct;
+ char attgenerated;
Oid attrdefOid;
ObjectAddress colobject,
defobject;
@@ -1994,6 +1997,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)
{
attStruct->atthasdef = true;
@@ -2015,7 +2019,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.
@@ -2279,7 +2302,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
@@ -2644,6 +2668,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 = getrelid(var->varno, pstate->p_rtable);
+ 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)),
+ 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.
@@ -2661,7 +2725,8 @@ cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
- const char *attname)
+ const char *attname,
+ char attgenerated)
{
Node *expr;
@@ -2670,17 +2735,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/index.c b/src/backend/catalog/index.c
index 849a469127..137cc6c6d5 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -371,6 +371,7 @@ ConstructTupleDescriptor(Relation heapRelation,
to->attnotnull = false;
to->atthasdef = false;
to->attidentity = '\0';
+ to->attgenerated = '\0';
to->attislocal = true;
to->attinhcount = 0;
to->attcollation = collationObjectId[i];
diff --git a/src/backend/catalog/information_schema.sql
b/src/backend/catalog/information_schema.sql
index 6fb1a1bc1c..5a33c9ca23 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -299,7 +299,7 @@ CREATE VIEW attributes AS
CAST(c.relname AS sql_identifier) AS udt_name,
CAST(a.attname AS sql_identifier) AS attribute_name,
CAST(a.attnum AS cardinal_number) AS ordinal_position,
- CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS
attribute_default,
+ CAST(pg_get_expr(ad.adbin, ad.adrelid, true) AS character_data) AS
attribute_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, -- This column was apparently removed between
SQL:2003 and SQL:2008.
@@ -656,7 +656,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, true) 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 +745,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, true) END AS character_data) AS generation_expression,
CAST(CASE WHEN c.relkind IN ('r', 'p') OR
(c.relkind IN ('v', 'f') AND
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 04a24c6082..b34e50c30a 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -2992,7 +2992,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 */
@@ -4683,6 +4683,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);
}
}
@@ -4707,6 +4709,12 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, List
*attnamelist)
continue;
if (namestrcmp(&(att->attname), name) == 0)
{
+ if (att->attgenerated)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("column
\"%s\" is a generated column",
+
name),
+
errdetail("Generated columns cannot be used in COPY.")));
attnum = att->attnum;
break;
}
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index a9461a4b06..97632f3681 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -641,6 +641,8 @@ 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 generated columns in indexes. (could be implemented)
*/
for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
{
@@ -650,10 +652,16 @@ DefineIndex(Oid relationId,
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("index creation on system
columns is not supported")));
+
+ if (get_attgenerated(relationId, attno))
+ 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)
{
@@ -662,14 +670,20 @@ DefineIndex(Oid relationId,
pull_varattnos((Node *) indexInfo->ii_Expressions, 1,
&indexattrs);
pull_varattnos((Node *) indexInfo->ii_Predicate, 1,
&indexattrs);
- for (i = FirstLowInvalidHeapAttributeNumber + 1; i < 0; i++)
+ i = -1;
+ while ((i = bms_next_member(indexattrs, i)) >= 0)
{
- if (i != ObjectIdAttributeNumber &&
- bms_is_member(i -
FirstLowInvalidHeapAttributeNumber,
- indexattrs))
+ AttrNumber attno = i +
FirstLowInvalidHeapAttributeNumber;
+
+ if (attno < 0 && attno != ObjectIdAttributeNumber)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("index creation on
system columns is not supported")));
+
+ if (get_attgenerated(relationId, attno))
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("index creation on
virtual generated columns is not supported")));
}
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2e768dd5e4..7c40dbb5d7 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -736,6 +736,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (colDef->identity)
attr->attidentity = colDef->identity;
+
+ if (colDef->generated)
+ attr->attgenerated = colDef->generated;
}
/*
@@ -2012,6 +2015,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
{
@@ -2030,6 +2040,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;
@@ -4502,7 +4513,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap,
LOCKMODE lockmode)
{
case CONSTR_CHECK:
needscan = true;
- con->qualstate = ExecPrepareExpr((Expr *)
con->qual, estate);
+ con->qualstate =
ExecPrepareExpr(expand_generated_columns_in_expr((Expr *) con->qual,
+
newrel ? newrel : oldrel),
+
estate);
break;
case CONSTR_FOREIGN:
/* Nothing to do here */
@@ -5401,6 +5414,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab,
Relation rel,
attribute.attnotnull = colDef->is_not_null;
attribute.atthasdef = false;
attribute.attidentity = colDef->identity;
+ attribute.attgenerated = colDef->generated;
attribute.attisdropped = false;
attribute.attislocal = colDef->is_local;
attribute.attinhcount = colDef->inhcount;
@@ -5774,6 +5788,18 @@ ATExecDropNotNull(Relation rel, const char *colName,
LOCKMODE lockmode)
errmsg("cannot alter system column \"%s\"",
colName)));
+ /*
+ * 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 (((Form_pg_attribute) GETSTRUCT(tuple))->attgenerated)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use DROP NOT NULL on generated
column \"%s\"",
+ colName)));
+
if (get_attidentity(RelationGetRelid(rel), attnum))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -5865,9 +5891,6 @@ ATExecDropNotNull(Relation rel, const char *colName,
LOCKMODE lockmode)
/*
* ALTER TABLE ALTER COLUMN SET NOT NULL
- *
- * Return the address of the modified column. If the column was already NOT
- * NULL, InvalidObjectAddress is returned.
*/
static void
@@ -5890,6 +5913,10 @@ ATPrepSetNotNull(Relation rel, bool recurse, bool
recursing)
}
}
+/*
+ * Return the address of the modified column. If the column was already NOT
+ * NULL, InvalidObjectAddress is returned.
+ */
static ObjectAddress
ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
const char *colName, LOCKMODE lockmode)
@@ -5921,6 +5948,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)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use SET NOT NULL on generated
column \"%s\"",
+ colName),
+ errhint("Add a CHECK constraint instead.")));
+
/*
* Okay, actually perform the catalog change ... if needed
*/
@@ -5983,6 +6021,12 @@ ATExecColumnDefault(Relation rel, const char *colName,
colName,
RelationGetRelationName(rel)),
newDefault ? 0 : errhint("Use ALTER TABLE ...
ALTER COLUMN ... DROP IDENTITY instead.")));
+ if (get_attgenerated(RelationGetRelid(rel), attnum))
+ 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
@@ -7257,6 +7301,41 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab,
Relation rel,
*/
checkFkeyPermissions(pkrel, pkattnum, numpks);
+ /*
+ * Foreign keys on generated columns are not yet implemented.
+ */
+ for (i = 0; i < numpks; i++)
+ {
+ if (get_attgenerated(RelationGetRelid(pkrel), pkattnum[i]))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("foreign key constraints
referencing generated columns are not supported")));
+ }
+ for (i = 0; i < numfks; i++)
+ {
+ if (get_attgenerated(RelationGetRelid(rel), fkattnum[i]))
+ {
+ /*
+ * 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")));
+
+ 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.
*
@@ -8262,7 +8341,7 @@ validateCheckConstraint(Relation rel, HeapTuple constrtup)
HeapTupleGetOid(constrtup));
conbin = TextDatumGetCString(val);
origexpr = (Expr *) stringToNode(conbin);
- exprstate = ExecPrepareExpr(origexpr, estate);
+ exprstate = ExecPrepareExpr(expand_generated_columns_in_expr(origexpr,
rel), estate);
econtext = GetPerTupleExprContext(estate);
tupdesc = RelationGetDescr(rel);
@@ -8878,8 +8957,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) &&
+ get_attgenerated(RelationGetRelid(rel), attnum) !=
ATTRIBUTE_GENERATED_VIRTUAL)
{
/*
* Set up an expression to transform the old data value to the
new
@@ -9153,10 +9233,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 (get_attgenerated(RelationGetRelid(rel), attnum))
+ 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;
@@ -9232,6 +9320,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))));
+ }
else
{
/* Not expecting any other
direct dependencies... */
@@ -9397,7 +9500,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,
@@ -9418,15 +9522,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);
}
@@ -13450,6 +13565,11 @@ ComputePartitionAttrs(Relation rel, List *partParams,
AttrNumber *partattrs,
errmsg("cannot use system
column \"%s\" in partition key",
pelem->name)));
+ if (attform->attgenerated)
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("using virtual
generated column in partition key is not supported")));
+
partattrs[attn] = attform->attnum;
atttype = attform->atttypid;
attcollation = attform->attcollation;
@@ -13537,6 +13657,20 @@ ComputePartitionAttrs(Relation rel, List *partParams,
AttrNumber *partattrs,
errmsg("partition key expressions cannot contain system column references")));
}
+ /*
+ * Generated columns not supported yet
+ */
+ i = -1;
+ while ((i = bms_next_member(expr_attrs, i)) >=
0)
+ {
+ AttrNumber attno = i +
FirstLowInvalidHeapAttributeNumber;
+
+ if
(get_attgenerated(RelationGetRelid(rel), attno))
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("using
virtual generated column in partition key is not supported")));
+ }
+
/*
* While it is not exactly *wrong* for a
partition expression
* to be a constant, it seems better to reject
such keys.
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 160d941c00..c667006b94 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -43,6 +43,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"
@@ -101,6 +102,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);
/*
@@ -562,6 +564,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
(get_attgenerated(RelationGetRelid(rel), var->varattno) &&
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 */
@@ -2342,6 +2349,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);
ExecStoreTuple(newtuple, newslot, InvalidBuffer, false);
@@ -2829,6 +2838,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);
ExecStoreTuple(newtuple, newslot, InvalidBuffer, false);
@@ -3226,6 +3237,7 @@ TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
oldContext =
MemoryContextSwitchTo(estate->es_query_cxt);
tgqual = stringToNode(trigger->tgqual);
+ tgqual = (Node *)
expand_generated_columns_in_expr((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);
@@ -5869,3 +5881,27 @@ 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)
+{
+ int i;
+
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ if (TupleDescAttr(tupdesc, i)->attgenerated ==
ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ bool isnull;
+
+ fastgetattr(tuple, i + 1, tupdesc, &isnull);
+ if (!isnull)
+ ereport(ERROR,
+
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("trigger modified
virtual generated column value")));
+ }
+ }
+}
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 74eb430f96..d555c30558 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -917,7 +917,8 @@ DefineDomain(CreateDomainStmt *stmt)
defaultExpr = cookDefault(pstate,
constr->raw_expr,
basetypeoid,
basetypeMod,
-
domainName);
+
domainName,
+
0);
/*
* If the expression is just a NULL
constant, we treat it
@@ -2244,7 +2245,8 @@ AlterDomainDefault(List *names, Node *defaultRaw)
defaultExpr = cookDefault(pstate, defaultRaw,
typTup->typbasetype,
typTup->typtypmod,
-
NameStr(typTup->typname));
+
NameStr(typTup->typname),
+ 0);
/*
* If the expression is just a NULL constant, we treat the
command
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 410921cc40..efd9606dd2 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -52,7 +52,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"
@@ -1812,6 +1812,7 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
Expr *checkconstr;
checkconstr = stringToNode(check[i].ccbin);
+ checkconstr =
expand_generated_columns_in_expr(checkconstr, rel);
resultRelInfo->ri_ConstraintExprs[i] =
ExecPrepareExpr(checkconstr, estate);
}
@@ -2290,6 +2291,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/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index e5d2de5330..72daf68c7c 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2819,6 +2819,7 @@ _copyColumnDef(const ColumnDef *from)
COPY_NODE_FIELD(raw_default);
COPY_NODE_FIELD(cooked_default);
COPY_SCALAR_FIELD(identity);
+ COPY_SCALAR_FIELD(generated);
COPY_NODE_FIELD(collClause);
COPY_SCALAR_FIELD(collOid);
COPY_NODE_FIELD(constraints);
@@ -2842,6 +2843,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(exclusions);
COPY_NODE_FIELD(options);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 785dc54d37..5e34d975bb 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2565,6 +2565,7 @@ _equalColumnDef(const ColumnDef *a, const ColumnDef *b)
COMPARE_NODE_FIELD(raw_default);
COMPARE_NODE_FIELD(cooked_default);
COMPARE_SCALAR_FIELD(identity);
+ COMPARE_SCALAR_FIELD(generated);
COMPARE_NODE_FIELD(collClause);
COMPARE_SCALAR_FIELD(collOid);
COMPARE_NODE_FIELD(constraints);
@@ -2586,6 +2587,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(exclusions);
COMPARE_NODE_FIELD(options);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index e0f4befd9f..90088d726b 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2814,6 +2814,7 @@ _outColumnDef(StringInfo str, const ColumnDef *node)
WRITE_NODE_FIELD(raw_default);
WRITE_NODE_FIELD(cooked_default);
WRITE_CHAR_FIELD(identity);
+ WRITE_CHAR_FIELD(generated);
WRITE_NODE_FIELD(collClause);
WRITE_OID_FIELD(collOid);
WRITE_NODE_FIELD(constraints);
@@ -3465,6 +3466,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.y
index 459a227e57..5f9ae2510b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3546,6 +3546,17 @@ ColConstraintElem:
n->location = @1;
$$ = (Node *)n;
}
+ | GENERATED generated_when AS '(' a_expr ')'
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_GENERATED;
+ n->generated_when = $2;
+ n->raw_expr = $5;
+ n->cooked_expr = NULL;
+ n->generated_kind =
ATTRIBUTE_GENERATED_VIRTUAL; /* only option for now */
+ n->location = @1;
+ $$ = (Node *)n;
+ }
| REFERENCES qualified_name opt_column_list key_match
key_actions
{
Constraint *n = makeNode(Constraint);
@@ -3635,6 +3646,7 @@ TableLikeOption:
DEFAULTS { $$ =
CREATE_TABLE_LIKE_DEFAULTS; }
| CONSTRAINTS { $$ =
CREATE_TABLE_LIKE_CONSTRAINTS; }
| IDENTITY_P { $$ =
CREATE_TABLE_LIKE_IDENTITY; }
+ | GENERATED { $$ =
CREATE_TABLE_LIKE_GENERATED; }
| INDEXES { $$ =
CREATE_TABLE_LIKE_INDEXES; }
| STORAGE { $$ =
CREATE_TABLE_LIKE_STORAGE; }
| COMMENTS { $$ =
CREATE_TABLE_LIKE_COMMENTS; }
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 6a9f1b0217..c82feb82ba 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -507,6 +507,14 @@ check_agglevels_and_constraints(ParseState *pstate, Node
*expr)
err = _("grouping operations are not allowed in
partition key expression");
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:
if (isAgg)
@@ -894,6 +902,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc
*wfunc,
case EXPR_KIND_CALL:
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.c
index b2f5e46e3b..78826b1dee 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1846,6 +1846,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_PARTITION_EXPRESSION:
err = _("cannot use subquery in partition key
expression");
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
@@ -3470,6 +3473,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "PARTITION BY";
case EXPR_KIND_CALL:
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.c
index ffae0f3cf3..b2f0414f38 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -557,6 +557,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.
@@ -2292,6 +2301,9 @@ check_srf_call_placement(ParseState *pstate, Node
*last_srf, int location)
case EXPR_KIND_CALL:
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.c
index 5afb363096..6440554f16 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -506,6 +506,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);
@@ -613,6 +614,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef
*column)
saw_nullable = false;
saw_default = false;
saw_identity = false;
+ saw_generated = false;
foreach(clist, column->constraints)
{
@@ -693,6 +695,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;
@@ -777,6 +823,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 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)
+ {
+ 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;
+ }
}
/*
@@ -1024,7 +1114,8 @@ 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;
@@ -1049,6 +1140,9 @@ transformTableLikeClause(CreateStmtContext *cxt,
TableLikeClause *table_like_cla
*/
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.c
index 32e3798972..429b052c6d 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"
@@ -37,6 +38,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 */
@@ -828,6 +830,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)
@@ -838,9 +847,23 @@ 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
+ */
+ new_tle = NULL;
+ }
+ else if (apply_default)
{
Node *new_expr;
@@ -1144,13 +1167,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 */
@@ -3551,6 +3573,96 @@ 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;
+ Oid relid = RelationGetRelid(rel);
+ AttrNumber attnum = v->varattno;
+
+ if (relid && attnum && get_attgenerated(relid, attnum) ==
ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ node = build_column_default(rel, attnum);
+ ChangeVarNodes(node, 1, v->varno, 0);
+ }
+
+ return node;
+ }
+ else
+ return expression_tree_mutator(node,
expand_generated_columns_in_expr_mutator, rel);
+}
+
+
+Expr *
+expand_generated_columns_in_expr(Expr *expr, Relation rel)
+{
+ return (Expr *) expression_tree_mutator((Node *) expr,
+
expand_generated_columns_in_expr_mutator,
+
rel);
+}
+
+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 = getrelid(v->varno, rtable);
+ 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.
@@ -3606,6 +3718,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.c
index e8aa179347..b5dd12122e 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -836,6 +836,38 @@ 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.
+ *
+ * Returns '\0' 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
+ return '\0';
+}
+
/*
* get_attidentity
*
diff --git a/src/backend/utils/cache/relcache.c
b/src/backend/utils/cache/relcache.c
index c081b88b73..754a352028 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -3247,6 +3247,7 @@ RelationBuildLocalRelation(const char *relname,
Form_pg_attribute datt = TupleDescAttr(rel->rd_att, i);
datt->attidentity = satt->attidentity;
+ datt->attgenerated = satt->attgenerated;
datt->attnotnull = satt->attnotnull;
has_not_null |= satt->attnotnull;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d65ea54a69..cff87f314e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1976,6 +1976,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);
@@ -8133,6 +8138,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;
@@ -8189,6 +8195,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int
numTables)
"CASE WHEN
a.attcollation <> t.typcollation "
"THEN a.attcollation
ELSE 0 END AS attcollation, "
"a.attidentity, "
+ "a.attgenerated, "
"pg_catalog.array_to_string(ARRAY("
"SELECT
pg_catalog.quote_ident(option_name) || "
"' ' ||
pg_catalog.quote_literal(option_value) "
@@ -8302,6 +8309,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");
@@ -8318,6 +8326,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));
@@ -8343,6 +8352,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] = (i_attidentity >= 0 ?
*(PQgetvalue(res, j, i_attidentity)) : '\0');
+ tbinfo->attgenerated[j] = (i_attgenerated >= 0 ?
*(PQgetvalue(res, j, i_attgenerated)) : '\0');
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));
@@ -8375,7 +8385,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int
numTables)
tbinfo->dobj.name);
printfPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
-
"pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
+
"pg_catalog.pg_get_expr(adbin, adrelid, true) AS adsrc "
"FROM
pg_catalog.pg_attrdef "
"WHERE adrelid =
'%u'::pg_catalog.oid",
tbinfo->dobj.catId.oid);
@@ -15764,6 +15774,20 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
tbinfo->atttypnames[j]);
}
+ if (has_default)
+ {
+ 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]))
{
@@ -15778,13 +15802,6 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferStr(q,
fmtId(coll->dobj.name));
}
}
-
- if (has_default)
- appendPQExpBuffer(q, " DEFAULT
%s",
-
tbinfo->attrdefs[j]->adef_expr);
-
- if (has_notnull)
- appendPQExpBufferStr(q, " NOT
NULL");
}
}
@@ -18332,6 +18349,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;
@@ -18341,6 +18359,8 @@ fmtCopyColumnList(const TableInfo *ti, PQExpBuffer
buffer)
{
if (attisdropped[i])
continue;
+ if (attgenerated[i])
+ continue;
if (needComma)
appendPQExpBufferStr(buffer, ", ");
appendPQExpBufferStr(buffer, fmtId(attnames[i]));
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 6c18d451ef..8dc48dc35a 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -310,6 +310,7 @@ typedef struct _tableInfo
char *typstorage; /* type storage scheme */
bool *attisdropped; /* true if attr is dropped; don't dump
it */
char *attidentity;
+ char *attgenerated;
int *attlen; /* attribute length,
used by binary_upgrade */
char *attalign; /* attribute align, used by
binary_upgrade */
bool *attislocal; /* true if attr has local definition */
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 5ce3c5d485..d95846fa71 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -1189,6 +1189,16 @@ repairDependencyLoop(DumpableObject **loop,
}
}
+ /* Loop of table with itself, happens with generated columns */
+ if (nLoop == 1)
+ {
+ if (loop[0]->objType == DO_TABLE)
+ {
+ removeObjectDependency(loop[0], loop[0]->dumpId);
+ return;
+ }
+ }
+
/*
* If all the objects are TABLE_DATA items, what we must have is a
* circular set of foreign key constraints (or a single self-referential
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 74730bfc65..c8f2a91341 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -5029,6 +5029,45 @@
role => 1,
section_post_data => 1, }, },
+ 'CREATE TABLE test_table_generated' => {
+ all_runs => 1,
+ catch_all => 'CREATE ... commands',
+ 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 test_table_generated (\E\n
+ \s+\Qcol1 integer NOT NULL,\E\n
+ \s+\Qcol2 integer GENERATED ALWAYS AS (col1 * 2)\E\n
+ \);
+ /xms,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_blobs => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ pg_dumpall_dbprivs => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ with_oids => 1, },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ role => 1,
+ section_post_data => 1, }, },
+
'CREATE STATISTICS extended_stats_no_options' => {
all_runs => 1,
catch_all => 'CREATE ... commands',
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 466a78004b..e1698c2a1b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1692,7 +1692,7 @@ describeOneTableDetails(const char *schemaname,
*/
printfPQExpBuffer(&buf, "SELECT a.attname,");
appendPQExpBufferStr(&buf, "\n pg_catalog.format_type(a.atttypid,
a.atttypmod),"
- "\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, a.attnum,");
@@ -1705,6 +1705,10 @@ describeOneTableDetails(const char *schemaname,
appendPQExpBufferStr(&buf, ",\n a.attidentity");
else
appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS
attidentity");
+ if (pset.sversion >= 110000)
+ appendPQExpBufferStr(&buf, ",\n a.attgenerated");
+ else
+ appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS
attgenerated");
if (tableinfo.relkind == RELKIND_INDEX ||
tableinfo.relkind == RELKIND_PARTITIONED_INDEX)
appendPQExpBufferStr(&buf, ",\n
pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
@@ -1890,6 +1894,7 @@ describeOneTableDetails(const char *schemaname,
if (show_column_details)
{
char *identity;
+ char *generated;
char *default_str = "";
printTableAddCell(&cont, PQgetvalue(res, i, 5), false,
false);
@@ -1897,31 +1902,34 @@ describeOneTableDetails(const char *schemaname,
printTableAddCell(&cont, strcmp(PQgetvalue(res, i, 3),
"t") == 0 ? "not null" : "", false, false);
identity = PQgetvalue(res, i, 6);
+ generated = PQgetvalue(res, i, 7);
- if (!identity[0])
- /* (note: above we cut off the 'default' string
at 128) */
- default_str = PQgetvalue(res, i, 2);
- 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_VIRTUAL)
+ default_str = psprintf("generated always as
(%s)", PQgetvalue(res, i, 2));
+ else
+ /* (note: above we cut off the 'default' string
at 128) */
+ default_str = PQgetvalue(res, i, 2);
- printTableAddCell(&cont, default_str, false, false);
+ printTableAddCell(&cont, default_str, false,
generated[0] ? true : false);
}
/* Expression for index column */
if (tableinfo.relkind == RELKIND_INDEX ||
tableinfo.relkind == RELKIND_PARTITIONED_INDEX)
- printTableAddCell(&cont, PQgetvalue(res, i, 7), false,
false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 8), false,
false);
/* FDW options for foreign table column, only for 9.2 or later
*/
if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion
>= 90200)
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false,
false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 9), false,
false);
/* Storage and Description */
if (verbose)
{
- int firstvcol = 9;
+ int firstvcol = 10;
char *storage = PQgetvalue(res, i, firstvcol);
/* these strings are literal in our syntax, so not
translated. */
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 9bdc63ceb5..39bdbf3d10 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -109,7 +109,8 @@ extern Node *cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
- const char *attname);
+ const char *attname,
+ char attgenerated);
extern void DeleteRelationTuple(Oid relid);
extern void DeleteAttributeTuples(Oid relid);
diff --git a/src/include/catalog/pg_attribute.h
b/src/include/catalog/pg_attribute.h
index 8159383834..56b70466ee 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -136,6 +136,9 @@ CATALOG(pg_attribute,1249) BKI_BOOTSTRAP BKI_WITHOUT_OIDS
BKI_ROWTYPE_OID(75) BK
/* One of the ATTRIBUTE_IDENTITY_* constants below, or '\0' */
char attidentity BKI_DEFAULT("");
+ /* One of the ATTRIBUTE_GENERATED_* constants below, or '\0' */
+ char attgenerated BKI_DEFAULT("");
+
/* Is dropped (ie, logically invisible) or not */
bool attisdropped BKI_DEFAULT(f);
@@ -191,7 +194,7 @@ typedef FormData_pg_attribute *Form_pg_attribute;
* ----------------
*/
-#define Natts_pg_attribute 22
+#define Natts_pg_attribute 23
#define Anum_pg_attribute_attrelid 1
#define Anum_pg_attribute_attname 2
#define Anum_pg_attribute_atttypid 3
@@ -207,13 +210,14 @@ typedef FormData_pg_attribute *Form_pg_attribute;
#define Anum_pg_attribute_attnotnull 13
#define Anum_pg_attribute_atthasdef 14
#define Anum_pg_attribute_attidentity 15
-#define Anum_pg_attribute_attisdropped 16
-#define Anum_pg_attribute_attislocal 17
-#define Anum_pg_attribute_attinhcount 18
-#define Anum_pg_attribute_attcollation 19
-#define Anum_pg_attribute_attacl 20
-#define Anum_pg_attribute_attoptions 21
-#define Anum_pg_attribute_attfdwoptions 22
+#define Anum_pg_attribute_attgenerated 16
+#define Anum_pg_attribute_attisdropped 17
+#define Anum_pg_attribute_attislocal 18
+#define Anum_pg_attribute_attinhcount 19
+#define Anum_pg_attribute_attcollation 20
+#define Anum_pg_attribute_attacl 21
+#define Anum_pg_attribute_attoptions 22
+#define Anum_pg_attribute_attfdwoptions 23
/* ----------------
@@ -228,4 +232,7 @@ typedef FormData_pg_attribute *Form_pg_attribute;
#define ATTRIBUTE_IDENTITY_ALWAYS 'a'
#define ATTRIBUTE_IDENTITY_BY_DEFAULT 'd'
+/* only one option for now */
+#define ATTRIBUTE_GENERATED_VIRTUAL 'v'
+
#endif /* PG_ATTRIBUTE_H */
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 26b1866c69..1098811f9f 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -149,7 +149,7 @@ typedef FormData_pg_class *Form_pg_class;
*/
DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0
f f p r 30 0 t f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
-DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r
22 0 f f f f f f f t n f 3 1 _null_ _null_ _null_));
+DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r
23 0 f f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0
f f p r 29 0 t f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index bbacbe144c..a21323d007 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -647,6 +647,7 @@ typedef struct ColumnDef
Node *raw_default; /* default value (untransformed parse
tree) */
Node *cooked_default; /* default value (transformed expr tree) */
char identity; /* attidentity setting */
+ 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 */
@@ -669,9 +670,10 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_DEFAULTS = 1 << 0,
CREATE_TABLE_LIKE_CONSTRAINTS = 1 << 1,
CREATE_TABLE_LIKE_IDENTITY = 1 << 2,
- CREATE_TABLE_LIKE_INDEXES = 1 << 3,
- CREATE_TABLE_LIKE_STORAGE = 1 << 4,
- CREATE_TABLE_LIKE_COMMENTS = 1 << 5,
+ CREATE_TABLE_LIKE_GENERATED = 1 << 3,
+ CREATE_TABLE_LIKE_INDEXES = 1 << 4,
+ CREATE_TABLE_LIKE_STORAGE = 1 << 5,
+ CREATE_TABLE_LIKE_COMMENTS = 1 << 6,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -2044,6 +2046,7 @@ typedef enum ConstrType /* types of
constraints */
CONSTR_NOTNULL,
CONSTR_DEFAULT,
CONSTR_IDENTITY,
+ CONSTR_GENERATED,
CONSTR_CHECK,
CONSTR_PRIMARY,
CONSTR_UNIQUE,
@@ -2082,7 +2085,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; /* VIRTUAL or other options in the
future */
/* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */
List *keys; /* String nodes naming
referenced column(s) */
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 4e96fa7907..ee4c447f1f 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -68,7 +68,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 /* CALL argument */
+ EXPR_KIND_CALL, /* CALL argument */
+ EXPR_KIND_GENERATED_COLUMN /* generation expression for a column */
} ParseExprKind;
diff --git a/src/include/rewrite/rewriteHandler.h
b/src/include/rewrite/rewriteHandler.h
index 8128199fc3..30093d7eb3 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 Expr *expand_generated_columns_in_expr(Expr *expr, Relation rel);
#endif /* REWRITEHANDLER_H */
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 9731e6f7ae..7be4438a8e 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -86,6 +86,7 @@ extern Oid get_opfamily_proc(Oid opfamily, Oid lefttype, Oid
righttype,
extern char *get_attname(Oid relid, AttrNumber attnum);
extern char *get_relid_attribute_name(Oid relid, AttrNumber attnum);
extern AttrNumber get_attnum(Oid relid, const char *attname);
+extern char get_attgenerated(Oid relid, AttrNumber attnum);
extern char get_attidentity(Oid relid, AttrNumber attnum);
extern Oid get_atttype(Oid relid, AttrNumber attnum);
extern int32 get_atttypmod(Oid relid, AttrNumber attnum);
diff --git a/src/pl/plperl/expected/plperl_trigger.out
b/src/pl/plperl/expected/plperl_trigger.out
index 28011cd9f6..d754c399b3 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.c
index 77c41b2821..7290dc76ec 100644
--- a/src/pl/plperl/plperl.c
+++ b/src/pl/plperl/plperl.c
@@ -1790,6 +1790,11 @@ plperl_modify_tuple(HV *hvTD, TriggerData *tdata,
HeapTuple otup)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot set system attribute
\"%s\"",
key)));
+ if (get_attgenerated(RelationGetRelid(tdata->tg_relation),
attn))
+ 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,
@@ -3040,7 +3045,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.sql
index 624193b9d0..0dc7908a46 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.out
index d7ab8ac6b8..d7c93c0a5a 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.c
index 1e0f3d9d3a..baf79228e0 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"
@@ -954,6 +955,11 @@ PLy_modify_tuple(PLyProcedure *proc, PyObject *pltd,
TriggerData *tdata,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot set system
attribute \"%s\"",
plattstr)));
+ if
(get_attgenerated(RelationGetRelid(tdata->tg_relation), attn))
+ 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.c
index 6c6b16f4d7..648979bed6 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.sql
index 79c24b714b..22465387e0 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.out
index 736671cc1b..9ca3c0025e 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.out
index f1958c3a98..910119e385 100644
--- a/src/pl/tcl/expected/pltcl_setup.out
+++ b/src/pl/tcl/expected/pltcl_setup.out
@@ -59,6 +59,8 @@ CREATE TABLE trigger_test (
);
-- Make certain dropped attributes are handled correctly
ALTER TABLE trigger_test DROP dropme;
+CREATE TABLE trigger_test_generated
+ (i int, j int GENERATED ALWAYS AS (i * 2));
CREATE VIEW trigger_test_view AS SELECT i, v FROM trigger_test;
CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$
if {$TG_table_name eq "trigger_test" && $TG_level eq "ROW" && $TG_op ne
"DELETE"} {
@@ -110,6 +112,12 @@ FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
CREATE TRIGGER statement_trigger
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON trigger_test
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_data(42,'statement trigger');
+CREATE TRIGGER show_trigger_data_trig_before
+BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated
+FOR EACH ROW EXECUTE PROCEDURE trigger_data();
+CREATE TRIGGER show_trigger_data_trig_after
+AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated
+FOR EACH ROW EXECUTE PROCEDURE trigger_data();
CREATE TRIGGER show_trigger_data_view_trig
INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view
FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view');
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c
index 5df4dfdf55..8f1ab15c86 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -3250,6 +3250,13 @@ pltcl_build_tuple_result(Tcl_Interp *interp, Tcl_Obj
**kvObjv, int kvObjc,
errmsg("cannot set system attribute
\"%s\"",
fieldName)));
+ if (call_state->trigdata &&
+
get_attgenerated(RelationGetRelid(call_state->trigdata->tg_relation), attn))
+ ereport(ERROR,
+
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("cannot set generated column
\"%s\"",
+ fieldName)));
+
values[attn - 1] = utf_u2e(Tcl_GetString(kvObjv[i + 1]));
}
diff --git a/src/pl/tcl/sql/pltcl_queries.sql b/src/pl/tcl/sql/pltcl_queries.sql
index 71c1238bd2..f6e1147dcb 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.sql
index 56a90dc844..7e6ed699e3 100644
--- a/src/pl/tcl/sql/pltcl_setup.sql
+++ b/src/pl/tcl/sql/pltcl_setup.sql
@@ -68,6 +68,9 @@ CREATE TABLE trigger_test (
-- Make certain dropped attributes are handled correctly
ALTER TABLE trigger_test DROP dropme;
+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.out
index 3f405c94ce..5d47e22981 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -113,6 +113,52 @@ SELECT * FROM test_like_id_3; -- identity was copied and
applied
(1 row)
DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
+CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2));
+\d test_like_gen_1
+ Table "public.test_like_gen_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+
+INSERT INTO test_like_gen_1 (a) VALUES (1);
+SELECT * FROM test_like_gen_1;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1);
+\d test_like_gen_2
+ Table "public.test_like_gen_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+
+INSERT INTO test_like_gen_2 (a) VALUES (1);
+SELECT * FROM test_like_gen_2;
+ a | b
+---+---
+ 1 |
+(1 row)
+
+CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);
+\d test_like_gen_3
+ Table "public.test_like_gen_3"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+
+INSERT INTO test_like_gen_3 (a) VALUES (1);
+SELECT * FROM test_like_gen_3;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies
indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/expected/generated.out
b/src/test/regress/expected/generated.out
new file mode 100644
index 0000000000..e11b3f585e
--- /dev/null
+++ b/src/test/regress/expected/generated.out
@@ -0,0 +1,462 @@
+-- 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)
+
+\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
+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
+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
+-- 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
+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;
+-- 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
+(3 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 gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b *
2));
+INSERT INTO gtest11 VALUES (1, 10), (2, 20);
+GRANT SELECT (a, c) ON gtest11 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 gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS
(gf1(b)));
+INSERT INTO gtest12 VALUES (1, 10), (2, 20);
+GRANT SELECT (a, c) ON gtest12 TO regress_user11;
+SET ROLE regress_user11;
+SELECT a, b FROM gtest11; -- not allowed
+ERROR: permission denied for table gtest11
+SELECT a, c FROM gtest11; -- allowed
+ a | c
+---+----
+ 1 | 20
+ 2 | 40
+(2 rows)
+
+SELECT gf1(10); -- not allowed
+ERROR: permission denied for function gf1
+SELECT a, c FROM gtest12; -- FIXME: should be allowed
+ERROR: permission denied for function gf1
+RESET ROLE;
+DROP TABLE gtest11, gtest12;
+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 gtest21 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a,
0)) not null);
+INSERT INTO gtest21 (a) VALUES (1); -- ok
+INSERT INTO gtest21 (a) VALUES (0); -- violates constraint
+ERROR: new row for relation "gtest21" violates check constraint
"gtest21_b_check"
+DETAIL: Failing row contains (0).
+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a,
0)));
+ALTER TABLE gtest21a ALTER COLUMN b SET NOT NULL; -- error
+ERROR: cannot use SET NOT NULL on generated column "b"
+HINT: Add a CHECK constraint instead.
+ALTER TABLE gtest21a ALTER COLUMN b DROP NOT NULL; -- error
+ERROR: cannot use DROP NOT NULL on generated column "b"
+-- 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 ON gtest22c (b); -- error
+ERROR: index creation on virtual generated columns is not supported
+CREATE INDEX ON gtest22c ((b * 2)); -- error
+ERROR: index creation on virtual generated columns is not supported
+CREATE INDEX ON gtest22c (a) WHERE b > 0; -- error
+ERROR: index creation on virtual generated columns is not supported
+-- foreign keys
+CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
REFERENCES gtest23a (x) ON UPDATE CASCADE);
+ERROR: invalid ON UPDATE 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) ON DELETE SET NULL);
+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: foreign key constraints on virtual generated columns are not supported
+DROP TABLE gtest23a;
+-- 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;
+-- 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 virtual generated column in partition key is not supported
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED
ALWAYS AS (f2 * 2)) PARTITION BY RANGE ((f3 * 3));
+ERROR: using virtual generated column in partition key is not supported
+-- 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
diff --git a/src/test/regress/parallel_schedule
b/src/test/regress/parallel_schedule
index ad9434fb87..358f1e7121 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
+test: identity generated partition_join partition_prune reloptions hash_part
indexing
# event triggers cannot run concurrently with any test that runs DDL
test: event_trigger
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 27cd49845e..c77291344a 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -180,6 +180,7 @@ test: largeobject
test: with
test: xml
test: identity
+test: generated
test: partition_join
test: partition_prune
test: reloptions
diff --git a/src/test/regress/sql/create_table_like.sql
b/src/test/regress/sql/create_table_like.sql
index 557040bbe7..2ae96e3d68 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -51,6 +51,20 @@ CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING
IDENTITY);
SELECT * FROM test_like_id_3; -- identity was copied and applied
DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
+CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2));
+\d test_like_gen_1
+INSERT INTO test_like_gen_1 (a) VALUES (1);
+SELECT * FROM test_like_gen_1;
+CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1);
+\d test_like_gen_2
+INSERT INTO test_like_gen_2 (a) VALUES (1);
+SELECT * FROM test_like_gen_2;
+CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);
+\d test_like_gen_3
+INSERT INTO test_like_gen_3 (a) VALUES (1);
+SELECT * FROM test_like_gen_3;
+DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3;
+
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies
indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/sql/generated.sql
b/src/test/regress/sql/generated.sql
new file mode 100644
index 0000000000..287462897b
--- /dev/null
+++ b/src/test/regress/sql/generated.sql
@@ -0,0 +1,277 @@
+-- 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;
+
+\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;
+
+-- 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
+\.
+
+COPY gtest1 (a, b) FROM stdin;
+
+SELECT * FROM gtest1 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 gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b *
2));
+INSERT INTO gtest11 VALUES (1, 10), (2, 20);
+GRANT SELECT (a, c) ON gtest11 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 gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS
(gf1(b)));
+INSERT INTO gtest12 VALUES (1, 10), (2, 20);
+GRANT SELECT (a, c) ON gtest12 TO regress_user11;
+
+SET ROLE regress_user11;
+SELECT a, b FROM gtest11; -- not allowed
+SELECT a, c FROM gtest11; -- allowed
+SELECT gf1(10); -- not allowed
+SELECT a, c FROM gtest12; -- FIXME: should be allowed
+RESET ROLE;
+
+DROP TABLE gtest11, gtest12;
+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 gtest21 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a,
0)) not null);
+INSERT INTO gtest21 (a) VALUES (1); -- ok
+INSERT INTO gtest21 (a) VALUES (0); -- violates constraint
+
+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a,
0)));
+ALTER TABLE gtest21a ALTER COLUMN b SET NOT NULL; -- error
+ALTER TABLE gtest21a ALTER COLUMN b DROP NOT NULL; -- error
+
+-- 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 ON gtest22c (b); -- error
+CREATE INDEX ON gtest22c ((b * 2)); -- error
+CREATE INDEX ON gtest22c (a) WHERE b > 0; -- error
+
+-- foreign keys
+CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
REFERENCES gtest23a (x) ON UPDATE CASCADE);
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
REFERENCES gtest23a (x) ON DELETE SET NULL);
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
REFERENCES gtest23a (x));
+DROP TABLE gtest23a;
+
+-- 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;
+
+-- 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));
+
+-- 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;
base-commit: 945f71db845262e7491b5fe4403b01147027576b
--
2.16.1
