Can't drop temp table in Postgres function: "being used by active queries in this session"

Question

It is expected to now take in a table called waypoints and follow through the function body.

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 do I go about rectifying this issue?

Why do you use temp tables? Are the temp tables supposed to be referenced outside the function also? Also: your version of Postgres is *always* part of the necessary details. — Erwin Brandstetter, Jul 02 '15 at 15:39
@ErwinBrandstetter because they are all just intermediates to get my final result. I'm using 9.3, I'll add it to the question. — Louis93, Jul 02 '15 at 15:53
Also, are reads and writes from CTEs as fast as reads and writes from tables? Because when I made views, everytime I accesses the view, the view would be recalculated. — Louis93, Jul 02 '15 at 15:54
"take in a table called `waypoints`" .. makes no sense. If you know the table is called `waypoints`, you don't need a parameter. If you need a parameter for the table, you need dynamic SQL and things get more complicated. Don't confuse parameter name, parameter value and data type. — Erwin Brandstetter, Jul 02 '15 at 16:12

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

The error message is rather obvious, you cannot drop a temp table while it is being used.

You might be able to avoid the problem by adding ON COMMIT DROP:

Temporary table and loops in a function

However, this can probably be simpler. If you don't need all those temp tables to begin with (which I suspect), you can replace them all with CTEs (or most of them probably even with cheaper subqueries) and simplify to one big query. Can be 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;

Views are just storing a query ("the recipe"), not the actual resulting values ("the soup").

It's typically cheaper to use CTEs instead of creating temp tables.
Derived tables in queries, sorted by their typical overall performance (exceptions for special cases involving indexes). From slow to fast:

CREATE TABLE
CREATE UNLOGGED TABLE
CREATE TEMP TABLE
CTE
subquery

UNION would try to fold duplicate rows. Typically, people really want UNION ALL, which just appends rows. Faster and does not try to remove dupes.

edited May 23 '17 at 11:43

Community

1
1

answered Jul 02 '15 at 15:52

Erwin Brandstetter

605,456
145
1,078
1,228

Awesome @Erwin. I see no RETURN(s) there, are they not necessary? – Louis93 Jul 02 '15 at 16:00
@Louis93: RETURN would be necessary in plpgsql, I switched to `LANGUAGE sql` for the simplified case. You can use plpgsql as well. – Erwin Brandstetter Jul 02 '15 at 16:03
Hello - hopefully this is the last blocker. So in the `results` CTE, I invoke a function like so: `pgr_ksp('select * FROM b_graph')` to form `results` However, I get this back: `relation "b_graph" does not exist`. Does b_graph have to be a table? – Louis93 Jul 02 '15 at 16:15
Also, I'm really grateful for your in-depth responses. I'm a little out of depth here and the pointers are really helpful. – Louis93 Jul 02 '15 at 16:17
If you are executing dynamic SQL, then yes, `b_graph` has to be a table. But what I see here is just horrible. Wide open for SQL injection .... – Erwin Brandstetter Jul 02 '15 at 16:18
How would you refactor? – Louis93 Jul 02 '15 at 16:19
@Louis93: Read and understand first: http://bobby-tables.com/, http://dba.stackexchange.com/a/49718/3684, http://stackoverflow.com/a/10711349/939860 – Erwin Brandstetter Jul 02 '15 at 16:21

Can't drop temp table in Postgres function: "being used by active queries in this session"

1 Answers1

Linked