From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PGSQL Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] Issues with generate_series using integer boundaries |
Date: | 2011-02-07 11:38:09 |
Message-ID: | AANLkTik8LS7VvGoXvC-g-6PyqRDm1TzfgrrzhvAhs4J1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
generate_series_fix.v4.patch | application/octet-stream | 3.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | rsmogura | 2011-02-07 11:44:36 | XML Encoding problem |
Previous Message | Itagaki Takahiro | 2011-02-07 09:04:15 | Re: [HACKERS] Issues with generate_series using integer boundaries |
From | Date | Subject | |
---|---|---|---|
Next Message | Itagaki Takahiro | 2011-02-07 12:00:53 | Re: SQL/MED - file_fdw |
Previous Message | strk | 2011-02-07 11:38:08 | DROP SCHEMA xxx CASCADE: ERROR: could not open relation with OID yyy |