无法在Postgres函数中删除临时表：“此会话中的活动查询正在使用” [英] Can't drop temp table in Postgres function: "being used by active queries in this session"

问题描述

现在预计将使用一个名为 waypoints 的表，并依次浏览函数主体。

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;

这将返回无法删除表b_graph，因为它被活动查询中的本届会议

我该如何解决这个问题？

How do I go about rectifying this issue?

推荐答案

错误消息非常明显，您无法在使用临时表时删除它。

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

您也许可以避免该问题。通过添加 ON COMMIT DROP ：

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

• 函数中的临时表和循环

• Temporary table and loops in a function

但是，这可能更简单。如果您不需要所有临时表（我怀疑），则可以将它们全部替换为CTE（或大多数替换为更便宜的子查询），然后简化为一个大查询。可以是plpgsql或仅是SQL：

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").

使用CTE而不是创建临时表通常更便宜。

查询中的派生表，按其总体表现排序（涉及索引的特殊情况除外）。从慢到快：

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 会尝试折叠重复的行。通常，人们真的想要 UNION ALL ，它只是追加行。更快，并且不会尝试删除重复项。

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.

这篇关于无法在Postgres函数中删除临时表：“此会话中的活动查询正在使用”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！