Re: WIP: System Versioned Temporal Table
Surafel Temesgen <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.devel.general |
|---|---|
| Message-ID | <CALAY4q88-1P1CWCJKEGHy1S3azG5g8cP_z_sEh+cd0G14YfmgA@mail.gmail.com> |
On Thu, Jan 2, 2020 at 12:12 AM Vik Fearing <[email protected]> wrote: > This does not compile against current head (0ce38730ac). > > > gram.y: error: shift/reduce conflicts: 6 found, 0 expected > > Rebased and conflict resolved i hope it build clean this time regards Surafel
0002-system-versioned-temporal-table.patch
(text/x-patch, 93.3 KB)
From 640f8fc466acc90f0e46d65f21077f652651f34f Mon Sep 17 00:00:00 2001 From: Surafel Temesgen <[email protected]> Date: Fri, 3 Jan 2020 13:50:19 +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 | 189 +++++++++++++ src/backend/parser/analyze.c | 9 + src/backend/parser/gram.y | 252 ++++++++++++++---- src/backend/parser/parse_clause.c | 76 ++++++ 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 ++++++++ 36 files changed, 1583 insertions(+), 68 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 00bb4cb53d..7de3fdbd11 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 c93a788798..1a39d35f5a 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -3225,6 +3225,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 1c4394abea..a83085372c 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" @@ -1515,6 +1516,7 @@ ExecuteTruncate(TruncateStmt *stmt) bool recurse = rv->inh; Oid myrelid; LOCKMODE lockmode = AccessExclusiveLock; + TupleDesc tupdesc; myrelid = RangeVarGetRelidExtended(rv, lockmode, 0, RangeVarCallbackForTruncate, @@ -1523,6 +1525,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)) { @@ -3585,6 +3595,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; @@ -3615,6 +3626,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 */ @@ -6124,6 +6136,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 * @@ -6501,6 +6519,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 @@ -6601,6 +6625,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); @@ -6847,6 +6877,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); @@ -10625,6 +10661,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); @@ -11608,6 +11649,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)); @@ -15080,7 +15127,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 06bc2b76ed..b028e5dcd8 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" @@ -424,6 +425,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 59d1a31c97..7abe795ba4 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 8034d5a51c..3b697d6073 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 9c8070c640..3df01ef2d4 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 f7d63df6e1..7fbc552ef4 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 a53d47371b..35f0a1cf8a 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 d6f2153593..73413ca6ae 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 3650e8329d..157078ec9e 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 d82fc5ab8b..7ed09f83c7 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,188 @@ 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; + ParseNamespaceItem *newnsitem; + + + 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); + newnsitem = addRangeTableEntryForRelation(pstate, + relation, + AccessShareLock, + NULL, + false, + true); + addNSItemToQuery(pstate, newnsitem, 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 447a61ef8c..e57ecf2257 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1220,6 +1220,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 ad5be902b0..2356a2be2e 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 @@ -731,6 +749,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %nonassoc BETWEEN IN_P LIKE ILIKE SIMILAR NOT_LA %nonassoc ESCAPE /* ESCAPE must be just above LIKE/ILIKE/SIMILAR */ %left POSTFIXOP /* dummy for postfix Op rules */ +%nonassoc SYSTEM_P +%nonassoc VERSIONING /* * To support target_el without AS, we must give IDENT an explicit priority * between POSTFIXOP and Op. We can safely assign the same priority to @@ -2101,7 +2121,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); @@ -2223,7 +2251,19 @@ alter_table_cmd: $$ = (Node *)n; } /* ALTER TABLE <name> DROP [COLUMN] IF EXISTS <colname> [RESTRICT|CASCADE] */ - | DROP opt_column IF_P EXISTS ColId opt_drop_behavior + | DROP IF_P EXISTS ColId opt_drop_behavior + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_DropColumn; + n->name = $4; + n->behavior = $5; + n->missing_ok = true; + $$ = (Node *)n; + } + /* + * Redundancy here is needed to avoid shift/reduce conflicts. + */ + | DROP COLUMN IF_P EXISTS ColId opt_drop_behavior { AlterTableCmd *n = makeNode(AlterTableCmd); n->subtype = AT_DropColumn; @@ -2232,8 +2272,20 @@ alter_table_cmd: n->missing_ok = true; $$ = (Node *)n; } - /* ALTER TABLE <name> DROP [COLUMN] <colname> [RESTRICT|CASCADE] */ - | DROP opt_column ColId opt_drop_behavior + /* ALTER TABLE <name> DROP <colname> [RESTRICT|CASCADE] */ + | DROP ColId opt_drop_behavior + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_DropColumn; + n->name = $2; + n->behavior = $3; + n->missing_ok = false; + $$ = (Node *)n; + } + /* + * Redundancy here is needed to avoid shift/reduce conflicts. + */ + | DROP COLUMN ColId opt_drop_behavior { AlterTableCmd *n = makeNode(AlterTableCmd); n->subtype = AT_DropColumn; @@ -2242,6 +2294,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 +3202,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 +3222,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 +3242,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 +3263,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 +3284,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 +3305,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 +3389,7 @@ TableElement: columnDef { $$ = $1; } | TableLikeClause { $$ = $1; } | TableConstraint { $$ = $1; } + | optSystemTimeColumn { $$ = $1; } ; TypedTableElement: @@ -3418,6 +3486,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 +3578,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 +3601,7 @@ ColConstraintElem: $$ = (Node *)n; } + | REFERENCES qualified_name opt_column_list key_match key_actions { Constraint *n = makeNode(Constraint); @@ -3545,6 +3624,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 +4027,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 +4190,7 @@ create_as_target: $$->rel = $1; $$->colNames = $2; $$->accessMethod = $3; - $$->options = $4; + $$->options = ($4)->options; $$->onCommit = $5; $$->tableSpaceName = $6; $$->viewQuery = NULL; @@ -11321,7 +11449,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 +11481,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 +11961,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 +11990,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 +12032,7 @@ values_clause: *****************************************************************************/ from_clause: - FROM from_list { $$ = $2; } + FROM from_list { $$ = $2 ; } | /*EMPTY*/ { $$ = NIL; } ; @@ -11925,6 +12057,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 +12160,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 Sconst TO Sconst + { + $$ = makeNode(TemporalClause); + $$->kind = FROM_TO; + $$->from = makeStringConst($3, @3); + $$->to = makeStringConst($5, @5); + } + ; /* * It may seem silly to separate joined_table from table_ref, but there is @@ -15284,6 +15442,7 @@ unreserved_keyword: | PARTITION | PASSING | PASSWORD + | PERIOD | PLANS | POLICY | PRECEDING @@ -15360,6 +15519,7 @@ unreserved_keyword: | SUPPORT | SYSID | SYSTEM_P + | SYSTEM_TIME | TABLES | TABLESPACE | TEMP @@ -15389,6 +15549,7 @@ unreserved_keyword: | VALUE_P | VARYING | VERSION_P + | VERSIONING | VIEW | VIEWS | VOLATILE @@ -16083,29 +16244,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 5fa42d307a..d99001e106 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" @@ -97,6 +98,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); /* @@ -1141,6 +1143,35 @@ transformFromClauseItem(ParseState *pstate, Node *n, rte->tablesample = transformRangeTableSample(pstate, rts); return rel; } + else if (IsA(n, TemporalClause)) + { + TemporalClause *tc = (TemporalClause *) n; + RangeVar *rv = (RangeVar *) tc->relation; + RangeTblRef *rtr; + ParseNamespaceItem *nsitem; + RangeTblEntry *rte; + Relation rel; + TupleDesc tupdesc; + + nsitem = transformTableEntry(pstate, rv); + rte = nsitem->p_rte; + rel = table_open(rte->relid, NoLock); + tupdesc = RelationGetDescr(rel); + rte->system_versioned = (tupdesc->constr && tupdesc->constr->is_system_versioned); + table_close(rel, NoLock); + + 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); + *top_nsitem = nsitem; + *namespace = list_make1(nsitem); + rtr = makeNode(RangeTblRef); + rtr->rtindex = nsitem->p_rtindex; + return (Node *) rtr; + } else if (IsA(n, JoinExpr)) { /* A newfangled join expression */ @@ -3669,3 +3700,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_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 42095ab830..8144d58d70 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) { @@ -3078,6 +3188,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 @@ -3121,6 +3235,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 4474658ddf..0dc1fe9587 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 df025a5a30..ff7843cd37 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 799b6988b7..9f8704b498 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 f3c7eb96fa..8cb6eb8d3d 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 d17af13ee3..d3f74ddba7 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 591e0d65ae..500a5441b8 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 0495cae051..ab5be9ad2a 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 31d9aedeeb..585ad10ce7 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 baced7eec0..245e8aa3d0 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 f67bd9fad5..6156aa5376 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 c29a7091ec..fdce5d01db 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 0fe4e6cb20..f8f80afc0a 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 d25819aa28..fdb4da1b70 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