1

I have tried to cancel the hung query after a pg_routing query, but cannot. I have encountered this problem previously with pg_routing, but ended up really messing up the local database and had to delete and redo, which I don't want to do every time this happens when testing a query locally.

The following is the pgr_dijkstra function I am running:

CREATE OR REPLACE FUNCTION find_shortest_routes_sk()
    RETURNS TABLE (
        source_vertex bigint,
        destination_vertex bigint,
        source_id int,
        destination_id int,
        cost float,
        sequence int[],
        edges bigint[],
        nodes bigint[],
        geom geometry(LineString, 26913) 
    )
AS
$$
DECLARE
    _source int;
    _destinations int[];
    _start timestamptz := clock_timestamp();
    _stop timestamptz;

BEGIN  

    RAISE NOTICE '% - start time', substr(cast(clock_timestamp() AS varchar), 12, 8);

     _destinations := ARRAY(SELECT DISTINCT tv FROM public.rd_vertices_lookup);
    RAISE NOTICE 'destination vertices: %', _destinations;

    FOR _source IN
        SELECT DISTINCT(fv) FROM public.rd_vertices_lookup
    LOOP

        RAISE NOTICE 'start vertex: %', _source;

        RETURN QUERY SELECT  
            _source::bigint AS source_vertex,
            MIN(r.end_vid)::bigint AS destination_vertex,
            vl.fid::int AS source_id,
            vl.tid::int AS destination_id,
            SUM(r.cost) AS cost,
            array_agg(r.path_seq) AS sequence,
            array_agg(r.edge) AS edges,
            array_agg(r.node) AS nodes,
            ST_Collect(ws.geom) AS geom
        FROM pgr_dijkstra(
            'SELECT id, source, target, cost, reverse_cost FROM public.rd_strms',
            _source, 
            _destinations, 
            directed := true
        ) AS r
        JOIN public.rd_strms AS ws
        ON r.edge = ws.id
        JOIN public.rd_vertices_lookup AS vl
        ON _source = vl.fv
        AND r.end_vid = vl.tv 
        GROUP BY end_vid, vl.fid, vl.tid
        ORDER BY cost
        LIMIT 1;
        
    END LOOP;

    _stop = clock_timestamp();
    RAISE NOTICE '% - completed in % seconds', substr(cast(clock_timestamp() AS varchar), 12, 8), to_seconds((_stop - _start)::text);

DROP TABLE IF EXISTS public.sk_shortest_routes_rd;
CREATE TABLE public.sk_shortest_routes_rd 
AS SELECT 
    rd_result.source_vertex,
    rd_result.destination_vertex,
    rd_result.source_id,
    rd_result.destination_id,
    rd_result.cost,
    rd_result.sequence,
    rd_result.edges,
    rd_result.nodes,
    ST_CollectionExtract(rd_result.geom) AS geom
FROM find_shortest_routes_sk() AS rd_result;

ALTER TABLE public.sk_shortest_routes_rd ADD COLUMN id SERIAL PRIMARY KEY;

END;
$$
LANGUAGE plpgsql;

I have tried the following to retrieve active queries and nothing comes up:

SELECT pid, query_start, query
FROM pg_stat_activity
WHERE state = 'active'
AND pid <> pg_backend_pid();

I believe there is an active query that wasn't terminating because I received the message after running my query a 2nd time:

ERROR:  cannot DROP TABLE "sk_shortest_routes_rd" because it is being used by active queries in this session

CONTEXT:  SQL statement "DROP TABLE IF EXISTS public.sk_shortest_routes_rd" PL/pgSQL function find_shortest_routes_sk() line 51 at SQL statement SQL statement "CREATE TABLE public.sk_shortest_routes_rd   AS SELECT   rd_result.source_vertex,    
            rd_result.destination_vertex,   
            rd_result.source_id,    
            rd_result.destination_id,   
            rd_result.cost,     
            rd_result.sequence,     
            rd_result.edges,    
            rd_result.nodes,    
            ST_CollectionExtract(rd_result.geom) AS geom  FROM find_shortest_routes_sk() AS rd_result"  PL/pgSQL function find_shortest_routes_sk() line 52 at SQL statement  SQL state: 55006
0

1 Answer 1

1

The problem was not that I had a query running, but that my function, as expected, was recursively creating the table public.sk_shortest_routes_rd becasue it was inside the function call.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.