SQL:2011 introduced the concept of a "period". It takes two existing columns
and basically does the same thing as our range types except there is no new
storage. I believe if Jeff Davis had given us range types a few years later
than he did, it would have been using periods.
Attached is a WIP patch that I have been working on. The only thing left is
completing periods on inherited tables, and finishing up pg_dump. I'm posting
this now just to make sure my basic foundation is sound, and to let people know
that I'm working on this.
The patch itself doesn't have any functionality, it just allows periods to be
defined. With that, there are several things that we can do: SYSTEM_TIME
periods, which are explicitly not allowed by this patch, will allow us to do
SQL standard versioned tables, and also allows some time travel functionality.
Application periods can be used in PRIMARY/UNIQUE keys, foreign keys, and give
nice new features to UPDATE and DELETE. They also allow "period predicates"
which are the same kind of operations we already have for range types. All of
that is for future patches that build on the infrastructure presented in this
patch.
The SQL standard restricts period columns to dates or timestamps, but I'm
allowing anything that has a btree operator class, as is the PostgreSQL way.
System periods, once allowed, will only be timestamptz though. Unfortunately,
I had to fully reserve the word PERIOD for this.
I'm looking for comments on everything except the pg_dump stuff, keeping in
mind that inheritance is not finished either.
Thanks!
This is patch is based off of 71b349aef4.
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 3ed9021c2f..025d6b5355 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -225,6 +225,11 @@
<entry>information about partition key of tables</entry>
</row>
+ <row>
+ <entry><link linkend="catalog-pg-period"><structname>pg_period</structname></link></entry>
+ <entry>periods</entry>
+ </row>
+
<row>
<entry><link linkend="catalog-pg-pltemplate"><structname>pg_pltemplate</structname></link></entry>
<entry>template data for procedural languages</entry>
@@ -4902,6 +4907,116 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</sect1>
+ <sect1 id="catalog-pg-period">
+ <title><structname>pg_period</structname></title>
+
+ <indexterm zone="catalog-pg-period">
+ <primary>pg_period</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_period</structname> stores
+ information about system and application time periods.
+ </para>
+
+ <para>
+ Periods are described in <xref linkend="ddl-periods"/>.
+ </para>
+
+ <table>
+ <title><structname>pg_period</structname> Columns</title>
+
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+
+ <row>
+ <entry><structfield>pername</structfield></entry>
+ <entry><type>name</type></entry>
+ <entry></entry>
+ <entry>Period name</entry>
+ </row>
+
+ <row>
+ <entry><structfield>perrelid</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
+ <entry>The OID of the <structname>pg_class</structname> entry for the table containing this period.</entry>
+ </row>
+
+ <row>
+ <entry><structfield>perstart</structfield></entry>
+ <entry><type>int2</type></entry>
+ <entry><literal><link linkend="catalog-pg-attribute">pg_attribute</link>.attnum</literal></entry>
+ <entry>
+ The attribute number of the start column.
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>perend</structfield></entry>
+ <entry><type>int2</type></entry>
+ <entry><literal><link linkend="catalog-pg-attribute">pg_attribute</link>.attnum</literal></entry>
+ <entry>
+ The attribute number of the end column.
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>peropclass</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>
+ <entry>
+ This contains the OID of the operator class to use.
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>perconstraint</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-constraint"><structname>pg_constraint</structname></link>.oid</literal></entry>
+ <entry>
+ This contains the OID of the CHECK constraint owned by the period to
+ ensure that <literal>(</literal><replaceable>startcolumn</replaceable>
+ <literal><</literal>
+ <replaceable>endcolumn</replaceable><literal>)</literal>.
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>perislocal</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry></entry>
+ <entry>
+ This period is defined locally for the relation. Note that a period can
+ be locally defined and inherited simultaneously.
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>perinhcount</structfield></entry>
+ <entry><type>int4</type></entry>
+ <entry></entry>
+ <entry>
+ The number of direct inheritance ancestors this period has. A period
+ with a nonzero number of ancestors cannot be dropped.
+ </entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
+
<sect1 id="catalog-pg-pltemplate">
<title><structname>pg_pltemplate</structname></title>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 2cd0b8ab9d..cdbe06196c 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -919,6 +919,64 @@ CREATE TABLE circles (
</sect2>
</sect1>
+ <sect1 id="ddl-periods">
+ <title>Periods</title>
+
+ <para>
+ Periods are definitions on a table that associate a period name with a start
+ column and an end column. Both columns must be of exactly the same type
+ (including collation), have a btree operator class, and the start column
+ value must be strictly less than the end column value.
+ </para>
+
+ <para>
+ There are two types of periods: application and system. System periods are
+ distinguished by their name which must be <literal>SYSTEM_TIME</literal>. Any
+ other name is an application period.
+ </para>
+
+ <para>
+ Currently, periods in PostgreSQL have no functionality; they can only be
+ defined for future use.
+ </para>
+
+ <sect2 id="ddl-periods-application-periods">
+ <title>Application Periods</title>
+
+ <indexterm>
+ <primary>period</primary>
+ <secondary>application</secondary>
+ </indexterm>
+
+ <para>
+ Application periods are defined on a table using the following syntax:
+ </para>
+
+<programlisting>
+CREATE TABLE billing_addresses (
+ customer_id integer,
+ address_id integer,
+ valid_from date,
+ valid_to date,
+ <emphasis>PERIOD FOR validity (valid_from, valid_to)</emphasis>
+);
+</programlisting>
+ </sect2>
+
+ <sect2 id="ddl-periods-system-periods">
+ <title>System Periods</title>
+
+ <indexterm>
+ <primary>period</primary>
+ <secondary>system</secondary>
+ </indexterm>
+
+ <para>
+ Periods for <literal>SYSTEM_TIME</literal> are currently not implemented.
+ </para>
+ </sect2>
+ </sect1>
+
<sect1 id="ddl-system-columns">
<title>System Columns</title>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 09ef2827f2..95a621370d 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -3348,6 +3348,69 @@ ORDER BY c.ordinal_position;
</table>
</sect1>
+ <sect1 id="infoschema-periods">
+ <title><literal>periods</literal></title>
+
+ <para>
+ The view <literal>parameters</literal> contains information about the
+ periods of all tables in the current database. The start and end column
+ names are only shown if the current user has access to them (by way of being
+ the owner or having some privilege).
+ </para>
+
+ <table>
+ <title><literal>periods</literal> Columns</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>table_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the database containing the period (always the current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>table_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the schema containing the period</entry>
+ </row>
+
+ <row>
+ <entry><literal>table_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the table containing the period</entry>
+ </row>
+
+ <row>
+ <entry><literal>period_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the period</entry>
+ </row>
+
+ <row>
+ <entry><literal>start_column_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the start column for the period</entry>
+ </row>
+
+ <row>
+ <entry><literal>end_column_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the end column for the period</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="infoschema-referential-constraints">
<title><literal>referential_constraints</literal></title>
diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml
index a37d0b756b..6c78fe221a 100644
--- a/doc/src/sgml/keywords.sgml
+++ b/doc/src/sgml/keywords.sgml
@@ -3431,7 +3431,7 @@
</row>
<row>
<entry><token>PERIOD</token></entry>
- <entry></entry>
+ <entry>reserved</entry>
<entry>reserved</entry>
<entry></entry>
<entry></entry>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1cce00eaf9..2a0f16e175 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -58,6 +58,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
+ ADD PERIOD FOR <replaceable class="parameter">period_name</replaceable> ( <replaceable class="parameter">start_column</replaceable>, <replaceable class="parameter">end_column</replaceable> ) [ WITH ( <replaceable class="parameter">period_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ]
+ DROP PERIOD FOR <replaceable class="parameter">period_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
ENABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
ENABLE REPLICA TRIGGER <replaceable class="parameter">trigger_name</replaceable>
@@ -481,6 +483,29 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>ADD PERIOD FOR</literal></term>
+ <listitem>
+ <para>
+ This form adds a new period to a table using the same syntax as
+ <xref linkend="sql-createtable"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DROP PERIOD FOR</literal></term>
+ <listitem>
+ <para>
+ This form drops the specified period on a table. The start and end
+ columns will not be dropped by this command but the
+ <literal>CHECK</literal> constraint will be. You will need to say
+ <literal>CASCADE</literal> if anything outside the table depends on the
+ column.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 965c5a40ad..89a5406da9 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -44,6 +44,7 @@ COMMENT ON
OPERATOR <replaceable class="parameter">operator_name</replaceable> (<replaceable class="parameter">left_type</replaceable>, <replaceable class="parameter">right_type</replaceable>) |
OPERATOR CLASS <replaceable class="parameter">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> |
OPERATOR FAMILY <replaceable class="parameter">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> |
+ PERIOD <replaceable class="parameter">relation_name</replaceable>.<replaceable class="parameter">period_name</replaceable> |
POLICY <replaceable class="parameter">policy_name</replaceable> ON <replaceable class="parameter">table_name</replaceable> |
[ PROCEDURAL ] LANGUAGE <replaceable class="parameter">object_name</replaceable> |
PROCEDURE <replaceable class="parameter">procedure_name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ] |
@@ -328,6 +329,7 @@ COMMENT ON OPERATOR ^ (text, text) IS 'Performs intersection of two texts';
COMMENT ON OPERATOR - (NONE, integer) IS 'Unary minus';
COMMENT ON OPERATOR CLASS int4ops USING btree IS '4 byte integer operators for btrees';
COMMENT ON OPERATOR FAMILY integer_ops USING btree IS 'all integer operators for btrees';
+COMMENT ON PERIOD my_table.my_column IS 'Sales promotion validity';
COMMENT ON POLICY my_policy ON mytable IS 'Filter rows by users';
COMMENT ON PROCEDURE my_proc (integer, integer) IS 'Runs a report';
COMMENT ON ROLE my_role IS 'Administration group for finance tables';
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 2a1eac9592..b030866c12 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -23,6 +23,7 @@ PostgreSQL documentation
<synopsis>
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
+ | <replaceable>period_definition</replaceable>
| <replaceable>table_constraint</replaceable>
| LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
@@ -36,6 +37,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable>
OF <replaceable class="parameter">type_name</replaceable> [ (
{ <replaceable class="parameter">column_name</replaceable> [ WITH OPTIONS ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
+ | <replaceable>period_definition</replaceable>
| <replaceable>table_constraint</replaceable> }
[, ... ]
) ]
@@ -47,6 +49,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable>
PARTITION OF <replaceable class="parameter">parent_table</replaceable> [ (
{ <replaceable class="parameter">column_name</replaceable> [ WITH OPTIONS ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
+ | <replaceable>period_definition</replaceable>
| <replaceable>table_constraint</replaceable> }
[, ... ]
) ] { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }
@@ -69,6 +72,11 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
[ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+<phrase>and <replaceable class="parameter">period_definition</replaceable> is:</phrase>
+
+PERIOD FOR { <replaceable class="parameter">period_name</replaceable> | SYSTEM_TIME } ( <replaceable class="parameter">column_name</replaceable>, <replaceable class="parameter">column_name</replaceable> )
+[ WITH ( <replaceable class="parameter">period_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ]
+
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
@@ -130,6 +138,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
name as any existing data type in the same schema.
</para>
+ <para>
+ Periods my be defined on tables, specifying that two existing columns
+ represent start and end values for the period. Periods may have any name
+ that doesn't conflict with a column name, but the name
+ <literal>SYSTEM_TIME</literal> is special, used for versioning tables.
+ System periods are not yet implemented. See <xref linkend="ddl-periods"/> for more details.
+ </para>
+
<para>
The optional constraint clauses specify constraints (tests) that
new or updated rows must satisfy for an insert or update operation
@@ -639,6 +655,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>PERIOD FOR <replaceable class="parameter">period_name</replaceable> ( <replaceable class="parameter">column_name</replaceable>, <replaceable class="parameter">column_name</replaceable> ) [ WITH ( <replaceable class="parameter">period_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ]</literal></term>
+ <listitem>
+ <para>
+ A period definition gives semantic meaning to two existing columns of
+ the table. It defines a "start column" and an "end column" where the
+ start value is strictly less than the end value. A
+ <literal>CHECK</literal> constraint is automatically created to enforce
+ this. The columns are also marked as <literal>NOT NULL</literal>.
+ </para>
+
+ <para>
+ Both columns must have exactly the same type and must have a btree
+ operator class. The operator class can be specified with the
+ <literal>operator_class</literal> <replaceable
+ class="parameter">period_option</replaceable>. If not specified, the
+ default operator class for the type is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
<listitem>
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 0865240f11..cb5e9f1d2b 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -17,7 +17,7 @@ OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
objectaccess.o objectaddress.o partition.o pg_aggregate.o pg_collation.o \
pg_constraint.o pg_conversion.o \
pg_depend.o pg_enum.o pg_inherits.o pg_largeobject.o pg_namespace.o \
- pg_operator.o pg_proc.o pg_publication.o pg_range.o \
+ pg_operator.o pg_period.o pg_proc.o pg_publication.o pg_range.o \
pg_db_role_setting.o pg_shdepend.o pg_subscription.o pg_type.o \
storage.o toasting.o
@@ -44,7 +44,7 @@ CATALOG_HEADERS := \
pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \
pg_foreign_table.h pg_policy.h pg_replication_origin.h \
pg_default_acl.h pg_init_privs.h pg_seclabel.h pg_shseclabel.h \
- pg_collation.h pg_partitioned_table.h pg_range.h pg_transform.h \
+ pg_collation.h pg_partitioned_table.h pg_period.h pg_range.h pg_transform.h \
pg_sequence.h pg_publication.h pg_publication_rel.h pg_subscription.h \
pg_subscription_rel.h
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 578e4c6592..1f70090367 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -3468,6 +3468,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_DEFAULT:
case OBJECT_DEFACL:
case OBJECT_DOMCONSTRAINT:
+ case OBJECT_PERIOD:
case OBJECT_PUBLICATION_REL:
case OBJECT_ROLE:
case OBJECT_RULE:
@@ -3607,6 +3608,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_DEFAULT:
case OBJECT_DEFACL:
case OBJECT_DOMCONSTRAINT:
+ case OBJECT_PERIOD:
case OBJECT_PUBLICATION_REL:
case OBJECT_ROLE:
case OBJECT_TRANSFORM:
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 4f1d365357..26452e5b76 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -43,6 +43,7 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_opfamily.h"
+#include "catalog/pg_period.h"
#include "catalog/pg_policy.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_publication.h"
@@ -139,6 +140,7 @@ static const Oid object_classes[] = {
CastRelationId, /* OCLASS_CAST */
CollationRelationId, /* OCLASS_COLLATION */
ConstraintRelationId, /* OCLASS_CONSTRAINT */
+ PeriodRelationId, /* OCLASS_PERIOD */
ConversionRelationId, /* OCLASS_CONVERSION */
AttrDefaultRelationId, /* OCLASS_DEFAULT */
LanguageRelationId, /* OCLASS_LANGUAGE */
@@ -1163,6 +1165,10 @@ doDeletion(const ObjectAddress *object, int flags)
RemoveConstraintById(object->objectId);
break;
+ case OCLASS_PERIOD:
+ RemovePeriodById(object->objectId);
+ break;
+
case OCLASS_CONVERSION:
RemoveConversionById(object->objectId);
break;
@@ -2442,6 +2448,9 @@ getObjectClass(const ObjectAddress *object)
case ConstraintRelationId:
return OCLASS_CONSTRAINT;
+ case PeriodRelationId:
+ return OCLASS_PERIOD;
+
case ConversionRelationId:
return OCLASS_CONVERSION;
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 39813de991..dd164efab4 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -50,6 +50,7 @@
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_partitioned_table.h"
+#include "catalog/pg_period.h"
#include "catalog/pg_statistic.h"
#include "catalog/pg_subscription_rel.h"
#include "catalog/pg_tablespace.h"
@@ -2001,6 +2002,68 @@ RelationClearMissing(Relation rel)
heap_close(attr_rel, RowExclusiveLock);
}
+/*
+ * Store a period of relation rel.
+ *
+ * Returns the OID of the new pg_period tuple.
+ */
+Oid
+StorePeriod(Relation rel, const char *periodname, AttrNumber startnum,
+ AttrNumber endnum, Oid opclass, Oid conoid)
+{
+ Datum values[Natts_pg_period];
+ bool nulls[Natts_pg_period];
+ Relation pg_period;
+ HeapTuple tuple;
+ Oid oid;
+ NameData pername;
+ ObjectAddress myself, referenced;
+
+ namestrcpy(&pername, periodname);
+
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, false, sizeof(nulls));
+
+ values[Anum_pg_period_pername - 1] = NameGetDatum(&pername);
+ values[Anum_pg_period_perrelid - 1] = RelationGetRelid(rel);
+ values[Anum_pg_period_perstart - 1] = startnum;
+ values[Anum_pg_period_perend - 1] = endnum;
+ values[Anum_pg_period_peropclass -1] = opclass;
+ values[Anum_pg_period_perconstraint - 1] = conoid;
+
+ pg_period = heap_open(PeriodRelationId, RowExclusiveLock);
+
+ tuple = heap_form_tuple(RelationGetDescr(pg_period), values, nulls);
+ oid = CatalogTupleInsert(pg_period, tuple);
+
+ ObjectAddressSet(myself, PeriodRelationId, oid);
+
+ /* Drop the period when the table is dropped. */
+ ObjectAddressSet(referenced, RelationRelationId, RelationGetRelid(rel));
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);
+
+ /* Forbid dropping the columns of the period. */
+ ObjectAddressSubSet(referenced, RelationRelationId, RelationGetRelid(rel), startnum);
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+ ObjectAddressSubSet(referenced, RelationRelationId, RelationGetRelid(rel), endnum);
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+
+ /* Make sure we don't lose our operator class. */
+ ObjectAddressSet(referenced, OperatorClassRelationId, opclass);
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+
+ /*
+ * The constraint is an implementation detail, so we mark it as such.
+ * (Note that myself and referenced are reversed for this one.)
+ */
+ ObjectAddressSet(referenced, ConstraintRelationId, conoid);
+ recordDependencyOn(&referenced, &myself, DEPENDENCY_INTERNAL_AUTO);
+
+ heap_close(pg_period, RowExclusiveLock);
+
+ return oid;
+}
+
/*
* Store a default expression for column attnum of relation rel.
*
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index f4e69f4a26..4eaeb302bf 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1179,7 +1179,29 @@ GRANT SELECT ON parameters TO PUBLIC;
* PERIODS view
*/
--- feature not supported
+CREATE VIEW periods AS
+ SELECT current_database()::information_schema.sql_identifier AS table_catalog,
+ nc.nspname::information_schema.sql_identifier AS table_schema,
+ c.relname::information_schema.sql_identifier AS table_name,
+ p.pername::information_schema.sql_identifier AS period_name,
+ CASE WHEN pg_has_role(c.relowner, 'USAGE')
+ OR has_column_privilege(sa.attrelid, sa.attnum, 'SELECT, INSERT, UPDATE, REFERENCES')
+ THEN sa.attname::information_schema.sql_identifier
+ END AS start_column_name,
+ CASE WHEN pg_has_role(c.relowner, 'USAGE')
+ OR has_column_privilege(ea.attrelid, ea.attnum, 'SELECT, INSERT, UPDATE, REFERENCES')
+ THEN ea.attname::information_schema.sql_identifier
+ END AS end_column_name
+ FROM pg_period AS p
+ JOIN pg_class AS c ON c.oid = p.perrelid
+ JOIN pg_namespace AS nc ON nc.oid = c.relnamespace
+ JOIN pg_attribute AS sa ON (sa.attrelid, sa.attnum) = (p.perrelid, p.perstart)
+ JOIN pg_attribute AS ea ON (ea.attrelid, ea.attnum) = (p.perrelid, p.perend)
+ WHERE NOT pg_is_other_temp_schema(nc.oid)
+ --AND c.relkind = ANY (ARRAY['r']);
+ AND c.relkind IN ('r', 'v');
+
+GRANT SELECT ON periods TO PUBLIC;
/*
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index ad682673e6..b430236eb8 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -42,6 +42,7 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_opfamily.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_period.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_policy.h"
#include "catalog/pg_publication.h"
@@ -587,6 +588,10 @@ static const struct object_type_map
{
"domain constraint", OBJECT_DOMCONSTRAINT
},
+ /* OCLASS_PERIOD */
+ {
+ "period", OBJECT_PERIOD
+ },
/* OCLASS_CONVERSION */
{
"conversion", OBJECT_CONVERSION
@@ -844,6 +849,7 @@ get_object_address(ObjectType objtype, Node *object,
case OBJECT_TRIGGER:
case OBJECT_TABCONSTRAINT:
case OBJECT_POLICY:
+ case OBJECT_PERIOD:
address = get_object_address_relobject(objtype, castNode(List, object),
&relation, missing_ok);
break;
@@ -1335,6 +1341,13 @@ get_object_address_relobject(ObjectType objtype, List *object,
InvalidOid;
address.objectSubId = 0;
break;
+ case OBJECT_PERIOD:
+ address.classId = PeriodRelationId;
+ address.objectId = relation ?
+ get_relation_period_oid(reloid, depname, missing_ok) :
+ InvalidOid;
+ address.objectSubId = 0;
+ break;
default:
elog(ERROR, "unrecognized objtype: %d", (int) objtype);
}
@@ -2129,6 +2142,7 @@ pg_get_object_address(PG_FUNCTION_ARGS)
case OBJECT_RULE:
case OBJECT_TRIGGER:
case OBJECT_TABCONSTRAINT:
+ case OBJECT_PERIOD:
case OBJECT_OPCLASS:
case OBJECT_OPFAMILY:
objnode = (Node *) name;
@@ -2243,6 +2257,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
case OBJECT_TRIGGER:
case OBJECT_POLICY:
case OBJECT_TABCONSTRAINT:
+ case OBJECT_PERIOD:
if (!pg_class_ownercheck(RelationGetRelid(relation), roleid))
aclcheck_error(ACLCHECK_NOT_OWNER, objtype,
RelationGetRelationName(relation));
@@ -2804,6 +2819,38 @@ getObjectDescription(const ObjectAddress *object)
break;
}
+ case OCLASS_PERIOD:
+ {
+ HeapTuple perTup;
+ Form_pg_period per;
+
+ perTup = SearchSysCache1(PERIODOID,
+ ObjectIdGetDatum(object->objectId));
+ if (!HeapTupleIsValid(perTup))
+ elog(ERROR, "cache lookup failed for period %u",
+ object->objectId);
+ per = (Form_pg_period) GETSTRUCT(perTup);
+
+ if (OidIsValid(per->perrelid))
+ {
+ StringInfoData rel;
+
+ initStringInfo(&rel);
+ getRelationDescription(&rel, per->perrelid);
+ appendStringInfo(&buffer, _("period %s on %s"),
+ NameStr(per->pername), rel.data);
+ pfree(rel.data);
+ }
+ else
+ {
+ appendStringInfo(&buffer, _("period %s"),
+ NameStr(per->pername));
+ }
+
+ ReleaseSysCache(perTup);
+ break;
+ }
+
case OCLASS_CONVERSION:
{
HeapTuple conTup;
@@ -3942,6 +3989,10 @@ getObjectTypeDescription(const ObjectAddress *object)
getConstraintTypeDescription(&buffer, object->objectId);
break;
+ case OCLASS_PERIOD:
+ appendStringInfoString(&buffer, "period");
+ break;
+
case OCLASS_CONVERSION:
appendStringInfoString(&buffer, "conversion");
break;
@@ -4355,6 +4406,28 @@ getObjectIdentityParts(const ObjectAddress *object,
break;
}
+ case OCLASS_PERIOD:
+ {
+ HeapTuple perTup;
+ Form_pg_period per;
+
+ perTup = SearchSysCache1(PERIODOID,
+ ObjectIdGetDatum(object->objectId));
+ if (!HeapTupleIsValid(perTup))
+ elog(ERROR, "cache lookup failed for period %u",
+ object->objectId);
+ per = (Form_pg_period) GETSTRUCT(perTup);
+
+ appendStringInfo(&buffer, "%s on ",
+ quote_identifier(NameStr(per->pername)));
+ getRelationIdentity(&buffer, per->perrelid, objname);
+ if (objname)
+ *objname = lappend(*objname, pstrdup(NameStr(per->pername)));
+
+ ReleaseSysCache(perTup);
+ break;
+ }
+
case OCLASS_CONVERSION:
{
HeapTuple conTup;
diff --git a/src/backend/catalog/pg_period.c b/src/backend/catalog/pg_period.c
new file mode 100644
index 0000000000..e819ea0cff
--- /dev/null
+++ b/src/backend/catalog/pg_period.c
@@ -0,0 +1,106 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_period.c
+ * routines to support manipulation of the pg_period relation
+ *
+ * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/catalog/pg_period.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/genam.h"
+#include "access/heapam.h"
+#include "access/htup_details.h"
+#include "catalog/indexing.h"
+#include "catalog/pg_period.h"
+#include "utils/fmgroids.h"
+#include "utils/lsyscache.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
+
+
+/*
+ * Delete a single period record.
+ */
+void
+RemovePeriodById(Oid periodId)
+{
+ Relation pg_period;
+ HeapTuple tup;
+
+ pg_period = heap_open(PeriodRelationId, RowExclusiveLock);
+
+ tup = SearchSysCache1(PERIODOID, ObjectIdGetDatum(periodId));
+ if (!HeapTupleIsValid(tup)) /* should not happen */
+ elog(ERROR, "cache lookup failed for period %u", periodId);
+
+ /* Fry the period itself */
+ CatalogTupleDelete(pg_period, &tup->t_self);
+
+ /* Clean up */
+ ReleaseSysCache(tup);
+ heap_close(pg_period, RowExclusiveLock);
+}
+
+/*
+ * get_relation_period_oid
+ * Find a period on the specified relation with the specified name.
+ * Returns period's OID.
+ */
+Oid
+get_relation_period_oid(Oid relid, const char *pername, bool missing_ok)
+{
+ Relation pg_period;
+ HeapTuple tuple;
+ SysScanDesc scan;
+ ScanKeyData skey[1];
+ Oid perOid = InvalidOid;
+
+ /*
+ * Fetch the period tuple from pg_period. There may be more than
+ * one match, because periods are not required to have unique names;
+ * if so, error out.
+ */
+ pg_period = heap_open(PeriodRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey[0],
+ Anum_pg_period_perrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(relid));
+
+ scan = systable_beginscan(pg_period, PeriodRelidNameIndexId, true,
+ NULL, 1, skey);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ Form_pg_period period = (Form_pg_period) GETSTRUCT(tuple);
+
+ if (strcmp(NameStr(period->pername), pername) == 0)
+ {
+ if (OidIsValid(perOid))
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("table \"%s\" has multiple periods named \"%s\"",
+ get_rel_name(relid), pername)));
+ perOid = HeapTupleGetOid(tuple);
+ }
+ }
+
+ systable_endscan(scan);
+
+ /* If no such period exists, complain */
+ if (!OidIsValid(perOid) && !missing_ok)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("period \"%s\" for table \"%s\" does not exist",
+ pername, get_rel_name(relid))));
+
+ heap_close(pg_period, AccessShareLock);
+
+ return perOid;
+}
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index aeb262a5b0..61088d97ea 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -426,7 +426,7 @@ T176 Sequence generator support NO
T177 Sequence generator support: simple restart option YES
T178 Identity columns: simple restart option YES
T180 System-versioned tables NO
-T181 Application-time period tables NO
+T181 Application-time period tables YES
T191 Referential action RESTRICT YES
T201 Comparable data types for referential constraints YES
T211 Basic trigger capability NO
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index eff325cc7d..ee64694fb7 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -608,6 +608,7 @@ AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid,
case OCLASS_CAST:
case OCLASS_CONSTRAINT:
+ case OCLASS_PERIOD:
case OCLASS_DEFAULT:
case OCLASS_LANGUAGE:
case OCLASS_LARGEOBJECT:
diff --git a/src/backend/commands/comment.c b/src/backend/commands/comment.c
index 2f2e69b4a8..4e9c0fbf92 100644
--- a/src/backend/commands/comment.c
+++ b/src/backend/commands/comment.c
@@ -101,6 +101,16 @@ CommentObject(CommentStmt *stmt)
errmsg("\"%s\" is not a table, view, materialized view, composite type, or foreign table",
RelationGetRelationName(relation))));
break;
+
+ case OBJECT_PERIOD:
+ /* Periods can only go on tables */
+ if (relation->rd_rel->relkind != RELKIND_RELATION)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a table",
+ RelationGetRelationName(relation))));
+ break;
+
default:
break;
}
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index eecc85d14e..9c98a18495 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -1126,6 +1126,7 @@ EventTriggerSupportsObjectType(ObjectType obtype)
case OBJECT_OPCLASS:
case OBJECT_OPERATOR:
case OBJECT_OPFAMILY:
+ case OBJECT_PERIOD:
case OBJECT_POLICY:
case OBJECT_PROCEDURE:
case OBJECT_PUBLICATION:
@@ -1181,6 +1182,7 @@ EventTriggerSupportsObjectClass(ObjectClass objclass)
case OCLASS_CAST:
case OCLASS_COLLATION:
case OCLASS_CONSTRAINT:
+ case OCLASS_PERIOD:
case OCLASS_CONVERSION:
case OCLASS_DEFAULT:
case OCLASS_LANGUAGE:
@@ -2264,6 +2266,7 @@ stringify_grant_objtype(ObjectType objtype)
case OBJECT_OPCLASS:
case OBJECT_OPERATOR:
case OBJECT_OPFAMILY:
+ case OBJECT_PERIOD:
case OBJECT_POLICY:
case OBJECT_PUBLICATION:
case OBJECT_PUBLICATION_REL:
@@ -2346,6 +2349,7 @@ stringify_adefprivs_objtype(ObjectType objtype)
case OBJECT_OPCLASS:
case OBJECT_OPERATOR:
case OBJECT_OPFAMILY:
+ case OBJECT_PERIOD:
case OBJECT_POLICY:
case OBJECT_PUBLICATION:
case OBJECT_PUBLICATION_REL:
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0e95037dcf..81398adfb8 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -40,6 +40,7 @@
#include "catalog/pg_inherits.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
+#include "catalog/pg_period.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -365,6 +366,8 @@ static ObjectAddress ATExecAddColumn(List **wqueue, AlteredTableInfo *tab,
bool if_not_exists, LOCKMODE lockmode);
static bool check_for_column_name_collision(Relation rel, const char *colname,
bool if_not_exists);
+static bool check_for_period_name_collision(Relation rel, const char *pername,
+ bool if_not_exists);
static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid);
static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid);
static void ATPrepAddOids(List **wqueue, Relation rel, bool recurse,
@@ -376,6 +379,11 @@ static ObjectAddress ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
const char *colName, LOCKMODE lockmode);
static ObjectAddress ATExecColumnDefault(Relation rel, const char *colName,
Node *newDefault, LOCKMODE lockmode);
+static ObjectAddress ATExecAddPeriod(Relation rel, Period *period, LOCKMODE lockmode);
+static void ATExecDropPeriod(Relation rel, const char *periodName,
+ DropBehavior behavior,
+ bool recurse, bool recursing,
+ bool missing_ok, LOCKMODE lockmode);
static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName,
Node *def, LOCKMODE lockmode);
static ObjectAddress ATExecSetIdentity(Relation rel, const char *colName,
@@ -1854,6 +1862,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
*
* Note that we also need to check that we do not exceed this figure after
* including columns from inherited relations.
+ *
+ * TODO: What about periods?
*/
if (list_length(schema) > MaxHeapAttributeNumber)
ereport(ERROR,
@@ -1932,6 +1942,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
* Scan the parents left-to-right, and merge their attributes to form a
* list of inherited attributes (inhSchema). Also check to see if we need
* to inherit an OID column.
+ *
+ * TODO: probably need periods here, too.
*/
child_attno = 0;
foreach(entry, supers)
@@ -3382,6 +3394,20 @@ AlterTableGetLockLevel(List *cmds)
cmd_lockmode = AccessExclusiveLock;
break;
+ /*
+ * Adding a period may conflict with a column name so we need
+ * an exclusive lock to make sure columns aren't added
+ * concurrently.
+ *
+ * It also adds a CHECK constraint so we need to match that
+ * level, and dropping a period drops the constraint so that
+ * level needs to be matched, too.
+ */
+ case AT_AddPeriod:
+ case AT_DropPeriod:
+ cmd_lockmode = AccessExclusiveLock;
+ break;
+
/*
* These subcommands may require addition of toast tables. If
* we add a toast table to a table currently being scanned, we
@@ -3690,6 +3716,14 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* No command-specific prep needed */
pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP;
break;
+ case AT_AddPeriod: /* ALTER TABLE ... ADD PERIOD FOR name (start, end) */
+ ATSimplePermissions(rel, ATT_TABLE);
+ pass = AT_PASS_ADD_CONSTR;
+ break;
+ case AT_DropPeriod: /* ALTER TABLE ... DROP PERIOD FOR name */
+ ATSimplePermissions(rel, ATT_TABLE);
+ pass = AT_PASS_DROP;
+ break;
case AT_AddIdentity:
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
pass = AT_PASS_ADD_CONSTR;
@@ -4032,6 +4066,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */
address = ATExecColumnDefault(rel, cmd->name, cmd->def, lockmode);
break;
+ case AT_AddPeriod:
+ address = ATExecAddPeriod(rel, (Period *) cmd->def, lockmode);
+ break;
+ case AT_DropPeriod:
+ ATExecDropPeriod(rel, cmd->name, cmd->behavior,
+ false, false,
+ cmd->missing_ok, lockmode);
+ break;
case AT_AddIdentity:
address = ATExecAddIdentity(rel, cmd->name, cmd->def, lockmode);
break;
@@ -5731,14 +5773,29 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
/*
* If a new or renamed column will collide with the name of an existing
* column and if_not_exists is false then error out, else do nothing.
+ *
+ * See also check_for_period_name_collision.
*/
static bool
check_for_column_name_collision(Relation rel, const char *colname,
bool if_not_exists)
{
- HeapTuple attTuple;
+ HeapTuple attTuple, perTuple;
int attnum;
+ /* If the name exists as a period, we're done. */
+ perTuple = SearchSysCache2(PERIODNAME,
+ ObjectIdGetDatum(RelationGetRelid(rel)),
+ PointerGetDatum(colname));
+ if (HeapTupleIsValid(perTuple))
+ {
+ ReleaseSysCache(perTuple);
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_COLUMN),
+ errmsg("column name \"%s\" conflicts with a period name",
+ colname)));
+ }
+
/*
* this test is deliberately not attisdropped-aware, since if one tries to
* add a column matching a dropped column name, it's gonna fail anyway.
@@ -5782,6 +5839,78 @@ check_for_column_name_collision(Relation rel, const char *colname,
return true;
}
+/*
+ * If a new period name will collide with the name of an existing column or
+ * period [and if_not_exists is false] then error out, else do nothing.
+ *
+ * See also check_for_column_name_collision.
+ */
+static bool
+check_for_period_name_collision(Relation rel, const char *pername,
+ bool if_not_exists)
+{
+ HeapTuple attTuple, perTuple;
+ int attnum;
+
+ /* TODO: implement IF [NOT] EXISTS for periods */
+ Assert(!if_not_exists);
+
+ /* If there is already a period with this name, then we're done. */
+ perTuple = SearchSysCache2(PERIODNAME,
+ ObjectIdGetDatum(RelationGetRelid(rel)),
+ PointerGetDatum(pername));
+ if (HeapTupleIsValid(perTuple))
+ {
+ if (if_not_exists)
+ {
+ ReleaseSysCache(perTuple);
+
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_COLUMN),
+ errmsg("period \"%s\" of relation \"%s\" already exists, skipping",
+ pername, RelationGetRelationName(rel))));
+ return false;
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_COLUMN),
+ errmsg("period \"%s\" of relation \"%s\" already exists",
+ pername, RelationGetRelationName(rel))));
+ }
+
+ /*
+ * this test is deliberately not attisdropped-aware, since if one tries to
+ * add a column matching a dropped column name, it's gonna fail anyway.
+ *
+ * XXX: Does this hold for periods?
+ */
+ attTuple = SearchSysCache2(ATTNAME,
+ ObjectIdGetDatum(RelationGetRelid(rel)),
+ PointerGetDatum(pername));
+ if (HeapTupleIsValid(attTuple))
+ {
+ attnum = ((Form_pg_attribute) GETSTRUCT(attTuple))->attnum;
+ ReleaseSysCache(attTuple);
+
+ /*
+ * We throw a different error message for conflicts with system column
+ * names, since they are normally not shown and the user might otherwise
+ * be confused about the reason for the conflict.
+ */
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_COLUMN),
+ errmsg("period name \"%s\" conflicts with a system column name",
+ pername)));
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_COLUMN),
+ errmsg("period name \"%s\" conflicts with a column name",
+ pername)));
+ }
+
+ return true;
+}
+
/*
* Install a column's dependency on its datatype.
*/
@@ -6154,6 +6283,285 @@ ATExecColumnDefault(Relation rel, const char *colName,
return address;
}
+/*
+ * make_constraints_for_period
+ *
+ * Add constraints to make both columns NOT NULL and CHECK (start < end).
+ *
+ * Returns the CHECK constraint Oid.
+ */
+static Oid
+make_constraints_for_period(Relation rel, Period *period, char *constraintname)
+{
+ List *cmds = NIL;
+ AlterTableCmd *cmd;
+ ColumnRef *scol, *ecol;
+ Constraint *constr;
+ char *conname;
+
+ /* Start column must be NOT NULL */
+ cmd = makeNode(AlterTableCmd);
+ cmd->subtype = AT_SetNotNull;
+ cmd->name = period->startcolname;
+ cmds = lappend(cmds, cmd);
+
+ /* End column must be NOT NULL */
+ cmd = makeNode(AlterTableCmd);
+ cmd->subtype = AT_SetNotNull;
+ cmd->name = period->endcolname;
+ cmds = lappend(cmds, cmd);
+
+ /*
+ * Create the CHECK constraint
+ */
+ if (constraintname)
+ conname = constraintname;
+ else
+ conname = ChooseConstraintName(RelationGetRelationName(rel),
+ period->periodname,
+ "check",
+ RelationGetNamespace(rel),
+ NIL);
+
+ scol = makeNode(ColumnRef);
+ scol->fields = list_make1(makeString(pstrdup(period->startcolname)));
+ scol->location = 0;
+
+ ecol = makeNode(ColumnRef);
+ ecol->fields = list_make1(makeString(pstrdup(period->endcolname)));
+ ecol->location = 0;
+
+ constr = makeNode(Constraint);
+ constr->contype = CONSTR_CHECK;
+ constr->conname = conname;
+ constr->deferrable = false;
+ constr->initdeferred = false;
+ constr->location = 0;
+ constr->is_no_inherit = false;
+ constr->raw_expr = (Node *) makeSimpleA_Expr(AEXPR_OP, "<", (Node *) scol, (Node *) ecol, 0);
+ constr->cooked_expr = NULL;
+ constr->skip_validation = false;
+ constr->initially_valid = true;
+
+ cmd = makeNode(AlterTableCmd);
+ cmd->subtype = AT_AddConstraint;
+ cmd->def = (Node *) constr;
+ cmds = lappend(cmds, cmd);
+
+ /* Do the deed. */
+ AlterTableInternal(RelationGetRelid(rel), cmds, true);
+
+ return get_relation_constraint_oid(RelationGetRelid(rel), conname, false);
+}
+
+/*
+ * ALTER TABLE ADD PERIOD
+ *
+ * Return the address of the what?
+ */
+static ObjectAddress
+ATExecAddPeriod(Relation rel, Period *period, LOCKMODE lockmode)
+{
+ Relation attrelation;
+ HeapTuple starttuple, endtuple;
+ Form_pg_attribute startatttuple, endatttuple;
+ AttrNumber startattnum, endattnum;
+ ListCell *option;
+ DefElem *dconstraintname = NULL;
+ DefElem *dopclass = NULL;
+ Oid conoid, opclass;
+
+ /*
+ * PERIOD FOR SYSTEM_TIME is not yet implemented, but make sure no one uses
+ * the name.
+ */
+ if (strcmp(period->periodname, "system_time") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PERIOD FOR SYSTEM_TIME is not supported")));
+
+ /* The period name must not already exist */
+ (void) check_for_period_name_collision(rel, period->periodname, false);
+
+ /* Parse options */
+ foreach(option, period->options)
+ {
+ DefElem *defel = (DefElem *) lfirst(option);
+
+ if (strcmp(defel->defname, "check_constraint_name") == 0)
+ {
+ if (dconstraintname)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ dconstraintname = defel;
+ }
+ else if (strcmp(defel->defname, "operator_class") == 0)
+ {
+ if (dopclass)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ dopclass = defel;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("option \"%s\" not recognized", defel->defname)));
+ }
+
+ attrelation = heap_open(AttributeRelationId, RowExclusiveLock);
+
+ /* Find the start column */
+ starttuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), period->startcolname);
+ if (!HeapTupleIsValid(starttuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ period->startcolname, RelationGetRelationName(rel))));
+ startatttuple = (Form_pg_attribute) GETSTRUCT(starttuple);
+ startattnum = startatttuple->attnum;
+
+ /* Make sure it's not a system column */
+ if (startattnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot use system column \"%s\" in period",
+ period->startcolname)));
+
+ /*
+ * Get the btree operator class for it (ResolveOpClass will error out if
+ * necessary)
+ */
+ opclass = ResolveOpClass(dopclass ? defGetQualifiedName(dopclass) : NULL,
+ startatttuple->atttypid,
+ "btree", BTREE_AM_OID);
+
+ /* Find the end column */
+ endtuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), period->endcolname);
+ if (!HeapTupleIsValid(endtuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ period->endcolname, RelationGetRelationName(rel))));
+ endatttuple = (Form_pg_attribute) GETSTRUCT(endtuple);
+ endattnum = endatttuple->attnum;
+
+ /* Make sure it's not a system column */
+ if (endattnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot use system column \"%s\" in period",
+ period->endcolname)));
+
+ /* Both columns must be of same type */
+ if (startatttuple->atttypid != endatttuple->atttypid)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("start and end columns of period must be of same type")));
+
+ /* Both columns must have the same collation */
+ if (startatttuple->attcollation != endatttuple->attcollation)
+ ereport(ERROR,
+ (errcode(ERRCODE_COLLATION_MISMATCH),
+ errmsg("start and end columns of period must have same collation")));
+
+ heap_freetuple(starttuple);
+ heap_freetuple(endtuple);
+
+ conoid = make_constraints_for_period(rel, period,
+ dconstraintname ? defGetString(dconstraintname) : NULL);
+
+ /* Save it */
+ StorePeriod(rel, period->periodname, startattnum, endattnum, opclass, conoid);
+
+ heap_close(attrelation, RowExclusiveLock);
+
+ return InvalidObjectAddress;
+}
+
+/*
+ * ALTER TABLE DROP PERIOD
+ *
+ * Like DROP COLUMN, we can't use the normal ALTER TABLE recursion mechanism.
+ */
+static void
+ATExecDropPeriod(Relation rel, const char *periodName,
+ DropBehavior behavior,
+ bool recurse, bool recursing,
+ bool missing_ok, LOCKMODE lockmode)
+{
+ Relation pg_period;
+ Form_pg_period period;
+ SysScanDesc scan;
+ ScanKeyData key;
+ HeapTuple tuple;
+ bool found = false;
+
+ /* At top level, permission check was done in ATPrepCmd, else do it */
+ if (recursing)
+ ATSimplePermissions(rel, ATT_TABLE);
+
+ pg_period = heap_open(PeriodRelationId, RowExclusiveLock);
+
+ /*
+ * Find and drop the target period
+ */
+ ScanKeyInit(&key,
+ Anum_pg_period_perrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ scan = systable_beginscan(pg_period, PeriodRelidNameIndexId,
+ true, NULL, 1, &key);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ ObjectAddress perobj;
+
+ period = (Form_pg_period) GETSTRUCT(tuple);
+
+ if (strcmp(NameStr(period->pername), periodName) != 0)
+ continue;
+
+ /*
+ * Perform the actual period deletion
+ */
+ perobj.classId = PeriodRelationId;
+ perobj.objectId = HeapTupleGetOid(tuple);
+ perobj.objectSubId = 0;
+
+ performDeletion(&perobj, behavior, 0);
+
+ found = true;
+
+ /* period found and dropped -- no need to keep looping */
+ break;
+ }
+
+ systable_endscan(scan);
+
+ if (!found)
+ {
+ if (!missing_ok)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("period \"%s\" on relation \"%s\" does not exist",
+ periodName, RelationGetRelationName(rel))));
+ }
+ else
+ {
+ ereport(NOTICE,
+ (errmsg("period \"%s\" on relation \"%s\" does not exist, skipping",
+ periodName, RelationGetRelationName(rel))));
+ heap_close(pg_period, RowExclusiveLock);
+ return;
+ }
+ }
+
+ heap_close(pg_period, RowExclusiveLock);
+}
+
/*
* ALTER TABLE ALTER COLUMN ADD IDENTITY
*
@@ -9512,6 +9920,15 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
}
break;
+ case OCLASS_PERIOD:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter type of a column used by a period"),
+ errdetail("%s depends on column \"%s\"",
+ getObjectDescription(&foundObject),
+ colName)));
+ break;
+
case OCLASS_REWRITE:
/* XXX someday see if we can cope with revising views */
ereport(ERROR,
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 7c045a7afe..fa5bc9cdd3 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2911,6 +2911,20 @@ _copyConstraint(const Constraint *from)
return newnode;
}
+static Period *
+_copyPeriod(const Period *from)
+{
+ Period *newnode = makeNode(Period);
+
+ COPY_STRING_FIELD(periodname);
+ COPY_STRING_FIELD(startcolname);
+ COPY_STRING_FIELD(endcolname);
+ COPY_NODE_FIELD(options);
+ COPY_LOCATION_FIELD(location);
+
+ return newnode;
+}
+
static DefElem *
_copyDefElem(const DefElem *from)
{
@@ -5553,6 +5567,9 @@ copyObjectImpl(const void *from)
case T_Constraint:
retval = _copyConstraint(from);
break;
+ case T_Period:
+ retval = _copyPeriod(from);
+ break;
case T_DefElem:
retval = _copyDefElem(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 6a971d0141..13fc726cbb 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2602,6 +2602,18 @@ _equalConstraint(const Constraint *a, const Constraint *b)
return true;
}
+static bool
+_equalPeriod(const Period *a, const Period *b)
+{
+ COMPARE_STRING_FIELD(periodname);
+ COMPARE_STRING_FIELD(startcolname);
+ COMPARE_STRING_FIELD(endcolname);
+ COMPARE_NODE_FIELD(options);
+ COMPARE_LOCATION_FIELD(location);
+
+ return true;
+}
+
static bool
_equalDefElem(const DefElem *a, const DefElem *b)
{
@@ -3630,6 +3642,9 @@ equal(const void *a, const void *b)
case T_Constraint:
retval = _equalConstraint(a, b);
break;
+ case T_Period:
+ retval = _equalPeriod(a, b);
+ break;
case T_DefElem:
retval = _equalDefElem(a, b);
break;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index a10014f755..a45d82cf40 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1501,6 +1501,9 @@ exprLocation(const Node *expr)
case T_Constraint:
loc = ((const Constraint *) expr)->location;
break;
+ case T_Period:
+ loc = ((const Period *) expr)->location;
+ break;
case T_FunctionParameter:
/* just use typename's location */
loc = exprLocation((Node *) ((const FunctionParameter *) expr)->argType);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 1da9d7ed15..9aa4808015 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3592,6 +3592,18 @@ _outConstraint(StringInfo str, const Constraint *node)
}
}
+static void
+_outPeriod(StringInfo str, const Period *node)
+{
+ WRITE_NODE_TYPE("PERIOD");
+
+ WRITE_STRING_FIELD(periodname);
+ WRITE_STRING_FIELD(startcolname);
+ WRITE_STRING_FIELD(endcolname);
+ WRITE_NODE_FIELD(options);
+ WRITE_LOCATION_FIELD(location);
+}
+
static void
_outForeignKeyCacheInfo(StringInfo str, const ForeignKeyCacheInfo *node)
{
@@ -4275,6 +4287,9 @@ outNode(StringInfo str, const void *obj)
case T_Constraint:
_outConstraint(str, obj);
break;
+ case T_Period:
+ _outPeriod(str, obj);
+ break;
case T_FuncCall:
_outFuncCall(str, obj);
break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 90dfac2cb1..430021a984 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -533,7 +533,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <keyword> unreserved_keyword type_func_name_keyword
%type <keyword> col_name_keyword reserved_keyword
-%type <node> TableConstraint TableLikeClause
+%type <node> TableConstraint TableLikeClause TablePeriod
%type <ival> TableLikeOptionList TableLikeOption
%type <list> ColQualList
%type <node> ColConstraint ColConstraintElem ConstraintAttr
@@ -662,7 +662,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 PERIOD PLACING PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -2261,6 +2261,24 @@ alter_table_cmd:
n->def = (Node *) $4;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> ADD PERIOD FOR <name> (<name>, <name>) */
+ | ADD_P TablePeriod
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_AddPeriod;
+ n->def = $2;
+ $$ = (Node *)n;
+ }
+ /* ALTER TABLE <name> DROP PERIOD FOR <name> [RESTRICT|CASCADE] */
+ | DROP PERIOD FOR name opt_drop_behavior
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_DropPeriod;
+ n->name = $4;
+ n->behavior = $5;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> ADD CONSTRAINT ... */
| ADD_P TableConstraint
{
@@ -3373,8 +3391,10 @@ TableElement:
columnDef { $$ = $1; }
| TableLikeClause { $$ = $1; }
| TableConstraint { $$ = $1; }
+ | TablePeriod { $$ = $1; }
;
+
TypedTableElement:
columnOptions { $$ = $1; }
| TableConstraint { $$ = $1; }
@@ -3651,6 +3671,19 @@ TableLikeOption:
;
+TablePeriod:
+ PERIOD FOR name '(' name ',' name ')' opt_definition
+ {
+ Period *n = makeNode(Period);
+ n->periodname = $3;
+ n->startcolname = $5;
+ n->endcolname = $7;
+ n->options = $9;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
/* ConstraintElem specifies constraint syntax which is not embedded into
* a column definition. ColConstraintElem specifies the embedded form.
* - thomas 1997-12-03
@@ -6422,6 +6455,7 @@ opt_restart_seqs:
* OPERATOR <op> (leftoperand_typ, rightoperand_typ) |
* OPERATOR CLASS <name> USING <access-method> |
* OPERATOR FAMILY <name> USING <access-method> |
+ * PERIOD <relname>.<periodname> |
* RULE <rulename> ON <relname> |
* TRIGGER <triggername> ON <relname> ]
* IS { 'text' | NULL }
@@ -6604,6 +6638,7 @@ comment_type_any_name:
| TEXT_P SEARCH DICTIONARY { $$ = OBJECT_TSDICTIONARY; }
| TEXT_P SEARCH PARSER { $$ = OBJECT_TSPARSER; }
| TEXT_P SEARCH TEMPLATE { $$ = OBJECT_TSTEMPLATE; }
+ | PERIOD { $$ = OBJECT_PERIOD; }
;
/* object types taking name */
@@ -15447,6 +15482,7 @@ reserved_keyword:
| ONLY
| OR
| ORDER
+ | PERIOD
| PLACING
| PRIMARY
| REFERENCES
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 17b54b20cc..43bbb3bbf7 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -114,6 +114,8 @@ typedef struct
static void transformColumnDefinition(CreateStmtContext *cxt,
ColumnDef *column);
+static void transformTablePeriod(CreateStmtContext *cxt,
+ Period *period);
static void transformTableConstraint(CreateStmtContext *cxt,
Constraint *constraint);
static void transformTableLikeClause(CreateStmtContext *cxt,
@@ -286,6 +288,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
transformColumnDefinition(&cxt, (ColumnDef *) element);
break;
+ case T_Period:
+ transformTablePeriod(&cxt, (Period *) element);
+ break;
+
case T_Constraint:
transformTableConstraint(&cxt, (Constraint *) element);
break;
@@ -806,6 +812,42 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
}
}
+/*
+ * transformTablePeriod
+ * transform a Period node within CREATE TABLE or ALTER TABLE
+ */
+static void
+transformTablePeriod(CreateStmtContext *cxt, Period *period)
+{
+ AlterTableStmt *alterstmt;
+ AlterTableCmd *altercmd;
+
+ if (strcmp(period->periodname, "system_time") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PERIOD FOR SYSTEM_TIME is not supported"),
+ parser_errposition(cxt->pstate,
+ period->location)));
+
+ /*
+ * Instead of duplicating code, just create an ALTER TABLE statement to run
+ * after the table is created.
+ */
+ alterstmt = makeNode(AlterTableStmt);
+ alterstmt->relation = cxt->relation;
+ alterstmt->cmds = NIL;
+ alterstmt->relkind = OBJECT_TABLE;
+
+ altercmd = makeNode(AlterTableCmd);
+ altercmd->subtype = AT_AddPeriod;
+ altercmd->name = NULL;
+ altercmd->def = (Node *) period;
+
+ alterstmt->cmds = lappend(alterstmt->cmds, altercmd);
+
+ cxt->alist = lappend(cxt->alist, alterstmt);
+}
+
/*
* transformTableConstraint
* transform a Constraint node within CREATE TABLE or ALTER TABLE
@@ -3023,6 +3065,23 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
(int) nodeTag(cmd->def));
break;
+ case AT_AddPeriod:
+ {
+ /*
+ Period *period = castNode(Period, cmd->def);
+
+ if (strcmp(period->periodname, "system_time") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("period for system_time is not supported"),
+ parser_errposition(cxt->pstate,
+ period->location)));
+
+ */
+ newcmds = lappend(newcmds, cmd);
+ break;
+ }
+
case AT_ProcessedConstraint:
/*
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index 2b381782a3..3067cce14a 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -49,6 +49,7 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_opfamily.h"
#include "catalog/pg_partitioned_table.h"
+#include "catalog/pg_period.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_publication.h"
#include "catalog/pg_publication_rel.h"
@@ -584,6 +585,27 @@ static const struct cachedesc cacheinfo[] = {
},
32
},
+ {PeriodRelationId, /* PERIODNAME */
+ PeriodRelidNameIndexId,
+ 2,
+ {
+ Anum_pg_period_perrelid,
+ Anum_pg_period_pername,
+ 0,
+ 0
+ },
+ 32
+ },
+ {PeriodRelationId, /* PERIODOID */
+ PeriodObjectIndexId,
+ 1,
+ {
+ ObjectIdAttributeNumber,
+ 0,
+ 0
+ },
+ 32
+ },
{ProcedureRelationId, /* PROCNAMEARGSNSP */
ProcedureNameArgsNspIndexId,
3,
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 83c976eaf7..edb1fa5085 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3686,6 +3686,7 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData)
strcmp(te->desc, "DATABASE PROPERTIES") == 0 ||
strcmp(te->desc, "DEFAULT") == 0 ||
strcmp(te->desc, "FK CONSTRAINT") == 0 ||
+ strcmp(te->desc, "PERIOD") == 0 ||
strcmp(te->desc, "INDEX") == 0 ||
strcmp(te->desc, "RULE") == 0 ||
strcmp(te->desc, "TRIGGER") == 0 ||
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f8fbcdad5b..9824aa5342 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5843,6 +5843,7 @@ getTables(Archive *fout, int *numTables)
int i_initrrelacl;
int i_rolname;
int i_relchecks;
+ int i_nperiod;
int i_relhastriggers;
int i_relhasindex;
int i_relhasrules;
@@ -5891,6 +5892,114 @@ getTables(Archive *fout, int *numTables)
* we cannot correctly identify inherited columns, owned sequences, etc.
*/
+ if (fout->remoteVersion >= 0 /* TODO */)
+ {
+ PQExpBuffer acl_subquery = createPQExpBuffer();
+ PQExpBuffer racl_subquery = createPQExpBuffer();
+ PQExpBuffer initacl_subquery = createPQExpBuffer();
+ PQExpBuffer initracl_subquery = createPQExpBuffer();
+
+ PQExpBuffer attacl_subquery = createPQExpBuffer();
+ PQExpBuffer attracl_subquery = createPQExpBuffer();
+ PQExpBuffer attinitacl_subquery = createPQExpBuffer();
+ PQExpBuffer attinitracl_subquery = createPQExpBuffer();
+
+ /*
+ * Left join to pick up dependency info linking sequences to their
+ * owning column, if any (note this dependency is AUTO as of 8.2)
+ *
+ * Left join to detect if any privileges are still as-set-at-init, in
+ * which case we won't dump out ACL commands for those.
+ */
+
+ buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
+ initracl_subquery, "c.relacl", "c.relowner",
+ "CASE WHEN c.relkind = " CppAsString2(RELKIND_SEQUENCE)
+ " THEN 's' ELSE 'r' END::\"char\"",
+ dopt->binary_upgrade);
+
+ buildACLQueries(attacl_subquery, attracl_subquery, attinitacl_subquery,
+ attinitracl_subquery, "at.attacl", "c.relowner", "'c'",
+ dopt->binary_upgrade);
+
+ appendPQExpBuffer(query,
+ "SELECT c.tableoid, c.oid, c.relname, "
+ "%s AS relacl, %s as rrelacl, "
+ "%s AS initrelacl, %s as initrrelacl, "
+ "c.relkind, c.relnamespace, "
+ "(%s c.relowner) AS rolname, "
+ "c.relchecks, c.relhastriggers, "
+ "c.relhasindex, c.relhasrules, c.relhasoids, "
+ "c.relrowsecurity, c.relforcerowsecurity, "
+ "c.relfrozenxid, c.relminmxid, tc.oid AS toid, "
+ "tc.relfrozenxid AS tfrozenxid, "
+ "tc.relminmxid AS tminmxid, "
+ "c.relpersistence, c.relispopulated, "
+ "c.relreplident, c.relpages, "
+ "(SELECT count(*) FROM pg_period WHERE perrelid = c.oid) AS nperiod, "
+ "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, "
+ "d.refobjid AS owning_tab, "
+ "d.refobjsubid AS owning_col, "
+ "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
+ "array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded') AS reloptions, "
+ "CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text "
+ "WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, "
+ "tc.reloptions AS toast_reloptions, "
+ "c.relkind = '%c' AND EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = c.oid AND objsubid = 0 AND refclassid = 'pg_class'::regclass AND deptype = 'i') AS is_identity_sequence, "
+ "EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON "
+ "(c.oid = pip.objoid "
+ "AND pip.classoid = 'pg_class'::regclass "
+ "AND pip.objsubid = at.attnum)"
+ "WHERE at.attrelid = c.oid AND ("
+ "%s IS NOT NULL "
+ "OR %s IS NOT NULL "
+ "OR %s IS NOT NULL "
+ "OR %s IS NOT NULL"
+ "))"
+ "AS changed_acl, "
+ "pg_get_partkeydef(c.oid) AS partkeydef, "
+ "c.relispartition AS ispartition, "
+ "pg_get_expr(c.relpartbound, c.oid) AS partbound "
+ "FROM pg_class c "
+ "LEFT JOIN pg_depend d ON "
+ "(c.relkind = '%c' AND "
+ "d.classid = c.tableoid AND d.objid = c.oid AND "
+ "d.objsubid = 0 AND "
+ "d.refclassid = c.tableoid AND d.deptype IN ('a', 'i')) "
+ "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) "
+ "LEFT JOIN pg_init_privs pip ON "
+ "(c.oid = pip.objoid "
+ "AND pip.classoid = 'pg_class'::regclass "
+ "AND pip.objsubid = 0) "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
+ "ORDER BY c.oid",
+ acl_subquery->data,
+ racl_subquery->data,
+ initacl_subquery->data,
+ initracl_subquery->data,
+ username_subquery,
+ RELKIND_SEQUENCE,
+ attacl_subquery->data,
+ attracl_subquery->data,
+ attinitacl_subquery->data,
+ attinitracl_subquery->data,
+ RELKIND_SEQUENCE,
+ RELKIND_RELATION, RELKIND_SEQUENCE,
+ RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
+ RELKIND_PARTITIONED_TABLE);
+
+ destroyPQExpBuffer(acl_subquery);
+ destroyPQExpBuffer(racl_subquery);
+ destroyPQExpBuffer(initacl_subquery);
+ destroyPQExpBuffer(initracl_subquery);
+
+ destroyPQExpBuffer(attacl_subquery);
+ destroyPQExpBuffer(attracl_subquery);
+ destroyPQExpBuffer(attinitacl_subquery);
+ destroyPQExpBuffer(attinitracl_subquery);
+ }
+ else
if (fout->remoteVersion >= 90600)
{
char *partkeydef = "NULL";
@@ -6420,6 +6529,7 @@ getTables(Archive *fout, int *numTables)
i_relkind = PQfnumber(res, "relkind");
i_rolname = PQfnumber(res, "rolname");
i_relchecks = PQfnumber(res, "relchecks");
+ i_nperiod = PQfnumber(res, "nperiod");
i_relhastriggers = PQfnumber(res, "relhastriggers");
i_relhasindex = PQfnumber(res, "relhasindex");
i_relhasrules = PQfnumber(res, "relhasrules");
@@ -6499,6 +6609,7 @@ getTables(Archive *fout, int *numTables)
else
tblinfo[i].reloftype = pg_strdup(PQgetvalue(res, i, i_reloftype));
tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks));
+ tblinfo[i].nperiod = atoi(PQgetvalue(res, i, i_nperiod));
if (PQgetisnull(res, i, i_owning_tab))
{
tblinfo[i].owning_tab = InvalidOid;
@@ -8106,6 +8217,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
PGresult *res;
int ntups;
bool hasdefaults;
+ int ndumpablechecks; /* number of CHECK constraints that do
+ not belong to a period */
for (i = 0; i < numTables; i++)
{
@@ -8408,9 +8521,10 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
}
/*
- * Get info about table CHECK constraints
+ * Get info about table CHECK constraints that don't belong to a PERIOD
*/
- if (tbinfo->ncheck > 0)
+ ndumpablechecks = tbinfo->ncheck - tbinfo->nperiod;
+ if (ndumpablechecks > 0)
{
ConstraintInfo *constrs;
int numConstrs;
@@ -8421,7 +8535,25 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->dobj.name);
resetPQExpBuffer(q);
- if (fout->remoteVersion >= 90200)
+ if (fout->remoteVersion >= 0)
+ {
+ /*
+ * PERIODs were added in v12 and we don't dump CHECK
+ * constraints for them.
+ */
+ appendPQExpBuffer(q,
+ "SELECT tableoid, oid, conname, "
+ "pg_catalog.pg_get_constraintdef(oid) AS consrc, "
+ "conislocal, convalidated "
+ "FROM pg_catalog.pg_constraint "
+ "WHERE conrelid = '%u'::pg_catalog.oid "
+ " AND contype = 'c' "
+ " AND NOT EXISTS (SELECT FROM pg_period "
+ " WHERE (perrelid, perconstraint) = (conrelid, pg_constraint.oid)) "
+ "ORDER BY conname",
+ tbinfo->dobj.catId.oid);
+ }
+ else if (fout->remoteVersion >= 90200)
{
/*
* convalidated is new in 9.2 (actually, it is there in 9.1,
@@ -8463,12 +8595,12 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
numConstrs = PQntuples(res);
- if (numConstrs != tbinfo->ncheck)
+ if (numConstrs != ndumpablechecks)
{
write_msg(NULL, ngettext("expected %d check constraint on table \"%s\" but found %d\n",
"expected %d check constraints on table \"%s\" but found %d\n",
- tbinfo->ncheck),
- tbinfo->ncheck, tbinfo->dobj.name, numConstrs);
+ ndumpablechecks),
+ ndumpablechecks, tbinfo->dobj.name, numConstrs);
write_msg(NULL, "(The system catalogs might be corrupted.)\n");
exit_nicely(1);
}
@@ -8526,6 +8658,76 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
}
PQclear(res);
}
+
+ /*
+ * Get info about PERIOD definitions
+ */
+ if (tbinfo->nperiod > 0)
+ {
+ PeriodInfo *periods;
+ int numPeriods;
+
+ /* We shouldn't have any periods before v12 */
+ Assert(fout->remoteVersion >= 0); /* TODO */
+
+ if (g_verbose)
+ write_msg(NULL, "finding periods for table \"%s.%s\"\n",
+ tbinfo->dobj.namespace->dobj.name,
+ tbinfo->dobj.name);
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q,
+ "SELECT p.tableoid, p.oid, p.pername, "
+ " sa.attname AS perstart, ea.attname AS perend, "
+ " no.nspname AS opcnamespace, o.opcname, "
+ " c.conname AS conname "
+ "FROM pg_catalog.pg_period AS p "
+ "JOIN pg_catalog.pg_attribute AS sa ON (sa.attrelid, sa.attnum) = (p.perrelid, p.perstart) "
+ "JOIN pg_catalog.pg_attribute AS ea ON (ea.attrelid, ea.attnum) = (p.perrelid, p.perend) "
+ "JOIN pg_catalog.pg_opclass AS o ON o.oid = p.peropclass "
+ "JOIN pg_catalog.pg_namespace AS no ON no.oid = o.opcnamespace "
+ "JOIN pg_catalog.pg_constraint AS c ON c.oid = p.perconstraint "
+ "WHERE p.perrelid = '%u'::pg_catalog.oid "
+ "ORDER BY p.pername",
+ tbinfo->dobj.catId.oid);
+
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+
+ /*
+ * If we didn't get the number of rows we thought we were going to,
+ * then those JOINs didn't work.
+ */
+ numPeriods = PQntuples(res);
+ if (numPeriods != tbinfo->nperiod)
+ {
+ write_msg(NULL, ngettext("expected %d period on table \"%s\" but found %d\n",
+ "expected %d periods on table \"%s\" but found %d\n",
+ tbinfo->nperiod),
+ tbinfo->nperiod, tbinfo->dobj.name, numPeriods);
+ write_msg(NULL, "(The system catalogs might be corrupted.)\n");
+ exit_nicely(1);
+ }
+
+ periods = (PeriodInfo *) pg_malloc(numPeriods * sizeof(PeriodInfo));
+ tbinfo->periods = periods;
+
+ for (j = 0; j < numPeriods; j++)
+ {
+ periods[j].dobj.objType = DO_PERIOD;
+ periods[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
+ periods[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
+ AssignDumpId(&periods[j].dobj);
+ periods[j].dobj.name = pg_strdup(PQgetvalue(res, j, 2));
+ periods[j].dobj.namespace = tbinfo->dobj.namespace;
+ periods[j].pertable = tbinfo;
+ periods[j].perstart = pg_strdup(PQgetvalue(res, j, 3));
+ periods[j].perend = pg_strdup(PQgetvalue(res, j, 4));
+ periods[j].opcnamespace = pg_strdup(PQgetvalue(res, j, 5));
+ periods[j].opcname = pg_strdup(PQgetvalue(res, j, 6));
+ periods[j].conname = pg_strdup(PQgetvalue(res, j, 7));
+ }
+ PQclear(res);
+ }
}
destroyPQExpBuffer(q);
@@ -9736,6 +9938,8 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj)
case DO_FK_CONSTRAINT:
dumpConstraint(fout, (ConstraintInfo *) dobj);
break;
+ case DO_PERIOD:
+ break;
case DO_PROCLANG:
dumpProcLang(fout, (ProcLangInfo *) dobj);
break;
@@ -15553,10 +15757,38 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
}
}
+ /*
+ * Add non-inherited PERIOD definitions, if any.
+ */
+ for (j = 0; j < tbinfo->nperiod; j++)
+ {
+ PeriodInfo *period = &(tbinfo->periods[j]);
+
+ char *name = pg_strdup(fmtId(period->dobj.name));
+ char *start = pg_strdup(fmtId(period->perstart));
+ char *end = pg_strdup(fmtId(period->perend));
+ char *opcnamespace = pg_strdup(fmtId(period->opcnamespace));
+ char *opcname = pg_strdup(fmtId(period->opcname));
+ char *conname = pg_strdup(fmtId(period->conname));
+
+ if (actual_atts == 0)
+ appendPQExpBufferStr(q, " (\n ");
+ else
+ appendPQExpBufferStr(q, ",\n ");
+
+ appendPQExpBuffer(q, "PERIOD FOR %s (%s, %s) "
+ "WITH (operator_class = %s.%s, constraint_name = %s)",
+ name, start, end,
+ opcnamespace, opcname,
+ conname);
+
+ actual_atts++;
+ }
+
/*
* Add non-inherited CHECK constraints, if any.
*/
- for (j = 0; j < tbinfo->ncheck; j++)
+ for (j = 0; j < tbinfo->ncheck - tbinfo->nperiod; j++)
{
ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
@@ -15723,7 +15955,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
}
}
- for (k = 0; k < tbinfo->ncheck; k++)
+ for (k = 0; k < tbinfo->ncheck - tbinfo->nperiod; k++)
{
ConstraintInfo *constr = &(tbinfo->checkexprs[k]);
@@ -16001,7 +16233,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
dumpTableSecLabel(fout, tbinfo, reltypename);
/* Dump comments on inlined table constraints */
- for (j = 0; j < tbinfo->ncheck; j++)
+ for (j = 0; j < tbinfo->ncheck - tbinfo->nperiod; j++)
{
ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
@@ -17844,6 +18076,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs,
case DO_TRIGGER:
case DO_EVENT_TRIGGER:
case DO_DEFAULT_ACL:
+ case DO_PERIOD:
case DO_POLICY:
case DO_PUBLICATION:
case DO_PUBLICATION_REL:
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index e96c662b1e..631e94a997 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -62,6 +62,7 @@ typedef enum
DO_TRIGGER,
DO_CONSTRAINT,
DO_FK_CONSTRAINT, /* see note for ConstraintInfo */
+ DO_PERIOD,
DO_PROCLANG,
DO_CAST,
DO_TABLE_DATA,
@@ -279,12 +280,14 @@ typedef struct _tableInfo
bool rowsec; /* is row security enabled? */
bool forcerowsec; /* is row security forced? */
bool hasoids; /* does it have OIDs? */
+ bool hasperiods; /* does it have any periods? */
uint32 frozenxid; /* table's relfrozenxid */
uint32 minmxid; /* table's relminmxid */
Oid toast_oid; /* toast table's OID, or 0 if none */
uint32 toast_frozenxid; /* toast table's relfrozenxid, if any */
uint32 toast_minmxid; /* toast table's relminmxid */
int ncheck; /* # of CHECK expressions */
+ int nperiod; /* # of PERIOD definitions */
char *reloftype; /* underlying type for typed table */
/* these two are set only if table is a sequence owned by a column: */
Oid owning_tab; /* OID of table owning sequence */
@@ -320,6 +323,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ struct _periodInfo *periods; /* PERIOD definitions */
char *partkeydef; /* partition key definition */
char *partbound; /* partition bound definition */
bool needs_override; /* has GENERATED ALWAYS AS IDENTITY */
@@ -448,6 +452,17 @@ typedef struct _constraintInfo
bool separate; /* true if must dump as separate item */
} ConstraintInfo;
+typedef struct _periodInfo
+{
+ DumpableObject dobj;
+ TableInfo *pertable;
+ char *perstart; /* the name of the start column */
+ char *perend; /* the name of the end column */
+ char *opcnamespace; /* the name of the operator class schema */
+ char *opcname; /* the name of the operator class */
+ char *conname; /* the name of the CHECK constraint */
+} PeriodInfo;
+
typedef struct _procLangInfo
{
DumpableObject dobj;
@@ -698,6 +713,7 @@ extern InhInfo *getInherits(Archive *fout, int *numInherits);
extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables);
extern void getExtendedStatistics(Archive *fout);
extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables);
+/*extern void getPeriods(Archive *fout, TableInfo tblinfo[], int numTables);*/
extern RuleInfo *getRules(Archive *fout, int *numRules);
extern void getTriggers(Archive *fout, TableInfo tblinfo[], int numTables);
extern ProcLangInfo *getProcLangs(Archive *fout, int *numProcLangs);
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index d2b0949d6b..57aab962ef 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -63,6 +63,7 @@ static const int dbObjectTypePriority[] =
32, /* DO_TRIGGER */
27, /* DO_CONSTRAINT */
33, /* DO_FK_CONSTRAINT */
+ 30, /* DO_PERIOD */
2, /* DO_PROCLANG */
10, /* DO_CAST */
23, /* DO_TABLE_DATA */
@@ -1361,6 +1362,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize)
"FK CONSTRAINT %s (ID %d OID %u)",
obj->name, obj->dumpId, obj->catId.oid);
return;
+ case DO_PERIOD:
+ snprintf(buf, bufsize,
+ "PERIOD %s (ID %d OID %u)",
+ obj->name, obj->dumpId, obj->catId.oid);
+ return;
case DO_PROCLANG:
snprintf(buf, bufsize,
"PROCEDURAL LANGUAGE %s (ID %d OID %u)",
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e5b3c1ebf9..d694448130 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2172,6 +2172,40 @@ describeOneTableDetails(const char *schemaname,
PGresult *result = NULL;
int tuples = 0;
+ /* print periods */
+ if (pset.sversion >= 0) /* TODO */
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT quote_ident(p.pername), quote_ident(s.attname) AS startatt, quote_ident(e.attname) AS endatt\n"
+ "FROM pg_period AS p\n"
+ "JOIN pg_attribute AS s ON (s.attrelid, s.attnum) = (p.perrelid, p.perstart)\n"
+ "JOIN pg_attribute AS e ON (e.attrelid, e.attnum) = (p.perrelid, p.perend)\n"
+ "WHERE p.perrelid = '%s'\n"
+ "ORDER BY 1;",
+ oid);
+ result = PSQLexec(buf.data);
+ if (!result)
+ goto error_return;
+ else
+ tuples = PQntuples(result);
+
+ if (tuples > 0)
+ {
+ printTableAddFooter(&cont, _("Periods:"));
+ for (i = 0; i < tuples; i++)
+ {
+ /* untranslated constraint name and def */
+ printfPQExpBuffer(&buf, " %s (%s, %s)",
+ PQgetvalue(result, i, 0),
+ PQgetvalue(result, i, 1),
+ PQgetvalue(result, i, 2));
+
+ printTableAddFooter(&cont, buf.data);
+ }
+ }
+ PQclear(result);
+ }
+
/* print indexes */
if (tableinfo.hasindex)
{
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 46c271a46c..8031e9015b 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -149,6 +149,7 @@ typedef enum ObjectClass
OCLASS_CAST, /* pg_cast */
OCLASS_COLLATION, /* pg_collation */
OCLASS_CONSTRAINT, /* pg_constraint */
+ OCLASS_PERIOD, /* pg_period */
OCLASS_CONVERSION, /* pg_conversion */
OCLASS_DEFAULT, /* pg_attrdef */
OCLASS_LANGUAGE, /* pg_language */
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 59fc052494..9e872a9b17 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -106,6 +106,10 @@ extern List *AddRelationNewConstraints(Relation rel,
extern void RelationClearMissing(Relation rel);
+extern Oid StorePeriod(Relation rel, const char *period,
+ AttrNumber startnum, AttrNumber endnum,
+ Oid opclass, Oid conoid);
+
extern Oid StoreAttrDefault(Relation rel, AttrNumber attnum,
Node *expr, bool is_internal,
bool add_column_mode);
diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h
index 24915824ca..7a2703901c 100644
--- a/src/include/catalog/indexing.h
+++ b/src/include/catalog/indexing.h
@@ -339,6 +339,11 @@ DECLARE_UNIQUE_INDEX(pg_replication_origin_roname_index, 6002, on pg_replication
DECLARE_UNIQUE_INDEX(pg_partitioned_table_partrelid_index, 3351, on pg_partitioned_table using btree(partrelid oid_ops));
#define PartitionedRelidIndexId 3351
+DECLARE_UNIQUE_INDEX(pg_period_oid_index, 4101, on pg_period using btree(oid oid_ops));
+#define PeriodObjectIndexId 4101
+DECLARE_UNIQUE_INDEX(pg_period_perrelid_pername_index, 3998, on pg_period using btree(perrelid oid_ops, pername name_ops));
+#define PeriodRelidNameIndexId 3998
+
DECLARE_UNIQUE_INDEX(pg_publication_oid_index, 6110, on pg_publication using btree(oid oid_ops));
#define PublicationObjectIndexId 6110
diff --git a/src/include/catalog/pg_period.h b/src/include/catalog/pg_period.h
new file mode 100644
index 0000000000..c5c84252ce
--- /dev/null
+++ b/src/include/catalog/pg_period.h
@@ -0,0 +1,51 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_period.h
+ * definition of the "period" system catalog (pg_period)
+ *
+ *
+ * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
+ *
+ * src/include/catalog/pg_period.h
+ *
+ * NOTES
+ * The Catalog.pm module reads this file and derives schema
+ * information.
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_PERIOD_H
+#define PG_PERIOD_H
+
+#include "catalog/genbki.h"
+#include "catalog/pg_period_d.h"
+
+/* ----------------
+ * pg_period definition. cpp turns this into
+ * typedef struct FormData_pg_period
+ * ----------------
+ */
+CATALOG(pg_period,3996,PeriodRelationId)
+{
+ NameData pername; /* name of period */
+ Oid perrelid; /* OID of relation containing this period */
+ int16 perstart; /* column for start value */
+ int16 perend; /* column for end value */
+ Oid peropclass; /* OID of the operator class used */
+ Oid perconstraint; /* OID of (start < end) constraint */
+ bool perislocal; /* is the period local or inherited? */
+ int32 perinhcount; /* number of parents having this period */
+} FormData_pg_period;
+
+/* ----------------
+ * Form_pg_period corresponds to a pointer to a tuple with
+ * the format of pg_period relation.
+ * ----------------
+ */
+typedef FormData_pg_period *Form_pg_period;
+
+extern void RemovePeriodById(Oid periodId);
+
+extern Oid get_relation_period_oid(Oid relid, const char *pername, bool missing_ok);
+
+#endif /* PG_PERIOD_H */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index adb159a6da..27d1c2a379 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -447,6 +447,7 @@ typedef enum NodeTag
T_ColumnDef,
T_IndexElem,
T_Constraint,
+ T_Period,
T_DefElem,
T_RangeTblEntry,
T_RangeTblFunction,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 6390f7e8c1..60d12268b9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1660,6 +1660,7 @@ typedef enum ObjectType
OBJECT_OPCLASS,
OBJECT_OPERATOR,
OBJECT_OPFAMILY,
+ OBJECT_PERIOD,
OBJECT_POLICY,
OBJECT_PROCEDURE,
OBJECT_PUBLICATION,
@@ -1749,6 +1750,8 @@ typedef enum AlterTableType
AT_AddIndexConstraint, /* add constraint using existing index */
AT_DropConstraint, /* drop constraint */
AT_DropConstraintRecurse, /* internal to commands/tablecmds.c */
+ AT_AddPeriod, /* ADD PERIOD */
+ AT_DropPeriod, /* DROP PERIOD */
AT_ReAddComment, /* internal to commands/tablecmds.c */
AT_AlterColumnType, /* alter column type */
AT_AlterColumnGenericOptions, /* alter column OPTIONS (...) */
@@ -2000,9 +2003,9 @@ typedef struct VariableShowStmt
/* ----------------------
* Create Table Statement
*
- * NOTE: in the raw gram.y output, ColumnDef and Constraint nodes are
- * intermixed in tableElts, and constraints is NIL. After parse analysis,
- * tableElts contains just ColumnDefs, and constraints contains just
+ * NOTE: in the raw gram.y output, ColumnDef, Period, and Constraint nodes are
+ * intermixed in tableElts; periods and constraints are NIL. After parse analysis,
+ * tableElts contains just ColumnDefs, periods contains just Period nodes, and constraints contains just
* Constraint nodes (in fact, only CONSTR_CHECK nodes, in the present
* implementation).
* ----------------------
@@ -2018,6 +2021,7 @@ typedef struct CreateStmt
PartitionBoundSpec *partbound; /* FOR VALUES clause */
PartitionSpec *partspec; /* PARTITION BY clause */
TypeName *ofTypename; /* OF typename */
+ List *periods; /* periods (list of Period nodes) */
List *constraints; /* constraints (list of Constraint nodes) */
List *options; /* options from WITH clause */
OnCommitAction oncommit; /* what do we do at COMMIT? */
@@ -2025,6 +2029,22 @@ typedef struct CreateStmt
bool if_not_exists; /* just do nothing if it already exists? */
} CreateStmt;
+
+/* ----------
+ * Definitions for periods in CreateStmt
+ * ----------
+ */
+
+typedef struct Period
+{
+ NodeTag type;
+ char *periodname; /* period name */
+ char *startcolname; /* name of start column */
+ char *endcolname; /* name of end column */
+ List *options; /* options from WITH clause */
+ int location; /* token location, or -1 if unknown */
+} Period;
+
/* ----------
* Definitions for constraints in CreateStmt
*
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 23db40147b..59ea903cbc 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -300,6 +300,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD)
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD)
+PG_KEYWORD("period", PERIOD, RESERVED_KEYWORD)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD)
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index 4f333586ee..f28dfb0e4e 100644
--- a/src/include/utils/syscache.h
+++ b/src/include/utils/syscache.h
@@ -73,6 +73,8 @@ enum SysCacheIdentifier
OPFAMILYAMNAMENSP,
OPFAMILYOID,
PARTRELID,
+ PERIODNAME,
+ PERIODOID,
PROCNAMEARGSNSP,
PROCOID,
PUBLICATIONNAME,
diff --git a/src/test/regress/expected/periods.out b/src/test/regress/expected/periods.out
new file mode 100644
index 0000000000..787b68eff5
--- /dev/null
+++ b/src/test/regress/expected/periods.out
@@ -0,0 +1,55 @@
+/* System periods are not implemented */
+create table pt (id integer, ds date, de date, period for system_time (ds, de));
+ERROR: PERIOD FOR SYSTEM_TIME is not supported
+LINE 2: create table pt (id integer, ds date, de date, period for sy...
+ ^
+/* Periods must specify actual columns */
+create table pt (id integer, ds date, de date, period for p (bogus, de));
+ERROR: column "bogus" of relation "pt" does not exist
+create table pt (id integer, ds date, de date, period for p (ds, bogus));
+ERROR: column "bogus" of relation "pt" does not exist
+/* Data types must match exactly */
+create table pt (id integer, ds date, de timestamp, period for p (ds, de));
+ERROR: start and end columns of period must be of same type
+create table pt (id integer, ds text collate "C", de text collate "POSIX", period for p (ds, de));
+ERROR: start and end columns of period must have same collation
+/* Periods must have a default BTree operator class */
+create table pt (id integer, ds xml, de xml, period for p (ds, de));
+ERROR: data type xml has no default operator class for access method "btree"
+HINT: You must specify an operator class for the index or define a default operator class for the data type.
+/* Period and column names are in the same namespace */
+create table pt (id integer, ds date, de date, period for ctid (ds, de));
+ERROR: period name "ctid" conflicts with a system column name
+create table pt (id integer, ds date, de date, period for id (ds, de));
+ERROR: period name "id" conflicts with a column name
+/* Now make one that works */
+create table pt (id integer, ds date, de date, period for p (ds, de));
+/*
+ * CREATE TABLE currently adds an ALTER TABLE to add the periods, but let's do
+ * some explicit testing anyway
+ */
+alter table pt drop period for p;
+alter table pt add period for system_time (ds, de);
+ERROR: PERIOD FOR SYSTEM_TIME is not supported
+alter table pt add period for p (ds, de);
+/* Can't drop its columns */
+alter table pt drop column ds;
+ERROR: cannot drop column ds of table pt because other objects depend on it
+DETAIL: period p on table pt depends on column ds of table pt
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+alter table pt drop column de;
+ERROR: cannot drop column de of table pt because other objects depend on it
+DETAIL: period p on table pt depends on column de of table pt
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+/* Can't change the data types */
+alter table pt alter column ds type timestamp;
+ERROR: cannot alter type of a column used by a period
+DETAIL: period p on table pt depends on column "ds"
+alter table pt alter column ds type timestamp;
+ERROR: cannot alter type of a column used by a period
+DETAIL: period p on table pt depends on column "ds"
+/* column/period namespace conflicts */
+alter table pt add column p integer;
+ERROR: column name "p" conflicts with a period name
+alter table pt rename column id to p;
+ERROR: column name "p" conflicts with a period name
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index 0aa5357917..329864e7f1 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -136,6 +136,7 @@ pg_opclass|t
pg_operator|t
pg_opfamily|t
pg_partitioned_table|t
+pg_period|t
pg_pltemplate|t
pg_policy|t
pg_proc|t
@@ -165,6 +166,7 @@ pg_type|t
pg_user_mapping|t
point_tbl|t
polygon_tbl|t
+pt|f
quad_box_tbl|t
quad_point_tbl|t
quad_poly_tbl|t
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 16f979c8d9..95165c5f41 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -60,7 +60,7 @@ test: create_index create_view index_including
# ----------
# Another group of parallel tests
# ----------
-test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes create_am hash_func
+test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table periods vacuum drop_if_exists updatable_views rolenames roleattributes create_am hash_func
# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 42632be675..80c9d857ba 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -75,6 +75,7 @@ test: triggers
test: inherit
test: create_table_like
test: typed_table
+test: periods
test: vacuum
test: drop_if_exists
test: updatable_views
diff --git a/src/test/regress/sql/periods.sql b/src/test/regress/sql/periods.sql
new file mode 100644
index 0000000000..4b40890b34
--- /dev/null
+++ b/src/test/regress/sql/periods.sql
@@ -0,0 +1,40 @@
+/* System periods are not implemented */
+create table pt (id integer, ds date, de date, period for system_time (ds, de));
+
+/* Periods must specify actual columns */
+create table pt (id integer, ds date, de date, period for p (bogus, de));
+create table pt (id integer, ds date, de date, period for p (ds, bogus));
+
+/* Data types must match exactly */
+create table pt (id integer, ds date, de timestamp, period for p (ds, de));
+create table pt (id integer, ds text collate "C", de text collate "POSIX", period for p (ds, de));
+
+/* Periods must have a default BTree operator class */
+create table pt (id integer, ds xml, de xml, period for p (ds, de));
+
+/* Period and column names are in the same namespace */
+create table pt (id integer, ds date, de date, period for ctid (ds, de));
+create table pt (id integer, ds date, de date, period for id (ds, de));
+
+/* Now make one that works */
+create table pt (id integer, ds date, de date, period for p (ds, de));
+
+/*
+ * CREATE TABLE currently adds an ALTER TABLE to add the periods, but let's do
+ * some explicit testing anyway
+ */
+alter table pt drop period for p;
+alter table pt add period for system_time (ds, de);
+alter table pt add period for p (ds, de);
+
+/* Can't drop its columns */
+alter table pt drop column ds;
+alter table pt drop column de;
+
+/* Can't change the data types */
+alter table pt alter column ds type timestamp;
+alter table pt alter column ds type timestamp;
+
+/* column/period namespace conflicts */
+alter table pt add column p integer;
+alter table pt rename column id to p;
