Re: Infinite Interval

On Sun, Jan 8, 2023 at 11:17 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
>
>
> On Sun, Jan 8, 2023 at 4:22 AM Joseph Koshakow <koshy44(at)gmail(dot)com> wrote:
>>
>> On Sat, Jan 7, 2023 at 3:05 PM Joseph Koshakow <koshy44(at)gmail(dot)com> wrote:
>> >
>> > On Sat, Jan 7, 2023 at 3:04 PM Joseph Koshakow <koshy44(at)gmail(dot)com> wrote:
>> > >
>> > > I think this patch is just about ready for review, except for the
>> > > following two questions:
>> > > 1. Should finite checks on intervals only look at months or all three
>> > > fields?
>> > > 2. Should we make the error messages for adding/subtracting infinite
>> > > values more generic or leave them as is?
>> > >
>> > > My opinions are
>> > > 1. We should only look at months.
>> > > 2. We should make the errors more generic.
>> > >
>> > > Anyone else have any thoughts?
>>
>> Here's a patch with the more generic error messages.
>>
>> - Joe
>
>
> HI.
>
> I just found out another problem.
>
> select * from generate_series(timestamp'-infinity', timestamp 'infinity', interval 'infinity');
> ERROR: timestamp out of range
>
> select * from generate_series(timestamp'-infinity',timestamp 'infinity', interval '-infinity'); --return following
>
> generate_series
> -----------------
> (0 rows)
>
>
> select * from generate_series(timestamp 'infinity',timestamp 'infinity', interval 'infinity');
> --will run all the time.
>
> select * from generate_series(timestamp 'infinity',timestamp 'infinity', interval '-infinity');
> ERROR: timestamp out of range
>
> select * from generate_series(timestamp'-infinity',timestamp'-infinity', interval 'infinity');
> ERROR: timestamp out of range
>
> select * from generate_series(timestamp'-infinity',timestamp'-infinity', interval '-infinity');
> --will run all the time.

Good catch, I didn't think to check non date/time functions.
Unfortunately, I think you may have opened Pandoras box. I went through
pg_proc.dat and found the following functions that all involve
intervals. We should probably investigate all of them and make sure
that they handle infinite intervals properly.

{ oid => '1026', descr => 'adjust timestamp to new time zone',
proname => 'timezone', prorettype => 'timestamp',
proargtypes => 'interval timestamptz', prosrc => 'timestamptz_izone' },

{ oid => '4133', descr => 'window RANGE support',
proname => 'in_range', prorettype => 'bool',
proargtypes => 'date date interval bool bool',
prosrc => 'in_range_date_interval' },

{ oid => '1305', descr => 'intervals overlap?',
proname => 'overlaps', prolang => 'sql', proisstrict => 'f',
provolatile => 's', prorettype => 'bool',
proargtypes => 'timestamptz interval timestamptz interval',
prosrc => 'see system_functions.sql' },

{ oid => '1305', descr => 'intervals overlap?',
proname => 'overlaps', prolang => 'sql', proisstrict => 'f',
provolatile => 's', prorettype => 'bool',
proargtypes => 'timestamptz interval timestamptz interval',
prosrc => 'see system_functions.sql' },
{ oid => '1306', descr => 'intervals overlap?',
proname => 'overlaps', prolang => 'sql', proisstrict => 'f',
provolatile => 's', prorettype => 'bool',
proargtypes => 'timestamptz timestamptz timestamptz interval',
prosrc => 'see system_functions.sql' },
{ oid => '1307', descr => 'intervals overlap?',
proname => 'overlaps', prolang => 'sql', proisstrict => 'f',
provolatile => 's', prorettype => 'bool',
proargtypes => 'timestamptz interval timestamptz timestamptz',
prosrc => 'see system_functions.sql' },

{ oid => '1308', descr => 'intervals overlap?',
proname => 'overlaps', proisstrict => 'f', prorettype => 'bool',
proargtypes => 'time time time time', prosrc => 'overlaps_time' },
{ oid => '1309', descr => 'intervals overlap?',
proname => 'overlaps', prolang => 'sql', proisstrict => 'f',
prorettype => 'bool', proargtypes => 'time interval time interval',
prosrc => 'see system_functions.sql' },
{ oid => '1310', descr => 'intervals overlap?',
proname => 'overlaps', prolang => 'sql', proisstrict => 'f',
prorettype => 'bool', proargtypes => 'time time time interval',
prosrc => 'see system_functions.sql' },
{ oid => '1311', descr => 'intervals overlap?',
proname => 'overlaps', prolang => 'sql', proisstrict => 'f',
prorettype => 'bool', proargtypes => 'time interval time time',
prosrc => 'see system_functions.sql' },

{ oid => '1386',
descr => 'date difference from today preserving months and years',
proname => 'age', prolang => 'sql', provolatile => 's',
prorettype => 'interval', proargtypes => 'timestamptz',
prosrc => 'see system_functions.sql' },

{ oid => '2042', descr => 'intervals overlap?',
proname => 'overlaps', prolang => 'sql', proisstrict => 'f',
prorettype => 'bool', proargtypes => 'timestamp interval timestamp interval',
prosrc => 'see system_functions.sql' },
{ oid => '2043', descr => 'intervals overlap?',
proname => 'overlaps', prolang => 'sql', proisstrict => 'f',
prorettype => 'bool', proargtypes => 'timestamp timestamp timestamp interval',
prosrc => 'see system_functions.sql' },
{ oid => '2044', descr => 'intervals overlap?',
proname => 'overlaps', prolang => 'sql', proisstrict => 'f',
prorettype => 'bool', proargtypes => 'timestamp interval timestamp timestamp',
prosrc => 'see system_functions.sql' },

{ oid => '4134', descr => 'window RANGE support',
proname => 'in_range', prorettype => 'bool',
proargtypes => 'timestamp timestamp interval bool bool',
prosrc => 'in_range_timestamp_interval' },
{ oid => '4135', descr => 'window RANGE support',
proname => 'in_range', provolatile => 's', prorettype => 'bool',
proargtypes => 'timestamptz timestamptz interval bool bool',
prosrc => 'in_range_timestamptz_interval' },
{ oid => '4136', descr => 'window RANGE support',
proname => 'in_range', prorettype => 'bool',
proargtypes => 'interval interval interval bool bool',
prosrc => 'in_range_interval_interval' },
{ oid => '4137', descr => 'window RANGE support',
proname => 'in_range', prorettype => 'bool',
proargtypes => 'time time interval bool bool',
prosrc => 'in_range_time_interval' },
{ oid => '4138', descr => 'window RANGE support',
proname => 'in_range', prorettype => 'bool',
proargtypes => 'timetz timetz interval bool bool',
prosrc => 'in_range_timetz_interval' },

{ oid => '2058', descr => 'date difference preserving months and years',
proname => 'age', prorettype => 'interval',
proargtypes => 'timestamp timestamp', prosrc => 'timestamp_age' },
{ oid => '2059',
descr => 'date difference from today preserving months and years',
proname => 'age', prolang => 'sql', provolatile => 's',
prorettype => 'interval', proargtypes => 'timestamp',
prosrc => 'see system_functions.sql' },

{ oid => '2070', descr => 'adjust timestamp to new time zone',
proname => 'timezone', prorettype => 'timestamptz',
proargtypes => 'interval timestamp', prosrc => 'timestamp_izone' },

{ oid => '3935', descr => 'sleep for the specified interval',
proname => 'pg_sleep_for', prolang => 'sql', provolatile => 'v',
prorettype => 'void', proargtypes => 'interval',
prosrc => 'see system_functions.sql' },

{ oid => '2599', descr => 'get the available time zone abbreviations',
proname => 'pg_timezone_abbrevs', prorows => '1000', proretset => 't',
provolatile => 's', prorettype => 'record', proargtypes => '',
proallargtypes => '{text,interval,bool}', proargmodes => '{o,o,o}',
proargnames => '{abbrev,utc_offset,is_dst}',
prosrc => 'pg_timezone_abbrevs' },
{ oid => '2856', descr => 'get the available time zone names',
proname => 'pg_timezone_names', prorows => '1000', proretset => 't',
provolatile => 's', prorettype => 'record', proargtypes => '',
proallargtypes => '{text,text,interval,bool}', proargmodes => '{o,o,o,o}',
proargnames => '{name,abbrev,utc_offset,is_dst}',
prosrc => 'pg_timezone_names' },

{ oid => '939', descr => 'non-persistent series generator',
proname => 'generate_series', prorows => '1000', proretset => 't',
provolatile => 's', prorettype => 'timestamptz',
proargtypes => 'timestamptz timestamptz interval',
prosrc => 'generate_series_timestamptz' },

{ oid => '3976', descr => 'continuous distribution percentile',
proname => 'percentile_cont', prokind => 'a', proisstrict => 'f',
prorettype => 'interval', proargtypes => 'float8 interval',
prosrc => 'aggregate_dummy' },
{ oid => '3977', descr => 'aggregate final function',
proname => 'percentile_cont_interval_final', proisstrict => 'f',
prorettype => 'interval', proargtypes => 'internal float8',
prosrc => 'percentile_cont_interval_final' },

{ oid => '3982', descr => 'multiple continuous percentiles',
proname => 'percentile_cont', prokind => 'a', proisstrict => 'f',
prorettype => '_interval', proargtypes => '_float8 interval',
prosrc => 'aggregate_dummy' },
{ oid => '3983', descr => 'aggregate final function',
proname => 'percentile_cont_interval_multi_final', proisstrict => 'f',
prorettype => '_interval', proargtypes => 'internal _float8',
prosrc => 'percentile_cont_interval_multi_final' },

- Joe