Horton

Thread

Commits

  1. Detect Julian-date overflow in timestamp[tz]_pl_interval.

  1. BUG #18313: No error triggered when subtracting an interval from a timestamp

    The Post Office <noreply@postgresql.org> — 2024-01-26T13:06:03Z

    The following bug has been logged on the website:

Bug reference:      18313
Logged by:          Christian Maurer
Email address:      c.maurer@gmx.at
PostgreSQL version: 16.1
Operating system:   RHEL 9
Description:        

Hi

The SQL script below uses an anchor date (2000-01-01) in a table and tries
to subtract an interval (in days) from it.
Considering the timestamp range, the maximum number of days that can be
successfully subtracted should be 2451545. All higher values are then
expected to trigger an exception.

However, when subtracting 2483590, no error is triggered. This is true up to
2539738.
Subtracting 2539739 again shows an error message.

Is this the intended behavior?

Platform: We use PostgreSQL 16.1 (PGDG) under Red Hat Enterprise Linux 9
with standard settings.

Regards,
Christian

create table tbl_timestamp_limit (anchor_date TIMESTAMP(6), offset_value
BIGINT);
insert into tbl_timestamp_limit (anchor_date) values
(to_timestamp('20000101','YYYYMMDD'));

-- the maximum value in days we can subtract from '2000-01-01' without an
error is 2451545
update tbl_timestamp_limit set offset_value=2483589;
-- 2483589 fails as expected
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;

-- one day more
update tbl_timestamp_limit set offset_value=2483590;
-- 2483590 surprisingly succeeds, no error is triggered
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;

update tbl_timestamp_limit set offset_value=2539738;
-- 2539738 still does not trigger an error
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;

update tbl_timestamp_limit set offset_value=2539739;
-- 2539739 fails as expected
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;

  2. Re: BUG #18313: No error triggered when subtracting an interval from a timestamp

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-26T17:32:02Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> The SQL script below uses an anchor date (2000-01-01) in a table and tries
> to subtract an interval (in days) from it.
> Considering the timestamp range, the maximum number of days that can be
> successfully subtracted should be 2451545. All higher values are then
> expected to trigger an exception.

> However, when subtracting 2483590, no error is triggered. This is true up to
> 2539738.
> Subtracting 2539739 again shows an error message.

> Is this the intended behavior?

Nope.  Looks like we need to check for an out-of-range Julian date
when subtracting intervals from timestamps, more or less as attached.

			regards, tom lane