Re: SQL:2011 application time

On Wed, Apr 3, 2024 at 1:30 PM Paul Jungwirth
<pj(at)illuminatedcomputing(dot)com> 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;