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
Date: Tue, 06 Oct 2020 21:31:14 -0400
Message-ID: <2038995.1602034274@sss.pgh.pa.us> (raw)
In-Reply-To: <16657-cde2f876d8cc7971@postgresql.org>
References: <16657-cde2f876d8cc7971@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, huss@9fin.com, 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: <2038995.1602034274@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox