PostgreSQL临时表的范围是什么? [英] What is the scope of a PostgreSQL Temp Table?

问题描述

我已经在Google上搜索了很多，而且我的阅读理解也相当不错，但是我不知道该脚本是否可以在我的postgres/postgis框中的多个线程中工作.这是代码:

I have googled quite a bit, and I have fairly decent reading comprehension, but I don't understand if this script will work in multiple threads on my postgres/postgis box. Here is the code:

Do
$do$
DECLARE
    x RECORD;
    b int;       
    begin
    create temp table geoms (id serial, geom geometry) on commit drop;

    for x in select id,geom from asdf loop   

        truncate table geoms;
        insert into geoms (geom) select someGeomfield from sometable where st_intersects(somegeomfield,x.geom);

        ----do something with the records in geoms here...and insert that data somewhere else

    end loop;
end;
$do$

因此，如果我在多个从Java调用的客户端中运行此程序，geoms临时表的范围会引起问题吗?如果是这样，则在PostGres中解决此问题的任何想法都将有所帮助.

So, if I run this in more than one client, called from Java, will the scope of the geoms temp table cause problems? If so, any ideas for a solution to this in PostGres would be helpful.

谢谢

推荐答案

您会遇到一个细微的陷阱，这就是为什么我还没有准备好宣布它为安全"是因为作用域是针对每个会话的，所以人们通常会忘记删除表(因此它们会在断开连接时掉线).

One subtle trap you will run into though, which is why I am not quite ready to declare it "safe" is that the scope is per session, but people often forget to drop the tables (so they drop on disconnect).

我认为，如果函数完成后不需要临时表以将其显式删除后，您的境况会好得多.这将防止在同一事务中尝试两次运行该功能而引起的问题. (提交后，您将放弃)

I think you are much better off if you don't need the temp table after your function to drop it explicitly after you are done with it. This will prevent issues that arise from trying to run the function twice in the same transaction. (On commit you are dropping)

这篇关于PostgreSQL临时表的范围是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！