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