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 по дате отправления: