From: Tom Lane <tgl@sss.pgh.pa.us>
To: huss@9fin.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16657: Index not reflecting update when date to timestamp
 comparison operation used in index scan
In-reply-to: <16657-cde2f876d8cc7971@postgresql.org>
References: <16657-cde2f876d8cc7971@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Tue, 06 Oct 2020 22:22:18 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2038994.1602034274.1@sss.pgh.pa.us>
Date: Tue, 06 Oct 2020 21:31:14 -0400
Message-ID: <2038995.1602034274@sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> I observed the following odd behaviour when debugging a problem where my
> application inserted a wildly large date into a table. On my end, the date
> should have been properly boundary checked in the application first, however
> when fixing the value with an update, subsequent queries against the table
> were returning  "SQL Error [22008]: ERROR: date out of range for timestamp".
> After some investigation, I think I have narrowed it down to some sort of
> inconsistency with the index used in the query. It is behaving as if the old
> invalid value is still in the table, which it is not.

This is entirely unsurprising so far as the index is concerned.  It will
retain the old entry until it's removed by VACUUM.  (Yes, REINDEX does
it too, but that's overkill.)  Queries that have occasion to visit
that part of the index will need to compare against the dead value,
since they don't (yet) know it's dead.

The real issue is that date_cmp_timestamp works by promoting the date
to timestamp before comparing, and it fails to cope with an out-of-range
date.  You don't need any index to demonstrate that:

# select '2202020-10-05'::date > '2020-10-05'::timestamp;
ERROR:  date out of range for timestamp

That seems fairly silly actually, especially since I observe that somebody
has added all the necessary infrastructure for such comparisons to not
fail ... it's just not being used by the mainline code path.  I'll fix
that in a few days if nobody beats me to it.  Although it looks like said
infrastructure is only about a year old, so unless we want to back-patch
it, it'll only be convenient to fix this in v13 and HEAD.

			regards, tom lane