Re: SRF patch (was Re: [HACKERS] troubleshooting pointers)

With the current SRF patch, in certain circumstances selecting from a
VIEW produces "Buffer Leak" warnings, while selecting from the function
itself does not. Also the VIEW returns only one of the two expected
rows. The same SQL function when declared as "... getfoo(int) RETURNS
int AS ..." instead of "... getfoo(int) RETURNS *setof* int AS..." does
not produce the warning. Any ideas what I should be focusing on to track
this down? Does anyone have any favorite troubleshooting techniques for
this type of problem?

Thanks,
Joe

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo
WHERE fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
getfoo
--------
1
1
(2 rows)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
psql:../srf-test.sql:21: WARNING: Buffer Leak: [055] (freeNext=-3,
freePrev=-3, rel=16570/123204, blockNum=1, flags=0x4, refcount=1 1)
psql:../srf-test.sql:21: WARNING: Buffer Leak: [059] (freeNext=-3,
freePrev=-3, rel=16570/123199, blockNum=0, flags=0x85, refcount=1 1)
getfoo
--------
1
(1 row)

Hello, Joe!

JC> With the current SRF patch, in certain circumstances selecting from
JC> a
JC> VIEW produces "Buffer Leak" warnings, while selecting from the
JC> function itself does not. Also the VIEW returns only one of the two

Selecting from the function produces such a warning when using it with
limit,
but it does not when the function returns less rows than specified in limit
.
e.g.

just_fun=# create table testtab(i integer, v varchar);
CREATE
just_fun=# insert into testtab values(1,'one');
INSERT 16592 1
just_fun=# insert into testtab values(2,'two');
INSERT 16593 1
just_fun=# insert into testtab values(3,'three');
INSERT 16594 1
just_fun=# insert into testtab values(1,'one again');
INSERT 16595 1
just_fun=# create function fun(integer) returns setof testtab as 'select *
from testtab where i= $1;' language 'sql';
just_fun=# select * from fun(1) as fun;
i | v
---+-----------
1 | one
1 | one again
(2 rows)

just_fun=# select * from fun(1) as fun limit 1;
WARNING: Buffer Leak: [050] (freeNext=-3, freePrev=-3, rel=16570/16587,
blockNum=0, flags=0x85, refcount=1 2)
i | v
---+-----
1 | one
(1 row)

....And there is no warning with "ORDER BY"

just_fun=# select * from fun(1) as fun order by v limit 1;
i | v
---+-----
1 | one
(1 row)

Hope this info maybe useful to solve the problem.

By the way, could you give an example of C-function returning set?

JC> expected rows. The same SQL function when declared as "...
JC> getfoo(int) RETURNS int AS ..." instead of "... getfoo(int) RETURNS
JC> *setof* int AS..." does not produce the warning. Any ideas what I
JC> should be focusing on to track this down? Does anyone have any
JC> favorite troubleshooting techniques for this type of problem?

JC> Thanks,
JC> Joe

Thank you for your work in this direction!

With best regards, Valentine Zaretsky

Valentine Zaretsky wrote:
> just_fun=# select * from fun(1) as fun limit 1;
> WARNING: Buffer Leak: [050] (freeNext=-3, freePrev=-3, rel=16570/16587,
> blockNum=0, flags=0x85, refcount=1 2)
> i | v
> ---+-----
> 1 | one
> (1 row)
>
> ....And there is no warning with "ORDER BY"
>
> just_fun=# select * from fun(1) as fun order by v limit 1;
> i | v
> ---+-----
> 1 | one
> (1 row)
>
>
> Hope this info maybe useful to solve the problem.

Hmm. Yes, it looks like this is probably the same or a related issue.

>
> By the way, could you give an example of C-function returning set?
>

In contrib/dblink, see dblink.c for a couple of examples (dblink(),
dblink_get_pkey()), or look at pg_stat_get_backend_idset() in the
backend code. I haven't written a C-function returning a setof composite
type yet, but probably will soon, because I'll need it for testing (and
ultimately for the regression test script).

Thanks for the help!

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> With the current SRF patch, in certain circumstances selecting from a
> VIEW produces "Buffer Leak" warnings, while selecting from the function
> itself does not. Also the VIEW returns only one of the two expected
> rows.

The buffer leak suggests failure to shut down a plan tree (ie, no
ExecutorEnd call). Probably related to not running the VIEW to
completion, but it's hard to guess at the underlying cause.

Do the plan trees (EXPLAIN display) look the same in both cases?

regards, tom lane

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>
>>With the current SRF patch, in certain circumstances selecting from a
>>VIEW produces "Buffer Leak" warnings, while selecting from the function
>>itself does not. Also the VIEW returns only one of the two expected
>>rows.
>
> The buffer leak suggests failure to shut down a plan tree (ie, no
> ExecutorEnd call). Probably related to not running the VIEW to
> completion, but it's hard to guess at the underlying cause.
>
> Do the plan trees (EXPLAIN display) look the same in both cases?

Yes, but it suffers from the issue you brought up yesterday -- i.e.
EXPLAIN doesn't run from within the function, and EXPLAIN outside the
function (or VIEW which calls it) doesn't show very much:

test=# EXPLAIN SELECT * FROM vw_getfoo;
QUERY PLAN
-----------------------------------------------------------
Function Scan on getfoo (cost=0.00..0.00 rows=0 width=0)
(1 row)

test=# EXPLAIN SELECT * FROM getfoo(1);
QUERY PLAN
-----------------------------------------------------------
Function Scan on getfoo (cost=0.00..0.00 rows=0 width=0)
(1 row)

I found an explaination you gave a while back which sounds like it
explains the problem:
http://archives.postgresql.org/pgsql-bugs/2001-06/msg00051.php

I also confirmed that postquel_end(), which calls ExecutorEnd(), never
gets called for the VIEW case (or the LIMIT case that was pointed out on
an earlier post).

Just now I was looking for a way to propagate the necessary information
to call ExecutorEnd() from ExecEndFunctionScan() in the case that fmgr
doesn't. It looks like I might be able to add a member to the
ExprContext struct for this purpose. Does this sound like the correct
(or at least a reasonable) approach?

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Just now I was looking for a way to propagate the necessary information
> to call ExecutorEnd() from ExecEndFunctionScan() in the case that fmgr
> doesn't. It looks like I might be able to add a member to the
> ExprContext struct for this purpose. Does this sound like the correct
> (or at least a reasonable) approach?

Yeah, this is something that's bothered me in the past: with the
existing API, a function-returning-set will not get a chance to shut
down cleanly and release resources if its result is not read all the
way to completion. You can demonstrate the problem without any
use of the SRF patch. Using current CVS tip (no patch), and the
regression database:

regression=# create function foo(int) returns setof int as '
regression'# select unique1 from tenk1 where unique2 > $1'
regression-# language sql;

regression=# select foo(9990) limit 4;
WARNING: Buffer Leak: [009] (freeNext=-3, freePrev=-3, rel=16570/135224, blockNum=29, flags=0x4, refcount=1 1)
WARNING: Buffer Leak: [021] (freeNext=-3, freePrev=-3, rel=16570/18464, blockNum=232, flags=0x4, refcount=1 1)
foo
------
4093
6587
6093
429
(4 rows)

I don't much care for the thought of trawling every expression tree
looking for functions-returning-set during plan shutdown, so the thought
that comes to mind is to expect functions that want a shutdown callback
to register themselves somehow. Adding a list of callbacks to
ExprContext seems pretty reasonable, but you'd also need some link in
ReturnSetInfo to let the function find the ExprContext to register
itself with. Then FreeExprContext would call the callbacks.

Hmm ... another advantage of doing this is that the function would be
able to find the ecxt_per_query_memory associated with the ExprContext.
That would be a Good Thing.

We should also think about the fcache (FunctionCache) struct and whether
that needs to tie into this. See the FIXME in utils/fcache.h.

regards, tom lane

Tom Lane wrote:
> I don't much care for the thought of trawling every expression tree
> looking for functions-returning-set during plan shutdown, so the thought
> that comes to mind is to expect functions that want a shutdown callback
> to register themselves somehow. Adding a list of callbacks to
> ExprContext seems pretty reasonable, but you'd also need some link in
> ReturnSetInfo to let the function find the ExprContext to register
> itself with. Then FreeExprContext would call the callbacks.

I've made changes which fix this and will send them in with a revised
SRF patch later today. Summary of design:
1.) moved the execution_state struct and ExecStatus enum to executor.h
2.) added "void *es" member to ExprContext
3.) added econtext member to ReturnSetInfo
4.) set rsi->econtext on the way in at ExecMakeFunctionResult()
5.) set rsi->econtext->es on the way in at fmgr_sql()
6.) used econtext->es on the way out at ExecFreeExprContext() to call
ExecutorEnd() if needed (because postquel_execute() never got the chance).

One note: I changed ExecFreeExprContext() because that's where all the
action was for SQL function calls. FreeExprContext() was not involved
for the test case, but it looked like it probably should have the same
changes, so I made them there also.

>
> Hmm ... another advantage of doing this is that the function would be
> able to find the ecxt_per_query_memory associated with the ExprContext.
> That would be a Good Thing.

What does this allow done that can't be done today?

>
> We should also think about the fcache (FunctionCache) struct and whether
> that needs to tie into this. See the FIXME in utils/fcache.h.

While I was at it, I added an fcache member to ExprContext, and
populated it in ExecMakeFunctionResult() for SRF cases. I wasn't sure
what else to do with it at the moment, but at least it is a step in the
right direction.

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Tom Lane wrote:
>> Adding a list of callbacks to
>> ExprContext seems pretty reasonable, but you'd also need some link in
>> ReturnSetInfo to let the function find the ExprContext to register
>> itself with. Then FreeExprContext would call the callbacks.

> I've made changes which fix this and will send them in with a revised
> SRF patch later today. Summary of design:
> 1.) moved the execution_state struct and ExecStatus enum to executor.h
> 2.) added "void *es" member to ExprContext
> 3.) added econtext member to ReturnSetInfo
> 4.) set rsi->econtext on the way in at ExecMakeFunctionResult()
> 5.) set rsi->econtext->es on the way in at fmgr_sql()
> 6.) used econtext->es on the way out at ExecFreeExprContext() to call
> ExecutorEnd() if needed (because postquel_execute() never got the chance).

Um. I don't like that; it assumes not only that ExecutorEnd is the only
kind of callback needed, but also that there is at most one function
per ExprContext that needs a shutdown callback. Neither of these
assumptions hold water IMO.

The design I had in mind was more like this: add to ExprContext a list
header field pointing to a list of structs along the lines of

struct exprcontext_callback {
struct exprcontext_callback *next;
void (*function) (Datum);
Datum arg;
}

and then call each specified function with given argument during
FreeExprContext. Probably ought to be careful to do that in reverse
order of registration. We'd also need to invent a RescanExprContext
operation to call the callbacks during a Rescan. The use of Datum
(and not, say, void *) as PG's standard callback arg type was settled on
some time ago --- originally for on_proc_exit IIRC --- and seems to have
worked well enough.

>> Hmm ... another advantage of doing this is that the function would be
>> able to find the ecxt_per_query_memory associated with the ExprContext.
>> That would be a Good Thing.

> What does this allow done that can't be done today?

It provides a place for the function to allocate stuff that needs to
live over multiple calls, ie, until it gets its shutdown callback.
Right now a function has to use TransactionCommandContext for that,
but that's really too coarse-grained.

>> We should also think about the fcache (FunctionCache) struct and whether
>> that needs to tie into this. See the FIXME in utils/fcache.h.

> While I was at it, I added an fcache member to ExprContext, and
> populated it in ExecMakeFunctionResult() for SRF cases. I wasn't sure
> what else to do with it at the moment, but at least it is a step in the
> right direction.

Well, I was debating whether that's good or not. The existing fcache
approach is wrong (per cited FIXME); it might be better not to propagate
access of it into more places. Unless you can see a specific reason to
allow the function to have access to the fcache struct, I think I'm
inclined not to.

What's really more relevant here is that during the hypothetical new
RescanExprContext function, we ought to go around and clear any fcaches
in the context that have setArgsValid = true, so that they will be
restarted afresh during the next scan of the plan. (The fact that that
doesn't happen now is another shortcoming of the existing set-functions-
in-expressions code.) So this suggests making a callback function type
specifically to do that, and registering every fcache that is executing
a set function in the callback list...

regards, tom lane

Tom Lane wrote:
> Um. I don't like that; it assumes not only that ExecutorEnd is the only
> kind of callback needed, but also that there is at most one function
> per ExprContext that needs a shutdown callback. Neither of these
> assumptions hold water IMO.
>
> The design I had in mind was more like this: add to ExprContext a list
> header field pointing to a list of structs along the lines of
>
> struct exprcontext_callback {
> struct exprcontext_callback *next;
> void (*function) (Datum);
> Datum arg;
> }
>
> and then call each specified function with given argument during
> FreeExprContext. Probably ought to be careful to do that in reverse
> order of registration. We'd also need to invent a RescanExprContext
> operation to call the callbacks during a Rescan. The use of Datum
> (and not, say, void *) as PG's standard callback arg type was settled on
> some time ago --- originally for on_proc_exit IIRC --- and seems to have
> worked well enough.

Well, I guess I set my sights too low ;-) This is a very nice design.

I have the shutdown callback working now, and will send a new patch in a
few minutes. I have not started RescanExprContext() yet, but will do it
when I address rescans in general.

> What's really more relevant here is that during the hypothetical new
> RescanExprContext function, we ought to go around and clear any fcaches
> in the context that have setArgsValid = true, so that they will be
> restarted afresh during the next scan of the plan. (The fact that that
> doesn't happen now is another shortcoming of the existing set-functions-
> in-expressions code.) So this suggests making a callback function type
> specifically to do that, and registering every fcache that is executing
> a set function in the callback list...

I also added FunctionCachePtr_callback struct and a member to
ExprContext. I have not yet created the registration or shutdown
functions, but again, I'll work on them as part of the rescan work.

I still have a couple of issues related to VIEWs that I need to figure
out, then I'll start the rescan work.

Thanks for the review and help!

Joe

Tom Lane wrote:
> Um. I don't like that; it assumes not only that ExecutorEnd is the only
> kind of callback needed, but also that there is at most one function
> per ExprContext that needs a shutdown callback. Neither of these
> assumptions hold water IMO.
>
> The design I had in mind was more like this: add to ExprContext a list
> header field pointing to a list of structs along the lines of
>
> struct exprcontext_callback {
> struct exprcontext_callback *next;
> void (*function) (Datum);
> Datum arg;
> }
>
> and then call each specified function with given argument during
> FreeExprContext. Probably ought to be careful to do that in reverse
> order of registration. We'd also need to invent a RescanExprContext
> operation to call the callbacks during a Rescan. The use of Datum
> (and not, say, void *) as PG's standard callback arg type was settled on
> some time ago --- originally for on_proc_exit IIRC --- and seems to have
> worked well enough.

Here's the patch, per my post to HACKERS.

It builds cleanly on my dev box, and passes all regression tests.

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> ... I have not started RescanExprContext() yet, but will do it
> when I address rescans in general.

> I still have a couple of issues related to VIEWs that I need to figure
> out, then I'll start the rescan work.

It's not unlikely that those issues are exactly due to not having rescan
handled properly. What misbehavior are you seeing?

regards, tom lane

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>>... I have not started RescanExprContext() yet, but will do it
>>when I address rescans in general.
>
>>I still have a couple of issues related to VIEWs that I need to figure
>>out, then I'll start the rescan work.
>
> It's not unlikely that those issues are exactly due to not having rescan
> handled properly. What misbehavior are you seeing?

Hmm, that might just be it.

When I select from a view based on a function which returns a base type,
I only get the first row. When I select from a view which is based on a
function returning a composite type, it triggers an assertion. I've
traced the latter down to a slot pointer which is reset to NULL
somewhere. Haven't had the time to get much further. In both cases,
selecting from the function directly works great.

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Tom Lane wrote:
>> It's not unlikely that those issues are exactly due to not having rescan
>> handled properly. What misbehavior are you seeing?

> Hmm, that might just be it.

> When I select from a view based on a function which returns a base type,
> I only get the first row. When I select from a view which is based on a
> function returning a composite type, it triggers an assertion. I've
> traced the latter down to a slot pointer which is reset to NULL
> somewhere.

Um, that's probably not it then. Rescan would only come into play for
a plan node that's being used as the inside of a join, or some other
contexts more complicated than this. A simple view ought to make no
difference at all in the generated plan --- perhaps there's some bit
of the planner that you missed teaching about function RTEs or
FunctionScan plan nodes?

Anyway, I plan to review and apply your patch today, if I don't run
into any major problems. Will look to see if I see a reason for the
view trouble.

regards, tom lane

Tom Lane wrote:
> Um, that's probably not it then. Rescan would only come into play for
> a plan node that's being used as the inside of a join, or some other
> contexts more complicated than this. A simple view ought to make no
> difference at all in the generated plan --- perhaps there's some bit
> of the planner that you missed teaching about function RTEs or
> FunctionScan plan nodes?
>
> Anyway, I plan to review and apply your patch today, if I don't run
> into any major problems. Will look to see if I see a reason for the
> view trouble.

(Sorry for the slow response -- been out all day)

Actually I found late last night that when the view is used, the RTE is
a RangeVar, so the RangeFunction code never gets executed. So I think
your comment above is right on. That may well explain both problems.
I'll start looking again tonight.

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Actually I found late last night that when the view is used, the RTE is
> a RangeVar, so the RangeFunction code never gets executed. So I think
> your comment above is right on. That may well explain both problems.

Hmm. I thought your view problems were explained by the cut-and-pasteos
I noticed in _readRangeTblEntry. Maybe there's more though. I haven't
got to the point of trying to actually execute the patch ... will work
on it more today.

regards, tom lane

Joe Conway <mail(at)joeconway(dot)com> writes:
> Here's the patch, per my post to HACKERS.
> It builds cleanly on my dev box, and passes all regression tests.

I've committed this with some revisions. The VIEW cases you were
worried about seem to work now. I think you'll find that
single-FROM-item cases generally work, and it's time to start worrying
about joins (ie, rescans).

Parameters also need thought. This should be rejected:

regression=# select * from foo, foot(fooid) z where foo.f2 = z.f2;
server closed the connection unexpectedly

On the other hand, IMHO this should work:

regression=# select * from foo where f2 in
regression-# (select f2 from foot(foo.fooid) z where z.fooid = foo.fooid);
server closed the connection unexpectedly

and here again rescanning is going to be critical.

regards, tom lane

PS: test case for above:

create table foo(fooid int, f2 int);
insert into foo values(1, 11);
insert into foo values(2, 22);
insert into foo values(1, 111);

create function foot(int) returns setof foo as '
select * from foo where fooid = $1' language sql;

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>
>>Here's the patch, per my post to HACKERS.
>>It builds cleanly on my dev box, and passes all regression tests.
>
>
> I've committed this with some revisions. The VIEW cases you were
> worried about seem to work now. I think you'll find that
> single-FROM-item cases generally work, and it's time to start worrying
> about joins (ie, rescans).

Thanks! I've been offline most of the weekend, but I can get back on
this now. I'll start work on the rescans and test cases below right
away. Were your revisions extensive? Any major misconceptions on my part?

Thanks,

Joe

>
> Parameters also need thought. This should be rejected:
>
> regression=# select * from foo, foot(fooid) z where foo.f2 = z.f2;
> server closed the connection unexpectedly
>
> On the other hand, IMHO this should work:
>
> regression=# select * from foo where f2 in
> regression-# (select f2 from foot(foo.fooid) z where z.fooid = foo.fooid);
> server closed the connection unexpectedly
>
> and here again rescanning is going to be critical.
>
> regards, tom lane
>
> PS: test case for above:
>
> create table foo(fooid int, f2 int);
> insert into foo values(1, 11);
> insert into foo values(2, 22);
> insert into foo values(1, 111);
>
> create function foot(int) returns setof foo as '
> select * from foo where fooid = $1' language sql;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Joe Conway <mail(at)joeconway(dot)com> writes:
> Were your revisions extensive? Any major misconceptions on my part?

I did a fair amount of polishing on the ExprContext callback stuff,
and removed or moved around some node fields that I thought were
unnecessary or in the wrong place. I also set up proper infrastructure
for cost estimation on function RTEs (though the estimates themselves
are still pretty lame). Nothing I'd call "major"... more in the
line of stylistic improvements...

regards, tom lane

Tom Lane wrote:
> I've committed this with some revisions. The VIEW cases you were
> worried about seem to work now. I think you'll find that
> single-FROM-item cases generally work, and it's time to start worrying
> about joins (ie, rescans).

Hi Tom,

I've been looking through the SRF patch as-committed, and I think I
understand most of your changes, but I have a question: FunctionNext()
now seems to *always* use a tuplestore instead of conditionally using
the store only for rescans, or if the function was explicitly marked as
PM_MATERIALIZE. Do you still think there should be an option to project
tuples without first storing them, or should we eliminate the notion of
function mode and always materialize?

>
> Parameters also need thought. This should be rejected:
>
> regression=# select * from foo, foot(fooid) z where foo.f2 = z.f2;
> server closed the connection unexpectedly

I don't understand why this should be rejected, but it does fail for me
also, due to a NULL slot pointer. At what point should it be rejected?

>
> On the other hand, IMHO this should work:
>
> regression=# select * from foo where f2 in
> regression-# (select f2 from foot(foo.fooid) z where z.fooid = foo.fooid);
> server closed the connection unexpectedly

This also fails in (based on a quick look) exactly the same way -- a
NULL slot pointer (econtext->ecxt_scantuple) passed to ExecEvalVar().

BTW, The test cases I was using previously now all pass (copy below).

Thanks,

Joe

DROP TABLE foo;
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary
key(fooid,foosubid));
INSERT INTO foo VALUES(1,1,'Joe');
INSERT INTO foo VALUES(1,2,'Ed');
INSERT INTO foo VALUES(2,1,'Mary');

-- sql, proretset = f, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo
WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM
foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = f, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE
fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo
WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- C, proretset = f, prorettype = b
SELECT * FROM dblink_replace('123456789987654321', '99', 'HelloWorld');
DROP VIEW vw_dblink_replace;
CREATE VIEW vw_dblink_replace AS SELECT * FROM
dblink_replace('123456789987654321', '99', 'HelloWorld');
SELECT * FROM vw_dblink_replace;

-- C, proretset = t, prorettype = b
SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
DROP VIEW vw_dblink_get_pkey;
CREATE VIEW vw_dblink_get_pkey AS SELECT dblink_get_pkey FROM
dblink_get_pkey('foo');
SELECT * FROM vw_dblink_get_pkey;

Joe Conway <mail(at)joeconway(dot)com> writes:
> I've been looking through the SRF patch as-committed, and I think I
> understand most of your changes, but I have a question: FunctionNext()
> now seems to *always* use a tuplestore instead of conditionally using
> the store only for rescans,

The problem is that as things now stand, you do not know whether you
will be asked to rescan, so you must materialize the result just in
case.

I would like to improve the system so that lower-level plan nodes will
be told whether they need to support rescan; but we aren't there yet,
and I don't think it's the first priority to work on for SRF. Always
materializing will do for the moment.

> Do you still think there should be an option to project
> tuples without first storing them, or should we eliminate the notion of
> function mode and always materialize?

If the function is going to produce a materialized tupleset to begin
with (because that's convenient for it internally) then there's no value
in having nodeFunctionscan.c make duplicate storage of the tupleset.
We need some way of communicating that fact from the function back to
the plan node ... but again, not first priority.

>> Parameters also need thought. This should be rejected:
>>
>> regression=# select * from foo, foot(fooid) z where foo.f2 = z.f2;
>> server closed the connection unexpectedly

> I don't understand why this should be rejected, but it does fail for me
> also, due to a NULL slot pointer. At what point should it be rejected?

In the parser. Ideally, fooid should not even be *visible* while we are
parsing the arguments to the sibling FROM node. Compare the handling of
variable resolution in JOIN/ON clauses --- the namespace gets
manipulated so that those clauses can't see vars from sibling FROM nodes.

>> On the other hand, IMHO this should work:
>>
>> regression=# select * from foo where f2 in
>> regression-# (select f2 from foot(foo.fooid) z where z.fooid = foo.fooid);
>> server closed the connection unexpectedly

> This also fails in (based on a quick look) exactly the same way -- a
> NULL slot pointer (econtext->ecxt_scantuple) passed to ExecEvalVar().

Right. This should work, but the Var has to be converted into a Param
referencing the upper-level variable. I've forgotten right at the
moment where that happens (someplace in the planner) ... but I'll bet
that the someplace doesn't know it needs to process function argument
nodetrees in function RTEs.

regards, tom lane

Tom Lane wrote:
>>I don't understand why this should be rejected, but it does fail for me
>>also, due to a NULL slot pointer. At what point should it be rejected?
>
>
> In the parser. Ideally, fooid should not even be *visible* while we are
> parsing the arguments to the sibling FROM node. Compare the handling of
> variable resolution in JOIN/ON clauses --- the namespace gets
> manipulated so that those clauses can't see vars from sibling FROM nodes.
>

Attached patch takes care of this case. It also passes my previous test
cases (see below). Applies cleanly to CVS tip and passes all regression
tests. Please apply if there are no objections.

I'm still working on the second test case from Tom (the NULL slot
pointer inducing subselect).

Joe

------< tests >-------
test=# \i /opt/src/srf-test.sql
DROP TABLE foo;
DROP
CREATE TABLE foo(fooid int, f2 int);
CREATE
INSERT INTO foo VALUES(1, 11);
INSERT 126218 1
INSERT INTO foo VALUES(2, 22);
INSERT 126219 1
INSERT INTO foo VALUES(1, 111);
INSERT 126220 1
DROP FUNCTION foot(int);
DROP
CREATE FUNCTION foot(int) returns setof foo as 'SELECT * FROM foo WHERE
fooid = $1;' LANGUAGE SQL;
CREATE

-- should fail with ERROR message
select * from foo, foot(fooid) z where foo.f2 = z.f2;
psql:/opt/src/srf-test.sql:10: ERROR: Function relation in FROM clause
may not refer to other relation, "foo"

DROP TABLE foo;
DROP
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary
key(fooid,foosubid));
psql:/opt/src/srf-test.sql:13: NOTICE: CREATE TABLE / PRIMARY KEY will
create implicit index 'foo_pkey' for table 'foo'
CREATE
INSERT INTO foo VALUES(1,1,'Joe');
INSERT 126228 1
INSERT INTO foo VALUES(1,2,'Ed');
INSERT 126229 1
INSERT INTO foo VALUES(2,1,'Mary');
INSERT 126230 1

-- sql, proretset = f, prorettype = b
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
getfoo
--------
1
(1 row)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
getfoo
--------
1
(1 row)

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo
WHERE fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
getfoo
--------
1
1
(2 rows)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
getfoo
--------
1
1
(2 rows)

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM
foo WHERE fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
getfoo
--------
Joe
Ed
(2 rows)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
getfoo
--------
Joe
Ed
(2 rows)

-- sql, proretset = f, prorettype = c
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE
fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
(1 row)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
(1 row)

-- sql, proretset = t, prorettype = c
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo
WHERE fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)

-- C, proretset = f, prorettype = b
SELECT * FROM dblink_replace('123456789987654321', '99', 'HelloWorld');
dblink_replace
----------------------------
12345678HelloWorld87654321
(1 row)

DROP VIEW vw_dblink_replace;
DROP
CREATE VIEW vw_dblink_replace AS SELECT * FROM
dblink_replace('123456789987654321', '99', 'HelloWorld');
CREATE
SELECT * FROM vw_dblink_replace;
dblink_replace
----------------------------
12345678HelloWorld87654321
(1 row)

-- C, proretset = t, prorettype = b
SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
dblink_get_pkey
-----------------
fooid
foosubid
(2 rows)

DROP VIEW vw_dblink_get_pkey;
DROP
CREATE VIEW vw_dblink_get_pkey AS SELECT dblink_get_pkey FROM
dblink_get_pkey('foo');
CREATE
SELECT * FROM vw_dblink_get_pkey;
dblink_get_pkey
-----------------
fooid
foosubid
(2 rows)

Joe Conway wrote:
>>
>
> Attached patch takes care of this case. It also passes my previous
> test

Sorry, I just noticed that I did not finish modifying the comments that
were cut-and-pasted from elsewhere. This patch includes better comments.

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Tom Lane wrote:
>> In the parser. Ideally, fooid should not even be *visible* while we are
>> parsing the arguments to the sibling FROM node. Compare the handling of
>> variable resolution in JOIN/ON clauses --- the namespace gets
>> manipulated so that those clauses can't see vars from sibling FROM nodes.

> Attached patch takes care of this case. It also passes my previous test
> cases (see below). Applies cleanly to CVS tip and passes all regression
> tests. Please apply if there are no objections.

I've applied a simplified form of this patch --- it seemed you were
doing it the hard way. (Possibly I should have recommended
RangeSubselect as a model, not JOIN/ON. Like RangeSubselect,
RangeFunction doesn't need to allow *any* references to Vars of the
current query level.)

Further digging also revealed that query_tree_walker,
query_tree_mutator, and SS_finalize_plan had been missing out on their
responsibilities to process function-RTE expressions. With those things
fixed, it appears that outer-level Var references and sub-selects work
as expected in function-RTE expressions.

I am still concerned about whether ExecFunctionReScan works correctly;
if not, the problems would show up in join and subquery situations.
I think the parser and planner stages are in pretty good shape now,
though. (At least as far as the basic functionality goes. Having
a smarter materialization policy will take work in the planner.)

It's not too soon to start thinking about documentation and regression
tests for SRFs ...

regards, tom lane

Tom Lane wrote:
> I am still concerned about whether ExecFunctionReScan works correctly;
> if not, the problems would show up in join and subquery situations.
> I think the parser and planner stages are in pretty good shape now,
> though. (At least as far as the basic functionality goes. Having
> a smarter materialization policy will take work in the planner.)

I have been beating heavily on this function, but so far I can't find an
example which doesn't seem to work correctly. However, I also cannot
find an example which executes this part of the function:

. . .
/*
* Here we have a choice whether to drop the tuplestore (and recompute
* the function outputs) or just rescan it. This should depend on
* whether the function expression contains parameters and/or is
* marked volatile. FIXME soon.
*/
if (node->scan.plan.chgParam != NULL)
{
tuplestore_end((Tuplestorestate *) scanstate->tuplestorestate);
scanstate->tuplestorestate = NULL;
}
else
. . .

Here's at least part of what I've used to test:

CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary
key(fooid,foosubid));

-- use PHP to insert 100,000 records --

VACUUM ANALYZE;
CREATE FUNCTION foorescan(int,int) returns setof foorescan as 'SELECT *
FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL;
select * from foorescan f, (select fooid, foosubid from
foorescan(5000,5010)) as s where f.fooid = s.fooid and f.foosubid =
s.foosubid;
CREATE VIEW vw_foorescan as select * from foorescan f, (select fooid,
foosubid from foorescan(5000,5010)) as s where f.fooid = s.fooid and
f.foosubid = s.foosubid;

--invokes ExecFunctionReScan
select * from foorescan f where f.fooid in (select fooid from
foorescan(5000,5001));

CREATE TABLE barrescan (fooid int primary key);
INSERT INTO barrescan values(5000);
INSERT INTO barrescan values(5001);
INSERT INTO barrescan values(5002);
INSERT INTO barrescan values(5003);
INSERT INTO barrescan values(5004);
INSERT INTO barrescan values(5005);
INSERT INTO barrescan values(5006);
INSERT INTO barrescan values(5007);
INSERT INTO barrescan values(5008);
INSERT INTO barrescan values(5009);

--invokes ExecFunctionReScan
select * from random(), foorescan(5000,5010) f JOIN barrescan b ON
b.fooid = f.fooid WHERE f.foosubid = 9;
select * from foorescan(5000,5000 + (random() * 10)::int) f JOIN
barrescan b ON b.fooid = f.fooid WHERE f.foosubid = 9;

Any ideas on getting (node->scan.plan.chgParam != NULL) to be true?

Joe

Tom Lane wrote:
> I am still concerned about whether ExecFunctionReScan works correctly;
> if not, the problems would show up in join and subquery situations.
> I think the parser and planner stages are in pretty good shape now,
> though. (At least as far as the basic functionality goes. Having
> a smarter materialization policy will take work in the planner.)
>
Here's a small patch to ExecFunctionReScan. It was clearing
scanstate->csstate.cstate.cs_ResultTupleSlot
when I think it should have been clearing
scanstate->csstate.css_ScanTupleSlot

although there is no discernable (at least to me) difference either way.

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Any ideas on getting (node->scan.plan.chgParam != NULL) to be true?

You need something that passes a parameter into the scan node.
I think the only thing that would do it is a subquery that references
an outer-level variable, for example

select * from foo where fooid in
(select barid from bar(foo.fieldx));

Here, each time we rescan the subselect result for a new foo row, we
need to update the foo.fieldx Param to the new value for the new row.
That's what the chgParam mechanism is for: to notify you that a Param
changed since your last scan. (Without that, you could and probably
should just rewind and regurgitate your prior output.)

Note that

select * from foo, bar(5000) where fooid = barid

does not involve any parameters: the WHERE condition will be executed
by the join node, and the FunctionScan node will have no contact at all
with data coming from the other table.

Now that I think about it, it's possible that ExecFunctionReScan is
correct now, at least given the simplistic always-materialize policy
that we've implemented so far. But it hasn't gotten much testing.

regards, tom lane

Joe Conway <mail(at)joeconway(dot)com> writes:
> Here's a small patch to ExecFunctionReScan. It was clearing
> scanstate->csstate.cstate.cs_ResultTupleSlot
> when I think it should have been clearing
> scanstate->csstate.css_ScanTupleSlot

Why do you think that? To the extent that other rescan routines are
clearing anything, they're clearing ResultTupleSlot.

> although there is no discernable (at least to me) difference either way.

My guess is that it's pretty much a no-op, since the slot will get
cleared and re-used on the next call anyway.

regards, tom lane

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>
>>Here's a small patch to ExecFunctionReScan. It was clearing
>> scanstate->csstate.cstate.cs_ResultTupleSlot
>>when I think it should have been clearing
>> scanstate->csstate.css_ScanTupleSlot
>
>
> Why do you think that? To the extent that other rescan routines are
> clearing anything, they're clearing ResultTupleSlot.

Well, nodeMaterial and nodeSort both clear cs_ResultTupleSlot, but they
also use cs_ResultTupleSlot in ExecMaterial/ExecSort, whereas
FunctionNext uses css_ScanTupleSlot. But as you pointed out, perhaps
it's a noop anyway.

I was having trouble getting everything to work correctly with
FunctionNext using cs_ResultTupleSlot. I guess I don't really understand
the distinction, but I did note that the scan nodes (subqueryscan,
seqscan, etc) used css_ScanTupleSlot, while the materialization nodes
tended to use cs_ResultTupleSlot.

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> I was having trouble getting everything to work correctly with
> FunctionNext using cs_ResultTupleSlot. I guess I don't really understand
> the distinction, but I did note that the scan nodes (subqueryscan,
> seqscan, etc) used css_ScanTupleSlot, while the materialization nodes
> tended to use cs_ResultTupleSlot.

ResultTupleSlot is generally used by plan nodes that do ExecProject;
it holds the tuple formed by ExecProject (ie, the calculated SELECT
targetlist). ScanTupleSlot is normally the raw input tuple. For
Functionscan I'd suppose that the scan tuple is the tuple returned
by the function and ResultTupleSlot holds the result of ExecProject.
To see the difference, consider

SELECT a, b, c+1 FROM foo(33);

where foo returns a tuple (a,b,c,d,e). The scanned tuple is
(a,b,c,d,e), the projected tuple is (a,b,c+1).

It may well be that rescan could usefully clear both scan and result
tuples, but I don't see the point of making such a change only in
FunctionScan.

regards, tom lane

was Re: [PATCHES] SRF patch (was Re: [HACKERS] troubleshooting pointers)

Tom Lane wrote:
>
> Now that I think about it, it's possible that ExecFunctionReScan is
> correct now, at least given the simplistic always-materialize policy
> that we've implemented so far. But it hasn't gotten much testing.

OK -- the attached (stand alone) test script exercises
ExecFunctionReScan, including cases with chgParam != NULL. I'll try to
come up with one or two more variants for the latter, but so far I have
not found any misbehavior.

Joe

Tom Lane wrote:
> It's not too soon to start thinking about documentation and
> regression tests for SRFs ...

Attached is a regression test patch for SRFs. I based it on the test
scripts that I have been using, minus the C function tests and without
calls to random() -- figured random() wouldn't work too well for a
regression test ;-)

Joe

Joe Conway wrote:
> Tom Lane wrote:
>>
>> Now that I think about it, it's possible that ExecFunctionReScan is
>> correct now, at least given the simplistic always-materialize policy
>> that we've implemented so far. But it hasn't gotten much testing.
>
> OK -- the attached (stand alone) test script exercises
> ExecFunctionReScan, including cases with chgParam != NULL. I'll try to
> come up with one or two more variants for the latter, but so far I have
> not found any misbehavior.

I'm thinking about next steps for SRFs and looking for input. The
current status is that SRFs seem to work properly in the
alway-materialize mode, for the following cases of FROM clause functions
and VIEWs created based on FROM clause functions:

(rehash from earlier post)
Language RetSet RetType Status
--------------- ------- ------- ---------------------
C t b OK
C t c Not tested
C f b OK
C f c Not tested
SQL t b OK
SQL t c OK
SQL f b OK
SQL f c OK
PL/pgSQL t b No retset support
PL/pgSQL t c No retset support
PL/pgSQL f b OK
PL/pgSQL f c OK
-----------------------------------------------------
RetSet: t = function declared to return setof something
RetType: b = base type; c = composite type

I've also submitted a patch for a regression test (any feedback?). At
this point I know of several things which need to be done (or at least I
think they are desirable):

1. Documentation -- it wasn't clear if Joel Burton was going to have
time to contribute something here, but if not, I'll start working on
this next. Any guidance as to which section of the docs this should go in?

2. Create a sample C-function which returns setof a composite type
(possibly in conjunction with #1)

3. PL/pgSQL support for returning sets -- this seems to me like an
important item if SRFs are to be useful to the masses. Any pointers on
how to approach this would be appreciated.

4. Non-materialize mode support for SRFs.

5. Improve the system so that lower-level plan nodes will be told
whether they need to support rescan.

6. Support for named composite types that don't have a table tied to them.

Have I missed anything major? Is this order of priority reasonable?

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> I'm thinking about next steps for SRFs and looking for input. ... At
> this point I know of several things which need to be done (or at least I
> think they are desirable):

> 1. Documentation -- it wasn't clear if Joel Burton was going to have
> time to contribute something here, but if not, I'll start working on
> this next. Any guidance as to which section of the docs this should go in?

There is related material currently in the SQL-functions section of the
programmer's guide. This should perhaps be moved to someplace where
it's more clearly relevant to all types of functions. On the other hand
it's awfully nice to be able to show simple examples, so I'm not sure we
want to divorce the material from SQL functions entirely.

> 3. PL/pgSQL support for returning sets -- this seems to me like an
> important item if SRFs are to be useful to the masses. Any pointers on
> how to approach this would be appreciated.

Does Oracle's pl/sql support this? If so what does it look like?

> 6. Support for named composite types that don't have a table tied to them.

I agree that this is bottom priority. It doesn't really add any
functionality (since a dummy table doesn't cost much of anything).
And a clean solution would require major rearchitecting of the system
tables --- pg_attribute rows would need to be tied to pg_type rows for
composite types, not to pg_class rows. While this would be quite doable
considering the backend alone, I'm not excited about the prospect of
breaking every catalog-examining client in sight. Another interesting
question is whether inheritance now applies to types rather than tables,
and if so what does that imply?

(OTOH one could make a good argument that now is the time to do it
if we're ever gonna do it --- clients that are not schema-aware will
be badly in need of work anyway for 7.3...)

regards, tom lane

Tom Lane wrote:
>>3. PL/pgSQL support for returning sets -- this seems to me like an
>>important item if SRFs are to be useful to the masses. Any pointers on
>>how to approach this would be appreciated.
>
> Does Oracle's pl/sql support this? If so what does it look like?

I *think* Oracle pl/sql can return (the equivilent of) setof composite
using a special Oracle package (DBMS_OUTPUT, see:
http://www.ora.com/catalog/oraclebip/chapter/ch06.html), but it cannot
be used as a row source in a FROM clause. Hopefully an Oracle guru will
correct or add to this.

I know that MS SQL Server can return one *or more* result sets from a
"stored procedure", however they cannot be used as FROM clause row
sources either (at least not as of MSSQL 7, but I don't think that has
changed in MSSQL 2000). The syntax is something like:
exec sp_myprocedure
It is *not* possible to define a VIEW based on a stored procedure, but
many MS centric report writers allow the "exec sp_myprocedure" syntax as
a row source for reports.

As far as PL/pgSQL is concerned, I was thinking that a new type of
RETURN (maybe "RETURN NEXT myval" ??) command could be used, which would
indicate "rsi->isDone = ExprMultipleResult", and that the standard
RETURN command would set "rsi->isDone = ExprEndResult", but only if
"fcinfo->resultinfo != NULL". That way you could do something like:

. . .
FOR row IN select_query LOOP
statements
RETURN NEXT row;
END LOOP;

RETURN NULL;
. . .

Does this sound reasonable?

Joe

> (OTOH one could make a good argument that now is the time to do it
> if we're ever gonna do it --- clients that are not schema-aware will
> be badly in need of work anyway for 7.3...)

Maybe the attisdropped column should be created and added to the
pg_attribute catalog now as well. It would always be false, but would mean
only 1 round of mad postgres admin program hacking... Might be able to
avoid catalog changes for a drop column implementation in 7.4...

Chris

On Sun, 2002-05-26 at 21:55, Joe Conway wrote:
> Tom Lane wrote:
> >>3. PL/pgSQL support for returning sets -- this seems to me like an
> >>important item if SRFs are to be useful to the masses. Any pointers on
> >>how to approach this would be appreciated.
> >
> > Does Oracle's pl/sql support this? If so what does it look like?
>
> I *think* Oracle pl/sql can return (the equivilent of) setof composite
> using a special Oracle package (DBMS_OUTPUT, see:
> http://www.ora.com/catalog/oraclebip/chapter/ch06.html), but it cannot
> be used as a row source in a FROM clause. Hopefully an Oracle guru will
> correct or add to this.

I'm no Oracle guru, but this is what a quick Google search found me:

http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89856/08_subs.htm#19677

-------------
Hannu

Hannu Krosing wrote:
> On Sun, 2002-05-26 at 21:55, Joe Conway wrote:
>
>>Tom Lane wrote:
>>
>>>>3. PL/pgSQL support for returning sets -- this seems to me like an
>>>>important item if SRFs are to be useful to the masses. Any pointers on
>>>>how to approach this would be appreciated.
>>>
>>>Does Oracle's pl/sql support this? If so what does it look like?
>>
>>I *think* Oracle pl/sql can return (the equivilent of) setof composite
>>using a special Oracle package (DBMS_OUTPUT, see:
>>http://www.ora.com/catalog/oraclebip/chapter/ch06.html), but it cannot
>>be used as a row source in a FROM clause. Hopefully an Oracle guru will
>>correct or add to this.
>
>
> I'm no Oracle guru, but this is what a quick Google search found me:
>
> http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89856/08_subs.htm#19677
>

After a quick look, this appears to be a very relevant document. Does
anyone know if this is new in 9i?

Joe

Tom Lane wrote:

>>3. PL/pgSQL support for returning sets -- this seems to me like an
>>important item if SRFs are to be useful to the masses. Any pointers on
>>how to approach this would be appreciated.
>>
>
>Does Oracle's pl/sql support this? If so what does it look like?
>
Oracle supports "pipelined functions". These functions use operator
PIPE(set%rowtype) to return a row.
Syntax for queries using pipelined functions:

SELECT f1,f2,... FROM TABLE(func(p1,p2, ...));

It seems that the most important thing to implement for PL/pgSQL
functions returning sets is restoring of the function execution state in
the next call

WBR, Valentine Zaretsky

Here's the first doc patch for SRFs. The patch covers general
information and SQL language specific info wrt SRFs. I've taken to
calling this feature "Table Fuctions" to be consistent with (at least)
one well known RDBMS.

Note that I mention under the SQL language Table Function section that
"Functions returning sets" in query target lists is a deprecated
feature, subject to removal in later releases. I think there was general
agreement on this, but I thought it was worth pointing out.

I still need to submit some C language function documentation, but was
hoping to see if any further changes were needed in the Composite and
SRF API patch that I sent in earlier. I've started the documentation but
will hold of sending in a patch for now on that.

If no objections, please apply.

Thanks,

Joe

p.s. any feedback on the SRF regression test patch?

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Joe Conway wrote:
> Here's the first doc patch for SRFs. The patch covers general
> information and SQL language specific info wrt SRFs. I've taken to
> calling this feature "Table Fuctions" to be consistent with (at least)
> one well known RDBMS.
>
> Note that I mention under the SQL language Table Function section that
> "Functions returning sets" in query target lists is a deprecated
> feature, subject to removal in later releases. I think there was general
> agreement on this, but I thought it was worth pointing out.
>
> I still need to submit some C language function documentation, but was
> hoping to see if any further changes were needed in the Composite and
> SRF API patch that I sent in earlier. I've started the documentation but
> will hold of sending in a patch for now on that.
>
> If no objections, please apply.
>
> Thanks,
>
> Joe
>
> p.s. any feedback on the SRF regression test patch?

[ text/html is unsupported, treating like TEXT/PLAIN ]

> Index: doc//src/sgml/xfunc.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/doc/src/sgml/xfunc.sgml,v
> retrieving revision 1.51
> diff -c -r1.51 xfunc.sgml
> *** doc//src/sgml/xfunc.sgml 22 Mar 2002 19:20:33 -0000 1.51
> --- doc//src/sgml/xfunc.sgml 13 Jun 2002 20:30:27 -0000
> ***************
> *** 188,193 ****
> --- 188,194 ----
> 1
> </screen>
> </para>
> +
> </sect2>
>
> <sect2>
> ***************
> *** 407,427 ****
> </sect2>
>
> <sect2>
> ! <title><acronym>SQL</acronym> Functions Returning Sets</title>
>
> <para>
> ! As previously mentioned, an SQL function may be declared as
> ! returning <literal>SETOF <replaceable>sometype</></literal>.
> ! In this case the function's final <command>SELECT</> query is executed to
> ! completion, and each row it outputs is returned as an element
> ! of the set.
> </para>
>
> <para>
> ! Functions returning sets may only be called in the target list
> ! of a <command>SELECT</> query. For each row that the <command>SELECT</> generates by itself,
> ! the function returning set is invoked, and an output row is generated
> ! for each element of the function's result set. An example:
>
> <programlisting>
> CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
> --- 408,460 ----
> </sect2>
>
> <sect2>
> ! <title><acronym>SQL</acronym> Table Functions (Functions Returning Sets)</title>
>
> <para>
> ! A table function is one that may be used in the <command>FROM</command>
> ! clause of a query. All SQL Language functions may be used in this manner.
> ! If the function is defined to return a base type, the table function
> ! produces a one column result set. If the function is defined to
> ! return <literal>SETOF <replaceable>sometype</></literal>, the table
> ! function returns multiple rows. To illustrate a SQL table function,
> ! consider the following, which returns <literal>SETOF</literal> a
> ! composite type:
> !
> ! <programlisting>
> ! CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
> ! INSERT INTO foo VALUES(1,1,'Joe');
> ! INSERT INTO foo VALUES(1,2,'Ed');
> ! INSERT INTO foo VALUES(2,1,'Mary');
> ! CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
> ! SELECT * FROM foo WHERE fooid = $1;
> ! ' LANGUAGE SQL;
> ! SELECT * FROM getfoo(1) AS t1;
> ! </programlisting>
> !
> ! <screen>
> ! fooid | foosubid | fooname
> ! -------+----------+---------
> ! 1 | 1 | Joe
> ! 1 | 2 | Ed
> ! (2 rows)
> ! </screen>
> </para>
>
> <para>
> ! When an SQL function is declared as returning <literal>SETOF
> ! <replaceable>sometype</></literal>, the function's final
> ! <command>SELECT</> query is executed to completion, and each row it
> ! outputs is returned as an element of the set.
> ! </para>
> !
> ! <para>
> ! Functions returning sets may also currently be called in the target list
> ! of a <command>SELECT</> query. For each row that the <command>SELECT</>
> ! generates by itself, the function returning set is invoked, and an output
> ! row is generated for each element of the function's result set. Note,
> ! however, that this capability is deprecated and may be removed in future
> ! releases. The following is an example function returning a set from the
> ! target list:
>
> <programlisting>
> CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
> ***************
> *** 1620,1625 ****
> --- 1653,1706 ----
> </para>
> </sect1>
>
> + <sect1 id="xfunc-tablefunctions">
> + <title>Table Functions</title>
> +
> + <indexterm zone="xfunc-tablefunctions"><primary>function</></>
> +
> + <para>
> + Table functions are functions that produce a set of rows, made up of
> + either base (scalar) data types, or composite (multi-column) data types.
> + They are used like a table, view, or subselect in the <literal>FROM</>
> + clause of a query. Columns returned by table functions may be included in
> + <literal>SELECT</>, <literal>JOIN</>, or <literal>WHERE</> clauses in the
> + same manner as a table, view, or subselect column.
> + </para>
> +
> + <para>
> + If a table function returns a base data type, the single result column
> + is named for the function. If the function returns a composite type, the
> + result columns get the same names as the individual attributes of the type.
> + </para>
> +
> + <para>
> + A table function may be aliased in the <literal>FROM</> clause, but it also
> + may be left unaliased. If a function is used in the FROM clause with no
> + alias, the function name is used as the relation name.
> + </para>
> +
> + <para>
> + Table functions work wherever tables do in <literal>SELECT</> statements.
> + For example
> + <programlisting>
> + CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
> + CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
> + SELECT * FROM getfoo(1) AS t1;
> + SELECT * FROM foo where foosubid in (select foosubid from getfoo(foo.fooid) z where z.fooid = foo.fooid);
> + CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> + SELECT * FROM vw_getfoo;
> + </programlisting>
> + are all valid statements.
> + </para>
> +
> + <para>
> + Currently, table functions are supported as SQL language functions
> + (<xref linkend="xfunc-sql">) and C language functions
> + (<xref linkend="xfunc-c">). See these individual sections for more
> + details.
> + </para>
> +
> + </sect1>
>
> <sect1 id="xfunc-plhandler">
> <title>Procedural Language Handlers</title>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Patch applied. Thanks.

---------------------------------------------------------------------------

Bruce Momjian wrote:
>
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
> http://candle.pha.pa.us/cgi-bin/pgpatches
>
> I will try to apply it within the next 48 hours.
>
> ---------------------------------------------------------------------------
>
>
> Joe Conway wrote:
> > Here's the first doc patch for SRFs. The patch covers general
> > information and SQL language specific info wrt SRFs. I've taken to
> > calling this feature "Table Fuctions" to be consistent with (at least)
> > one well known RDBMS.
> >
> > Note that I mention under the SQL language Table Function section that
> > "Functions returning sets" in query target lists is a deprecated
> > feature, subject to removal in later releases. I think there was general
> > agreement on this, but I thought it was worth pointing out.
> >
> > I still need to submit some C language function documentation, but was
> > hoping to see if any further changes were needed in the Composite and
> > SRF API patch that I sent in earlier. I've started the documentation but
> > will hold of sending in a patch for now on that.
> >
> > If no objections, please apply.
> >
> > Thanks,
> >
> > Joe
> >
> > p.s. any feedback on the SRF regression test patch?
>
> [ text/html is unsupported, treating like TEXT/PLAIN ]
>
> > Index: doc//src/sgml/xfunc.sgml
> > ===================================================================
> > RCS file: /opt/src/cvs/pgsql/doc/src/sgml/xfunc.sgml,v
> > retrieving revision 1.51
> > diff -c -r1.51 xfunc.sgml
> > *** doc//src/sgml/xfunc.sgml 22 Mar 2002 19:20:33 -0000 1.51
> > --- doc//src/sgml/xfunc.sgml 13 Jun 2002 20:30:27 -0000
> > ***************
> > *** 188,193 ****
> > --- 188,194 ----
> > 1
> > </screen>
> > </para>
> > +
> > </sect2>
> >
> > <sect2>
> > ***************
> > *** 407,427 ****
> > </sect2>
> >
> > <sect2>
> > ! <title><acronym>SQL</acronym> Functions Returning Sets</title>
> >
> > <para>
> > ! As previously mentioned, an SQL function may be declared as
> > ! returning <literal>SETOF <replaceable>sometype</></literal>.
> > ! In this case the function's final <command>SELECT</> query is executed to
> > ! completion, and each row it outputs is returned as an element
> > ! of the set.
> > </para>
> >
> > <para>
> > ! Functions returning sets may only be called in the target list
> > ! of a <command>SELECT</> query. For each row that the <command>SELECT</> generates by itself,
> > ! the function returning set is invoked, and an output row is generated
> > ! for each element of the function's result set. An example:
> >
> > <programlisting>
> > CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
> > --- 408,460 ----
> > </sect2>
> >
> > <sect2>
> > ! <title><acronym>SQL</acronym> Table Functions (Functions Returning Sets)</title>
> >
> > <para>
> > ! A table function is one that may be used in the <command>FROM</command>
> > ! clause of a query. All SQL Language functions may be used in this manner.
> > ! If the function is defined to return a base type, the table function
> > ! produces a one column result set. If the function is defined to
> > ! return <literal>SETOF <replaceable>sometype</></literal>, the table
> > ! function returns multiple rows. To illustrate a SQL table function,
> > ! consider the following, which returns <literal>SETOF</literal> a
> > ! composite type:
> > !
> > ! <programlisting>
> > ! CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
> > ! INSERT INTO foo VALUES(1,1,'Joe');
> > ! INSERT INTO foo VALUES(1,2,'Ed');
> > ! INSERT INTO foo VALUES(2,1,'Mary');
> > ! CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
> > ! SELECT * FROM foo WHERE fooid = $1;
> > ! ' LANGUAGE SQL;
> > ! SELECT * FROM getfoo(1) AS t1;
> > ! </programlisting>
> > !
> > ! <screen>
> > ! fooid | foosubid | fooname
> > ! -------+----------+---------
> > ! 1 | 1 | Joe
> > ! 1 | 2 | Ed
> > ! (2 rows)
> > ! </screen>
> > </para>
> >
> > <para>
> > ! When an SQL function is declared as returning <literal>SETOF
> > ! <replaceable>sometype</></literal>, the function's final
> > ! <command>SELECT</> query is executed to completion, and each row it
> > ! outputs is returned as an element of the set.
> > ! </para>
> > !
> > ! <para>
> > ! Functions returning sets may also currently be called in the target list
> > ! of a <command>SELECT</> query. For each row that the <command>SELECT</>
> > ! generates by itself, the function returning set is invoked, and an output
> > ! row is generated for each element of the function's result set. Note,
> > ! however, that this capability is deprecated and may be removed in future
> > ! releases. The following is an example function returning a set from the
> > ! target list:
> >
> > <programlisting>
> > CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
> > ***************
> > *** 1620,1625 ****
> > --- 1653,1706 ----
> > </para>
> > </sect1>
> >
> > + <sect1 id="xfunc-tablefunctions">
> > + <title>Table Functions</title>
> > +
> > + <indexterm zone="xfunc-tablefunctions"><primary>function</></>
> > +
> > + <para>
> > + Table functions are functions that produce a set of rows, made up of
> > + either base (scalar) data types, or composite (multi-column) data types.
> > + They are used like a table, view, or subselect in the <literal>FROM</>
> > + clause of a query. Columns returned by table functions may be included in
> > + <literal>SELECT</>, <literal>JOIN</>, or <literal>WHERE</> clauses in the
> > + same manner as a table, view, or subselect column.
> > + </para>
> > +
> > + <para>
> > + If a table function returns a base data type, the single result column
> > + is named for the function. If the function returns a composite type, the
> > + result columns get the same names as the individual attributes of the type.
> > + </para>
> > +
> > + <para>
> > + A table function may be aliased in the <literal>FROM</> clause, but it also
> > + may be left unaliased. If a function is used in the FROM clause with no
> > + alias, the function name is used as the relation name.
> > + </para>
> > +
> > + <para>
> > + Table functions work wherever tables do in <literal>SELECT</> statements.
> > + For example
> > + <programlisting>
> > + CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
> > + CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
> > + SELECT * FROM getfoo(1) AS t1;
> > + SELECT * FROM foo where foosubid in (select foosubid from getfoo(foo.fooid) z where z.fooid = foo.fooid);
> > + CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> > + SELECT * FROM vw_getfoo;
> > + </programlisting>
> > + are all valid statements.
> > + </para>
> > +
> > + <para>
> > + Currently, table functions are supported as SQL language functions
> > + (<xref linkend="xfunc-sql">) and C language functions
> > + (<xref linkend="xfunc-c">). See these individual sections for more
> > + details.
> > + </para>
> > +
> > + </sect1>
> >
> > <sect1 id="xfunc-plhandler">
> > <title>Procedural Language Handlers</title>
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026