Re: Followup Timestamp to timestamp with TZ conversion

On Fri, Jul 23, 2021 at 6:18 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> For btree indexes, you need a compatible notion of ordering, not only
> equality. That's really what's breaking my hypothetical case of a uint
> type. But as long as you implement operators that behave in a consistent
> fashion, whether they interpret the same heap bitpattern the same is not
> something that matters for constructing a consistent operator family.
> datetime_ops (which includes timestamp and timestamptz) is already a
> counterexample, since unless the timezone is UTC, its operators *don't*
> all agree on what a particular bitpattern means.

Well, that depends a bit on what "means" means. I would argue that the
meaning does not depend on the timezone setting, and that the timezone
merely controls the way that values are printed. That is, I would say
that the meaning is the point in time which the timestamp represents,
considered as an abstract concept, and timezone is merely the user's
way of asking that point in time to be expressed in a way that's easy
for them to understand. Regardless of that philosophical point, I
think it must be true that if a and b are timestamps, a < b implies
a::timestamptz < b::timestamptz, and a > b implies a::timestamptz >
b::timestamptz, and the other way around, which is surely good enough,
but wasn't the case in your example. So the question I suppose is
whether in your example it's really legitimate to include your uint
type in the integer_ops opfamily. I said above that I didn't think so,
but I'm less sure now, because I realize that I read what you wrote
carefully enough, and it's not as artificial as I first thought.

> >> ... I'm also a bit confused about how it ever succeeds at all.
>
> > Well, you can change just the typemod, for example, which was a case
> > that motivated this work originally.
>
> Ah, right. I guess binary-compatible cases such as text and varchar
> would also fit into that.

Oh, right. That was another one of the cases that motivated that work.

--
Robert Haas
EDB: http://www.enterprisedb.com