Thread: generate_series from now to infinity...

Hi all

Is a simple "SELECT generate_series(now(), CAST('infinity'::date AS
timestamp), interval '1 hour');" working forever, an expected
behavior?

regards...
-- 
Dickson S. Guedes
-
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

"Dickson S. Guedes" <listas@guedesoft.net> writes:
> Is a simple "SELECT generate_series(now(), CAST('infinity'::date AS
> timestamp), interval '1 hour');" working forever, an expected
> behavior?

Uh, what were you expecting it to do?

Actually, I believe it will fail eventually when the repeated additions
overflow ... in 294277 AD.  So you've got about 2 billion timestamp
additions to wait through.
        regards, tom lane

On Sun, May 17, 2009 at 1:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Dickson S. Guedes" <listas@guedesoft.net> writes:
>> Is a simple "SELECT generate_series(now(), CAST('infinity'::date AS
>> timestamp), interval '1 hour');" working forever, an expected
>> behavior?
>
> Uh, what were you expecting it to do?

It appears that any generate_series involving infinity is guaranteed to fail.

That being the case, wouldn't it be more useful to throw an error than
to just keep on running until overflow?

Cheers,
BJ

On May 16, 2009, at 11:58 PM, Brendan Jurd <direvus@gmail.com> wrote:

> On Sun, May 17, 2009 at 1:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Dickson S. Guedes" <listas@guedesoft.net> writes:
>>> Is a simple "SELECT generate_series(now(), CAST('infinity'::date AS
>>> timestamp), interval '1 hour');" working forever, an expected
>>> behavior?
>>
>> Uh, what were you expecting it to do?
>
> It appears that any generate_series involving infinity is guaranteed  
> to fail.
>
> That being the case, wouldn't it be more useful to throw an error than
> to just keep on running until overflow?

What if it were combined with LIMIT?

...Robert

Em Sáb, 2009-05-16 às 23:40 -0400, Tom Lane escreveu:
> "Dickson S. Guedes" <listas@guedesoft.net> writes:
> > Is a simple "SELECT generate_series(now(), CAST('infinity'::date AS
> > timestamp), interval '1 hour');" working forever, an expected
> > behavior?
>
> Uh, what were you expecting it to do?

Perhaps, a HINT?

> Actually, I believe it will fail eventually when the repeated additions
> overflow ... in 294277 AD.  So you've got about 2 billion timestamp
> additions to wait through.

A customer are porting his application to 8.4, and are using a query
like that. Someone unintentionally included a "infinity" date and that
query have been running until they see the test server memory at 99% and
cpu at 100%.

I suggested him to use LIMIT.

[]s
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

On Sun, May 17, 2009 at 01:38:35PM -0300, Dickson S. Guedes wrote:
> I suggested him to use LIMIT.

interesting. I just tested (on beta1), that while limit works in this
query:
SELECT generate_series(now(), CAST('infinity'::date AS timestamp), interval '1 hour') limit 3;

i.e. it returns 3 rows instantly, 
it doesn't for this query:

SELECT i from generate_series(now(), CAST('infinity'::date AS timestamp), interval '1 hour') as x (i) limit 3;

which (as far as i understand it) should be the same.

why is it not limiting generate_series in the second example? is it
intentional?

Best regards,

depesz

-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Em Dom, 2009-05-17 às 19:22 +0200, hubert depesz lubaczewski escreveu:
> it doesn't for this query:
>
> SELECT i from generate_series(now(), CAST('infinity'::date AS
> timestamp), interval '1 hour') as x (i) limit 3;
>
> which (as far as i understand it) should be the same.
>
> why is it not limiting generate_series in the second example? is it
> intentional?

The EXPLAIN output differ between both.

postgres=# EXPLAIN SELECT generate_series(now(), CAST('infinity'::date
AS timestamp), interval '1 hour') limit 3;                  QUERY PLAN
------------------------------------------------Limit  (cost=0.00..0.02 rows=1 width=0)  ->  Result  (cost=0.00..0.02
rows=1width=0) 


postgres=# explain SELECT i from generate_series(now(),
CAST('infinity'::date AS timestamp), interval '1 hour') as x (i) limit
3;                                  QUERY PLAN
--------------------------------------------------------------------------------Limit  (cost=0.00..0.05 rows=3 width=8)
->  Function Scan on generate_series x  (cost=0.00..17.50 rows=1000 
width=8)

hubert depesz lubaczewski <depesz@depesz.com> writes:
> why is it not limiting generate_series in the second example?

nodeFunctionscan.c sucks the whole SRF output into a tuplestore before
returning any of it.  In principle you could do something different for
a value-per-call SRF, but it would require a second whole code path
(or else major refactoring of ExecMakeTableFunctionResult and related
code).  The performance implications are unclear too.

I seem to recall there was some discussion of this point when that code
was first written, but nobody bothered to do anything about it.
        regards, tom lane