On 7 February 2011 09:04, Itagaki Takahiro <itagaki.takahiro@xxxxxxxxx> wrote:
> On Fri, Feb 4, 2011 at 21:32, Thom Brown <thom@xxxxxxxxx> 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
Attachment:
generate_series_fix.v4.patch
Description: Binary data
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
