Re: timestamp vs. timestamptz comparisons inconsistently ordered under DST

On Mon, 2026-07-27 at 08:23 -0400, Tom Lane wrote:
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> > On Sun, 2026-07-26 at 18:39 -0700, Jacob Brazeal wrote:
> > > This seems maybe annoying to fix, but the alternative could be adding some documentation about it?
>
> > It is quite unacceptable to allow a bad query result, so documenting
> > this is not good enough in my opinion.
>
> It's not really the index's fault IMO: the locus of the problem is
> inconsistent promotions of timestamp to timestamptz.

You are right.

> Our general rule for timestamp-vs-timestamptz comparisons is
> "promote the timestamp side to timestamptz, then compare".
> So we get the effect Jacob describes that an index that is
> validly ordered per timestamp rules appears out-of-order
> when it's being compared to a timestamptz query value.
>
> I thought for a bit about changing the support function
> timestamp_cmp_timestamptz so that it instead converts the
> timestamptz side down to timestamp. That would make an index
> search work consistently ... but it would be inconsistent with
> what the SQL-accessible operators do, so I think it's a nonstarter.

What if we change the definition of the SQL operators accordingly?
That should only affect very few actual values. We can do that
in master, with a note in the compatibility section, right?

> I think the only way we could really resolve this is to change
> the promotion rule to be monotonic, that is "all timestamp values
> between 02:00 and 03:00 convert to 03:00". But that probably
> breaks other things, and in abstract terms it doesn't seem
> better than what we do now (which we define as "assume that
> times in the gap are meant to be standard time").

That doesn't look attractive at all.

> Or, as you say, we could stop treating timestamp-vs-timestamptz as an
> index-scannable query. But the people that that would make unhappy
> (because their query suddenly takes forever) surely vastly outnumber
> the people whom it'd make happy (approximately no one, given the lack
> of prior complaints). It's not an issue unless you store timestamp
> values that don't actually correspond to any local clock reading.

I agree with your assessment of the happiness:unhappiness ratio.

But wouldn't that also mean that very few people are going to be
unhappy if we backpatch a change in the behavior of the SQL
operators?

Yours,
Laurenz Albe