Cannot drop temp table in Postgres function: "used by active queries in this session"
We are now expected to take the table named waypoints
and walk through the body of the function.
drop function if exists everything(waypoints);
create function everything(waypoints) RETURNS TABLE(node int, xy text[]) as $$
BEGIN
drop table if exists bbox;
create temporary table bbox(...);
insert into bbox
select ... from waypoints;
drop table if exists b_spaces;
create temporary table b_spaces(
...
);
insert into b_spaces
select ...
drop table if exists b_graph; -- Line the error flags.
create temporary table b_graph(
...
);
insert into b_graph
select ...
drop table if exists local_green;
create temporary table local_green(
...
);
insert into local_green
...
with aug_temp as (
select ...
)
insert into b_graph(source, target, cost) (
(select ... from aug_temp)
UNION
(select ... from aug_temp)
);
return query
with
results as (
select id1, ... from b_graph -- The relation being complained about.
),
pkg as (
select loc, ...
)
select id1, array_agg(loc)
from pkg
group by id1;
return;
END;
$$ LANGUAGE plpgsql;
This returns cannot DROP TABLE b_graph because it is being used by active queries in this session
How can I fix this problem?
source to share
The error message is pretty obvious, you cannot drop the temporary table while you are using it.
You might be able to avoid the problem by adding ON COMMIT DROP
:
However, it may be easier. If you don't need all those temporary tables that I suspect you can replace them with all CTEs (or most of them, probably even with cheaper subqueries) and simplify one big query. Maybe plpgsql or just SQL:
CREATE FUNCTION everything(waypoints)
RETURNS TABLE(node int, xy text[]) AS
$func$
WITH bbox AS (SELECT ... FROM waypoints) -- not the fct. parameter!
, b_spaces AS (SELECT ... )
, b_graph AS (SELECT ... )
, local_green AS (SELECT ... )
, aug_temp AS (SELECT ... )
, b_graph2(source, target, cost) AS (
SELECT ... FROM b_graph
UNION ALL -- guessing you really want UNION ALL
SELECT ... FROM aug_temp
UNION ALL
SELECT ... FROM aug_temp
)
, results AS (SELECT id1, ... FROM b_graph2)
, pkg AS (SELECT loc, ... )
SELECT id1, array_agg(loc)
FROM pkg
GROUP BY id1
$func$ LANGUAGE sql;
The views simply store the query ("recipe"), not the actual result values ("soup").
It is generally cheaper to use CTEs instead of creating temporary tables.
Derived tables in queries , sorted by their typical overall performance (exceptions for special cases with indexes). Slow to Fast:
CREATE TABLE
CREATE UNLOGGED TABLE
CREATE TEMP TABLE
CTE
subquery
UNION
will try to flush duplicate lines. Usually people really want UNION ALL
one that just adds lines. Faster and doesn't try to remove trickery.
source to share