wrong optimization ( postgres 8.0.3 )

From: Gaetano Mendola <mendola(at)bigfoot(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Subject: wrong optimization ( postgres 8.0.3 )
Date: 2005-10-05 15:20:30
Message-ID: 4343EF3E.7070601@bigfoot.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all,
take a look at this simple function and view:

CREATE OR REPLACE FUNCTION sp_connected_test ( INTEGER )
RETURNS BOOLEAN AS'
DECLARE
a_id_user ALIAS FOR $1;
BEGIN
PERFORM *
FROM v_current_connection
WHERE id_user = a_id_user;

IF NOT FOUND THEN
RETURN FALSE;
END IF;

RETURN TRUE;

END;
' LANGUAGE 'plpgsql';

CREATE VIEW v_current_connection_test
AS SELECT ul.id_user, cc.connected
FROM current_connection cc,
user_login ul
WHERE cc.id_user = ul.id_user AND
connected = TRUE;

SELECT * FROM v_current_connection_test WHERE sp_connected_test(id_user) = FALSE;

this line shall produce no row, but randomly does.

If I put a RAISE NOTICE before RETURN NULL with the id_user I notice that
the function is called on records present on user_login but discarged because
the join with current_connectin have connected = FALSE!

I can work_around the problem rewriting the view:

CREATE VIEW v_current_connection_test
AS SELECT cc.id_user, cc.connected
FROM current_connection cc,
user_login ul
WHERE cc.id_user = ul.id_user AND
connected = TRUE;

Regards
Gaetano Mendola

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Luke Lonergan 2005-10-05 15:24:07 Re: [HACKERS] A Better External Sort?
Previous Message Tom Lane 2005-10-05 15:08:27 Re: Fix for file names with spaces