Re: SELECT on VIEW returns wrong result, Buffer Leak

pgsql-bugs(at)postgresql(dot)org writes:
> CREATE FUNCTION exceptions(int4) RETURNS setof varchar(64) AS '
> SELECT property_id
> FROM itm_exception
> WHERE item_id = $1
> ' LANGUAGE 'sql';
> CREATE VIEW itm_property_ALL AS
> SELECT *
> FROM itm_property
> WHERE property_id NOT IN (SELECT exceptions(item_id))
> ;

Okay ... I get different, but equally wrong, results.

The problem is a fundamental shortcoming in the way the Berkeley guys
did SQL-functions-returning-sets: the plan inside the function is left
running from call to call, and there's no provision for shutting it down
if the function is not allowed to run to completion. Since the NOT IN
operator stops demanding results once it finds a match, the subselect's
function call is left incomplete --- and when the next iteration of the
outer query calls the subselect again, you get more of the previous
cycle's output, not a fresh run of the function. (The same problem
explains the Buffer Leak notices: the last outer query cycle leaves the
function still active and holding buffer pins.)

This has been a known problem for awhile, and I don't see any nice way
to fix it. I don't really want to invest more effort in the Berkeley
function-returning-set code anyway; I'd rather rip it out and do
something that treats a function returning set as a table source.
(That is, you'd do "SELECT ... FROM func(args)".) There are plans to
make that happen in the next release or two.

In the meantime, I'd suggest writing the view without a function call.

regards, tom lane