Re: [PATCH] Support empty ranges with bounds information

On Tue, Mar 2, 2021, at 21:58, Mark Dilger wrote:
> Part of what was strange about arguing against your patch is that I kind of wanted the feature to work that way back when it originally got written. (Not to say that it *should* have worked that way, just that part of me wanted it.)

That's encouraging to hear. I've marked the patch as Rejected, since it was a dead end.

I would accept things as they are, if there was nothing that could be done,
but since Multiranges have not been released yet,
I think it's worth thinking intensively about possible problems until it's too late.

For discrete types, Multiranges <=> Sets should be true,
i.e. they should be equivalent, since there cannot be any values
in between two discrete adjacent values.

Due to the internals of ranges, it's not possible to create a range
that covers all possible valid values for a discrete type,
since the very last value cannot be included.

Example for int4:

SELECT int4range(-2147483647,2147483647,'[]');
ERROR: integer out of range

SELECT int4range(0,2147483647,'[]');
ERROR: integer out of range

SELECT int4range(-2147483647,0,'[]');
int4range
-----------------
[-2147483647,1)
(1 row)

However, 2147483647 is a valid int4 value.

This is due to the unfortunate decision to use [) as the canonical form,
since it must then always be able to calculate the next adjacent value.

If instead [] would have been used as the canonical form,
we would not have this problem.

Not a biggie for int4 maybe, but imagine a very small discrete type,
where it's actually necessary to create a range including its very last value.

Suggestion #1: Use [] as the canonical form for discrete types.
This would allow creating ranges for all values for discrete types.

/Joel