Re: Re: Should we have an optional limit on the recursion depth of recursive CTEs?

On Tue, Aug 16, 2011 at 7:23 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> There is no LIMIT imposed on a query by a server setting, which would be the
> right analogy here.

I am not sure I understand any of these analogies. I think Peter's
point is that it's not very difficult to write (perhaps accidentally)
a CTE that goes into infinite recursion. In general, we can't detect
that situation, because it's equivalent to the halting problem. But
there's an old joke about a Turing test (where a computer program must
try to fool a human into believing that it is also human) where the
person asks the computer:

What would the following program do?
10 PRINT "HELLO"
20 GOTO 10

And gets back an infinite stream of HELLO HELLO HELLO HELLO HELLO....

I don't think it's going to be feasible to implement a security
restriction that keeps untrusted users from hosing the machine with a
long running CTE; there are nearly infinitely many ways for an
untrusted user who can run queries to hose the machine, and plugging
one of them imperfectly is going to get us pretty much nowhere. On
the other hand, there is perhaps a reasonable argument to be made that
we should cut off CTE processing at some point to prevent
*inadvertent* exhaustion of system resources. Or even query
processing more generally.

In fact, we already have some things sort of like this: you can use
statement_timeout to kill queries that run for too long, and we just
recently added temp_file_limit to kill those that eat too much temp
file space. I can see a good case for memory_limit and
query_cpu_limit and maybe some others. cte_recursion_depth_limit
wouldn't be all that high on my personal list, I guess, but the
concept doesn't seem completely insane.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company