diff options
47 files changed, 149 insertions, 4475 deletions
diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile index 7ac2df26c10..073dcc745c4 100644 --- a/contrib/btree_gist/Makefile +++ b/contrib/btree_gist/Makefile @@ -33,14 +33,12 @@ EXTENSION = btree_gist DATA = btree_gist--1.0--1.1.sql \ btree_gist--1.1--1.2.sql btree_gist--1.2.sql btree_gist--1.2--1.3.sql \ btree_gist--1.3--1.4.sql btree_gist--1.4--1.5.sql \ - btree_gist--1.5--1.6.sql btree_gist--1.6--1.7.sql \ - btree_gist--1.7--1.8.sql + btree_gist--1.5--1.6.sql btree_gist--1.6--1.7.sql PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes" REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \ time timetz date interval macaddr macaddr8 inet cidr text varchar char \ - bytea bit varbit numeric uuid not_equal enum bool partitions \ - stratnum without_overlaps + bytea bit varbit numeric uuid not_equal enum bool partitions SHLIB_LINK += $(filter -lm, $(LIBS)) diff --git a/contrib/btree_gist/btree_gist--1.7--1.8.sql b/contrib/btree_gist/btree_gist--1.7--1.8.sql deleted file mode 100644 index 307bfe574b0..00000000000 --- a/contrib/btree_gist/btree_gist--1.7--1.8.sql +++ /dev/null @@ -1,87 +0,0 @@ -/* contrib/btree_gist/btree_gist--1.7--1.8.sql */ - --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use "ALTER EXTENSION btree_gist UPDATE TO '1.8'" to load this file. \quit - -CREATE FUNCTION gist_stratnum_btree(smallint) -RETURNS smallint -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE PARALLEL SAFE STRICT; - -ALTER OPERATOR FAMILY gist_oid_ops USING gist ADD - FUNCTION 12 (oid, oid) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_int2_ops USING gist ADD - FUNCTION 12 (int2, int2) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD - FUNCTION 12 (int4, int4) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_int8_ops USING gist ADD - FUNCTION 12 (int8, int8) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_float4_ops USING gist ADD - FUNCTION 12 (float4, float4) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_float8_ops USING gist ADD - FUNCTION 12 (float8, float8) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_timestamp_ops USING gist ADD - FUNCTION 12 (timestamp, timestamp) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_timestamptz_ops USING gist ADD - FUNCTION 12 (timestamptz, timestamptz) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_time_ops USING gist ADD - FUNCTION 12 (time, time) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_date_ops USING gist ADD - FUNCTION 12 (date, date) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_interval_ops USING gist ADD - FUNCTION 12 (interval, interval) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_cash_ops USING gist ADD - FUNCTION 12 (money, money) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_macaddr_ops USING gist ADD - FUNCTION 12 (macaddr, macaddr) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_text_ops USING gist ADD - FUNCTION 12 (text, text) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_bpchar_ops USING gist ADD - FUNCTION 12 (bpchar, bpchar) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_bytea_ops USING gist ADD - FUNCTION 12 (bytea, bytea) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_numeric_ops USING gist ADD - FUNCTION 12 (numeric, numeric) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_bit_ops USING gist ADD - FUNCTION 12 (bit, bit) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_vbit_ops USING gist ADD - FUNCTION 12 (varbit, varbit) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_inet_ops USING gist ADD - FUNCTION 12 (inet, inet) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_cidr_ops USING gist ADD - FUNCTION 12 (cidr, cidr) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_timetz_ops USING gist ADD - FUNCTION 12 (timetz, timetz) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_uuid_ops USING gist ADD - FUNCTION 12 (uuid, uuid) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_macaddr8_ops USING gist ADD - FUNCTION 12 (macaddr8, macaddr8) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_enum_ops USING gist ADD - FUNCTION 12 (anyenum, anyenum) gist_stratnum_btree (int2) ; - -ALTER OPERATOR FAMILY gist_bool_ops USING gist ADD - FUNCTION 12 (bool, bool) gist_stratnum_btree (int2) ; diff --git a/contrib/btree_gist/btree_gist.c b/contrib/btree_gist/btree_gist.c index 5fd4cce27d0..92520aedae1 100644 --- a/contrib/btree_gist/btree_gist.c +++ b/contrib/btree_gist/btree_gist.c @@ -3,7 +3,6 @@ */ #include "postgres.h" -#include "access/stratnum.h" #include "utils/builtins.h" PG_MODULE_MAGIC; @@ -11,7 +10,6 @@ PG_MODULE_MAGIC; PG_FUNCTION_INFO_V1(gbt_decompress); PG_FUNCTION_INFO_V1(gbtreekey_in); PG_FUNCTION_INFO_V1(gbtreekey_out); -PG_FUNCTION_INFO_V1(gist_stratnum_btree); /************************************************** * In/Out for keys @@ -53,28 +51,3 @@ gbt_decompress(PG_FUNCTION_ARGS) { PG_RETURN_POINTER(PG_GETARG_POINTER(0)); } - -/* - * Returns the btree number for supported operators, otherwise invalid. - */ -Datum -gist_stratnum_btree(PG_FUNCTION_ARGS) -{ - StrategyNumber strat = PG_GETARG_UINT16(0); - - switch (strat) - { - case RTEqualStrategyNumber: - PG_RETURN_UINT16(BTEqualStrategyNumber); - case RTLessStrategyNumber: - PG_RETURN_UINT16(BTLessStrategyNumber); - case RTLessEqualStrategyNumber: - PG_RETURN_UINT16(BTLessEqualStrategyNumber); - case RTGreaterStrategyNumber: - PG_RETURN_UINT16(BTGreaterStrategyNumber); - case RTGreaterEqualStrategyNumber: - PG_RETURN_UINT16(BTGreaterEqualStrategyNumber); - default: - PG_RETURN_UINT16(InvalidStrategy); - } -} diff --git a/contrib/btree_gist/btree_gist.control b/contrib/btree_gist/btree_gist.control index abf66538f32..fa9171a80a2 100644 --- a/contrib/btree_gist/btree_gist.control +++ b/contrib/btree_gist/btree_gist.control @@ -1,6 +1,6 @@ # btree_gist extension comment = 'support for indexing common datatypes in GiST' -default_version = '1.8' +default_version = '1.7' module_pathname = '$libdir/btree_gist' relocatable = true trusted = true diff --git a/contrib/btree_gist/expected/stratnum.out b/contrib/btree_gist/expected/stratnum.out deleted file mode 100644 index 9d80c6590d9..00000000000 --- a/contrib/btree_gist/expected/stratnum.out +++ /dev/null @@ -1,13 +0,0 @@ --- test stratnum support func -SELECT gist_stratnum_btree(3::smallint); - gist_stratnum_btree ---------------------- - 0 -(1 row) - -SELECT gist_stratnum_btree(18::smallint); - gist_stratnum_btree ---------------------- - 3 -(1 row) - diff --git a/contrib/btree_gist/expected/without_overlaps.out b/contrib/btree_gist/expected/without_overlaps.out deleted file mode 100644 index 18856900ded..00000000000 --- a/contrib/btree_gist/expected/without_overlaps.out +++ /dev/null @@ -1,92 +0,0 @@ --- Core must test WITHOUT OVERLAPS --- with an int4range + daterange, --- so here we do some simple tests --- to make sure int + daterange works too, --- since that is the expected use-case. -CREATE TABLE temporal_rng ( - id integer, - valid_at daterange, - CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_rng - Table "public.temporal_rng" - Column | Type | Collation | Nullable | Default -----------+-----------+-----------+----------+--------- - id | integer | | not null | - valid_at | daterange | | not null | -Indexes: - "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) - -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; - pg_get_constraintdef ---------------------------------------------- - PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -(1 row) - -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; - pg_get_indexdef -------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) -(1 row) - -INSERT INTO temporal_rng VALUES - (1, '[2000-01-01,2001-01-01)'); --- same key, doesn't overlap: -INSERT INTO temporal_rng VALUES - (1, '[2001-01-01,2002-01-01)'); --- overlaps but different key: -INSERT INTO temporal_rng VALUES - (2, '[2000-01-01,2001-01-01)'); --- should fail: -INSERT INTO temporal_rng VALUES - (1, '[2000-06-01,2001-01-01)'); -ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk" -DETAIL: Key (id, valid_at)=(1, [06-01-2000,01-01-2001)) conflicts with existing key (id, valid_at)=(1, [01-01-2000,01-01-2001)). --- Foreign key -CREATE TABLE temporal_fk_rng2rng ( - id integer, - valid_at daterange, - parent_id integer, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id, PERIOD valid_at) -); -\d temporal_fk_rng2rng - Table "public.temporal_fk_rng2rng" - Column | Type | Collation | Nullable | Default ------------+-----------+-----------+----------+--------- - id | integer | | not null | - valid_at | daterange | | not null | - parent_id | integer | | | -Indexes: - "temporal_fk_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -Foreign-key constraints: - "temporal_fk_rng2rng_fk" FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) - -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; - pg_get_constraintdef ---------------------------------------------------------------------------------------- - FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) -(1 row) - --- okay -INSERT INTO temporal_fk_rng2rng VALUES - (1, '[2000-01-01,2001-01-01)', 1); --- okay spanning two parent records: -INSERT INTO temporal_fk_rng2rng VALUES - (2, '[2000-01-01,2002-01-01)', 1); --- key is missing -INSERT INTO temporal_fk_rng2rng VALUES - (3, '[2000-01-01,2001-01-01)', 3); -ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=(3, [01-01-2000,01-01-2001)) is not present in table "temporal_rng". --- key exist but is outside range -INSERT INTO temporal_fk_rng2rng VALUES - (4, '[2001-01-01,2002-01-01)', 2); -ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=(2, [01-01-2001,01-01-2002)) is not present in table "temporal_rng". --- key exist but is partly outside range -INSERT INTO temporal_fk_rng2rng VALUES - (5, '[2000-01-01,2002-01-01)', 2); -ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=(2, [01-01-2000,01-01-2002)) is not present in table "temporal_rng". diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build index 73b1bbf52a6..c88a6ac84ae 100644 --- a/contrib/btree_gist/meson.build +++ b/contrib/btree_gist/meson.build @@ -50,7 +50,6 @@ install_data( 'btree_gist--1.4--1.5.sql', 'btree_gist--1.5--1.6.sql', 'btree_gist--1.6--1.7.sql', - 'btree_gist--1.7--1.8.sql', kwargs: contrib_data_args, ) @@ -90,8 +89,6 @@ tests += { 'enum', 'bool', 'partitions', - 'stratnum', - 'without_overlaps', ], }, } diff --git a/contrib/btree_gist/sql/stratnum.sql b/contrib/btree_gist/sql/stratnum.sql deleted file mode 100644 index f58cdbe93da..00000000000 --- a/contrib/btree_gist/sql/stratnum.sql +++ /dev/null @@ -1,3 +0,0 @@ --- test stratnum support func -SELECT gist_stratnum_btree(3::smallint); -SELECT gist_stratnum_btree(18::smallint); diff --git a/contrib/btree_gist/sql/without_overlaps.sql b/contrib/btree_gist/sql/without_overlaps.sql deleted file mode 100644 index b1b581fcabc..00000000000 --- a/contrib/btree_gist/sql/without_overlaps.sql +++ /dev/null @@ -1,53 +0,0 @@ --- Core must test WITHOUT OVERLAPS --- with an int4range + daterange, --- so here we do some simple tests --- to make sure int + daterange works too, --- since that is the expected use-case. -CREATE TABLE temporal_rng ( - id integer, - valid_at daterange, - CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_rng -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; - -INSERT INTO temporal_rng VALUES - (1, '[2000-01-01,2001-01-01)'); --- same key, doesn't overlap: -INSERT INTO temporal_rng VALUES - (1, '[2001-01-01,2002-01-01)'); --- overlaps but different key: -INSERT INTO temporal_rng VALUES - (2, '[2000-01-01,2001-01-01)'); --- should fail: -INSERT INTO temporal_rng VALUES - (1, '[2000-06-01,2001-01-01)'); - --- Foreign key -CREATE TABLE temporal_fk_rng2rng ( - id integer, - valid_at daterange, - parent_id integer, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id, PERIOD valid_at) -); -\d temporal_fk_rng2rng -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; - --- okay -INSERT INTO temporal_fk_rng2rng VALUES - (1, '[2000-01-01,2001-01-01)', 1); --- okay spanning two parent records: -INSERT INTO temporal_fk_rng2rng VALUES - (2, '[2000-01-01,2002-01-01)', 1); --- key is missing -INSERT INTO temporal_fk_rng2rng VALUES - (3, '[2000-01-01,2001-01-01)', 3); --- key exist but is outside range -INSERT INTO temporal_fk_rng2rng VALUES - (4, '[2001-01-01,2002-01-01)', 2); --- key exist but is partly outside range -INSERT INTO temporal_fk_rng2rng VALUES - (5, '[2000-01-01,2002-01-01)', 2); diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 5a6f65025b1..15f6255d865 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2731,17 +2731,6 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <row> <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>conperiod</structfield> <type>bool</type> - </para> - <para> - This constraint is defined with <literal>WITHOUT OVERLAPS</literal> - (for primary keys and unique constraints) or <literal>PERIOD</literal> - (for foreign keys). - </para></entry> - </row> - - <row> - <entry role="catalog_table_entry"><para role="column_definition"> <structfield>conkey</structfield> <type>int2[]</type> (references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attnum</structfield>) </para> diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml index dcf9433fa78..39c7bf370d6 100644 --- a/doc/src/sgml/gist.sgml +++ b/doc/src/sgml/gist.sgml @@ -266,7 +266,7 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops); <para> There are five methods that an index operator class for - <acronym>GiST</acronym> must provide, and seven that are optional. + <acronym>GiST</acronym> must provide, and six that are optional. Correctness of the index is ensured by proper implementation of the <function>same</function>, <function>consistent</function> and <function>union</function> methods, while efficiency (size and speed) of the @@ -289,11 +289,6 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops); user-specified parameters. The optional eleventh method <function>sortsupport</function> is used to speed up building a <acronym>GiST</acronym> index. - The optional twelfth method <function>stratnum</function> is used to - translate well-known <literal>RT*StrategyNumber</literal>s (from - <filename>src/include/access/stratnum.h</filename>) into strategy numbers - used by the operator class. This lets the core code look up operators for - temporal constraint indexes. </para> <variablelist> @@ -1168,76 +1163,6 @@ my_sortsupport(PG_FUNCTION_ARGS) </para> </listitem> </varlistentry> - - <varlistentry> - <term><function>stratnum</function></term> - <listitem> - <para> - Given an <literal>RT*StrategyNumber</literal> value from - <filename>src/include/access/stratnum.h</filename>, returns a strategy - number used by this operator class for matching functionality. The - function should return <literal>InvalidStrategy</literal> if the - operator class has no matching strategy. - </para> - - <para> - This is used for temporal index constraints (i.e., <literal>PRIMARY - KEY</literal> and <literal>UNIQUE</literal>). If the operator class - provides this function and it returns results for - <literal>RTEqualStrategyNumber</literal>, it can be used in the - non-<literal>WITHOUT OVERLAPS</literal> part(s) of an index constraint. - If it returns results for <literal>RTOverlapStrategyNumber</literal>, - the operator class can be used in the <literal>WITHOUT - OVERLAPS</literal> part of an index constraint. - </para> - - <para> - The <acronym>SQL</acronym> declaration of the function must look like - this: - -<programlisting> -CREATE OR REPLACE FUNCTION my_stratnum(integer) -RETURNS integer -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT; -</programlisting> - </para> - - <para> - The matching code in the C module could then follow this skeleton: - -<programlisting> -PG_FUNCTION_INFO_V1(my_stratnum); - -Datum -my_stratnum(PG_FUNCTION_ARGS) -{ - StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(1); - StrategyNumber ret = InvalidStrategy; - - switch (strategy) - { - case RTEqualStrategyNumber: - ret = BTEqualStrategyNumber; - } - - PG_RETURN_UINT16(ret); -} -</programlisting> - </para> - - <para> - One translation function is provided by - <productname>PostgreSQL</productname>: - <literal>gist_stratnum_identity</literal> is for operator classes that - already use the <literal>RT*StrategyNumber</literal> constants. It - returns whatever is passed to it. The <literal>btree_gist</literal> - extension defines a second translation function, - <literal>gist_stratnum_btree</literal>, for operator classes that use - the <literal>BT*StrategyNumber</literal> constants. - </para> - </listitem> - </varlistentry> </variablelist> <para> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index a5bf80fb27e..f19306e7760 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -77,10 +77,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] { CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] | - UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> | - PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> | + UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> | + PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> | 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> ) ] | - FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] ) ] + FOREIGN KEY ( <replaceable class="parameter">column_name</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 ] @@ -964,7 +964,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <varlistentry id="sql-createtable-parms-unique"> <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ]</literal> (column constraint)</term> - <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] )</literal> + <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal> <optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term> <listitem> @@ -979,30 +979,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </para> <para> - If the <literal>WITHOUT OVERLAPS</literal> option is specified for the - last column, then that column is checked for overlaps instead of - equality. In that case, the other columns of the constraint will allow - duplicates so long as the duplicates don't overlap in the - <literal>WITHOUT OVERLAPS</literal> column. (This is sometimes called a - temporal key, if the column is a range of dates or timestamps, but - PostgreSQL allows ranges over any base type.) In effect, such a - constraint is enforced with an <literal>EXCLUDE</literal> constraint - rather than a <literal>UNIQUE</literal> constraint. So for example - <literal>UNIQUE (id, valid_at WITHOUT OVERLAPS)</literal> behaves like - <literal>EXCLUDE USING GIST (id WITH =, valid_at WITH - &&)</literal>. The <literal>WITHOUT OVERLAPS</literal> column - must have a range or multirange type. (Technically, any type is allowed - whose default GiST opclass includes an overlaps operator. See the - <literal>stratnum</literal> support function under <xref - linkend="gist-extensibility"/> for details.) The non-<literal>WITHOUT - OVERLAPS</literal> columns of the constraint can be any type that can be - compared for equality in a GiST index. By default, only range types are - supported, but you can use other types by adding the <xref - linkend="btree-gist"/> extension (which is the expected way to use this - feature). - </para> - - <para> For the purpose of a unique constraint, null values are not considered equal, unless <literal>NULLS NOT DISTINCT</literal> is specified. @@ -1023,11 +999,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </para> <para> - Adding a unique constraint will automatically create a unique B-tree - index on the column or group of columns used in the constraint. But if - the constraint includes a <literal>WITHOUT OVERLAPS</literal> clause, it - will use a GiST index. The created index has the same name as the unique - constraint. + Adding a unique constraint will automatically create a unique btree + index on the column or group of columns used in the constraint. The + created index has the same name as the unique constraint. </para> <para> @@ -1045,7 +1019,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <varlistentry id="sql-createtable-parms-primary-key"> <term><literal>PRIMARY KEY</literal> (column constraint)</term> - <term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] )</literal> + <term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal> <optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term> <listitem> <para> @@ -1078,11 +1052,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </para> <para> - As with a <literal>UNIQUE</literal> constraint, adding a - <literal>PRIMARY KEY</literal> constraint will automatically create a - unique B-tree index, or GiST if <literal>WITHOUT OVERLAPS</literal> was - specified, on the column or group of columns used in the constraint. - That index has the same name as the primary key constraint. + Adding a <literal>PRIMARY KEY</literal> constraint will automatically + create a unique btree index on the column or group of columns used in + the constraint. That index has the same name as the primary key + constraint. </para> <para> @@ -1151,8 +1124,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <varlistentry id="sql-createtable-parms-references"> <term><literal>REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ]</literal> (column constraint)</term> - <term><literal>FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] ) - REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] ) ] + <term><literal>FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) + REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ]</literal> @@ -1168,32 +1141,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM primary key of the <replaceable class="parameter">reftable</replaceable> is used. Otherwise, the <replaceable class="parameter">refcolumn</replaceable> list must refer to the columns of a non-deferrable unique or primary key - constraint or be the columns of a non-partial unique index. - </para> - - <para> - If the last column is marked with <literal>PERIOD</literal>, it is - treated in a special way. While the non-<literal>PERIOD</literal> - columns are compared for equality (and there must be at least one of - them), the <literal>PERIOD</literal> column is not. Instead, the - constraint is considered satisfied if the referenced table has matching - records (based on the non-<literal>PERIOD</literal> parts of the key) - whose combined <literal>PERIOD</literal> values completely cover the - referencing record's. In other words, the reference must have a - referent for its entire duration. This column must be a range or - multirange type. In addition, the referenced table must have a primary - key or unique constraint declared with <literal>WITHOUT - OVERLAPS</literal>. Finally, if the foreign key has a PERIOD - <replaceable class="parameter">column_name</replaceable> specification - the corresponding <replaceable class="parameter">refcolumn</replaceable>, - if present, must also be marked <literal>PERIOD</literal>. If the - <replaceable class="parameter">refcolumn</replaceable> clause is omitted, - and thus the reftable's primary key constraint chosen, the primary key - must have its final column marked <literal>WITHOUT OVERLAPS</literal>. - </para> - - <para> - The user + constraint or be the columns of a non-partial unique index. The user must have <literal>REFERENCES</literal> permission on the referenced table (either the whole table, or the specific referenced columns). The addition of a foreign key constraint requires a @@ -1267,10 +1215,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM values of the referencing column(s) to the new values of the referenced columns, respectively. </para> - - <para> - In a temporal foreign key, this option is not supported. - </para> </listitem> </varlistentry> @@ -1282,10 +1226,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM referencing columns, to null. A subset of columns can only be specified for <literal>ON DELETE</literal> actions. </para> - - <para> - In a temporal foreign key, this option is not supported. - </para> </listitem> </varlistentry> @@ -1299,10 +1239,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) </para> - - <para> - In a temporal foreign key, this option is not supported. - </para> </listitem> </varlistentry> </variablelist> diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml index 3a19dab15e0..22d8ad1aac4 100644 --- a/doc/src/sgml/xindex.sgml +++ b/doc/src/sgml/xindex.sgml @@ -508,7 +508,7 @@ </table> <para> - GiST indexes have twelve support functions, seven of which are optional, + GiST indexes have eleven support functions, six of which are optional, as shown in <xref linkend="xindex-gist-support-table"/>. (For more information see <xref linkend="gist"/>.) </para> @@ -590,12 +590,6 @@ (optional)</entry> <entry>11</entry> </row> - <row> - <entry><function>stratnum</function></entry> - <entry>translate well-known strategy numbers to ones - used by the operator class (optional)</entry> - <entry>12</entry> - </row> </tbody> </tgroup> </table> diff --git a/src/backend/access/gist/gistutil.c b/src/backend/access/gist/gistutil.c index d2d0b36d4ea..78e98d68b15 100644 --- a/src/backend/access/gist/gistutil.c +++ b/src/backend/access/gist/gistutil.c @@ -21,7 +21,6 @@ #include "common/pg_prng.h" #include "storage/indexfsm.h" #include "utils/float.h" -#include "utils/fmgrprotos.h" #include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/snapmgr.h" @@ -1056,45 +1055,3 @@ gistGetFakeLSN(Relation rel) return GetFakeLSNForUnloggedRel(); } } - -/* - * Returns the same number that was received. - * - * This is for GiST opclasses that use the RT*StrategyNumber constants. - */ -Datum -gist_stratnum_identity(PG_FUNCTION_ARGS) -{ - StrategyNumber strat = PG_GETARG_UINT16(0); - - PG_RETURN_UINT16(strat); -} - -/* - * Returns the opclass's private stratnum used for the given strategy. - * - * Calls the opclass's GIST_STRATNUM_PROC support function, if any, - * and returns the result. - * Returns InvalidStrategy if the function is not defined. - */ -StrategyNumber -GistTranslateStratnum(Oid opclass, StrategyNumber strat) -{ - Oid opfamily; - Oid opcintype; - Oid funcid; - Datum result; - - /* Look up the opclass family and input datatype. */ - if (!get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) - return InvalidStrategy; - - /* Check whether the function is provided. */ - funcid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_STRATNUM_PROC); - if (!OidIsValid(funcid)) - return InvalidStrategy; - - /* Ask the translation function */ - result = OidFunctionCall1Coll(funcid, InvalidOid, UInt16GetDatum(strat)); - return DatumGetUInt16(result); -} diff --git a/src/backend/access/gist/gistvalidate.c b/src/backend/access/gist/gistvalidate.c index 0901543a60a..36b5a85cf31 100644 --- a/src/backend/access/gist/gistvalidate.c +++ b/src/backend/access/gist/gistvalidate.c @@ -146,10 +146,6 @@ gistvalidate(Oid opclassoid) ok = check_amproc_signature(procform->amproc, VOIDOID, true, 1, 1, INTERNALOID); break; - case GIST_STRATNUM_PROC: - ok = check_amproc_signature(procform->amproc, INT2OID, true, - 1, 1, INT2OID); - break; default: ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), @@ -270,8 +266,7 @@ gistvalidate(Oid opclassoid) continue; /* got it */ if (i == GIST_DISTANCE_PROC || i == GIST_FETCH_PROC || i == GIST_COMPRESS_PROC || i == GIST_DECOMPRESS_PROC || - i == GIST_OPTIONS_PROC || i == GIST_SORTSUPPORT_PROC || - i == GIST_STRATNUM_PROC) + i == GIST_OPTIONS_PROC || i == GIST_SORTSUPPORT_PROC) continue; /* optional methods */ ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), @@ -343,7 +338,6 @@ gistadjustmembers(Oid opfamilyoid, case GIST_FETCH_PROC: case GIST_OPTIONS_PROC: case GIST_SORTSUPPORT_PROC: - case GIST_STRATNUM_PROC: /* Optional, so force it to be a soft family dependency */ op->ref_is_hard = false; op->ref_is_family = true; diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 08b8362d64d..a122bbffce7 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2155,7 +2155,6 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, is_local, /* conislocal */ inhcount, /* coninhcount */ is_no_inherit, /* connoinherit */ - false, /* conperiod */ is_internal); /* internally constructed? */ pfree(ccbin); diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 5a8568c55c9..55fdde4b242 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1873,7 +1873,6 @@ index_concurrently_set_dead(Oid heapId, Oid indexId) * INDEX_CONSTR_CREATE_UPDATE_INDEX: update the pg_index row * INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS: remove existing dependencies * of index on table's columns - * INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS: constraint uses WITHOUT OVERLAPS * allow_system_table_mods: allow table to be a system catalog * is_internal: index is constructed due to internal process */ @@ -1897,13 +1896,11 @@ index_constraint_create(Relation heapRelation, bool mark_as_primary; bool islocal; bool noinherit; - bool is_without_overlaps; int inhcount; deferrable = (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) != 0; initdeferred = (constr_flags & INDEX_CONSTR_CREATE_INIT_DEFERRED) != 0; mark_as_primary = (constr_flags & INDEX_CONSTR_CREATE_MARK_AS_PRIMARY) != 0; - is_without_overlaps = (constr_flags & INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS) != 0; /* constraint creation support doesn't work while bootstrapping */ Assert(!IsBootstrapProcessingMode()); @@ -1980,7 +1977,6 @@ index_constraint_create(Relation heapRelation, islocal, inhcount, noinherit, - is_without_overlaps, is_internal); /* diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index b10e458b449..3baf9231ed0 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -15,7 +15,6 @@ #include "postgres.h" #include "access/genam.h" -#include "access/gist.h" #include "access/htup_details.h" #include "access/sysattr.h" #include "access/table.h" @@ -76,7 +75,6 @@ CreateConstraintEntry(const char *constraintName, bool conIsLocal, int conInhCount, bool conNoInherit, - bool conPeriod, bool is_internal) { Relation conDesc; @@ -192,7 +190,6 @@ CreateConstraintEntry(const char *constraintName, values[Anum_pg_constraint_conislocal - 1] = BoolGetDatum(conIsLocal); values[Anum_pg_constraint_coninhcount - 1] = Int16GetDatum(conInhCount); values[Anum_pg_constraint_connoinherit - 1] = BoolGetDatum(conNoInherit); - values[Anum_pg_constraint_conperiod - 1] = BoolGetDatum(conPeriod); if (conkeyArray) values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(conkeyArray); @@ -1351,63 +1348,6 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks, } /* - * FindFKPeriodOpers - - * - * Looks up the operator oids used for the PERIOD part of a temporal foreign key. - * The opclass should be the opclass of that PERIOD element. - * Everything else is an output: containedbyoperoid is the ContainedBy operator for - * types matching the PERIOD element. - * aggedcontainedbyoperoid is also a ContainedBy operator, - * but one whose rhs is a multirange. - * That way foreign keys can compare fkattr <@ range_agg(pkattr). - */ -void -FindFKPeriodOpers(Oid opclass, - Oid *containedbyoperoid, - Oid *aggedcontainedbyoperoid) -{ - Oid opfamily = InvalidOid; - Oid opcintype = InvalidOid; - StrategyNumber strat; - - /* Make sure we have a range or multirange. */ - if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) - { - if (opcintype != ANYRANGEOID && opcintype != ANYMULTIRANGEOID) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("invalid type for PERIOD part of foreign key"), - errdetail("Only range and multirange are supported.")); - - } - else - elog(ERROR, "cache lookup failed for opclass %u", opclass); - - /* - * Look up the ContainedBy operator whose lhs and rhs are the opclass's - * type. We use this to optimize RI checks: if the new value includes all - * of the old value, then we can treat the attribute as if it didn't - * change, and skip the RI check. - */ - strat = RTContainedByStrategyNumber; - GetOperatorFromWellKnownStrategy(opclass, - InvalidOid, - containedbyoperoid, - &strat); - - /* - * Now look up the ContainedBy operator. Its left arg must be the type of - * the column (or rather of the opclass). Its right arg must match the - * return type of the support proc. - */ - strat = RTContainedByStrategyNumber; - GetOperatorFromWellKnownStrategy(opclass, - ANYMULTIRANGEOID, - aggedcontainedbyoperoid, - &strat); -} - -/* * Determine whether a relation can be proven functionally dependent on * a set of grouping columns. If so, return true and add the pg_constraint * OIDs of the constraints needed for the proof to the *constraintDeps list. diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index d9016ef487b..309389e20d2 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -16,7 +16,6 @@ #include "postgres.h" #include "access/amapi.h" -#include "access/gist.h" #include "access/heapam.h" #include "access/htup_details.h" #include "access/reloptions.h" @@ -88,7 +87,6 @@ static void ComputeIndexAttrs(IndexInfo *indexInfo, Oid accessMethodId, bool amcanorder, bool isconstraint, - bool iswithoutoverlaps, Oid ddl_userid, int ddl_sec_context, int *ddl_save_nestlevel); @@ -147,7 +145,6 @@ typedef struct ReindexErrorInfo * to index on. * 'exclusionOpNames': list of names of exclusion-constraint operators, * or NIL if not an exclusion constraint. - * 'isWithoutOverlaps': true iff this index has a WITHOUT OVERLAPS clause. * * This is tailored to the needs of ALTER TABLE ALTER TYPE, which recreates * any indexes that depended on a changing column from their pg_get_indexdef @@ -177,8 +174,7 @@ bool CheckIndexCompatible(Oid oldId, const char *accessMethodName, const List *attributeList, - const List *exclusionOpNames, - bool isWithoutOverlaps) + const List *exclusionOpNames) { bool isconstraint; Oid *typeIds; @@ -253,8 +249,8 @@ CheckIndexCompatible(Oid oldId, coloptions, attributeList, exclusionOpNames, relationId, accessMethodName, accessMethodId, - amcanorder, isconstraint, isWithoutOverlaps, InvalidOid, - 0, NULL); + amcanorder, isconstraint, InvalidOid, 0, NULL); + /* Get the soon-obsolete pg_index tuple. */ tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(oldId)); @@ -564,7 +560,6 @@ DefineIndex(Oid tableId, bool amcanorder; bool amissummarizing; amoptions_function amoptions; - bool exclusion; bool partitioned; bool safe_index; Datum reloptions; @@ -685,12 +680,6 @@ DefineIndex(Oid tableId, namespaceId = RelationGetNamespace(rel); - /* - * It has exclusion constraint behavior if it's an EXCLUDE constraint or a - * temporal PRIMARY KEY/UNIQUE constraint - */ - exclusion = stmt->excludeOpNames || stmt->iswithoutoverlaps; - /* Ensure that it makes sense to index this kind of relation */ switch (rel->rd_rel->relkind) { @@ -859,7 +848,7 @@ DefineIndex(Oid tableId, pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID, accessMethodId); - if (stmt->unique && !stmt->iswithoutoverlaps && !amRoutine->amcanunique) + if (stmt->unique && !amRoutine->amcanunique) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("access method \"%s\" does not support unique indexes", @@ -874,7 +863,7 @@ DefineIndex(Oid tableId, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("access method \"%s\" does not support multicolumn indexes", accessMethodName))); - if (exclusion && amRoutine->amgettuple == NULL) + if (stmt->excludeOpNames && amRoutine->amgettuple == NULL) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("access method \"%s\" does not support exclusion constraints", @@ -927,9 +916,8 @@ DefineIndex(Oid tableId, coloptions, allIndexParams, stmt->excludeOpNames, tableId, accessMethodName, accessMethodId, - amcanorder, stmt->isconstraint, stmt->iswithoutoverlaps, - root_save_userid, root_save_sec_context, - &root_save_nestlevel); + amcanorder, stmt->isconstraint, root_save_userid, + root_save_sec_context, &root_save_nestlevel); /* * Extra checks when creating a PRIMARY KEY index. @@ -947,7 +935,7 @@ DefineIndex(Oid tableId, * We could lift this limitation if we had global indexes, but those have * their own problems, so this is a useful feature combination. */ - if (partitioned && (stmt->unique || exclusion)) + if (partitioned && (stmt->unique || stmt->excludeOpNames)) { PartitionKey key = RelationGetPartitionKey(rel); const char *constraint_type; @@ -1001,10 +989,10 @@ DefineIndex(Oid tableId, * associated with index columns, too. We know what to do with * btree opclasses; if there are ever any other index types that * support unique indexes, this logic will need extension. But if - * we have an exclusion constraint (or a temporal PK), it already - * knows the operators, so we don't have to infer them. + * we have an exclusion constraint, it already knows the + * operators, so we don't have to infer them. */ - if (stmt->unique && !stmt->iswithoutoverlaps && accessMethodId != BTREE_AM_OID) + if (stmt->unique && accessMethodId != BTREE_AM_OID) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot match partition key to an index using access method \"%s\"", @@ -1043,12 +1031,12 @@ DefineIndex(Oid tableId, { Oid idx_eqop = InvalidOid; - if (stmt->unique && !stmt->iswithoutoverlaps) + if (stmt->unique) idx_eqop = get_opfamily_member(idx_opfamily, idx_opcintype, idx_opcintype, BTEqualStrategyNumber); - else if (exclusion) + else if (stmt->excludeOpNames) idx_eqop = indexInfo->ii_ExclusionOps[j]; Assert(idx_eqop); @@ -1057,7 +1045,7 @@ DefineIndex(Oid tableId, found = true; break; } - else if (exclusion) + else if (stmt->excludeOpNames) { /* * We found a match, but it's not an equality @@ -1201,8 +1189,6 @@ DefineIndex(Oid tableId, constr_flags |= INDEX_CONSTR_CREATE_DEFERRABLE; if (stmt->initdeferred) constr_flags |= INDEX_CONSTR_CREATE_INIT_DEFERRED; - if (stmt->iswithoutoverlaps) - constr_flags |= INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS; indexRelationId = index_create(rel, indexRelationName, indexRelationId, parentIndexId, @@ -1868,7 +1854,6 @@ ComputeIndexAttrs(IndexInfo *indexInfo, Oid accessMethodId, bool amcanorder, bool isconstraint, - bool iswithoutoverlaps, Oid ddl_userid, int ddl_sec_context, int *ddl_save_nestlevel) @@ -1892,14 +1877,6 @@ ComputeIndexAttrs(IndexInfo *indexInfo, else nextExclOp = NULL; - /* exclusionOpNames can be non-NIL if we are creating a partition */ - if (iswithoutoverlaps && exclusionOpNames == NIL) - { - indexInfo->ii_ExclusionOps = palloc_array(Oid, nkeycols); - indexInfo->ii_ExclusionProcs = palloc_array(Oid, nkeycols); - indexInfo->ii_ExclusionStrats = palloc_array(uint16, nkeycols); - } - if (OidIsValid(ddl_userid)) GetUserIdAndSecContext(&save_userid, &save_sec_context); @@ -2176,21 +2153,6 @@ ComputeIndexAttrs(IndexInfo *indexInfo, indexInfo->ii_ExclusionStrats[attn] = strat; nextExclOp = lnext(exclusionOpNames, nextExclOp); } - else if (iswithoutoverlaps) - { - StrategyNumber strat; - Oid opid; - - if (attn == nkeycols - 1) - strat = RTOverlapStrategyNumber; - else - strat = RTEqualStrategyNumber; - GetOperatorFromWellKnownStrategy(opclassOids[attn], InvalidOid, - &opid, &strat); - indexInfo->ii_ExclusionOps[attn] = opid; - indexInfo->ii_ExclusionProcs[attn] = get_opcode(opid); - indexInfo->ii_ExclusionStrats[attn] = strat; - } /* * Set up the per-column options (indoption field). For now, this is @@ -2422,88 +2384,6 @@ GetDefaultOpClass(Oid type_id, Oid am_id) } /* - * GetOperatorFromWellKnownStrategy - * - * opclass - the opclass to use - * rhstype - the type for the right-hand side, or InvalidOid to use the type of the given opclass. - * opid - holds the operator we found - * strat - holds the input and output strategy number - * - * Finds an operator from a "well-known" strategy number. This is used for - * temporal index constraints (and other temporal features) to look up - * equality and overlaps operators, since the strategy numbers for non-btree - * indexams need not follow any fixed scheme. We ask an opclass support - * function to translate from the well-known number to the internal value. If - * the function isn't defined or it gives no result, we return - * InvalidStrategy. - */ -void -GetOperatorFromWellKnownStrategy(Oid opclass, Oid rhstype, - Oid *opid, StrategyNumber *strat) -{ - Oid opfamily; - Oid opcintype; - StrategyNumber instrat = *strat; - - Assert(instrat == RTEqualStrategyNumber || instrat == RTOverlapStrategyNumber || instrat == RTContainedByStrategyNumber); - - *opid = InvalidOid; - - if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) - { - /* - * Ask the opclass to translate to its internal stratnum - * - * For now we only need GiST support, but this could support other - * indexams if we wanted. - */ - *strat = GistTranslateStratnum(opclass, instrat); - if (*strat == InvalidStrategy) - { - HeapTuple tuple; - - tuple = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass)); - if (!HeapTupleIsValid(tuple)) - elog(ERROR, "cache lookup failed for operator class %u", opclass); - - ereport(ERROR, - errcode(ERRCODE_UNDEFINED_OBJECT), - instrat == RTEqualStrategyNumber ? errmsg("could not identify an equality operator for type %s", format_type_be(opcintype)) : - instrat == RTOverlapStrategyNumber ? errmsg("could not identify an overlaps operator for type %s", format_type_be(opcintype)) : - instrat == RTContainedByStrategyNumber ? errmsg("could not identify a contained-by operator for type %s", format_type_be(opcintype)) : 0, - errdetail("Could not translate strategy number %d for operator class \"%s\" for access method \"%s\".", - instrat, NameStr(((Form_pg_opclass) GETSTRUCT(tuple))->opcname), "gist")); - } - - /* - * We parameterize rhstype so foreign keys can ask for a <@ operator - * whose rhs matches the aggregate function. For example range_agg - * returns anymultirange. - */ - if (!OidIsValid(rhstype)) - rhstype = opcintype; - *opid = get_opfamily_member(opfamily, opcintype, rhstype, *strat); - } - - if (!OidIsValid(*opid)) - { - HeapTuple tuple; - - tuple = SearchSysCache1(OPFAMILYOID, ObjectIdGetDatum(opfamily)); - if (!HeapTupleIsValid(tuple)) - elog(ERROR, "cache lookup failed for operator family %u", opfamily); - - ereport(ERROR, - errcode(ERRCODE_UNDEFINED_OBJECT), - instrat == RTEqualStrategyNumber ? errmsg("could not identify an equality operator for type %s", format_type_be(opcintype)) : - instrat == RTOverlapStrategyNumber ? errmsg("could not identify an overlaps operator for type %s", format_type_be(opcintype)) : - instrat == RTContainedByStrategyNumber ? errmsg("could not identify a contained-by operator for type %s", format_type_be(opcintype)) : 0, - errdetail("There is no suitable operator in operator family \"%s\" for access method \"%s\".", - NameStr(((Form_pg_opfamily) GETSTRUCT(tuple))->opfname), "gist")); - } -} - -/* * makeObjectName() * * Create a name for an implicitly created index, sequence, constraint, diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 79c9c031833..313c782cae2 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -16,7 +16,6 @@ #include "access/attmap.h" #include "access/genam.h" -#include "access/gist.h" #include "access/heapam.h" #include "access/heapam_xlog.h" #include "access/multixact.h" @@ -216,7 +215,6 @@ typedef struct NewConstraint ConstrType contype; /* CHECK or FOREIGN */ Oid refrelid; /* PK rel, if FOREIGN */ Oid refindid; /* OID of PK's index, if FOREIGN */ - bool conwithperiod; /* Whether the new FOREIGN KEY uses PERIOD */ Oid conid; /* OID of pg_constraint entry, if FOREIGN */ Node *qual; /* Check expr or CONSTR_FOREIGN Constraint */ ExprState *qualstate; /* Execution state for CHECK expr */ @@ -391,17 +389,16 @@ static int transformColumnNameList(Oid relId, List *colList, static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, List **attnamelist, int16 *attnums, Oid *atttypids, - Oid *opclasses, bool *pk_has_without_overlaps); + Oid *opclasses); static Oid transformFkeyCheckAttrs(Relation pkrel, int numattrs, int16 *attnums, - bool with_period, Oid *opclasses, - bool *pk_has_without_overlaps); + Oid *opclasses); static void checkFkeyPermissions(Relation rel, int16 *attnums, int natts); static CoercionPathType findFkeyCast(Oid targetTypeId, Oid sourceTypeId, Oid *funcid); static void validateForeignKeyConstraint(char *conname, Relation rel, Relation pkrel, - Oid pkindOid, Oid constraintOid, bool hasperiod); + Oid pkindOid, Oid constraintOid); static void ATController(AlterTableStmt *parsetree, Relation rel, List *cmds, bool recurse, LOCKMODE lockmode, AlterTableUtilityContext *context); @@ -512,8 +509,7 @@ static ObjectAddress addFkRecurseReferenced(List **wqueue, Constraint *fkconstra Oid *pfeqoperators, Oid *ppeqoperators, Oid *ffeqoperators, int numfkdelsetcols, int16 *fkdelsetcols, bool old_check_ok, - Oid parentDelTrigger, Oid parentUpdTrigger, - bool with_period); + Oid parentDelTrigger, Oid parentUpdTrigger); static void validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums, int numfksetcols, const int16 *fksetcolsattnums, List *fksetcols); @@ -523,9 +519,7 @@ static void addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Oid *pfeqoperators, Oid *ppeqoperators, Oid *ffeqoperators, int numfkdelsetcols, int16 *fkdelsetcols, bool old_check_ok, LOCKMODE lockmode, - Oid parentInsTrigger, Oid parentUpdTrigger, - bool with_period); - + Oid parentInsTrigger, Oid parentUpdTrigger); static void CloneForeignKeyConstraints(List **wqueue, Relation parentRel, Relation partitionRel); static void CloneFkReferenced(Relation parentRel, Relation partitionRel); @@ -5924,8 +5918,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode, validateForeignKeyConstraint(fkconstraint->conname, rel, refrel, con->refindid, - con->conid, - con->conwithperiod); + con->conid); /* * No need to mark the constraint row as validated, we did @@ -9566,8 +9559,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, Oid ppeqoperators[INDEX_MAX_KEYS] = {0}; Oid ffeqoperators[INDEX_MAX_KEYS] = {0}; int16 fkdelsetcols[INDEX_MAX_KEYS] = {0}; - bool with_period; - bool pk_has_without_overlaps; int i; int numfks, numpks, @@ -9662,11 +9653,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numfks = transformColumnNameList(RelationGetRelid(rel), fkconstraint->fk_attrs, fkattnum, fktypoid); - with_period = fkconstraint->fk_with_period || fkconstraint->pk_with_period; - if (with_period && !fkconstraint->fk_with_period) - ereport(ERROR, - errcode(ERRCODE_INVALID_FOREIGN_KEY), - errmsg("foreign key uses PERIOD on the referenced table but not the referencing table")); numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel), fkconstraint->fk_del_set_cols, @@ -9686,41 +9672,19 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid, &fkconstraint->pk_attrs, pkattnum, pktypoid, - opclasses, &pk_has_without_overlaps); - - /* If the primary key uses WITHOUT OVERLAPS, the fk must use PERIOD */ - if (pk_has_without_overlaps && !fkconstraint->fk_with_period) - ereport(ERROR, - errcode(ERRCODE_INVALID_FOREIGN_KEY), - errmsg("foreign key uses PERIOD on the referenced table but not the referencing table")); + opclasses); } else { numpks = transformColumnNameList(RelationGetRelid(pkrel), fkconstraint->pk_attrs, pkattnum, pktypoid); - - /* Since we got pk_attrs, one should be a period. */ - if (with_period && !fkconstraint->pk_with_period) - ereport(ERROR, - errcode(ERRCODE_INVALID_FOREIGN_KEY), - errmsg("foreign key uses PERIOD on the referencing table but not the referenced table")); - /* Look for an index matching the column list */ indexOid = transformFkeyCheckAttrs(pkrel, numpks, pkattnum, - with_period, opclasses, &pk_has_without_overlaps); + opclasses); } /* - * If the referenced primary key has WITHOUT OVERLAPS, the foreign key - * must use PERIOD. - */ - if (pk_has_without_overlaps && !with_period) - ereport(ERROR, - errcode(ERRCODE_INVALID_FOREIGN_KEY), - errmsg("foreign key must use PERIOD when referencing a primary using WITHOUT OVERLAPS")); - - /* * Now we can check permissions. */ checkFkeyPermissions(pkrel, pkattnum, numpks); @@ -9754,28 +9718,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, } /* - * Some actions are currently unsupported for foreign keys using PERIOD. - */ - if (fkconstraint->fk_with_period) - { - if (fkconstraint->fk_upd_action == FKCONSTR_ACTION_CASCADE || - fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETNULL || - fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETDEFAULT) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("unsupported %s action for foreign key constraint using PERIOD", - "ON UPDATE")); - - if (fkconstraint->fk_del_action == FKCONSTR_ACTION_CASCADE || - fkconstraint->fk_del_action == FKCONSTR_ACTION_SETNULL || - fkconstraint->fk_del_action == FKCONSTR_ACTION_SETDEFAULT) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("unsupported %s action for foreign key constraint using PERIOD", - "ON DELETE")); - } - - /* * Look up the equality operators to use in the constraint. * * Note that we have to be careful about the difference between the actual @@ -9821,56 +9763,16 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, opcintype = cla_tup->opcintype; ReleaseSysCache(cla_ht); - if (with_period) - { - StrategyNumber rtstrategy; - bool for_overlaps = with_period && i == numpks - 1; - - /* - * GiST indexes are required to support temporal foreign keys - * because they combine equals and overlaps. - */ - if (amid != GIST_AM_OID) - elog(ERROR, "only GiST indexes are supported for temporal foreign keys"); - - rtstrategy = for_overlaps ? RTOverlapStrategyNumber : RTEqualStrategyNumber; - - /* - * An opclass can use whatever strategy numbers it wants, so we - * ask the opclass what number it actually uses instead of our RT* - * constants. - */ - eqstrategy = GistTranslateStratnum(opclasses[i], rtstrategy); - if (eqstrategy == InvalidStrategy) - { - HeapTuple tuple; - - tuple = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclasses[i])); - if (!HeapTupleIsValid(tuple)) - elog(ERROR, "cache lookup failed for operator class %u", opclasses[i]); - - ereport(ERROR, - errcode(ERRCODE_UNDEFINED_OBJECT), - for_overlaps - ? errmsg("could not identify an overlaps operator for foreign key") - : errmsg("could not identify an equality operator for foreign key"), - errdetail("Could not translate strategy number %d for operator class \"%s\" for access method \"%s\".", - rtstrategy, NameStr(((Form_pg_opclass) GETSTRUCT(tuple))->opcname), "gist")); - } - } - else - { - /* - * Check it's a btree; currently this can never fail since no - * other index AMs support unique indexes. If we ever did have - * other types of unique indexes, we'd need a way to determine - * which operator strategy number is equality. (We could use - * something like GistTranslateStratnum.) - */ - if (amid != BTREE_AM_OID) - elog(ERROR, "only b-tree indexes are supported for foreign keys"); - eqstrategy = BTEqualStrategyNumber; - } + /* + * Check it's a btree; currently this can never fail since no other + * index AMs support unique indexes. If we ever did have other types + * of unique indexes, we'd need a way to determine which operator + * strategy number is equality. (Is it reasonable to insist that + * every such index AM use btree's number for equality?) + */ + if (amid != BTREE_AM_OID) + elog(ERROR, "only b-tree indexes are supported for foreign keys"); + eqstrategy = BTEqualStrategyNumber; /* * There had better be a primary equality operator for the index. @@ -10021,22 +9923,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, } /* - * For FKs with PERIOD we need additional operators to check whether the - * referencing row's range is contained by the aggregated ranges of the - * referenced row(s). For rangetypes and multirangetypes this is - * fk.periodatt <@ range_agg(pk.periodatt). Those are the only types we - * support for now. FKs will look these up at "runtime", but we should - * make sure the lookup works here, even if we don't use the values. - */ - if (with_period) - { - Oid periodoperoid; - Oid aggedperiodoperoid; - - FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid); - } - - /* * Create all the constraint and trigger objects, recursing to partitions * as necessary. First handle the referenced side. */ @@ -10052,8 +9938,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numfkdelsetcols, fkdelsetcols, old_check_ok, - InvalidOid, InvalidOid, - with_period); + InvalidOid, InvalidOid); /* Now handle the referencing side. */ addFkRecurseReferencing(wqueue, fkconstraint, rel, pkrel, @@ -10069,8 +9954,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, fkdelsetcols, old_check_ok, lockmode, - InvalidOid, InvalidOid, - with_period); + InvalidOid, InvalidOid); /* * Done. Close pk table, but keep lock until we've committed. @@ -10155,8 +10039,7 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, Oid *ppeqoperators, Oid *ffeqoperators, int numfkdelsetcols, int16 *fkdelsetcols, bool old_check_ok, - Oid parentDelTrigger, Oid parentUpdTrigger, - bool with_period) + Oid parentDelTrigger, Oid parentUpdTrigger) { ObjectAddress address; Oid constrOid; @@ -10242,7 +10125,6 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, conislocal, /* islocal */ coninhcount, /* inhcount */ connoinherit, /* conNoInherit */ - with_period, /* conPeriod */ false); /* is_internal */ ObjectAddressSet(address, ConstraintRelationId, constrOid); @@ -10318,8 +10200,7 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, pfeqoperators, ppeqoperators, ffeqoperators, numfkdelsetcols, fkdelsetcols, old_check_ok, - deleteTriggerOid, updateTriggerOid, - with_period); + deleteTriggerOid, updateTriggerOid); /* Done -- clean up (but keep the lock) */ table_close(partRel, NoLock); @@ -10377,8 +10258,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, Oid *pfeqoperators, Oid *ppeqoperators, Oid *ffeqoperators, int numfkdelsetcols, int16 *fkdelsetcols, bool old_check_ok, LOCKMODE lockmode, - Oid parentInsTrigger, Oid parentUpdTrigger, - bool with_period) + Oid parentInsTrigger, Oid parentUpdTrigger) { Oid insertTriggerOid, updateTriggerOid; @@ -10426,7 +10306,6 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, newcon->refrelid = RelationGetRelid(pkrel); newcon->refindid = indexOid; newcon->conid = parentConstr; - newcon->conwithperiod = fkconstraint->fk_with_period; newcon->qual = (Node *) fkconstraint; tab->constraints = lappend(tab->constraints, newcon); @@ -10544,7 +10423,6 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, false, 1, false, - with_period, /* conPeriod */ false); /* @@ -10575,8 +10453,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, old_check_ok, lockmode, insertTriggerOid, - updateTriggerOid, - with_period); + updateTriggerOid); table_close(partition, NoLock); } @@ -10812,8 +10689,7 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel) confdelsetcols, true, deleteTriggerOid, - updateTriggerOid, - constrForm->conperiod); + updateTriggerOid); table_close(fkRel, NoLock); ReleaseSysCache(tuple); @@ -10906,7 +10782,6 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) ListCell *lc; Oid insertTriggerOid, updateTriggerOid; - bool with_period; tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(parentConstrOid)); if (!HeapTupleIsValid(tuple)) @@ -11022,7 +10897,6 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) fkconstraint->conname = pstrdup(NameStr(constrForm->conname)); indexOid = constrForm->conindid; - with_period = constrForm->conperiod; constrOid = CreateConstraintEntry(fkconstraint->conname, constrForm->connamespace, @@ -11054,7 +10928,6 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* islocal */ 1, /* inhcount */ false, /* conNoInherit */ - with_period, /* conPeriod */ true); /* Set up partition dependencies for the new constraint */ @@ -11088,8 +10961,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* no old check exists */ AccessExclusiveLock, insertTriggerOid, - updateTriggerOid, - with_period); + updateTriggerOid); table_close(pkrel, NoLock); } @@ -11899,8 +11771,7 @@ transformColumnNameList(Oid relId, List *colList, * * Look up the names, attnums, and types of the primary key attributes * for the pkrel. Also return the index OID and index opclasses of the - * index supporting the primary key. Also return whether the index has - * WITHOUT OVERLAPS. + * index supporting the primary key. * * All parameters except pkrel are output parameters. Also, the function * return value is the number of attributes in the primary key. @@ -11911,7 +11782,7 @@ static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, List **attnamelist, int16 *attnums, Oid *atttypids, - Oid *opclasses, bool *pk_has_without_overlaps) + Oid *opclasses) { List *indexoidlist; ListCell *indexoidscan; @@ -11989,8 +11860,6 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno))))); } - *pk_has_without_overlaps = indexStruct->indisexclusion; - ReleaseSysCache(indexTuple); return i; @@ -12004,16 +11873,14 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, * * Returns the OID of the unique index supporting the constraint and * populates the caller-provided 'opclasses' array with the opclasses - * associated with the index columns. Also sets whether the index - * uses WITHOUT OVERLAPS. + * associated with the index columns. * * Raises an ERROR on validation failure. */ static Oid transformFkeyCheckAttrs(Relation pkrel, int numattrs, int16 *attnums, - bool with_period, Oid *opclasses, - bool *pk_has_without_overlaps) + Oid *opclasses) { Oid indexoid = InvalidOid; bool found = false; @@ -12060,12 +11927,12 @@ transformFkeyCheckAttrs(Relation pkrel, indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); /* - * Must have the right number of columns; must be unique (or if - * temporal then exclusion instead) and not a partial index; forget it - * if there are any expressions, too. Invalid indexes are out as well. + * Must have the right number of columns; must be unique and not a + * partial index; forget it if there are any expressions, too. Invalid + * indexes are out as well. */ if (indexStruct->indnkeyatts == numattrs && - (with_period ? indexStruct->indisexclusion : indexStruct->indisunique) && + indexStruct->indisunique && indexStruct->indisvalid && heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL) && heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL)) @@ -12103,13 +11970,6 @@ transformFkeyCheckAttrs(Relation pkrel, if (!found) break; } - /* The last attribute in the index must be the PERIOD FK part */ - if (found && with_period) - { - int16 periodattnum = attnums[numattrs - 1]; - - found = (periodattnum == indexStruct->indkey.values[numattrs - 1]); - } /* * Refuse to use a deferrable unique/primary key. This is per SQL @@ -12125,10 +11985,6 @@ transformFkeyCheckAttrs(Relation pkrel, found_deferrable = true; found = false; } - - /* We need to know whether the index has WITHOUT OVERLAPS */ - if (found) - *pk_has_without_overlaps = indexStruct->indisexclusion; } ReleaseSysCache(indexTuple); if (found) @@ -12223,8 +12079,7 @@ validateForeignKeyConstraint(char *conname, Relation rel, Relation pkrel, Oid pkindOid, - Oid constraintOid, - bool hasperiod) + Oid constraintOid) { TupleTableSlot *slot; TableScanDesc scan; @@ -12252,11 +12107,9 @@ validateForeignKeyConstraint(char *conname, /* * See if we can do it with a single LEFT JOIN query. A false result - * indicates we must proceed with the fire-the-trigger method. We can't do - * a LEFT JOIN for temporal FKs yet, but we can once we support temporal - * left joins. + * indicates we must proceed with the fire-the-trigger method. */ - if (!hasperiod && RI_Initial_Check(&trig, rel, pkrel)) + if (RI_Initial_Check(&trig, rel, pkrel)) return; /* @@ -12407,7 +12260,6 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr fk_trigger->whenClause = NULL; fk_trigger->transitionRels = NIL; fk_trigger->constrrel = NULL; - switch (fkconstraint->fk_del_action) { case FKCONSTR_ACTION_NOACTION: @@ -12468,7 +12320,6 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr fk_trigger->whenClause = NULL; fk_trigger->transitionRels = NIL; fk_trigger->constrrel = NULL; - switch (fkconstraint->fk_upd_action) { case FKCONSTR_ACTION_NOACTION: @@ -14245,8 +14096,7 @@ TryReuseIndex(Oid oldId, IndexStmt *stmt) if (CheckIndexCompatible(oldId, stmt->accessMethod, stmt->indexParams, - stmt->excludeOpNames, - stmt->iswithoutoverlaps)) + stmt->excludeOpNames)) { Relation irel = index_open(oldId, NoLock); diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 35eb7180f7e..95de402fa65 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -834,7 +834,6 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString, true, /* islocal */ 0, /* inhcount */ true, /* noinherit */ - false, /* conperiod */ isInternal); /* is_internal */ } diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 2a6550de907..2a1e7133356 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -3621,7 +3621,6 @@ domainAddCheckConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, true, /* is local */ 0, /* inhcount */ false, /* connoinherit */ - false, /* conperiod */ false); /* is_internal */ if (constrAddr) ObjectAddressSet(*constrAddr, ConstraintRelationId, ccoid); @@ -3728,7 +3727,6 @@ domainAddNotNullConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, true, /* is local */ 0, /* inhcount */ false, /* connoinherit */ - false, /* conperiod */ false); /* is_internal */ if (constrAddr) diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c index 59acf67a36a..9f05b3654c1 100644 --- a/src/backend/executor/execIndexing.c +++ b/src/backend/executor/execIndexing.c @@ -210,7 +210,7 @@ ExecOpenIndices(ResultRelInfo *resultRelInfo, bool speculative) * If the indexes are to be used for speculative insertion, add extra * information required by unique index entries. */ - if (speculative && ii->ii_Unique && !indexDesc->rd_index->indisexclusion) + if (speculative && ii->ii_Unique) BuildSpeculativeIndexInfo(indexDesc, ii); relationDescs[i] = indexDesc; diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 26f8de77135..a51fc34e6e0 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -815,7 +815,7 @@ infer_arbiter_indexes(PlannerInfo *root) */ if (indexOidFromConstraint == idxForm->indexrelid) { - if (idxForm->indisexclusion && onconflict->action == ONCONFLICT_UPDATE) + if (!idxForm->indisunique && onconflict->action == ONCONFLICT_UPDATE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints"))); @@ -840,13 +840,6 @@ infer_arbiter_indexes(PlannerInfo *root) if (!idxForm->indisunique) goto next; - /* - * So-called unique constraints with WITHOUT OVERLAPS are really - * exclusion constraints, so skip those too. - */ - if (idxForm->indisexclusion) - goto next; - /* Build BMS representation of plain (non expression) index attrs */ indexedAttrs = NULL; for (natt = 0; natt < idxForm->indnkeyatts; natt++) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index cabfba0921b..4d582950b72 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -525,15 +525,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); SetResetClause FunctionSetResetClause %type <node> TableElement TypedTableElement ConstraintElem DomainConstraintElem TableFuncElement -%type <node> columnDef columnOptions optionalPeriodName +%type <node> columnDef columnOptions %type <defelt> def_elem reloption_elem old_aggr_elem operator_def_elem %type <node> def_arg columnElem where_clause where_or_current_clause a_expr b_expr c_expr AexprConst indirection_el opt_slice_bound columnref in_expr having_clause func_table xmltable array_expr OptWhereClause operator_def_arg -%type <list> opt_column_and_period_list %type <list> rowsfrom_item rowsfrom_list opt_col_def_list -%type <boolean> opt_ordinality opt_without_overlaps +%type <boolean> opt_ordinality %type <list> ExclusionConstraintList ExclusionConstraintElem %type <list> func_arg_list func_arg_list_opt %type <node> func_arg_expr @@ -765,7 +764,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARAMETER PARSER PARTIAL PARTITION PARTITIONS PASSING PASSWORD PATH - PERIOD PLACING PLAN PLANS POLICY + PLACING PLAN PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -4194,7 +4193,7 @@ ConstraintElem: n->initially_valid = !n->skip_validation; $$ = (Node *) n; } - | UNIQUE opt_unique_null_treatment '(' columnList opt_without_overlaps ')' opt_c_include opt_definition OptConsTableSpace + | UNIQUE opt_unique_null_treatment '(' columnList ')' opt_c_include opt_definition OptConsTableSpace ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); @@ -4203,12 +4202,11 @@ ConstraintElem: n->location = @1; n->nulls_not_distinct = !$2; n->keys = $4; - n->without_overlaps = $5; - n->including = $7; - n->options = $8; + n->including = $6; + n->options = $7; n->indexname = NULL; - n->indexspace = $9; - processCASbits($10, @10, "UNIQUE", + n->indexspace = $8; + processCASbits($9, @9, "UNIQUE", &n->deferrable, &n->initdeferred, NULL, NULL, yyscanner); $$ = (Node *) n; @@ -4229,7 +4227,7 @@ ConstraintElem: NULL, yyscanner); $$ = (Node *) n; } - | PRIMARY KEY '(' columnList opt_without_overlaps ')' opt_c_include opt_definition OptConsTableSpace + | PRIMARY KEY '(' columnList ')' opt_c_include opt_definition OptConsTableSpace ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); @@ -4237,12 +4235,11 @@ ConstraintElem: n->contype = CONSTR_PRIMARY; n->location = @1; n->keys = $4; - n->without_overlaps = $5; - n->including = $7; - n->options = $8; + n->including = $6; + n->options = $7; n->indexname = NULL; - n->indexspace = $9; - processCASbits($10, @10, "PRIMARY KEY", + n->indexspace = $8; + processCASbits($9, @9, "PRIMARY KEY", &n->deferrable, &n->initdeferred, NULL, NULL, yyscanner); $$ = (Node *) n; @@ -4283,31 +4280,21 @@ ConstraintElem: NULL, yyscanner); $$ = (Node *) n; } - | FOREIGN KEY '(' columnList optionalPeriodName ')' REFERENCES qualified_name - opt_column_and_period_list key_match key_actions ConstraintAttributeSpec + | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name + opt_column_list key_match key_actions ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_FOREIGN; n->location = @1; - n->pktable = $8; + n->pktable = $7; n->fk_attrs = $4; - if ($5) - { - n->fk_attrs = lappend(n->fk_attrs, $5); - n->fk_with_period = true; - } - n->pk_attrs = linitial($9); - if (lsecond($9)) - { - n->pk_attrs = lappend(n->pk_attrs, lsecond($9)); - n->pk_with_period = true; - } - n->fk_matchtype = $10; - n->fk_upd_action = ($11)->updateAction->action; - n->fk_del_action = ($11)->deleteAction->action; - n->fk_del_set_cols = ($11)->deleteAction->cols; - processCASbits($12, @12, "FOREIGN KEY", + n->pk_attrs = $8; + n->fk_matchtype = $9; + n->fk_upd_action = ($10)->updateAction->action; + n->fk_del_action = ($10)->deleteAction->action; + n->fk_del_set_cols = ($10)->deleteAction->cols; + processCASbits($11, @11, "FOREIGN KEY", &n->deferrable, &n->initdeferred, &n->skip_validation, NULL, yyscanner); @@ -4374,11 +4361,6 @@ opt_no_inherit: NO INHERIT { $$ = true; } | /* EMPTY */ { $$ = false; } ; -opt_without_overlaps: - WITHOUT OVERLAPS { $$ = true; } - | /*EMPTY*/ { $$ = false; } - ; - opt_column_list: '(' columnList ')' { $$ = $2; } | /*EMPTY*/ { $$ = NIL; } @@ -4389,16 +4371,6 @@ columnList: | columnList ',' columnElem { $$ = lappend($1, $3); } ; -optionalPeriodName: - ',' PERIOD columnElem { $$ = $3; } - | /*EMPTY*/ { $$ = NULL; } - ; - -opt_column_and_period_list: - '(' columnList optionalPeriodName ')' { $$ = list_make2($2, $3); } - | /*EMPTY*/ { $$ = list_make2(NIL, NULL); } - ; - columnElem: ColId { $$ = (Node *) makeString($1); @@ -17793,7 +17765,6 @@ unreserved_keyword: | PASSING | PASSWORD | PATH - | PERIOD | PLAN | PLANS | POLICY @@ -18420,7 +18391,6 @@ bare_label_keyword: | PASSING | PASSWORD | PATH - | PERIOD | PLACING | PLAN | PLANS diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index b692d251522..639cfa443e2 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1562,7 +1562,6 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, index->unique = idxrec->indisunique; index->nulls_not_distinct = idxrec->indnullsnotdistinct; index->primary = idxrec->indisprimary; - index->iswithoutoverlaps = (idxrec->indisprimary || idxrec->indisunique) && idxrec->indisexclusion; index->transformed = true; /* don't need transformIndexStmt */ index->concurrent = false; index->if_not_exists = false; @@ -1612,9 +1611,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, int nElems; int i; - Assert(conrec->contype == CONSTRAINT_EXCLUSION || - (index->iswithoutoverlaps && - (conrec->contype == CONSTRAINT_PRIMARY || conrec->contype == CONSTRAINT_UNIQUE))); + Assert(conrec->contype == CONSTRAINT_EXCLUSION); /* Extract operator OIDs from the pg_constraint tuple */ datum = SysCacheGetAttrNotNull(CONSTROID, ht_constr, Anum_pg_constraint_conexclop); @@ -2156,7 +2153,6 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) } index->nulls_not_distinct = constraint->nulls_not_distinct; index->isconstraint = true; - index->iswithoutoverlaps = constraint->without_overlaps; index->deferrable = constraint->deferrable; index->initdeferred = constraint->initdeferred; @@ -2249,11 +2245,6 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) errmsg("index \"%s\" is not valid", index_name), parser_errposition(cxt->pstate, constraint->location))); - /* - * Today we forbid non-unique indexes, but we could permit GiST - * indexes whose last entry is a range type and use that to create a - * WITHOUT OVERLAPS constraint (i.e. a temporal constraint). - */ if (!index_form->indisunique) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), @@ -2542,23 +2533,6 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) notnullcmds = lappend(notnullcmds, notnullcmd); } } - - if (constraint->without_overlaps) - { - /* - * This enforces that there is at least one equality column - * besides the WITHOUT OVERLAPS columns. This is per SQL - * standard. XXX Do we need this? - */ - if (list_length(constraint->keys) < 2) - ereport(ERROR, - errcode(ERRCODE_SYNTAX_ERROR), - errmsg("constraint using WITHOUT OVERLAPS needs at least two columns")); - - /* WITHOUT OVERLAPS requires a GiST index */ - index->accessMethod = "gist"; - } - } /* diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index bbc2e3e2f0a..62601a6d80c 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -30,7 +30,6 @@ #include "access/xact.h" #include "catalog/pg_collation.h" #include "catalog/pg_constraint.h" -#include "catalog/pg_proc.h" #include "commands/trigger.h" #include "executor/executor.h" #include "executor/spi.h" @@ -46,7 +45,6 @@ #include "utils/inval.h" #include "utils/lsyscache.h" #include "utils/memutils.h" -#include "utils/rangetypes.h" #include "utils/rel.h" #include "utils/rls.h" #include "utils/ruleutils.h" @@ -98,9 +96,6 @@ * * Information extracted from an FK pg_constraint entry. This is cached in * ri_constraint_cache. - * - * Note that pf/pp/ff_eq_oprs may hold the overlaps operator instead of equals - * for the PERIOD part of a temporal foreign key. */ typedef struct RI_ConstraintInfo { @@ -120,15 +115,12 @@ typedef struct RI_ConstraintInfo int16 confdelsetcols[RI_MAX_NUMKEYS]; /* attnums of cols to set on * delete */ char confmatchtype; /* foreign key's match type */ - bool hasperiod; /* if the foreign key uses PERIOD */ int nkeys; /* number of key columns */ int16 pk_attnums[RI_MAX_NUMKEYS]; /* attnums of referenced cols */ int16 fk_attnums[RI_MAX_NUMKEYS]; /* attnums of referencing cols */ Oid pf_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = FK) */ Oid pp_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = PK) */ Oid ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK = FK) */ - Oid period_contained_by_oper; /* anyrange <@ anyrange */ - Oid agged_period_contained_by_oper; /* fkattr <@ range_agg(pkattr) */ dlist_node valid_link; /* Link in list of valid entries */ } RI_ConstraintInfo; @@ -207,8 +199,8 @@ static void ri_BuildQueryKey(RI_QueryKey *key, int32 constr_queryno); static bool ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, const RI_ConstraintInfo *riinfo, bool rel_is_pk); -static bool ri_CompareWithCast(Oid eq_opr, Oid typeid, - Datum lhs, Datum rhs); +static bool ri_AttributesEqual(Oid eq_opr, Oid typeid, + Datum oldvalue, Datum newvalue); static void ri_InitHashTables(void); static void InvalidateConstraintCacheCallBack(Datum arg, int cacheid, uint32 hashvalue); @@ -370,41 +362,14 @@ RI_FKey_check(TriggerData *trigdata) * FOR KEY SHARE OF x * The type id's for the $ parameters are those of the * corresponding FK attributes. - * - * But for temporal FKs we need to make sure - * the FK's range is completely covered. - * So we use this query instead: - * SELECT 1 - * FROM ( - * SELECT pkperiodatt AS r - * FROM [ONLY] pktable x - * WHERE pkatt1 = $1 [AND ...] - * AND pkperiodatt && $n - * FOR KEY SHARE OF x - * ) x1 - * HAVING $n <@ range_agg(x1.r) - * Note if FOR KEY SHARE ever allows GROUP BY and HAVING - * we can make this a bit simpler. * ---------- */ initStringInfo(&querybuf); pk_only = pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? "" : "ONLY "; quoteRelationName(pkrelname, pk_rel); - if (riinfo->hasperiod) - { - quoteOneName(attname, - RIAttName(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1])); - - appendStringInfo(&querybuf, - "SELECT 1 FROM (SELECT %s AS r FROM %s%s x", - attname, pk_only, pkrelname); - } - else - { - appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x", - pk_only, pkrelname); - } + appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x", + pk_only, pkrelname); querysep = "WHERE"; for (int i = 0; i < riinfo->nkeys; i++) { @@ -422,18 +387,6 @@ RI_FKey_check(TriggerData *trigdata) queryoids[i] = fk_type; } appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); - if (riinfo->hasperiod) - { - Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]); - - appendStringInfoString(&querybuf, ") x1 HAVING "); - sprintf(paramname, "$%d", riinfo->nkeys); - ri_GenerateQual(&querybuf, "", - paramname, fk_type, - riinfo->agged_period_contained_by_oper, - "pg_catalog.range_agg", ANYMULTIRANGEOID); - appendStringInfoString(&querybuf, "(x1.r)"); - } /* Prepare and save the plan */ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, @@ -541,39 +494,14 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, * FOR KEY SHARE OF x * The type id's for the $ parameters are those of the * PK attributes themselves. - * But for temporal FKs we need to make sure - * the FK's range is completely covered. - * So we use this query instead: - * SELECT 1 - * FROM ( - * SELECT pkperiodatt AS r - * FROM [ONLY] pktable x - * WHERE pkatt1 = $1 [AND ...] - * AND pkperiodatt && $n - * FOR KEY SHARE OF x - * ) x1 - * HAVING $n <@ range_agg(x1.r) - * Note if FOR KEY SHARE ever allows GROUP BY and HAVING - * we can make this a bit simpler. * ---------- */ initStringInfo(&querybuf); pk_only = pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? "" : "ONLY "; quoteRelationName(pkrelname, pk_rel); - if (riinfo->hasperiod) - { - quoteOneName(attname, RIAttName(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1])); - - appendStringInfo(&querybuf, - "SELECT 1 FROM (SELECT %s AS r FROM %s%s x", - attname, pk_only, pkrelname); - } - else - { - appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x", - pk_only, pkrelname); - } + appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x", + pk_only, pkrelname); querysep = "WHERE"; for (int i = 0; i < riinfo->nkeys; i++) { @@ -590,18 +518,6 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, queryoids[i] = pk_type; } appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); - if (riinfo->hasperiod) - { - Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]); - - appendStringInfoString(&querybuf, ") x1 HAVING "); - sprintf(paramname, "$%d", riinfo->nkeys); - ri_GenerateQual(&querybuf, "", - paramname, fk_type, - riinfo->agged_period_contained_by_oper, - "pg_catalog.range_agg", ANYMULTIRANGEOID); - appendStringInfoString(&querybuf, "(x1.r)"); - } /* Prepare and save the plan */ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, @@ -2246,7 +2162,6 @@ ri_LoadConstraintInfo(Oid constraintOid) riinfo->confupdtype = conForm->confupdtype; riinfo->confdeltype = conForm->confdeltype; riinfo->confmatchtype = conForm->confmatchtype; - riinfo->hasperiod = conForm->conperiod; DeconstructFkConstraintRow(tup, &riinfo->nkeys, @@ -2258,20 +2173,6 @@ ri_LoadConstraintInfo(Oid constraintOid) &riinfo->ndelsetcols, riinfo->confdelsetcols); - /* - * For temporal FKs, get the operators and functions we need. We ask the - * opclass of the PK element for these. This all gets cached (as does the - * generated plan), so there's no performance issue. - */ - if (riinfo->hasperiod) - { - Oid opclass = get_index_column_opclass(conForm->conindid, riinfo->nkeys); - - FindFKPeriodOpers(opclass, - &riinfo->period_contained_by_oper, - &riinfo->agged_period_contained_by_oper); - } - ReleaseSysCache(tup); /* @@ -2883,10 +2784,7 @@ ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan) /* * ri_KeysEqual - * - * Check if all key values in OLD and NEW are "equivalent": - * For normal FKs we check for equality. - * For temporal FKs we check that the PK side is a superset of its old value, - * or the FK side is a subset of its old value. + * Check if all key values in OLD and NEW are equal. * * Note: at some point we might wish to redefine this as checking for * "IS NOT DISTINCT" rather than "=", that is, allow two nulls to be @@ -2942,25 +2840,13 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, } else { - Oid eq_opr; - - /* - * When comparing the PERIOD columns we can skip the check - * whenever the referencing column stayed equal or shrank, so test - * with the contained-by operator instead. - */ - if (riinfo->hasperiod && i == riinfo->nkeys - 1) - eq_opr = riinfo->period_contained_by_oper; - else - eq_opr = riinfo->ff_eq_oprs[i]; - /* * For the FK table, compare with the appropriate equality * operator. Changes that compare equal will still satisfy the * constraint after the update. */ - if (!ri_CompareWithCast(eq_opr, RIAttType(rel, attnums[i]), - newvalue, oldvalue)) + if (!ri_AttributesEqual(riinfo->ff_eq_oprs[i], RIAttType(rel, attnums[i]), + oldvalue, newvalue)) return false; } } @@ -2970,31 +2856,29 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, /* - * ri_CompareWithCast - + * ri_AttributesEqual - * - * Call the appropriate comparison operator for two values. - * Normally this is equality, but for the PERIOD part of foreign keys - * it is ContainedBy, so the order of lhs vs rhs is significant. + * Call the appropriate equality comparison operator for two values. * * NB: we have already checked that neither value is null. */ static bool -ri_CompareWithCast(Oid eq_opr, Oid typeid, - Datum lhs, Datum rhs) +ri_AttributesEqual(Oid eq_opr, Oid typeid, + Datum oldvalue, Datum newvalue) { RI_CompareHashEntry *entry = ri_HashCompareOp(eq_opr, typeid); /* Do we need to cast the values? */ if (OidIsValid(entry->cast_func_finfo.fn_oid)) { - lhs = FunctionCall3(&entry->cast_func_finfo, - lhs, - Int32GetDatum(-1), /* typmod */ - BoolGetDatum(false)); /* implicit coercion */ - rhs = FunctionCall3(&entry->cast_func_finfo, - rhs, - Int32GetDatum(-1), /* typmod */ - BoolGetDatum(false)); /* implicit coercion */ + oldvalue = FunctionCall3(&entry->cast_func_finfo, + oldvalue, + Int32GetDatum(-1), /* typmod */ + BoolGetDatum(false)); /* implicit coercion */ + newvalue = FunctionCall3(&entry->cast_func_finfo, + newvalue, + Int32GetDatum(-1), /* typmod */ + BoolGetDatum(false)); /* implicit coercion */ } /* @@ -3008,16 +2892,10 @@ ri_CompareWithCast(Oid eq_opr, Oid typeid, * open), we'll just use the default collation here, which could lead to * some false negatives. All this would break if we ever allow * database-wide collations to be nondeterministic. - * - * With range/multirangetypes, the collation of the base type is stored as - * part of the rangetype (pg_range.rngcollation), and always used, so - * there is no danger of inconsistency even using a non-equals operator. - * But if we support arbitrary types with PERIOD, we should perhaps just - * always force a re-check. */ return DatumGetBool(FunctionCall2Coll(&entry->eq_opr_finfo, DEFAULT_COLLATION_OID, - lhs, rhs)); + oldvalue, newvalue)); } /* @@ -3072,7 +2950,7 @@ ri_HashCompareOp(Oid eq_opr, Oid typeid) * the cast function to get to the operator's input type. * * XXX eventually it would be good to support array-coercion cases - * here and in ri_CompareWithCast(). At the moment there is no point + * here and in ri_AttributesEqual(). At the moment there is no point * because cases involving nonidentical array types will be rejected * at constraint creation time. * diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 9a6d372414c..9618619762b 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -338,7 +338,7 @@ static char *pg_get_viewdef_worker(Oid viewoid, int prettyFlags, int wrapColumn); static char *pg_get_triggerdef_worker(Oid trigid, bool pretty); static int decompile_column_index_array(Datum column_index_array, Oid relId, - bool withPeriod, StringInfo buf); + StringInfo buf); static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags); static char *pg_get_indexdef_worker(Oid indexrelid, int colno, const Oid *excludeOps, @@ -2260,8 +2260,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - /* If it is a temporal foreign key then it uses PERIOD. */ - decompile_column_index_array(val, conForm->conrelid, conForm->conperiod, &buf); + decompile_column_index_array(val, conForm->conrelid, &buf); /* add foreign relation name */ appendStringInfo(&buf, ") REFERENCES %s(", @@ -2272,7 +2271,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_confkey); - decompile_column_index_array(val, conForm->confrelid, conForm->conperiod, &buf); + decompile_column_index_array(val, conForm->confrelid, &buf); appendStringInfoChar(&buf, ')'); @@ -2358,7 +2357,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, if (!isnull) { appendStringInfoString(&buf, " ("); - decompile_column_index_array(val, conForm->conrelid, false, &buf); + decompile_column_index_array(val, conForm->conrelid, &buf); appendStringInfoChar(&buf, ')'); } @@ -2393,9 +2392,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - keyatts = decompile_column_index_array(val, conForm->conrelid, false, &buf); - if (conForm->conperiod) - appendStringInfoString(&buf, " WITHOUT OVERLAPS"); + keyatts = decompile_column_index_array(val, conForm->conrelid, &buf); appendStringInfoChar(&buf, ')'); @@ -2577,7 +2574,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, */ static int decompile_column_index_array(Datum column_index_array, Oid relId, - bool withPeriod, StringInfo buf) + StringInfo buf) { Datum *keys; int nKeys; @@ -2596,9 +2593,7 @@ decompile_column_index_array(Datum column_index_array, Oid relId, if (j == 0) appendStringInfoString(buf, quote_identifier(colName)); else - appendStringInfo(buf, ", %s%s", - (withPeriod && j == nKeys - 1) ? "PERIOD " : "", - quote_identifier(colName)); + appendStringInfo(buf, ", %s", quote_identifier(colName)); } return nKeys; diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index e6072cbdd9e..cc9b0c6524f 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -5540,14 +5540,11 @@ RelationGetIdentityKeyBitmap(Relation relation) /* * RelationGetExclusionInfo -- get info about index's exclusion constraint * - * This should be called only for an index that is known to have an associated - * exclusion constraint or primary key/unique constraint using WITHOUT - * OVERLAPS. - - * It returns arrays (palloc'd in caller's context) of the exclusion operator - * OIDs, their underlying functions' OIDs, and their strategy numbers in the - * index's opclasses. We cache all this information since it requires a fair - * amount of work to get. + * This should be called only for an index that is known to have an + * associated exclusion constraint. It returns arrays (palloc'd in caller's + * context) of the exclusion operator OIDs, their underlying functions' + * OIDs, and their strategy numbers in the index's opclasses. We cache + * all this information since it requires a fair amount of work to get. */ void RelationGetExclusionInfo(Relation indexRelation, @@ -5611,10 +5608,7 @@ RelationGetExclusionInfo(Relation indexRelation, int nelem; /* We want the exclusion constraint owning the index */ - if ((conform->contype != CONSTRAINT_EXCLUSION && - !(conform->conperiod && ( - conform->contype == CONSTRAINT_PRIMARY - || conform->contype == CONSTRAINT_UNIQUE))) || + if (conform->contype != CONSTRAINT_EXCLUSION || conform->conindid != RelationGetRelid(indexRelation)) continue; diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 5dfa7b3bcba..cb14fcafeac 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -7344,7 +7344,6 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_conname, i_condeferrable, i_condeferred, - i_conperiod, i_contableoid, i_conoid, i_condef, @@ -7426,17 +7425,10 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) if (fout->remoteVersion >= 150000) appendPQExpBufferStr(query, - "i.indnullsnotdistinct, "); + "i.indnullsnotdistinct "); else appendPQExpBufferStr(query, - "false AS indnullsnotdistinct, "); - - if (fout->remoteVersion >= 170000) - appendPQExpBufferStr(query, - "c.conperiod "); - else - appendPQExpBufferStr(query, - "NULL AS conperiod "); + "false AS indnullsnotdistinct "); /* * The point of the messy-looking outer join is to find a constraint that @@ -7504,7 +7496,6 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_conname = PQfnumber(res, "conname"); i_condeferrable = PQfnumber(res, "condeferrable"); i_condeferred = PQfnumber(res, "condeferred"); - i_conperiod = PQfnumber(res, "conperiod"); i_contableoid = PQfnumber(res, "contableoid"); i_conoid = PQfnumber(res, "conoid"); i_condef = PQfnumber(res, "condef"); @@ -7612,7 +7603,6 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) constrinfo->conindex = indxinfo[j].dobj.dumpId; constrinfo->condeferrable = *(PQgetvalue(res, j, i_condeferrable)) == 't'; constrinfo->condeferred = *(PQgetvalue(res, j, i_condeferred)) == 't'; - constrinfo->conperiod = *(PQgetvalue(res, j, i_conperiod)) == 't'; constrinfo->conislocal = true; constrinfo->separate = true; @@ -17058,8 +17048,6 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo) (k == 0) ? "" : ", ", fmtId(attname)); } - if (coninfo->conperiod) - appendPQExpBufferStr(q, " WITHOUT OVERLAPS"); if (indxinfo->indnkeyattrs < indxinfo->indnattrs) appendPQExpBufferStr(q, ") INCLUDE ("); diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index f518a1e6d2a..865823868f1 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -474,7 +474,6 @@ typedef struct _constraintInfo DumpId conindex; /* identifies associated index if any */ bool condeferrable; /* true if constraint is DEFERRABLE */ bool condeferred; /* true if constraint is INITIALLY DEFERRED */ - bool conperiod; /* true if the constraint is WITHOUT OVERLAPS */ bool conislocal; /* true if constraint has local definition */ bool separate; /* true if must dump as separate item */ } ConstraintInfo; diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index beed8db4d10..d3dd8784d64 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -1004,42 +1004,6 @@ my %tests = ( }, }, - 'CONSTRAINT PRIMARY KEY / WITHOUT OVERLAPS' => { - create_sql => 'CREATE TABLE dump_test.test_table_tpk ( - col1 int4range, - col2 tstzrange, - CONSTRAINT test_table_tpk_pkey PRIMARY KEY (col1, col2 WITHOUT OVERLAPS));', - regexp => qr/^ - \QALTER TABLE ONLY dump_test.test_table_tpk\E \n^\s+ - \QADD CONSTRAINT test_table_tpk_pkey PRIMARY KEY (col1, col2 WITHOUT OVERLAPS);\E - /xm, - like => { - %full_runs, %dump_test_schema_runs, section_post_data => 1, - }, - unlike => { - exclude_dump_test_schema => 1, - only_dump_measurement => 1, - }, - }, - - 'CONSTRAINT UNIQUE / WITHOUT OVERLAPS' => { - create_sql => 'CREATE TABLE dump_test.test_table_tuq ( - col1 int4range, - col2 tstzrange, - CONSTRAINT test_table_tuq_uq UNIQUE (col1, col2 WITHOUT OVERLAPS));', - regexp => qr/^ - \QALTER TABLE ONLY dump_test.test_table_tuq\E \n^\s+ - \QADD CONSTRAINT test_table_tuq_uq UNIQUE (col1, col2 WITHOUT OVERLAPS);\E - /xm, - like => { - %full_runs, %dump_test_schema_runs, section_post_data => 1, - }, - unlike => { - exclude_dump_test_schema => 1, - only_dump_measurement => 1, - }, - }, - 'ALTER TABLE (partitioned) ADD CONSTRAINT ... FOREIGN KEY' => { create_order => 4, create_sql => 'CREATE TABLE dump_test.test_table_fk ( diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 3af44acef1d..f67bf0b8925 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2383,10 +2383,6 @@ describeOneTableDetails(const char *schemaname, else appendPQExpBufferStr(&buf, ", false AS indisreplident"); appendPQExpBufferStr(&buf, ", c2.reltablespace"); - if (pset.sversion >= 170000) - appendPQExpBufferStr(&buf, ", con.conperiod"); - else - appendPQExpBufferStr(&buf, ", false AS conperiod"); appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n" " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n" @@ -2408,12 +2404,8 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, " \"%s\"", PQgetvalue(result, i, 0)); - /* - * If exclusion constraint or PK/UNIQUE constraint WITHOUT - * OVERLAPS, print the constraintdef - */ - if (strcmp(PQgetvalue(result, i, 7), "x") == 0 || - strcmp(PQgetvalue(result, i, 12), "t") == 0) + /* If exclusion constraint, print the constraintdef */ + if (strcmp(PQgetvalue(result, i, 7), "x") == 0) { appendPQExpBuffer(&buf, " %s", PQgetvalue(result, i, 6)); diff --git a/src/include/access/gist.h b/src/include/access/gist.h index 22dd04c1418..c6dcd6a90dd 100644 --- a/src/include/access/gist.h +++ b/src/include/access/gist.h @@ -17,7 +17,6 @@ #define GIST_H #include "access/itup.h" -#include "access/stratnum.h" #include "access/transam.h" #include "access/xlog.h" #include "access/xlogdefs.h" @@ -39,8 +38,7 @@ #define GIST_FETCH_PROC 9 #define GIST_OPTIONS_PROC 10 #define GIST_SORTSUPPORT_PROC 11 -#define GIST_STRATNUM_PROC 12 -#define GISTNProcs 12 +#define GISTNProcs 11 /* * Page opaque data in a GiST index page. @@ -247,6 +245,4 @@ typedef struct do { (e).key = (k); (e).rel = (r); (e).page = (pg); \ (e).offset = (o); (e).leafkey = (l); } while (0) -extern StrategyNumber GistTranslateStratnum(Oid opclass, StrategyNumber strat); - #endif /* GIST_H */ diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 4b89f980925..f0809c0e58a 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202405141 +#define CATALOG_VERSION_NO 202405161 #endif diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 2dea96f47c3..7d434f8e653 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -93,7 +93,6 @@ extern Oid index_create(Relation heapRelation, #define INDEX_CONSTR_CREATE_INIT_DEFERRED (1 << 2) #define INDEX_CONSTR_CREATE_UPDATE_INDEX (1 << 3) #define INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS (1 << 4) -#define INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS (1 << 5) extern Oid index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index 352558c1f06..f639c3a6a51 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -507,9 +507,6 @@ amprocrighttype => 'box', amprocnum => '7', amproc => 'gist_box_same' }, { amprocfamily => 'gist/box_ops', amproclefttype => 'box', amprocrighttype => 'box', amprocnum => '8', amproc => 'gist_box_distance' }, -{ amprocfamily => 'gist/box_ops', amproclefttype => 'box', - amprocrighttype => 'box', amprocnum => '12', - amproc => 'gist_stratnum_identity' }, { amprocfamily => 'gist/poly_ops', amproclefttype => 'polygon', amprocrighttype => 'polygon', amprocnum => '1', amproc => 'gist_poly_consistent' }, @@ -529,9 +526,6 @@ { amprocfamily => 'gist/poly_ops', amproclefttype => 'polygon', amprocrighttype => 'polygon', amprocnum => '8', amproc => 'gist_poly_distance' }, -{ amprocfamily => 'gist/poly_ops', amproclefttype => 'polygon', - amprocrighttype => 'polygon', amprocnum => '12', - amproc => 'gist_stratnum_identity' }, { amprocfamily => 'gist/circle_ops', amproclefttype => 'circle', amprocrighttype => 'circle', amprocnum => '1', amproc => 'gist_circle_consistent' }, @@ -550,9 +544,6 @@ { amprocfamily => 'gist/circle_ops', amproclefttype => 'circle', amprocrighttype => 'circle', amprocnum => '8', amproc => 'gist_circle_distance' }, -{ amprocfamily => 'gist/circle_ops', amproclefttype => 'circle', - amprocrighttype => 'circle', amprocnum => '12', - amproc => 'gist_stratnum_identity' }, { amprocfamily => 'gist/tsvector_ops', amproclefttype => 'tsvector', amprocrighttype => 'tsvector', amprocnum => '1', amproc => 'gtsvector_consistent(internal,tsvector,int2,oid,internal)' }, @@ -607,9 +598,6 @@ { amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '7', amproc => 'range_gist_same' }, -{ amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', - amprocrighttype => 'anyrange', amprocnum => '12', - amproc => 'gist_stratnum_identity' }, { amprocfamily => 'gist/network_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '1', amproc => 'inet_gist_consistent' }, @@ -626,9 +614,6 @@ amprocrighttype => 'inet', amprocnum => '7', amproc => 'inet_gist_same' }, { amprocfamily => 'gist/network_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '9', amproc => 'inet_gist_fetch' }, -{ amprocfamily => 'gist/network_ops', amproclefttype => 'inet', - amprocrighttype => 'inet', amprocnum => '12', - amproc => 'gist_stratnum_identity' }, { amprocfamily => 'gist/multirange_ops', amproclefttype => 'anymultirange', amprocrighttype => 'anymultirange', amprocnum => '1', amproc => 'multirange_gist_consistent' }, @@ -647,9 +632,6 @@ { amprocfamily => 'gist/multirange_ops', amproclefttype => 'anymultirange', amprocrighttype => 'anymultirange', amprocnum => '7', amproc => 'range_gist_same' }, -{ amprocfamily => 'gist/multirange_ops', amproclefttype => 'anymultirange', - amprocrighttype => 'anymultirange', amprocnum => '12', - amproc => 'gist_stratnum_identity' }, # gin { amprocfamily => 'gin/array_ops', amproclefttype => 'anyarray', diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index 115217a6162..7a8017f15b5 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -107,12 +107,6 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) /* Has a local definition and cannot be inherited */ bool connoinherit; - /* - * For primary keys, unique constraints, and foreign keys, signifies the - * last column uses overlaps instead of equals. - */ - bool conperiod; - #ifdef CATALOG_VARLEN /* variable-length fields start here */ /* @@ -127,22 +121,20 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) int16 confkey[1]; /* - * If a foreign key, the OIDs of the PK = FK equality/overlap operators - * for each column of the constraint + * If a foreign key, the OIDs of the PK = FK equality operators for each + * column of the constraint */ Oid conpfeqop[1] BKI_LOOKUP(pg_operator); /* - * If a foreign key, the OIDs of the PK = PK equality/overlap operators - * for each column of the constraint (i.e., equality for the referenced - * columns) + * If a foreign key, the OIDs of the PK = PK equality operators for each + * column of the constraint (i.e., equality for the referenced columns) */ Oid conppeqop[1] BKI_LOOKUP(pg_operator); /* - * If a foreign key, the OIDs of the FK = FK equality/overlap operators - * for each column of the constraint (i.e., equality for the referencing - * columns) + * If a foreign key, the OIDs of the FK = FK equality operators for each + * column of the constraint (i.e., equality for the referencing columns) */ Oid conffeqop[1] BKI_LOOKUP(pg_operator); @@ -154,8 +146,7 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) /* * If an exclusion constraint, the OIDs of the exclusion operators for - * each column of the constraint. Also set for unique constraints/primary - * keys using WITHOUT OVERLAPS. + * each column of the constraint */ Oid conexclop[1] BKI_LOOKUP(pg_operator); @@ -247,7 +238,6 @@ extern Oid CreateConstraintEntry(const char *constraintName, bool conIsLocal, int conInhCount, bool conNoInherit, - bool conPeriod, bool is_internal); extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId, @@ -279,9 +269,6 @@ extern void DeconstructFkConstraintRow(HeapTuple tuple, int *numfks, AttrNumber *conkey, AttrNumber *confkey, Oid *pf_eq_oprs, Oid *pp_eq_oprs, Oid *ff_eq_oprs, int *num_fk_del_set_cols, AttrNumber *fk_del_set_cols); -extern void FindFKPeriodOpers(Oid opclass, - Oid *containedbyoperoid, - Oid *aggedcontainedbyoperoid); extern bool check_functional_grouping(Oid relid, Index varno, Index varlevelsup, diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 2a9f2105b12..6a5476d3c4c 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12185,9 +12185,4 @@ proargnames => '{summarized_tli,summarized_lsn,pending_lsn,summarizer_pid}', prosrc => 'pg_get_wal_summarizer_state' }, -# GiST stratnum implementations -{ oid => '6313', descr => 'GiST support', - proname => 'gist_stratnum_identity', prorettype => 'int2', - proargtypes => 'int2', prosrc => 'gist_stratnum_identity' }, - ] diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 5fd095ea177..29c511e3196 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -14,7 +14,6 @@ #ifndef DEFREM_H #define DEFREM_H -#include "access/stratnum.h" #include "catalog/objectaddress.h" #include "nodes/params.h" #include "parser/parse_node.h" @@ -45,13 +44,10 @@ extern char *ChooseRelationName(const char *name1, const char *name2, extern bool CheckIndexCompatible(Oid oldId, const char *accessMethodName, const List *attributeList, - const List *exclusionOpNames, - bool isWithoutOverlaps); + const List *exclusionOpNames); extern Oid GetDefaultOpClass(Oid type_id, Oid am_id); extern Oid ResolveOpClass(const List *opclass, Oid attrType, const char *accessMethodName, Oid accessMethodId); -extern void GetOperatorFromWellKnownStrategy(Oid opclass, Oid rhstype, - Oid *opid, StrategyNumber *strat); /* commands/functioncmds.c */ extern ObjectAddress CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index dcfd080dd5c..ddfed02db22 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2750,7 +2750,6 @@ typedef struct Constraint bool nulls_not_distinct; /* null treatment for UNIQUE constraints */ List *keys; /* String nodes naming referenced key * column(s); for UNIQUE/PK/NOT NULL */ - bool without_overlaps; /* WITHOUT OVERLAPS specified */ List *including; /* String nodes naming referenced nonkey * column(s); for UNIQUE/PK */ List *exclusions; /* list of (IndexElem, operator name) pairs; @@ -2767,8 +2766,6 @@ typedef struct Constraint RangeVar *pktable; /* Primary key table */ List *fk_attrs; /* Attributes of foreign key */ List *pk_attrs; /* Corresponding attrs in PK table */ - bool fk_with_period; /* Last attribute of FK uses PERIOD */ - bool pk_with_period; /* Last attribute of PK uses PERIOD */ char fk_matchtype; /* FULL, PARTIAL, SIMPLE */ char fk_upd_action; /* ON UPDATE action */ char fk_del_action; /* ON DELETE action */ @@ -3376,7 +3373,6 @@ typedef struct IndexStmt bool nulls_not_distinct; /* null treatment for UNIQUE constraints */ bool primary; /* is index a primary key? */ bool isconstraint; /* is it for a pkey/unique constraint? */ - bool iswithoutoverlaps; /* is the constraint WITHOUT OVERLAPS? */ bool deferrable; /* is the constraint DEFERRABLE? */ bool initdeferred; /* is the constraint INITIALLY DEFERRED? */ bool transformed; /* true when transformIndexStmt is finished */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f9a4afd4723..f7fe834cf45 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -339,7 +339,6 @@ PG_KEYWORD("partitions", PARTITIONS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("path", PATH, UNRESERVED_KEYWORD, BARE_LABEL) -PG_KEYWORD("period", PERIOD, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plan", PLAN, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out index e0ba9fdafae..d94056862ae 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -670,19 +670,6 @@ FROM pg_walfile_name_offset('0/0'::pg_lsn + :segment_size - 1), 0 | t (1 row) --- test stratnum support functions -SELECT gist_stratnum_identity(3::smallint); - gist_stratnum_identity ------------------------- - 3 -(1 row) - -SELECT gist_stratnum_identity(18::smallint); - gist_stratnum_identity ------------------------- - 18 -(1 row) - -- pg_current_logfile CREATE ROLE regress_current_logfile; -- not available by default diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out deleted file mode 100644 index e2f2a1cbe20..00000000000 --- a/src/test/regress/expected/without_overlaps.out +++ /dev/null @@ -1,1722 +0,0 @@ --- Tests for WITHOUT OVERLAPS. --- --- We leave behind several tables to test pg_dump etc: --- temporal_rng, temporal_rng2, --- temporal_fk_rng2rng. -SET datestyle TO ISO, YMD; --- --- test input parser --- --- PK with no columns just WITHOUT OVERLAPS: -CREATE TABLE temporal_rng ( - valid_at daterange, - CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS) -); -ERROR: constraint using WITHOUT OVERLAPS needs at least two columns --- PK with a range column/PERIOD that isn't there: -CREATE TABLE temporal_rng ( - id INTEGER, - CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ERROR: column "valid_at" named in key does not exist -LINE 3: CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOU... - ^ --- PK with a non-range column: -CREATE TABLE temporal_rng ( - id int4range, - valid_at TEXT, - CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ERROR: data type text has no default operator class for access method "gist" -HINT: You must specify an operator class for the index or define a default operator class for the data type. --- PK with one column plus a range: -CREATE TABLE temporal_rng ( - -- Since we can't depend on having btree_gist here, - -- use an int4range instead of an int. - -- (The rangetypes regression test uses the same trick.) - id int4range, - valid_at daterange, - CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_rng - Table "public.temporal_rng" - Column | Type | Collation | Nullable | Default -----------+-----------+-----------+----------+--------- - id | int4range | | not null | - valid_at | daterange | | not null | -Indexes: - "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) - -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; - pg_get_constraintdef ---------------------------------------------- - PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -(1 row) - -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; - pg_get_indexdef -------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) -(1 row) - --- PK with two columns plus a range: --- We don't drop this table because tests below also need multiple scalar columns. -CREATE TABLE temporal_rng2 ( - id1 int4range, - id2 int4range, - valid_at daterange, - CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) -); -\d temporal_rng2 - Table "public.temporal_rng2" - Column | Type | Collation | Nullable | Default -----------+-----------+-----------+----------+--------- - id1 | int4range | | not null | - id2 | int4range | | not null | - valid_at | daterange | | not null | -Indexes: - "temporal_rng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) - -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; - pg_get_constraintdef ---------------------------------------------------- - PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) -(1 row) - -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; - pg_get_indexdef ---------------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_rng2_pk ON temporal_rng2 USING gist (id1, id2, valid_at) -(1 row) - --- PK with a custom range type: -CREATE TYPE textrange2 AS range (subtype=text, collation="C"); -CREATE TABLE temporal_rng3 ( - id int4range, - valid_at textrange2, - CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; -DROP TABLE temporal_rng3; -DROP TYPE textrange2; --- PK with one column plus a multirange: -CREATE TABLE temporal_mltrng ( - id int4range, - valid_at datemultirange, - CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_mltrng - Table "public.temporal_mltrng" - Column | Type | Collation | Nullable | Default -----------+----------------+-----------+----------+--------- - id | int4range | | not null | - valid_at | datemultirange | | not null | -Indexes: - "temporal_mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) - --- PK with two columns plus a multirange: --- We don't drop this table because tests below also need multiple scalar columns. -CREATE TABLE temporal_mltrng2 ( - id1 int4range, - id2 int4range, - valid_at datemultirange, - CONSTRAINT temporal_mltrng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) -); -\d temporal_mltrng2 - Table "public.temporal_mltrng2" - Column | Type | Collation | Nullable | Default -----------+----------------+-----------+----------+--------- - id1 | int4range | | not null | - id2 | int4range | | not null | - valid_at | datemultirange | | not null | -Indexes: - "temporal_mltrng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) - -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; - pg_get_constraintdef ---------------------------------------------------- - PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) -(1 row) - -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; - pg_get_indexdef ---------------------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_mltrng2_pk ON temporal_mltrng2 USING gist (id1, id2, valid_at) -(1 row) - --- UNIQUE with no columns just WITHOUT OVERLAPS: -CREATE TABLE temporal_rng3 ( - valid_at daterange, - CONSTRAINT temporal_rng3_uq UNIQUE (valid_at WITHOUT OVERLAPS) -); -ERROR: constraint using WITHOUT OVERLAPS needs at least two columns --- UNIQUE with a range column/PERIOD that isn't there: -CREATE TABLE temporal_rng3 ( - id INTEGER, - CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -); -ERROR: column "valid_at" named in key does not exist -LINE 3: CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OV... - ^ --- UNIQUE with a non-range column: -CREATE TABLE temporal_rng3 ( - id int4range, - valid_at TEXT, - CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -); -ERROR: data type text has no default operator class for access method "gist" -HINT: You must specify an operator class for the index or define a default operator class for the data type. --- UNIQUE with one column plus a range: -CREATE TABLE temporal_rng3 ( - id int4range, - valid_at daterange, - CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_rng3 - Table "public.temporal_rng3" - Column | Type | Collation | Nullable | Default -----------+-----------+-----------+----------+--------- - id | int4range | | | - valid_at | daterange | | | -Indexes: - "temporal_rng3_uq" UNIQUE (id, valid_at WITHOUT OVERLAPS) - -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; - pg_get_constraintdef ----------------------------------------- - UNIQUE (id, valid_at WITHOUT OVERLAPS) -(1 row) - -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; - pg_get_indexdef ---------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id, valid_at) -(1 row) - -DROP TABLE temporal_rng3; --- UNIQUE with two columns plus a range: -CREATE TABLE temporal_rng3 ( - id1 int4range, - id2 int4range, - valid_at daterange, - CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) -); -\d temporal_rng3 - Table "public.temporal_rng3" - Column | Type | Collation | Nullable | Default -----------+-----------+-----------+----------+--------- - id1 | int4range | | | - id2 | int4range | | | - valid_at | daterange | | | -Indexes: - "temporal_rng3_uq" UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) - -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; - pg_get_constraintdef ----------------------------------------------- - UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) -(1 row) - -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; - pg_get_indexdef ---------------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id1, id2, valid_at) -(1 row) - -DROP TABLE temporal_rng3; --- UNIQUE with a custom range type: -CREATE TYPE textrange2 AS range (subtype=text, collation="C"); -CREATE TABLE temporal_rng3 ( - id int4range, - valid_at textrange2, - CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq; -DROP TABLE temporal_rng3; -DROP TYPE textrange2; --- --- test ALTER TABLE ADD CONSTRAINT --- -DROP TABLE temporal_rng; -CREATE TABLE temporal_rng ( - id int4range, - valid_at daterange -); -ALTER TABLE temporal_rng - ADD CONSTRAINT temporal_rng_pk - PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); --- PK with USING INDEX (not possible): -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange -); -CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at); -ALTER TABLE temporal3 - ADD CONSTRAINT temporal3_pk - PRIMARY KEY USING INDEX idx_temporal3_uq; -ERROR: "idx_temporal3_uq" is not a unique index -LINE 2: ADD CONSTRAINT temporal3_pk - ^ -DETAIL: Cannot create a primary key or unique constraint using such an index. -DROP TABLE temporal3; --- UNIQUE with USING INDEX (not possible): -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange -); -CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at); -ALTER TABLE temporal3 - ADD CONSTRAINT temporal3_uq - UNIQUE USING INDEX idx_temporal3_uq; -ERROR: "idx_temporal3_uq" is not a unique index -LINE 2: ADD CONSTRAINT temporal3_uq - ^ -DETAIL: Cannot create a primary key or unique constraint using such an index. -DROP TABLE temporal3; --- UNIQUE with USING [UNIQUE] INDEX (possible but not a temporal constraint): -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange -); -CREATE UNIQUE INDEX idx_temporal3_uq ON temporal3 (id, valid_at); -ALTER TABLE temporal3 - ADD CONSTRAINT temporal3_uq - UNIQUE USING INDEX idx_temporal3_uq; -NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "idx_temporal3_uq" to "temporal3_uq" -DROP TABLE temporal3; --- Add range column and the PK at the same time -CREATE TABLE temporal3 ( - id int4range -); -ALTER TABLE temporal3 - ADD COLUMN valid_at daterange, - ADD CONSTRAINT temporal3_pk - PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); -DROP TABLE temporal3; --- Add range column and UNIQUE constraint at the same time -CREATE TABLE temporal3 ( - id int4range -); -ALTER TABLE temporal3 - ADD COLUMN valid_at daterange, - ADD CONSTRAINT temporal3_uq - UNIQUE (id, valid_at WITHOUT OVERLAPS); -DROP TABLE temporal3; --- --- test PK inserts --- --- okay: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL)); --- should fail: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); -ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk" -DETAIL: Key (id, valid_at)=([1,2), [2018-01-01,2018-01-05)) conflicts with existing key (id, valid_at)=([1,2), [2018-01-02,2018-02-03)). -INSERT INTO temporal_rng (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05')); -ERROR: null value in column "id" of relation "temporal_rng" violates not-null constraint -DETAIL: Failing row contains (null, [2018-01-01,2018-01-05)). -INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', NULL); -ERROR: null value in column "valid_at" of relation "temporal_rng" violates not-null constraint -DETAIL: Failing row contains ([3,4), null). --- okay: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL))); --- should fail: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); -ERROR: conflicting key value violates exclusion constraint "temporal_mltrng_pk" -DETAIL: Key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}) conflicts with existing key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}). -INSERT INTO temporal_mltrng (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05'))); -ERROR: null value in column "id" of relation "temporal_mltrng" violates not-null constraint -DETAIL: Failing row contains (null, {[2018-01-01,2018-01-05)}). -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', NULL); -ERROR: null value in column "valid_at" of relation "temporal_mltrng" violates not-null constraint -DETAIL: Failing row contains ([3,4), null). -SELECT * FROM temporal_mltrng ORDER BY id, valid_at; - id | valid_at --------+--------------------------- - [1,2) | {[2018-01-02,2018-02-03)} - [1,2) | {[2018-03-03,2018-04-04)} - [2,3) | {[2018-01-01,2018-01-05)} - [3,4) | {[2018-01-01,)} -(4 rows) - --- --- test a range with both a PK and a UNIQUE constraint --- -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange, - id2 int8range, - name TEXT, - CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal3_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS) -); -INSERT INTO temporal3 (id, valid_at, id2, name) - VALUES - ('[1,2)', daterange('2000-01-01', '2010-01-01'), '[7,8)', 'foo'), - ('[2,3)', daterange('2000-01-01', '2010-01-01'), '[9,10)', 'bar') -; -DROP TABLE temporal3; --- --- test changing the PK's dependencies --- -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange, - CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal3 ALTER COLUMN valid_at DROP NOT NULL; -ERROR: column "valid_at" is in a primary key -ALTER TABLE temporal3 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at), upper(valid_at)); -ALTER TABLE temporal3 RENAME COLUMN valid_at TO valid_thru; -ALTER TABLE temporal3 DROP COLUMN valid_thru; -DROP TABLE temporal3; --- --- test PARTITION BY for ranges --- --- temporal PRIMARY KEY: -CREATE TABLE temporal_partitioned ( - id int4range, - valid_at daterange, - name text, - CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -) PARTITION BY LIST (id); -CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); -CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); -INSERT INTO temporal_partitioned (id, valid_at, name) VALUES - ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), - ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), - ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one - [3,4) | [2000-01-01,2010-01-01) | three -(3 rows) - -SELECT * FROM tp1 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------ - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one -(2 rows) - -SELECT * FROM tp2 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [3,4) | [2000-01-01,2010-01-01) | three -(1 row) - -DROP TABLE temporal_partitioned; --- temporal UNIQUE: -CREATE TABLE temporal_partitioned ( - id int4range, - valid_at daterange, - name text, - CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -) PARTITION BY LIST (id); -CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); -CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); -INSERT INTO temporal_partitioned (id, valid_at, name) VALUES - ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), - ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), - ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one - [3,4) | [2000-01-01,2010-01-01) | three -(3 rows) - -SELECT * FROM tp1 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------ - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one -(2 rows) - -SELECT * FROM tp2 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [3,4) | [2000-01-01,2010-01-01) | three -(1 row) - -DROP TABLE temporal_partitioned; --- ALTER TABLE REPLICA IDENTITY --- (should fail) -ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk; -ERROR: cannot use non-unique index "temporal_rng_pk" as replica identity --- --- ON CONFLICT --- -TRUNCATE temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING; --- id matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT DO NOTHING; --- date matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING; -SELECT * FROM temporal_rng ORDER BY id, valid_at; - id | valid_at --------+------------------------- - [1,2) | [2000-01-01,2010-01-01) - [1,2) | [2010-01-01,2020-01-01) - [2,3) | [2005-01-01,2006-01-01) -(3 rows) - -TRUNCATE temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification --- id matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification --- date matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -SELECT * FROM temporal_rng ORDER BY id, valid_at; - id | valid_at --------+------------------------- - [1,2) | [2000-01-01,2010-01-01) -(1 row) - -TRUNCATE temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO NOTHING; --- id matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO NOTHING; --- date matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO NOTHING; -SELECT * FROM temporal_rng ORDER BY id, valid_at; - id | valid_at --------+------------------------- - [1,2) | [2000-01-01,2010-01-01) - [1,2) | [2010-01-01,2020-01-01) - [2,3) | [2005-01-01,2006-01-01) -(3 rows) - -TRUNCATE temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[2,3)'; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification --- id matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[3,4)'; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification --- date matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[4,5)'; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -SELECT * FROM temporal_rng ORDER BY id, valid_at; - id | valid_at --------+------------------------- - [1,2) | [2000-01-01,2010-01-01) -(1 row) - -TRUNCATE temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO UPDATE SET id = EXCLUDED.id + '[2,3)'; -ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints --- id matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO UPDATE SET id = EXCLUDED.id + '[3,4)'; -ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints --- date matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO UPDATE SET id = EXCLUDED.id + '[4,5)'; -ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -SELECT * FROM temporal_rng ORDER BY id, valid_at; - id | valid_at --------+------------------------- - [1,2) | [2000-01-01,2010-01-01) -(1 row) - --- with a UNIQUE constraint: -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange, - CONSTRAINT temporal3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -); -TRUNCATE temporal3; -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING; --- id matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT DO NOTHING; --- date matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING; -SELECT * FROM temporal3 ORDER BY id, valid_at; - id | valid_at --------+------------------------- - [1,2) | [2000-01-01,2010-01-01) - [1,2) | [2010-01-01,2020-01-01) - [2,3) | [2005-01-01,2006-01-01) -(3 rows) - -TRUNCATE temporal3; -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification --- id matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification --- date matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -SELECT * FROM temporal3 ORDER BY id, valid_at; - id | valid_at --------+------------------------- - [1,2) | [2000-01-01,2010-01-01) -(1 row) - -TRUNCATE temporal3; -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING; --- id matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING; --- date matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING; -SELECT * FROM temporal3 ORDER BY id, valid_at; - id | valid_at --------+------------------------- - [1,2) | [2000-01-01,2010-01-01) - [1,2) | [2010-01-01,2020-01-01) - [2,3) | [2005-01-01,2006-01-01) -(3 rows) - -TRUNCATE temporal3; -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[2,3)'; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification --- id matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[3,4)'; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification --- date matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[4,5)'; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -SELECT * FROM temporal3 ORDER BY id, valid_at; - id | valid_at --------+------------------------- - [1,2) | [2000-01-01,2010-01-01) -(1 row) - -TRUNCATE temporal3; -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO UPDATE SET id = EXCLUDED.id + '[2,3)'; -ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints --- id matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO UPDATE SET id = EXCLUDED.id + '[3,4)'; -ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints --- date matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO UPDATE SET id = EXCLUDED.id + '[4,5)'; -ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -SELECT * FROM temporal3 ORDER BY id, valid_at; - id | valid_at --------+------------------------- - [1,2) | [2000-01-01,2010-01-01) -(1 row) - -DROP TABLE temporal3; --- --- test FK dependencies --- --- can't drop a range referenced by an FK, unless with CASCADE -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange, - CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal3 (id, PERIOD valid_at) -); -ALTER TABLE temporal3 DROP COLUMN valid_at; -ERROR: cannot drop column valid_at of table temporal3 because other objects depend on it -DETAIL: constraint temporal_fk_rng2rng_fk on table temporal_fk_rng2rng depends on column valid_at of table temporal3 -HINT: Use DROP ... CASCADE to drop the dependent objects too. -ALTER TABLE temporal3 DROP COLUMN valid_at CASCADE; -NOTICE: drop cascades to constraint temporal_fk_rng2rng_fk on table temporal_fk_rng2rng -DROP TABLE temporal_fk_rng2rng; -DROP TABLE temporal3; --- --- test FOREIGN KEY, range references range --- --- test table setup -DROP TABLE temporal_rng; -CREATE TABLE temporal_rng (id int4range, valid_at daterange); -ALTER TABLE temporal_rng - ADD CONSTRAINT temporal_rng_pk - PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); --- Can't create a FK with a mismatched range type -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at int4range, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id, PERIOD valid_at) -); -ERROR: foreign key constraint "temporal_fk_rng2rng_fk2" cannot be implemented -DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: int4range and daterange. --- works: PERIOD for both referenced and referencing -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id, PERIOD valid_at) -); -DROP TABLE temporal_fk_rng2rng; --- with mismatched PERIOD columns: --- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) --- REFERENCES part should specify PERIOD -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id, valid_at) -); -ERROR: foreign key uses PERIOD on the referencing table but not the referenced table --- (parent_id, valid_at) REFERENCES (id, valid_at) --- both should specify PERIOD: -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_rng (id, valid_at) -); -ERROR: foreign key must use PERIOD when referencing a primary using WITHOUT OVERLAPS --- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) --- FOREIGN KEY part should specify PERIOD -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_rng (id, PERIOD valid_at) -); -ERROR: foreign key uses PERIOD on the referenced table but not the referencing table --- (parent_id, valid_at) REFERENCES [implicit] --- FOREIGN KEY part should specify PERIOD -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_rng -); -ERROR: foreign key uses PERIOD on the referenced table but not the referencing table --- (parent_id, PERIOD valid_at) REFERENCES (id) -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id) -); -ERROR: foreign key uses PERIOD on the referencing table but not the referenced table --- (parent_id) REFERENCES (id, PERIOD valid_at) -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) - REFERENCES temporal_rng (id, PERIOD valid_at) -); -ERROR: foreign key uses PERIOD on the referenced table but not the referencing table --- with inferred PK on the referenced table: --- (parent_id, PERIOD valid_at) REFERENCES [implicit] -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng -); -DROP TABLE temporal_fk_rng2rng; --- (parent_id) REFERENCES [implicit] -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) - REFERENCES temporal_rng -); -ERROR: foreign key uses PERIOD on the referenced table but not the referencing table --- should fail because of duplicate referenced columns: -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id) - REFERENCES temporal_rng (id, PERIOD id) -); -ERROR: foreign key referenced-columns list must not contain duplicates --- Two scalar columns -DROP TABLE temporal_rng2; -CREATE TABLE temporal_rng2 ( - id1 int4range, - id2 int4range, - valid_at daterange, - CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) -); -CREATE TABLE temporal_fk2_rng2rng ( - id int4range, - valid_at daterange, - parent_id1 int4range, - parent_id2 int4range, - CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk2_rng2rng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) - REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at) -); -\d temporal_fk2_rng2rng - Table "public.temporal_fk2_rng2rng" - Column | Type | Collation | Nullable | Default -------------+-----------+-----------+----------+--------- - id | int4range | | not null | - valid_at | daterange | | not null | - parent_id1 | int4range | | | - parent_id2 | int4range | | | -Indexes: - "temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -Foreign-key constraints: - "temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at) - -DROP TABLE temporal_fk2_rng2rng; --- --- test ALTER TABLE ADD CONSTRAINT --- -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id, PERIOD valid_at); --- Two scalar columns: -CREATE TABLE temporal_fk2_rng2rng ( - id int4range, - valid_at daterange, - parent_id1 int4range, - parent_id2 int4range, - CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_fk2_rng2rng - ADD CONSTRAINT temporal_fk2_rng2rng_fk - FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) - REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at); -\d temporal_fk2_rng2rng - Table "public.temporal_fk2_rng2rng" - Column | Type | Collation | Nullable | Default -------------+-----------+-----------+----------+--------- - id | int4range | | not null | - valid_at | daterange | | not null | - parent_id1 | int4range | | | - parent_id2 | int4range | | | -Indexes: - "temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -Foreign-key constraints: - "temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at) - --- with inferred PK on the referenced table, and wrong column type: -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk, - ALTER COLUMN valid_at TYPE tsrange USING tsrange(lower(valid_at), upper(valid_at)); -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; -ERROR: foreign key constraint "temporal_fk_rng2rng_fk" cannot be implemented -DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: tsrange and daterange. -ALTER TABLE temporal_fk_rng2rng - ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date); --- with inferred PK on the referenced table: -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; --- should fail because of duplicate referenced columns: -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk2 - FOREIGN KEY (parent_id, PERIOD parent_id) - REFERENCES temporal_rng (id, PERIOD id); -ERROR: foreign key referenced-columns list must not contain duplicates --- --- test with rows already --- -DELETE FROM temporal_fk_rng2rng; -DELETE FROM temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[1,2)', daterange('2018-01-02', '2018-02-03')), - ('[1,2)', daterange('2018-03-03', '2018-04-04')), - ('[2,3)', daterange('2018-01-01', '2018-01-05')), - ('[3,4)', daterange('2018-01-01', NULL)); -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-01'), '[1,2)'); -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); --- should fail: -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; -ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=([1,2), [2018-01-02,2018-04-01)) is not present in table "temporal_rng". --- okay again: -DELETE FROM temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; --- --- test pg_get_constraintdef --- -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; - pg_get_constraintdef ---------------------------------------------------------------------------------------- - FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) -(1 row) - --- --- test FK referencing inserts --- -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-01'), '[1,2)'); --- should fail: -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); -ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=([1,2), [2018-01-02,2018-04-01)) is not present in table "temporal_rng". --- now it should work: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-02-03', '2018-03-03')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); --- --- test FK referencing updates --- -UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-03-01') WHERE id = '[1,2)'; --- should fail: -UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-05-01') WHERE id = '[1,2)'; -ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=([1,2), [2018-01-02,2018-05-01)) is not present in table "temporal_rng". -UPDATE temporal_fk_rng2rng SET parent_id = '[8,9)' WHERE id = '[1,2)'; -ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=([8,9), [2018-01-02,2018-03-01)) is not present in table "temporal_rng". --- ALTER FK DEFERRABLE -BEGIN; - INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); - INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - DELETE FROM temporal_rng WHERE id = '[5,6)'; --should not fail yet. -COMMIT; -- should fail here. -ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". --- --- test FK referenced updates NO ACTION --- -TRUNCATE temporal_rng, temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON UPDATE NO ACTION; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_rng WHERE id = '[5,6)'; -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') -WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- a PK update that fails because both are referenced: -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') -WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". --- a PK update that fails because both are referenced, but not 'til commit: -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -COMMIT; -ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". --- changing the scalar part fails: -UPDATE temporal_rng SET id = '[7,8)' -WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". --- then delete the objecting FK record and the same PK update succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') -WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); --- --- test FK referenced updates RESTRICT --- -TRUNCATE temporal_rng, temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON UPDATE RESTRICT; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_rng WHERE id = '[5,6)'; -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') -WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- a PK update that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". -ROLLBACK; --- changing the scalar part fails: -UPDATE temporal_rng SET id = '[7,8)' -WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". --- then delete the objecting FK record and the same PK update succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') -WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); --- --- test FK referenced deletes NO ACTION --- -TRUNCATE temporal_rng, temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; --- a PK delete that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -DELETE FROM temporal_rng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- a PK delete that fails because both are referenced: -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". --- a PK delete that fails because both are referenced, but not 'til commit: -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -COMMIT; -ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". --- then delete the objecting FK record and the same PK delete succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); --- --- test FK referenced deletes RESTRICT --- -TRUNCATE temporal_rng, temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON DELETE RESTRICT; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -DELETE FROM temporal_rng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- a PK delete that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". -ROLLBACK; --- then delete the objecting FK record and the same PK delete succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); --- --- test ON UPDATE/DELETE options --- --- test FK referenced updates CASCADE -INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk, - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON DELETE CASCADE ON UPDATE CASCADE; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD --- test FK referenced updates SET NULL -INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk, - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON DELETE SET NULL ON UPDATE SET NULL; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD --- test FK referenced updates SET DEFAULT -INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); -ALTER TABLE temporal_fk_rng2rng - ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', - DROP CONSTRAINT temporal_fk_rng2rng_fk, - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD --- --- test FOREIGN KEY, multirange references multirange --- --- Can't create a FK with a mismatched multirange type -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at int4multirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) -); -ERROR: foreign key constraint "temporal_fk_mltrng2mltrng_fk2" cannot be implemented -DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: int4multirange and datemultirange. -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) -); -DROP TABLE temporal_fk_mltrng2mltrng; --- with mismatched PERIOD columns: --- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) --- REFERENCES part should specify PERIOD -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, valid_at) -); -ERROR: foreign key uses PERIOD on the referencing table but not the referenced table --- (parent_id, valid_at) REFERENCES (id, valid_at) --- both should specify PERIOD: -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_mltrng (id, valid_at) -); -ERROR: foreign key must use PERIOD when referencing a primary using WITHOUT OVERLAPS --- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) --- FOREIGN KEY part should specify PERIOD -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) -); -ERROR: foreign key uses PERIOD on the referenced table but not the referencing table --- (parent_id, valid_at) REFERENCES [implicit] --- FOREIGN KEY part should specify PERIOD -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_mltrng -); -ERROR: foreign key uses PERIOD on the referenced table but not the referencing table --- (parent_id, PERIOD valid_at) REFERENCES (id) -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id) -); -ERROR: foreign key uses PERIOD on the referencing table but not the referenced table --- (parent_id) REFERENCES (id, PERIOD valid_at) -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) - REFERENCES temporal_mltrng (id, PERIOD valid_at) -); -ERROR: foreign key uses PERIOD on the referenced table but not the referencing table --- with inferred PK on the referenced table: --- (parent_id, PERIOD valid_at) REFERENCES [implicit] -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng -); -DROP TABLE temporal_fk_mltrng2mltrng; --- (parent_id) REFERENCES [implicit] -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) - REFERENCES temporal_mltrng -); -ERROR: foreign key uses PERIOD on the referenced table but not the referencing table --- should fail because of duplicate referenced columns: -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD parent_id) - REFERENCES temporal_mltrng (id, PERIOD id) -); -ERROR: foreign key referenced-columns list must not contain duplicates --- Two scalar columns -CREATE TABLE temporal_fk2_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id1 int4range, - parent_id2 int4range, - CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk2_mltrng2mltrng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) - REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at) -); -\d temporal_fk2_mltrng2mltrng - Table "public.temporal_fk2_mltrng2mltrng" - Column | Type | Collation | Nullable | Default -------------+----------------+-----------+----------+--------- - id | int4range | | not null | - valid_at | datemultirange | | not null | - parent_id1 | int4range | | | - parent_id2 | int4range | | | -Indexes: - "temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -Foreign-key constraints: - "temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at) - -DROP TABLE temporal_fk2_mltrng2mltrng; --- --- test ALTER TABLE ADD CONSTRAINT --- -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at); --- Two scalar columns: -CREATE TABLE temporal_fk2_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id1 int4range, - parent_id2 int4range, - CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_fk2_mltrng2mltrng - ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk - FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) - REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at); -\d temporal_fk2_mltrng2mltrng - Table "public.temporal_fk2_mltrng2mltrng" - Column | Type | Collation | Nullable | Default -------------+----------------+-----------+----------+--------- - id | int4range | | not null | - valid_at | datemultirange | | not null | - parent_id1 | int4range | | | - parent_id2 | int4range | | | -Indexes: - "temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -Foreign-key constraints: - "temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at) - --- should fail because of duplicate referenced columns: -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk2 - FOREIGN KEY (parent_id, PERIOD parent_id) - REFERENCES temporal_mltrng (id, PERIOD id); -ERROR: foreign key referenced-columns list must not contain duplicates --- --- test with rows already --- -DELETE FROM temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)'); -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at); -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); --- should fail: -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at); -ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" -DETAIL: Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-04-01)}) is not present in table "temporal_mltrng". --- okay again: -DELETE FROM temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at); --- --- test pg_get_constraintdef --- -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_mltrng2mltrng_fk'; - pg_get_constraintdef ------------------------------------------------------------------------------------------- - FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng(id, PERIOD valid_at) -(1 row) - --- --- test FK referencing inserts --- -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)'); --- should fail: -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); -ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" -DETAIL: Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-04-01)}) is not present in table "temporal_mltrng". --- now it should work: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-02-03', '2018-03-03'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); --- --- test FK referencing updates --- -UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-03-01')) WHERE id = '[1,2)'; --- should fail: -UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-05-01')) WHERE id = '[1,2)'; -ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" -DETAIL: Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-05-01)}) is not present in table "temporal_mltrng". -UPDATE temporal_fk_mltrng2mltrng SET parent_id = '[8,9)' WHERE id = '[1,2)'; -ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" -DETAIL: Key (parent_id, valid_at)=([8,9), {[2018-01-02,2018-03-01)}) is not present in table "temporal_mltrng". --- ALTER FK DEFERRABLE -BEGIN; - INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); - INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES - ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --should not fail yet. -COMMIT; -- should fail here. -ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". --- --- test FK referenced updates NO ACTION --- -TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) - ON UPDATE NO ACTION; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) -WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- a PK update that fails because both are referenced: -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) -WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". --- a PK update that fails because both are referenced, but not 'til commit: -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -COMMIT; -ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". --- changing the scalar part fails: -UPDATE temporal_mltrng SET id = '[7,8)' -WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". --- --- test FK referenced updates RESTRICT --- -TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) - ON UPDATE RESTRICT; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) -WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- a PK update that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". -ROLLBACK; --- changing the scalar part fails: -UPDATE temporal_mltrng SET id = '[7,8)' -WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". --- --- test FK referenced deletes NO ACTION --- -TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at); --- a PK delete that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- a PK delete that fails because both are referenced: -DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". --- a PK delete that fails because both are referenced, but not 'til commit: -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -COMMIT; -ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". --- --- test FK referenced deletes RESTRICT --- -TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) - ON DELETE RESTRICT; -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- a PK delete that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". -ROLLBACK; --- --- test FOREIGN KEY, box references box --- (not allowed: PERIOD part must be a range or multirange) --- -CREATE TABLE temporal_box ( - id int4range, - valid_at box, - CONSTRAINT temporal_box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_box - Table "public.temporal_box" - Column | Type | Collation | Nullable | Default -----------+-----------+-----------+----------+--------- - id | int4range | | not null | - valid_at | box | | not null | -Indexes: - "temporal_box_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) - -CREATE TABLE temporal_fk_box2box ( - id int4range, - valid_at box, - parent_id int4range, - CONSTRAINT temporal_fk_box2box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_box2box_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_box (id, PERIOD valid_at) -); -ERROR: invalid type for PERIOD part of foreign key -DETAIL: Only range and multirange are supported. --- --- FK between partitioned tables --- -CREATE TABLE temporal_partitioned_rng ( - id int4range, - valid_at daterange, - name text, - CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -) PARTITION BY LIST (id); -CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); -CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); -INSERT INTO temporal_partitioned_rng (id, valid_at, name) VALUES - ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), - ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), - ('[2,3)', daterange('2000-01-01', '2010-01-01'), 'two'); -CREATE TABLE temporal_partitioned_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_partitioned_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng (id, PERIOD valid_at) -) PARTITION BY LIST (id); -CREATE TABLE tfkp1 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); -CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); --- --- partitioned FK referencing inserts --- -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[1,2)', daterange('2000-01-01', '2000-02-15'), '[1,2)'), - ('[1,2)', daterange('2001-01-01', '2002-01-01'), '[2,3)'), - ('[2,3)', daterange('2000-01-01', '2000-02-15'), '[1,2)'); --- should fail: -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[3,4)', daterange('2010-01-01', '2010-02-15'), '[1,2)'); -ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=([1,2), [2010-01-01,2010-02-15)) is not present in table "temporal_partitioned_rng". -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[3,4)', daterange('2000-01-01', '2000-02-15'), '[3,4)'); -ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=([3,4), [2000-01-01,2000-02-15)) is not present in table "temporal_partitioned_rng". --- --- partitioned FK referencing updates --- -UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-02-13') WHERE id = '[2,3)'; --- move a row from the first partition to the second -UPDATE temporal_partitioned_fk_rng2rng SET id = '[4,5)' WHERE id = '[1,2)'; --- move a row from the second partition to the first -UPDATE temporal_partitioned_fk_rng2rng SET id = '[1,2)' WHERE id = '[4,5)'; --- should fail: -UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-04-01') WHERE id = '[1,2)'; -ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey" -DETAIL: Key (id, valid_at)=([1,2), [2000-01-01,2000-04-01)) conflicts with existing key (id, valid_at)=([1,2), [2000-01-01,2000-04-01)). --- --- partitioned FK referenced updates NO ACTION --- -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2016-01-01', '2016-02-01')); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)'; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". --- --- partitioned FK referenced deletes NO ACTION --- -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". --- --- partitioned FK referenced updates RESTRICT --- -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -ALTER TABLE temporal_partitioned_fk_rng2rng - DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk; -ALTER TABLE temporal_partitioned_fk_rng2rng - ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng - ON DELETE RESTRICT; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2016-01-01', '2016-02-01')); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)'; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". --- --- partitioned FK referenced deletes RESTRICT --- -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". --- --- partitioned FK referenced updates CASCADE --- -ALTER TABLE temporal_partitioned_fk_rng2rng - DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, - ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng - ON DELETE CASCADE ON UPDATE CASCADE; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD --- --- partitioned FK referenced deletes CASCADE --- --- --- partitioned FK referenced updates SET NULL --- -ALTER TABLE temporal_partitioned_fk_rng2rng - DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, - ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng - ON DELETE SET NULL ON UPDATE SET NULL; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD --- --- partitioned FK referenced deletes SET NULL --- --- --- partitioned FK referenced updates SET DEFAULT --- -ALTER TABLE temporal_partitioned_fk_rng2rng - ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', - DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, - ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng - ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD --- --- partitioned FK referenced deletes SET DEFAULT --- -DROP TABLE temporal_partitioned_fk_rng2rng; -DROP TABLE temporal_partitioned_rng; -RESET datestyle; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 675c5676171..969ced994f4 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -78,7 +78,7 @@ test: brin_bloom brin_multi # psql depends on create_am # amutils depends on geometry, create_index_spgist, hash_index, brin # ---------- -test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions sysviews tsrf tid tidscan tidrangescan collate.utf8 collate.icu.utf8 incremental_sort create_role without_overlaps +test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions sysviews tsrf tid tidscan tidrangescan collate.utf8 collate.icu.utf8 incremental_sort create_role # collate.linux.utf8 and collate.icu.utf8 tests cannot be run in parallel with each other test: rules psql psql_crosstab amutils stats_ext collate.linux.utf8 collate.windows.win1252 diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql index ff5ed5eddeb..76470fcb3f6 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -251,10 +251,6 @@ SELECT segment_number, file_offset = :segment_size - 1 FROM pg_walfile_name_offset('0/0'::pg_lsn + :segment_size - 1), pg_split_walfile_name(file_name); --- test stratnum support functions -SELECT gist_stratnum_identity(3::smallint); -SELECT gist_stratnum_identity(18::smallint); - -- pg_current_logfile CREATE ROLE regress_current_logfile; -- not available by default diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql deleted file mode 100644 index 5d41a6bd628..00000000000 --- a/src/test/regress/sql/without_overlaps.sql +++ /dev/null @@ -1,1461 +0,0 @@ --- Tests for WITHOUT OVERLAPS. --- --- We leave behind several tables to test pg_dump etc: --- temporal_rng, temporal_rng2, --- temporal_fk_rng2rng. - -SET datestyle TO ISO, YMD; - --- --- test input parser --- - --- PK with no columns just WITHOUT OVERLAPS: - -CREATE TABLE temporal_rng ( - valid_at daterange, - CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS) -); - --- PK with a range column/PERIOD that isn't there: - -CREATE TABLE temporal_rng ( - id INTEGER, - CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); - --- PK with a non-range column: - -CREATE TABLE temporal_rng ( - id int4range, - valid_at TEXT, - CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); - --- PK with one column plus a range: - -CREATE TABLE temporal_rng ( - -- Since we can't depend on having btree_gist here, - -- use an int4range instead of an int. - -- (The rangetypes regression test uses the same trick.) - id int4range, - valid_at daterange, - CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_rng -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; - --- PK with two columns plus a range: --- We don't drop this table because tests below also need multiple scalar columns. -CREATE TABLE temporal_rng2 ( - id1 int4range, - id2 int4range, - valid_at daterange, - CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) -); -\d temporal_rng2 -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; - --- PK with a custom range type: -CREATE TYPE textrange2 AS range (subtype=text, collation="C"); -CREATE TABLE temporal_rng3 ( - id int4range, - valid_at textrange2, - CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; -DROP TABLE temporal_rng3; -DROP TYPE textrange2; - --- PK with one column plus a multirange: -CREATE TABLE temporal_mltrng ( - id int4range, - valid_at datemultirange, - CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_mltrng - --- PK with two columns plus a multirange: --- We don't drop this table because tests below also need multiple scalar columns. -CREATE TABLE temporal_mltrng2 ( - id1 int4range, - id2 int4range, - valid_at datemultirange, - CONSTRAINT temporal_mltrng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) -); -\d temporal_mltrng2 -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; - --- UNIQUE with no columns just WITHOUT OVERLAPS: - -CREATE TABLE temporal_rng3 ( - valid_at daterange, - CONSTRAINT temporal_rng3_uq UNIQUE (valid_at WITHOUT OVERLAPS) -); - --- UNIQUE with a range column/PERIOD that isn't there: - -CREATE TABLE temporal_rng3 ( - id INTEGER, - CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -); - --- UNIQUE with a non-range column: - -CREATE TABLE temporal_rng3 ( - id int4range, - valid_at TEXT, - CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -); - --- UNIQUE with one column plus a range: - -CREATE TABLE temporal_rng3 ( - id int4range, - valid_at daterange, - CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_rng3 -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; -DROP TABLE temporal_rng3; - --- UNIQUE with two columns plus a range: -CREATE TABLE temporal_rng3 ( - id1 int4range, - id2 int4range, - valid_at daterange, - CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) -); -\d temporal_rng3 -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; -SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; -DROP TABLE temporal_rng3; - --- UNIQUE with a custom range type: -CREATE TYPE textrange2 AS range (subtype=text, collation="C"); -CREATE TABLE temporal_rng3 ( - id int4range, - valid_at textrange2, - CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq; -DROP TABLE temporal_rng3; -DROP TYPE textrange2; - --- --- test ALTER TABLE ADD CONSTRAINT --- - -DROP TABLE temporal_rng; -CREATE TABLE temporal_rng ( - id int4range, - valid_at daterange -); -ALTER TABLE temporal_rng - ADD CONSTRAINT temporal_rng_pk - PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); - --- PK with USING INDEX (not possible): -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange -); -CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at); -ALTER TABLE temporal3 - ADD CONSTRAINT temporal3_pk - PRIMARY KEY USING INDEX idx_temporal3_uq; -DROP TABLE temporal3; - --- UNIQUE with USING INDEX (not possible): -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange -); -CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at); -ALTER TABLE temporal3 - ADD CONSTRAINT temporal3_uq - UNIQUE USING INDEX idx_temporal3_uq; -DROP TABLE temporal3; - --- UNIQUE with USING [UNIQUE] INDEX (possible but not a temporal constraint): -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange -); -CREATE UNIQUE INDEX idx_temporal3_uq ON temporal3 (id, valid_at); -ALTER TABLE temporal3 - ADD CONSTRAINT temporal3_uq - UNIQUE USING INDEX idx_temporal3_uq; -DROP TABLE temporal3; - --- Add range column and the PK at the same time -CREATE TABLE temporal3 ( - id int4range -); -ALTER TABLE temporal3 - ADD COLUMN valid_at daterange, - ADD CONSTRAINT temporal3_pk - PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); -DROP TABLE temporal3; - --- Add range column and UNIQUE constraint at the same time -CREATE TABLE temporal3 ( - id int4range -); -ALTER TABLE temporal3 - ADD COLUMN valid_at daterange, - ADD CONSTRAINT temporal3_uq - UNIQUE (id, valid_at WITHOUT OVERLAPS); -DROP TABLE temporal3; - --- --- test PK inserts --- - --- okay: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL)); - --- should fail: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); -INSERT INTO temporal_rng (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', NULL); - --- okay: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL))); - --- should fail: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', NULL); - -SELECT * FROM temporal_mltrng ORDER BY id, valid_at; - --- --- test a range with both a PK and a UNIQUE constraint --- - -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange, - id2 int8range, - name TEXT, - CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal3_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS) -); -INSERT INTO temporal3 (id, valid_at, id2, name) - VALUES - ('[1,2)', daterange('2000-01-01', '2010-01-01'), '[7,8)', 'foo'), - ('[2,3)', daterange('2000-01-01', '2010-01-01'), '[9,10)', 'bar') -; -DROP TABLE temporal3; - --- --- test changing the PK's dependencies --- - -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange, - CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); - -ALTER TABLE temporal3 ALTER COLUMN valid_at DROP NOT NULL; -ALTER TABLE temporal3 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at), upper(valid_at)); -ALTER TABLE temporal3 RENAME COLUMN valid_at TO valid_thru; -ALTER TABLE temporal3 DROP COLUMN valid_thru; -DROP TABLE temporal3; - --- --- test PARTITION BY for ranges --- - --- temporal PRIMARY KEY: -CREATE TABLE temporal_partitioned ( - id int4range, - valid_at daterange, - name text, - CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -) PARTITION BY LIST (id); -CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); -CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); -INSERT INTO temporal_partitioned (id, valid_at, name) VALUES - ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), - ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), - ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; -SELECT * FROM tp1 ORDER BY id, valid_at; -SELECT * FROM tp2 ORDER BY id, valid_at; -DROP TABLE temporal_partitioned; - --- temporal UNIQUE: -CREATE TABLE temporal_partitioned ( - id int4range, - valid_at daterange, - name text, - CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -) PARTITION BY LIST (id); -CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); -CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); -INSERT INTO temporal_partitioned (id, valid_at, name) VALUES - ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), - ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), - ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; -SELECT * FROM tp1 ORDER BY id, valid_at; -SELECT * FROM tp2 ORDER BY id, valid_at; -DROP TABLE temporal_partitioned; - --- ALTER TABLE REPLICA IDENTITY --- (should fail) -ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk; - --- --- ON CONFLICT --- - -TRUNCATE temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING; --- id matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT DO NOTHING; --- date matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING; -SELECT * FROM temporal_rng ORDER BY id, valid_at; - -TRUNCATE temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; --- id matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; --- date matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; -SELECT * FROM temporal_rng ORDER BY id, valid_at; - -TRUNCATE temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO NOTHING; --- id matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO NOTHING; --- date matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO NOTHING; -SELECT * FROM temporal_rng ORDER BY id, valid_at; - -TRUNCATE temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[2,3)'; --- id matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[3,4)'; --- date matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[4,5)'; -SELECT * FROM temporal_rng ORDER BY id, valid_at; - -TRUNCATE temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO UPDATE SET id = EXCLUDED.id + '[2,3)'; --- id matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO UPDATE SET id = EXCLUDED.id + '[3,4)'; --- date matches but no conflict -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO UPDATE SET id = EXCLUDED.id + '[4,5)'; -SELECT * FROM temporal_rng ORDER BY id, valid_at; - --- with a UNIQUE constraint: - -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange, - CONSTRAINT temporal3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) -); -TRUNCATE temporal3; -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING; --- id matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT DO NOTHING; --- date matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING; -SELECT * FROM temporal3 ORDER BY id, valid_at; - -TRUNCATE temporal3; -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; --- id matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; --- date matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING; -SELECT * FROM temporal3 ORDER BY id, valid_at; - -TRUNCATE temporal3; -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING; --- id matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING; --- date matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING; -SELECT * FROM temporal3 ORDER BY id, valid_at; - -TRUNCATE temporal3; -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[2,3)'; --- id matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[3,4)'; --- date matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[4,5)'; -SELECT * FROM temporal3 ORDER BY id, valid_at; - -TRUNCATE temporal3; -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); --- with a conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO UPDATE SET id = EXCLUDED.id + '[2,3)'; --- id matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO UPDATE SET id = EXCLUDED.id + '[3,4)'; --- date matches but no conflict -INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO UPDATE SET id = EXCLUDED.id + '[4,5)'; -SELECT * FROM temporal3 ORDER BY id, valid_at; - -DROP TABLE temporal3; - --- --- test FK dependencies --- - --- can't drop a range referenced by an FK, unless with CASCADE -CREATE TABLE temporal3 ( - id int4range, - valid_at daterange, - CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal3 (id, PERIOD valid_at) -); -ALTER TABLE temporal3 DROP COLUMN valid_at; -ALTER TABLE temporal3 DROP COLUMN valid_at CASCADE; -DROP TABLE temporal_fk_rng2rng; -DROP TABLE temporal3; - --- --- test FOREIGN KEY, range references range --- - --- test table setup -DROP TABLE temporal_rng; -CREATE TABLE temporal_rng (id int4range, valid_at daterange); -ALTER TABLE temporal_rng - ADD CONSTRAINT temporal_rng_pk - PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); - --- Can't create a FK with a mismatched range type -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at int4range, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id, PERIOD valid_at) -); - --- works: PERIOD for both referenced and referencing -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id, PERIOD valid_at) -); -DROP TABLE temporal_fk_rng2rng; - --- with mismatched PERIOD columns: - --- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) --- REFERENCES part should specify PERIOD -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id, valid_at) -); --- (parent_id, valid_at) REFERENCES (id, valid_at) --- both should specify PERIOD: -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_rng (id, valid_at) -); --- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) --- FOREIGN KEY part should specify PERIOD -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_rng (id, PERIOD valid_at) -); --- (parent_id, valid_at) REFERENCES [implicit] --- FOREIGN KEY part should specify PERIOD -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_rng -); --- (parent_id, PERIOD valid_at) REFERENCES (id) -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id) -); --- (parent_id) REFERENCES (id, PERIOD valid_at) -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) - REFERENCES temporal_rng (id, PERIOD valid_at) -); --- with inferred PK on the referenced table: --- (parent_id, PERIOD valid_at) REFERENCES [implicit] -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng -); -DROP TABLE temporal_fk_rng2rng; --- (parent_id) REFERENCES [implicit] -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) - REFERENCES temporal_rng -); - --- should fail because of duplicate referenced columns: -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id) - REFERENCES temporal_rng (id, PERIOD id) -); - --- Two scalar columns -DROP TABLE temporal_rng2; -CREATE TABLE temporal_rng2 ( - id1 int4range, - id2 int4range, - valid_at daterange, - CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) -); - -CREATE TABLE temporal_fk2_rng2rng ( - id int4range, - valid_at daterange, - parent_id1 int4range, - parent_id2 int4range, - CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk2_rng2rng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) - REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at) -); -\d temporal_fk2_rng2rng -DROP TABLE temporal_fk2_rng2rng; - --- --- test ALTER TABLE ADD CONSTRAINT --- - -CREATE TABLE temporal_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng (id, PERIOD valid_at); --- Two scalar columns: -CREATE TABLE temporal_fk2_rng2rng ( - id int4range, - valid_at daterange, - parent_id1 int4range, - parent_id2 int4range, - CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_fk2_rng2rng - ADD CONSTRAINT temporal_fk2_rng2rng_fk - FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) - REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at); -\d temporal_fk2_rng2rng - --- with inferred PK on the referenced table, and wrong column type: -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk, - ALTER COLUMN valid_at TYPE tsrange USING tsrange(lower(valid_at), upper(valid_at)); -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; -ALTER TABLE temporal_fk_rng2rng - ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date); - --- with inferred PK on the referenced table: -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; - --- should fail because of duplicate referenced columns: -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk2 - FOREIGN KEY (parent_id, PERIOD parent_id) - REFERENCES temporal_rng (id, PERIOD id); - --- --- test with rows already --- - -DELETE FROM temporal_fk_rng2rng; -DELETE FROM temporal_rng; -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[1,2)', daterange('2018-01-02', '2018-02-03')), - ('[1,2)', daterange('2018-03-03', '2018-04-04')), - ('[2,3)', daterange('2018-01-01', '2018-01-05')), - ('[3,4)', daterange('2018-01-01', NULL)); - -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-01'), '[1,2)'); -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); --- should fail: -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; --- okay again: -DELETE FROM temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; - --- --- test pg_get_constraintdef --- - -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; - --- --- test FK referencing inserts --- - -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-01'), '[1,2)'); --- should fail: -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); --- now it should work: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-02-03', '2018-03-03')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); - --- --- test FK referencing updates --- - -UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-03-01') WHERE id = '[1,2)'; --- should fail: -UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-05-01') WHERE id = '[1,2)'; -UPDATE temporal_fk_rng2rng SET parent_id = '[8,9)' WHERE id = '[1,2)'; - --- ALTER FK DEFERRABLE - -BEGIN; - INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); - INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - - DELETE FROM temporal_rng WHERE id = '[5,6)'; --should not fail yet. -COMMIT; -- should fail here. - --- --- test FK referenced updates NO ACTION --- - -TRUNCATE temporal_rng, temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON UPDATE NO ACTION; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_rng WHERE id = '[5,6)'; -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') -WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- a PK update that fails because both are referenced: -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') -WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); --- a PK update that fails because both are referenced, but not 'til commit: -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - - UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -COMMIT; --- changing the scalar part fails: -UPDATE temporal_rng SET id = '[7,8)' -WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); --- then delete the objecting FK record and the same PK update succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') -WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); - --- --- test FK referenced updates RESTRICT --- - -TRUNCATE temporal_rng, temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON UPDATE RESTRICT; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_rng WHERE id = '[5,6)'; -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') -WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- a PK update that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ROLLBACK; --- changing the scalar part fails: -UPDATE temporal_rng SET id = '[7,8)' -WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); --- then delete the objecting FK record and the same PK update succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') -WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); - --- --- test FK referenced deletes NO ACTION --- - -TRUNCATE temporal_rng, temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng; --- a PK delete that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -DELETE FROM temporal_rng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- a PK delete that fails because both are referenced: -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); --- a PK delete that fails because both are referenced, but not 'til commit: -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - - DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -COMMIT; --- then delete the objecting FK record and the same PK delete succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); - --- --- test FK referenced deletes RESTRICT --- - -TRUNCATE temporal_rng, temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; -ALTER TABLE temporal_fk_rng2rng - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON DELETE RESTRICT; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -DELETE FROM temporal_rng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- a PK delete that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ROLLBACK; --- then delete the objecting FK record and the same PK delete succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); - --- --- test ON UPDATE/DELETE options --- - --- test FK referenced updates CASCADE -INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk, - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON DELETE CASCADE ON UPDATE CASCADE; - --- test FK referenced updates SET NULL -INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk, - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON DELETE SET NULL ON UPDATE SET NULL; - --- test FK referenced updates SET DEFAULT -INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); -ALTER TABLE temporal_fk_rng2rng - ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', - DROP CONSTRAINT temporal_fk_rng2rng_fk, - ADD CONSTRAINT temporal_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_rng - ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; - --- --- test FOREIGN KEY, multirange references multirange --- - --- Can't create a FK with a mismatched multirange type -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at int4multirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) -); - -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) -); -DROP TABLE temporal_fk_mltrng2mltrng; - --- with mismatched PERIOD columns: - --- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) --- REFERENCES part should specify PERIOD -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, valid_at) -); --- (parent_id, valid_at) REFERENCES (id, valid_at) --- both should specify PERIOD: -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_mltrng (id, valid_at) -); --- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) --- FOREIGN KEY part should specify PERIOD -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) -); --- (parent_id, valid_at) REFERENCES [implicit] --- FOREIGN KEY part should specify PERIOD -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) - REFERENCES temporal_mltrng -); --- (parent_id, PERIOD valid_at) REFERENCES (id) -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id) -); --- (parent_id) REFERENCES (id, PERIOD valid_at) -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) - REFERENCES temporal_mltrng (id, PERIOD valid_at) -); --- with inferred PK on the referenced table: --- (parent_id, PERIOD valid_at) REFERENCES [implicit] -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng -); -DROP TABLE temporal_fk_mltrng2mltrng; --- (parent_id) REFERENCES [implicit] -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) - REFERENCES temporal_mltrng -); - --- should fail because of duplicate referenced columns: -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD parent_id) - REFERENCES temporal_mltrng (id, PERIOD id) -); - --- Two scalar columns -CREATE TABLE temporal_fk2_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id1 int4range, - parent_id2 int4range, - CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk2_mltrng2mltrng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) - REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at) -); -\d temporal_fk2_mltrng2mltrng -DROP TABLE temporal_fk2_mltrng2mltrng; - --- --- test ALTER TABLE ADD CONSTRAINT --- - -CREATE TABLE temporal_fk_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id int4range, - CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at); --- Two scalar columns: -CREATE TABLE temporal_fk2_mltrng2mltrng ( - id int4range, - valid_at datemultirange, - parent_id1 int4range, - parent_id2 int4range, - CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -ALTER TABLE temporal_fk2_mltrng2mltrng - ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk - FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) - REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at); -\d temporal_fk2_mltrng2mltrng - --- should fail because of duplicate referenced columns: -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk2 - FOREIGN KEY (parent_id, PERIOD parent_id) - REFERENCES temporal_mltrng (id, PERIOD id); - --- --- test with rows already --- - -DELETE FROM temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)'); -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at); -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); --- should fail: -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at); --- okay again: -DELETE FROM temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at); - --- --- test pg_get_constraintdef --- - -SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_mltrng2mltrng_fk'; - --- --- test FK referencing inserts --- - -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)'); --- should fail: -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); --- now it should work: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-02-03', '2018-03-03'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); - --- --- test FK referencing updates --- - -UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-03-01')) WHERE id = '[1,2)'; --- should fail: -UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-05-01')) WHERE id = '[1,2)'; -UPDATE temporal_fk_mltrng2mltrng SET parent_id = '[8,9)' WHERE id = '[1,2)'; - --- ALTER FK DEFERRABLE - -BEGIN; - INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); - INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES - ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - - DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --should not fail yet. -COMMIT; -- should fail here. - --- --- test FK referenced updates NO ACTION --- - -TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) - ON UPDATE NO ACTION; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) -WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- a PK update that fails because both are referenced: -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) -WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); --- a PK update that fails because both are referenced, but not 'til commit: -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - - UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -COMMIT; --- changing the scalar part fails: -UPDATE temporal_mltrng SET id = '[7,8)' -WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); - --- --- test FK referenced updates RESTRICT --- - -TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) - ON UPDATE RESTRICT; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) -WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- a PK update that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - - UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ROLLBACK; --- changing the scalar part fails: -UPDATE temporal_mltrng SET id = '[7,8)' -WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); - --- --- test FK referenced deletes NO ACTION --- - -TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at); --- a PK delete that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- a PK delete that fails because both are referenced: -DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); --- a PK delete that fails because both are referenced, but not 'til commit: -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - - DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -COMMIT; - --- --- test FK referenced deletes RESTRICT --- - -TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) - ON DELETE RESTRICT; -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- a PK delete that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - - DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ROLLBACK; - --- --- test FOREIGN KEY, box references box --- (not allowed: PERIOD part must be a range or multirange) --- - -CREATE TABLE temporal_box ( - id int4range, - valid_at box, - CONSTRAINT temporal_box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_box - -CREATE TABLE temporal_fk_box2box ( - id int4range, - valid_at box, - parent_id int4range, - CONSTRAINT temporal_fk_box2box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_fk_box2box_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_box (id, PERIOD valid_at) -); - --- --- FK between partitioned tables --- - -CREATE TABLE temporal_partitioned_rng ( - id int4range, - valid_at daterange, - name text, - CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -) PARTITION BY LIST (id); -CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); -CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); -INSERT INTO temporal_partitioned_rng (id, valid_at, name) VALUES - ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), - ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), - ('[2,3)', daterange('2000-01-01', '2010-01-01'), 'two'); - -CREATE TABLE temporal_partitioned_fk_rng2rng ( - id int4range, - valid_at daterange, - parent_id int4range, - CONSTRAINT temporal_partitioned_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), - CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng (id, PERIOD valid_at) -) PARTITION BY LIST (id); -CREATE TABLE tfkp1 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); -CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); - --- --- partitioned FK referencing inserts --- - -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[1,2)', daterange('2000-01-01', '2000-02-15'), '[1,2)'), - ('[1,2)', daterange('2001-01-01', '2002-01-01'), '[2,3)'), - ('[2,3)', daterange('2000-01-01', '2000-02-15'), '[1,2)'); --- should fail: -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[3,4)', daterange('2010-01-01', '2010-02-15'), '[1,2)'); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[3,4)', daterange('2000-01-01', '2000-02-15'), '[3,4)'); - --- --- partitioned FK referencing updates --- - -UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-02-13') WHERE id = '[2,3)'; --- move a row from the first partition to the second -UPDATE temporal_partitioned_fk_rng2rng SET id = '[4,5)' WHERE id = '[1,2)'; --- move a row from the second partition to the first -UPDATE temporal_partitioned_fk_rng2rng SET id = '[1,2)' WHERE id = '[4,5)'; --- should fail: -UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-04-01') WHERE id = '[1,2)'; - --- --- partitioned FK referenced updates NO ACTION --- - -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2016-01-01', '2016-02-01')); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)'; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); - --- --- partitioned FK referenced deletes NO ACTION --- - -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); - --- --- partitioned FK referenced updates RESTRICT --- - -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -ALTER TABLE temporal_partitioned_fk_rng2rng - DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk; -ALTER TABLE temporal_partitioned_fk_rng2rng - ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng - ON DELETE RESTRICT; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2016-01-01', '2016-02-01')); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)'; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); - --- --- partitioned FK referenced deletes RESTRICT --- - -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); - --- --- partitioned FK referenced updates CASCADE --- - -ALTER TABLE temporal_partitioned_fk_rng2rng - DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, - ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng - ON DELETE CASCADE ON UPDATE CASCADE; - --- --- partitioned FK referenced deletes CASCADE --- - --- --- partitioned FK referenced updates SET NULL --- - -ALTER TABLE temporal_partitioned_fk_rng2rng - DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, - ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng - ON DELETE SET NULL ON UPDATE SET NULL; - --- --- partitioned FK referenced deletes SET NULL --- - --- --- partitioned FK referenced updates SET DEFAULT --- - -ALTER TABLE temporal_partitioned_fk_rng2rng - ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', - DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, - ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng - ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; - --- --- partitioned FK referenced deletes SET DEFAULT --- - -DROP TABLE temporal_partitioned_fk_rng2rng; -DROP TABLE temporal_partitioned_rng; - -RESET datestyle; |