2

Per the documentation:

ON COMMIT DROP: The temporary table will be dropped at the end of the current transaction block.

However, temporary tables are unique per session, not per thread: see here

My question:

If a temp table is marked WITH ON COMMIT DROP, how will PG handle multiple threads accessing that table as regards the fact any thread could commit, but the table couldn't be dropped as it's in use by other threads.

Edit: AFAIK, multiple transactions can run within one session. If this is the case, more than one transaction could access a function that has a temp table, hence my question.

IamIC
  • 17,747
  • 20
  • 91
  • 154
  • Do the *threads* share the same connection/session? – wildplasser Oct 18 '17 at 20:48
  • This is a bit out of my area, but from what I read, if one is using connection pooling, then multiple "threads" would share the same connection and presumably session. – IamIC Oct 18 '17 at 20:50
  • Don't complicate things by throwing in connection pooling. (or: rephrase the question) – wildplasser Oct 18 '17 at 20:53
  • AFAIK, a session can run multiple transactions. Put pooling aside. – IamIC Oct 18 '17 at 20:56
  • It's also possible I misunderstood the data in the link I read, and "thread" simply refers to a function call from any point within a transaction. If that's the case, my question is answered. – IamIC Oct 18 '17 at 21:02

1 Answers1

3

Postgres (server) knows nothing about client threads. It sees a session as a consecutive series of commands grouped in transactions. From the server's point of view concurrency inside a session is impossible.

A client application may be multi-threaded and threads can use the same connection. An application developer is responsible for ensuring that threads do not compete with each other for access to server resources. From the documentation:

One thread restriction is that no two threads attempt to manipulate the same PGconn object at the same time. In particular, you cannot issue concurrent commands from different threads through the same connection object. (If you need to run concurrent commands, use multiple connections.)

klin
  • 112,967
  • 15
  • 204
  • 232
  • That makes sense. What I don't get is Erwin Brandstetter's answer in this link: https://stackoverflow.com/questions/10596896/postgresql-thread-safety-for-temporary-tables. Why go to the trouble of assigning unique IDs to a "thread"? – IamIC Oct 18 '17 at 22:06
  • In my understanding this is a description of a special case of multithreading on the client side. Erwin writes about one of possible ways of sharing a table by multiple threads. – klin Oct 18 '17 at 22:17
  • So the bottom line is, on the server, without any odd clientside threading (breaking the rules), there is no need to care whatsoever, right? – IamIC Oct 18 '17 at 22:22
  • Exactly. Application developers must use their methods in a multithreaded environment so that the server sees one linear session. – klin Oct 18 '17 at 22:27