Thread: 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