public inbox for [email protected]
help / color / mirror / Atom feedFrom: Simon Riggs <[email protected]>
To: vignesh C <[email protected]>
Cc: Ibrar Ahmed <[email protected]>
Cc: Surafel Temesgen <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: Rémi Lapeyre <[email protected]>
Cc: Ryan Lambert <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Eli Marmor <[email protected]>
Cc: David Steele <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Georgios <[email protected]>
Cc: Li Japin <[email protected]>
Subject: Re: WIP: System Versioned Temporal Table
Date: Tue, 10 Aug 2021 13:20:14 +0100
Message-ID: <CANbhV-GeD9PWHMkrZLV2vHASjFk3n2QFhGur6+o1gnAf5wymAg@mail.gmail.com> (raw)
In-Reply-To: <CALDaNm3-8ZvqFOcZwpN6MYV+Apk9=mHTL2SBJJsUmrR_ZdVznA@mail.gmail.com>
References: <[email protected]>
<CALAY4q_LCeyMj2KPq2aA5f3L-fPF-kP_w1nuwjZJkrop-o_w=Q@mail.gmail.com>
<CAN-V+g_ZpK7cbx7szKV8Ma6sNNGy4d34sGh5FMy14h68F8gjBQ@mail.gmail.com>
<CALAY4q-JMDqW5=LH6f70WhiO1+tP2NhGbWv_fjPtOt=KezchUQ@mail.gmail.com>
<CAD21AoAUDJj_y_5fH5FLBLUDTZa579jbE5Y12SQz4nJh-RVx7A@mail.gmail.com>
<CANbhV-EZu-SHXERZ=Bbq5g+ePi7QUX_0WgLtRsove_Jiigtaaw@mail.gmail.com>
<CANbhV-EOTSc2EWS_LKiXKgLqnrjtaU0hf3Rzxn111nir6Pn4zA@mail.gmail.com>
<CANbhV-ECkPhsXEtj7gYFjXCdq+3WTiHo9XkF5wQmFOj4w+YTaw@mail.gmail.com>
<CANbhV-Fb35HMoySGMFfcxN_3i45Bb6cZODOcz8S7+Nys3Y2raA@mail.gmail.com>
<CAN-V+g-D7bObC3m5rcm3zr8MmTVE4_OVJuEp8Qse0ZgE4PAhsw@mail.gmail.com>
<CANbhV-F3S=5+5w19cowPwewBXSV3+C5T3BD327KQD7BLFgZbCQ@mail.gmail.com>
<CAN-V+g_7EC=kAVeQV=+XL+KoV=QLdx_79J55OBarEYd49_uHRA@mail.gmail.com>
<CANbhV-H-Wt7TVEwzzaYNEZ8-OteipdtvsxN1Qr1G9XpZoX2GgA@mail.gmail.com>
<CANbhV-EymXWOFdX-Sz7PqQ8kphqHMteErBgD=FxmE+uF7jVW4w@mail.gmail.com>
<[email protected]>
<CALAY4q-4YpPc=hSGpxCJBVwpTKzm2A5JyoXUr+2Lh0KTiUcjPQ@mail.gmail.com>
<[email protected]>
<CALtqXTeMemTWNUQfa9_M+p=eOcvw5yLyfRReBu2BrAbo8rkYNw@mail.gmail.com>
<CALDaNm3-8ZvqFOcZwpN6MYV+Apk9=mHTL2SBJJsUmrR_ZdVznA@mail.gmail.com>
On Wed, 14 Jul 2021 at 12:48, vignesh C <[email protected]> wrote:
> The patch does not apply on Head anymore, could you rebase and post a
> patch. I'm changing the status to "Waiting for Author".
OK, so I've rebased the patch against current master to take it to v15.
I've then worked on the patch some myself to make v16 (attached),
adding these things:
* Add code, docs and test to remove the potential anomaly where
endtime < starttime, using the sqlstate 2201H as pointed out by Vik
* Add code and tests to handle multiple changes in a transaction
correctly, according to SQL Std
* Add code and tests to make Foreign Keys behave correctly, according to SQL Std
* Fixed nascent bug in relcache setup code
* Various small fixes from Japin's review - thanks! I've used
starttime and endtime as default column names
* Additional tests and docs to show that the functionality works with
or without PKs on the table
I am now satisfied that the patch does not have any implementation
anomalies in behavioral design, but it is still a long way short in
code architecture.
There are various aspects still needing work. This is not yet ready
for Commit, but it is appropriate now to ask for initial design
guidance on architecture and code placement by a Committer, so I am
setting this to Ready For Committer, in the hope that we get the
review in SeptCF and a later version can be submitted for later commit
in JanCF. With the right input, this patch is about a person-month
away from being ready, assuming we don't hit any blocking issues.
Major Known Issues
* SQLStd says that it should not be possible to update historical
rows, but those tests show we fail to prevent that and there is code
marked NOT_USED in those areas
* The code is structured poorly around
parse-analyze/rewriter/optimizer/executor and that needs positive
design recommendations, rather than critical review
* Joins currently fail because of the botched way WHERE clauses are
added, resulting in duplicate names
* Views probably don't work, but there are no tests
* CREATE TABLE (LIKE foo) doesn't correctly copy across all features -
test for that added, with test failure accepted for now
* ALTER TABLE is still incomplete and also broken; I suggest we remove
that for the first version of the patch to reduce patch size for an
initial commit.
Minor Known Issues
* Logical replication needs some minor work, no tests yet
* pg_dump support looks like it exists and might work easily, but
there are no tests yet
* Correlation names don't work in FROM clause - shift/reduce errors
from double use of AS
* Add test and code to prevent triggers referencing period cols in the
WHEN clause
* No tests yet to prove you can't set various parameters/settings on
the period time start/end cols
* Code needs some cleanup in a few places
* Not really sure what value is added by
lock-update-delete-system-versioned.spec
* IMHO we should make the PK definition use "endtime DESC", so that
the current version is always the first row found in the PK for any
key, since historical indexes will grow bigger over time
There are no expected issues with integration with MERGE, since SQLStd
explains how to handle that.
Other reviews are welcome.
--
Simon Riggs http://www.EnterpriseDB.com/
Attachments:
[application/octet-stream] system-versioning-temporal-table_v16.patch (120.7K, ../CANbhV-GeD9PWHMkrZLV2vHASjFk3n2QFhGur6+o1gnAf5wymAg@mail.gmail.com/2-system-versioning-temporal-table_v16.patch)
download | inline diff:
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index e0ffb020bf..9f557a92ef 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4944,6 +4944,111 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
</para>
</sect1>
+ <sect1 id="ddl-temporal">
+ <title>Temporal Data</title>
+
+ <indexterm>
+ <primary>temporal data</primary>
+ </indexterm>
+ <indexterm>
+ <primary>system versioning</primary>
+ </indexterm>
+ <indexterm>
+ <primary>SYSTEM_TIME</primary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> implements a table option
+ for system versioning that records each change permanently,
+ allowing access to the full history of data changes.
+ </para>
+
+ <para>
+ System versioning is optional and can be defined easily for a table:
+<programlisting>
+CREATE TABLE products (
+ pno integer,
+ name text,
+ price numeric
+) WITH SYSTEM VERSIONING;
+</programlisting>
+ When system versioning is specified two columns are added which
+ record the start timestamp and end timestamp of each row version.
+ The data type of these columns will be TIMESTAMP WITH TIME ZONE.
+ By default, the column names will be <literal>starttime</literal>
+ and <literal>endtime</literal>, though you can specify different
+ names if you choose. The <literal>starttime</literal> column
+ will be automatically added to the Primary Key of the table.
+ </para>
+ <para>
+ <literal>starttime</literal> is generated always when a new row
+ version is added by INSERT or UPDATE, with <literal>endtime</literal>
+ set at <literal>Infinity</literal>.
+ System versioning implements an UPDATE and DELETE as normal,
+ plus an additional row inserted to record the now-old version
+ of the row, with the <literal>endtime</literal> set, but only
+ for the first change in any transaction. Multiple updates or
+ insert-deletes don't record history.
+ This sequence of commands produces the full history shown in
+ the resulting query:
+<programlisting>
+INSERT INTO products VALUES (100, 'Wash Machine', 300.0);
+INSERT INTO products VALUES (200, 'Ext Warranty', 50.0);
+INSERT INTO products VALUES (300, 'Laptop', 250.0);
+UPDATE products SET price = 75.0 WHERE pno = 200;
+UPDATE products SET price = 350.0 WHERE pno = 300;
+UPDATE products SET pno = 400 WHERE pno = 100;
+DELETE FROM products WHERE pno = 300;
+INSERT INTO products VALUES (500, 'Spare Parts', 25.0);
+
+SELECT * FROM products FOR system_time FROM '-infinity' TO 'infinity' ORDER BY product_no, start_timestamp;
+ pno | name | price | starttime | endtime
+------------+-------------------+-------+-------------------------------------+-------------------------
+ 100 | Wash Machine | 300.0 | Mon Jan 11 03:47:36.020361 2021 PST | Mon Jan 11 03:47:36.027029 2021 PST
+ 200 | Ext Warranty | 50.0 | Mon Jan 11 03:47:36.022847 2021 PST | Mon Jan 11 03:47:36.026411 2021 PST
+ 200 | Ext Warranty | 75.0 | Mon Jan 11 03:47:36.026411 2021 PST | infinity
+ 300 | Laptop | 250.0 | Mon Jan 11 03:47:36.023316 2021 PST | Mon Jan 11 03:47:36.026779 2021 PST
+ 300 | Laptop | 350.0 | Mon Jan 11 03:47:36.026779 2021 PST | Mon Jan 11 03:47:36.029086 2021 PST
+ 400 | Wash Machine | 300.0 | Mon Jan 11 03:47:36.027029 2021 PST | infinity
+ 500 | Spare Parts | 25.0 | Mon Jan 11 03:47:36.032077 2021 PST | infinity
+(7 rows)
+</programlisting>
+ With system versioning, UPDATE and DELETE increase the number of
+ visible rows in the table. System versioning works alongside MVCC,
+ so in addition, UPDATEs, DELETEs and aborted transactions will still
+ cause bloat that needs to be removed by VACUUM.
+ </para>
+ <para>
+ Temporal queries are discussed in <xref linkend="temporal-queries"/>.
+ </para>
+ <para>
+ A Primary Key constraint is not required, but if one exists then
+ the system versioning period end column will be added to the
+ Primary Key of the table to ensure UPDATEs do not cause uniqueness
+ violations. Foreign Keys referencing a system versioned table must
+ match against a current row, hence FKs must match against all columns
+ of the PK except the period end columns. FK lookups will filter out
+ historical rows.
+ </para>
+ <para>
+ The user may never set the system time column values explicitly
+ on INSERT for generated columns.
+ </para>
+ <para>
+ SELECT * currently returns the period start and end columns, even
+ when added implicitly.
+ </para>
+ <para>
+ The SQL Standard defines that the timestamps used should be the
+ CURRENT_TIMESTAMP value defined at statement start. This will
+ cause an ERROR with SQLState 2201H, rather than allow a period end
+ to be set to a value earlier than the period start time. This
+ situation could arise when an UPDATE attempts to update a tuple
+ modified by a transaction that started earlier than the updating
+ transaction.
+ </para>
+ </sect1>
+
<sect1 id="ddl-others">
<title>Other Database Objects</title>
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 834b83b509..dcb5141f0b 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -2703,4 +2703,92 @@ SELECT * FROM t;
</sect1>
+ <sect1 id="temporal-queries">
+ <title>Temporal Queries</title>
+
+ <para>
+ <productname>PostgreSQL</productname> implements an option to
+ to add system versioning onto a user table. Such tables can
+ be accessed normally to see the current contents, but also
+ allow you to access data using both temporal and
+ historical queries. Queries must specify a period name,
+ which for system versioning must be <quote>SYSTEM_TIME</quote>.
+ </para>
+
+ <para>
+ Historical queries show the changes to rows over time, such as the
+ following query that shows changes to a company's products and pricing:
+<programlisting>
+SELECT * FROM products FOR system_time BETWEEN '-infinity' AND 'infinity' ORDER BY pno, starttime;
+ pno | name | price | starttime | endtime
+-----+-------------------+-------+-------------------------------------+-------------------------------------
+ 100 | Washing Machine | 300.0 | Mon Jan 11 03:47:36.020361 2021 PST | Mon Jan 11 08:47:36.027029 2021 PST
+ 200 | Extended Warranty | 50.0 | Mon Jan 11 03:47:36.022847 2021 PST | Mon Jan 11 05:47:36.02641 2021 PST
+ 200 | Extended Warranty | 75.0 | Mon Jan 11 05:47:36.02641 2021 PST | infinity
+ 300 | Laptop | 250.0 | Mon Jan 11 03:47:36.023316 2021 PST | Mon Jan 11 06:47:36.026779 2021 PST
+ 300 | Laptop | 350.0 | Mon Jan 11 06:47:36.026779 2021 PST | Mon Jan 11 07:47:36.029086 2021 PST
+ 400 | Washing Machine | 300.0 | Mon Jan 11 08:47:36.027029 2021 PST | infinity
+ 500 | Spare Parts | 25.0 | Mon Jan 11 09:47:36.032077 2021 PST | infinity
+(7 rows)
+</programlisting>
+ This query shows the full history over all time since it includes
+ all changes between a timestamp of '-infinity' to 'infinity'.
+ The user can specify tighter time ranges to filter away unwanted
+ parts of the change history.
+ </para>
+
+ <para>
+ Normal non-temporal queries show the current contents of the
+ system versioned table, just like normal tables:
+<programlisting>
+SELECT pno, name, price FROM products ORDER BY pno;
+ pno | name | price
+-----+--------------+-------
+ 200 | Ext Warranty | 75.0
+ 400 | Wash Machine | 300.0
+ 500 | Spare Parts | 25.0
+(3 rows)
+</programlisting>
+ which is achieved by generating extra WHERE clauses to exclude
+ the historical row versions from the query. This can be seen
+ more clearly using <command>EXPLAIN</command>:
+<programlisting>
+EXPLAIN (COSTS OFF) SELECT * FROM products;
+ QUERY PLAN
+------------------------------------------------------------------
+ Seq Scan on products
+ Filter: (endtime = 'infinity'::timestamp with time zone)
+(2 rows)
+</programlisting>
+ which shows that the presence of historical row versions will
+ affect the performance of all types of query on tables with
+ system versioning.
+ </para>
+
+ <para>
+ Temporal queries show the table rows at a specific timestamp, e.g.
+<programlisting>
+SELECT pno, name, price FROM products FOR system_time AS OF 'Mon Jan 11 07:30:00 2021 PST' ORDER BY pno;
+ pno | name | price
+-----+--------------+-------
+ 100 | Wash Machine | 300.0
+ 200 | Ext Warranty | 75.0
+ 300 | Laptop | 350.0
+(3 rows)
+</programlisting>
+
+ or
+
+<programlisting>
+SELECT pno, name, price FROM products FOR system_time AS OF 'Mon Jan 11 09:30:00 2021 PST' ORDER BY pno;
+ pno | name | price
+-----+--------------+-------
+ 200 | Ext Warranty | 75.0
+ 400 | Wash Machine | 300.0
+(2 rows)
+</programlisting>
+ </para>
+
+ </sect1>
+
</chapter>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 81291577f8..57811e88c7 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
@@ -161,6 +163,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>ADD SYSTEM VERSIONING</literal></term>
+ <listitem>
+ <para>
+ This form enables system versioning to the table by adding two columns which
+ record the period start and period end for the validity of each row version.
+ The column names will be starttime and endtime respectively, though the data
+ types are TIMESTAMP WITH TIME ZONE. See
+ <xref linkend="ddl-temporal"/>
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>DROP COLUMN [ IF EXISTS ]</literal></term>
<listitem>
@@ -180,6 +195,18 @@ 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 from the table.
+ Indexes and table constraints involving system versioning columns will be
+ automatically dropped along with system versioning columns. If the table is
+ not empty then history records are also removed.
+ </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 15aed2f251..9f4f0519b8 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 ]
@@ -912,6 +916,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 generated column.
+ 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 generated column.
+ 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>
@@ -1018,6 +1044,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>PERIOD FOR SYSTEM_TIME ( <replaceable class="parameter">row_start_time_column</replaceable>, <replaceable class="parameter">row_end_time_column</replaceable> )</literal></term>
+ <listitem>
+ <para>
+ Specifies the pair of columns that hold the row start
+ timestamp and row end timestamp column names.
+ </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>
@@ -1272,6 +1308,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>WITH SYSTEM VERSIONING</literal></term>
+ <listitem>
+ <para>
+ This clause specifies that the table contains multiple historical
+ row versions that may be viewed using temporal queries.
+ If period columns are not specified explicitly the default columns
+ StartTime and EndTime will be added automatically.
+ </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 fa676b1698..326ab99588 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -60,6 +60,15 @@ 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> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</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 BETWEEN ASYMMETRIC <replaceable class="parameter">start_time</replaceable> AND <replaceable class="parameter">end_time</replaceable>
+ <replaceable class="parameter">table_name</replaceable> FOR SYSTEM_TIME BETWEEN SYMMETRIC <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>
+
+
<phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
@@ -582,6 +591,42 @@ 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>
+ Allows temporal queries for tables defined with system versioning.
+ This specifies a single timestamp that is used in place of the
+ normal transaction snapshot to determine which row version of a
+ row is visible for this query. At most one row version will be
+ returned for any row.
+ </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>
+ <term><literal>FOR SYSTEM_TIME FROM <replaceable class="parameter">start_time</replaceable> TO <replaceable class="parameter">end_time</replaceable></literal></term>
+ <listitem>
+ <para>
+ For tables with system versioning this specifies that all
+ row versions visible at any point over the time range
+ will be visible to this query.
+ Note that this form of query allows multiple row versions to be
+ returned for one row rather than just a single row. It allows
+ the query to inspect the history of UPDATEs and DELETEs
+ that have occured to row after the initial INSERT, over
+ the time range specified. To see all changes over time
+ specify a start_time of '-Infinity' and an end_time of 'Infinity'.
+ </para>
+ <para>
+ Optionally, <replaceable class="parameter">SYMMETRIC</replaceable>
+ or <replaceable class="parameter">ASYMMETRIC</replaceable> may
+ be specified for the time range.
+ </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 4c63bd4dc6..8e895876af 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -168,6 +168,7 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
cpy->has_not_null = constr->has_not_null;
cpy->has_generated_stored = constr->has_generated_stored;
+ cpy->has_system_versioning = constr->has_system_versioning;
if ((cpy->num_defval = constr->num_defval) > 0)
{
@@ -477,6 +478,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (constr1->has_generated_stored != constr2->has_generated_stored)
return false;
+ if (constr1->has_system_versioning != constr2->has_system_versioning)
+ return false;
n = constr1->num_defval;
if (n != (int) constr2->num_defval)
return false;
@@ -847,6 +850,7 @@ BuildDescForRelation(List *schema)
constr->has_not_null = true;
constr->has_generated_stored = false;
+ constr->has_system_versioning = false;
constr->defval = NULL;
constr->missing = NULL;
constr->num_defval = 0;
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 40a54ad0bd..9fbc1f8330 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1026,6 +1026,11 @@ CopyFrom(CopyFromState cstate)
ExecComputeStoredGenerated(resultRelInfo, estate, myslot,
CMD_INSERT);
+ /* Set system time columns */
+ if (resultRelInfo->ri_RelationDesc->rd_att->constr &&
+ resultRelInfo->ri_RelationDesc->rd_att->constr->has_system_versioning)
+ ExecSetRowStartTime(estate, myslot, resultRelInfo);
+
/*
* 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 b18de38e73..c449d9f0a5 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -59,6 +59,7 @@
#include "commands/typecmds.h"
#include "commands/user.h"
#include "executor/executor.h"
+#include "executor/nodeModifyTable.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
#include "miscadmin.h"
@@ -77,6 +78,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"
@@ -181,6 +183,9 @@ typedef struct AlteredTableInfo
bool chgPersistence; /* T if SET LOGGED/UNLOGGED is used */
char newrelpersistence; /* if above is true */
Expr *partition_constraint; /* for attach partition validation */
+ bool systemVersioningAdded; /* is system time column added? */
+ bool systemVersioningRemoved; /* is system time column removed? */
+ AttrNumber attnum; /* which column is system end time column */
/* true, if validating default due to some other attach/detach */
bool validate_default;
/* Objects to rebuild after completing ALTER TYPE operations */
@@ -454,11 +459,12 @@ static ObjectAddress ATExecSetStorage(Relation rel, const char *colName,
static void ATPrepDropColumn(List **wqueue, Relation rel, bool recurse, bool recursing,
AlterTableCmd *cmd, LOCKMODE lockmode,
AlterTableUtilityContext *context);
-static ObjectAddress ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
+static ObjectAddress ATExecDropColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, const char *colName,
DropBehavior behavior,
bool recurse, bool recursing,
bool missing_ok, LOCKMODE lockmode,
- ObjectAddresses *addrs);
+ ObjectAddresses *addrs,
+ AlterTableUtilityContext *context);
static ObjectAddress ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
IndexStmt *stmt, bool is_rebuild, LOCKMODE lockmode);
static ObjectAddress ATExecAddStatistics(AlteredTableInfo *tab, Relation rel,
@@ -1626,6 +1632,7 @@ ExecuteTruncate(TruncateStmt *stmt)
bool recurse = rv->inh;
Oid myrelid;
LOCKMODE lockmode = AccessExclusiveLock;
+ TupleDesc tupdesc;
myrelid = RangeVarGetRelidExtended(rv, lockmode,
0, RangeVarCallbackForTruncate,
@@ -1644,6 +1651,14 @@ ExecuteTruncate(TruncateStmt *stmt)
*/
truncate_check_activity(rel);
+ tupdesc = RelationGetDescr(rel);
+
+ /* throw error for system versioned table */
+ if (tupdesc->constr && tupdesc->constr->has_system_versioning)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot truncate table with system versioning")));
+
rels = lappend(rels, rel);
relids = lappend_oid(relids, myrelid);
@@ -4101,6 +4116,8 @@ AlterTableGetLockLevel(List *cmds)
case AT_SetAccessMethod: /* must rewrite heap */
case AT_SetTableSpace: /* must rewrite heap */
case AT_AlterColumnType: /* must rewrite heap */
+ case AT_PeriodColumn:
+ case AT_AddSystemVersioning:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -4129,6 +4146,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 */
@@ -4794,6 +4812,35 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode,
lfirst_node(AlterTableCmd, lcmd),
lockmode, pass, context);
+ /*
+ * Both system time columns have to be specified
+ */
+ if (context)
+ {
+ if (context->hasSystemVersioning)
+ {
+ if (!context->startTimeColName)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("period start time column not specified")));
+
+ if (!context->endTimeColName)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("period end time column not specified")));
+
+ if (context->periodStart && strcmp(context->periodStart, context->startTimeColName) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("period start time column name must be the same as the name of row start time column")));
+
+ if (context->periodEnd && strcmp(context->periodEnd, context->endTimeColName) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("period end time column name must be the same as the name of row end time column")));
+ }
+ }
+
/*
* After the ALTER TYPE pass, do cleanup work (this is not done in
* ATExecAlterColumnType since it should be done only once if
@@ -4902,16 +4949,16 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
lockmode);
break;
case AT_DropColumn: /* DROP COLUMN */
- address = ATExecDropColumn(wqueue, rel, cmd->name,
+ address = ATExecDropColumn(wqueue, tab, rel, cmd->name,
cmd->behavior, false, false,
cmd->missing_ok, lockmode,
- NULL);
+ NULL, context);
break;
case AT_DropColumnRecurse: /* DROP COLUMN with recursion */
- address = ATExecDropColumn(wqueue, rel, cmd->name,
+ address = ATExecDropColumn(wqueue, tab, rel, cmd->name,
cmd->behavior, true, false,
cmd->missing_ok, lockmode,
- NULL);
+ NULL, context);
break;
case AT_AddIndex: /* ADD INDEX */
address = ATExecAddIndex(tab, rel, (IndexStmt *) cmd->def, false,
@@ -5198,7 +5245,7 @@ ATParseTransformCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Transform the AlterTableStmt */
atstmt = transformAlterTableStmt(RelationGetRelid(rel),
atstmt,
- context->queryString,
+ context,
&beforeStmts,
&afterStmts);
@@ -5586,6 +5633,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
BulkInsertState bistate;
int ti_options;
ExprState *partqualstate = NULL;
+ ResultRelInfo *resultRelInfo;
/*
* Open the relation(s). We have surely already locked the existing
@@ -5623,6 +5671,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
*/
estate = CreateExecutorState();
+ resultRelInfo = makeNode(ResultRelInfo);
/* Build the needed expression execution states */
foreach(l, tab->constraints)
@@ -5690,6 +5739,12 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
ListCell *lc;
Snapshot snapshot;
+ InitResultRelInfo(resultRelInfo,
+ oldrel,
+ 0, /* dummy rangetable index */
+ NULL,
+ 0);
+
if (newrel)
ereport(DEBUG1,
(errmsg_internal("rewriting table \"%s\"",
@@ -5778,6 +5833,13 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
slot_getallattrs(oldslot);
ExecClearTuple(newslot);
+ /* Only current data have to be in */
+ if (tab->systemVersioningRemoved)
+ {
+ if (oldslot->tts_values[tab->attnum - 1] != PG_INT64_MAX)
+ continue;
+ }
+
/* copy attributes */
memcpy(newslot->tts_values, oldslot->tts_values,
sizeof(Datum) * oldslot->tts_nvalid);
@@ -5851,6 +5913,10 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
insertslot = oldslot;
}
+ /* Set system time columns */
+ if (tab->systemVersioningAdded)
+ ExecSetRowStartTime(estate, insertslot, resultRelInfo);
+
/* Now check any constraints on the possibly-changed tuple */
econtext->ecxt_scantuple = insertslot;
@@ -6115,6 +6181,12 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "ALTER COLUMN ... DROP IDENTITY";
case AT_ReAddStatistics:
return NULL; /* not real grammar */
+ case AT_AddSystemVersioning:
+ return "ALTER TABLE ... ADD SYSTEM VERSIONING";
+ case AT_DropSystemVersioning:
+ return "ALTER TABLE ... DROP SYSTEM VERSIONING";
+ case AT_PeriodColumn:
+ return "ALTER TABLE ... ADD PERIOD COLUMN";
}
return NULL;
@@ -6803,6 +6875,14 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
tab->rewrite |= AT_REWRITE_DEFAULT_VAL;
}
+ if (colDef->generated == ATTRIBUTE_ROW_START_TIME ||
+ colDef->generated == ATTRIBUTE_ROW_END_TIME)
+ {
+ /* must do a rewrite for system time columns */
+ tab->rewrite |= AT_REWRITE_COLUMN_REWRITE;
+ tab->systemVersioningAdded = true;
+ }
+
/*
* Tell Phase 3 to fill in the default expression, if there is one.
*
@@ -7123,6 +7203,13 @@ 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
*
@@ -7566,6 +7653,13 @@ 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
@@ -7666,6 +7760,13 @@ 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);
@@ -8073,6 +8174,13 @@ 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);
@@ -8304,11 +8412,12 @@ ATPrepDropColumn(List **wqueue, Relation rel, bool recurse, bool recursing,
* checked recursively.
*/
static ObjectAddress
-ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
+ATExecDropColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, const char *colName,
DropBehavior behavior,
bool recurse, bool recursing,
bool missing_ok, LOCKMODE lockmode,
- ObjectAddresses *addrs)
+ ObjectAddresses *addrs,
+ AlterTableUtilityContext *context)
{
HeapTuple tuple;
Form_pg_attribute targetatt;
@@ -8351,6 +8460,32 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
attnum = targetatt->attnum;
+#ifdef NOTUSED
+ if (targetatt->attgenerated == ATTRIBUTE_ROW_START_TIME ||
+ targetatt->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" of relation \"%s\" is system time column",
+ colName, RelationGetRelationName(rel))));
+#endif
+
+ /*
+ * Reviewers note: We should be disallowing DROP COLUMN on a
+ * system time column, but DROP SYSTEM VERSIONING is currently
+ * kluged to generate multiple DropColumn subcommands, which
+ * means we need to allow this for now, even though an explicit
+ * DROP COLUMN will crash the server. Needs work.
+ */
+ if (targetatt->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ {
+ tab->attnum = attnum;
+ tab->systemVersioningRemoved = true;
+ tab->rewrite |= AT_REWRITE_COLUMN_REWRITE;
+ context->endTimeColName = NameStr(targetatt->attname);
+ }
+ if (targetatt->attgenerated == ATTRIBUTE_ROW_START_TIME)
+ context->startTimeColName = NameStr(targetatt->attname);
+
/* Can't drop a system attribute */
if (attnum <= 0)
ereport(ERROR,
@@ -8412,11 +8547,15 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
Oid childrelid = lfirst_oid(child);
Relation childrel;
Form_pg_attribute childatt;
+ AlteredTableInfo *childtab;
/* find_inheritance_children already got lock */
childrel = table_open(childrelid, NoLock);
CheckTableNotInUse(childrel, "ALTER TABLE");
+ /* Find or create work queue entry for this table */
+ childtab = ATGetQueueEntry(wqueue, childrel);
+
tuple = SearchSysCacheCopyAttName(childrelid, colName);
if (!HeapTupleIsValid(tuple)) /* shouldn't happen */
elog(ERROR, "cache lookup failed for attribute \"%s\" of relation %u",
@@ -8437,9 +8576,9 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
if (childatt->attinhcount == 1 && !childatt->attislocal)
{
/* Time to delete this child column, too */
- ATExecDropColumn(wqueue, childrel, colName,
+ ATExecDropColumn(wqueue, childtab, childrel, colName,
behavior, true, true,
- false, lockmode, addrs);
+ false, lockmode, addrs, context);
}
else
{
@@ -10928,6 +11067,7 @@ transformFkeyCheckAttrs(Relation pkrel,
ListCell *indexoidscan;
int i,
j;
+ int nattrs = numattrs;
/*
* Reject duplicate appearances of columns in the referenced-columns list.
@@ -10947,6 +11087,30 @@ transformFkeyCheckAttrs(Relation pkrel,
}
}
+ /*
+ * SQL Standard requires that an FK must refer to a current row of the pkrel,
+ * if it system versioned. Thus the FK cannot contain the period start or end
+ * time columns in the referenced-columns list.
+ */
+ if (pkrel->rd_att->constr &&
+ pkrel->rd_att->constr->has_system_versioning)
+ {
+ for (i = 0; i < numattrs; i++)
+ if (pkrel->rd_att->constr->sv_starttime == attnums[i] ||
+ pkrel->rd_att->constr->sv_endtime == attnums[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FOREIGN_KEY),
+ errmsg("foreign key referenced-columns list must not contain system_time period columns"
+ " of a system versioned table")));
+
+ /*
+ * System versioned tables must have a unique index on the PK plus period
+ * end time, so we are looking for an index that has one more attribute
+ * than the number of attrs in the referenced-columns list.
+ */
+ nattrs++;
+ }
+
/*
* Get the list of index OIDs for the table from the relcache, and look up
* each one in the pg_index syscache, and match unique indexes to the list
@@ -10970,7 +11134,7 @@ transformFkeyCheckAttrs(Relation pkrel,
* partial index; forget it if there are any expressions, too. Invalid
* indexes are out as well.
*/
- if (indexStruct->indnkeyatts == numattrs &&
+ if (indexStruct->indnkeyatts == nattrs &&
indexStruct->indisunique &&
indexStruct->indisvalid &&
heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL) &&
@@ -10995,11 +11159,17 @@ transformFkeyCheckAttrs(Relation pkrel,
* start of this function, and we checked above that the number of
* index columns agrees, so if we find a match for each attnums[]
* entry then we must have a one-to-one match in some order.
+ *
+ * If pkrel is a system versioned table then its PK will include
+ * period end time, but that will never match anything in the
+ * referenced-columns list because of our check above. So we don't
+ * try to match that. i.e. we have numattrs columns to match, yet
+ * we're looking for an index with numattrs+1 columns in it.
*/
for (i = 0; i < numattrs; i++)
{
found = false;
- for (j = 0; j < numattrs; j++)
+ for (j = 0; j < nattrs; j++)
{
if (attnums[i] == indexStruct->indkey.values[j])
{
@@ -11012,6 +11182,27 @@ transformFkeyCheckAttrs(Relation pkrel,
break;
}
+ /*
+ * Check that the last remaining unmatched column of the index
+ * is the period endtime of the system versioned pkrel.
+ */
+ if (found && nattrs != numattrs)
+ {
+ found = false;
+ for (j = 0; j < nattrs; j++)
+ {
+ if (pkrel->rd_att->constr->sv_endtime == indexStruct->indkey.values[j])
+ {
+ /*
+ * FK code will automatically add starttime = 'infinity'
+ * so we don't need to add any opclasses for that match here.
+ */
+ found = true;
+ break;
+ }
+ }
+ }
+
/*
* Refuse to use a deferrable unique/primary key. This is per SQL
* spec, and there would be a lot of interesting semantic problems
@@ -12040,6 +12231,12 @@ 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);
@@ -12821,10 +13018,13 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
{
List *beforeStmts;
List *afterStmts;
+ AlterTableUtilityContext context;
+
+ context.queryString = cmd;
stmt = (Node *) transformAlterTableStmt(oldRelId,
(AlterTableStmt *) stmt,
- cmd,
+ &context,
&beforeStmts,
&afterStmts);
querytree_list = list_concat(querytree_list, beforeStmts);
@@ -13177,6 +13377,13 @@ 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));
@@ -16801,7 +17008,8 @@ 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 4df05a0b33..a9777d9f5d 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"
@@ -425,6 +426,11 @@ DefineView(ViewStmt *stmt, const char *queryString,
viewParse = parse_analyze(rawstmt, queryString, NULL, 0, NULL);
+ /*
+ * Check and add filter clause to filter out historical data.
+ */
+ 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 c24684aa6f..e7cff4b565 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -572,6 +572,145 @@ ExecGetUpdateNewTuple(ResultRelInfo *relinfo,
}
+/*
+ * Set row start time column for a tuple.
+ */
+void
+ExecSetRowStartTime(EState *estate, TupleTableSlot *slot, ResultRelInfo *resultRelInfo)
+{
+ Relation rel = resultRelInfo->ri_RelationDesc;
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int natts = tupdesc->natts;
+ MemoryContext oldContext;
+ Datum *values;
+ bool *nulls;
+
+ Assert(tupdesc->constr && tupdesc->constr->has_system_versioning);
+
+ 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(timestamptz_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 column for a tuple.
+ */
+bool
+ExecSetRowEndTime(EState *estate, TupleTableSlot *slot, ResultRelInfo *resultRelInfo)
+{
+ Relation rel = resultRelInfo->ri_RelationDesc;
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int natts = tupdesc->natts;
+ MemoryContext oldContext;
+ Datum *values;
+ bool *nulls;
+ bool same_xact = false;
+
+ Assert(tupdesc->constr && tupdesc->constr->has_system_versioning);
+
+ 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_START_TIME)
+ {
+ Assert(!nulls[i]);
+ values[i] = datumCopy(slot->tts_values[i], attr->attbyval, attr->attlen);
+
+ /*
+ * Avoid anomalies, as directed by SQL Standard,
+ * 13.4 General Rule 15 a) iii) Case 1.
+ */
+ if (GetCurrentTransactionStartTimestamp() < values[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_EXCEPTION_INVALID_ROW_VERSION),
+ errmsg("data exception — invalid row version")));
+ else if (GetCurrentTransactionStartTimestamp() == values[i])
+ same_xact = true;
+ }
+ else 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);
+
+ return !same_xact;
+}
+
/* ----------------------------------------------------------------
* ExecInsert
*
@@ -738,6 +877,13 @@ ExecInsert(ModifyTableState *mtstate,
return NULL;
}
+ /*
+ * Set row start time
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->has_system_versioning)
+ ExecSetRowStartTime(estate, slot, resultRelInfo);
+
/*
* insert into foreign table: let the FDW do it
*/
@@ -774,6 +920,13 @@ ExecInsert(ModifyTableState *mtstate,
ExecComputeStoredGenerated(resultRelInfo, estate, slot,
CMD_INSERT);
+ /*
+ * Set row start time
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->has_system_versioning)
+ ExecSetRowStartTime(estate, slot, resultRelInfo);
+
/*
* Check any RLS WITH CHECK policies.
*
@@ -1151,6 +1304,30 @@ ExecDelete(ModifyTableState *mtstate,
}
else
{
+ /*
+ * Set row end time
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->has_system_versioning)
+ {
+ TupleTableSlot *mslot = NULL;
+
+ mslot = table_slot_create(resultRelationDesc, NULL);
+ if (!table_tuple_fetch_row_version(resultRelationDesc, tupleid, estate->es_snapshot,
+ mslot))
+ {
+ elog(ERROR, "failed to fetch tuple while attempting delete with system versioning");
+ }
+ else
+ {
+ if (ExecSetRowEndTime(estate, mslot, resultRelInfo))
+ table_tuple_insert(resultRelationDesc, mslot,
+ estate->es_output_cid,
+ 0, NULL);
+ }
+ ExecDropSingleTupleTableSlot(mslot);
+ }
+
/*
* delete the tuple
*
@@ -1698,6 +1875,40 @@ ExecUpdate(ModifyTableState *mtstate,
ExecComputeStoredGenerated(resultRelInfo, estate, slot,
CMD_UPDATE);
+ /*
+ * Set row end time and insert
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->has_system_versioning)
+ {
+ TupleTableSlot *mslot = NULL;
+
+ /*
+ * Insert a new row to represent the old row with EndTime set
+ * Note that this creates a new row version rather than updating in place
+ * the existing row version.
+ */
+ mslot = table_slot_create(resultRelationDesc, NULL);
+ if (!table_tuple_fetch_row_version(resultRelationDesc, tupleid, estate->es_snapshot,
+ mslot))
+ {
+ elog(ERROR, "failed to fetch tuple while attempting update with system versioning");
+ }
+ else
+ {
+ if (ExecSetRowEndTime(estate, mslot, resultRelInfo))
+ table_tuple_insert(resultRelationDesc, mslot,
+ estate->es_output_cid,
+ 0, NULL);
+ }
+ ExecDropSingleTupleTableSlot(mslot);
+
+ /*
+ * Set the StartTime for the soon to be newly updated row
+ */
+ ExecSetRowStartTime(estate, slot, resultRelInfo);
+ }
+
/*
* Check any RLS UPDATE WITH CHECK policies
*
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 38251c2b8e..8cfe4c6226 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3523,6 +3523,7 @@ CopyCreateStmtFields(const CreateStmt *from, CreateStmt *newnode)
COPY_STRING_FIELD(tablespacename);
COPY_STRING_FIELD(accessMethod);
COPY_SCALAR_FIELD(if_not_exists);
+ COPY_SCALAR_FIELD(systemVersioning);
}
static CreateStmt *
@@ -4939,6 +4940,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
@@ -5854,6 +5879,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 8a1762000c..d0182f5a49 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1293,6 +1293,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(systemVersioning);
return true;
}
@@ -3025,6 +3026,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
*/
@@ -3862,6 +3884,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 01c110cd2f..cb9e7eef01 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -815,3 +815,126 @@ makeVacuumRelation(RangeVar *relation, Oid oid, List *va_cols)
v->va_cols = va_cols;
return v;
}
+
+Node *
+makeAndExpr(Node *lexpr, Node *rexpr, int location)
+{
+ /* Flatten "a AND b AND c ..." to a single BoolExpr on sight */
+ if (IsA(lexpr, BoolExpr))
+ {
+ BoolExpr *blexpr = (BoolExpr *) lexpr;
+
+ if (blexpr->boolop == AND_EXPR)
+ {
+ blexpr->args = lappend(blexpr->args, rexpr);
+ return (Node *) blexpr;
+ }
+ }
+ return (Node *) makeBoolExpr(AND_EXPR, list_make2(lexpr, rexpr), location);
+}
+
+Node *
+makeTypeCast(Node *arg, TypeName *typename, int location)
+{
+ TypeCast *n = makeNode(TypeCast);
+
+ n->arg = arg;
+ n->typeName = typename;
+ n->location = location;
+ return (Node *) n;
+}
+
+/*
+ * makeColumnRefFromName -
+ * creates a ColumnRef node using column name
+ */
+ColumnRef *
+makeColumnRefFromName(char *colname)
+{
+ ColumnRef *c = makeNode(ColumnRef);
+
+ c->location = -1;
+ c->fields = lcons(makeString(colname), NIL);
+
+ return c;
+}
+
+/*
+ * makeTemporalColumnDef -
+ * create a ColumnDef node for system time column
+ */
+ColumnDef *
+makeTemporalColumnDef(char *name)
+{
+ ColumnDef *n = makeNode(ColumnDef);
+
+ if (strcmp(name, SYSTEM_VERSIONING_DEFAULT_START_NAME) == 0)
+ {
+ Constraint *c = makeNode(Constraint);
+
+ c->contype = CONSTR_ROW_START_TIME;
+ c->raw_expr = NULL;
+ c->cooked_expr = NULL;
+ c->location = -1;
+ n->colname = SYSTEM_VERSIONING_DEFAULT_START_NAME;
+ n->constraints = list_make1((Node *) c);
+ }
+ else if (strcmp(name, SYSTEM_VERSIONING_DEFAULT_END_NAME) == 0)
+ {
+ Constraint *c = makeNode(Constraint);
+
+ c->contype = CONSTR_ROW_END_TIME;
+ c->raw_expr = NULL;
+ c->cooked_expr = NULL;
+ c->location = -1;
+
+ n->colname = SYSTEM_VERSIONING_DEFAULT_END_NAME;
+ n->constraints = list_make1((Node *) c);
+ }
+ else
+ elog(ERROR, "unexpected temporal column name");
+ n->typeName = makeTypeNameFromNameList(list_make2(makeString("pg_catalog"),
+ makeString("timestamptz")));
+ 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 87561cbb6f..f0af5a0bef 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2716,6 +2716,7 @@ _outCreateStmtInfo(StringInfo str, const CreateStmt *node)
WRITE_STRING_FIELD(tablespacename);
WRITE_STRING_FIELD(accessMethod);
WRITE_BOOL_FIELD(if_not_exists);
+ WRITE_BOOL_FIELD(systemVersioning);
}
static void
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 2cd691191c..5d850b1271 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"
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index c9f7a09d10..11ca7bca0a 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -28,6 +28,7 @@
#include "optimizer/optimizer.h"
#include "optimizer/paramassign.h"
#include "optimizer/pathnode.h"
+#include "optimizer/plancat.h"
#include "optimizer/planmain.h"
#include "optimizer/planner.h"
#include "optimizer/prep.h"
@@ -912,6 +913,15 @@ SS_process_ctes(PlannerInfo *root)
*/
if (cte->cterefcount == 0 && cmdType == CMD_SELECT)
{
+ Query *query;
+
+ query = (Query *) cte->ctequery;
+
+ /*
+ * Check and add filter clause to remove historical row versions.
+ */
+ 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;
@@ -958,6 +968,15 @@ SS_process_ctes(PlannerInfo *root)
!contain_outer_selfref(cte->ctequery)) &&
!contain_volatile_functions(cte->ctequery))
{
+ Query *query;
+
+ query = (Query *) cte->ctequery;
+
+ /*
+ * Check and add filter clause to remove historical row versions.
+ */
+ 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 c5194fdbbf..577c075e5e 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -41,6 +41,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"
@@ -52,6 +53,7 @@
#include "utils/rel.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+#include "utils/memutils.h"
/* GUC parameter */
int constraint_exclusion = CONSTRAINT_EXCLUSION_PARTITION;
@@ -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_versioning_column(Node *node, RangeTblEntry *rte);
+static bool check_system_versioning_table(RangeTblEntry *rte);
/*
@@ -2408,3 +2412,193 @@ 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 = NULL;
+ 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 = NULL;
+ 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 this is a system versioned relation and the where clause did not
+ * already contain a 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_versioning_table(rte) ||
+ check_system_versioning_column(query->jointree->quals, rte))
+ {
+ continue;
+ }
+ else
+ {
+ Node *wClause;
+ Relation relation;
+ ColumnRef *c;
+ A_Const *n;
+ ParseState *pstate;
+ ParseNamespaceItem *newnsitem;
+
+ relation = table_open(rte->relid, NoLock);
+
+ /*
+ * Create a condition that filters 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 = -1;
+
+ 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 if (IsA((Node *) linitial(query->jointree->fromlist), JoinExpr))
+ {
+ JoinExpr *j = (JoinExpr *) query->jointree->fromlist;
+
+ j->quals = make_and_qual(j->quals, wClause);
+ }
+ else
+ {
+ query->jointree->quals = wClause;
+ }
+
+ table_close(relation, NoLock);
+ }
+
+ }
+}
+
+/*
+ * Check for references to system versioned columns
+ */
+static bool
+check_system_versioning_column_walker(Node *node, RangeTblEntry *rte)
+{
+
+ if (node == NULL)
+ return false;
+ 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;
+ }
+ return expression_tree_walker(node, check_system_versioning_column_walker,
+ rte);
+}
+
+static bool
+check_system_versioning_column(Node *node, RangeTblEntry *rte)
+{
+ return check_system_versioning_column_walker(node, rte);
+}
+
+static bool
+check_system_versioning_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->has_system_versioning;
+
+ table_close(rel, NoLock);
+
+ return result;
+}
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 438b077004..8896440505 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.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_agg.h"
#include "parser/parse_clause.h"
@@ -477,6 +478,11 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
qry->rtable = pstate->p_rtable;
qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
+ /*
+ * Check and add filter clause to filter out historical data.
+ */
+ add_history_data_filter(qry);
+
qry->hasSubLinks = pstate->p_hasSubLinks;
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
@@ -1269,6 +1275,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);
@@ -1367,6 +1382,11 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
if (pstate->p_hasAggs || qry->groupClause || qry->groupingSets || qry->havingQual)
parseCheckAggregates(pstate, qry);
+ /*
+ * Check and add filter clause to filter out historical data.
+ */
+ add_history_data_filter(qry);
+
return qry;
}
@@ -2357,6 +2377,11 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasSubLinks = pstate->p_hasSubLinks;
+ /*
+ * Check and add filter clause to filter out historical data.
+ */
+ add_history_data_filter(qry);
+
assign_query_collations(pstate, qry);
return qry;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 39a2849eba..2a52badc67 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -141,6 +141,20 @@ typedef struct GroupClause
List *list;
} GroupClause;
+/* 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 systemVersioning;
+} OptionWith;
+
/* ConstraintAttributeSpec yields an integer bitmask of these flags: */
#define CAS_NOT_DEFERRABLE 0x01
#define CAS_DEFERRABLE 0x02
@@ -159,7 +173,6 @@ static RawStmt *makeRawStmt(Node *stmt, int stmt_location);
static void updateRawStmtEnd(RawStmt *rs, int end_location);
static Node *makeColumnRef(char *colname, List *indirection,
int location, core_yyscan_t yyscanner);
-static Node *makeTypeCast(Node *arg, TypeName *typename, int location);
static Node *makeStringConst(char *str, int location);
static Node *makeStringConstCast(char *str, int location, TypeName *typename);
static Node *makeIntConst(int val, int location);
@@ -184,7 +197,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);
@@ -260,6 +272,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
struct SelectLimit *selectlimit;
SetQuantifier setquantifier;
struct GroupClause *groupclause;
+ TemporalClause *temporalClause;
+ struct GenerateType *GenerateType;
+ struct OptionWith *OptionWith;
}
%type <node> stmt toplevel_stmt schema_stmt routine_body_stmt
@@ -395,11 +410,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> vacuum_relation
%type <selectlimit> opt_select_limit select_limit limit_clause
+%type <GenerateType> generated_type
+%type <OptionWith> OptWith
+
%type <list> parse_toplevel stmtmulti routine_body_stmt_list
OptTableElementList TableElementList OptInherit definition
OptTypedTableElementList TypedTableElementList
reloptions opt_reloptions
- OptWith opt_definition func_args func_args_list
+ 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 opt_createfunc_opt_list alterfunc_opt_list
@@ -521,7 +539,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
@@ -559,7 +577,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <keyword> col_name_keyword reserved_keyword
%type <keyword> bare_label_keyword
-%type <node> TableConstraint TableLikeClause
+%type <node> TableConstraint TableLikeClause optSystemTimeColumn
%type <ival> TableLikeOptionList TableLikeOption
%type <str> column_compression opt_column_compression
%type <list> ColQualList
@@ -692,7 +710,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
@@ -707,7 +725,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
@@ -718,7 +736,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UNLISTEN UNLOGGED 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
@@ -739,7 +757,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
* as NOT, at least with respect to their left-hand subexpression.
* NULLS_LA and WITH_LA are needed to make the grammar LALR(1).
*/
-%token NOT_LA NULLS_LA WITH_LA
+%token NOT_LA NULLS_LA WITH_LA FOR_LA
/*
* The grammar likewise thinks these tokens are keywords, but they are never
@@ -766,6 +784,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%nonassoc '<' '>' '=' LESS_EQUALS GREATER_EQUALS NOT_EQUALS
%nonassoc BETWEEN IN_P LIKE ILIKE SIMILAR NOT_LA
%nonassoc ESCAPE /* ESCAPE must be just above LIKE/ILIKE/SIMILAR */
+%nonassoc SYSTEM_P
+%nonassoc VERSIONING
+%nonassoc DAY_P
/*
* To support target_el without AS, it used to be necessary to assign IDENT an
* explicit precedence just less than Op. While that's not really necessary
@@ -805,6 +826,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%left '(' ')'
%left TYPECAST
%left '.'
+%left YEAR_P
+%left MONTH_P
+%left HOUR_P
+%left MINUTE_P
+%left TO
/*
* These might seem to be low-precedence, but actually they are not part
* of the arithmetic hierarchy at all in their use as JOIN operators.
@@ -2181,6 +2207,14 @@ alter_table_cmd:
n->missing_ok = false;
$$ = (Node *)n;
}
+ | ADD_P optSystemTimeColumn
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_PeriodColumn;
+ n->def = $2;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> ADD IF NOT EXISTS <coldef> */
| ADD_P IF_P NOT EXISTS columnDef
{
@@ -2208,7 +2242,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);
@@ -2356,7 +2398,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;
@@ -2365,8 +2419,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;
@@ -2375,6 +2441,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> ]
@@ -3282,12 +3357,13 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$4->relpersistence = $2;
n->relation = $4;
n->tableElts = $6;
+ n->systemVersioning = ($11)->systemVersioning;
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;
@@ -3301,12 +3377,13 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$7->relpersistence = $2;
n->relation = $7;
n->tableElts = $9;
+ n->systemVersioning = ($14)->systemVersioning;
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;
@@ -3320,13 +3397,14 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$4->relpersistence = $2;
n->relation = $4;
n->tableElts = $7;
+ n->systemVersioning = ($10)->systemVersioning;
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;
@@ -3340,13 +3418,14 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$7->relpersistence = $2;
n->relation = $7;
n->tableElts = $10;
+ n->systemVersioning = ($13)->systemVersioning;
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;
@@ -3360,13 +3439,14 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$4->relpersistence = $2;
n->relation = $4;
n->tableElts = $8;
+ n->systemVersioning = ($12)->systemVersioning;
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;
@@ -3380,13 +3460,14 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$7->relpersistence = $2;
n->relation = $7;
n->tableElts = $11;
+ n->systemVersioning = ($15)->systemVersioning;
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;
@@ -3463,6 +3544,7 @@ TableElement:
columnDef { $$ = $1; }
| TableLikeClause { $$ = $1; }
| TableConstraint { $$ = $1; }
+ | optSystemTimeColumn { $$ = $1; }
;
TypedTableElement:
@@ -3570,6 +3652,16 @@ ColConstraint:
}
;
+optSystemTimeColumn:
+ PERIOD FOR_LA 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.
@@ -3652,12 +3744,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;
@@ -3675,6 +3767,7 @@ ColConstraintElem:
$$ = (Node *)n;
}
+
| REFERENCES qualified_name opt_column_list key_match key_actions
{
Constraint *n = makeNode(Constraint);
@@ -3697,6 +3790,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
@@ -4074,9 +4191,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->systemVersioning = false;
+ $$ = n;
+ }
+ | WITHOUT OIDS
+ {
+ OptionWith *n = (OptionWith *) palloc(sizeof(OptionWith));
+ n->options = NIL;
+ n->systemVersioning = false;
+ $$ = n;
+ }
+ | WITH SYSTEM_P VERSIONING
+ {
+ OptionWith *n = (OptionWith *) palloc(sizeof(OptionWith));
+ n->options = NIL;
+ n->systemVersioning = true;
+ $$ = n;
+ }
+ | /*EMPTY*/
+ {
+ OptionWith *n = (OptionWith *) palloc(sizeof(OptionWith));
+ n->options = NIL;
+ n->systemVersioning = false;
+ $$ = n;
+ }
;
OnCommitOption: ON COMMIT DROP { $$ = ONCOMMIT_DROP; }
@@ -4242,7 +4384,7 @@ create_as_target:
$$->rel = $1;
$$->colNames = $2;
$$->accessMethod = $3;
- $$->options = $4;
+ $$->options = ($4)->options;
$$->onCommit = $5;
$$->tableSpaceName = $6;
$$->viewQuery = NULL;
@@ -12003,7 +12145,7 @@ having_clause:
for_locking_clause:
for_locking_items { $$ = $1; }
- | FOR READ ONLY { $$ = NIL; }
+ | FOR READ ONLY { $$ = NIL; }
;
opt_for_locking_clause:
@@ -12082,12 +12224,16 @@ from_list:
/*
* table_ref is where an alias clause can be attached.
*/
-table_ref: relation_expr opt_alias_clause
+table_ref: relation_expr alias_clause
{
$1->alias = $2;
$$ = (Node *) $1;
}
- | relation_expr opt_alias_clause tablesample_clause
+ | relation_expr %prec UMINUS
+ {
+ $$ = (Node *) $1;
+ }
+ | relation_expr alias_clause tablesample_clause
{
RangeTableSample *n = (RangeTableSample *) $3;
$1->alias = $2;
@@ -12095,6 +12241,19 @@ table_ref: relation_expr opt_alias_clause
n->relation = (Node *) $1;
$$ = (Node *) n;
}
+
+ | relation_expr tablesample_clause
+ {
+ RangeTableSample *n = (RangeTableSample *) $2;
+ /* relation_expr goes inside the RangeTableSample node */
+ n->relation = (Node *) $1;
+ $$ = (Node *) n;
+ }
+ | relation_expr temporal_clause
+ {
+ $2->relation = (Node *)$1;
+ $$ = (Node *)$2;
+ }
| func_table func_alias_clause
{
RangeFunction *n = (RangeFunction *) $1;
@@ -12193,7 +12352,54 @@ table_ref: relation_expr opt_alias_clause
$$ = (Node *) $2;
}
;
+temporal_clause: FOR_LA SYSTEM_TIME AS OF a_expr
+ {
+ $$ = makeNode(TemporalClause);
+ $$->kind = AS_OF;
+ $$->from = NULL;
+ $$->to = (Node *)makeTypeCast($5, SystemTypeName("timestamptz"), @5);
+ }
+ | FOR_LA SYSTEM_TIME BETWEEN a_expr AND a_expr
+ {
+ $$ = makeNode(TemporalClause);
+ $$->kind = BETWEEN_T;
+ $$->from = (Node *)makeTypeCast($4, SystemTypeName("timestamptz"), @4);
+ $$->to = (Node *)makeTypeCast($6, SystemTypeName("timestamptz"), @6);
+ }
+ | FOR_LA SYSTEM_TIME BETWEEN SYMMETRIC a_expr AND a_expr
+ {
+ MinMaxExpr *g = makeNode(MinMaxExpr);
+ MinMaxExpr *l = makeNode(MinMaxExpr);
+ $$ = makeNode(TemporalClause);
+ $$->kind = BETWEEN_SYMMETRIC;
+ l->args = list_make2((Node *)makeTypeCast($5, SystemTypeName("timestamptz"),
+ @2),(Node *)makeTypeCast($7, SystemTypeName("timestamptz"), @7));
+ l->op = IS_LEAST;
+ l->location = @1;
+ $$->from = (Node *)l;
+
+ g->args = list_make2((Node *)makeTypeCast($5, SystemTypeName("timestamptz"),
+ @2),(Node *)makeTypeCast($7, SystemTypeName("timestamptz"), @7));
+ g->op = IS_GREATEST;
+ g->location = @1;
+ $$->to = (Node *)g;
+ }
+ | FOR_LA SYSTEM_TIME BETWEEN ASYMMETRIC a_expr AND a_expr
+ {
+ $$ = makeNode(TemporalClause);
+ $$->kind = BETWEEN_ASYMMETRIC;
+ $$->from = (Node *)makeTypeCast($5, SystemTypeName("timestamptz"), @5);
+ $$->to = (Node *)makeTypeCast($7, SystemTypeName("timestamptz"), @7);
+ }
+ | FOR_LA SYSTEM_TIME FROM a_expr TO a_expr
+ {
+ $$ = makeNode(TemporalClause);
+ $$->kind = FROM_TO;
+ $$->from = (Node *)makeTypeCast($4, SystemTypeName("timestamptz"), @4);
+ $$->to = (Node *)makeTypeCast($6, SystemTypeName("timestamptz"), @6);
+ }
+ ;
/*
* It may seem silly to separate joined_table from table_ref, but there is
@@ -15661,6 +15867,7 @@ unreserved_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PERIOD
| PLANS
| POLICY
| PRECEDING
@@ -15738,6 +15945,7 @@ unreserved_keyword:
| SUPPORT
| SYSID
| SYSTEM_P
+ | SYSTEM_TIME
| TABLES
| TABLESPACE
| TEMP
@@ -15768,6 +15976,7 @@ unreserved_keyword:
| VALUE_P
| VARYING
| VERSION_P
+ | VERSIONING
| VIEW
| VIEWS
| VOLATILE
@@ -16239,6 +16448,7 @@ bare_label_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PERIOD
| PLACING
| PLANS
| POLICY
@@ -16330,6 +16540,7 @@ bare_label_keyword:
| SYMMETRIC
| SYSID
| SYSTEM_P
+ | SYSTEM_TIME
| TABLE
| TABLES
| TABLESAMPLE
@@ -16375,6 +16586,7 @@ bare_label_keyword:
| VARIADIC
| VERBOSE
| VERSION_P
+ | VERSIONING
| VIEW
| VIEWS
| VOLATILE
@@ -16491,16 +16703,6 @@ makeColumnRef(char *colname, List *indirection,
return (Node *) c;
}
-static Node *
-makeTypeCast(Node *arg, TypeName *typename, int location)
-{
- TypeCast *n = makeNode(TypeCast);
- n->arg = arg;
- n->typeName = typename;
- n->location = location;
- return (Node *) n;
-}
-
static Node *
makeStringConst(char *str, int location)
{
@@ -16905,23 +17107,6 @@ doNegateFloat(Value *v)
v->val.str = psprintf("-%s", oldval);
}
-static Node *
-makeAndExpr(Node *lexpr, Node *rexpr, int location)
-{
- /* Flatten "a AND b AND c ..." to a single BoolExpr on sight */
- if (IsA(lexpr, BoolExpr))
- {
- BoolExpr *blexpr = (BoolExpr *) lexpr;
-
- 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 b3f151d33b..8fd4a19702 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 changeTemporalToWhereClause(ParseState *pstate, TemporalClause * tc, RangeTblEntry *rte);
/*
@@ -1138,6 +1140,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->has_system_versioning = (tupdesc->constr && tupdesc->constr->has_system_versioning);
+ table_close(rel, NoLock);
+
+ if (!rte->has_system_versioning)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("temporal clause can only specified for a table with system versioning")));
+
+ changeTemporalToWhereClause(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 */
@@ -3704,3 +3735,73 @@ transformFrameOffset(ParseState *pstate, int frameOptions,
return node;
}
+
+/*
+ * changeTemporalToWhereClause
+ * make where clause from temporal clause specification.
+ */
+static void
+changeTemporalToWhereClause(ParseState *pstate, TemporalClause * tc, RangeTblEntry *rte)
+{
+ Node *fClause = NULL;
+ Node *tClause = NULL;
+ Node *cClause = NULL;
+ 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 == BETWEEN_T)
+ {
+ cClause = (Node *) makeSimpleA_Expr(AEXPR_OP, "<=", tc->from, tc->to, 0);
+ 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);
+ fClause = makeAndExpr(fClause, cClause, 0);
+ }
+ else if (tc->kind == BETWEEN_ASYMMETRIC)
+ {
+ cClause = (Node *) makeSimpleA_Expr(AEXPR_OP, "<=", tc->from, tc->to, 0);
+ 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);
+ fClause = makeAndExpr(fClause, tClause, 0);
+
+ }
+ else if (tc->kind == BETWEEN_SYMMETRIC)
+ {
+ tc->to = makeTypeCast((Node *) tc->to, typeStringToTypeName("timestamptz"), -1);
+ tc->from = makeTypeCast((Node *) tc->from, typeStringToTypeName("timestamptz"), -1);
+ 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 == FROM_TO)
+ {
+ cClause = (Node *) makeSimpleA_Expr(AEXPR_OP, "<", tc->from, tc->to, 0);
+ 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);
+ fClause = makeAndExpr(fClause, cClause, 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 675e400839..7c2fdeb08c 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -58,6 +58,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"
@@ -69,6 +70,8 @@
#include "utils/typcache.h"
+#include <string.h>
+
/* State shared by transformCreateStmt and its subroutines */
typedef struct
{
@@ -93,6 +96,11 @@ typedef struct
bool ispartitioned; /* true if table is partitioned */
PartitionBoundSpec *partbound; /* transformed FOR VALUES */
bool ofType; /* true if statement contains OF typename */
+ bool hasSystemVersioning; /* 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 time column */
+ char *periodEnd; /* name of period end time column */
} CreateStmtContext;
/* State shared by transformCreateSchemaStmt and its subroutines */
@@ -116,6 +124,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,
@@ -242,6 +252,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.hasSystemVersioning = false;
+
Assert(!stmt->ofTypename || !stmt->inhRelations); /* grammar enforces */
@@ -278,6 +292,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
transformTableLikeClause(&cxt, (TableLikeClause *) element);
break;
+ case T_RowTime:
+ transformPeriodColumn(&cxt, (RowTime *) element);
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(element));
@@ -285,6 +303,27 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
}
}
+ /*
+ * If there are no system time columns and the user specified "WITH SYSTEM
+ * VERSIONING", default system time columns is prepended to the table
+ * definition. This is an extension to the SQL Standard.
+ */
+ if (!cxt.hasSystemVersioning && stmt->systemVersioning)
+ {
+ ColumnDef *startCol;
+ ColumnDef *endCol;
+
+ startCol = makeTemporalColumnDef(SYSTEM_VERSIONING_DEFAULT_START_NAME);
+ endCol = makeTemporalColumnDef(SYSTEM_VERSIONING_DEFAULT_END_NAME);
+ if (stmt->tableElts == NIL)
+ stmt->tableElts = list_make2(startCol, endCol);
+ else
+ stmt->tableElts = lappend(list_make2(startCol, endCol), stmt->tableElts);
+
+ 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
@@ -296,6 +335,36 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
Assert(stmt->constraints == NIL);
+ if (cxt.hasSystemVersioning)
+ {
+ ListCell *lc;
+
+ if (!cxt.startTimeColName)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("period start time column not specified")));
+
+ if (!cxt.endTimeColName)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("period end time column not specified")));
+
+ /*
+ * End time column is added to primary and unique key constraint
+ * implicitly to make history and current data co-exist.
+ */
+ 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.
*/
@@ -747,6 +816,62 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
saw_generated = true;
break;
+ case CONSTR_ROW_START_TIME:
+ {
+ Type ctype;
+ Form_pg_type typform;
+ char *typname;
+
+ ctype = typenameType(cxt->pstate, column->typeName, NULL);
+ typform = (Form_pg_type) GETSTRUCT(ctype);
+ typname = NameStr(typform->typname);
+ ReleaseSysCache(ctype);
+
+ if (strcmp(typname, "timestamptz") != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("data type of row start time must be timestamptz")));
+
+ if (cxt->startTimeColName)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row start time specified more than once")));
+
+ column->generated = ATTRIBUTE_ROW_START_TIME;
+ cxt->startTimeColName = column->colname;
+ cxt->hasSystemVersioning = true;
+ column->is_not_null = true;
+ break;
+ }
+
+ case CONSTR_ROW_END_TIME:
+ {
+ Type ctype;
+ Form_pg_type typform;
+ char *typname;
+
+ ctype = typenameType(cxt->pstate, column->typeName, NULL);
+ typform = (Form_pg_type) GETSTRUCT(ctype);
+ typname = NameStr(typform->typname);
+ ReleaseSysCache(ctype);
+
+ if (strcmp(typname, "timestamptz") != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("data type of row end time must be timestamptz")));
+
+ if (cxt->endTimeColName)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row end time specified more than once")));
+
+ column->generated = ATTRIBUTE_ROW_END_TIME;
+ cxt->endTimeColName = column->colname;
+ cxt->hasSystemVersioning = true;
+ column->is_not_null = true;
+ break;
+ }
+
case CONSTR_CHECK:
cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
break;
@@ -1444,6 +1569,35 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
return result;
}
+/*
+ * 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 (!cxt->startTimeColName)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("period start time column not specified")));
+ if (!cxt->endTimeColName)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("period end time column not specified")));
+
+ if (strcmp(cxt->periodStart, cxt->startTimeColName) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("period start time must reference the row start time column")));
+ if (strcmp(cxt->periodEnd, cxt->endTimeColName) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("period end time must reference the row end time column")));
+}
+
static void
transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
{
@@ -3283,7 +3437,7 @@ transformRuleStmt(RuleStmt *stmt, const char *queryString,
*/
AlterTableStmt *
transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
- const char *queryString,
+ AlterTableUtilityContext *context,
List **beforeStmts, List **afterStmts)
{
Relation rel;
@@ -3304,7 +3458,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
/* Set up pstate */
pstate = make_parsestate(NULL);
- pstate->p_sourcetext = queryString;
+ pstate->p_sourcetext = context->queryString;
nsitem = addRangeTableEntryForRelation(pstate,
rel,
AccessShareLock,
@@ -3341,6 +3495,9 @@ 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.hasSystemVersioning = false;
/*
* Transform ALTER subcommands that need it (most don't). These largely
@@ -3375,6 +3532,14 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
newcmds = lappend(newcmds, cmd);
break;
}
+ case AT_PeriodColumn:
+ {
+ RowTime *rtime = castNode(RowTime, cmd->def);
+
+ context->periodStart = rtime->start_time;
+ context->periodEnd = rtime->end_time;
+ }
+ break;
case AT_AddConstraint:
case AT_AddConstraintRecurse:
@@ -3384,6 +3549,23 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
*/
if (IsA(cmd->def, Constraint))
{
+ Constraint *constraint = castNode(Constraint, cmd->def);
+
+ /*
+ * End time column is added to primary and unique key
+ * constraint implicitly to make history data and current
+ * data co-exist.
+ */
+ if ((rel->rd_att->constr &&
+ rel->rd_att->constr->has_system_versioning) &&
+ (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;
@@ -3551,6 +3733,21 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
}
}
+ if (cxt.hasSystemVersioning)
+ {
+ if (cxt.startTimeColName)
+ {
+ context->hasSystemVersioning = cxt.hasSystemVersioning;
+ context->startTimeColName = cxt.startTimeColName;
+ }
+
+ if (cxt.endTimeColName)
+ {
+ context->hasSystemVersioning = cxt.hasSystemVersioning;
+ context->endTimeColName = cxt.endTimeColName;
+ }
+ }
+
/*
* Transfer anything we already have in cxt.alist into save_alist, to keep
* it separate from the output of transformIndexConstraints.
@@ -3582,7 +3779,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
{
IndexStmt *idxstmt = (IndexStmt *) istmt;
- idxstmt = transformIndexStmt(relid, idxstmt, queryString);
+ idxstmt = transformIndexStmt(relid, idxstmt, context->queryString);
newcmd = makeNode(AlterTableCmd);
newcmd->subtype = OidIsValid(idxstmt->indexOid) ? AT_AddIndexConstraint : AT_AddIndex;
newcmd->def = (Node *) idxstmt;
diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c
index 875de7ba28..e000a4040a 100644
--- a/src/backend/parser/parser.c
+++ b/src/backend/parser/parser.c
@@ -137,6 +137,9 @@ base_yylex(YYSTYPE *lvalp, YYLTYPE *llocp, core_yyscan_t yyscanner)
*/
switch (cur_token)
{
+ case FOR:
+ cur_token_length = 3;
+ break;
case NOT:
cur_token_length = 3;
break;
@@ -188,6 +191,10 @@ base_yylex(YYSTYPE *lvalp, YYLTYPE *llocp, core_yyscan_t yyscanner)
/* Replace cur_token if needed, based on lookahead */
switch (cur_token)
{
+ case FOR:
+ if (next_token == SYSTEM_TIME)
+ cur_token = FOR_LA;
+ break;
case NOT:
/* Replace NOT by NOT_LA if it's followed by BETWEEN, IN, etc */
switch (next_token)
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 54fd6d6fb2..5b123cd1f4 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -910,14 +910,24 @@ rewriteTargetListIU(List *targetList,
NameStr(att_tup->attname)),
errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.",
NameStr(att_tup->attname))));
-
- if (att_tup->attgenerated && new_tle && !apply_default)
+#ifdef NOTUSED
+ if (att_tup->attgenerated == ATTRIBUTE_ROW_START_TIME ||
+ att_tup->attgenerated == ATTRIBUTE_ROW_END_TIME)
ereport(ERROR,
- (errcode(ERRCODE_GENERATED_ALWAYS),
- errmsg("column \"%s\" can only be updated to DEFAULT",
- NameStr(att_tup->attname)),
- errdetail("Column \"%s\" is a generated column.",
- NameStr(att_tup->attname))));
+ (errcode(ERRCODE_GENERATED_ALWAYS),
+ errmsg("column \"%s\" cannot be updated",
+ NameStr(att_tup->attname)),
+ errdetail("Column \"%s\" is a system versioning column.",
+ NameStr(att_tup->attname))));
+ else if (att_tup->attgenerated && new_tle && !apply_default)
+#endif
+ if (att_tup->attgenerated && new_tle && !apply_default)
+ ereport(ERROR,
+ (errcode(ERRCODE_GENERATED_ALWAYS),
+ errmsg("column \"%s\" can only be updated to DEFAULT",
+ NameStr(att_tup->attname)),
+ errdetail("Column \"%s\" is a generated column.",
+ NameStr(att_tup->attname))));
}
if (att_tup->attgenerated)
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 27fbf1f3aa..0028d8dac9 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"
@@ -59,7 +60,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"
@@ -1253,6 +1256,10 @@ ProcessUtilitySlow(ParseState *pstate,
Oid relid;
LOCKMODE lockmode;
ListCell *cell;
+ ListCell *s;
+ Relation rel;
+ bool hasSystemVersioning = false;
+ TupleDesc tupdesc;
/*
* Disallow ALTER TABLE .. DETACH CONCURRENTLY in a
@@ -1281,6 +1288,83 @@ 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);
+ tupdesc = RelationGetDescr(rel);
+ hasSystemVersioning = tupdesc->constr && tupdesc->constr->has_system_versioning;
+ if (hasSystemVersioning)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("table is already defined with system versioning")));
+
+ /*
+ * TODO create composite primary key
+ */
+ if (RelationGetPrimaryKeyIndex(rel) != InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("can not add system versioning to table with primary key")));
+
+ /*
+ * we use defualt column names for system
+ * versioning in ALTER TABLE ADD system versioning statement
+ */
+ startTimeCol = makeTemporalColumnDef(SYSTEM_VERSIONING_DEFAULT_START_NAME);
+ endTimeCol = makeTemporalColumnDef(SYSTEM_VERSIONING_DEFAULT_END_NAME);
+
+ /*
+ * create alter table add column cmd and append to the end
+ * of alter table commands.
+ */
+ atstmt->cmds = lappend(atstmt->cmds, (Node *) makeAddColCmd(startTimeCol));
+ atstmt->cmds = lappend(atstmt->cmds, (Node *) makeAddColCmd(endTimeCol));
+
+ /*
+ * delete current listCell
+ */
+ atstmt->cmds = list_delete_cell(atstmt->cmds, s);
+ relation_close(rel, NoLock);
+
+ }
+
+ if (cmd->subtype == AT_DropSystemVersioning)
+ {
+ rel = relation_open(relid, NoLock);
+ tupdesc = RelationGetDescr(rel);
+ hasSystemVersioning = tupdesc->constr && tupdesc->constr->has_system_versioning;
+ if (!hasSystemVersioning)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("table is not defined with system versioning")));
+
+ /*
+ * create alter table drop column cmd and append to the ende
+ * of alter table commands.
+ */
+ 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))
{
AlterTableUtilityContext atcontext;
@@ -1291,6 +1375,11 @@ ProcessUtilitySlow(ParseState *pstate,
atcontext.relid = relid;
atcontext.params = params;
atcontext.queryEnv = queryEnv;
+ atcontext.startTimeColName = NULL;
+ atcontext.endTimeColName = NULL;
+ atcontext.periodEnd = NULL;
+ atcontext.periodStart = NULL;
+ atcontext.hasSystemVersioning = false;
/* ... ensure we have an event trigger context ... */
EventTriggerAlterTableStart(parsetree);
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 13d9994af3..37e08b904b 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->has_system_versioning = false;
/*
* Form a scan key that selects only user attributes (attnum > 0).
@@ -568,6 +569,16 @@ 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->sv_starttime = attnum;
+ constr->has_system_versioning = true;
+ }
+ if (attp->attgenerated == ATTRIBUTE_ROW_END_TIME)
+ {
+ constr->sv_endtime = attnum;
+ constr->has_system_versioning = true;
+ }
if (attp->atthasdef)
ndef++;
@@ -664,6 +675,7 @@ RelationBuildTupleDesc(Relation relation)
*/
if (constr->has_not_null ||
constr->has_generated_stored ||
+ constr->has_system_versioning ||
ndef > 0 ||
attrmiss ||
relation->rd_rel->relchecks > 0)
diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt
index 9874a77805..7acf4f4205 100644
--- a/src/backend/utils/errcodes.txt
+++ b/src/backend/utils/errcodes.txt
@@ -153,6 +153,7 @@ Section: Class 21 - Cardinality Violation
Section: Class 22 - Data Exception
22000 E ERRCODE_DATA_EXCEPTION data_exception
+2201H E ERRCODE_DATA_EXCEPTION_INVALID_ROW_VERSION data_exception_invalid_row_version
2202E E ERRCODE_ARRAY_ELEMENT_ERROR
# SQL99's actual definition of "array element error" is subscript error
2202E E ERRCODE_ARRAY_SUBSCRIPT_ERROR array_subscript_error
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 90ac445bcd..5c0ac4d36f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16083,6 +16083,10 @@ dumpTableSchema(Archive *fout, const 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 8333558bda..6d9d91de9e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2237,6 +2237,10 @@ describeOneTableDetails(const char *schemaname,
default_str = "generated always as identity";
else if (identity[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT)
default_str = "generated by default as identity";
+ else if (generated[0] == ATTRIBUTE_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 if (generated[0] == ATTRIBUTE_GENERATED_STORED)
{
default_str = psprintf("generated always as (%s) stored",
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index f45d47aab7..607d080597 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -43,6 +43,9 @@ typedef struct TupleConstr
uint16 num_check;
bool has_not_null;
bool has_generated_stored;
+ bool has_system_versioning;
+ AttrNumber sv_starttime; /* which column is starttime */
+ AttrNumber sv_endtime; /* which column is endtime */
} TupleConstr;
/*
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index 5c1ec9313e..048bcbd508 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -216,6 +216,9 @@ DECLARE_UNIQUE_INDEX_PKEY(pg_attribute_relid_attnum_index, 2659, AttributeRelidN
#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 83e2965531..6df221e57d 100644
--- a/src/include/executor/nodeModifyTable.h
+++ b/src/include/executor/nodeModifyTable.h
@@ -22,5 +22,7 @@ extern void ExecComputeStoredGenerated(ResultRelInfo *resultRelInfo,
extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags);
extern void ExecEndModifyTable(ModifyTableState *node);
extern void ExecReScanModifyTable(ModifyTableState *node);
+extern void ExecSetRowStartTime(EState *estate, TupleTableSlot *slot, ResultRelInfo *resultRelInfo);
+extern bool ExecSetRowEndTime(EState *estate, TupleTableSlot *slot, ResultRelInfo *resultRelInfo);
#endif /* NODEMODIFYTABLE_H */
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 48a7ebfe45..f9f66ad80d 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -105,5 +105,16 @@ 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 Node *makeTypeCast(Node *arg, TypeName *typename, int location);
+extern ColumnRef *makeColumnRefFromName(char *colname);
+extern ColumnDef *makeTemporalColumnDef(char *name);
+
+#define SYSTEM_VERSIONING_DEFAULT_START_NAME "starttime"
+#define SYSTEM_VERSIONING_DEFAULT_END_NAME "endtime"
+
+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 6a4d82f0a8..9dc95b3fa0 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -490,6 +490,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 e28248af32..9727681400 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1023,6 +1023,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 has_system_versioning; /* relation has system versioning */
/*
* Fields valid for a subquery RTE (else NULL):
@@ -1850,7 +1851,7 @@ typedef enum DropBehavior
} DropBehavior;
/* ----------------------
- * Alter Table
+ * Alter Table
* ----------------------
*/
typedef struct AlterTableStmt
@@ -1934,7 +1935,10 @@ typedef enum AlterTableType
AT_AddIdentity, /* ADD IDENTITY */
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
- AT_ReAddStatistics /* internal to commands/tablecmds.c */
+ AT_ReAddStatistics, /* internal to commands/tablecmds.c */
+ AT_AddSystemVersioning, /* ADD system versioning */
+ AT_DropSystemVersioning, /* DROP system versioning */
+ AT_PeriodColumn /* Period column */
} AlterTableType;
typedef struct ReplicaIdentityStmt
@@ -2181,6 +2185,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 systemVersioning; /* true with system versioning */
} CreateStmt;
/* ----------
@@ -2230,7 +2235,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 */
@@ -3686,4 +3693,31 @@ 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_T,
+ BETWEEN_SYMMETRIC,
+ BETWEEN_ASYMMETRIC,
+ 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 8d1d6c1b42..dd2d6bce2e 100644
--- a/src/include/optimizer/plancat.h
+++ b/src/include/optimizer/plancat.h
@@ -73,5 +73,8 @@ 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 f836acf876..10d199ed45 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -312,6 +312,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("period", PERIOD, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -406,6 +407,7 @@ PG_KEYWORD("support", SUPPORT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("symmetric", SYMMETRIC, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("sysid", SYSID, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("system", SYSTEM_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("system_time", SYSTEM_TIME, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("table", TABLE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("tablesample", TABLESAMPLE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
@@ -454,6 +456,7 @@ PG_KEYWORD("variadic", VARIADIC, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("varying", VARYING, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("versioning", VERSIONING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 1500de2dd0..182a22c604 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -201,7 +201,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/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h
index 1056bf081b..bb80a2a018 100644
--- a/src/include/parser/parse_utilcmd.h
+++ b/src/include/parser/parse_utilcmd.h
@@ -15,13 +15,14 @@
#define PARSE_UTILCMD_H
#include "parser/parse_node.h"
+#include "tcop/utility.h"
struct AttrMap; /* avoid including attmap.h here */
extern List *transformCreateStmt(CreateStmt *stmt, const char *queryString);
extern AlterTableStmt *transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
- const char *queryString,
+ AlterTableUtilityContext *context,
List **beforeStmts,
List **afterStmts);
extern IndexStmt *transformIndexStmt(Oid relid, IndexStmt *stmt,
diff --git a/src/include/tcop/utility.h b/src/include/tcop/utility.h
index 212e9b3280..6caec53805 100644
--- a/src/include/tcop/utility.h
+++ b/src/include/tcop/utility.h
@@ -34,6 +34,11 @@ typedef struct AlterTableUtilityContext
Oid relid; /* OID of ALTER's target table */
ParamListInfo params; /* any parameters available to ALTER TABLE */
QueryEnvironment *queryEnv; /* execution environment for ALTER TABLE */
+ bool hasSystemVersioning; /* true if table has system versioning */
+ char *startTimeColName; /* name of row start time column */
+ char *endTimeColName; /* name of row end time column */
+ char *periodStart; /* name of period start time column */
+ char *periodEnd; /* name of period end time column */
} AlterTableUtilityContext;
/*
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index f4c01006fc..6af99a4467 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -36,6 +36,7 @@ test: fk-partitioned-2
test: eval-plan-qual
test: eval-plan-qual-trigger
test: lock-update-delete
+test: lock-update-delete-system-versioned
test: lock-update-traversal
test: inherit-temp
test: insert-conflict-do-nothing
@@ -96,3 +97,4 @@ test: plpgsql-toast
test: truncate-conflict
test: serializable-parallel
test: serializable-parallel-2
+test: system-versioned
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 7be89178f0..709cc4ba94 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -86,7 +86,7 @@ test: brin_bloom brin_multi
# ----------
# Another group of parallel tests
# ----------
-test: create_table_like alter_generic alter_operator misc async dbsize misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort
+test: create_table_like alter_generic alter_operator misc async dbsize misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort system_versioning
# rules cannot run concurrently with any test that creates
# a view or rule in the public schema
view thread (97+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: WIP: System Versioned Temporal Table
In-Reply-To: <CANbhV-GeD9PWHMkrZLV2vHASjFk3n2QFhGur6+o1gnAf5wymAg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox