public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 11/19] doc review: basebackup_to_shell.required_role 17+ messages / 6 participants [nested] [flat]
* [PATCH 11/19] doc review: basebackup_to_shell.required_role @ 2022-04-07 14:04 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 17+ messages in thread From: Justin Pryzby @ 2022-04-07 14:04 UTC (permalink / raw) c6306db24bd913375f99494e38ab315befe44e11 See also: https://www.postgresql.org/message-id/CA%2BTgmoaBQ5idAh7OsQGAbLY166SVkj7KkKROkTyN5sOF6QDuww%40mail.g... --- doc/src/sgml/basebackup-to-shell.sgml | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/basebackup-to-shell.sgml b/doc/src/sgml/basebackup-to-shell.sgml index 9f44071d502..15231ef3939 100644 --- a/doc/src/sgml/basebackup-to-shell.sgml +++ b/doc/src/sgml/basebackup-to-shell.sgml @@ -65,8 +65,8 @@ </term> <listitem> <para> - A role which replication whose privileges users are required to possess - in order to make use of the <literal>shell</literal> backup target. + The role required in order to + make use of the <literal>shell</literal> backup target. If this is not set, any replication user may make use of the <literal>shell</literal> backup target. </para> -- 2.17.1 --8X7/QrJGcKSMr1RN Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0012-v15-comment-typos.patch" ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-04-03 05:30 Paul Jungwirth <[email protected]> 0 siblings, 3 replies; 17+ messages in thread From: Paul Jungwirth @ 2024-04-03 05:30 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> 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 ~{:-) [email protected] Attachments: [text/x-patch] v1-0001-Fix-ON-CONFLICT-DO-NOTHING-UPDATE-for-temporal-in.patch (21.3K, ../../[email protected]/2-v1-0001-Fix-ON-CONFLICT-DO-NOTHING-UPDATE-for-temporal-in.patch) download | inline diff: From 9df52edb99612b112f798fc40ccf11efa1474f64 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> 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 [text/x-patch] v1-0002-Add-test-for-REPLICA-IDENTITY-with-a-temporal-key.patch (1.6K, ../../[email protected]/3-v1-0002-Add-test-for-REPLICA-IDENTITY-with-a-temporal-key.patch) download | inline diff: From d8a17e60d6cd5f932bdc3ea0911738f675dbb187 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> 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 [text/x-patch] v1-0003-Don-t-treat-WITHOUT-OVERLAPS-indexes-as-unique-in.patch (3.9K, ../../[email protected]/4-v1-0003-Don-t-treat-WITHOUT-OVERLAPS-indexes-as-unique-in.patch) download | inline diff: From 0d2fe0e778570843f44ce274dbdbd9de13021262 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-04-15 00:00 jian he <[email protected]> parent: Paul Jungwirth <[email protected]> 2 siblings, 0 replies; 17+ messages in thread From: jian he @ 2024-04-15 00:00 UTC (permalink / raw) To: Paul Jungwirth <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; PostgreSQL Hackers <[email protected]> On Wed, Apr 3, 2024 at 1:30 PM Paul Jungwirth <[email protected]> wrote: > > 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?) > hi. for unique index, primary key: ii_ExclusionOps, ii_UniqueOps is enough to distinguish this index support without overlaps, we don't need another ii_HasWithoutOverlaps? (i didn't test it though) ON CONFLICT DO NOTHING ON CONFLICT (id, valid_at) DO NOTHING ON CONFLICT ON CONSTRAINT temporal_rng_pk DO NOTHING I am confused by the test. here temporal_rng only has one primary key, ON CONFLICT only deals with it. I thought these three are the same thing? DROP TABLE temporal_rng; CREATE TABLE temporal_rng (id int4range,valid_at daterange); ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- 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 +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 +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; ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-04-26 19:25 Robert Haas <[email protected]> parent: Paul Jungwirth <[email protected]> 2 siblings, 1 reply; 17+ messages in thread From: Robert Haas @ 2024-04-26 19:25 UTC (permalink / raw) To: Paul Jungwirth <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]> On Wed, Apr 3, 2024 at 1:30 AM Paul Jungwirth <[email protected]> wrote: > 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. I think this thread should be added to the open items list. You're raising questions about whether the feature that was committed to this release is fully correct. If it isn't, we shouldn't release it without fixing it. https://wiki.postgresql.org/wiki/PostgreSQL_17_Open_Items -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-04-26 19:41 Paul Jungwirth <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 1 reply; 17+ messages in thread From: Paul Jungwirth @ 2024-04-26 19:41 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]> On 4/26/24 12:25, Robert Haas wrote: > I think this thread should be added to the open items list. Thanks! I sent a request to pgsql-www to get edit permission. I didn't realize there was a wiki page tracking things like this. I agree it needs to be fixed if we want to include the feature. Yours, -- Paul ~{:-) [email protected] ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-04-30 16:24 Robert Haas <[email protected]> parent: Paul Jungwirth <[email protected]> 0 siblings, 1 reply; 17+ messages in thread From: Robert Haas @ 2024-04-30 16:24 UTC (permalink / raw) To: Paul Jungwirth <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]> On Fri, Apr 26, 2024 at 3:41 PM Paul Jungwirth <[email protected]> wrote: > On 4/26/24 12:25, Robert Haas wrote: > > I think this thread should be added to the open items list. > > Thanks! I sent a request to pgsql-www to get edit permission. I didn't realize there was a wiki page > tracking things like this. I agree it needs to be fixed if we want to include the feature. Great, I see that it's on the list now. Peter, could you have a look at http://postgr.es/m/[email protected] 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? Thanks, -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-04-30 16:39 Paul Jungwirth <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 3 replies; 17+ messages in thread From: Paul Jungwirth @ 2024-04-30 16:39 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]> On 4/30/24 09:24, Robert Haas wrote: > Peter, could you have a look at > http://postgr.es/m/[email protected] > 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 ~{:-) [email protected] Attachments: [text/x-patch] v2-0001-Fix-ON-CONFLICT-DO-NOTHING-UPDATE-for-temporal-in.patch (21.3K, ../../[email protected]/2-v2-0001-Fix-ON-CONFLICT-DO-NOTHING-UPDATE-for-temporal-in.patch) download | inline diff: From 0dbc008a654ab1fdc5f492345ee4575c352499d3 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> 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 [text/x-patch] v2-0002-Add-test-for-REPLICA-IDENTITY-with-a-temporal-key.patch (1.6K, ../../[email protected]/3-v2-0002-Add-test-for-REPLICA-IDENTITY-with-a-temporal-key.patch) download | inline diff: From c4bd0404568bf0b333165df42415c6a1ce980a1e Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> 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 [text/x-patch] v2-0003-Don-t-treat-WITHOUT-OVERLAPS-indexes-as-unique-in.patch (3.9K, ../../[email protected]/4-v2-0003-Don-t-treat-WITHOUT-OVERLAPS-indexes-as-unique-in.patch) download | inline diff: From 7e80c503d3a179315810d61587b9f18462f772f7 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> 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 [text/x-patch] v2-0004-Add-CHECK-NOT-isempty-constraint-to-PRIMARY-KEYs-.patch (22.3K, ../../[email protected]/5-v2-0004-Add-CHECK-NOT-isempty-constraint-to-PRIMARY-KEYs-.patch) download | inline diff: From 72c3808f9a2a1936ac72b669e106374ac011ce49 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-05-01 11:27 jian he <[email protected]> parent: Paul Jungwirth <[email protected]> 2 siblings, 0 replies; 17+ messages in thread From: jian he @ 2024-05-01 11:27 UTC (permalink / raw) To: Paul Jungwirth <[email protected]>; +Cc: Robert Haas <[email protected]>; Peter Eisentraut <[email protected]>; PostgreSQL Hackers <[email protected]> On Wed, May 1, 2024 at 12:39 AM Paul Jungwirth <[email protected]> wrote: > > On 4/30/24 09:24, Robert Haas wrote: > > Peter, could you have a look at > > http://postgr.es/m/[email protected] > > 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. > I will consider these issues later. The following are general ideas after applying your patches. CREATE TABLE temporal_rng1( id int4range, valid_at daterange, CONSTRAINT temporal_rng1_pk unique (id, valid_at WITHOUT OVERLAPS) ); insert into temporal_rng1(id, valid_at) values (int4range '[1,1]', 'empty'::daterange), ('[1,1]', 'empty'); table temporal_rng1; id | valid_at -------+---------- [1,2) | empty [1,2) | empty (2 rows) i hope i didn't miss something: exclude the 'empty' special value, WITHOUT OVERLAP constraint will be unique and is more restrictive? if so, then adding a check constraint to make the WITHOUT OVERLAP not include the special value 'empty' is better than writing a doc explaining that on some special occasion, a unique constraint is not meant to be unique ? in here https://www.postgresql.org/docs/devel/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS says: << Unique constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. << + /* + * 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))); + } + if (attr->attisdropped) + break; it will break the loop? but here you want to continue the loop? + if (OidIsValid(typid) && !type_is_range(typid) && !type_is_multirange(typid)) didn't consider the case where typid is InvalidOid, maybe we can simplify to + if (!type_is_range(typid) && !type_is_multirange(typid)) + notnullcmds = lappend(notnullcmds, notemptycmd); seems weird. we can imitate notnullcmds related logic for notemptycmd, not associated notnullcmds in any way. ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-05-06 03:01 jian he <[email protected]> parent: Paul Jungwirth <[email protected]> 2 siblings, 2 replies; 17+ messages in thread From: jian he @ 2024-05-06 03:01 UTC (permalink / raw) To: Paul Jungwirth <[email protected]>; +Cc: Robert Haas <[email protected]>; Peter Eisentraut <[email protected]>; PostgreSQL Hackers <[email protected]> On Wed, May 1, 2024 at 12:39 AM Paul Jungwirth <[email protected]> wrote: > > On 4/30/24 09:24, Robert Haas wrote: > > Peter, could you have a look at > > http://postgr.es/m/[email protected] > > 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. > hi. I hope I understand the problem correctly. my understanding is that we are trying to solve a corner case: create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS)); insert into t values ('[1,2]','empty'), ('[1,2]','empty'); I think the entry point is ATAddCheckNNConstraint and index_create. in a chain of DDL commands, you cannot be sure which one (primary key constraint or check constraint) is being created first, you just want to make sure that after both constraints are created, then add a dependency between primary key and check constraint. so you need to validate at different functions (ATAddCheckNNConstraint, index_create) that these two constraints are indeed created, only after that we have a dependency linking these two constraints. I've attached a patch trying to solve this problem. the patch is not totally polished, but works as expected, and also has lots of comments. Attachments: [text/x-patch] v4-0001-add-a-special-check-constrint-for-PERIOD-primary-.patch (40.9K, ../../CACJufxFQ4EkJDTZXzgC3boid7dnXaRD8gW1KcXi_H-JAg2FPMA@mail.gmail.com/2-v4-0001-add-a-special-check-constrint-for-PERIOD-primary-.patch) download | inline diff: From 2028de0384b81bb9b2bff53fd391b08f57aba242 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Mon, 6 May 2024 10:12:26 +0800 Subject: [PATCH v4 1/1] add a special check constrint for PERIOD primary key last column of PERIOD primary key cannot have empty value, otherwise primary key may lost uniqueness property. corner case demo: create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS)); insert into t values ('[1,2]','empty'), ('[1,2]','empty'); this patch makes it fails by internally add a check constraint: CHECK (NOT isempty(period_column)) after that, the table `t` will look like: Column | Type | Collation | Nullable | Default --------+-----------+-----------+----------+--------- a | int4range | | not null | b | int4range | | not null | Indexes: "t_pkey" PRIMARY KEY (a, b WITHOUT OVERLAPS) Check constraints: "b_not_empty" CHECK (NOT isempty(b)) to distinguish this constraint with other check constraint, we make it conperiod as true in pg_constraint catalog. we aslo add a internal dependency between the primary key constraint and this check constraint. so you cannot drop the check constraint itself, if you drop the primary key constraint, this check constraint will be dropped automatically. generally we add check constraint within the function ATAddCheckNNConstraint, primary key constraint within the function index_create. in a chain of DDL command, we are not sure which one is be first created, to make it safe, we do cross check at these two functions, after both check constraint and primary key constraint are created, then we add a internal dependencies on it. N.B. we also need to have special care for case where check constraint was readded, e.g. ALTER TYPE. if ALTER TYPE is altering the PERIOD column of the primary key, alter column of primary key makes the index recreate, check constraint recreate, however, former interally also including add a check constraint. so we need to take care of merging two check constraint. N.B. the check constraint name is hard-wired, so if you create the constraint with the same name, PERIOD primary key cannot be created. N.B. what about UNIQUE constraint? N.B. seems ok to not care about FOREIGN KEY regarding this corner case? Discussion: https://postgr.es/m/[email protected] --- doc/src/sgml/gist.sgml | 3 - doc/src/sgml/ref/create_table.sgml | 5 +- src/backend/catalog/heap.c | 10 +- src/backend/catalog/index.c | 22 ++ src/backend/commands/tablecmds.c | 188 +++++++++++++ src/backend/parser/parse_utilcmd.c | 88 +++++- src/include/commands/tablecmds.h | 4 + .../regress/expected/without_overlaps.out | 251 +++++++++++++++++- src/test/regress/sql/without_overlaps.sql | 120 +++++++++ 9 files changed, 664 insertions(+), 27 deletions(-) diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml index dcf9433f..638d912d 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 02f31d2d..8022e0e1 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/catalog/heap.c b/src/backend/catalog/heap.c index 136cc42a..22e818e0 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -103,7 +103,7 @@ static ObjectAddress AddNewRelationType(const char *typeName, static void RelationRemoveInheritance(Oid relid); static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr, bool is_validated, bool is_local, int inhcount, - bool is_no_inherit, bool is_internal); + bool is_no_inherit, bool is_internal, bool without_overlaps); static void StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal); static bool MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr, @@ -2065,7 +2065,7 @@ SetAttrMissing(Oid relid, char *attname, char *value) static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr, bool is_validated, bool is_local, int inhcount, - bool is_no_inherit, bool is_internal) + bool is_no_inherit, bool is_internal, bool without_overlaps) { char *ccbin; List *varList; @@ -2155,7 +2155,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, is_local, /* conislocal */ inhcount, /* coninhcount */ is_no_inherit, /* connoinherit */ - false, /* conperiod */ + without_overlaps, /* conperiod */ is_internal); /* internally constructed? */ pfree(ccbin); @@ -2252,7 +2252,7 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal) StoreRelCheck(rel, con->name, con->expr, !con->skip_validation, con->is_local, con->inhcount, con->is_no_inherit, - is_internal); + is_internal, false); numchecks++; break; @@ -2518,7 +2518,7 @@ AddRelationNewConstraints(Relation rel, */ constrOid = StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local, - is_local ? 0 : 1, cdef->is_no_inherit, is_internal); + is_local ? 0 : 1, cdef->is_no_inherit, is_internal, cdef->without_overlaps); numchecks++; diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 5a8568c5..2ec78777 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1224,6 +1224,28 @@ index_create(Relation heapRelation, */ CommandCounterIncrement(); + if (isprimary) + { + ObjectAddress pk_period_address = InvalidObjectAddress; + ObjectAddress check_period_address = InvalidObjectAddress; + Oid check_conperiod_oid = InvalidOid; + Oid pk_conperiod_oid = InvalidOid; + + if (validate_period_check_constr(heapRelation, &check_conperiod_oid, &pk_conperiod_oid)) + { + Assert(OidIsValid(check_conperiod_oid)); + Assert(OidIsValid(pk_conperiod_oid)); + ObjectAddressSet(check_period_address, ConstraintRelationId, check_conperiod_oid); + ObjectAddressSet(pk_period_address, ConstraintRelationId, pk_conperiod_oid); + /* + * Register this special check constraint as internally dependent on the + * primary key constraint. + * Note that we also have direct () dependency from the + * special check constraint to the table. + */ + recordDependencyOn(&check_period_address, &pk_period_address, DEPENDENCY_INTERNAL); + } + } /* * In bootstrap mode, we have to fill in the index strategy structure with * information from the catalogs. If we aren't bootstrapping, then the diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3309332f..14bf0b53 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -9724,6 +9724,105 @@ ChooseForeignKeyConstraintNameAddition(List *colnames) return pstrdup(buf); } +/* + * for PERIOD PRIMARY KEY, we need our last key column be a range data type + * also cannot be as empty range, since empty range itself not overlaps, + * so it can have a duplicated entry for the PERIOD primary key. + * To deal with it, we add a check constraint to enforce the last PERIOD column + * cannot have empty range value, we also make an internal dependency + * between primary key constraint and check constraint. + * With this dependency, the PERIOD primary key constraint drops, + * this special check constraint will drop automatically, but we cannot drop itself. + * + * we also mark this special check constraint conperiod as true. + * in a chain of command, we are not sure primary key constraint + * created first or check constraint, so we call this function within index_create + * and ATAddCheckNNConstraint. + * + * return true means both PERIOD PRIMARY KEY, and special check constraint are existed + * in our pg_constraint catalog. +*/ +bool +validate_period_check_constr(Relation heaprel, Oid *check_conperiod_oid, Oid *pk_conperiod_oid) +{ + Oid pk_constr_oid = InvalidOid; + Oid check_constr_oid = InvalidOid; + Relation pg_constraint; + HeapTuple conTup; + SysScanDesc scan; + ScanKeyData key; + ArrayType *arr; + bool isNull; + Datum adatum; + int numkeys; + int16 *attnums; + int16 pk_period_attnum = -1; + int16 check_period_attnum = -1; + + pg_constraint = table_open(ConstraintRelationId, AccessShareLock); + ScanKeyInit(&key, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + RelationGetRelid(heaprel)); + + scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, + true, NULL, 1, &key); + + while (HeapTupleIsValid(conTup = systable_getnext(scan))) + { + Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup); + /* + * We're looking for both CHECK constraint and primary key constraint + * that are marked as validated and conperiod is true + */ + if (con->contype != CONSTRAINT_PRIMARY && con->contype != CONSTRAINT_CHECK) + continue; + if (!con->convalidated) + continue; + if (!con->conperiod) + continue; + + adatum = heap_getattr(conTup, Anum_pg_constraint_conkey, + RelationGetDescr(pg_constraint), &isNull); + if (isNull) + elog(ERROR, "null conkey for constraint %u", con->oid); + + arr = DatumGetArrayTypeP(adatum); + numkeys = ARR_DIMS(arr)[0]; + if (ARR_NDIM(arr) != 1 || + numkeys < 0 || + ARR_HASNULL(arr) || + ARR_ELEMTYPE(arr) != INT2OID) + elog(ERROR, "conkey is not a 1-D smallint array"); + + attnums = (int16 *) ARR_DATA_PTR(arr); + if (con->contype == CONSTRAINT_PRIMARY) + { + pk_period_attnum = attnums[numkeys - 1]; + pk_constr_oid = con->oid; + } + else + { + check_period_attnum = attnums[numkeys - 1]; + check_constr_oid = con->oid; + } + } + systable_endscan(scan); + table_close(pg_constraint, AccessShareLock); + + if (check_period_attnum != -1 && pk_period_attnum != -1) + { + if (check_period_attnum != pk_period_attnum) + elog(ERROR, "PERIOD check constraint associated attribute number should be same as PERIOD column's"); + + *check_conperiod_oid = check_constr_oid; + *pk_conperiod_oid = pk_constr_oid; + return true; + } + else + return false; +} + /* * Add a check or not-null constraint to a single table and its children. * Returns the address of the constraint added to the parent relation, @@ -9816,6 +9915,95 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, /* Advance command counter in case same table is visited multiple times */ CommandCounterIncrement(); + /* + * deal with ALTER TYPE or other command where is_readd is true. + * when is_readd (eg. ALTER TYPE), PERIOD primary key will be reconstructed, it also + * instruct to append the add notempty check subcommand, see transformIndexConstraint. + * change the PERIOD column also readd the check constraint, obviously the readded one + * conperiod attribute will set to false. + * these two check constraint will be merged into one, see MergeWithExistingConstraint. + * MergeWithExistingConstraint don't deal with conperiod is true CHECK constraint, so + * we need reset it true manually + */ + if (constr->without_overlaps && is_readd && constr->contype == CONSTR_CHECK) + { + Relation pg_constraint; + HeapTuple conTup; + SysScanDesc scan; + ScanKeyData skey[3]; + HeapTuple copyTuple; + Form_pg_constraint copy_con; + + pg_constraint = table_open(ConstraintRelationId, RowExclusiveLock); + /* + * Find and check the target constraint + */ + ScanKeyInit(&skey[0], + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(rel))); + ScanKeyInit(&skey[1], + Anum_pg_constraint_contypid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(InvalidOid)); + ScanKeyInit(&skey[2], + Anum_pg_constraint_conname, + BTEqualStrategyNumber, F_NAMEEQ, + CStringGetDatum(constr->conname)); + + scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, + true, NULL, 1, skey); + + while (HeapTupleIsValid(conTup = systable_getnext(scan))) + { + Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup); + + if (con->contype != CONSTRAINT_CHECK) + continue; + + if (!con->convalidated) + continue; + if (con->conperiod) + break; + copyTuple = heap_copytuple(conTup); + copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple); + copy_con->conperiod = true; + + /* Reset conperiod */ + CatalogTupleUpdate(pg_constraint, ©Tuple->t_self, copyTuple); + heap_freetuple(copyTuple); + + CommandCounterIncrement(); + } + systable_endscan(scan); + table_close(pg_constraint, RowExclusiveLock); + } + + /* validate and record the internal dependency between PERIOD primary key and CHECK */ + if (constr->without_overlaps) + { + ObjectAddress pk_period_address = InvalidObjectAddress; + ObjectAddress check_period_address = InvalidObjectAddress; + Oid check_conperiod_oid = InvalidOid; + Oid pk_conperiod_oid = InvalidOid; + + if (validate_period_check_constr(rel, &check_conperiod_oid, &pk_conperiod_oid)) + { + Assert(OidIsValid(check_conperiod_oid)); + Assert(OidIsValid(pk_conperiod_oid)); + + ObjectAddressSet(pk_period_address,ConstraintRelationId, pk_conperiod_oid); + ObjectAddressSet(check_period_address,ConstraintRelationId, check_conperiod_oid); + /* + * Register this special check constraint as internally dependent on the + * primary key constraint. + * Note that we also have direct dependency from the + * special check constraint to the table. + */ + recordDependencyOn(&check_period_address, &pk_period_address, DEPENDENCY_INTERNAL); + } + } + /* * If the constraint got merged with an existing constraint, we're done. * We mustn't recurse to child tables in this case, because they've diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 9fb6ff86..ff996072 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -36,6 +36,7 @@ #include "catalog/pg_am.h" #include "catalog/pg_collation.h" #include "catalog/pg_constraint.h" +#include "catalog/pg_namespace.h" #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_statistic_ext.h" @@ -2301,6 +2302,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) @@ -2683,6 +2686,47 @@ 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++) + { + const char *attname; + Form_pg_attribute attr = TupleDescAttr(rel->rd_att, i); + + if (attr->attisdropped) + continue; + + attname = NameStr(attr->attname); + if (strcmp(attname, key) == 0) + { + typid = attr->atttypid; + break; + } + } + } + else + typid = typenameTypeId(NULL, column->typeName); + + if (!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); @@ -2719,8 +2763,50 @@ 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. + */ + char *key = strVal(llast(constraint->keys)); + AlterTableCmd *notemptycmd = makeNode(AlterTableCmd); + Constraint *checkcon = makeNode(Constraint); + ColumnRef *col; + FuncCall *func; + Node *expr; + char *conname; + + 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); + conname = psprintf("%s_not_empty", key); + + if (! checkcon->conname) + checkcon->conname = conname; + 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->without_overlaps = true; + checkcon->location = -1; + + notemptycmd->subtype = AT_AddConstraint; + notemptycmd->def = (Node *) checkcon; + notemptycmd->name = psprintf("%s_not_empty", key); + + notnullcmds = lcons(notemptycmd, notnullcmds); + } } - } /* diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index 85cbad3d..081d2fac 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -27,6 +27,10 @@ struct AlterTableUtilityContext; /* avoid including tcop/utility.h here */ extern ObjectAddress DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, ObjectAddress *typaddress, const char *queryString); +extern bool validate_period_check_constr(Relation heaprel, + Oid *check_conperiod_oid, + Oid *pk_conperiod_oid); + extern TupleDesc BuildDescForRelation(const List *columns); extern void RemoveRelations(DropStmt *drop); diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index f6fe8f09..0f8b9db4 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,7 +118,215 @@ 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)) +--for PERIOD primary key and check constraint dependency. +create or replace function validate_period_empty_dependency(regclass) +returns table +( +dependent_conname name,dependent_con_type "char", +both_con_are_period bool,referenced_conname name, +referenced_con_type "char",deptype "char" +) +as $$ +begin + return query + select + pc.conname as dependent_conname, + pc.contype as dependent_con_type, + pc.conperiod = pc1.conperiod as both_con_are_period, + pc1.conname as referenced_conname, + pc1.contype as referenced_con_type, + pd.deptype + from pg_depend pd join pg_constraint pc on pc.oid = pd.objid + join pg_constraint pc1 on pc1.oid = pd.refobjid + where pd.refclassid = pd.classid + and pd.classid = (select oid + from pg_class + where relname = 'pg_constraint' + and relnamespace = 'pg_catalog'::regnamespace) + and pc.conrelid = pc1.conrelid + and pc.conrelid = $1 + ; +end; $$ language plpgsql; +--pk with not_empty check constraint +CREATE TABLE temporal_t1 (id int4range, valid_at daterange); +ALTER TABLE temporal_t1 + ADD CONSTRAINT temporal_t1_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +select * from validate_period_empty_dependency('temporal_t1'::regclass); + dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype +--------------------+--------------------+---------------------+--------------------+---------------------+--------- + valid_at_not_empty | c | t | temporal_t1_pk | p | i +(1 row) + +select pg_get_constraintdef(oid) from pg_constraint +where conrelid = 'temporal_t1'::regclass +and contype = 'c' +and conperiod; + pg_get_constraintdef +--------------------------------- + CHECK ((NOT isempty(valid_at))) +(1 row) + +--should fail, primary key depend on it +alter table temporal_t1 drop constraint valid_at_not_empty; +ERROR: cannot drop constraint valid_at_not_empty on table temporal_t1 because constraint temporal_t1_pk on table temporal_t1 requires it +HINT: You can drop constraint temporal_t1_pk on table temporal_t1 instead. +--ok, also valid_at_not_empty chek constraint will also be dropped. +alter table temporal_t1 drop constraint temporal_t1_pk; +--expect zero row +select * from validate_period_empty_dependency('temporal_t1'::regclass); + dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype +-------------------+--------------------+---------------------+--------------------+---------------------+--------- +(0 rows) + +DROP TABLE temporal_t1; +CREATE TABLE temporal_t2 (id int4range, valid_at daterange); +alter table temporal_t2 add constraint valid_at_not_empty CHECK (NOT isempty(valid_at)); +-- fail for now +ALTER TABLE temporal_t2 + ADD CONSTRAINT temporal_t2_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: constraint "valid_at_not_empty" for relation "temporal_t2" already exists +DROP TABLE temporal_t2; +CREATE TABLE temporal_t3 ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_t3_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)); +--should fail. +alter table temporal_t3 drop constraint valid_at_not_empty; +ERROR: cannot drop constraint valid_at_not_empty on table temporal_t3 because constraint temporal_t3_rng_pk on table temporal_t3 requires it +HINT: You can drop constraint temporal_t3_rng_pk on table temporal_t3 instead. +begin; +ALTER TABLE temporal_t3 + 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 +--should be fine with data type change. +select * from validate_period_empty_dependency('temporal_t3'::regclass); + dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype +--------------------+--------------------+---------------------+--------------------+---------------------+--------- + valid_at_not_empty | c | t | temporal_t3_rng_pk | p | i +(1 row) + +alter table temporal_t3 drop constraint temporal_t3_rng_pk; +rollback; +alter table temporal_t3 rename valid_at to valid_at1; +select * from validate_period_empty_dependency('temporal_t3'::regclass); + dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype +--------------------+--------------------+---------------------+--------------------+---------------------+--------- + valid_at_not_empty | c | t | temporal_t3_rng_pk | p | i +(1 row) + +select pg_get_constraintdef(oid) from pg_constraint +where conrelid = 'temporal_t3'::regclass +and contype = 'c' +and conperiod; + pg_get_constraintdef +---------------------------------- + CHECK ((NOT isempty(valid_at1))) +(1 row) + +alter table temporal_t3 drop constraint temporal_t3_rng_pk; +select * from validate_period_empty_dependency('temporal_t3'::regclass); + dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype +-------------------+--------------------+---------------------+--------------------+---------------------+--------- +(0 rows) + +DROP TABLE temporal_t3; +-- Add range column and the PK at the same time +CREATE TABLE temporal_t4 (id int4range); +ALTER TABLE temporal_t4 + ADD COLUMN valid_at daterange, + ADD CONSTRAINT temporal_t4_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +\d temporal_t4 + Table "public.temporal_t4" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | +Indexes: + "temporal_t4_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) + +select * from validate_period_empty_dependency('temporal_t4'::regclass); + dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype +--------------------+--------------------+---------------------+--------------------+---------------------+--------- + valid_at_not_empty | c | t | temporal_t4_pk | p | i +(1 row) + +DROP TABLE temporal_t4; +-- temporal partition table with check constraint +CREATE TABLE temporal_p ( + id int4range, + valid_at daterange, + name text, + CONSTRAINT temporal_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +CREATE TABLE temporal_p1 PARTITION OF temporal_p FOR VALUES IN ('[1,2)', '[2,3)'); +CREATE TABLE temporal_p2 PARTITION OF temporal_p FOR VALUES IN ('[3,4)', '[4,5)'); +\d+ temporal_p + Partitioned table "public.temporal_p" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +----------+-----------+-----------+----------+---------+----------+--------------+------------- + id | int4range | | not null | | extended | | + valid_at | daterange | | not null | | extended | | + name | text | | | | extended | | +Partition key: LIST (id) +Indexes: + "temporal_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) +Partitions: temporal_p1 FOR VALUES IN ('[1,2)', '[2,3)'), + temporal_p2 FOR VALUES IN ('[3,4)', '[4,5)') + +\d temporal_p1 + Table "public.temporal_p1" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | + name | text | | | +Partition of: temporal_p FOR VALUES IN ('[1,2)', '[2,3)') +Indexes: + "temporal_p1_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) + +alter table temporal_p1 drop constraint valid_at_not_empty; +ERROR: cannot drop inherited constraint "valid_at_not_empty" of relation "temporal_p1" +\d temporal_p2 + Table "public.temporal_p2" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | + name | text | | | +Partition of: temporal_p FOR VALUES IN ('[3,4)', '[4,5)') +Indexes: + "temporal_p2_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) + +alter table temporal_p2 drop constraint valid_at_not_empty; +ERROR: cannot drop inherited constraint "valid_at_not_empty" of relation "temporal_p2" +alter table temporal_p drop constraint temporal_pk; +\d+ temporal_p + Partitioned table "public.temporal_p" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +----------+-----------+-----------+----------+---------+----------+--------------+------------- + id | int4range | | | | extended | | + valid_at | daterange | | | | extended | | + name | text | | | | extended | | +Partition key: LIST (id) +Partitions: temporal_p1 FOR VALUES IN ('[1,2)', '[2,3)'), + temporal_p2 FOR VALUES IN ('[3,4)', '[4,5)') + +DROP TABLE temporal_p; +DROP FUNCTION validate_period_empty_dependency; -- PK with two columns plus a multirange: -- We don't drop this table because tests below also need multiple scalar columns. CREATE TABLE temporal_mltrng2 ( @@ -131,6 +344,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 +379,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, @@ -372,6 +588,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; @@ -626,6 +843,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) @@ -665,6 +884,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) @@ -672,6 +893,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) @@ -680,6 +902,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 @@ -1096,6 +1319,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) @@ -1135,6 +1360,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) @@ -1364,15 +1591,10 @@ CREATE TABLE temporal_box ( valid_at box, CONSTRAINT temporal_box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); +ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type +LINE 4: CONSTRAINT temporal_box_pk PRIMARY KEY (id, valid_at WITHO... + ^ \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, @@ -1381,8 +1603,9 @@ CREATE TABLE temporal_fk_box2box ( 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. +ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type +LINE 5: CONSTRAINT temporal_fk_box2box_pk PRIMARY KEY (id, valid_a... + ^ -- -- FK between partitioned tables -- diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index da2b7f19..05cb5a14 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -77,6 +77,126 @@ CREATE TABLE temporal_mltrng ( ); \d temporal_mltrng +--for PERIOD primary key and check constraint dependency. +create or replace function validate_period_empty_dependency(regclass) +returns table +( +dependent_conname name,dependent_con_type "char", +both_con_are_period bool,referenced_conname name, +referenced_con_type "char",deptype "char" +) +as $$ +begin + return query + select + pc.conname as dependent_conname, + pc.contype as dependent_con_type, + pc.conperiod = pc1.conperiod as both_con_are_period, + pc1.conname as referenced_conname, + pc1.contype as referenced_con_type, + pd.deptype + from pg_depend pd join pg_constraint pc on pc.oid = pd.objid + join pg_constraint pc1 on pc1.oid = pd.refobjid + where pd.refclassid = pd.classid + and pd.classid = (select oid + from pg_class + where relname = 'pg_constraint' + and relnamespace = 'pg_catalog'::regnamespace) + and pc.conrelid = pc1.conrelid + and pc.conrelid = $1 + ; +end; $$ language plpgsql; + +--pk with not_empty check constraint +CREATE TABLE temporal_t1 (id int4range, valid_at daterange); +ALTER TABLE temporal_t1 + ADD CONSTRAINT temporal_t1_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +select * from validate_period_empty_dependency('temporal_t1'::regclass); +select pg_get_constraintdef(oid) from pg_constraint +where conrelid = 'temporal_t1'::regclass +and contype = 'c' +and conperiod; + +--should fail, primary key depend on it +alter table temporal_t1 drop constraint valid_at_not_empty; + +--ok, also valid_at_not_empty chek constraint will also be dropped. +alter table temporal_t1 drop constraint temporal_t1_pk; + +--expect zero row +select * from validate_period_empty_dependency('temporal_t1'::regclass); +DROP TABLE temporal_t1; + + +CREATE TABLE temporal_t2 (id int4range, valid_at daterange); +alter table temporal_t2 add constraint valid_at_not_empty CHECK (NOT isempty(valid_at)); +-- fail for now +ALTER TABLE temporal_t2 + ADD CONSTRAINT temporal_t2_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE temporal_t2; + +CREATE TABLE temporal_t3 ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_t3_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)); +--should fail. +alter table temporal_t3 drop constraint valid_at_not_empty; + +begin; +ALTER TABLE temporal_t3 + ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date); +--should be fine with data type change. +select * from validate_period_empty_dependency('temporal_t3'::regclass); +alter table temporal_t3 drop constraint temporal_t3_rng_pk; +rollback; + +alter table temporal_t3 rename valid_at to valid_at1; +select * from validate_period_empty_dependency('temporal_t3'::regclass); + +select pg_get_constraintdef(oid) from pg_constraint +where conrelid = 'temporal_t3'::regclass +and contype = 'c' +and conperiod; + +alter table temporal_t3 drop constraint temporal_t3_rng_pk; +select * from validate_period_empty_dependency('temporal_t3'::regclass); + +DROP TABLE temporal_t3; + + +-- Add range column and the PK at the same time +CREATE TABLE temporal_t4 (id int4range); +ALTER TABLE temporal_t4 + ADD COLUMN valid_at daterange, + ADD CONSTRAINT temporal_t4_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +\d temporal_t4 + +select * from validate_period_empty_dependency('temporal_t4'::regclass); +DROP TABLE temporal_t4; + + +-- temporal partition table with check constraint +CREATE TABLE temporal_p ( + id int4range, + valid_at daterange, + name text, + CONSTRAINT temporal_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +CREATE TABLE temporal_p1 PARTITION OF temporal_p FOR VALUES IN ('[1,2)', '[2,3)'); +CREATE TABLE temporal_p2 PARTITION OF temporal_p FOR VALUES IN ('[3,4)', '[4,5)'); +\d+ temporal_p +\d temporal_p1 +alter table temporal_p1 drop constraint valid_at_not_empty; +\d temporal_p2 +alter table temporal_p2 drop constraint valid_at_not_empty; +alter table temporal_p drop constraint temporal_pk; +\d+ temporal_p +DROP TABLE temporal_p; + +DROP FUNCTION validate_period_empty_dependency; -- PK with two columns plus a multirange: -- We don't drop this table because tests below also need multiple scalar columns. CREATE TABLE temporal_mltrng2 ( -- 2.34.1 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-05-08 13:51 Peter Eisentraut <[email protected]> parent: Paul Jungwirth <[email protected]> 2 siblings, 0 replies; 17+ messages in thread From: Peter Eisentraut @ 2024-05-08 13:51 UTC (permalink / raw) To: Paul Jungwirth <[email protected]>; Robert Haas <[email protected]>; +Cc: jian he <[email protected]>; PostgreSQL Hackers <[email protected]> On 30.04.24 18:39, Paul Jungwirth wrote: > On 4/30/24 09:24, Robert Haas wrote: >> Peter, could you have a look at >> http://postgr.es/m/[email protected] >> 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 have committed v2-0002-Add-test-for-REPLICA-IDENTITY-with-a-temporal-key.patch. 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. 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. --- 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. * 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 /* not supported yet etc. */ if (idxForm->indixexclusion) next; ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-05-12 00:00 jian he <[email protected]> parent: jian he <[email protected]> 1 sibling, 1 reply; 17+ messages in thread From: jian he @ 2024-05-12 00:00 UTC (permalink / raw) To: Paul Jungwirth <[email protected]>; +Cc: Robert Haas <[email protected]>; Peter Eisentraut <[email protected]>; PostgreSQL Hackers <[email protected]> On Mon, May 6, 2024 at 11:01 AM jian he <[email protected]> wrote: > > On Wed, May 1, 2024 at 12:39 AM Paul Jungwirth > <[email protected]> wrote: > > > > On 4/30/24 09:24, Robert Haas wrote: > > > Peter, could you have a look at > > > http://postgr.es/m/[email protected] > > > 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. > > > > hi. > I hope I understand the problem correctly. > my understanding is that we are trying to solve a corner case: > create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS)); > insert into t values ('[1,2]','empty'), ('[1,2]','empty'); > but we still not yet address for cases like: create table t10(a int4range, b int4range, unique (a, b WITHOUT OVERLAPS)); insert into t10 values ('[1,2]','empty'), ('[1,2]','empty'); one table can have more than one temporal unique constraint, for each temporal unique constraint adding a check isempty constraint seems not easy. for example: CREATE TABLE t ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT t1 unique (id, valid_at WITHOUT OVERLAPS), CONSTRAINT t2 unique (parent_id, valid_at WITHOUT OVERLAPS), CONSTRAINT t3 unique (valid_at, id WITHOUT OVERLAPS), CONSTRAINT t4 unique (parent_id, id WITHOUT OVERLAPS), CONSTRAINT t5 unique (id, parent_id WITHOUT OVERLAPS), CONSTRAINT t6 unique (valid_at, parent_id WITHOUT OVERLAPS) ); add 6 check isempty constraints for table "t" is challenging. so far, I see the challenging part: * alter table alter column data type does not drop previous check isempty constraint, and will also add a check isempty constraint, so overall it will add more check constraints. * adding more check constraints needs a way to resolve naming collisions. Maybe we can just mention that the special 'empty' range value makes temporal unique constraints not "unique". also we can make sure that FOREIGN KEY can only reference primary keys, not unique temporal constraints. so the unique temporal constraints not "unique" implication is limited. I played around with it, we can error out these cases in the function transformFkeyCheckAttrs. ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-05-12 03:25 Paul Jungwirth <[email protected]> parent: jian he <[email protected]> 0 siblings, 0 replies; 17+ messages in thread From: Paul Jungwirth @ 2024-05-12 03:25 UTC (permalink / raw) To: jian he <[email protected]>; +Cc: Robert Haas <[email protected]>; Peter Eisentraut <[email protected]>; PostgreSQL Hackers <[email protected]> On 5/11/24 17:00, jian he wrote: >> I hope I understand the problem correctly. >> my understanding is that we are trying to solve a corner case: >> create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS)); >> insert into t values ('[1,2]','empty'), ('[1,2]','empty'); >> > > > but we still not yet address for cases like: > create table t10(a int4range, b int4range, unique (a, b WITHOUT OVERLAPS)); > insert into t10 values ('[1,2]','empty'), ('[1,2]','empty'); > > one table can have more than one temporal unique constraint, > for each temporal unique constraint adding a check isempty constraint > seems not easy. I think we should add the not-empty constraint only for PRIMARY KEYs, not all UNIQUE constraints. The empty edge case is very similar to the NULL edge case, and while every PK column must be non-null, we do allow nulls in ordinary UNIQUE constraints. If users want to have 'empty' in those constraints, I think we should let them. And then the problems you give don't arise. > Maybe we can just mention that the special 'empty' range value makes > temporal unique constraints not "unique". Just documenting the behavior is also an okay solution here I think. I see two downsides though: (1) it makes rangetype temporal keys differ from PERIOD temporal keys (2) it could allow more planner/etc bugs than we have thought of. So I think it's worth adding the constraint instead. > also we can make sure that > FOREIGN KEY can only reference primary keys, not unique temporal constraints. > so the unique temporal constraints not "unique" implication is limited. > I played around with it, we can error out these cases in the function > transformFkeyCheckAttrs. I don't think it is a problem to reference a temporal UNIQUE constraint, even if it contains empty values. An empty value means you're not asserting that row at any time (though another row might assert the same thing for some time), so it could never contribute toward fulfilling a reference anyway. I do think it would be nice if the *reference* could contain empty values. Right now the FK SQL will cause that to never match, because we use `&&` as an optimization, but we could tweak the SQL (maybe for v18 instead) so that users could get away with that kind of thing. As I said in an earlier email, this would be you an escape hatch to reference a temporal table from a non-temporal table. Otherwise temporal tables are "contagious," which is a bit of a drawback. Yours, -- Paul ~{:-) [email protected] ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-05-13 00:51 Paul Jungwirth <[email protected]> parent: jian he <[email protected]> 1 sibling, 0 replies; 17+ messages in thread From: Paul Jungwirth @ 2024-05-13 00:51 UTC (permalink / raw) To: jian he <[email protected]>; +Cc: Robert Haas <[email protected]>; Peter Eisentraut <[email protected]>; PostgreSQL Hackers <[email protected]> On 5/5/24 20:01, jian he wrote: > hi. > I hope I understand the problem correctly. > my understanding is that we are trying to solve a corner case: > create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS)); > insert into t values ('[1,2]','empty'), ('[1,2]','empty'); > > > I think the entry point is ATAddCheckNNConstraint and index_create. > in a chain of DDL commands, you cannot be sure which one > (primary key constraint or check constraint) is being created first, > you just want to make sure that after both constraints are created, > then add a dependency between primary key and check constraint. > > so you need to validate at different functions > (ATAddCheckNNConstraint, index_create) > that these two constraints are indeed created, > only after that we have a dependency linking these two constraints. > > > I've attached a patch trying to solve this problem. > the patch is not totally polished, but works as expected, and also has > lots of comments. Thanks for this! I've incorporated it into the CHECK constraint patch with some changes. In particular I thought index_create was a strange place to change the conperiod value of a pg_constraint record, and it is not actually needed if we are copying that value correctly. Some other comments on the patch file: > N.B. we also need to have special care for case > where check constraint was readded, e.g. ALTER TYPE. > if ALTER TYPE is altering the PERIOD column of the primary key, > alter column of primary key makes the index recreate, check constraint recreate, > however, former interally also including add a check constraint. > so we need to take care of merging two check constraint. This is a good point. I've included tests for this based on your patch. > N.B. the check constraint name is hard-wired, so if you create the constraint > with the same name, PERIOD primary key cannot be created. Yes, it may be worth doing something like other auto-named constraints and trying to avoid duplicates. I haven't taken that on yet; I'm curious what others have to say about it. > N.B. what about UNIQUE constraint? See my previous posts on this thread about allowing 'empty' in UNIQUE constraints. > N.B. seems ok to not care about FOREIGN KEY regarding this corner case? Agreed. v3 patches attached, rebased to 3ca43dbbb6. Yours, -- Paul ~{:-) [email protected] Attachments: [text/x-patch] v3-0001-Don-t-treat-WITHOUT-OVERLAPS-indexes-as-unique-in.patch (3.9K, ../../[email protected]/2-v3-0001-Don-t-treat-WITHOUT-OVERLAPS-indexes-as-unique-in.patch) download | inline diff: From 4f4428fb41ea79056a13e425826fdac9c7b5d349 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> Date: Tue, 2 Apr 2024 15:39:04 -0700 Subject: [PATCH v3 1/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 c3fd4a81f8a..dc8327d5769 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -814,8 +814,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; @@ -823,7 +823,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.42.0 [text/x-patch] v3-0002-Add-CHECK-NOT-isempty-constraint-to-PRIMARY-KEYs-.patch (74.6K, ../../[email protected]/3-v3-0002-Add-CHECK-NOT-isempty-constraint-to-PRIMARY-KEYs-.patch) download | inline diff: From 11ef535bc78ef9b50d8af089e6d71f20532a693c Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> Date: Tue, 9 Apr 2024 20:52:23 -0700 Subject: [PATCH v3 2/2] 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/catalogs.sgml | 4 +- doc/src/sgml/gist.sgml | 3 - doc/src/sgml/ref/create_table.sgml | 5 +- src/backend/access/common/tupdesc.c | 4 +- src/backend/catalog/heap.c | 30 +- src/backend/catalog/index.c | 30 ++ src/backend/catalog/pg_constraint.c | 1 + src/backend/commands/tablecmds.c | 154 +++++- src/backend/parser/parse_utilcmd.c | 91 +++- src/backend/utils/cache/relcache.c | 1 + src/include/access/tupdesc.h | 1 + src/include/catalog/heap.h | 1 + src/include/commands/tablecmds.h | 2 + .../regress/expected/without_overlaps.out | 479 ++++++++++++++++-- src/test/regress/sql/without_overlaps.sql | 248 ++++++++- 15 files changed, 964 insertions(+), 90 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index b530c030f01..31a55d56891 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2733,7 +2733,9 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <para> This constraint is defined with <literal>WITHOUT OVERLAPS</literal> (for primary keys and unique constraints) or <literal>PERIOD</literal> - (for foreign keys). + (for foreign keys). This is also true for the internal + <literal>CHECK</literal> constraint used by <literal>WITHOUT + OVERLAPS</literal> primary keys to prevent empty range/multirange values. </para></entry> </row> 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 75f06bc49cc..1209fda2451 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/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c index 47379fef220..194633d5c0b 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -226,6 +226,7 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc) cpy->check[i].ccbin = pstrdup(constr->check[i].ccbin); cpy->check[i].ccvalid = constr->check[i].ccvalid; cpy->check[i].ccnoinherit = constr->check[i].ccnoinherit; + cpy->check[i].ccperiod = constr->check[i].ccperiod; } } @@ -548,7 +549,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2) if (!(strcmp(check1->ccname, check2->ccname) == 0 && strcmp(check1->ccbin, check2->ccbin) == 0 && check1->ccvalid == check2->ccvalid && - check1->ccnoinherit == check2->ccnoinherit)) + check1->ccnoinherit == check2->ccnoinherit && + check1->ccperiod == check2->ccperiod)) return false; } } diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 922ba79ac25..88900ef7867 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -103,13 +103,13 @@ static ObjectAddress AddNewRelationType(const char *typeName, static void RelationRemoveInheritance(Oid relid); static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr, bool is_validated, bool is_local, int inhcount, - bool is_no_inherit, bool is_internal); + bool is_no_inherit, bool is_internal, bool without_overlaps); static void StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal); static bool MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr, bool allow_merge, bool is_local, bool is_initially_valid, - bool is_no_inherit); + bool is_no_inherit, bool conperiod); static void SetRelationNumChecks(Relation rel, int numchecks); static Node *cookConstraint(ParseState *pstate, Node *raw_constraint, @@ -2065,7 +2065,7 @@ SetAttrMissing(Oid relid, char *attname, char *value) static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr, bool is_validated, bool is_local, int inhcount, - bool is_no_inherit, bool is_internal) + bool is_no_inherit, bool is_internal, bool without_overlaps) { char *ccbin; List *varList; @@ -2155,7 +2155,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, is_local, /* conislocal */ inhcount, /* coninhcount */ is_no_inherit, /* connoinherit */ - false, /* conperiod */ + without_overlaps, /* conperiod */ is_internal); /* internally constructed? */ pfree(ccbin); @@ -2252,7 +2252,7 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal) StoreRelCheck(rel, con->name, con->expr, !con->skip_validation, con->is_local, con->inhcount, con->is_no_inherit, - is_internal); + is_internal, con->conperiod); numchecks++; break; @@ -2399,6 +2399,7 @@ AddRelationNewConstraints(Relation rel, cooked->is_local = is_local; cooked->inhcount = is_local ? 0 : 1; cooked->is_no_inherit = false; + cooked->conperiod = false; cookedConstraints = lappend(cookedConstraints, cooked); } @@ -2470,7 +2471,7 @@ AddRelationNewConstraints(Relation rel, if (MergeWithExistingConstraint(rel, ccname, expr, allow_merge, is_local, cdef->initially_valid, - cdef->is_no_inherit)) + cdef->is_no_inherit, cdef->without_overlaps)) continue; } else @@ -2518,7 +2519,8 @@ AddRelationNewConstraints(Relation rel, */ constrOid = StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local, - is_local ? 0 : 1, cdef->is_no_inherit, is_internal); + is_local ? 0 : 1, cdef->is_no_inherit, is_internal, + cdef->without_overlaps); numchecks++; @@ -2532,6 +2534,7 @@ AddRelationNewConstraints(Relation rel, cooked->is_local = is_local; cooked->inhcount = is_local ? 0 : 1; cooked->is_no_inherit = cdef->is_no_inherit; + cooked->conperiod = cdef->without_overlaps; cookedConstraints = lappend(cookedConstraints, cooked); } else if (cdef->contype == CONSTR_NOTNULL) @@ -2632,6 +2635,7 @@ AddRelationNewConstraints(Relation rel, nncooked->is_local = is_local; nncooked->inhcount = cdef->inhcount; nncooked->is_no_inherit = cdef->is_no_inherit; + nncooked->conperiod = false; cookedConstraints = lappend(cookedConstraints, nncooked); } @@ -2663,7 +2667,7 @@ static bool MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr, bool allow_merge, bool is_local, bool is_initially_valid, - bool is_no_inherit) + bool is_no_inherit, bool conperiod) { bool found; Relation conDesc; @@ -2758,6 +2762,16 @@ MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr, errmsg("constraint \"%s\" conflicts with NOT VALID constraint on relation \"%s\"", ccname, RelationGetRelationName(rel)))); + /* + * If either inherited or inheriting is a conperiod CHECK constraint, + * the other should be too. + */ + if (conperiod != con->conperiod) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("constraint \"%s\" conflicts with inherited constraint on relation \"%s\"", + ccname, RelationGetRelationName(rel)))); + /* OK to update the tuple */ ereport(NOTICE, (errmsg("merging constraint \"%s\" with inherited definition", diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 5a8568c55c9..fcba959c646 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1224,6 +1224,36 @@ index_create(Relation heapRelation, */ CommandCounterIncrement(); + /* + * If the index is a PRIMARY KEY with WITHOUT OVERLAPS, + * we must create a CHECK constraint to prevent range/multirange + * values of 'empty'. Since empty doesn't overlap itself, + * duplicates would be allowed. + */ + if (isprimary) + { + ObjectAddress pk_address = InvalidObjectAddress; + ObjectAddress check_address = InvalidObjectAddress; + Oid pk_oid = InvalidOid; + Oid check_oid = InvalidOid; + + if (get_pk_period_check_constraint(heapRelation, &check_oid, &pk_oid)) + { + Assert(OidIsValid(check_oid)); + Assert(OidIsValid(pk_oid)); + + ObjectAddressSet(check_address, ConstraintRelationId, check_oid); + ObjectAddressSet(pk_address, ConstraintRelationId, pk_oid); + + /* + * Register the CHECK constraint as an INTERNAL dependency of the PK + * so that it can't be dropped by hand and is dropped automatically + * with the PK. + */ + recordDependencyOn(&check_address, &pk_address, DEPENDENCY_INTERNAL); + } + } + /* * In bootstrap mode, we have to fill in the index strategy structure with * information from the catalogs. If we aren't bootstrapping, then the diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 12a73d5a309..925dacca10e 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -903,6 +903,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked) cooked->skip_validation = false; cooked->is_local = true; cooked->inhcount = 0; + cooked->conperiod = false; cooked->is_no_inherit = conForm->connoinherit; notnulls = lappend(notnulls, cooked); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index de0d911b468..356c244257e 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -364,7 +364,7 @@ static void RangeVarCallbackForTruncate(const RangeVar *relation, static List *MergeAttributes(List *columns, const List *supers, char relpersistence, bool is_partition, List **supconstr, List **supnotnulls); -static List *MergeCheckConstraint(List *constraints, const char *name, Node *expr); +static List *MergeCheckConstraint(List *constraints, const char *name, Node *expr, bool conperiod); static void MergeChildAttribute(List *inh_columns, int exist_attno, int newcol_attno, const ColumnDef *newdef); static ColumnDef *MergeInheritedAttribute(List *inh_columns, int exist_attno, const ColumnDef *newdef); static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispartition); @@ -584,7 +584,7 @@ static void RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab); static void RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab); static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode); -static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, +static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, bool conperiod, char *cmd, List **wqueue, LOCKMODE lockmode, bool rewrite); static void RebuildConstraintComment(AlteredTableInfo *tab, AlterTablePass pass, @@ -960,6 +960,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, cooked->is_local = true; /* not used for defaults */ cooked->inhcount = 0; /* ditto */ cooked->is_no_inherit = false; + cooked->conperiod = false; cookedDefaults = lappend(cookedDefaults, cooked); attr->atthasdef = true; } @@ -2811,6 +2812,7 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, nn->is_local = false; nn->inhcount = 1; nn->is_no_inherit = false; + nn->conperiod = false; nnconstraints = lappend(nnconstraints, nn); } @@ -2922,7 +2924,7 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, name, RelationGetRelationName(relation)))); - constraints = MergeCheckConstraint(constraints, name, expr); + constraints = MergeCheckConstraint(constraints, name, expr, check[i].ccperiod); } } @@ -3140,7 +3142,7 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, * the list. */ static List * -MergeCheckConstraint(List *constraints, const char *name, Node *expr) +MergeCheckConstraint(List *constraints, const char *name, Node *expr, bool conperiod) { ListCell *lc; CookedConstraint *newcon; @@ -3155,7 +3157,8 @@ MergeCheckConstraint(List *constraints, const char *name, Node *expr) if (strcmp(ccon->name, name) != 0) continue; - if (equal(expr, ccon->expr)) + /* Expressions match, and conperiod matches */ + if (equal(expr, ccon->expr) && ccon->conperiod == conperiod) { /* OK to merge constraint with existing */ ccon->inhcount++; @@ -3181,6 +3184,7 @@ MergeCheckConstraint(List *constraints, const char *name, Node *expr) newcon->name = pstrdup(name); newcon->expr = expr; newcon->inhcount = 1; + newcon->conperiod = conperiod; return lappend(constraints, newcon); } @@ -9727,6 +9731,94 @@ ChooseForeignKeyConstraintNameAddition(List *colnames) return pstrdup(buf); } +/* + * Gets the temporal PRIMARY KEY constraint oid and its not-empty CHECK constraint. + * Returns true if we found both, or else false (e.g. if the table has no PK + * or it doesn't use WITHOUT OVERLAPS). + * + * We may create the PRIMARY KEY first or the CHECK constraint first, + * depending on the operation (create-vs-alter table, with-vs-without partitioning + * or inheritance, re-adding an index from ALTER TYPE but keeping the CHECK constraint), + * so we do nothing unless both are found. + */ +bool +get_pk_period_check_constraint(Relation heapRel, Oid *check_oid, Oid *pk_oid) +{ + Relation pg_constraint; + HeapTuple conTup; + SysScanDesc scan; + ScanKeyData key; + ArrayType *arr; + bool isNull; + Datum adatum; + int numkeys; + int16 *attnums; + int16 pk_period_attnum = -1; + int16 check_period_attnum = -1; + + pg_constraint = table_open(ConstraintRelationId, AccessShareLock); + ScanKeyInit(&key, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + RelationGetRelid(heapRel)); + + scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, + true, NULL, 1, &key); + + while (HeapTupleIsValid(conTup = systable_getnext(scan))) + { + Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup); + + /* + * Find both the PRIMARY KEY and the CHECK constraint. + * They should be validated and with true conperiod. + */ + if (con->contype != CONSTRAINT_PRIMARY && con->contype != CONSTRAINT_CHECK) + continue; + if (!con->convalidated) + continue; + if (!con->conperiod) + continue; + + adatum = heap_getattr(conTup, Anum_pg_constraint_conkey, + RelationGetDescr(pg_constraint), &isNull); + if (isNull) + elog(ERROR, "null conkey for constraint %u", con->oid); + + arr = DatumGetArrayTypeP(adatum); + numkeys = ARR_DIMS(arr)[0]; + if (ARR_NDIM(arr) != 1 || + numkeys < 0 || + ARR_HASNULL(arr) || + ARR_ELEMTYPE(arr) != INT2OID) + elog(ERROR, "conkey is not a 1-D smallint array"); + + attnums = (int16 *) ARR_DATA_PTR(arr); + if (con->contype == CONSTRAINT_PRIMARY) + { + pk_period_attnum = attnums[numkeys - 1]; + *pk_oid = con->oid; + } + else + { + check_period_attnum = attnums[numkeys - 1]; + *check_oid = con->oid; + } + } + systable_endscan(scan); + table_close(pg_constraint, AccessShareLock); + + if (pk_period_attnum != -1 && check_period_attnum != -1) + { + if (check_period_attnum != pk_period_attnum) + elog(ERROR, "WITHOUT OVERLAPS CHECK constraint should match the PRIMARY KEY attribute"); + + return true; + } + else + return false; +} + /* * Add a check or not-null constraint to a single table and its children. * Returns the address of the constraint added to the parent relation, @@ -9819,6 +9911,34 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, /* Advance command counter in case same table is visited multiple times */ CommandCounterIncrement(); + /* + * If this is a not-empty CHECK constraint for a WITHOUT OVERLAPS PK, + * we must record it as an INTERNAL dependency. + */ + if (constr->without_overlaps) + { + ObjectAddress pk_address = InvalidObjectAddress; + ObjectAddress check_address = InvalidObjectAddress; + Oid pk_oid = InvalidOid; + Oid check_oid = InvalidOid; + + if (get_pk_period_check_constraint(rel, &check_oid, &pk_oid)) + { + Assert(OidIsValid(check_oid)); + Assert(OidIsValid(pk_oid)); + + ObjectAddressSet(check_address, ConstraintRelationId, check_oid); + ObjectAddressSet(pk_address, ConstraintRelationId, pk_oid); + + /* + * Register the CHECK constraint as an INTERNAL dependency of the PK + * so that it can't be dropped by hand and is dropped automatically + * with the PK. + */ + recordDependencyOn(&check_address, &pk_address, DEPENDENCY_INTERNAL); + } + } + /* * If the constraint got merged with an existing constraint, we're done. * We mustn't recurse to child tables in this case, because they've @@ -14455,6 +14575,7 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) Oid confrelid; char contype; bool conislocal; + bool conperiod; tup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(oldId)); if (!HeapTupleIsValid(tup)) /* should not happen */ @@ -14472,6 +14593,7 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) confrelid = con->confrelid; contype = con->contype; conislocal = con->conislocal; + conperiod = con->conperiod; ReleaseSysCache(tup); ObjectAddressSet(obj, ConstraintRelationId, oldId); @@ -14496,7 +14618,7 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) if (relid != tab->relid && contype == CONSTRAINT_FOREIGN) LockRelationOid(relid, AccessExclusiveLock); - ATPostAlterTypeParse(oldId, relid, confrelid, + ATPostAlterTypeParse(oldId, relid, confrelid, conperiod, (char *) lfirst(def_item), wqueue, lockmode, tab->rewrite); } @@ -14507,7 +14629,7 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) Oid relid; relid = IndexGetRelation(oldId, false); - ATPostAlterTypeParse(oldId, relid, InvalidOid, + ATPostAlterTypeParse(oldId, relid, InvalidOid, false, (char *) lfirst(def_item), wqueue, lockmode, tab->rewrite); @@ -14523,7 +14645,7 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) Oid relid; relid = StatisticsGetRelation(oldId, false); - ATPostAlterTypeParse(oldId, relid, InvalidOid, + ATPostAlterTypeParse(oldId, relid, InvalidOid, false, (char *) lfirst(def_item), wqueue, lockmode, tab->rewrite); @@ -14587,8 +14709,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) * operator that's not available for the new column type. */ static void -ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd, - List **wqueue, LOCKMODE lockmode, bool rewrite) +ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, bool conperiod, + char *cmd, List **wqueue, LOCKMODE lockmode, bool rewrite) { List *raw_parsetree_list; List *querytree_list; @@ -14717,6 +14839,8 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd, !rewrite && tab->rewrite == 0) TryReuseForeignKey(oldId, con); con->reset_default_tblspc = true; + /* preserve conperiod */ + con->without_overlaps = conperiod; cmd->subtype = AT_ReAddConstraint; tab->subcmds[AT_PASS_OLD_CONSTR] = lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd); @@ -16699,6 +16823,16 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) errmsg("constraint \"%s\" conflicts with NOT VALID constraint on child table \"%s\"", NameStr(child_con->conname), RelationGetRelationName(child_rel)))); + /* + * If the parent and child CHECK constraints have different conperiod values, + * don't merge them. + */ + if (parent_con->contype == CONSTRAINT_CHECK && + parent_con->conperiod != child_con->conperiod) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("constraint \"%s\" conflicts with inherited constraint on child table \"%s\"", + NameStr(child_con->conname), RelationGetRelationName(child_rel)))); /* * OK, bump the child constraint's inheritance count. (If we fail * later on, this change will just roll back.) diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 0598e897d90..db7bd53d6b5 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -2301,6 +2301,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) @@ -2564,7 +2566,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) * For UNIQUE and PRIMARY KEY, we just have a list of column names. * * Make sure referenced keys exist. If we are making a PRIMARY KEY index, - * also make sure they are not-null. + * also make sure they are not-null. For WITHOUT OVERLAPS constraints, + * we make sure the last part is a range or multirange. */ else { @@ -2575,6 +2578,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) ColumnDef *column = NULL; ListCell *columns; IndexElem *iparam; + Oid typid = InvalidOid; /* Make sure referenced column exists. */ foreach(columns, cxt->columns) @@ -2642,6 +2646,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) if (strcmp(key, inhname) == 0) { found = true; + typid = inhattr->atttypid; break; } } @@ -2683,6 +2688,49 @@ 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)) + { + 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; + } + } + } + if (found) + { + if (!OidIsValid(typid)) + 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); @@ -2719,8 +2767,47 @@ 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->without_overlaps = true; + 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/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 262c9878dd3..1b4590e1212 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4581,6 +4581,7 @@ CheckConstraintFetch(Relation relation) check[found].ccvalid = conform->convalidated; check[found].ccnoinherit = conform->connoinherit; + check[found].ccperiod = conform->conperiod; check[found].ccname = MemoryContextStrdup(CacheMemoryContext, NameStr(conform->conname)); diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h index 8930a28d660..f2adffe4bd1 100644 --- a/src/include/access/tupdesc.h +++ b/src/include/access/tupdesc.h @@ -31,6 +31,7 @@ typedef struct ConstrCheck char *ccbin; /* nodeToString representation of expr */ bool ccvalid; bool ccnoinherit; /* this is a non-inheritable constraint */ + bool ccperiod; /* used by WITHOUT OVERLAPS PRIMARY KEY */ } ConstrCheck; /* This structure contains constraints of a tuple */ diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index e446d49b3ea..54f5b792e7b 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -45,6 +45,7 @@ typedef struct CookedConstraint int inhcount; /* number of times constraint is inherited */ bool is_no_inherit; /* constraint has local def and cannot be * inherited */ + bool conperiod; /* constraint is for WITHOUT OVERLAPS */ } CookedConstraint; extern Relation heap_create(const char *relname, diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index 85cbad3d0c2..6a92e55988b 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -27,6 +27,8 @@ struct AlterTableUtilityContext; /* avoid including tcop/utility.h here */ extern ObjectAddress DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, ObjectAddress *typaddress, const char *queryString); +extern bool get_pk_period_check_constraint(Relation heapRel, Oid *check_oid, Oid *pk_oid); + extern TupleDesc BuildDescForRelation(const List *columns); extern void RemoveRelations(DropStmt *drop); diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index e2f2a1cbe20..8f16af2f7b0 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 @@ -59,6 +62,108 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) (1 row) +-- can't drop the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty; +ERROR: cannot drop constraint valid_at_not_empty on table temporal_rng because constraint temporal_rng_pk on table temporal_rng requires it +HINT: You can drop constraint temporal_rng_pk on table temporal_rng instead. +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; +\d temporal_rng + Table "public.temporal_rng" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | | + valid_at | daterange | | | + +-- fail if a constraint with that name already exists: +ALTER TABLE temporal_rng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)); +ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: constraint "valid_at_not_empty" for relation "temporal_rng" already exists +DROP TABLE temporal_rng; +-- PK from LIKE: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL); +\d temporal_rng2 + Table "public.temporal_rng2" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | +Indexes: + "temporal_rng2_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) + +DROP TABLE temporal_rng2; +CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL, CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at))); +ERROR: constraint "valid_at_not_empty" for relation "temporal_rng2" already exists +DROP TABLE temporal_rng; +-- PK from INHERITS: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +CREATE TABLE temporal_rng2 () INHERITS (temporal_rng); +\d temporal_rng2 + Table "public.temporal_rng2" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) +Inherits: temporal_rng + +DROP TABLE temporal_rng2; +CREATE TABLE temporal_rng2 (CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at))) INHERITS (temporal_rng); +ERROR: constraint "valid_at_not_empty" conflicts with inherited constraint on relation "temporal_rng2" +DROP TABLE temporal_rng; +-- PK in inheriting table: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange +); +CREATE TABLE temporal_rng2 (CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)) INHERITS (temporal_rng); +\d temporal_rng2 + Table "public.temporal_rng2" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | +Indexes: + "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) +Inherits: temporal_rng + +DROP TABLE temporal_rng CASCADE; +NOTICE: drop cascades to table temporal_rng2 +-- PK in inheriting table with conflicting CHECK constraint in parent: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)) +); +CREATE TABLE temporal_rng2 (CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)) INHERITS (temporal_rng); +ERROR: constraint "valid_at_not_empty" conflicts with inherited constraint on relation "temporal_rng2" +DROP TABLE temporal_rng; +-- add PK to already inheriting table: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)) +); +CREATE TABLE temporal_rng2 () INHERITS (temporal_rng); +ALTER TABLE temporal_rng2 + ADD CONSTRAINT temporal_rng2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: constraint "valid_at_not_empty" conflicts with inherited constraint on relation "temporal_rng2" +DROP TABLE temporal_rng2; +DROP TABLE temporal_rng; -- PK with two columns plus a range: -- We don't drop this table because tests below also need multiple scalar columns. CREATE TABLE temporal_rng2 ( @@ -76,6 +181,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,7 +220,33 @@ 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)) +-- can't drop the CHECK constraint: +ALTER TABLE temporal_mltrng DROP CONSTRAINT valid_at_not_empty; +ERROR: cannot drop constraint valid_at_not_empty on table temporal_mltrng because constraint temporal_mltrng_pk on table temporal_mltrng requires it +HINT: You can drop constraint temporal_mltrng_pk on table temporal_mltrng instead. +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_rng_pk; +ERROR: constraint "temporal_rng_pk" of relation "temporal_mltrng" does not exist +\d temporal_mltrng + Table "public.temporal_mltrng" + Column | Type | Collation | Nullable | Default +----------+----------------+-----------+----------+--------- + id | int4range | | not null | + valid_at | datemultirange | | not null | +Indexes: + "temporal_mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) + +-- fail if a constraint with that name already exists: +ALTER TABLE temporal_mltrng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)); +ERROR: constraint "valid_at_not_empty" for relation "temporal_mltrng" already exists +ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: multiple primary keys for table "temporal_mltrng" are not allowed +DROP TABLE temporal_mltrng; -- PK with two columns plus a multirange: -- We don't drop this table because tests below also need multiple scalar columns. CREATE TABLE temporal_mltrng2 ( @@ -131,6 +264,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 @@ -144,6 +279,72 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te CREATE UNIQUE INDEX temporal_mltrng2_pk ON temporal_mltrng2 USING gist (id1, id2, valid_at) (1 row) +-- test when the WITHOUT OVERLAPS column type changes: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_rng + ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at), upper(valid_at)); +NOTICE: merging constraint "valid_at_not_empty" with inherited definition +\d temporal_rng + Table "public.temporal_rng" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | +Indexes: + "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) + +-- can't drop the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty; +ERROR: cannot drop constraint valid_at_not_empty on table temporal_rng because constraint temporal_rng_pk on table temporal_rng requires it +HINT: You can drop constraint temporal_rng_pk on table temporal_rng instead. +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; +\d temporal_rng + Table "public.temporal_rng" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | | + valid_at | daterange | | | + +DROP TABLE temporal_rng; +-- test when the WITHOUT OVERLAPS column name changes: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_rng RENAME valid_at TO valid_at1; +\d temporal_rng + Table "public.temporal_rng" + Column | Type | Collation | Nullable | Default +-----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at1 | daterange | | not null | +Indexes: + "temporal_rng_pk" PRIMARY KEY (id, valid_at1 WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at1)) + +-- can't drop the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty; +ERROR: cannot drop constraint valid_at_not_empty on table temporal_rng because constraint temporal_rng_pk on table temporal_rng requires it +HINT: You can drop constraint temporal_rng_pk on table temporal_rng instead. +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; +\d temporal_rng + Table "public.temporal_rng" + Column | Type | Collation | Nullable | Default +-----------+-----------+-----------+----------+--------- + id | int4range | | | + valid_at1 | daterange | | | + +DROP TABLE temporal_rng; -- UNIQUE with no columns just WITHOUT OVERLAPS: CREATE TABLE temporal_rng3 ( valid_at daterange, @@ -164,8 +365,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, @@ -237,7 +439,6 @@ DROP TYPE textrange2; -- -- test ALTER TABLE ADD CONSTRAINT -- -DROP TABLE temporal_rng; CREATE TABLE temporal_rng ( id int4range, valid_at daterange @@ -245,6 +446,49 @@ CREATE TABLE temporal_rng ( ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- can't drop the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty; +ERROR: cannot drop constraint valid_at_not_empty on table temporal_rng because constraint temporal_rng_pk on table temporal_rng requires it +HINT: You can drop constraint temporal_rng_pk on table temporal_rng instead. +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; +\d temporal_rng + Table "public.temporal_rng" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | | + valid_at | daterange | | | + +-- fail if a constraint with that name already exists: +ALTER TABLE temporal_rng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)); +ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: constraint "valid_at_not_empty" for relation "temporal_rng" already exists +DROP TABLE temporal_rng; +CREATE TABLE temporal_mltrng ( + id int4range, + valid_at datemultirange +); +ALTER TABLE temporal_mltrng + ADD CONSTRAINT temporal_mltrng_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- can't drop the CHECK constraint: +ALTER TABLE temporal_mltrng DROP CONSTRAINT valid_at_not_empty; +ERROR: cannot drop constraint valid_at_not_empty on table temporal_mltrng because constraint temporal_mltrng_pk on table temporal_mltrng requires it +HINT: You can drop constraint temporal_mltrng_pk on table temporal_mltrng instead. +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_mltrng_pk; +\d temporal_mltrng + Table "public.temporal_mltrng" + Column | Type | Collation | Nullable | Default +----------+----------------+-----------+----------+--------- + id | int4range | | | + valid_at | datemultirange | | | + +-- fail if a constraint with that name already exists: +ALTER TABLE temporal_mltrng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)); +ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: constraint "valid_at_not_empty" for relation "temporal_mltrng" already exists +DROP TABLE temporal_mltrng; -- PK with USING INDEX (not possible): CREATE TABLE temporal3 ( id int4range, @@ -292,6 +536,23 @@ ALTER TABLE temporal3 ADD COLUMN valid_at daterange, ADD CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- can't drop the CHECK constraint: +ALTER TABLE temporal3 DROP CONSTRAINT valid_at_not_empty; +ERROR: cannot drop constraint valid_at_not_empty on table temporal3 because constraint temporal3_pk on table temporal3 requires it +HINT: You can drop constraint temporal3_pk on table temporal3 instead. +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal3 DROP CONSTRAINT temporal3_pk; +\d temporal3 + Table "public.temporal3" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | | + valid_at | daterange | | | + +-- fail if a constraint with that name already exists: +ALTER TABLE temporal3 ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)); +ALTER TABLE temporal3 ADD CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: constraint "valid_at_not_empty" for relation "temporal3" already exists DROP TABLE temporal3; -- Add range column and UNIQUE constraint at the same time CREATE TABLE temporal3 ( @@ -305,6 +566,11 @@ DROP TABLE temporal3; -- -- test PK inserts -- +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); -- okay: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); @@ -320,6 +586,14 @@ 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). +CREATE TABLE temporal_mltrng ( + id int4range, + valid_at datemultirange, + CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); -- 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 +609,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 +621,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 +700,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; @@ -383,10 +712,106 @@ CREATE TABLE temporal_partitioned ( id int4range, valid_at daterange, name text, - CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) + CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +-- the CHECK constraint is copied: +\d tp1 + Table "public.tp1" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | + name | text | | | +Partition of: temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)') +Indexes: + "tp1_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) + +-- can't drop the CHECK constraint: +ALTER TABLE tp1 DROP CONSTRAINT valid_at_not_empty; +ERROR: cannot drop inherited constraint "valid_at_not_empty" of relation "tp1" +-- dropping the PK drops all the CHECK constraints: +ALTER TABLE temporal_partitioned DROP CONSTRAINT temporal_partitioned_pk; +\d temporal_partitioned + Partitioned table "public.temporal_partitioned" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | | + valid_at | daterange | | | + name | text | | | +Partition key: LIST (id) +Number of partitions: 2 (Use \d+ to list them.) + +\d tp1 + Table "public.tp1" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | | + valid_at | daterange | | | + name | text | | | +Partition of: temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)') + +-- Now create the temporal PK with ALTER: +ALTER TABLE temporal_partitioned + ADD CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- the CHECK constraint is copied: +\d temporal_partitioned + Partitioned table "public.temporal_partitioned" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | + name | text | | | +Partition key: LIST (id) +Indexes: + "temporal_partitioned_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) +Number of partitions: 2 (Use \d+ to list them.) + +\d tp1 + Table "public.tp1" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | + name | text | | | +Partition of: temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)') +Indexes: + "tp1_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Check constraints: + "valid_at_not_empty" CHECK (NOT isempty(valid_at)) + +-- can't drop the CHECK constraint: +ALTER TABLE tp1 DROP CONSTRAINT valid_at_not_empty; +ERROR: cannot drop inherited constraint "valid_at_not_empty" of relation "tp1" +-- dropping the PK drops all the CHECK constraints: +ALTER TABLE temporal_partitioned DROP CONSTRAINT temporal_partitioned_pk; +\d temporal_partitioned + Partitioned table "public.temporal_partitioned" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | | + valid_at | daterange | | | + name | text | | | +Partition key: LIST (id) +Number of partitions: 2 (Use \d+ to list them.) + +\d tp1 + Table "public.tp1" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | | + valid_at | daterange | | | + name | text | | | +Partition of: temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)') + +-- Restore the PK and test some INSERTs: +ALTER TABLE temporal_partitioned + ADD CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), @@ -418,7 +843,7 @@ CREATE TABLE temporal_partitioned ( id int4range, valid_at daterange, name text, - CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) + CONSTRAINT temporal_partitioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); @@ -806,6 +1231,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 +1272,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 +1281,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 +1290,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 +1707,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 +1748,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,41 +1971,13 @@ 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 ( id int4range, valid_at daterange, name text, - CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) + CONSTRAINT temporal_partitioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index 5d41a6bd628..7d32d410f0c 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -45,6 +45,68 @@ CREATE TABLE temporal_rng ( \d temporal_rng SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; +-- can't drop the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty; +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; +\d temporal_rng +-- fail if a constraint with that name already exists: +ALTER TABLE temporal_rng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)); +ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE temporal_rng; + +-- PK from LIKE: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL); +\d temporal_rng2 +DROP TABLE temporal_rng2; +CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL, CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at))); +DROP TABLE temporal_rng; + +-- PK from INHERITS: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +CREATE TABLE temporal_rng2 () INHERITS (temporal_rng); +\d temporal_rng2 +DROP TABLE temporal_rng2; +CREATE TABLE temporal_rng2 (CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at))) INHERITS (temporal_rng); +DROP TABLE temporal_rng; + +-- PK in inheriting table: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange +); +CREATE TABLE temporal_rng2 (CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)) INHERITS (temporal_rng); +\d temporal_rng2 +DROP TABLE temporal_rng CASCADE; +-- PK in inheriting table with conflicting CHECK constraint in parent: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)) +); +CREATE TABLE temporal_rng2 (CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)) INHERITS (temporal_rng); +DROP TABLE temporal_rng; + +-- add PK to already inheriting table: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)) +); +CREATE TABLE temporal_rng2 () INHERITS (temporal_rng); +ALTER TABLE temporal_rng2 + ADD CONSTRAINT temporal_rng2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE temporal_rng2; +DROP TABLE temporal_rng; -- PK with two columns plus a range: -- We don't drop this table because tests below also need multiple scalar columns. @@ -76,6 +138,15 @@ CREATE TABLE temporal_mltrng ( CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); \d temporal_mltrng +-- can't drop the CHECK constraint: +ALTER TABLE temporal_mltrng DROP CONSTRAINT valid_at_not_empty; +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_rng_pk; +\d temporal_mltrng +-- fail if a constraint with that name already exists: +ALTER TABLE temporal_mltrng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)); +ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE temporal_mltrng; -- PK with two columns plus a multirange: -- We don't drop this table because tests below also need multiple scalar columns. @@ -89,6 +160,37 @@ CREATE TABLE temporal_mltrng2 ( SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; +-- test when the WITHOUT OVERLAPS column type changes: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_rng + ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at), upper(valid_at)); +\d temporal_rng +-- can't drop the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty; +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; +\d temporal_rng +DROP TABLE temporal_rng; + +-- test when the WITHOUT OVERLAPS column name changes: +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_rng RENAME valid_at TO valid_at1; +\d temporal_rng +-- can't drop the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty; +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; +\d temporal_rng +DROP TABLE temporal_rng; + -- UNIQUE with no columns just WITHOUT OVERLAPS: CREATE TABLE temporal_rng3 ( @@ -150,7 +252,6 @@ DROP TYPE textrange2; -- test ALTER TABLE ADD CONSTRAINT -- -DROP TABLE temporal_rng; CREATE TABLE temporal_rng ( id int4range, valid_at daterange @@ -158,8 +259,35 @@ CREATE TABLE temporal_rng ( ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- can't drop the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty; +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; +\d temporal_rng +-- fail if a constraint with that name already exists: +ALTER TABLE temporal_rng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)); +ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE temporal_rng; + +CREATE TABLE temporal_mltrng ( + id int4range, + valid_at datemultirange +); +ALTER TABLE temporal_mltrng + ADD CONSTRAINT temporal_mltrng_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- can't drop the CHECK constraint: +ALTER TABLE temporal_mltrng DROP CONSTRAINT valid_at_not_empty; +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_mltrng_pk; +\d temporal_mltrng +-- fail if a constraint with that name already exists: +ALTER TABLE temporal_mltrng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)); +ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE temporal_mltrng; -- PK with USING INDEX (not possible): + CREATE TABLE temporal3 ( id int4range, valid_at daterange @@ -200,6 +328,14 @@ ALTER TABLE temporal3 ADD COLUMN valid_at daterange, ADD CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- can't drop the CHECK constraint: +ALTER TABLE temporal3 DROP CONSTRAINT valid_at_not_empty; +-- dropping the PK drops the CHECK constraint: +ALTER TABLE temporal3 DROP CONSTRAINT temporal3_pk; +\d temporal3 +-- fail if a constraint with that name already exists: +ALTER TABLE temporal3 ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)); +ALTER TABLE temporal3 ADD CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); DROP TABLE temporal3; -- Add range column and UNIQUE constraint at the same time @@ -216,6 +352,12 @@ DROP TABLE temporal3; -- test PK inserts -- +CREATE TABLE temporal_rng ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); + -- okay: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); @@ -226,6 +368,13 @@ 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'); + +CREATE TABLE temporal_mltrng ( + id int4range, + valid_at datemultirange, + CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); -- okay: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); @@ -237,9 +386,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 -- @@ -284,10 +480,33 @@ CREATE TABLE temporal_partitioned ( id int4range, valid_at daterange, name text, - CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) + CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +-- the CHECK constraint is copied: +\d tp1 +-- can't drop the CHECK constraint: +ALTER TABLE tp1 DROP CONSTRAINT valid_at_not_empty; +-- dropping the PK drops all the CHECK constraints: +ALTER TABLE temporal_partitioned DROP CONSTRAINT temporal_partitioned_pk; +\d temporal_partitioned +\d tp1 +-- Now create the temporal PK with ALTER: +ALTER TABLE temporal_partitioned + ADD CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- the CHECK constraint is copied: +\d temporal_partitioned +\d tp1 +-- can't drop the CHECK constraint: +ALTER TABLE tp1 DROP CONSTRAINT valid_at_not_empty; +-- dropping the PK drops all the CHECK constraints: +ALTER TABLE temporal_partitioned DROP CONSTRAINT temporal_partitioned_pk; +\d temporal_partitioned +\d tp1 +-- Restore the PK and test some INSERTs: +ALTER TABLE temporal_partitioned + ADD CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), @@ -302,7 +521,7 @@ CREATE TABLE temporal_partitioned ( id int4range, valid_at daterange, name text, - CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) + CONSTRAINT temporal_partitioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); @@ -1273,27 +1492,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 -- @@ -1302,7 +1500,7 @@ CREATE TABLE temporal_partitioned_rng ( id int4range, valid_at daterange, name text, - CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) + CONSTRAINT temporal_partitioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); -- 2.42.0 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-05-13 10:11 Peter Eisentraut <[email protected]> parent: Paul Jungwirth <[email protected]> 2 siblings, 1 reply; 17+ messages in thread From: Peter Eisentraut @ 2024-05-13 10:11 UTC (permalink / raw) To: Paul Jungwirth <[email protected]>; jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On 03.04.24 07:30, Paul Jungwirth wrote: > 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. > 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. It looks like we missed some of these fundamental design questions early on, and it might be too late now to fix them for PG17. For example, the discussion on unique constraints misses that the question of null values in unique constraints itself is controversial and that there is now a way to change the behavior. So I imagine there is also a selection of possible behaviors you might want for empty ranges. Intuitively, I don't think empty ranges are sensible for temporal unique constraints. But anyway, it's a bit late now to be discussing this. I'm also concerned that if ranges have this fundamental incompatibility with periods, then the plan to eventually evolve this patch set to support standard periods will also have as-yet-unknown problems. Some of these issues might be design flaws in the underlying mechanisms, like range types and exclusion constraints. Like, if you're supposed to use this for scheduling but you can use empty ranges to bypass exclusion constraints, how is one supposed to use this? Yes, a check constraint using isempty() might be the right answer. But I don't see this documented anywhere. On the technical side, adding an implicit check constraint as part of a primary key constraint is quite a difficult implementation task, as I think you are discovering. I'm just reminded about how the patch for catalogued not-null constraints struggled with linking these not-null constraints to primary keys correctly. This sounds a bit similar. I'm afraid that these issues cannot be resolved in good time for this release, so we should revert this patch set for now. ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-05-16 23:22 Jeff Davis <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 17+ messages in thread From: Jeff Davis @ 2024-05-16 23:22 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; Paul Jungwirth <[email protected]>; jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Mon, 2024-05-13 at 12:11 +0200, Peter Eisentraut wrote: > Some of these issues might be design flaws in the underlying > mechanisms, > like range types and exclusion constraints. Like, if you're supposed > to > use this for scheduling but you can use empty ranges to bypass > exclusion > constraints, how is one supposed to use this? An empty range does not "bypass" the an exclusion constraint. The exclusion constraint has a documented meaning and it's enforced. Of course there are situations where an empty range doesn't make a lot of sense. For many domains zero doesn't make any sense, either. Consider receiving an email saying "thank you for purchasing 0 widgets!". Check constraints seem like a reasonable way to prevent those kinds of problems. Regards, Jeff Davis ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-05-21 17:57 Robert Haas <[email protected]> parent: Jeff Davis <[email protected]> 0 siblings, 1 reply; 17+ messages in thread From: Robert Haas @ 2024-05-21 17:57 UTC (permalink / raw) To: Jeff Davis <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Paul Jungwirth <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]> On Thu, May 16, 2024 at 7:22 PM Jeff Davis <[email protected]> wrote: > An empty range does not "bypass" the an exclusion constraint. The > exclusion constraint has a documented meaning and it's enforced. > > Of course there are situations where an empty range doesn't make a lot > of sense. For many domains zero doesn't make any sense, either. > Consider receiving an email saying "thank you for purchasing 0 > widgets!". Check constraints seem like a reasonable way to prevent > those kinds of problems. I think that's true. Having infinitely many events zero-length events scheduled at the same point in time isn't necessarily a problem: I can attend an infinite number of simultaneous meetings if I only need to attend them for exactly zero time. What I think is less clear is what that means for temporal primary keys. As Paul pointed out upthread, in every other case, a temporal primary key is at least as unique as a regular primary key, but in this case, it isn't. And someone might reasonably think that a temporal primary key should exclude empty ranges just as all primary keys exclude nulls. Or they might think the opposite. At least, so it seems to me. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL:2011 application time @ 2024-05-21 19:54 Jeff Davis <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 0 replies; 17+ messages in thread From: Jeff Davis @ 2024-05-21 19:54 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Paul Jungwirth <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]> On Tue, 2024-05-21 at 13:57 -0400, Robert Haas wrote: > What I think is less clear is what that means for temporal primary > keys. Right. My message was specifically a response to the concern that there was some kind of design flaw in the range types or exclusion constraints mechanisms. I don't believe that empty ranges represent a design flaw. If they don't make sense for temporal constraints, then temporal constraints should forbid them. Regards, Jeff Davis ^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2024-05-21 19:54 UTC | newest] Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-04-07 14:04 [PATCH 11/19] doc review: basebackup_to_shell.required_role Justin Pryzby <[email protected]> 2024-04-03 05:30 Re: SQL:2011 application time Paul Jungwirth <[email protected]> 2024-04-15 00:00 ` Re: SQL:2011 application time jian he <[email protected]> 2024-04-26 19:25 ` Re: SQL:2011 application time Robert Haas <[email protected]> 2024-04-26 19:41 ` Re: SQL:2011 application time Paul Jungwirth <[email protected]> 2024-04-30 16:24 ` Re: SQL:2011 application time Robert Haas <[email protected]> 2024-04-30 16:39 ` Re: SQL:2011 application time Paul Jungwirth <[email protected]> 2024-05-01 11:27 ` Re: SQL:2011 application time jian he <[email protected]> 2024-05-06 03:01 ` Re: SQL:2011 application time jian he <[email protected]> 2024-05-12 00:00 ` Re: SQL:2011 application time jian he <[email protected]> 2024-05-12 03:25 ` Re: SQL:2011 application time Paul Jungwirth <[email protected]> 2024-05-13 00:51 ` Re: SQL:2011 application time Paul Jungwirth <[email protected]> 2024-05-08 13:51 ` Re: SQL:2011 application time Peter Eisentraut <[email protected]> 2024-05-13 10:11 ` Re: SQL:2011 application time Peter Eisentraut <[email protected]> 2024-05-16 23:22 ` Re: SQL:2011 application time Jeff Davis <[email protected]> 2024-05-21 17:57 ` Re: SQL:2011 application time Robert Haas <[email protected]> 2024-05-21 19:54 ` Re: SQL:2011 application time Jeff Davis <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox