SELECT on VIEW returns wrong result, Buffer Leak
От | pgsql-bugs@postgresql.org |
---|---|
Тема | SELECT on VIEW returns wrong result, Buffer Leak |
Дата | |
Msg-id | 200106071518.f57FI3928575@hub.org обсуждение исходный текст |
Ответы |
Re: SELECT on VIEW returns wrong result, Buffer Leak
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: SELECT on VIEW returns wrong result, Buffer Leak (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: pgsql-bugs@postgresql.orgДата:
Сообщение: ERROR: EXECUTE of SELECT ... INTO is not implemented yet
Следующее
От: pgsql-bugs@postgresql.orgДата:
Сообщение: Apparent deadlock for simultaneous sequential scans