wrong optimization ( postgres 8.0.3 )

Поиск
Список
Период
Сортировка
От Gaetano Mendola
Тема wrong optimization ( postgres 8.0.3 )
Дата
Msg-id 4343EF3E.7070601@bigfoot.com
обсуждение исходный текст
Список 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












В списке pgsql-hackers по дате отправления:

Предыдущее
От: Marc Munro
Дата:
Сообщение: Re: Announcing Veil
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Interesting optimizer's supposition in 8.1