CODEWITHSUNDEEP
plpgsqlpostgresql-9.3
Mark Giaconia
Mark Giaconia

Reputation: 3953

What is the scope of a PostgreSQL Temp Table?

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$

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.

Thanks

Upvotes: 10

Views: 5459

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

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)

Upvotes: 5

Pavel Stehule
Pavel Stehule

Reputation: 45930

Temp tables in PostgreSQL (or Postgres) (PostGres doesn't exists) are local only and related to session where they are created. So no other sessions (clients) can see temp tables from other session. Both (schema and data) are invisible for others. Your code is safe.

Upvotes: 4

Related Questions