Skip site navigation (1) Skip section navigation (2)

SELECT on VIEW returns wrong result, Buffer Leak

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: SELECT on VIEW returns wrong result, Buffer Leak
Date: 2001-06-07 15:18:03
Message-ID: 200106071518.f57FI3928575@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Ulrich Dhner (Ulrich(dot)Doehner(at)suse(dot)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


Responses

pgsql-bugs by date

Next:From: Michael NguyenDate: 2001-06-07 19:12:56
Subject: column check constraint
Previous:From: Victor WagnerDate: 2001-06-07 13:54:12
Subject: Problem with locale on Solaris 8.0 intel

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group