Re: SQL:2011 application time

On Tue, May 14, 2024 at 7:30 AM Paul Jungwirth
<pj@illuminatedcomputing.com> wrote:
>
> On 5/13/24 03:11, Peter Eisentraut wrote:
> > 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.
>
> I think reverting is a good idea. I'm not really happy with the CHECK constraint solution either.
> I'd be happy to have some more time to rework this for v18.
>
> A couple alternatives I'd like to explore:
>
> 1. Domain constraints instead of a CHECK constraint. I think this is probably worse, and I don't
> plan to spend much time on it, but I thought I'd mention it in case someone else thought otherwise.
>
> 2. A slightly different overlaps operator, say &&&, where 'empty' &&& 'empty' is true. But 'empty'
> with anything else could still be false (or not). That operator would prevent duplicates in an
> exclusion constraint. This also means we could support more types than just ranges & multiranges. I
> need to think about whether this combines badly with existing operators, but if not it has a lot of
> promise. If anything it might be *less* contradictory, because it fits better with 'empty' @>
> 'empty', which we say is true.
>
thanks for the idea, I roughly played around with it, seems doable.
but the timing seems not good, reverting is a good idea.


I also checked the commit. 6db4598fcb82a87a683c4572707e522504830a2b
+
+/*
+ * Returns the btree number for equals, otherwise invalid.
+ */
+Datum
+gist_stratnum_btree(PG_FUNCTION_ARGS)
+{
+ StrategyNumber strat = PG_GETARG_UINT16(0);
+
+ switch (strat)
+ {
+ case RTEqualStrategyNumber:
+ PG_RETURN_UINT16(BTEqualStrategyNumber);
+ case RTLessStrategyNumber:
+ PG_RETURN_UINT16(BTLessStrategyNumber);
+ case RTLessEqualStrategyNumber:
+ PG_RETURN_UINT16(BTLessEqualStrategyNumber);
+ case RTGreaterStrategyNumber:
+ PG_RETURN_UINT16(BTGreaterStrategyNumber);
+ case RTGreaterEqualStrategyNumber:
+ PG_RETURN_UINT16(BTGreaterEqualStrategyNumber);
+ default:
+ PG_RETURN_UINT16(InvalidStrategy);
+ }
+}
the comments seem not right?