Ulrich Döhner ([EMAIL PROTECTED]) 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
