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