Обсуждение: SELECT on VIEW returns wrong result, Buffer Leak

Ulrich Döhner (Ulrich.Doehner@suse.de) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
SELECT on VIEW returns wrong result, Buffer Leak

Long Description
The following SELECT statements return different number of lines,
depending on the ORDER clause (Tested with PostgreSQL 7.1.2 and 7.1.0)

SELECT * FROM itm_property_all;                        -- 18 lines
SELECT * FROM itm_property_all ORDER BY item_id;       -- 16 lines
SELECT * FROM itm_property_all ORDER BY item_id DESC;  -- 10 lines

The probable cause is a Buffer Leak
psql: _database.txt:7: NOTICE:  Buffer Leak: [046] (freeNext=-3, freePrev=-3, relname=itm_exception_pkey, blockNum=1,
flags=0x4,refcount=1 1) 
psql:_database.txt:7: NOTICE:  Buffer Leak: [055] (freeNext=-3, freePrev=-3, relname=itm_exception, blockNum=0,
flags=0x4,refcount=1 1) 

Actual Output:
 item_id |                    property_id                |    day
---------+-----------------------------------------------+------------
       2 | BXXXXX_XXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX     | 2001-03-01
       4 | DXXXXXXXX_XX_XXXXXXX_XXXX_XXXXX_XXXX_XXXXXX   | 2001-03-01
       6 | FXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX            | 2001-03-01
       8 | HXXXXXXX_XXX_XXXXXXX_XXXX_XXXXX_XXXX_XXXXXX   | 2001-03-01
      10 | JXXXXXXX_XXXXX_XXXXXXX_XXXX_XXXXX_XXXX_XXXXXX | 2001-03-01
       2 | LXX_XXX_XXXXXXXX_XXXXX_XXXX_XXXXXX            | 2001-03-01
       4 | NXXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX          | 2001-03-01
       6 | PXXXXXXXX_XXXX_XXXX_XXX_XXX                   | 2001-03-01
       7 | QXXXXX_XXX_XXXXXXXX_XXXXX_XXXX_XXXXXX         | 2001-03-01
       8 | RXXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX          | 2001-03-01
       9 | SXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX           | 2001-03-01
      10 | TXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX            | 2001-03-01
       1 | UXXXXX_XXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX      | 2001-03-01
       2 | VXXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX          | 2001-03-01
       3 | WXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX             | 2001-03-01
       4 | XXXX_XXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX         | 2001-03-01
       5 | YXXXXXXX_XXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX     | 2001-03-01
       6 | ZXXXXXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX       | 2001-03-01
(18 rows)

psql: _database.txt:7: NOTICE:  Buffer Leak: [046] (freeNext=-3, freePrev=-3, relname=itm_exception_pkey, blockNum=1,
flags=0x4,refcount=1 1) 
psql: _database.txt:7: NOTICE:  Buffer Leak: [055] (freeNext=-3, freePrev=-3, relname=itm_exception, blockNum=0,
flags=0x4,refcount=1 1) 
 item_id |                   property_id                |    day
---------+----------------------------------------------+------------
       1 | UXXXXX_XXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX     | 2001-03-01
       1 | AXXXXXXX_XXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX     | 2001-03-01
       2 | VXXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX         | 2001-03-01
       2 | BXXXXX_XXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX    | 2001-03-01
       3 | WXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX            | 2001-03-01
       3 | CXXXXXXXX_XXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX    | 2001-03-01
       4 | XXXX_XXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX        | 2001-03-01
       4 | DXXXXXXXX_XX_XXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX | 2001-03-01
       5 | YXXXXXXX_XXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX    | 2001-03-01
       5 | EXXXXXXXX_XXX_XXXXXXXX_XXXXX_XXXX_XXXXXX     | 2001-03-01
       6 | ZXXXXXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX      | 2001-03-01
       6 | FXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX           | 2001-03-01
       7 | QXXXXX_XXX_XXXXXXXX_XXXXX_XXXX_XXXXXX        | 2001-03-01
       8 | RXXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX         | 2001-03-01
       9 | SXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX          | 2001-03-01
      10 | TXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX           | 2001-03-01
(16 rows)

 item_id |                property_id                |    day
---------+-------------------------------------------+------------
      10 | TXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX        | 2001-03-01
       9 | SXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX       | 2001-03-01
       8 | RXXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX      | 2001-03-01
       7 | QXXXXX_XXX_XXXXXXXX_XXXXX_XXXX_XXXXXX     | 2001-03-01
       6 | ZXXXXXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX   | 2001-03-01
       5 | YXXXXXXX_XXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX | 2001-03-01
       4 | XXXX_XXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX     | 2001-03-01
       3 | WXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX         | 2001-03-01
       2 | VXXXXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX      | 2001-03-01
       1 | UXXXXX_XXXXXX_XXXXXXXX_XXXXX_XXXX_XXXXXX  | 2001-03-01
(10 rows)

I can send a complete dump (9K) of the database on request


Sample Code
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))
;

No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> The probable cause is a Buffer Leak

Those notices aren't causes of anything, but they might be another
side effect of the real problem.

> I can send a complete dump (9K) of the database on request

Please.

            regards, tom lane

pgsql-bugs@postgresql.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