Обсуждение: BUG #16657: Index not reflecting update when date to timestamp comparison operation used in index scan

The following bug has been logged on the website:

Bug reference:      16657
Logged by:          Huss EL-Sheikh
Email address:      huss@9fin.com
PostgreSQL version: 13.0
Operating system:   Linux
Description:

Hello,

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. I think it's also
significant that this is happening with to the casting to ::timestamp in the
WHERE clause of the query, but the native type of the field is ::date. If
there is no ::timestamp casting, this issue does not happen.

I attach below a script to replicate the issue.

Regards,
Huss

```
begin;
drop table if exists public.replicate_timestamp_range_index;

create table public.replicate_timestamp_range_index (
id bigserial not null primary key,
date_field date not null
);
create index date_field_idx on public.replicate_timestamp_range_index
(date_field);

-- insert date which is within the allowable date range, but outside of the
timestamp range
insert into public.replicate_timestamp_range_index (date_field) values
('2202020-10-05');
-- update to "fix" this error
update public.replicate_timestamp_range_index set date_field = '2020-10-05'
where id = 1;

-- uncomment this block for the reindexing to take effect
-- appears as if committing the previous DML, and then committing the
reindex
-- is the only way for the index to "repair" itself
/*
commit;  -- commit previous
begin;  -- new tx
reindex index public.date_field_idx;
commit;  -- commit reindex
begin; -- new tx
*/

SET enable_seqscan = OFF; -- ensure the index is used
explain analyse
select *
FROM public.replicate_timestamp_range_index
where date_field = '2020-10-05'::timestamp;

drop table if exists public.replicate_timestamp_range_index;
commit;

-- tested against below version() outputs
-- PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
20140911 (Red Hat 4.8.3-9), 64-bit
-- PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
```

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