What is the scope of the PostgreSQL Temp Table?

Question

I have quite a few problems googled and I have a pretty decent reading comprehension, but I don't understand if this script will work in multiple threads in my postgres / postgis. 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 there be a problem with the temp geom table? If so, any ideas for solving this in PostGres will be helpful.

thanks

Answer 1

One subtle trap that you will encounter, although not quite ready to claim that it is “safe,” is that the scope is per session, but people often forget about dropping tables (so they are turned off).

I think you are much better off if you do not need a temporary table after your function automatically exits it after you finish with it. This will prevent problems associated with trying to execute a function twice in a single transaction. (On commit, you drop)

Answer 2

Temp tables in PostgreSQL (or Postgres) (PostGres does not exist) are only local and are associated with the session where they are created. Thus, no other sessions (clients) can see temporary tables from another session. Both (diagram and data) are invisible to others. Your code is safe.