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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

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