public inbox for [email protected]  

help / color / mirror / Atom feed

From: Paul Jungwirth <[email protected]>
To: jian he <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 application time
Date: Sat, 11 May 2024 20:25:45 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJufxHMbSiKX9BTg4P_vHj3BUf4uGyfprNYPgz_jrQw6RTsxg@mail.gmail.com>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CACJufxHsYDfHK62FTHdLsgDenvJd_bEuxCiyH62Lacj7NvzX4w@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CA+TgmoaH-2fAttd24pA3p45TUHbM9hRU6ud=brhKsp9ENHXWDg@mail.gmail.com>
	<[email protected]>
	<CA+TgmobfDJuXJATNUco+g6AiUb3WS2Pi9FrJe9hKAHGw37RX-g@mail.gmail.com>
	<[email protected]>
	<CACJufxFQ4EkJDTZXzgC3boid7dnXaRD8gW1KcXi_H-JAg2FPMA@mail.gmail.com>
	<CACJufxHMbSiKX9BTg4P_vHj3BUf4uGyfprNYPgz_jrQw6RTsxg@mail.gmail.com>

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]

view thread (17+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: SQL:2011 application time
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox