Geek Answers Handbook

Postgresql function gets stuck in loop

The function gets stuck just before the loop.

select * from scm_main.fn_connection_stations(1219646)

The message "Start ..." will appear, but the message "... end" will not be printed.

CREATE OR REPLACE FUNCTION     
scm_main.fn_connection_stations(var_connection_id bigint)
RETURNS SETOF scm_main.typ_connection_stations AS
$BODY$ DECLARE 
    var_affected                 INTEGER DEFAULT 0; 
    var_row                      scm_main.typ_connection_stations%ROWTYPE; 
BEGIN 
    RAISE NOTICE 'Start...'; 
    FOR var_row IN 
    SELECT DISTINCT v.vbvdata_station_id
    FROM scm_main.tbl_vbvdata AS v 
    INNER JOIN scm_main.tbl_packet AS p ON (v.vbvdata_packet_id = 
    p.packet_id and p.packet_connection_id = var_connection_id) 
    --WHERE v.vbvdata_packet_id IN 
    --( SELECT packet_id FROM scm_main.tbl_packet AS o_p WHERE 
    o_p.packet_connection_id = var_connection_id) 
    LOOP 
        RETURN NEXT var_row; 
    END LOOP; 
    RAISE NOTICE '...End'; 
    RETURN; 
END 
$BODY$
  LANGUAGE plpgsql STABLE STRICT
  COST 100
  ROWS 1000;
ALTER FUNCTION scm_main.fn_connection_stations(bigint)
  OWNER TO postgres;

The request itself is very simple and starts when called directly as follows:

SELECT DISTINCT v.vbvdata_station_id 
FROM scm_main.tbl_vbvdata AS v 
INNER JOIN scm_main.tbl_packet AS p ON (v.vbvdata_packet_id =  
p.packet_id and p.packet_connection_id = 1219646)

I think the problem should be locked for tables. But I absolutely do not understand what is the reason and what could be the solution.

Some time ago, the commented part worked. But after a while the same problem arose. I solved this by modifying the query and replacing the condition (cascading query) with the inner join. But this time, none of them work!

Update

I earned this again with a silly change:

INNER JOIN scm_main.tbl_packet AS p ON (v.vbvdata_packet_id = p.packet_id 
  and p.packet_connection_id = var_connection_id)`

has been changed to:

INNER JOIN scm_main.tbl_packet AS p ON (v.vbvdata_packet_id = p.packet_id)
  where p.packet_connection_id in (select var_connection_id)

and worked perfectly.

Another interesting point is that even the change below does not work and still freezes:

INNER JOIN scm_main.tbl_packet AS p ON (v.vbvdata_packet_id = p.packet_id) 
  where p.packet_connection_id = var_connection_id

. , !

+4
1

, , , "", - .

, , , , . , Postgres , ( ) . , .

- ; Postgres , . ANALYSE , - autovacuum ( ).

( , ). , ALTER TABLE ... SET STATISTICS ( ANALYSE). , 500 () .

, , EXECUTE.

+1

More articles:


All Articles