On 2006-04-11, Tom Lane <[EMAIL PROTECTED]> wrote: > More realistically, though, the theoretical point that you can do > arbitrary calculations by turning loops into recursive SQL functions is > mostly just theoretical,
It's not at all theoretical. The very practical problem of trying to write code that does useful stuff (like generate_series on 7.4 or parsing the values in pg_trigger.tgargs) without using pl/pgsql is a wonderful demonstration of just how much you can really do in plain SQL functions by using appropriate techniques. Sure, it requires some specialised approaches, but then so does system cracking ... > and the reason is that you won't be able to > loop very many times before running out of stack space. (On my machine > it looks like you can recurse a trivial SQL function only about 600 > times before hitting the default stack limit.) 600 times is enough for the function to do more computation than could ever be done in the lifetime of the universe. (Consider: how long would it take to do the Towers of Hanoi with 600 disks?) > If you have an exploit > that involves moderate amounts of calculation within the server --- say, > brute force password cracking --- the availability of a PL will render > that exploit actually practical, whereas with only SQL functions to work > with it won't be. Tom, when you're engaged in a debate on a topic, it's polite to actually _read_ what other people are posting. I've already posted a very straightforward example of code that will happily loop over 300 million values using a recursion depth of no greater than 7, and I specifically chose it because it shows how easily large brute-force searches can be done in plain SQL. The existence of cross joins means that arbitrarily large loops can be constructed without needing either deep recursion or large materialized function result sets. In many cases these methods give you code which is both simpler and faster than the equivalent in pl/pgsql (why code naive nested loops in pl/pgsql, for example, when the executor already has that functionality built in?). Here's your brute-force password crack (try it! should only take an hour or two) using the simple alpha(n) function example from my other post: select a||b||c from alpha(3) s1(a), alpha(3) s2(b), alpha(2) s3(c) where md5(a||b||c||'andrew') = 'ff113aee991f0a3519c3d4f97414561a' limit 1; -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
