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
```