Обсуждение: Query generates infinite loop

Поиск
Список
Период
Сортировка

Query generates infinite loop

От
Richard Wesley
Дата:
Hi All -

I was implementing the infinity time constants in DuckDB when I ran into an infinite loop. It seems that PG has the same problem for the same reason (adding an interval to an infinite timestamp produces the same timestamp, so the increment operation never goes anywhere.)  Here is the query:
  1. select COUNT(*) 
FROM generate_series('-infinity'::TIMESTAMP, 'epoch'::TIMESTAMP, INTERVAL '1 DAY');

This seems like a DoS great attack, so we are disallowing infinities as bounds for both table and scalar series generation. As an upper bound, it eventually gives an error, so it seems  there is not much utility anyway.


Met vriendelijke groet, best regards, mit freundlichen Grüßen,

Richard Wesley
Group-By Therapist
richard@duckdblabs.com





Re: Query generates infinite loop

От
Pavel Stehule
Дата:
Hi

st 20. 4. 2022 v 18:42 odesílatel Richard Wesley <richard@duckdblabs.com> napsal:
Hi All -

I was implementing the infinity time constants in DuckDB when I ran into an infinite loop. It seems that PG has the same problem for the same reason (adding an interval to an infinite timestamp produces the same timestamp, so the increment operation never goes anywhere.)  Here is the query:
  1. select COUNT(*) 
FROM generate_series('-infinity'::TIMESTAMP, 'epoch'::TIMESTAMP, INTERVAL '1 DAY');

This seems like a DoS great attack, so we are disallowing infinities as bounds for both table and scalar series generation. As an upper bound, it eventually gives an error, so it seems  there is not much utility anyway.

There are more ways to achieve the same effect. The protection is safe setting of temp_file_limit

2022-04-20 09:59:54) postgres=# set temp_file_limit to '1MB';
SET
(2022-04-20 18:51:48) postgres=# select COUNT(*)
FROM generate_series('-infinity'::TIMESTAMP, 'epoch'::TIMESTAMP, INTERVAL '1 DAY');
ERROR:  temporary file size exceeds temp_file_limit (1024kB)
(2022-04-20 18:51:50) postgres=#

Regards

Pavel




Met vriendelijke groet, best regards, mit freundlichen Grüßen,

Richard Wesley
Group-By Therapist
richard@duckdblabs.com





Re: Query generates infinite loop

От
Tom Lane
Дата:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> st 20. 4. 2022 v 18:42 odesílatel Richard Wesley <richard@duckdblabs.com>
> napsal:
>> select COUNT(*) FROM generate_series('-infinity'::TIMESTAMP, 'epoch'::TIMESTAMP, INTERVAL '1 DAY');
>>
>> This seems like a DoS great attack, so we are disallowing infinities as
>> bounds for both table and scalar series generation. As an upper bound, it
>> eventually gives an error, so it seems  there is not much utility anyway.

> There are more ways to achieve the same effect. The protection is safe
> setting of temp_file_limit

Well, there are any number of ways to DOS a database you can issue
arbitrary queries to.  For instance, cross joining a number of very
large tables.  So I'm not excited about that aspect of it.  Still,
it's true that infinities as generate_series endpoints are going
to work pretty oddly, so I agree with the idea of forbidding 'em.

Numeric has infinity as of late, so the numeric variant would
need to do this too.

I think we can allow infinity as the step, though.

            regards, tom lane

Re: Query generates infinite loop

От
Tom Lane
Дата:
I wrote:
> it's true that infinities as generate_series endpoints are going
> to work pretty oddly, so I agree with the idea of forbidding 'em.

> Numeric has infinity as of late, so the numeric variant would
> need to do this too.

Oh --- looks like numeric generate_series() already throws error for
this, so we should just make the timestamp variants do the same.

            regards, tom lane