Hi,
Attached is a complete patch and also contain a fix for your comments
regards
Surafel
From 0a1e51b6fcce03014e3ee355d42443add4da7a28 Mon Sep 17 00:00:00 2001
From: Surafel Temesgen <[email protected]>
Date: Wed, 1 Jan 2020 13:27:25 +0300
Subject: [PATCH] system versioned temporal table
---
doc/src/sgml/ref/alter_table.sgml | 23 ++
doc/src/sgml/ref/create_table.sgml | 47 ++++
doc/src/sgml/ref/select.sgml | 37 +++
src/backend/access/common/tupdesc.c | 4 +
src/backend/commands/copy.c | 5 +
src/backend/commands/tablecmds.c | 49 +++-
src/backend/commands/view.c | 6 +
src/backend/executor/nodeModifyTable.c | 180 ++++++++++++++
src/backend/nodes/copyfuncs.c | 31 +++
src/backend/nodes/equalfuncs.c | 28 +++
src/backend/nodes/makefuncs.c | 120 ++++++++++
src/backend/nodes/outfuncs.c | 1 +
src/backend/optimizer/plan/planner.c | 8 +
src/backend/optimizer/plan/subselect.c | 19 ++
src/backend/optimizer/util/plancat.c | 188 +++++++++++++++
src/backend/parser/analyze.c | 9 +
src/backend/parser/gram.y | 220 +++++++++++++-----
src/backend/parser/parse_clause.c | 73 +++++-
src/backend/parser/parse_relation.c | 4 +
src/backend/parser/parse_utilcmd.c | 133 ++++++++++-
src/backend/tcop/utility.c | 61 +++++
src/backend/utils/cache/relcache.c | 3 +
src/bin/pg_dump/pg_dump.c | 4 +
src/bin/psql/describe.c | 6 +-
src/include/access/tupdesc.h | 1 +
src/include/catalog/pg_attribute.h | 3 +
src/include/executor/nodeModifyTable.h | 2 +
src/include/nodes/makefuncs.h | 6 +
src/include/nodes/nodes.h | 2 +
src/include/nodes/parsenodes.h | 42 +++-
src/include/optimizer/plancat.h | 4 +-
src/include/parser/kwlist.h | 3 +
src/include/parser/parse_node.h | 2 +-
.../expected/system_versioned_table.out | 188 +++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
.../regress/sql/system_versioned_table.sql | 104 +++++++++
37 files changed, 1553 insertions(+), 66 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8403c797e2..6182cda9cb 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -41,7 +41,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
ADD [ COLUMN ] [ IF NOT EXISTS ] <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
+ ADD SYSTEM VERSIONING
DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="parameter">column_name</replaceable> [ RESTRICT | CASCADE ]
+ DROP SYSTEM VERSIONING
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> [ SET DATA ] TYPE <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ USING <replaceable class="parameter">expression</replaceable> ]
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT
@@ -158,6 +160,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>ADD SYSTEM VERSIONING</literal></term>
+ <listitem>
+ <para>
+ This form adds system versioning columns to the table, using default column
+ name of system versioning which is StartTime and EndtTime.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>DROP COLUMN [ IF EXISTS ]</literal></term>
<listitem>
@@ -177,6 +189,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>DROP SYSTEM VERSIONING</literal></term>
+ <listitem>
+ <para>
+ This form drops system versioning columns from a table. Indexes and
+ table constraints involving the columns will be automatically
+ dropped as well.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>SET DATA TYPE</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 4a2b6f0dae..cd1035b8d9 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -31,6 +31,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
[ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
[ USING <replaceable class="parameter">method</replaceable> ]
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
+[ WITH SYSTEM VERSIONING ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -67,8 +68,11 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
DEFAULT <replaceable>default_expr</replaceable> |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
+ GENERATED ALWAYS AS ROW START |
+ GENERATED ALWAYS AS ROW END |
UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
+ PERIOD FOR SYSTEM_TIME ( <replaceable class="parameter">row_start_time_column</replaceable>, <replaceable class="parameter">row_end_time_column</replaceable> ) |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
@@ -861,6 +865,28 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>GENERATED ALWAYS AS ROW START</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 the
+ row insertion time will be returned.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>GENERATED ALWAYS AS ROW END</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 the
+ row deletion time will be returned.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>UNIQUE</literal> (column constraint)</term>
<term><literal>UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
@@ -953,6 +979,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>PRIMARY PERIOD FOR SYSTEM_TIME ( <replaceable class="parameter">row_start_time_column</replaceable>, <replaceable class="parameter">row_end_time_column</replaceable> )</literal></term>
+ <listitem>
+ <para>
+ It specifies a pair of column that hold the row start
+ time and row end time column name.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createtable-exclude">
<term><literal>EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ]</literal></term>
<listitem>
@@ -1208,6 +1244,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>WITH SYSTEM VERSIONING</literal></term>
+ <listitem>
+ <para>
+ It specifies the table is system versioned temporal table.
+ If period columns is not specified the default column for
+ system versioned is created which are StartTime and EndTime.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>ON COMMIT</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 691e402803..ed228b781c 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -60,6 +60,9 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
[ LATERAL ] ROWS FROM( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
[ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
<replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]
+<replaceable class="parameter">table_name</replaceable> [ FOR SYSTEM_TIME AS OF ] <replaceable class="parameter">expression</replaceable>
+<replaceable class="parameter">table_name</replaceable> [ FOR SYSTEM_TIME BETWEEN ] <replaceable class="parameter">start_time</replaceable> [AND] <replaceable class="parameter">end_time</replaceable>
+<replaceable class="parameter">table_name</replaceable> [ FOR SYSTEM_TIME FROM ] <replaceable class="parameter">start_time</replaceable> [TO] <replaceable class="parameter">end_time</replaceable>
<phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
@@ -534,6 +537,40 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>FOR SYSTEM_TIME AS OF <replaceable class="parameter">expression</replaceable></literal></term>
+ <listitem>
+ <para>
+ Is specifies to see the table as where current as <replaceable class="parameter">
+ expression</replaceable> point in time.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>FOR SYSTEM_TIME BETWEEN <replaceable class="parameter">start_time</replaceable> [AND] <replaceable class="parameter">end_time</replaceable></literal></term>
+ <listitem>
+ <para>
+ Is specifies to see the table as where current at any point between
+ <replaceable class="parameter">start_time</replaceable> and
+ <replaceable class="parameter">end_time</replaceable> including
+ <replaceable class="parameter">start_time</replaceable> but excluding
+ <replaceable class="parameter">end_time</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>FOR SYSTEM_TIME FROM <replaceable class="parameter">start_time</replaceable> [TO] <replaceable class="parameter">end_time</replaceable></literal></term>
+ <listitem>
+ <para>
+ Is specifies to see the table as where current at any point between
+ <replaceable class="parameter">start_time</replaceable> and
+ <replaceable class="parameter">end_time</replaceable> inclusively.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">join_type</replaceable></term>
<listitem>
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index 6bc4e4c036..bacd78deb5 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -167,6 +167,7 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
cpy->has_not_null = constr->has_not_null;
cpy->has_generated_stored = constr->has_generated_stored;
+ cpy->is_system_versioned = constr->is_system_versioned;
if ((cpy->num_defval = constr->num_defval) > 0)
{
@@ -484,6 +485,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (constr1->has_generated_stored != constr2->has_generated_stored)
return false;
+ if (constr1->is_system_versioned != constr2->is_system_versioned)
+ return false;
n = constr1->num_defval;
if (n != (int) constr2->num_defval)
return false;
@@ -864,6 +867,7 @@ BuildDescForRelation(List *schema)
constr->has_not_null = true;
constr->has_generated_stored = false;
+ constr->is_system_versioned = false;
constr->defval = NULL;
constr->missing = NULL;
constr->num_defval = 0;
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 42a147b67d..2b6b5228bb 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -3223,6 +3223,11 @@ CopyFrom(CopyState cstate)
resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated_stored)
ExecComputeStoredGenerated(estate, myslot);
+ /* Set system time columns */
+ if (resultRelInfo->ri_RelationDesc->rd_att->constr &&
+ resultRelInfo->ri_RelationDesc->rd_att->constr->is_system_versioned)
+ ExecSetRowStartTime(estate, myslot);
+
/*
* If the target is a plain table, check the constraints of
* the tuple.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5b882f80bf..081682219b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -76,6 +76,7 @@
#include "parser/parser.h"
#include "partitioning/partbounds.h"
#include "partitioning/partdesc.h"
+#include "optimizer/plancat.h"
#include "pgstat.h"
#include "rewrite/rewriteDefine.h"
#include "rewrite/rewriteHandler.h"
@@ -1514,6 +1515,7 @@ ExecuteTruncate(TruncateStmt *stmt)
bool recurse = rv->inh;
Oid myrelid;
LOCKMODE lockmode = AccessExclusiveLock;
+ TupleDesc tupdesc;
myrelid = RangeVarGetRelidExtended(rv, lockmode,
0, RangeVarCallbackForTruncate,
@@ -1522,6 +1524,14 @@ ExecuteTruncate(TruncateStmt *stmt)
/* open the relation, we already hold a lock on it */
rel = table_open(myrelid, NoLock);
+ tupdesc = RelationGetDescr(rel);
+
+ /* throw error for system versioned table */
+ if (tupdesc->constr && tupdesc->constr->is_system_versioned)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot truncate system versioned table")));
+
/* don't throw error for "TRUNCATE foo, foo" */
if (list_member_oid(relids, myrelid))
{
@@ -3584,6 +3594,7 @@ AlterTableGetLockLevel(List *cmds)
*/
case AT_AddColumn: /* may rewrite heap, in some cases and visible
* to SELECT */
+ case AT_AddSystemVersioning:
case AT_SetTableSpace: /* must rewrite heap */
case AT_AlterColumnType: /* must rewrite heap */
cmd_lockmode = AccessExclusiveLock;
@@ -3614,6 +3625,7 @@ AlterTableGetLockLevel(List *cmds)
* Subcommands that may be visible to concurrent SELECTs
*/
case AT_DropColumn: /* change visible to SELECT */
+ case AT_DropSystemVersioning: /* change visible to SELECT */
case AT_AddColumnToView: /* CREATE VIEW */
case AT_DropOids: /* used to equiv to DropColumn */
case AT_EnableAlwaysRule: /* may change SELECT rules */
@@ -6123,6 +6135,12 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
errmsg("column \"%s\" of relation \"%s\" is an identity column",
colName, RelationGetRelationName(rel))));
+ if (attTup->attgenerated == ATTRIBUTE_ROW_START_TIME || attTup->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" of relation \"%s\" is system time column",
+ colName, RelationGetRelationName(rel))));
+
/*
* Check that the attribute is not in a primary key
*
@@ -6500,6 +6518,12 @@ ATExecAddIdentity(Relation rel, const char *colName,
errmsg("cannot alter system column \"%s\"",
colName)));
+ if (attTup->attgenerated == ATTRIBUTE_ROW_START_TIME || attTup->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" of relation \"%s\" is system time column",
+ colName, RelationGetRelationName(rel))));
+
/*
* Creating a column as identity implies NOT NULL, so adding the identity
* to an existing column that is not NOT NULL would create a state that
@@ -6600,6 +6624,12 @@ ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmod
errmsg("column \"%s\" of relation \"%s\" is not an identity column",
colName, RelationGetRelationName(rel))));
+ if (attTup->attgenerated == ATTRIBUTE_ROW_START_TIME || attTup->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" of relation \"%s\" is system time column",
+ colName, RelationGetRelationName(rel))));
+
if (generatedEl)
{
attTup->attidentity = defGetInt32(generatedEl);
@@ -6846,6 +6876,12 @@ ATExecSetOptions(Relation rel, const char *colName, Node *options,
errmsg("cannot alter system column \"%s\"",
colName)));
+ if (attrtuple->attgenerated == ATTRIBUTE_ROW_START_TIME || attrtuple->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" of relation \"%s\" is system time column",
+ colName, RelationGetRelationName(rel))));
+
/* Generate new proposed attoptions (text array) */
datum = SysCacheGetAttr(ATTNAME, tuple, Anum_pg_attribute_attoptions,
&isnull);
@@ -10624,6 +10660,11 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot alter type of column \"%s\" twice",
colName)));
+ if (attTup->attgenerated == ATTRIBUTE_ROW_START_TIME || attTup->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" of relation \"%s\" is system time column",
+ colName, RelationGetRelationName(rel))));
/* Look up the target type (should not fail, since prep found it) */
typeTuple = typenameType(NULL, typeName, &targettypmod);
@@ -11607,6 +11648,12 @@ ATExecAlterColumnGenericOptions(Relation rel,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot alter system column \"%s\"", colName)));
+ if (atttableform->attgenerated == ATTRIBUTE_ROW_START_TIME || atttableform->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" of relation \"%s\" is system time column",
+ colName, RelationGetRelationName(rel))));
+
/* Initialize buffers for new tuple values */
memset(repl_val, 0, sizeof(repl_val));
@@ -15079,7 +15126,7 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
* Generated columns cannot work: They are computed after BEFORE
* triggers, but partition routing is done before all triggers.
*/
- if (attform->attgenerated)
+ if (attform->attgenerated && attform->attgenerated != ATTRIBUTE_ROW_START_TIME && attform->attgenerated != ATTRIBUTE_ROW_END_TIME)
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("cannot use generated column in partition key"),
diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c
index 9b5148093b..27559300eb 100644
--- a/src/backend/commands/view.c
+++ b/src/backend/commands/view.c
@@ -25,6 +25,7 @@
#include "nodes/nodeFuncs.h"
#include "parser/analyze.h"
#include "parser/parse_relation.h"
+#include "optimizer/plancat.h"
#include "rewrite/rewriteDefine.h"
#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
@@ -420,6 +421,11 @@ DefineView(ViewStmt *stmt, const char *queryString,
viewParse = parse_analyze(rawstmt, queryString, NULL, 0, NULL);
+ /*
+ * check and filter out historical data if necessary.
+ */
+ add_history_data_filter(viewParse);
+
/*
* The grammar should ensure that the result is a single SELECT Query.
* However, it doesn't forbid SELECT INTO, so we have to check for that.
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 9ba1d78344..1502a59de0 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -330,6 +330,129 @@ ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot)
MemoryContextSwitchTo(oldContext);
}
+/*
+ * Set row start time in row start time column for a tuple.
+ */
+void
+ExecSetRowStartTime(EState *estate, TupleTableSlot *slot)
+{
+ ResultRelInfo *resultRelInfo = estate->es_result_relation_info;
+ Relation rel = resultRelInfo->ri_RelationDesc;
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int natts = tupdesc->natts;
+ MemoryContext oldContext;
+ Datum *values;
+ bool *nulls;
+
+ Assert(tupdesc->constr && tupdesc->constr->is_system_versioned);
+
+ oldContext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
+
+ values = palloc(sizeof(*values) * natts);
+ nulls = palloc(sizeof(*nulls) * natts);
+
+ slot_getallattrs(slot);
+ memcpy(nulls, slot->tts_isnull, sizeof(*nulls) * natts);
+
+ for (int i = 0; i < natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ /*
+ * We set infinity for row end time column for a tuple because row end
+ * time is not yet known.
+ */
+ if (attr->attgenerated == ATTRIBUTE_ROW_START_TIME)
+ {
+ Datum val;
+
+ val = GetCurrentTransactionStartTimestamp();
+
+ values[i] = val;
+ nulls[i] = false;
+ }
+ else if (attr->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ {
+ Datum val;
+
+ val = DirectFunctionCall3(timestamp_in,
+ CStringGetDatum("infinity"),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1));
+
+ values[i] = val;
+ nulls[i] = false;
+ }
+ else
+ {
+ if (!nulls[i])
+ values[i] = datumCopy(slot->tts_values[i], attr->attbyval, attr->attlen);
+ }
+ }
+
+ ExecClearTuple(slot);
+ memcpy(slot->tts_values, values, sizeof(*values) * natts);
+ memcpy(slot->tts_isnull, nulls, sizeof(*nulls) * natts);
+ ExecStoreVirtualTuple(slot);
+ ExecMaterializeSlot(slot);
+
+ MemoryContextSwitchTo(oldContext);
+}
+
+/*
+ * Set row end time in row end time columns for a tuple.
+ */
+void
+ExecSetRowEndTime(EState *estate, TupleTableSlot *slot)
+{
+ ResultRelInfo *resultRelInfo = estate->es_result_relation_info;
+ Relation rel = resultRelInfo->ri_RelationDesc;
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int natts = tupdesc->natts;
+ MemoryContext oldContext;
+ Datum *values;
+ bool *nulls;
+
+ Assert(tupdesc->constr && tupdesc->constr->is_system_versioned);
+
+ oldContext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
+
+ values = palloc(sizeof(*values) * natts);
+ nulls = palloc(sizeof(*nulls) * natts);
+
+ slot_getallattrs(slot);
+ memcpy(nulls, slot->tts_isnull, sizeof(*nulls) * natts);
+
+ for (int i = 0; i < natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ {
+ Datum val;
+
+ val = GetCurrentTransactionStartTimestamp();
+
+ values[i] = val;
+ nulls[i] = false;
+ }
+ else
+ {
+ if (!nulls[i])
+ values[i] = datumCopy(slot->tts_values[i], attr->attbyval, attr->attlen);
+ }
+
+ }
+
+ ExecClearTuple(slot);
+ memcpy(slot->tts_values, values, sizeof(*values) * natts);
+ memcpy(slot->tts_isnull, nulls, sizeof(*nulls) * natts);
+ ExecStoreVirtualTuple(slot);
+ ExecMaterializeSlot(slot);
+
+ MemoryContextSwitchTo(oldContext);
+}
+
/* ----------------------------------------------------------------
* ExecInsert
*
@@ -429,6 +552,13 @@ ExecInsert(ModifyTableState *mtstate,
resultRelationDesc->rd_att->constr->has_generated_stored)
ExecComputeStoredGenerated(estate, slot);
+ /*
+ * Set row start time
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->is_system_versioned)
+ ExecSetRowStartTime(estate, slot);
+
/*
* Check any RLS WITH CHECK policies.
*
@@ -755,6 +885,31 @@ ExecDelete(ModifyTableState *mtstate,
}
else
{
+ /*
+ * Set row end time and insert
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->is_system_versioned)
+ {
+ TupleTableSlot *sslot = NULL;
+
+ sslot = table_slot_create(resultRelationDesc, NULL);
+
+ if (!table_tuple_fetch_row_version(resultRelationDesc, tupleid, SnapshotAny,
+ sslot))
+ {
+ elog(ERROR, "failed to fetch tuple");
+ }
+ else
+ {
+ ExecSetRowEndTime(estate, sslot);
+ table_tuple_insert(resultRelationDesc, sslot,
+ estate->es_output_cid,
+ 0, NULL);
+ }
+ ExecDropSingleTupleTableSlot(sslot);
+ }
+
/*
* delete the tuple
*
@@ -1127,6 +1282,31 @@ ExecUpdate(ModifyTableState *mtstate,
resultRelationDesc->rd_att->constr->has_generated_stored)
ExecComputeStoredGenerated(estate, slot);
+ /*
+ * Set row end time and insert
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->is_system_versioned)
+ {
+ TupleTableSlot *sslot = NULL;
+
+ sslot = table_slot_create(resultRelationDesc, NULL);
+
+ if (!table_tuple_fetch_row_version(resultRelationDesc, tupleid, SnapshotAny,
+ sslot))
+ {
+ elog(ERROR, "failed to fetch tuple");
+ }
+ else
+ {
+ ExecSetRowEndTime(estate, sslot);
+ table_tuple_insert(resultRelationDesc, sslot,
+ estate->es_output_cid,
+ 0, NULL);
+ }
+ ExecDropSingleTupleTableSlot(sslot);
+ }
+
/*
* Check any RLS UPDATE WITH CHECK policies
*
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index a9b8b84b8f..4af36a1688 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3354,6 +3354,7 @@ CopyCreateStmtFields(const CreateStmt *from, CreateStmt *newnode)
COPY_STRING_FIELD(tablespacename);
COPY_STRING_FIELD(accessMethod);
COPY_SCALAR_FIELD(if_not_exists);
+ COPY_SCALAR_FIELD(systemVersioned);
}
static CreateStmt *
@@ -4752,6 +4753,30 @@ _copyForeignKeyCacheInfo(const ForeignKeyCacheInfo *from)
return newnode;
}
+static RowTime *
+_copyRowTime(const RowTime * from)
+{
+ RowTime *newnode = makeNode(RowTime);
+
+ COPY_STRING_FIELD(start_time);
+ COPY_STRING_FIELD(end_time);
+
+ return newnode;
+}
+
+static TemporalClause *
+_copyTemporalClause(const TemporalClause * from)
+{
+ TemporalClause *newnode = makeNode(TemporalClause);
+
+ COPY_SCALAR_FIELD(kind);
+ COPY_NODE_FIELD(from);
+ COPY_NODE_FIELD(to);
+ COPY_NODE_FIELD(relation);
+
+ return newnode;
+}
+
/*
* copyObjectImpl -- implementation of copyObject(); see nodes/nodes.h
@@ -5640,6 +5665,12 @@ copyObjectImpl(const void *from)
case T_PartitionCmd:
retval = _copyPartitionCmd(from);
break;
+ case T_RowTime:
+ retval = _copyRowTime(from);
+ break;
+ case T_TemporalClause:
+ retval = _copyTemporalClause(from);
+ break;
/*
* MISCELLANEOUS NODES
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 2fcd4a3467..0578b24bf3 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1245,6 +1245,7 @@ _equalCreateStmt(const CreateStmt *a, const CreateStmt *b)
COMPARE_STRING_FIELD(tablespacename);
COMPARE_STRING_FIELD(accessMethod);
COMPARE_SCALAR_FIELD(if_not_exists);
+ COMPARE_SCALAR_FIELD(systemVersioned);
return true;
}
@@ -2914,6 +2915,27 @@ _equalPartitionCmd(const PartitionCmd *a, const PartitionCmd *b)
return true;
}
+static bool
+_equalRowTime(const RowTime * a, const RowTime * b)
+{
+ COMPARE_STRING_FIELD(start_time);
+ COMPARE_STRING_FIELD(end_time);
+
+ return true;
+}
+
+static bool
+_equalTemporalClause(const TemporalClause * a, const TemporalClause * b)
+{
+
+ COMPARE_SCALAR_FIELD(kind);
+ COMPARE_NODE_FIELD(from);
+ COMPARE_NODE_FIELD(to);
+ COMPARE_NODE_FIELD(relation);
+
+ return true;
+}
+
/*
* Stuff from pg_list.h
*/
@@ -3733,6 +3755,12 @@ equal(const void *a, const void *b)
case T_PartitionCmd:
retval = _equalPartitionCmd(a, b);
break;
+ case T_RowTime:
+ retval = _equalRowTime(a, b);
+ break;
+ case T_TemporalClause:
+ retval = _equalTemporalClause(a, b);
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 18466ac568..b454e7ab44 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -809,3 +809,123 @@ makeVacuumRelation(RangeVar *relation, Oid oid, List *va_cols)
v->va_cols = va_cols;
return v;
}
+
+Node *
+makeAndExpr(Node *lexpr, Node *rexpr, int location)
+{
+ Node *lexp = lexpr;
+
+ /* Look through AEXPR_PAREN nodes so they don't affect flattening */
+ while (IsA(lexp, A_Expr) &&
+ ((A_Expr *) lexp)->kind == AEXPR_PAREN)
+ lexp = ((A_Expr *) lexp)->lexpr;
+ /* Flatten "a AND b AND c ..." to a single BoolExpr on sight */
+ if (IsA(lexp, BoolExpr))
+ {
+ BoolExpr *blexpr = (BoolExpr *) lexp;
+
+ if (blexpr->boolop == AND_EXPR)
+ {
+ blexpr->args = lappend(blexpr->args, rexpr);
+ return (Node *) blexpr;
+ }
+ }
+ return (Node *) makeBoolExpr(AND_EXPR, list_make2(lexpr, rexpr), location);
+}
+
+/*
+ * makeColumnRefFromName -
+ * creates a ColumnRef node using column name
+ */
+ColumnRef *
+makeColumnRefFromName(char *colname)
+{
+ ColumnRef *c = makeNode(ColumnRef);
+
+ c->location = 0;
+ c->fields = lcons(makeString(colname), NIL);
+
+ return c;
+}
+
+/*
+ * makeConstraint -
+ * create a constraint node
+ */
+Constraint *
+makeConstraint(ConstrType type)
+{
+ Constraint *c = makeNode(Constraint);
+
+ c->contype = type;
+ c->raw_expr = NULL;
+ c->cooked_expr = NULL;
+ c->location = 1;
+
+ return c;
+}
+
+/*
+ * makeSystemColumnDef -
+ * create a ColumnDef node for system column
+ */
+ColumnDef *
+makeSystemColumnDef(char *name)
+{
+ ColumnDef *n = makeNode(ColumnDef);
+
+ if (strcmp(name, "StartTime") == 0)
+ {
+ n->colname = "StartTime";
+ n->constraints = list_make1((Node *) makeConstraint(CONSTR_ROW_START_TIME));
+ }
+ else
+ {
+ n->colname = "EndTime";
+ n->constraints = list_make1((Node *) makeConstraint(CONSTR_ROW_END_TIME));
+ }
+ n->typeName = makeTypeNameFromNameList(list_make2(makeString("pg_catalog"),
+ makeString("timestamp")));
+ n->inhcount = 0;
+ n->is_local = true;
+ n->is_from_type = false;
+ n->storage = 0;
+ n->raw_default = NULL;
+ n->cooked_default = NULL;
+ n->collOid = InvalidOid;
+ n->location = 1;
+
+ return n;
+}
+
+/*
+ * makeAddColCmd -
+ * create add column AlterTableCmd node
+ */
+AlterTableCmd *
+makeAddColCmd(ColumnDef *coldef)
+{
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+
+ n->subtype = AT_AddColumn;
+ n->def = (Node *) coldef;
+ n->missing_ok = false;
+
+ return n;
+}
+
+/*
+ * makeDropColCmd -
+ * create drop column AlterTableCmd node
+ */
+AlterTableCmd *
+makeDropColCmd(char *name)
+{
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+
+ n->subtype = AT_DropColumn;
+ n->name = name;
+ n->missing_ok = false;
+
+ return n;
+}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index ac02e5ec8d..8efd8ce356 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2599,6 +2599,7 @@ _outCreateStmtInfo(StringInfo str, const CreateStmt *node)
WRITE_STRING_FIELD(tablespacename);
WRITE_STRING_FIELD(accessMethod);
WRITE_BOOL_FIELD(if_not_exists);
+ WRITE_BOOL_FIELD(systemVersioned);
}
static void
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index cb54b15507..68ac22b215 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -56,6 +56,8 @@
#include "optimizer/tlist.h"
#include "parser/analyze.h"
#include "parser/parse_agg.h"
+#include "parser/parse_clause.h"
+#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "partitioning/partdesc.h"
#include "rewrite/rewriteManip.h"
@@ -645,6 +647,12 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
if (parse->cteList)
SS_process_ctes(root);
+ /*
+ * Check and filter out historical data if necessary.
+ */
+ if (parse->commandType == CMD_SELECT)
+ add_history_data_filter(parse);
+
/*
* If the FROM clause is empty, replace it with a dummy RTE_RESULT RTE, so
* that we don't need so many special cases to deal with that situation.
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 48b62a55de..55f2465cf3 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -27,6 +27,7 @@
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/paramassign.h"
+#include "optimizer/plancat.h"
#include "optimizer/pathnode.h"
#include "optimizer/planmain.h"
#include "optimizer/planner.h"
@@ -850,6 +851,15 @@ SS_process_ctes(PlannerInfo *root)
*/
if (cte->cterefcount == 0 && cmdType == CMD_SELECT)
{
+ Query *query;
+
+ query = (Query *) cte->ctequery;
+
+ /*
+ * check and filter out historical data if necessary.
+ */
+ add_history_data_filter(query);
+
/* Make a dummy entry in cte_plan_ids */
root->cte_plan_ids = lappend_int(root->cte_plan_ids, -1);
continue;
@@ -896,6 +906,15 @@ SS_process_ctes(PlannerInfo *root)
!contain_outer_selfref(cte->ctequery)) &&
!contain_volatile_functions(cte->ctequery))
{
+ Query *query;
+
+ query = (Query *) cte->ctequery;
+
+ /*
+ * check and filter out historical data if necessary.
+ */
+ add_history_data_filter(query);
+
inline_cte(root, cte);
/* Make a dummy entry in cte_plan_ids */
root->cte_plan_ids = lappend_int(root->cte_plan_ids, -1);
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 5e889d1861..c1dfd04265 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -34,6 +34,7 @@
#include "foreign/fdwapi.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
#include "nodes/supportnodes.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
@@ -41,6 +42,7 @@
#include "optimizer/plancat.h"
#include "optimizer/prep.h"
#include "parser/parse_relation.h"
+#include "parser/parse_clause.h"
#include "parser/parsetree.h"
#include "partitioning/partdesc.h"
#include "rewrite/rewriteManip.h"
@@ -79,6 +81,8 @@ static void set_baserel_partition_key_exprs(Relation relation,
RelOptInfo *rel);
static void set_baserel_partition_constraint(Relation relation,
RelOptInfo *rel);
+static bool check_system_versioned_column(Node *node, RangeTblEntry *rte);
+static bool check_system_versioned_table(RangeTblEntry *rte);
/*
@@ -2342,3 +2346,187 @@ set_baserel_partition_constraint(Relation relation, RelOptInfo *rel)
rel->partition_qual = partconstr;
}
}
+
+/*
+ * get_row_end_time_col_name
+ *
+ * Retrieve the row end time column name of the given relation.
+ */
+char *
+get_row_end_time_col_name(Relation rel)
+{
+ TupleDesc tupdesc;
+ char *name;
+ int natts;
+
+ tupdesc = RelationGetDescr(rel);
+ natts = tupdesc->natts;
+ for (int i = 0; i < natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ {
+ name = NameStr(attr->attname);
+ break;
+ }
+ }
+
+ return name;
+}
+
+/*
+ * get_row_start_time_col_name
+ *
+ * Retrieve the row start time column name of the given relation.
+ */
+char *
+get_row_start_time_col_name(Relation rel)
+{
+ TupleDesc tupdesc;
+ char *name;
+ int natts;
+
+ tupdesc = RelationGetDescr(rel);
+ natts = tupdesc->natts;
+ for (int i = 0; i < natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attgenerated == ATTRIBUTE_ROW_START_TIME)
+ {
+ name = NameStr(attr->attname);
+ break;
+ }
+ }
+
+ return name;
+}
+
+/*
+ * add_history_data_filter
+ *
+ * Add history data filter clause to where clause specification
+ * if there are system versioned relation and where clause did not
+ * already contain filter condition involving system time column.
+ */
+void
+add_history_data_filter(Query *query)
+{
+ ListCell *l;
+
+ foreach(l, query->rtable)
+ {
+
+ RangeTblEntry *rte = lfirst_node(RangeTblEntry, l);
+
+ if (!check_system_versioned_table(rte) ||
+ check_system_versioned_column(query->jointree->quals, rte))
+ {
+ continue;
+ }
+ else
+ {
+ Node *wClause;
+ ParseState *pstate;
+ Relation relation;
+ ColumnRef *c;
+ A_Const *n;
+
+
+ relation = table_open(rte->relid, NoLock);
+
+ /*
+ * Create a condition that filter history data and attach it to
+ * the existing where clause.
+ */
+ c = makeColumnRefFromName(get_row_end_time_col_name(relation));
+ n = makeNode(A_Const);
+ n->val.type = T_String;
+ n->val.val.str = "infinity";
+ n->location = 0;
+
+ wClause = (Node *) makeSimpleA_Expr(AEXPR_OP, "=", (Node *) c, (Node *) n, 0);
+
+ /*
+ * Create a dummy ParseState and insert the target relation as its
+ * sole rangetable entry. We need a ParseState for transformExpr.
+ */
+ pstate = make_parsestate(NULL);
+ rte = addRangeTableEntryForRelation(pstate,
+ relation,
+ AccessShareLock,
+ NULL,
+ false,
+ true);
+ addRTEtoQuery(pstate, rte, false, true, true);
+ wClause = transformWhereClause(pstate,
+ wClause,
+ EXPR_KIND_WHERE,
+ "WHERE");
+
+ if (query->jointree->quals != NULL)
+ query->jointree->quals = make_and_qual(query->jointree->quals, wClause);
+ else
+ query->jointree->quals = wClause;
+ table_close(relation, NoLock);
+ }
+
+ }
+}
+
+/*
+ * Check for references to system versioned columns
+ */
+static bool
+check_system_versioned_column_walker(Node *node, RangeTblEntry *rte)
+{
+
+ if (node == NULL)
+ return false;
+ else if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+ Oid relid;
+ AttrNumber attnum;
+ char result;
+
+ relid = rte->relid;
+ attnum = var->varattno;
+ result = get_attgenerated(relid, attnum);
+
+ if (OidIsValid(relid) && AttributeNumberIsValid(attnum) &&
+ (result == ATTRIBUTE_ROW_START_TIME || result == ATTRIBUTE_ROW_END_TIME))
+ return true;
+ else
+ return false;
+ }
+ else
+ return expression_tree_walker(node, check_system_versioned_column_walker,
+ rte);
+}
+
+static bool
+check_system_versioned_column(Node *node, RangeTblEntry *rte)
+{
+ return check_system_versioned_column_walker(node, rte);
+}
+
+static bool
+check_system_versioned_table(RangeTblEntry *rte)
+{
+ Relation rel;
+ TupleDesc tupdesc;
+ bool result = false;
+
+ if (rte->relid == 0)
+ return false;
+
+ rel = table_open(rte->relid, NoLock);
+ tupdesc = RelationGetDescr(rel);
+ result = tupdesc->constr && tupdesc->constr->is_system_versioned;
+
+ table_close(rel, NoLock);
+
+ return result;
+}
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 0656279654..d5bb72c7e8 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1228,6 +1228,15 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
/* process the FROM clause */
transformFromClause(pstate, stmt->fromClause);
+ /* Add temporal filter clause to the rest of where clause */
+ if (pstate->p_tempwhere != NULL)
+ {
+ if (stmt->whereClause)
+ stmt->whereClause = makeAndExpr(stmt->whereClause, pstate->p_tempwhere, 0);
+ else
+ stmt->whereClause = pstate->p_tempwhere;
+ }
+
/* transform targetlist */
qry->targetList = transformTargetList(pstate, stmt->targetList,
EXPR_KIND_SELECT_TARGET);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c5086846de..062bf984f7 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -127,6 +127,20 @@ typedef struct ImportQual
List *table_names;
} ImportQual;
+/* Private struct for the result of generated_type production */
+typedef struct GenerateType
+{
+ ConstrType contype;
+ Node *raw_expr;
+} GenerateType;
+
+/* Private struct for the result of OptWith production */
+typedef struct OptionWith
+{
+ List *options;
+ bool systemVersioned;
+} OptionWith;
+
/* ConstraintAttributeSpec yields an integer bitmask of these flags: */
#define CAS_NOT_DEFERRABLE 0x01
#define CAS_DEFERRABLE 0x02
@@ -170,7 +184,6 @@ static void insertSelectOptions(SelectStmt *stmt,
static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
static Node *doNegate(Node *n, int location);
static void doNegateFloat(Value *v);
-static Node *makeAndExpr(Node *lexpr, Node *rexpr, int location);
static Node *makeOrExpr(Node *lexpr, Node *rexpr, int location);
static Node *makeNotExpr(Node *expr, int location);
static Node *makeAArrayExpr(List *elements, int location);
@@ -242,6 +255,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
PartitionSpec *partspec;
PartitionBoundSpec *partboundspec;
RoleSpec *rolespec;
+ TemporalClause *temporalClause;
+ struct GenerateType *GenerateType;
+ struct OptionWith *OptionWith;
}
%type <node> stmt schema_stmt
@@ -374,12 +390,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> import_qualification_type
%type <importqual> import_qualification
%type <node> vacuum_relation
+%type <GenerateType> generated_type
+%type <OptionWith> OptWith
%type <list> stmtblock stmtmulti
OptTableElementList TableElementList OptInherit definition
OptTypedTableElementList TypedTableElementList
reloptions opt_reloptions
- OptWith distinct_clause opt_all_clause opt_definition func_args func_args_list
+ distinct_clause opt_all_clause opt_definition func_args func_args_list
func_args_with_defaults func_args_with_defaults_list
aggr_args aggr_args_list
func_as createfunc_opt_list alterfunc_opt_list
@@ -433,7 +451,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> for_locking_strength
%type <node> for_locking_item
-%type <list> for_locking_clause opt_for_locking_clause for_locking_items
+%type <list> for_clause for_locking_clause opt_for_locking_clause for_locking_items
%type <list> locked_rels_list
%type <boolean> all_or_distinct
@@ -500,7 +518,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <range> relation_expr_opt_alias
%type <node> tablesample_clause opt_repeatable_clause
%type <target> target_el set_target insert_column_item
-
+%type <temporalClause> temporal_clause
%type <str> generic_option_name
%type <node> generic_option_arg
%type <defelt> generic_option_elem alter_generic_option_elem
@@ -541,7 +559,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <keyword> unreserved_keyword type_func_name_keyword
%type <keyword> col_name_keyword reserved_keyword
-%type <node> TableConstraint TableLikeClause
+%type <node> TableConstraint TableLikeClause optSystemTimeColumn
%type <ival> TableLikeOptionList TableLikeOption
%type <list> ColQualList
%type <node> ColConstraint ColConstraintElem ConstraintAttr
@@ -669,7 +687,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ORDER ORDINALITY OTHERS OUT_P OUTER_P
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
- PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY
+ PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PERIOD PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -684,7 +702,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P
- SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P
+ SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_TIME
TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
@@ -695,7 +713,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UNTIL UPDATE USER USING
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
- VERBOSE VERSION_P VIEW VIEWS VOLATILE
+ VERBOSE VERSION_P VERSIONING VIEW VIEWS VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -2101,7 +2119,15 @@ alter_table_cmd:
n->missing_ok = true;
$$ = (Node *)n;
}
- /* ALTER TABLE <name> ALTER [COLUMN] <colname> {SET DEFAULT <expr>|DROP DEFAULT} */
+ /* ALTER TABLE <name> ADD SYSTEM VERSIONING */
+ | ADD_P SYSTEM_P VERSIONING
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_AddSystemVersioning;
+ n->def = NULL;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
| ALTER opt_column ColId alter_column_default
{
AlterTableCmd *n = makeNode(AlterTableCmd);
@@ -2242,6 +2268,15 @@ alter_table_cmd:
n->missing_ok = false;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> DROP SYSTEM VERSIONING [RESTRICT|CASCADE] */
+ | DROP SYSTEM_P VERSIONING opt_drop_behavior
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_DropSystemVersioning;
+ n->behavior = $4;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
/*
* ALTER TABLE <name> ALTER [COLUMN] <colname> [SET DATA] TYPE <typename>
* [ USING <expression> ]
@@ -3141,12 +3176,13 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$4->relpersistence = $2;
n->relation = $4;
n->tableElts = $6;
+ n->systemVersioned = ($11)->systemVersioned;
n->inhRelations = $8;
n->partspec = $9;
n->ofTypename = NULL;
n->constraints = NIL;
n->accessMethod = $10;
- n->options = $11;
+ n->options = ($11)->options;
n->oncommit = $12;
n->tablespacename = $13;
n->if_not_exists = false;
@@ -3160,12 +3196,13 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$7->relpersistence = $2;
n->relation = $7;
n->tableElts = $9;
+ n->systemVersioned = ($14)->systemVersioned;
n->inhRelations = $11;
n->partspec = $12;
n->ofTypename = NULL;
n->constraints = NIL;
n->accessMethod = $13;
- n->options = $14;
+ n->options = ($14)->options;
n->oncommit = $15;
n->tablespacename = $16;
n->if_not_exists = true;
@@ -3179,13 +3216,14 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$4->relpersistence = $2;
n->relation = $4;
n->tableElts = $7;
+ n->systemVersioned = ($10)->systemVersioned;
n->inhRelations = NIL;
n->partspec = $8;
n->ofTypename = makeTypeNameFromNameList($6);
n->ofTypename->location = @6;
n->constraints = NIL;
n->accessMethod = $9;
- n->options = $10;
+ n->options = ($10)->options;
n->oncommit = $11;
n->tablespacename = $12;
n->if_not_exists = false;
@@ -3199,13 +3237,14 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$7->relpersistence = $2;
n->relation = $7;
n->tableElts = $10;
+ n->systemVersioned = ($13)->systemVersioned;
n->inhRelations = NIL;
n->partspec = $11;
n->ofTypename = makeTypeNameFromNameList($9);
n->ofTypename->location = @9;
n->constraints = NIL;
n->accessMethod = $12;
- n->options = $13;
+ n->options = ($13)->options;
n->oncommit = $14;
n->tablespacename = $15;
n->if_not_exists = true;
@@ -3219,13 +3258,14 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$4->relpersistence = $2;
n->relation = $4;
n->tableElts = $8;
+ n->systemVersioned = ($12)->systemVersioned;
n->inhRelations = list_make1($7);
n->partbound = $9;
n->partspec = $10;
n->ofTypename = NULL;
n->constraints = NIL;
n->accessMethod = $11;
- n->options = $12;
+ n->options = ($12)->options;
n->oncommit = $13;
n->tablespacename = $14;
n->if_not_exists = false;
@@ -3239,13 +3279,14 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$7->relpersistence = $2;
n->relation = $7;
n->tableElts = $11;
+ n->systemVersioned = ($15)->systemVersioned;
n->inhRelations = list_make1($10);
n->partbound = $12;
n->partspec = $13;
n->ofTypename = NULL;
n->constraints = NIL;
n->accessMethod = $14;
- n->options = $15;
+ n->options = ($15)->options;
n->oncommit = $16;
n->tablespacename = $17;
n->if_not_exists = true;
@@ -3322,6 +3363,7 @@ TableElement:
columnDef { $$ = $1; }
| TableLikeClause { $$ = $1; }
| TableConstraint { $$ = $1; }
+ | optSystemTimeColumn { $$ = $1; }
;
TypedTableElement:
@@ -3418,6 +3460,16 @@ ColConstraint:
}
;
+optSystemTimeColumn:
+ PERIOD FOR SYSTEM_TIME '(' name ',' name ')'
+ {
+ RowTime *n = makeNode(RowTime);
+ n->start_time = $5;
+ n->end_time = $7;
+ $$ = (Node *)n;
+ }
+ ;
+
/* DEFAULT NULL is already the default for Postgres.
* But define it here and carry it forward into the system
* to make it explicit.
@@ -3500,12 +3552,12 @@ ColConstraintElem:
n->location = @1;
$$ = (Node *)n;
}
- | GENERATED generated_when AS '(' a_expr ')' STORED
+ | GENERATED generated_when AS generated_type
{
Constraint *n = makeNode(Constraint);
- n->contype = CONSTR_GENERATED;
+ n->contype = ($4)->contype;
n->generated_when = $2;
- n->raw_expr = $5;
+ n->raw_expr = ($4)->raw_expr;
n->cooked_expr = NULL;
n->location = @1;
@@ -3523,6 +3575,7 @@ ColConstraintElem:
$$ = (Node *)n;
}
+
| REFERENCES qualified_name opt_column_list key_match key_actions
{
Constraint *n = makeNode(Constraint);
@@ -3545,6 +3598,30 @@ generated_when:
| BY DEFAULT { $$ = ATTRIBUTE_IDENTITY_BY_DEFAULT; }
;
+generated_type:
+ '(' a_expr ')' STORED
+ {
+ GenerateType *n = (GenerateType *) palloc(sizeof(GenerateType));
+ n->contype = CONSTR_GENERATED;
+ n->raw_expr = $2;
+ $$ = n;
+ }
+ | ROW START
+ {
+ GenerateType *n = (GenerateType *) palloc(sizeof(GenerateType));
+ n->contype = CONSTR_ROW_START_TIME;
+ n->raw_expr = NULL;
+ $$ = n;
+ }
+ | ROW END_P
+ {
+ GenerateType *n = (GenerateType *) palloc(sizeof(GenerateType));
+ n->contype = CONSTR_ROW_END_TIME;
+ n->raw_expr = NULL;
+ $$ = n;
+ }
+ ;
+
/*
* ConstraintAttr represents constraint attributes, which we parse as if
* they were independent constraint clauses, in order to avoid shift/reduce
@@ -3924,9 +4001,34 @@ table_access_method_clause:
/* WITHOUT OIDS is legacy only */
OptWith:
- WITH reloptions { $$ = $2; }
- | WITHOUT OIDS { $$ = NIL; }
- | /*EMPTY*/ { $$ = NIL; }
+ WITH reloptions
+ {
+ OptionWith *n = (OptionWith *) palloc(sizeof(OptionWith));
+ n->options = $2;
+ n->systemVersioned = false;
+ $$ = n;
+ }
+ | WITHOUT OIDS
+ {
+ OptionWith *n = (OptionWith *) palloc(sizeof(OptionWith));
+ n->options = NIL;
+ n->systemVersioned = false;
+ $$ = n;
+ }
+ | WITH SYSTEM_P VERSIONING
+ {
+ OptionWith *n = (OptionWith *) palloc(sizeof(OptionWith));
+ n->options = NIL;
+ n->systemVersioned = true;
+ $$ = n;
+ }
+ | /*EMPTY*/
+ {
+ OptionWith *n = (OptionWith *) palloc(sizeof(OptionWith));
+ n->options = NIL;
+ n->systemVersioned = false;
+ $$ = n;
+ }
;
OnCommitOption: ON COMMIT DROP { $$ = ONCOMMIT_DROP; }
@@ -4062,7 +4164,7 @@ create_as_target:
$$->rel = $1;
$$->colNames = $2;
$$->accessMethod = $3;
- $$->options = $4;
+ $$->options = ($4)->options;
$$->onCommit = $5;
$$->tableSpaceName = $6;
$$->viewQuery = NULL;
@@ -11321,7 +11423,7 @@ select_no_parens:
yyscanner);
$$ = $1;
}
- | select_clause opt_sort_clause for_locking_clause opt_select_limit
+ | select_clause opt_sort_clause for_clause opt_select_limit
{
insertSelectOptions((SelectStmt *) $1, $2, $3,
list_nth($4, 0), list_nth($4, 1),
@@ -11353,7 +11455,7 @@ select_no_parens:
yyscanner);
$$ = $2;
}
- | with_clause select_clause opt_sort_clause for_locking_clause opt_select_limit
+ | with_clause select_clause opt_sort_clause for_clause opt_select_limit
{
insertSelectOptions((SelectStmt *) $2, $3, $4,
list_nth($5, 0), list_nth($5, 1),
@@ -11833,11 +11935,15 @@ having_clause:
for_locking_clause:
for_locking_items { $$ = $1; }
+ ;
+
+for_clause:
+ FOR for_locking_clause { $$ = $2; }
| FOR READ ONLY { $$ = NIL; }
;
opt_for_locking_clause:
- for_locking_clause { $$ = $1; }
+ for_clause { $$ = $1; }
| /* EMPTY */ { $$ = NIL; }
;
@@ -11858,10 +11964,10 @@ for_locking_item:
;
for_locking_strength:
- FOR UPDATE { $$ = LCS_FORUPDATE; }
- | FOR NO KEY UPDATE { $$ = LCS_FORNOKEYUPDATE; }
- | FOR SHARE { $$ = LCS_FORSHARE; }
- | FOR KEY SHARE { $$ = LCS_FORKEYSHARE; }
+ UPDATE { $$ = LCS_FORUPDATE; }
+ | NO KEY UPDATE { $$ = LCS_FORNOKEYUPDATE; }
+ | SHARE { $$ = LCS_FORSHARE; }
+ | KEY SHARE { $$ = LCS_FORKEYSHARE; }
;
locked_rels_list:
@@ -11900,7 +12006,7 @@ values_clause:
*****************************************************************************/
from_clause:
- FROM from_list { $$ = $2; }
+ FROM from_list { $$ = $2 ; }
| /*EMPTY*/ { $$ = NIL; }
;
@@ -11925,6 +12031,11 @@ table_ref: relation_expr opt_alias_clause
n->relation = (Node *) $1;
$$ = (Node *) n;
}
+ | FOR relation_expr temporal_clause
+ {
+ $3->relation = (Node *)$2;
+ $$ = (Node *)$3;
+ }
| func_table func_alias_clause
{
RangeFunction *n = (RangeFunction *) $1;
@@ -12023,7 +12134,28 @@ table_ref: relation_expr opt_alias_clause
$$ = (Node *) $2;
}
;
-
+temporal_clause: SYSTEM_TIME AS OF b_expr
+ {
+ $$ = makeNode(TemporalClause);
+ $$->kind = AS_OF;
+ $$->from = NULL;
+ $$->to = $4;
+ }
+ | SYSTEM_TIME BETWEEN b_expr AND b_expr
+ {
+ $$ = makeNode(TemporalClause);
+ $$->kind = BETWEEN_SYMMETRIC;
+ $$->from = $3;
+ $$->to = $5;
+ }
+ | SYSTEM_TIME FROM b_expr TO b_expr
+ {
+ $$ = makeNode(TemporalClause);
+ $$->kind = FROM_TO;
+ $$->from = $3;
+ $$->to = $5;
+ }
+ ;
/*
* It may seem silly to separate joined_table from table_ref, but there is
@@ -15284,6 +15416,7 @@ unreserved_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PERIOD
| PLANS
| POLICY
| PRECEDING
@@ -15360,6 +15493,7 @@ unreserved_keyword:
| SUPPORT
| SYSID
| SYSTEM_P
+ | SYSTEM_TIME
| TABLES
| TABLESPACE
| TEMP
@@ -15389,6 +15523,7 @@ unreserved_keyword:
| VALUE_P
| VARYING
| VERSION_P
+ | VERSIONING
| VIEW
| VIEWS
| VOLATILE
@@ -16083,29 +16218,6 @@ doNegateFloat(Value *v)
v->val.str = psprintf("-%s", oldval);
}
-static Node *
-makeAndExpr(Node *lexpr, Node *rexpr, int location)
-{
- Node *lexp = lexpr;
-
- /* Look through AEXPR_PAREN nodes so they don't affect flattening */
- while (IsA(lexp, A_Expr) &&
- ((A_Expr *) lexp)->kind == AEXPR_PAREN)
- lexp = ((A_Expr *) lexp)->lexpr;
- /* Flatten "a AND b AND c ..." to a single BoolExpr on sight */
- if (IsA(lexp, BoolExpr))
- {
- BoolExpr *blexpr = (BoolExpr *) lexp;
-
- if (blexpr->boolop == AND_EXPR)
- {
- blexpr->args = lappend(blexpr->args, rexpr);
- return (Node *) blexpr;
- }
- }
- return (Node *) makeBoolExpr(AND_EXPR, list_make2(lexpr, rexpr), location);
-}
-
static Node *
makeOrExpr(Node *lexpr, Node *rexpr, int location)
{
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index ebbba2d7b5..5cd98b0f57 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -31,6 +31,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/optimizer.h"
+#include "optimizer/plancat.h"
#include "parser/analyze.h"
#include "parser/parse_clause.h"
#include "parser/parse_coerce.h"
@@ -101,6 +102,7 @@ static WindowClause *findWindowClause(List *wclist, const char *name);
static Node *transformFrameOffset(ParseState *pstate, int frameOptions,
Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc,
Node *clause);
+static void addTempToWhereClause(ParseState *pstate, TemporalClause * tc, RangeTblEntry *rte);
/*
@@ -1082,7 +1084,6 @@ transformFromClauseItem(ParseState *pstate, Node *n,
/* if not found above, must be a table reference */
if (!rte)
rte = transformTableEntry(pstate, rv);
-
/* assume new rte is at end */
rtindex = list_length(pstate->p_rtable);
Assert(rte == rt_fetch(rtindex, pstate->p_rtable));
@@ -1174,6 +1175,31 @@ transformFromClauseItem(ParseState *pstate, Node *n,
rte->tablesample = transformRangeTableSample(pstate, rts);
return (Node *) rtr;
}
+ else if (IsA(n, TemporalClause))
+ {
+ TemporalClause *tc = (TemporalClause *) n;
+ RangeVar *rv = (RangeVar *) tc->relation;
+ RangeTblRef *rtr;
+ RangeTblEntry *rte;
+ int rtindex;
+
+ rte = transformTableEntry(pstate, rv);
+ if (!rte->system_versioned)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Temporal clause can only be to system versioned table")));
+
+ addTempToWhereClause(pstate, tc, rte);
+ /* assume new rte is at end */
+ rtindex = list_length(pstate->p_rtable);
+ Assert(rte == rt_fetch(rtindex, pstate->p_rtable));
+ *top_rte = rte;
+ *top_rti = rtindex;
+ *namespace = list_make1(makeDefaultNSItem(rte, rtindex));
+ rtr = makeNode(RangeTblRef);
+ rtr->rtindex = rtindex;
+ return (Node *) rtr;
+ }
else if (IsA(n, JoinExpr))
{
/* A newfangled join expression */
@@ -3651,3 +3677,48 @@ transformFrameOffset(ParseState *pstate, int frameOptions,
return node;
}
+
+/*
+ * changeTempToWhereClause
+ * add temporal clause specification to where clause.
+ */
+static void
+addTempToWhereClause(ParseState *pstate, TemporalClause * tc, RangeTblEntry *rte)
+{
+ Node *fClause;
+ Node *tClause;
+ ColumnRef *s;
+ ColumnRef *e;
+ Relation rel;
+
+ rel = table_open(rte->relid, NoLock);
+
+ s = makeColumnRefFromName(get_row_start_time_col_name(rel));
+ e = makeColumnRefFromName(get_row_end_time_col_name(rel));
+
+ if (tc->kind == AS_OF)
+ {
+ fClause = (Node *) makeSimpleA_Expr(AEXPR_OP, "<=", (Node *) s, tc->to, 0);
+ tClause = (Node *) makeSimpleA_Expr(AEXPR_OP, ">", (Node *) e, tc->to, 0);
+ fClause = makeAndExpr(fClause, tClause, 0);
+ }
+ else if (tc->kind == FROM_TO)
+ {
+ fClause = (Node *) makeSimpleA_Expr(AEXPR_OP, ">", (Node *) e, tc->from, 0);
+ tClause = (Node *) makeSimpleA_Expr(AEXPR_OP, "<", (Node *) s, tc->to, 0);
+
+ fClause = makeAndExpr(fClause, tClause, 0);
+ }
+ else if (tc->kind == BETWEEN_SYMMETRIC)
+ {
+ fClause = (Node *) makeSimpleA_Expr(AEXPR_OP, ">", (Node *) e, tc->from, 0);
+ tClause = (Node *) makeSimpleA_Expr(AEXPR_OP, "<=", (Node *) s, tc->to, 0);
+ fClause = makeAndExpr(fClause, tClause, 0);
+ }
+ if (pstate->p_tempwhere != NULL)
+ pstate->p_tempwhere = makeAndExpr(pstate->p_tempwhere, fClause, 0);
+ else
+ pstate->p_tempwhere = fClause;
+
+ table_close(rel, NoLock);
+}
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 4888311f44..1251dc80c9 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -1270,6 +1270,7 @@ addRangeTableEntry(ParseState *pstate,
char *refname = alias ? alias->aliasname : relation->relname;
LOCKMODE lockmode;
Relation rel;
+ TupleDesc tupdesc;
Assert(pstate != NULL);
@@ -1294,6 +1295,9 @@ addRangeTableEntry(ParseState *pstate,
rte->relkind = rel->rd_rel->relkind;
rte->rellockmode = lockmode;
+ tupdesc = RelationGetDescr(rel);
+ rte->system_versioned = (tupdesc->constr && tupdesc->constr->is_system_versioned);
+
/*
* Build the list of effective column names using user-supplied aliases
* and/or actual column names.
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 45bb31ecf8..c7fca3cbfb 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -61,6 +61,7 @@
#include "parser/parse_type.h"
#include "parser/parse_utilcmd.h"
#include "parser/parser.h"
+#include "optimizer/plancat.h"
#include "rewrite/rewriteManip.h"
#include "utils/acl.h"
#include "utils/builtins.h"
@@ -71,6 +72,7 @@
#include "utils/syscache.h"
#include "utils/typcache.h"
+#include <string.h>
/* State shared by transformCreateStmt and its subroutines */
typedef struct
@@ -96,6 +98,11 @@ typedef struct
bool ispartitioned; /* true if table is partitioned */
PartitionBoundSpec *partbound; /* transformed FOR VALUES */
bool ofType; /* true if statement contains OF typename */
+ bool isSystemVersioned; /* true if table is system versioned */
+ char *startTimeColName; /* name of row start time column */
+ char *endTimeColName; /* name of row end time column */
+ char *periodStart; /* name of period start column */
+ char *periodEnd; /* name of period end column */
} CreateStmtContext;
/* State shared by transformCreateSchemaStmt and its subroutines */
@@ -119,6 +126,8 @@ static void transformTableConstraint(CreateStmtContext *cxt,
Constraint *constraint);
static void transformTableLikeClause(CreateStmtContext *cxt,
TableLikeClause *table_like_clause);
+static void transformPeriodColumn(CreateStmtContext *cxt,
+ RowTime * cols);
static void transformOfType(CreateStmtContext *cxt,
TypeName *ofTypename);
static CreateStatsStmt *generateClonedExtStatsStmt(RangeVar *heapRel,
@@ -249,6 +258,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
cxt.ispartitioned = stmt->partspec != NULL;
cxt.partbound = stmt->partbound;
cxt.ofType = (stmt->ofTypename != NULL);
+ cxt.startTimeColName = NULL;
+ cxt.endTimeColName = NULL;
+ cxt.isSystemVersioned = false;
+
Assert(!stmt->ofTypename || !stmt->inhRelations); /* grammar enforces */
@@ -284,7 +297,9 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
case T_TableLikeClause:
transformTableLikeClause(&cxt, (TableLikeClause *) element);
break;
-
+ case T_RowTime:
+ transformPeriodColumn(&cxt, (RowTime *) element);
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(element));
@@ -292,6 +307,27 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
}
}
+ /*
+ * If there are no system time column and the user specified "WITH SYSTEM
+ * VERSIONING", default system time columns is added to the table
+ * definition.
+ */
+ if (!cxt.isSystemVersioned && stmt->systemVersioned)
+ {
+ ColumnDef *startCol;
+ ColumnDef *endCol;
+
+ startCol = makeSystemColumnDef("StartTime");
+ endCol = makeSystemColumnDef("EndTime");
+ if (stmt->tableElts == NIL)
+ stmt->tableElts = list_make2(startCol, endCol);
+ else
+ stmt->tableElts = lappend(stmt->tableElts, list_make2(startCol, endCol));
+
+ transformColumnDefinition(&cxt, startCol);
+ transformColumnDefinition(&cxt, endCol);
+ }
+
/*
* Transfer anything we already have in cxt.alist into save_alist, to keep
* it separate from the output of transformIndexConstraints. (This may
@@ -303,6 +339,25 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
Assert(stmt->constraints == NIL);
+ /*
+ * End time column is added to primary and unique key constraint
+ * implicitly to make history data and current data co-exist.
+ */
+ if (cxt.isSystemVersioned)
+ {
+ ListCell *lc;
+
+ foreach(lc, cxt.ixconstraints)
+ {
+ Constraint *constraint = lfirst_node(Constraint, lc);
+
+ if ((constraint->contype == CONSTR_PRIMARY || constraint->contype == CONSTR_UNIQUE) && constraint->keys != NIL)
+ {
+ constraint->keys = lappend(constraint->keys, makeString(cxt.endTimeColName));
+ }
+ }
+ }
+
/*
* Postprocess constraints that give rise to index definitions.
*/
@@ -716,6 +771,40 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
saw_generated = true;
break;
+ case CONSTR_ROW_START_TIME:
+ if (strcmp(strVal(list_nth(column->typeName->names, 1)), "timestamp") != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("the data type of row start time must be timestamp")));
+
+ if (cxt->startTimeColName)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row start time can not be specified multiple time")));
+
+ column->generated = ATTRIBUTE_ROW_START_TIME;
+ cxt->startTimeColName = column->colname;
+ cxt->isSystemVersioned = true;
+ column->is_not_null = true;
+ break;
+
+ case CONSTR_ROW_END_TIME:
+ if (strcmp(strVal(list_nth(column->typeName->names, 1)), "timestamp") != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("the data type of row end time must be timestamp")));
+
+ if (cxt->endTimeColName)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row end time can not be specified multiple time")));
+
+
+ column->generated = ATTRIBUTE_ROW_END_TIME;
+ cxt->endTimeColName = column->colname;
+ column->is_not_null = true;
+ break;
+
case CONSTR_CHECK:
cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
break;
@@ -1267,6 +1356,27 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
table_close(relation, NoLock);
}
+/*
+ * transformPeriodColumn
+ * transform a period node within CREATE TABLE
+ */
+static void
+transformPeriodColumn(CreateStmtContext *cxt, RowTime * col)
+{
+ cxt->periodStart = col->start_time;
+ cxt->periodEnd = col->end_time;
+
+ if (strcmp(cxt->periodStart, cxt->startTimeColName) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("The period start time parameter must equal the name of row start time column")));
+
+ if (strcmp(cxt->periodEnd, cxt->endTimeColName) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("The period end time parameter must equal the name of row end time column")));
+}
+
static void
transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
{
@@ -3076,6 +3186,10 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
cxt.ispartitioned = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
cxt.partbound = NULL;
cxt.ofType = false;
+ cxt.startTimeColName = NULL;
+ cxt.endTimeColName = NULL;
+ cxt.isSystemVersioned = false;
+
/*
* The only subtypes that currently require parse transformation handling
@@ -3119,6 +3233,23 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
*/
if (IsA(cmd->def, Constraint))
{
+
+ /*
+ * End time column is added to primary and unique key
+ * constraint implicitly to make history data and current
+ * data co-exist.
+ */
+ Constraint *constraint = castNode(Constraint, cmd->def);
+
+ if ((rel->rd_att->constr &&
+ rel->rd_att->constr->is_system_versioned) && (constraint->contype == CONSTR_PRIMARY || constraint->contype == CONSTR_UNIQUE))
+ {
+ char *endColNme;
+
+ endColNme = get_row_end_time_col_name(rel);
+ constraint->keys = lappend(constraint->keys, makeString(endColNme));
+ }
+
transformTableConstraint(&cxt, (Constraint *) cmd->def);
if (((Constraint *) cmd->def)->contype == CONSTR_FOREIGN)
skipValidation = false;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 3a03ca7e2f..7c618ee3ba 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -17,6 +17,7 @@
#include "postgres.h"
#include "access/htup_details.h"
+#include "access/relation.h"
#include "access/reloptions.h"
#include "access/twophase.h"
#include "access/xact.h"
@@ -58,7 +59,9 @@
#include "commands/vacuum.h"
#include "commands/view.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
#include "parser/parse_utilcmd.h"
+#include "optimizer/plancat.h"
#include "postmaster/bgwriter.h"
#include "rewrite/rewriteDefine.h"
#include "rewrite/rewriteRemove.h"
@@ -1094,6 +1097,9 @@ ProcessUtilitySlow(ParseState *pstate,
Oid relid;
List *stmts;
ListCell *l;
+ ListCell *s;
+ Relation rel;
+
LOCKMODE lockmode;
/*
@@ -1105,6 +1111,61 @@ ProcessUtilitySlow(ParseState *pstate,
lockmode = AlterTableGetLockLevel(atstmt->cmds);
relid = AlterTableLookupRelation(atstmt, lockmode);
+
+ /*
+ * Change add and remove system versioning to individual
+ * ADD and DROP column command
+ */
+ foreach(s, atstmt->cmds)
+ {
+ AlterTableCmd *cmd = (AlterTableCmd *) lfirst(s);
+
+ if (cmd->subtype == AT_AddSystemVersioning)
+ {
+ ColumnDef *startTimeCol;
+ ColumnDef *endTimeCol;
+
+ rel = relation_open(relid, NoLock);
+
+ /*
+ * we use defualt column names for system
+ * versioning in ALTER TABLE statment
+ */
+ startTimeCol = makeSystemColumnDef("StartTime");
+ endTimeCol = makeSystemColumnDef("EndTime");
+
+ /*
+ * create alter table cmd and append to the ende
+ * of commands and remove current listCell because
+ * we don't want it anymore.
+ */
+ atstmt->cmds = lappend(atstmt->cmds, (Node *) makeAddColCmd(startTimeCol));
+ atstmt->cmds = lappend(atstmt->cmds, (Node *) makeAddColCmd(endTimeCol));
+
+ /*
+ * delete current listCell becouse we don't need
+ * it anymore
+ */
+ atstmt->cmds = list_delete_cell(atstmt->cmds, s);
+ relation_close(rel, NoLock);
+
+ }
+
+ if (cmd->subtype == AT_DropSystemVersioning)
+ {
+ rel = relation_open(relid, NoLock);
+ atstmt->cmds = lappend(atstmt->cmds, makeDropColCmd(get_row_end_time_col_name(rel)));
+ atstmt->cmds = lappend(atstmt->cmds, makeDropColCmd(get_row_start_time_col_name(rel)));
+
+ /*
+ * delete current listCell because we don't need
+ * it anymore
+ */
+ atstmt->cmds = list_delete_cell(atstmt->cmds, s);
+ relation_close(rel, NoLock);
+ }
+ }
+
if (OidIsValid(relid))
{
/* Run parse analysis ... */
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 969cb5a4af..f5a18072c1 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -509,6 +509,7 @@ RelationBuildTupleDesc(Relation relation)
sizeof(TupleConstr));
constr->has_not_null = false;
constr->has_generated_stored = false;
+ constr->is_system_versioned = false;
/*
* Form a scan key that selects only user attributes (attnum > 0).
@@ -563,6 +564,8 @@ RelationBuildTupleDesc(Relation relation)
constr->has_not_null = true;
if (attp->attgenerated == ATTRIBUTE_GENERATED_STORED)
constr->has_generated_stored = true;
+ if (attp->attgenerated == ATTRIBUTE_ROW_START_TIME || attp->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ constr->is_system_versioned = true;
/* If the column has a default, fill it into the attrdef array */
if (attp->atthasdef)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 08658c8e86..f5f8f0cb8d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15727,6 +15727,10 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
if (tbinfo->attgenerated[j] == ATTRIBUTE_GENERATED_STORED)
appendPQExpBuffer(q, " GENERATED ALWAYS AS (%s) STORED",
tbinfo->attrdefs[j]->adef_expr);
+ else if (tbinfo->attgenerated[j] == ATTRIBUTE_ROW_START_TIME)
+ appendPQExpBuffer(q, " GENERATED ALWAYS AS ROW START");
+ else if (tbinfo->attgenerated[j] == ATTRIBUTE_ROW_END_TIME)
+ appendPQExpBuffer(q, " GENERATED ALWAYS AS ROW END");
else
appendPQExpBuffer(q, " DEFAULT %s",
tbinfo->attrdefs[j]->adef_expr);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b3b9313b36..c9781df0f4 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2057,11 +2057,15 @@ describeOneTableDetails(const char *schemaname,
default_str = "generated by default as identity";
else if (generated[0] == ATTRIBUTE_GENERATED_STORED)
default_str = psprintf("generated always as (%s) stored", PQgetvalue(res, i, attrdef_col));
+ else if (generated[0] == ATTRIBUTE_ROW_START_TIME)
+ default_str = "generated always as row start";
+ else if (generated[0] == ATTRIBUTE_ROW_END_TIME)
+ default_str = "generated always as row end";
else
/* (note: above we cut off the 'default' string at 128) */
default_str = PQgetvalue(res, i, attrdef_col);
- printTableAddCell(&cont, default_str, false, generated[0] ? true : false);
+ printTableAddCell(&cont, default_str, false, generated[0] == ATTRIBUTE_GENERATED_STORED ? true : false);
}
/* Info for index columns */
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index a06800555c..f68fbb45f5 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -43,6 +43,7 @@ typedef struct TupleConstr
uint16 num_check;
bool has_not_null;
bool has_generated_stored;
+ bool is_system_versioned;
} TupleConstr;
/*
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index 04004b5703..f8ea8bff7c 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -206,6 +206,9 @@ typedef FormData_pg_attribute *Form_pg_attribute;
#define ATTRIBUTE_GENERATED_STORED 's'
+#define ATTRIBUTE_ROW_START_TIME 'S'
+#define ATTRIBUTE_ROW_END_TIME 'E'
+
#endif /* EXPOSE_TO_CLIENT_CODE */
#endif /* PG_ATTRIBUTE_H */
diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h
index 891b119608..26d894a5cc 100644
--- a/src/include/executor/nodeModifyTable.h
+++ b/src/include/executor/nodeModifyTable.h
@@ -16,6 +16,8 @@
#include "nodes/execnodes.h"
extern void ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot);
+extern void ExecSetRowStartTime(EState *estate, TupleTableSlot *slot);
+extern void ExecSetRowEndTime(EState *estate, TupleTableSlot *slot);
extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags);
extern void ExecEndModifyTable(ModifyTableState *node);
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 8032bb7aa2..0107aa2e32 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -104,5 +104,11 @@ extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg,
extern GroupingSet *makeGroupingSet(GroupingSetKind kind, List *content, int location);
extern VacuumRelation *makeVacuumRelation(RangeVar *relation, Oid oid, List *va_cols);
+extern Node *makeAndExpr(Node *lexpr, Node *rexpr, int location);
+extern ColumnRef *makeColumnRefFromName(char *colname);
+extern Constraint *makeConstraint(ConstrType type);
+extern ColumnDef *makeSystemColumnDef(char *name);
+extern AlterTableCmd *makeDropColCmd(char *name);
+extern AlterTableCmd *makeAddColCmd(ColumnDef *coldef);
#endif /* MAKEFUNC_H */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index bce2d59b0d..2c5d8c21bb 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -477,6 +477,8 @@ typedef enum NodeTag
T_PartitionRangeDatum,
T_PartitionCmd,
T_VacuumRelation,
+ T_RowTime,
+ T_TemporalClause,
/*
* TAGS FOR REPLICATION GRAMMAR PARSE NODES (replnodes.h)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ff626cbe61..cad578f6c9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1002,6 +1002,7 @@ typedef struct RangeTblEntry
char relkind; /* relation kind (see pg_class.relkind) */
int rellockmode; /* lock level that query requires on the rel */
struct TableSampleClause *tablesample; /* sampling info, or NULL */
+ bool system_versioned; /* is from relation system versioned? */
/*
* Fields valid for a subquery RTE (else NULL):
@@ -1744,7 +1745,7 @@ typedef enum DropBehavior
} DropBehavior;
/* ----------------------
- * Alter Table
+ * Alter Table
* ----------------------
*/
typedef struct AlterTableStmt
@@ -1824,7 +1825,10 @@ typedef enum AlterTableType
AT_DetachPartition, /* DETACH PARTITION */
AT_AddIdentity, /* ADD IDENTITY */
AT_SetIdentity, /* SET identity column options */
- AT_DropIdentity /* DROP IDENTITY */
+ AT_DropIdentity, /* DROP IDENTITY */
+ AT_AddSystemVersioning, /* ADD system versioning */
+ AT_DropSystemVersioning /* DROP system versioning */
+
} AlterTableType;
typedef struct ReplicaIdentityStmt
@@ -2059,6 +2063,7 @@ typedef struct CreateStmt
char *tablespacename; /* table space to use, or NULL */
char *accessMethod; /* table access method */
bool if_not_exists; /* just do nothing if it already exists? */
+ bool systemVersioned; /* true when its is system versioned table */
} CreateStmt;
/* ----------
@@ -2108,7 +2113,9 @@ typedef enum ConstrType /* types of constraints */
CONSTR_ATTR_DEFERRABLE, /* attributes for previous constraint node */
CONSTR_ATTR_NOT_DEFERRABLE,
CONSTR_ATTR_DEFERRED,
- CONSTR_ATTR_IMMEDIATE
+ CONSTR_ATTR_IMMEDIATE,
+ CONSTR_ROW_START_TIME,
+ CONSTR_ROW_END_TIME
} ConstrType;
/* Foreign key action codes */
@@ -3313,8 +3320,8 @@ typedef struct ConstraintsSetStmt
*/
/* Reindex options */
-#define REINDEXOPT_VERBOSE (1 << 0) /* print progress info */
-#define REINDEXOPT_REPORT_PROGRESS (1 << 1) /* report pgstat progress */
+#define REINDEXOPT_VERBOSE (1 << 0) /* print progress info */
+#define REINDEXOPT_REPORT_PROGRESS (1 << 1) /* report pgstat progress */
typedef enum ReindexObjectType
{
@@ -3534,4 +3541,29 @@ typedef struct DropSubscriptionStmt
DropBehavior behavior; /* RESTRICT or CASCADE behavior */
} DropSubscriptionStmt;
+typedef struct RowTime
+{
+ NodeTag type;
+ char *start_time; /* Row start time */
+ char *end_time; /* Row end time */
+} RowTime;
+
+typedef enum TemporalClauseType
+{
+ AS_OF,
+ BETWEEN_SYMMETRIC,
+ FROM_TO
+} TemporalClauseType;
+
+
+typedef struct TemporalClause
+{
+ NodeTag type;
+ TemporalClauseType kind;
+ Node *relation;
+ Node *from; /* starting time */
+ Node *to; /* ending time */
+} TemporalClause;
+
+
#endif /* PARSENODES_H */
diff --git a/src/include/optimizer/plancat.h b/src/include/optimizer/plancat.h
index bbb27f8779..b0b68be528 100644
--- a/src/include/optimizer/plancat.h
+++ b/src/include/optimizer/plancat.h
@@ -73,5 +73,7 @@ extern double get_function_rows(PlannerInfo *root, Oid funcid, Node *node);
extern bool has_row_triggers(PlannerInfo *root, Index rti, CmdType event);
extern bool has_stored_generated_columns(PlannerInfo *root, Index rti);
-
+extern char *get_row_start_time_col_name(Relation rel);
+extern char *get_row_end_time_col_name(Relation rel);
+extern void add_history_data_filter(Query *query);
#endif /* PLANCAT_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 00ace8425e..9d68973b09 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -299,6 +299,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD)
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD)
+PG_KEYWORD("period", PERIOD, UNRESERVED_KEYWORD)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD)
@@ -392,6 +393,7 @@ PG_KEYWORD("support", SUPPORT, UNRESERVED_KEYWORD)
PG_KEYWORD("symmetric", SYMMETRIC, RESERVED_KEYWORD)
PG_KEYWORD("sysid", SYSID, UNRESERVED_KEYWORD)
PG_KEYWORD("system", SYSTEM_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("system_time", SYSTEM_TIME, UNRESERVED_KEYWORD)
PG_KEYWORD("table", TABLE, RESERVED_KEYWORD)
PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD)
PG_KEYWORD("tablesample", TABLESAMPLE, TYPE_FUNC_NAME_KEYWORD)
@@ -439,6 +441,7 @@ PG_KEYWORD("variadic", VARIADIC, RESERVED_KEYWORD)
PG_KEYWORD("varying", VARYING, UNRESERVED_KEYWORD)
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("versioning", VERSIONING, UNRESERVED_KEYWORD)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD)
PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 674acc5d3c..2ead171ce7 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -199,7 +199,7 @@ struct ParseState
* with FOR UPDATE/FOR SHARE */
bool p_resolve_unknowns; /* resolve unknown-type SELECT outputs as
* type text */
-
+ Node *p_tempwhere; /* temporal where clause so far */
QueryEnvironment *p_queryEnv; /* curr env, incl refs to enclosing env */
/* Flags telling about things found in the query: */
diff --git a/src/test/regress/expected/system_versioned_table.out b/src/test/regress/expected/system_versioned_table.out
new file mode 100644
index 0000000000..6b5ceec910
--- /dev/null
+++ b/src/test/regress/expected/system_versioned_table.out
@@ -0,0 +1,188 @@
+CREATE TABLE stest0(a integer PRIMARY KEY, start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS row START,
+ end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
+ PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
+) WITH SYSTEM VERSIONING;
+--invalid datatype
+CREATE TABLE stest1(a integer PRIMARY KEY, start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS row START,
+ end_timestamp integer GENERATED ALWAYS AS ROW END,
+ PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
+) WITH SYSTEM VERSIONING;
+ERROR: the data type of row end time must be timestamp
+-- references to other column in period columns
+CREATE TABLE stest1(a integer PRIMARY KEY, start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS row START,
+ end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
+ PERIOD FOR SYSTEM_TIME(a, end_timestamp)
+) WITH SYSTEM VERSIONING;
+ERROR: The period start time parameter must equal the name of row start time column
+-- duplicate system time column
+CREATE TABLE stest1(a integer PRIMARY KEY, start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS row START,
+ end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
+ end_timestamp1 TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
+ PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
+) WITH SYSTEM VERSIONING;
+ERROR: row end time can not be specified multiple time
+-- default system time column usage
+CREATE TABLE stest2(a integer
+) WITH SYSTEM VERSIONING;
+\d stest2
+ Table "public.stest2"
+ Column | Type | Collation | Nullable | Default
+-----------+-----------------------------+-----------+----------+-------------------------------
+ a | integer | | |
+ StartTime | timestamp without time zone | | not null | generated always as row start
+ EndTime | timestamp without time zone | | not null | generated always as row end
+
+-- ALTER TABLE tbName ADD SYSTEM VERSIONING
+CREATE TABLE stest3(a integer
+);
+\d stest3
+ Table "public.stest3"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+
+ALTER TABLE stest3 ADD SYSTEM VERSIONING;
+\d stest3
+ Table "public.stest3"
+ Column | Type | Collation | Nullable | Default
+-----------+-----------------------------+-----------+----------+-------------------------------
+ a | integer | | |
+ StartTime | timestamp without time zone | | not null | generated always as row start
+ EndTime | timestamp without time zone | | not null | generated always as row end
+
+-- ALTER TABLE tbName DROP SYSTEM VERSIONING
+ALTER TABLE stest3 DROP SYSTEM VERSIONING;
+\d stest3
+ Table "public.stest3"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+
+-- ALTER TABLE
+ALTER TABLE stest0 ALTER start_timestamp drop not null;
+ERROR: column "start_timestamp" of relation "stest0" is system time column
+ALTER TABLE stest0 ALTER start_timestamp drop not null;
+ERROR: column "start_timestamp" of relation "stest0" is system time column
+ALTER TABLE stest0 ALTER COLUMN start_timestamp SET DATA TYPE char;
+ERROR: column "start_timestamp" of relation "stest0" is system time column
+--truncation
+truncate table stest0;
+ERROR: cannot truncate system versioned table
+-- test UPDATE/DELETE
+INSERT INTO stest0 VALUES (1);
+INSERT INTO stest0 VALUES (2);
+INSERT INTO stest0 VALUES (3);
+SELECT a FROM stest0 ORDER BY a;
+ a
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+UPDATE stest0 SET a = 4 where a = 1;
+SELECT a FROM stest0 ORDER BY a;
+ a
+---
+ 2
+ 3
+ 4
+(3 rows)
+
+select a from for stest0 system_time from '2000-01-01 00:00:00.00000' to 'infinity' ORDER BY a;
+ a
+---
+ 1
+ 2
+ 3
+ 4
+(4 rows)
+
+DELETE FROM stest0 WHERE a = 2;
+SELECT a FROM stest0 ORDER BY a;
+ a
+---
+ 3
+ 4
+(2 rows)
+
+select a from for stest0 system_time from '2000-01-01 00:00:00.00000' to 'infinity' ORDER BY a;
+ a
+---
+ 1
+ 2
+ 3
+ 4
+(4 rows)
+
+-- test with joins
+CREATE TABLE stestx (x int, y int);
+INSERT INTO stestx VALUES (11, 1), (22, 2), (33, 3);
+SELECT a FROM stestx, stest0 WHERE stestx.y = stest0.a;
+ a
+---
+ 3
+(1 row)
+
+DROP TABLE stestx;
+-- views
+CREATE VIEW stest1v AS SELECT a FROM stest0;
+CREATE VIEW stest2v AS select a from for stest0 system_time from '2000-01-01 00:00:00.00000' to 'infinity' ORDER BY a;
+SELECT * FROM stest1v;
+ a
+---
+ 3
+ 4
+(2 rows)
+
+SELECT * FROM stest2v;
+ a
+---
+ 1
+ 2
+ 3
+ 4
+(4 rows)
+
+DROP VIEW stest1v;
+DROP VIEW stest2v;
+-- CTEs
+WITH foo AS (SELECT a FROM stest0) SELECT * FROM foo;
+ a
+---
+ 3
+ 4
+(2 rows)
+
+WITH foo AS (select a from for stest0 system_time from '2000-01-01 00:00:00.00000' to 'infinity' ORDER BY a) SELECT * FROM foo;
+ a
+---
+ 1
+ 2
+ 3
+ 4
+(4 rows)
+
+-- inheritance
+CREATE TABLE stest1 () INHERITS (stest0);
+SELECT * FROM stest1;
+ a | start_timestamp | end_timestamp
+---+-----------------+---------------
+(0 rows)
+
+\d stest1
+ Table "public.stest1"
+ Column | Type | Collation | Nullable | Default
+-----------------+--------------------------------+-----------+----------+-------------------------------
+ a | integer | | not null |
+ start_timestamp | timestamp(6) without time zone | | not null | generated always as row start
+ end_timestamp | timestamp(6) without time zone | | not null | generated always as row end
+Inherits: stest0
+
+INSERT INTO stest1 VALUES (4);
+SELECT a FROM stest1;
+ a
+---
+ 4
+(1 row)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index d33a4e143d..b2c6904751 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -78,7 +78,7 @@ test: brin gin gist spgist privileges init_privs security_label collate matview
# ----------
# Another group of parallel tests
# ----------
-test: create_table_like alter_generic alter_operator misc async dbsize misc_functions sysviews tsrf tidscan collate.icu.utf8
+test: create_table_like alter_generic alter_operator misc async dbsize misc_functions sysviews tsrf tidscan collate.icu.utf8 system_versioned_table
# rules cannot run concurrently with any test that creates
# a view or rule in the public schema
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index f86f5c5682..80023ac9c1 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -122,6 +122,7 @@ test: drop_operator
test: password
test: identity
test: generated
+test: system_versioned_table
test: join_hash
test: create_table_like
test: alter_generic
diff --git a/src/test/regress/sql/system_versioned_table.sql b/src/test/regress/sql/system_versioned_table.sql
new file mode 100644
index 0000000000..76ad03c9b9
--- /dev/null
+++ b/src/test/regress/sql/system_versioned_table.sql
@@ -0,0 +1,104 @@
+
+CREATE TABLE stest0(a integer PRIMARY KEY, start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS row START,
+ end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
+ PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
+) WITH SYSTEM VERSIONING;
+
+--invalid datatype
+CREATE TABLE stest1(a integer PRIMARY KEY, start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS row START,
+ end_timestamp integer GENERATED ALWAYS AS ROW END,
+ PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
+) WITH SYSTEM VERSIONING;
+
+-- references to other column in period columns
+CREATE TABLE stest1(a integer PRIMARY KEY, start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS row START,
+ end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
+ PERIOD FOR SYSTEM_TIME(a, end_timestamp)
+) WITH SYSTEM VERSIONING;
+
+-- duplicate system time column
+CREATE TABLE stest1(a integer PRIMARY KEY, start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS row START,
+ end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
+ end_timestamp1 TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
+ PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
+) WITH SYSTEM VERSIONING;
+
+-- default system time column usage
+CREATE TABLE stest2(a integer
+) WITH SYSTEM VERSIONING;
+
+\d stest2
+
+-- ALTER TABLE tbName ADD SYSTEM VERSIONING
+CREATE TABLE stest3(a integer
+);
+
+\d stest3
+
+ALTER TABLE stest3 ADD SYSTEM VERSIONING;
+
+\d stest3
+
+-- ALTER TABLE tbName DROP SYSTEM VERSIONING
+ALTER TABLE stest3 DROP SYSTEM VERSIONING;
+
+\d stest3
+
+-- ALTER TABLE
+ALTER TABLE stest0 ALTER start_timestamp drop not null;
+
+ALTER TABLE stest0 ALTER start_timestamp drop not null;
+
+ALTER TABLE stest0 ALTER COLUMN start_timestamp SET DATA TYPE char;
+
+
+
+--truncation
+truncate table stest0;
+
+-- test UPDATE/DELETE
+INSERT INTO stest0 VALUES (1);
+INSERT INTO stest0 VALUES (2);
+INSERT INTO stest0 VALUES (3);
+
+SELECT a FROM stest0 ORDER BY a;
+
+UPDATE stest0 SET a = 4 where a = 1;
+
+SELECT a FROM stest0 ORDER BY a;
+
+select a from for stest0 system_time from '2000-01-01 00:00:00.00000' to 'infinity' ORDER BY a;
+
+DELETE FROM stest0 WHERE a = 2;
+
+SELECT a FROM stest0 ORDER BY a;
+select a from for stest0 system_time from '2000-01-01 00:00:00.00000' to 'infinity' ORDER BY a;
+
+-- test with joins
+CREATE TABLE stestx (x int, y int);
+INSERT INTO stestx VALUES (11, 1), (22, 2), (33, 3);
+SELECT a FROM stestx, stest0 WHERE stestx.y = stest0.a;
+
+DROP TABLE stestx;
+
+-- views
+CREATE VIEW stest1v AS SELECT a FROM stest0;
+CREATE VIEW stest2v AS select a from for stest0 system_time from '2000-01-01 00:00:00.00000' to 'infinity' ORDER BY a;
+SELECT * FROM stest1v;
+SELECT * FROM stest2v;
+
+DROP VIEW stest1v;
+DROP VIEW stest2v;
+-- CTEs
+WITH foo AS (SELECT a FROM stest0) SELECT * FROM foo;
+
+WITH foo AS (select a from for stest0 system_time from '2000-01-01 00:00:00.00000' to 'infinity' ORDER BY a) SELECT * FROM foo;
+
+-- inheritance
+CREATE TABLE stest1 () INHERITS (stest0);
+SELECT * FROM stest1;
+
+\d stest1
+
+INSERT INTO stest1 VALUES (4);
+SELECT a FROM stest1;
--
2.17.1
