Обсуждение: recursive SQL functions

Is there any reason why recursive SQL functions are not allowed in PG 7.2?

After all this:

create function foo() returns setof integer as 'select 1'
language 'sql';

create or replace function foo() returns setof integer as
'select foo()'
language 'sql';

Works fine ... (until you call it and run out of stack space!)

It turns out that with the aid of a very simple and efficient recursive 
SQL function it is quite easy to devise a key structure for trees that 
scales very, very well.  Probably better than using hierarchical 
("connect by") queries with an appropriate parent foreign key in Oracle, 
though I haven't done any serious benchmarking yet.

This is important for the OpenACS project which uses a filesystem 
paradigm to organize content in many of its packages.

One of our volunteer hackers figured out an ugly kludge that lets us 
define a recursive SQL function in PG 7.1 and it works great, leading to 
extremely efficient queries that work on the parents of a given node.

We were thinking we could just declare the function directly in PG 7.2 
but instead found we have to resort to a kludge similar to the example 
above in order to do it.  It's a far nicer kludge than our PG 7.1 hack, 
believe me, but we were hoping for a clean define of a recursive function.

SQL functions can return rowsets but recursive ones can't be defined 
directly.

Recursive PL/pgSQL functions can be defined directly but they can't 
return rowsets.

Sniff...sniff...sniff [:)]

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org

Tom Lane wrote:


> Aside from the forward-reference problem, which seems easy enough to
> solve, I think there may be some performance issues that'd have to be
> dealt with (memory leaks and so forth).

OK, that's reasonable.  If potential memory leaks are only for the 
duration of a transaction I'm not worried (in regard to the one 
recursive function we're using) as it's cachable and our trees not very 
deep.

I have found one bug that crashed the backend if a recursive function's 
called.  Are you interested in it, since folks can now define them 
semi-officially (if a bit unconventially) via CREATE AND REPLACE?

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org

Don Baccus <dhogaza@pacifier.com> writes:
> I have found one bug that crashed the backend if a recursive function's 
> called.  Are you interested in it,

You bet.  Crashes are always interesting ...
        regards, tom lane

Don Baccus <dhogaza@pacifier.com> writes:
> Is there any reason why recursive SQL functions are not allowed in PG 7.2?

It's been discussed, cf
http://fts.postgresql.org/db/mw/msg.html?mid=1038929
but no one got around to it for 7.2.

Aside from the forward-reference problem, which seems easy enough to
solve, I think there may be some performance issues that'd have to be
dealt with (memory leaks and so forth).
        regards, tom lane

Tom Lane wrote:

> Don Baccus <dhogaza@pacifier.com> writes:
> 
>>I have found one bug that crashed the backend if a recursive function's 
>>called.  Are you interested in it,
>>
> 
> You bet.  Crashes are always interesting ...


Darn ... dies in PG 7.1 works in PG 7.2 :)


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org

Tom Lane writes:

> Don Baccus <dhogaza@pacifier.com> writes:
> > Is there any reason why recursive SQL functions are not allowed in PG 7.2?
>
> It's been discussed, cf
> http://fts.postgresql.org/db/mw/msg.html?mid=1038929
> but no one got around to it for 7.2.
>
> Aside from the forward-reference problem, which seems easy enough to
> solve, I think there may be some performance issues that'd have to be
> dealt with (memory leaks and so forth).

I've prepared a patch for recursive SQL functions.  I assume these memory
leaks "and so forth" that you speak of are just issues of quality, not
something that should prevent the use of recursive functions altogether.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> I've prepared a patch for recursive SQL functions.  I assume these memory
> leaks "and so forth" that you speak of are just issues of quality, not
> something that should prevent the use of recursive functions altogether.

I suspect it would not work to re-use a query plan tree at multiple
recursion levels (someday, plan trees should be read-only during
execution, but they ain't now).  As long as you are making a new
plan tree for each recursive entry, it should work ...
        regards, tom lane

Tom Lane writes:

> I suspect it would not work to re-use a query plan tree at multiple
> recursion levels (someday, plan trees should be read-only during
> execution, but they ain't now).  As long as you are making a new
> plan tree for each recursive entry, it should work ...

ISTM that this is already what's happening.  Each level gets a new plan
tree.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> ISTM that this is already what's happening.  Each level gets a new plan
> tree.

Fine then.  I wasn't sure if that'd fall out of the existing behavior
or not...
        regards, tom lane