Re: plpgsql by default

On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> 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'limit1;
 

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services