pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16657: Index not reflecting update when date to timestamp comparison operation used in index scan
2+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2020-10-07 3db322eaab 5ed20a689e Prevent internal overflows in date-vs-timestamp and related comparisons.
* BUG #16657: Index not reflecting update when date to timestamp comparison operation used in index scan
@ 2020-10-06 22:22 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2020-10-06 22:22 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: huss@9fin.com
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
```
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #16657: Index not reflecting update when date to timestamp comparison operation used in index scan
@ 2020-10-07 01:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2020-10-07 01:31 UTC (permalink / raw)
To: huss@9fin.com; +Cc: pgsql-bugs@lists.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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-10-07 01:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-06 22:22 BUG #16657: Index not reflecting update when date to timestamp comparison operation used in index scan PG Bug reporting form <noreply@postgresql.org>
2020-10-07 01:31 ` Tom Lane <tgl@sss.pgh.pa.us>
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