Re: [HACKERS] Issues with generate_series using integer boundaries

On 02/07/2011 06:38 AM, Thom Brown wrote:
> 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.
>

Isn't this all really a bug fix that should be backpatched, rather than
a commitfest item?

cheers

andrew