alt Hacker NewsI will add more example links in the top-level comment, but here is the thread for that bug. [0]. tl;dr timestamp vs timestamptz comparisons aren't consistently ordered in a pathological edge case, which means that index scans can return the wrong answer. There's no way to fix it without either redefining the operators in a surprising way or disabling index scans in this case.
[0] https://www.postgresql.org/message-id/flat/c22286ad6268347c6...
Replies
paulryanrogers • today at 2:44 AM
OT: Crazy that TZ-less are default in so many RDBMS. TZ included should be the default, and no-TZ opt in like TIMESTAMP NO TIME ZONE.
➕ show 1 reply
> or disabling index scans in this case
This seems rather extreme. The pathological case only seems to make a given timestamp ~1h bigger, so you could still use an index scan, just with a bigger input followed by a filter that removes the timestamps that shouldn't be in the query.