On Mon, 2026-07-27 at 08:23 -0400, Tom Lane wrote: > Laurenz Albe <[email protected]> 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
