On 4/30/24 09:24, Robert Haas wrote:
Peter, could you have a look at
http://postgr.es/m/47550967-260b-4180-9791-b224859fe...@illuminatedcomputing.com
and express an opinion about whether each of those proposals are (a)
good or bad ideas and (b) whether they need to be fixed for the
current release?
Here are the same patches but rebased. I've added a fourth which is my progress on adding the CHECK
constraint. I don't really consider it finished though, because it has these problems:
- The CHECK constraint should be marked as an internal dependency of the PK, so that you can't drop
it, and it gets dropped when you drop the PK. I don't see a good way to tie the two together though,
so I'd appreciate any advice there. They are separate AlterTableCmds, so how do I get the
ObjectAddress of both constraints at the same time? I wanted to store the PK's ObjectAddress on the
Constraint node, but since ObjectAddress isn't a Node it doesn't work.
- The CHECK constraint should maybe be hidden when you say `\d foo`? Or maybe not, but that's what
we do with FK triggers.
- When you create partitions you get a warning about the constraint already existing, because it
gets created via the PK and then also the partitioning code tries to copy it. Solving the first
issue here should solve this nicely though.
Alternately we could just fix the GROUP BY functional dependency code to only accept b-tree indexes.
But I think the CHECK constraint approach is a better solution.
Thanks,
--
Paul ~{:-)
p...@illuminatedcomputing.comFrom 0dbc008a654ab1fdc5f492345ee4575c352499d3 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Sun, 24 Mar 2024 21:46:30 -0700
Subject: [PATCH v2 1/4] Fix ON CONFLICT DO NOTHING/UPDATE for temporal indexes
A PRIMARY KEY or UNIQUE constraint with WITHOUT OVERLAPS will be a GiST
index, not a B-Tree, but it will still have indisunique set. The code
for ON CONFLICT fails if it sees a non-btree index that has indisunique.
This commit fixes that and adds some tests. But now that we can't just
test indisunique, we also need some extra checks to prevent DO UPDATE
from running against a WITHOUT OVERLAPS constraint (because the conflict
could happen against more than one row, and we'd only update one).
---
src/backend/catalog/index.c | 1 +
src/backend/executor/execIndexing.c | 2 +-
src/backend/optimizer/util/plancat.c | 4 +-
src/include/nodes/execnodes.h | 1 +
.../regress/expected/without_overlaps.out | 176 ++++++++++++++++++
src/test/regress/sql/without_overlaps.sql | 113 +++++++++++
6 files changed, 294 insertions(+), 3 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 5a8568c55c9..1fd543cc550 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2443,6 +2443,7 @@ BuildIndexInfo(Relation index)
&ii->ii_ExclusionOps,
&ii->ii_ExclusionProcs,
&ii->ii_ExclusionStrats);
+ ii->ii_HasWithoutOverlaps = ii->ii_Unique;
}
return ii;
diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index 9f05b3654c1..faa37ca56db 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)
+ if (speculative && ii->ii_Unique && !ii->ii_HasWithoutOverlaps)
BuildSpeculativeIndexInfo(indexDesc, ii);
relationDescs[i] = indexDesc;
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 130f838629f..a398d7a78d1 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->indisunique && onconflict->action == ONCONFLICT_UPDATE)
+ if ((!idxForm->indisunique || idxForm->indisexclusion) && onconflict->action == ONCONFLICT_UPDATE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints")));
@@ -837,7 +837,7 @@ infer_arbiter_indexes(PlannerInfo *root)
* constraints), so index under consideration can be immediately
* skipped if it's not unique
*/
- if (!idxForm->indisunique)
+ if (!idxForm->indisunique || idxForm->indisexclusion)
goto next;
/* Build BMS representation of plain (non expression) index attrs */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index d927ac44a82..fdfaef284e9 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -204,6 +204,7 @@ typedef struct IndexInfo
bool ii_Summarizing;
int ii_ParallelWorkers;
Oid ii_Am;
+ bool ii_HasWithoutOverlaps;
void *ii_AmCache;
MemoryContext ii_Context;
} IndexInfo;
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index f6fe8f09369..9c157ad65b3 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -449,6 +449,182 @@ SELECT * FROM tp2 ORDER BY id, valid_at;
DROP TABLE temporal_partitioned;
--
+-- 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
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index da2b7f19a85..ec47846594e 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -315,6 +315,119 @@ SELECT * FROM tp1 ORDER BY id, valid_at;
SELECT * FROM tp2 ORDER BY id, valid_at;
DROP TABLE temporal_partitioned;
+--
+-- 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
--
--
2.42.0
From c4bd0404568bf0b333165df42415c6a1ce980a1e Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Tue, 26 Mar 2024 22:32:50 -0700
Subject: [PATCH v2 2/4] Add test for REPLICA IDENTITY with a temporal key
You can only use REPLICA IDENTITY USING INDEX with a unique b-tree
index. This commit just adds a test showing that you cannot use it with
a WITHOUT OVERLAPS index (which is GiST).
---
src/test/regress/expected/without_overlaps.out | 4 ++++
src/test/regress/sql/without_overlaps.sql | 4 ++++
2 files changed, 8 insertions(+)
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index 9c157ad65b3..d451343acfa 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -624,6 +624,10 @@ SELECT * FROM temporal3 ORDER BY id, valid_at;
(1 row)
DROP TABLE temporal3;
+-- 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
--
-- test FK dependencies
--
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index ec47846594e..4d953be306f 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -428,6 +428,10 @@ SELECT * FROM temporal3 ORDER BY id, valid_at;
DROP TABLE temporal3;
+-- ALTER TABLE REPLICA IDENTITY
+-- (should fail)
+ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk;
+
--
-- test FK dependencies
--
--
2.42.0
From 7e80c503d3a179315810d61587b9f18462f772f7 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Tue, 2 Apr 2024 15:39:04 -0700
Subject: [PATCH v2 3/4] Don't treat WITHOUT OVERLAPS indexes as unique in
planner
Because the special rangetype 'empty' never overlaps another value, it
is possible for WITHOUT OVERLAPS tables to have two rows with the same
key, despite being indisunique, if the application-time range is
'empty'. So to be safe we should not treat WITHOUT OVERLAPS indexes as
unique in any proofs.
This still needs a test, but I'm having trouble finding a query that
gives wrong results.
---
src/backend/optimizer/path/indxpath.c | 5 +++--
src/backend/optimizer/plan/analyzejoins.c | 6 +++---
src/backend/optimizer/util/plancat.c | 1 +
src/include/nodes/pathnodes.h | 2 ++
4 files changed, 9 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 2230b131047..f3a93834ef2 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3515,13 +3515,14 @@ relation_has_unique_index_ext(PlannerInfo *root, RelOptInfo *rel,
/*
* If the index is not unique, or not immediately enforced, or if it's
- * a partial index, it's useless here. We're unable to make use of
+ * a partial index, or if it's a WITHOUT OVERLAPS index (so not
+ * literally unique), it's useless here. We're unable to make use of
* predOK partial unique indexes due to the fact that
* check_index_predicates() also makes use of join predicates to
* determine if the partial index is usable. Here we need proofs that
* hold true before any joins are evaluated.
*/
- if (!ind->unique || !ind->immediate || ind->indpred != NIL)
+ if (!ind->unique || !ind->immediate || ind->indpred != NIL || ind->hasperiod)
continue;
/*
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 506fccd20c9..3d332e208f1 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -848,8 +848,8 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
* For a plain relation, we only know how to prove uniqueness by
* reference to unique indexes. Make sure there's at least one
* suitable unique index. It must be immediately enforced, and not a
- * partial index. (Keep these conditions in sync with
- * relation_has_unique_index_for!)
+ * partial index, and not WITHOUT OVERLAPS (Keep these conditions
+ * in sync with relation_has_unique_index_for!)
*/
ListCell *lc;
@@ -857,7 +857,7 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
{
IndexOptInfo *ind = (IndexOptInfo *) lfirst(lc);
- if (ind->unique && ind->immediate && ind->indpred == NIL)
+ if (ind->unique && ind->immediate && ind->indpred == NIL && !ind->hasperiod)
return true;
}
}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index a398d7a78d1..429b0a284f1 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -457,6 +457,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
info->predOK = false; /* set later, in indxpath.c */
info->unique = index->indisunique;
info->immediate = index->indimmediate;
+ info->hasperiod = index->indisunique && index->indisexclusion;
info->hypothetical = false;
/*
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index b8141f141aa..13422951164 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1176,6 +1176,8 @@ struct IndexOptInfo
bool unique;
/* is uniqueness enforced immediately? */
bool immediate;
+ /* true if index has WITHOUT OVERLAPS */
+ bool hasperiod;
/* true if index doesn't really exist */
bool hypothetical;
--
2.42.0
From 72c3808f9a2a1936ac72b669e106374ac011ce49 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Tue, 9 Apr 2024 20:52:23 -0700
Subject: [PATCH v2 4/4] Add CHECK (NOT isempty) constraint to PRIMARY KEYs
WITHOUT OVERLAPS
This is necessary because 'empty' && 'empty' is false (likewise with
multiranges), which means you can get multiple identical rows like (5,
'empty'). That will give wrong results using the PK to treat other
columns as functional dependencies in a GROUP BY, and maybe elsewhere.
We don't add such a constraint for UNIQUE constraints, just as we don't
force all their columns to be NOT NULL. (The situation is analogous.)
This updates the docs too which previously said you could use any type
in WITHOUT OVERLAPS, if its GiST opclass implemented stratnum. Now only
range and multirange types are allowed, since only they have isempty.
(We still need stratnum for the non-WITHOUT OVERLAPS columns though.)
---
doc/src/sgml/gist.sgml | 3 -
doc/src/sgml/ref/create_table.sgml | 5 +-
src/backend/parser/parse_utilcmd.c | 84 ++++++++++++-
.../regress/expected/without_overlaps.out | 114 +++++++++++++-----
src/test/regress/sql/without_overlaps.sql | 69 +++++++----
5 files changed, 214 insertions(+), 61 deletions(-)
diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml
index dcf9433fa78..638d912dc2d 100644
--- a/doc/src/sgml/gist.sgml
+++ b/doc/src/sgml/gist.sgml
@@ -1186,9 +1186,6 @@ my_sortsupport(PG_FUNCTION_ARGS)
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>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 02f31d2d6fd..8022e0e1f67 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -992,10 +992,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<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
+ must have a range or multirange type. 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
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index fef084f5d52..84e960f897d 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2291,6 +2291,8 @@ transformIndexConstraints(CreateStmtContext *cxt)
*
* For a PRIMARY KEY constraint, we additionally force the columns to be
* marked as not-null, without producing a not-null constraint.
+ * If the PRIMARY KEY has WITHOUT OVERLAPS we also add an internal
+ * CHECK constraint to prevent empty ranges/multiranges.
*/
static IndexStmt *
transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
@@ -2673,6 +2675,48 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
}
}
+ /*
+ * The WITHOUT OVERLAPS part (if any) must be
+ * a range or multirange type.
+ */
+ if (constraint->without_overlaps && lc == list_last_cell(constraint->keys))
+ {
+ Oid typid = InvalidOid;
+
+ if (!found && cxt->isalter)
+ {
+ /*
+ * Look up the column type on existing table.
+ * If we can't find it, let things fail in DefineIndex.
+ */
+ Relation rel = cxt->rel;
+ for (int i = 0; i < rel->rd_att->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(rel->rd_att, i);
+ const char *attname;
+
+ if (attr->attisdropped)
+ break;
+
+ attname = NameStr(attr->attname);
+ if (strcmp(attname, key) == 0)
+ {
+ typid = attr->atttypid;
+ break;
+ }
+ }
+ }
+ else
+ typid = typenameTypeId(NULL, column->typeName);
+
+ if (OidIsValid(typid) && !type_is_range(typid) && !type_is_multirange(typid))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("column \"%s\" in WITHOUT OVERLAPS is not a range or multirange type", key),
+ parser_errposition(cxt->pstate, constraint->location)));
+ }
+
+
/* OK, add it to the index definition */
iparam = makeNode(IndexElem);
iparam->name = pstrdup(key);
@@ -2709,8 +2753,46 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
/* WITHOUT OVERLAPS requires a GiST index */
index->accessMethod = "gist";
- }
+ if (constraint->contype == CONSTR_PRIMARY)
+ {
+ /*
+ * If the PRIMARY KEY has WITHOUT OVERLAPS, we must
+ * prevent empties as well as NULLs. Since
+ * 'empty' && 'empty' is false, you could insert a value
+ * like (5, 'empty') more than once. For convenience
+ * we add this to notnullcmds (by analogy).
+ */
+ char *key = strVal(llast(constraint->keys));
+ AlterTableCmd *notemptycmd = makeNode(AlterTableCmd);
+ Constraint *checkcon = makeNode(Constraint);
+ ColumnRef *col;
+ FuncCall *func;
+ Node *expr;
+
+ col = makeNode(ColumnRef);
+ col->fields = list_make1(makeString(key));
+ func = makeFuncCall(SystemFuncName("isempty"), list_make1(col),
+ COERCE_EXPLICIT_CALL, -1);
+ expr = (Node *) makeBoolExpr(NOT_EXPR, list_make1(func), -1);
+
+ checkcon->conname = psprintf("%s_not_empty", key);
+ checkcon->contype = CONSTR_CHECK;
+ checkcon->raw_expr = expr;
+ checkcon->cooked_expr = NULL;
+ checkcon->is_no_inherit = false;
+ checkcon->deferrable = false;
+ checkcon->initdeferred = false;
+ checkcon->skip_validation = false;
+ checkcon->initially_valid = true;
+ checkcon->location = -1;
+
+ notemptycmd->subtype = AT_AddConstraint;
+ notemptycmd->def = (Node *) checkcon;
+
+ notnullcmds = lappend(notnullcmds, notemptycmd);
+ }
+ }
}
/*
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index d451343acfa..f5c596ad651 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -27,8 +27,9 @@ CREATE TABLE temporal_rng (
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.
+ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type
+LINE 4: CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOU...
+ ^
-- PK with one column plus a range:
CREATE TABLE temporal_rng (
-- Since we can't depend on having btree_gist here,
@@ -46,6 +47,8 @@ CREATE TABLE temporal_rng (
valid_at | daterange | | not null |
Indexes:
"temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
pg_get_constraintdef
@@ -76,6 +79,8 @@ CREATE TABLE temporal_rng2 (
valid_at | daterange | | not null |
Indexes:
"temporal_rng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk';
pg_get_constraintdef
@@ -113,6 +118,8 @@ CREATE TABLE temporal_mltrng (
valid_at | datemultirange | | not null |
Indexes:
"temporal_mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
-- PK with two columns plus a multirange:
-- We don't drop this table because tests below also need multiple scalar columns.
@@ -131,6 +138,8 @@ CREATE TABLE temporal_mltrng2 (
valid_at | datemultirange | | not null |
Indexes:
"temporal_mltrng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk';
pg_get_constraintdef
@@ -164,8 +173,9 @@ CREATE TABLE temporal_rng3 (
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.
+ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type
+LINE 4: CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OV...
+ ^
-- UNIQUE with one column plus a range:
CREATE TABLE temporal_rng3 (
id int4range,
@@ -320,6 +330,9 @@ 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).
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty');
+ERROR: new row for relation "temporal_rng" violates check constraint "valid_at_not_empty"
+DETAIL: Failing row contains ([3,4), empty).
-- 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')));
@@ -335,6 +348,9 @@ 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).
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}');
+ERROR: new row for relation "temporal_mltrng" violates check constraint "valid_at_not_empty"
+DETAIL: Failing row contains ([3,4), {}).
SELECT * FROM temporal_mltrng ORDER BY id, valid_at;
id | valid_at
-------+---------------------------
@@ -344,6 +360,57 @@ SELECT * FROM temporal_mltrng ORDER BY id, valid_at;
[3,4) | {[2018-01-01,)}
(4 rows)
+--
+-- test UNIQUE inserts
+--
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+-- okay:
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', NULL);
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty');
+-- should fail:
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05'));
+ERROR: conflicting key value violates exclusion constraint "temporal_rng3_uq"
+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)).
+DROP TABLE temporal_rng3;
+CREATE TABLE temporal_mltrng3 (
+ id int4range,
+ valid_at datemultirange,
+ CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+-- okay:
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL)));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL);
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}');
+-- should fail:
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+ERROR: conflicting key value violates exclusion constraint "temporal_mltrng3_uq"
+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)}).
+SELECT * FROM temporal_mltrng3 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) | {}
+ [3,4) | {[2018-01-01,)}
+ [3,4) |
+ | {[2018-01-01,2018-01-05)}
+(7 rows)
+
+DROP TABLE temporal_mltrng3;
--
-- test a range with both a PK and a UNIQUE constraint
--
@@ -372,6 +439,7 @@ CREATE TABLE temporal3 (
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));
+NOTICE: merging constraint "valid_at_not_empty" with inherited definition
ALTER TABLE temporal3 RENAME COLUMN valid_at TO valid_thru;
ALTER TABLE temporal3 DROP COLUMN valid_thru;
DROP TABLE temporal3;
@@ -806,6 +874,8 @@ CREATE TABLE temporal_fk2_rng2rng (
parent_id2 | int4range | | |
Indexes:
"temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
Foreign-key constraints:
"temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at)
@@ -845,6 +915,8 @@ ALTER TABLE temporal_fk2_rng2rng
parent_id2 | int4range | | |
Indexes:
"temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
Foreign-key constraints:
"temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at)
@@ -852,6 +924,7 @@ Foreign-key constraints:
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));
+NOTICE: merging constraint "valid_at_not_empty" with inherited definition
ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
@@ -860,6 +933,7 @@ 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);
+NOTICE: merging constraint "valid_at_not_empty" with inherited definition
-- with inferred PK on the referenced table:
ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
@@ -1276,6 +1350,8 @@ CREATE TABLE temporal_fk2_mltrng2mltrng (
parent_id2 | int4range | | |
Indexes:
"temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
Foreign-key constraints:
"temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at)
@@ -1315,6 +1391,8 @@ ALTER TABLE temporal_fk2_mltrng2mltrng
parent_id2 | int4range | | |
Indexes:
"temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
Foreign-key constraints:
"temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at)
@@ -1536,34 +1614,6 @@ ERROR: update or delete on table "temporal_mltrng" violates foreign key constra
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 (
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 4d953be306f..761b0ba9bd6 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -226,6 +226,7 @@ INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01',
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);
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty');
-- okay:
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
@@ -237,9 +238,56 @@ INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(dater
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);
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}');
SELECT * FROM temporal_mltrng ORDER BY id, valid_at;
+--
+-- test UNIQUE inserts
+--
+
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+
+-- okay:
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', NULL);
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty');
+
+-- should fail:
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05'));
+
+DROP TABLE temporal_rng3;
+
+CREATE TABLE temporal_mltrng3 (
+ id int4range,
+ valid_at datemultirange,
+ CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+
+-- okay:
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL)));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL);
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}');
+
+-- should fail:
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+
+SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at;
+
+DROP TABLE temporal_mltrng3;
+
--
-- test a range with both a PK and a UNIQUE constraint
--
@@ -1273,27 +1321,6 @@ BEGIN;
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
--
--
2.42.0
