[HACKERS] Should we have an optional limit on the recursion depth of recursive CTEs?
Should we have an optional, disabled-by-default limit on the recursion/iteration depth of recursive CTEs to guard against stupid queries that loop ad infinitum? I've looked at other database systems that support WITH RECURSIVE queries, and this idea crops up there. For example, Firebird, the only other RDBMS that I cared to look at for reasons you can perhaps guess, has a hard limit of 1024 (though you could argue that that's a limitation of their implementation, and I'd agree). Maybe the proprietary databases like SQL server have similar, perhaps even optional/adjustable limits - I don't know because I didn't check. I'd suggest that an appropriate interface would be an int GUC with a GucContext of PGC_SUSET, so that DBAs can impose system-wide limits. A possible use of such a GUC is to zero in on the actual recursion depth of the rCTE with the greatest depth in a given query, by performing a git bisect style binary search, setting the GUC dynamically at each step. It's probably not worth having a proper interface to do that with, but I can imagine that being a useful trick in certain narrow situations. We could also add a similar GUC that can be separately set by unprivileged users, that independently limits the recursion depth per session. This could be used as a sort of assertion of the maximum recursion depth of a given query. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Should we have an optional limit on the recursion depth of recursive CTEs?
Peter Geoghegan pe...@2ndquadrant.com writes: Should we have an optional, disabled-by-default limit on the recursion/iteration depth of recursive CTEs to guard against stupid queries that loop ad infinitum? I think not ... I'd suggest that an appropriate interface would be an int GUC with a GucContext of PGC_SUSET, so that DBAs can impose system-wide limits. ... and that would be a seriously bad API. There are not SUSET restrictions on other resources such as work_mem. Why do we need one for this? By and large, this sounds like a solution looking for a problem. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Should we have an optional limit on the recursion depth of recursive CTEs?
On 15 August 2011 21:31, Tom Lane t...@sss.pgh.pa.us wrote: I'd suggest that an appropriate interface would be an int GUC with a GucContext of PGC_SUSET, so that DBAs can impose system-wide limits. ... and that would be a seriously bad API. There are not SUSET restrictions on other resources such as work_mem. Why do we need one for this? I think that there perhaps /should/ be optional SUSET restrictions on those resources, particularly work_mem (though I'd suggest a more sophisticated interface there) - I haven't argued for that because, respectfully, I already know that to do so would be pretty close to futile. I have argued for this because I think that an important distinction can be drawn that might convince those who'd reject the idea of poor man's admission control. The distinction is that the only way that we'll ever be able to guard against this sort of failure is with an approach that is essentially equivalent to my proposal - stop trying after some arbitrary number of some unit of work. I'm sure that you don't need me to tell you that it has already been proven that solving the halting problem is impossible. What you may not be aware of is the fact that a proof exists for PG rCTE's Turing completeness. Consequently, I think that solving the halting problem is the barrier to coming up with something fundamentally better. I don't think that your scepticism about the general need to have such protection is justified; I believe that there is plenty of anecdotal evidence out there that this is useful in larger commercial contexts, and I've already named some places where a person might look for such anecdotal evidence. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
