Обсуждение: 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 fromJC> aJC> VIEW produces "Buffer Leak" warnings, whileselecting from theJC> 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 | one1 | 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)RETURNSJC> *setof* int AS..." does not produce the warning. Any ideas what IJC> should be focusing on to track this down? Does anyone have anyJC> 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@joeconway.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@joeconway.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@joeconway.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
------409365876093 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@joeconway.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
Joe Conway <mail@joeconway.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@joeconway.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@joeconway.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@joeconway.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
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.
Thisshould 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
Joe Conway <mail@joeconway.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
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 DROP TABLE foorescan; CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid)); INSERT INTO foorescan values(5000,1,'abc.5000.1'); INSERT INTO foorescan values(5001,1,'abc.5001.1'); INSERT INTO foorescan values(5002,1,'abc.5002.1'); INSERT INTO foorescan values(5003,1,'abc.5003.1'); INSERT INTO foorescan values(5004,1,'abc.5004.1'); INSERT INTO foorescan values(5005,1,'abc.5005.1'); INSERT INTO foorescan values(5006,1,'abc.5006.1'); INSERT INTO foorescan values(5007,1,'abc.5007.1'); INSERT INTO foorescan values(5008,1,'abc.5008.1'); INSERT INTO foorescan values(5009,1,'abc.5009.1'); INSERT INTO foorescan values(5000,2,'abc.5000.2'); INSERT INTO foorescan values(5001,2,'abc.5001.2'); INSERT INTO foorescan values(5002,2,'abc.5002.2'); INSERT INTO foorescan values(5003,2,'abc.5003.2'); INSERT INTO foorescan values(5004,2,'abc.5004.2'); INSERT INTO foorescan values(5005,2,'abc.5005.2'); INSERT INTO foorescan values(5006,2,'abc.5006.2'); INSERT INTO foorescan values(5007,2,'abc.5007.2'); INSERT INTO foorescan values(5008,2,'abc.5008.2'); INSERT INTO foorescan values(5009,2,'abc.5009.2'); INSERT INTO foorescan values(5000,3,'abc.5000.3'); INSERT INTO foorescan values(5001,3,'abc.5001.3'); INSERT INTO foorescan values(5002,3,'abc.5002.3'); INSERT INTO foorescan values(5003,3,'abc.5003.3'); INSERT INTO foorescan values(5004,3,'abc.5004.3'); INSERT INTO foorescan values(5005,3,'abc.5005.3'); INSERT INTO foorescan values(5006,3,'abc.5006.3'); INSERT INTO foorescan values(5007,3,'abc.5007.3'); INSERT INTO foorescan values(5008,3,'abc.5008.3'); INSERT INTO foorescan values(5009,3,'abc.5009.3'); INSERT INTO foorescan values(5000,4,'abc.5000.4'); INSERT INTO foorescan values(5001,4,'abc.5001.4'); INSERT INTO foorescan values(5002,4,'abc.5002.4'); INSERT INTO foorescan values(5003,4,'abc.5003.4'); INSERT INTO foorescan values(5004,4,'abc.5004.4'); INSERT INTO foorescan values(5005,4,'abc.5005.4'); INSERT INTO foorescan values(5006,4,'abc.5006.4'); INSERT INTO foorescan values(5007,4,'abc.5007.4'); INSERT INTO foorescan values(5008,4,'abc.5008.4'); INSERT INTO foorescan values(5009,4,'abc.5009.4'); INSERT INTO foorescan values(5000,5,'abc.5000.5'); INSERT INTO foorescan values(5001,5,'abc.5001.5'); INSERT INTO foorescan values(5002,5,'abc.5002.5'); INSERT INTO foorescan values(5003,5,'abc.5003.5'); INSERT INTO foorescan values(5004,5,'abc.5004.5'); INSERT INTO foorescan values(5005,5,'abc.5005.5'); INSERT INTO foorescan values(5006,5,'abc.5006.5'); INSERT INTO foorescan values(5007,5,'abc.5007.5'); INSERT INTO foorescan values(5008,5,'abc.5008.5'); INSERT INTO foorescan values(5009,5,'abc.5009.5'); DROP FUNCTION foorescan(int,int); CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;'LANGUAGE SQL; DROP VIEW vw_foorescan; CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004); --invokes ExecFunctionReScan SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2; DROP TABLE barrescan; CREATE TABLE barrescan (fooid int primary key); 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); --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; DROP FUNCTION foorescan(int); CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL; --invokes ExecFunctionReScan with chgParam != NULL SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDERBY 1,2; SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid))GROUP BY b.fooid ORDER BY 1,2; DROP VIEW fooview1; CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROMfoorescan(b.fooid)) ORDER BY 1,2; SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004; DROP VIEW fooview2; CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooidAND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
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@joeconway.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