Here are a couple new patches, rebased to e305f715, addressing Peter's feedback. I'm still working
on integrating jian he's suggestions for the last patch, so I've omitted that one here.
On 5/8/24 06:51, Peter Eisentraut wrote:
About v2-0001-Fix-ON-CONFLICT-DO-NOTHING-UPDATE-for-temporal-in.patch, I think
the
ideas are right, but I wonder if we can fine-tune the new conditionals a bit.
--- 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);
Here, I think we could check !indexDesc->rd_index->indisexclusion instead. So
we
wouldn't need ii_HasWithoutOverlaps.
Okay.
Or we could push this into BuildSpeculativeIndexInfo(); it could just skip the
rest
if an exclusion constraint is passed, on the theory that all the speculative
index
info is already present in that case.
I like how BuildSpeculativeIndexInfo starts with an Assert that it's given a unique index, so I've
left the check outside the function. This seems cleaner anyway: the function stays more focused.
--- 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")));
Shouldn't this use only idxForm->indisexclusion anyway? Like
+ if (idxForm->indisexclusion && onconflict->action ==
ONCONFLICT_UPDATE)
That matches what the error message is reporting afterwards.
Agreed.
* constraints), so index under consideration can be immediately
* skipped if it's not unique
*/
- if (!idxForm->indisunique)
+ if (!idxForm->indisunique || idxForm->indisexclusion)
goto next;
Maybe here we need a comment. Or make that a separate statement, like
Yes, that is nice. Done.
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.comFrom 0ebe2e6d6cd8dc6f8120fe93b9024cf80472f8cc 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/2] 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 | 9 +-
src/include/nodes/execnodes.h | 1 +
.../regress/expected/without_overlaps.out | 176 ++++++++++++++++++
src/test/regress/sql/without_overlaps.sql | 113 +++++++++++
6 files changed, 300 insertions(+), 2 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..59acf67a36a 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 && !indexDesc->rd_index->indisexclusion)
BuildSpeculativeIndexInfo(indexDesc, ii);
relationDescs[i] = indexDesc;
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 130f838629f..775c3e26cd8 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->indisexclusion && onconflict->action == ONCONFLICT_UPDATE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints")));
@@ -840,6 +840,13 @@ 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/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 8bc421e7c05..4fb7e3b284c 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 abc22d0113f..e2f2a1cbe20 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -453,6 +453,182 @@ DROP TABLE temporal_partitioned;
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
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index d4ae03ae529..5d41a6bd628 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -319,6 +319,119 @@ DROP TABLE temporal_partitioned;
-- (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
--
--
2.45.0
From edc3d4112fb60112a3375e9316e056e61581971f 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 2/2] 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 c0fcc7d78df..72346f78ebe 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3498,13 +3498,14 @@ relation_has_unique_index_for(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 aa725925675..ebca049fd5b 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -800,8 +800,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;
@@ -809,7 +809,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 775c3e26cd8..146029577bd 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 14ef296ab72..e24a45f0cd5 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.45.0
