This summer, with the help of AI, I found an inconsistency in the way Postgres handles timestamp vs. timestamptz comparisons under a DST spring-forward gap for the datetime_ops btree family [0]. Essentially there are scenarios where expression B > A and B < C, but also C = A, which can cause queries using a btree index (among other things) to return an incorrect result.
The assessment in the mailing list was that this was a bug, but there were no good ways to fix it.
> Backpatching a behavioral change like this seems awfully scary.
For the moment I'm just contemplating what we could potentially
change in master. So far I don't like any of the choices :-(
Does the same hole exist for range types? If a GiST exclusion constraint on tstzrange is built on the same comparison logic, a booking table could accept two reservations that overlap only inside the DST gap, and nobody would notice until two people show up for the same room. Curious whether you checked that path or only the btree family.
tibbar · · focus · HN ↗
The assessment in the mailing list was that this was a bug, but there were no good ways to fix it.
> Backpatching a behavioral change like this seems awfully scary. For the moment I'm just contemplating what we could potentially change in master. So far I don't like any of the choices :-(
[0] <a href="https://www.postgresql.org/message-id/flat/CA%2BCOZaDmCuOds-MnMoDZwMVjxxby%3DW4W7_S0mxqS2ZNOYq0iJA%40mail.gmail.com" rel="nofollow">https://www.postgresql.org/message-id/flat/CA%2BCOZaDmCuOds-...
spacesez-ai · · focus · HN ↗