1

I want to create a TEMPORARY TABLE in a Pl/pgSQL function because I want to index it before doing some process. The fact that any concurrent call to the function will try to reuse the same table seems to be a problem.

e.g. A first call to the function creates and uses a temporary table named "test" with data depending on the function parameters. A second concurrent call tries also to create and use the temporary table with the same name but with different data...

The doc says

"Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction"

I guess the problem would not exist if temporary tables created with the "ON COMMIT DROP" option would only be visible to the current transaction. Is this the case?

If not, how to automatically create independent tables from two different function calls?

I could probably try to create a temporary name and check if a table with this name already exists but that seems like a lot of management to me...

Pierre
  • 270
  • 2
  • 11
  • I'm sure you have a reason for doing it, but creating an indexed temporary table in a function that could be called concurrently just sounds awful, surely there must be a better way to accomplish what you want? – 404 Sep 27 '17 at 16:32

2 Answers2

2

Temporary tables are visible only in the current session. Concurrent processes do not see each other's temporary tables even when they share the same names. Per the documentation:

PostgreSQL requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes (...)

klin
  • 112,967
  • 15
  • 204
  • 232
  • What's a session? Are two pgAdmin query windows two different sessions? – Pierre Sep 27 '17 at 21:07
  • 1
    When a client establishes a connection to a database, a separate backend process is started which handles subsequent commands sent from the client. When the client closes the connection, the backend process terminates. This is a session. Two query windows in PgAdmin involves two separate sessions. – klin Sep 27 '17 at 21:40
2

Temporary tables of distinct sessions cannot conflict because each session has a dedicated temporary schema, only visible to the current session.

In current Postgres only one transaction runs inside the same session at a time. So only two successive calls in the same session can see the same temporary objects. ON COMMIT DROP, like you found, limits the lifespan of temp tables to the current transaction, avoiding conflicts with other transactions.

If you (can) have temp tables that don't die with the transaction (like if you want to keep using some of those tables after the end of the current transaction), then an alternative approach would be to truncate instead of create if the temp table already exists - which is a bit cheaper, too.

Wrapped into a function:

CREATE OR REPLACE FUNCTION f_create_or_trunc_temp_table(_tbl text, OUT _result "char") AS
$func$
BEGIN
   SELECT INTO _result  relkind
   FROM   pg_catalog.pg_class
   WHERE  relnamespace = pg_my_temp_schema()          -- only temp objects!
   AND    relname = _tbl;

   IF NOT FOUND THEN                                  -- not found
      EXECUTE format('CREATE TEMP TABLE %I(id int)', _tbl);

   ELSIF _result = 'r' THEN                           -- table exists
      EXECUTE format('TRUNCATE TABLE %I', _tbl);      -- assuming identical table definition

   ELSE                                               -- other temp object occupies name
      RAISE EXCEPTION 'Other temp object of type >>%<< occupies name >>%<<', _result, _tbl;
      -- or do nothing, return more info or raise a warning / notice instead of an exception
   END IF;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT f_create_or_trunc_temp_table('my_tbl');

This assumes identical table definition if the table exists. You might do more and also return more informative messages, etc. This is just the basic concept.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • The function seems pointless as you can always `create temporary table if not exists ...` Whether it should be truncated or not depends on particular needs, truncating an empty temporary table costs nothing. – klin Sep 29 '17 at 10:02
  • @klin: The lookup in my function is similar to the what `IF NOT EXISTS` does, but the point is to cover 3 possible cases with a single check. Truncating an empty table costs *something*, even if very little. More importantly, attempting to truncate anything but a table raises an exception. May or may not be what's needed. This function can return or message something instead. I added a note. – Erwin Brandstetter Sep 29 '17 at 13:31
  • Your function does not produce any effect that can not be achieved without it, using simple Postgres commands. If a temporary table can not be created for any reason, Postgres will raise an error, just like your function. Due to the use of dynamic SQL, the function is much more expensive than a possible truncating of an empty table. [*Entities must not be multiplied beyond necessity.*](https://en.wikipedia.org/wiki/Occam%27s_razor) – klin Sep 29 '17 at 13:58
  • You said that the first time. Doesn't seem like you read my reply. – Erwin Brandstetter Sep 29 '17 at 22:56
  • I read each of your comments with due attention. However, I try to avoid writing about the obvious. You have added a bit misterious note that the function raises an exception but it do not have to. In no way does it concern the essence of the fact that the function is unnecessary. In any piece of plpgsql code you can catch an exception and do something else instead. You do not need querying `pg_class` or executing dynamic SQL for this. – klin Sep 30 '17 at 06:58
  • @klin: You also do not need to add expensive exception handling to all involved functions if you do not raise an exception to begin with. – Erwin Brandstetter Sep 30 '17 at 14:49
  • Exception handling is expensive when used as a method of processing data (e.g. the old implementation of upsert described in the documentation). In our case an exception is really exceptional - the user must have created a temporary object other than a table and now is trying to create a table with the same name. Interestingly, it is a total mindlessness or charming distraction, but the situation is absolutely unique. Personally, I would not bother with it at all. – klin Sep 30 '17 at 20:20
  • 1
    @klin: Expensive no matter how often an exception actually occurs. Please read the manual about the cost of exception handling: `Tip: A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.` I am done here. – Erwin Brandstetter Sep 30 '17 at 23:14
  • I guess you misunderstood me, maybe I wrote this vaguely. The question is: why write a function only in order to specifically handle such bizarre user behavior. It would never have occurred to me to catch such exceptions. – klin Oct 01 '17 at 00:33