SELECT on VIEW returns wrong result, Buffer Leak - Mailing list pgsql-bugs

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