Hi all ,
Temporal table is one of the main new features added in sql standard 2011.
>From that I will like to implement system versioned temporal table which
allows to keep past and present data so old data can be queried. Am propose
to implement it like below
CREATE
In create table only one table is create and both historical and current
data will be store in it. In order to make history and current data
co-exist row end time column will be added implicitly to primary key.
Regarding performance one can partition the table by row end time column
order to make history data didn't slowed performance.
INSERT
In insert row start time column and row end time column behave like a kind
of generated stored column except they store current transaction time and
highest value supported by the data type which is +infinity respectively.
DELETE and UPDATE
The old data is inserted with row end time column seated to current
transaction time
SELECT
If the query didn’t contain a filter condition that include system time
column, a filter condition will be added in early optimization that filter
history data.
Attached is WIP patch that implemented just the above and done on top of
commit b8e19b932a99a7eb5a. Temporal clause didn’t implemented yet so one
can use regular filter condition for the time being
NOTE: I implement sql standard syntax except it is PERIOD FOR SYSTEM TIME
rather than PERIOD FOR SYSTEM_TIME in CREATE TABLE statement and system
time is not selected unless explicitly asked
Any enlightenment?
regards
Surafel
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index 6bc4e4c036..c229c90e2d 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/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index c9d024ead5..340f0340bd 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -330,6 +330,120 @@ ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot)
MemoryContextSwitchTo(oldContext);
}
+/*
+ * Set row start time in row start time columns 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 columns 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
+ 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
+ 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
*
@@ -430,6 +544,12 @@ 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.
*
@@ -756,6 +876,31 @@ ExecDelete(ModifyTableState *mtstate,
}
else
{
+ /*
+ * Set row end time and insert
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->is_system_versioned)
+ {
+ TupleTableSlot *oslot = NULL;
+
+ oslot = table_slot_create(resultRelationDesc, NULL);
+
+ if (!table_tuple_fetch_row_version(resultRelationDesc, tupleid, SnapshotAny,
+ oslot))
+ {
+ elog(ERROR, "failed to fetch tuple");
+ }
+ else
+ {
+ ExecSetRowEndTime(estate, oslot);
+ table_tuple_insert(resultRelationDesc, oslot,
+ estate->es_output_cid,
+ 0, NULL);
+ }
+ ExecDropSingleTupleTableSlot(oslot);
+ }
+
/*
* delete the tuple
*
@@ -1128,6 +1273,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 *oslot = NULL;
+
+ oslot = table_slot_create(resultRelationDesc, NULL);
+
+ if (!table_tuple_fetch_row_version(resultRelationDesc, tupleid, SnapshotAny,
+ oslot))
+ {
+ elog(ERROR, "failed to fetch tuple");
+ }
+ else
+ {
+ ExecSetRowEndTime(estate, oslot);
+ table_tuple_insert(resultRelationDesc, oslot,
+ estate->es_output_cid,
+ 0, NULL);
+ }
+ ExecDropSingleTupleTableSlot(oslot);
+ }
+
/*
* Check any RLS UPDATE WITH CHECK policies
*
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 3432bb921d..d81c4c8965 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4746,6 +4746,16 @@ _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;
+}
/*
* copyObjectImpl -- implementation of copyObject(); see nodes/nodes.h
@@ -5634,6 +5644,9 @@ copyObjectImpl(const void *from)
case T_PartitionCmd:
retval = _copyPartitionCmd(from);
break;
+ case T_RowTime:
+ retval = _copyRowTime(from);
+ break;
/*
* MISCELLANEOUS NODES
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 18cb014373..cb6f59e4fa 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2911,6 +2911,15 @@ _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;
+}
+
/*
* Stuff from pg_list.h
*/
@@ -3730,6 +3739,9 @@ equal(const void *a, const void *b)
case T_PartitionCmd:
retval = _equalPartitionCmd(a, b);
break;
+ case T_RowTime:
+ retval = _equalRowTime(a, b);
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 17c5f086fb..198f061502 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -57,6 +57,8 @@
#include "parser/analyze.h"
#include "parser/parsetree.h"
#include "parser/parse_agg.h"
+#include "parser/parse_clause.h"
+#include "parser/parse_relation.h"
#include "partitioning/partdesc.h"
#include "rewrite/rewriteManip.h"
#include "storage/dsm_impl.h"
@@ -249,6 +251,9 @@ static bool group_by_has_partkey(RelOptInfo *input_rel,
List *targetList,
List *groupClause);
static int common_prefix_cmp(const void *a, const void *b);
+static bool check_system_versioned_columen( Node *node, RangeTblEntry *rte);
+static bool check_system_versioned_table( RangeTblEntry *rte);
+char * row_end_time_column_name(RangeTblEntry *rte);
/*****************************************************************************
@@ -745,6 +750,67 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
list_length(rte->securityQuals));
}
+ if (parse->commandType == CMD_SELECT)
+ {
+ foreach(l, parse->rtable)
+ {
+
+ RangeTblEntry *rte = lfirst_node(RangeTblEntry, l);
+
+ if(!check_system_versioned_table(rte) ||
+ check_system_versioned_columen(parse->jointree->quals, rte))
+ {
+ continue;
+ }
+ else
+ {
+ Node *wClause;
+ ParseState *pstate;
+ Relation relation;
+ char *endColNme;
+ ColumnRef *c;
+ A_Const *n ;
+
+ endColNme = row_end_time_column_name(rte);
+
+ c = makeNode(ColumnRef);
+ c->location = 0;
+ c->fields = lcons(makeString(endColNme), NIL);
+
+ 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);
+ relation = heap_open(rte->relid, NoLock);
+
+ /*
+ * 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 (parse->jointree->quals != NULL)
+ parse->jointree->quals =make_and_qual(parse->jointree->quals, wClause);
+ else
+ parse->jointree->quals = wClause;
+ }
+
+ }
+ }
+
/*
* Preprocess RowMark information. We need to do this after subquery
* pullup, so that all base relations are present.
@@ -7404,3 +7470,87 @@ group_by_has_partkey(RelOptInfo *input_rel,
return true;
}
+
+/*
+ * Check for references to system versioned columns
+ */
+static bool
+check_system_versioned_columen_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_columen_walker,
+ rte);
+}
+
+static bool
+check_system_versioned_columen( Node *node, RangeTblEntry *rte)
+{
+ return check_system_versioned_columen_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 = heap_open(rte->relid, NoLock);
+ tupdesc = RelationGetDescr(rel);
+ result = tupdesc->constr && tupdesc->constr->is_system_versioned;
+
+ heap_close(rel, NoLock);
+
+ return result;
+}
+
+char *
+row_end_time_column_name(RangeTblEntry *rte)
+{
+ Relation relation;
+ TupleDesc tupdesc;
+ char* name;
+ int natts;
+
+ relation = heap_open(rte->relid, NoLock);
+
+ tupdesc = RelationGetDescr(relation);
+ 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;
+ }
+ }
+
+ heap_close(relation, NoLock);
+
+ return name;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3f67aaf30e..8a7673342c 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
@@ -242,6 +256,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
PartitionSpec *partspec;
PartitionBoundSpec *partboundspec;
RoleSpec *rolespec;
+ struct GenerateType *GenerateType;
+ struct OptionWith *OptionWith;
}
%type <node> stmt schema_stmt
@@ -373,12 +389,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
@@ -539,7 +557,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
@@ -667,7 +685,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
@@ -693,7 +711,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
@@ -3139,12 +3157,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;
@@ -3158,12 +3177,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;
@@ -3177,13 +3197,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;
@@ -3197,13 +3218,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;
@@ -3217,13 +3239,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;
@@ -3237,13 +3260,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;
@@ -3320,6 +3344,7 @@ TableElement:
columnDef { $$ = $1; }
| TableLikeClause { $$ = $1; }
| TableConstraint { $$ = $1; }
+ | optSystemTimeColumn { $$ = $1; }
;
TypedTableElement:
@@ -3416,6 +3441,16 @@ ColConstraint:
}
;
+optSystemTimeColumn:
+ PERIOD FOR SYSTEM_P TIME '(' name ',' name ')'
+ {
+ RowTime *n = makeNode(RowTime);
+ n->start_time = $6;
+ n->end_time = $8;
+ $$ = (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.
@@ -3498,12 +3533,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;
@@ -3521,6 +3556,7 @@ ColConstraintElem:
$$ = (Node *)n;
}
+
| REFERENCES qualified_name opt_column_list key_match key_actions
{
Constraint *n = makeNode(Constraint);
@@ -3543,6 +3579,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
@@ -3922,9 +3982,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; }
@@ -4060,7 +4145,7 @@ create_as_target:
$$->rel = $1;
$$->colNames = $2;
$$->accessMethod = $3;
- $$->options = $4;
+ $$->options = ($4)->options;
$$->onCommit = $5;
$$->tableSpaceName = $6;
$$->viewQuery = NULL;
@@ -15221,6 +15306,7 @@ unreserved_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PERIOD
| PLANS
| POLICY
| PRECEDING
@@ -15326,6 +15412,7 @@ unreserved_keyword:
| VALUE_P
| VARYING
| VERSION_P
+ | VERSIONING
| VIEW
| VIEWS
| VOLATILE
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 4dd81507a7..480b71edfe 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -2621,6 +2621,13 @@ expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset,
continue;
}
+ if (attr->attgenerated == ATTRIBUTE_ROW_START_TIME || attr->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ {
+ if (aliascell)
+ aliascell = lnext(eref->colnames, aliascell);
+ continue;
+ }
+
if (colnames)
{
char *label;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ee47547624..52fe6ce770 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -71,6 +71,8 @@
#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 isSyetemVersioned; /* 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 transformPeriodColumen(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.isSyetemVersioned = false;
+
Assert(!stmt->ofTypename || !stmt->inhRelations); /* grammar enforces */
@@ -284,14 +297,15 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
case T_TableLikeClause:
transformTableLikeClause(&cxt, (TableLikeClause *) element);
break;
-
+ case T_RowTime:
+ transformPeriodColumen(&cxt, (RowTime *) element);
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(element));
break;
}
}
-
/*
* Transfer anything we already have in cxt.alist into save_alist, to keep
* it separate from the output of transformIndexConstraints. (This may
@@ -303,6 +317,20 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
Assert(stmt->constraints == NIL);
+ if (cxt.isSyetemVersioned)
+ {
+ ListCell *lc;
+ foreach(lc, cxt.ixconstraints)
+ {
+ Constraint *constraint = lfirst_node(Constraint, lc);
+
+ if(constraint->contype == CONSTR_PRIMARY && constraint->keys != NIL)
+ {
+ constraint->keys = lappend(constraint->keys, makeString(cxt.endTimeColName));
+ }
+ }
+ }
+
/*
* Postprocess constraints that give rise to index definitions.
*/
@@ -716,6 +744,38 @@ 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->isSyetemVersioned = 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;
+ break;
+
case CONSTR_CHECK:
cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
break;
@@ -1267,6 +1327,27 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
table_close(relation, NoLock);
}
+/*
+ * transformPeriodColumen
+ * transform a period node within CREATE TABLE
+ */
+static void
+transformPeriodColumen(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)
{
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 585dcee5db..14a9ccc988 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -515,6 +515,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).
@@ -569,6 +570,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)
+ constr->is_system_versioned= true;
/* If the column has a default, fill it into the attrdef array */
if (attp->atthasdef)
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/nodes.h b/src/include/nodes/nodes.h
index bce2d59b0d..a91a58fc26 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -477,6 +477,7 @@ typedef enum NodeTag
T_PartitionRangeDatum,
T_PartitionCmd,
T_VacuumRelation,
+ T_RowTime,
/*
* TAGS FOR REPLICATION GRAMMAR PARSE NODES (replnodes.h)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d93a79a554..f93f677757 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -656,6 +656,8 @@ typedef struct ColumnDef
RangeVar *identitySequence; /* to store identity sequence name for
* ALTER TABLE ... ADD COLUMN */
char generated; /* attgenerated setting */
+ char starttime; /* row star time */
+ char endtime; /* row end time */
CollateClause *collClause; /* untransformed COLLATE spec, if any */
Oid collOid; /* collation OID (InvalidOid if not set) */
List *constraints; /* other constraints on column */
@@ -2059,6 +2061,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 +2111,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 */
@@ -3533,4 +3538,11 @@ 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;
+
#endif /* PARSENODES_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 00ace8425e..8215443091 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)
@@ -439,6 +440,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)
