On 3/24/24 00:38, Peter Eisentraut wrote:> I have committed the patches
v33-0001-Add-temporal-FOREIGN-KEYs.patch and v33-0002-Support-multiranges-in-temporal-FKs.patch
(together).
Hi Hackers,
I found some problems with temporal primary keys and the idea of uniqueness, especially around the
indisunique column. Here are some small fixes and a proposal for a larger fix, which I think we need
but I'd like some feedback on.
The first patch fixes problems with ON CONFLICT DO NOTHING/UPDATE.
DO NOTHING fails because it doesn't expect a non-btree unique index. It's fine to make it accept a
temporal PRIMARY KEY/UNIQUE index though (i.e. an index with both indisunique and indisexclusion).
This is no different than an exclusion constraint. So I skip BuildSpeculativeIndexInfo for WITHOUT
OVERLAPS indexes. (Incidentally, AFAICT ii_UniqueOps is never used, only ii_UniqueProcs. Right?)
We should still forbid temporally-unique indexes for ON CONFLICT DO UPDATE, since there may be more
than one row that conflicts. Ideally we would find and update all the conflicting rows, but we don't
now, and we don't want to update just one:
postgres=# create table t (id int4range, valid_at daterange, name text, constraint tpk primary
key (id, valid_at without overlaps));
CREATE TABLE
postgres=# insert into t values ('[1,2)', '[2000-01-01,2001-01-01)', 'a'), ('[1,2)',
'[2001-01-01,2002-01-01)', 'b');
INSERT 0 2
postgres=# insert into t values ('[1,2)', '[2000-01-01,2002-01-01)', 'c') on conflict (id,
valid_at) do update set name = excluded.name;
INSERT 0 1
postgres=# select * from t;
id | valid_at | name
-------+-------------------------+------
[1,2) | [2001-01-01,2002-01-01) | b
[1,2) | [2000-01-01,2001-01-01) | c
(2 rows)
So I also added code to prevent that. This is just preserving the old behavior for exclusion
constraints, which was bypassed because of indisunique. All this is in the first patch.
That got me thinking about indisunique and where else it could cause problems. Perhaps there are
other places that assume only b-trees are unique. I couldn't find anywhere that just gives an error
like ON CONFLICT, but I can imagine more subtle problems.
A temporal PRIMARY KEY or UNIQUE constraint is unique in at least three ways: It is *metaphorically*
unique: the conceit is that the scalar part is unique at every moment in time. You may have id 5 in
your table more than once, as long as the records' application times don't overlap.
And it is *officially* unique: the standard calls these constraints unique. I think it is correct
for us to report them as unique in pg_index.
But is it *literally* unique? Well two identical keys, e.g. (5, '[Jan24,Mar24)') and (5,
'[Jan24,Mar24)'), do have overlapping ranges, so the second is excluded. Normally a temporal unique
index is *more* restrictive than a standard one, since it forbids other values too (e.g. (5,
'[Jan24,Feb24)')). But sadly there is one exception: the ranges in these keys do not overlap: (5,
'empty'), (5, 'empty'). With ranges/multiranges, `'empty' && x` is false for all x. You can add that
key as many times as you like, despite a PK/UQ constraint:
postgres=# insert into t values
('[1,2)', 'empty', 'foo'),
('[1,2)', 'empty', 'bar');
INSERT 0 2
postgres=# select * from t;
id | valid_at | name
-------+----------+------
[1,2) | empty | foo
[1,2) | empty | bar
(2 rows)
Cases like this shouldn't actually happen for temporal tables, since empty is not a meaningful
value. An UPDATE/DELETE FOR PORTION OF would never cause an empty. But we should still make sure
they don't cause problems.
One place we should avoid temporally-unique indexes is REPLICA IDENTITY. Fortunately we already do
that, but patch 2 adds a test to keep it that way.
Uniqueness is an important property to the planner, too.
We consider indisunique often for estimates, where it needn't be 100% true. Even if there are
nullable columns or a non-indimmediate index, it still gives useful stats. Duplicates from 'empty'
shouldn't cause any new problems there.
In proof code we must be more careful. Patch 3 updates relation_has_unique_index_ext and
rel_supports_distinctness to disqualify WITHOUT OVERLAPS indexes. Maybe that's more cautious than
needed, but better safe than sorry. This patch has no new test though. I had trouble writing SQL
that was wrong before its change. I'd be happy for help here!
Another problem is GROUP BY and functional dependencies. This is wrong:
postgres=# create table a (id int4range, valid_at daterange, name text, constraint apk primary
key (id, valid_at without overlaps));
CREATE TABLE
postgres=# insert into a values ('[1,2)', 'empty', 'foo'), ('[1,2)',
'empty', 'bar');
INSERT 0 2
postgres=# select * from a group by id, valid_at;
id | valid_at | name
-------+----------+------
[1,2) | empty | foo
(1 row)
One fix is to return false from check_functional_grouping for WITHOUT OVERLAPS primary keys. But I
think there is a better fix that is less ad hoc.
We should give temporal primary keys an internal CHECK constraint saying `NOT isempty(valid_at)`.
The problem is analogous to NULLs in parts of a primary key. NULLs prevent two identical keys from
ever comparing as equal. And just as a regular primary key cannot contain NULLs, so a temporal
primary key should not contain empties.
The standard effectively prevents this with PERIODs, because a PERIOD adds a constraint saying start
< end. But our ranges enforce only start <= end. If you say `int4range(4,4)` you get `empty`. If we
constrain primary keys as I'm suggesting, then they are literally unique, and indisunique seems safer.
Should we add the same CHECK constraint to temporal UNIQUE indexes? I'm inclined toward no, just as
we don't forbid NULLs in parts of a UNIQUE key. We should try to pick what gives users more options,
when possible. Even if it is questionably meaningful, I can see use cases for allowing empty ranges
in a temporal table. For example it lets you "disable" a row, preserving its values but marking it
as never true.
Also it gives you a way to make a non-temporal foreign key reference to a temporal table. Normally
temporal tables are "contagious", which is annoying. But if the referencing table had 'empty' for
its temporal part, then references should succeed. For example this is true: 'empty'::daterange <@
'[2000-01-01,2001-01-01)'. (Technically this would require a small change to our FK SQL, because we
do `pkperiod && fkperiod` as an optimization (to use the index more fully), and we would need to
skip that when fkperiod is empty.)
Finally, if we have a not-empty constraint on our primary keys, then the GROUP BY problem above goes
away. And we can still use temporal primary keys in proofs (but maybe not other temporally-unique
indexes). We can allow them in relation_has_unique_index_ext/rel_supports_distinctness.
The drawback to putting a CHECK constraint on just PKs and not UNIQUEs is that indisunique may not
be literally unique for them, if they have empty ranges. But even for traditional UNIQUE
constraints, indisunique can be misleading: If they have nullable parts, identical keys are still
"unique", so the code is already careful about them. Do note though the problems come from 'empty'
values, not nullable values, so there might still be some planner rules we need to correct.
Another drawback is that by using isempty we're limiting temporal PKs to just ranges and
multiranges, whereas currently any type with appropriate operators is allowed. But since we decided
to limit FKs already, I think this is okay. We can open it back up again later if we like (e.g. by
adding a support function for the isempty concept).
I'll start working on a patch for this too, but I'd be happy for early
feedback/objections/etc.
I guess an alternative would be to add a new operator, say &&&, that is the same as overlaps, except
'empty' overlaps everything instead of nothing. In a way that seems more consistent with <@. (How
can a range contain something if it doesn't overlap it?) I don't love that a key like (5, 'empty')
would conflict with every other 5, but you as I said it's not a meaningful value in a temporal table
anyway. Or you could have 'empty' overlap nothing except itself. Maybe I prefer this solution to an
internal CHECK constraint, but it feels like it has more unknown unknowns. Thoughts?
Also I suspect there are still places where indisunique causes problems. I'll keep looking for them,
but if others have thoughts please let me know.
Patches here are generated against c627d944e6.
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.comFrom 9df52edb99612b112f798fc40ccf11efa1474f64 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 v1 1/3] 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 b6a7c60e230..aa604dc430a 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 6bb53e4346f..b3261475bdb 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -804,7 +804,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")));
@@ -826,7 +826,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 e7ff8e4992f..d985fbaa06d 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 d8a17e60d6cd5f932bdc3ea0911738f675dbb187 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 v1 2/3] 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 0d2fe0e778570843f44ce274dbdbd9de13021262 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 v1 3/3] 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 32c6a8bbdcb..56fae66daf7 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3569,13 +3569,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 b3261475bdb..a110036988b 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -446,6 +446,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 0ab25d9ce7b..78b11f4cb5f 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1172,6 +1172,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
