Re: [HACKERS] Issues with generate_series using integer boundaries

On 7 February 2011 09:04, Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> wrote:
> On Fri, Feb 4, 2011 at 21:32, Thom Brown <thom(at)linux(dot)com> wrote:
>> The issue is that generate_series will not return if the series hits
>> either the upper or lower boundary during increment, or goes beyond
>> it.  The attached patch fixes this behaviour, but should probably be
>> done a better way.  The first 3 examples above will not return.
>
> There are same bug in int8 and timestamp[tz] versions.
> We also need fix for them.
> =# SELECT x FROM generate_series(9223372036854775807::int8,
> 9223372036854775807::int8) AS a(x);

Yes, of course, int8 functions are separate. I attach an updated
patch, although I still think there's a better way of doing this.

> =# SELECT x FROM generate_series('infinity'::timestamp, 'infinity', '1
> sec') AS a(x);
> =# SELECT x FROM generate_series('infinity'::timestamptz, 'infinity',
> '1 sec') AS a(x);

I'm not sure how this should be handled. Should there just be a check
for either kind of infinity and return an error if that's the case? I
didn't find anything wrong with using timestamp boundaries:

postgres=# SELECT x FROM generate_series('1 Jan 4713 BC
00:00:00'::timestamp, '1 Jan 4713 BC 00:00:05'::timestamp, '1 sec') AS
a(x);
x
------------------------
4713-01-01 00:00:00 BC
4713-01-01 00:00:01 BC
4713-01-01 00:00:02 BC
4713-01-01 00:00:03 BC
4713-01-01 00:00:04 BC
4713-01-01 00:00:05 BC
(6 rows)

Although whether this demonstrates a true timestamp boundary, I'm not sure.

>> postgres=# SELECT x FROM generate_series(1, 9,-1) AS a(x);
>> postgres=# SELECT x FROM generate_series(1, 9,3) AS a(x);
> They work as expected in 9.1dev.

Those 2 were to demonstrate that the changes don't affect existing
functionality. My previous patch proposal (v2) caused these to return
unexpected output.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935