CODEWITHSUNDEEP
postgresqlplpgsql
Pierre
Pierre

Reputation: 294

Create non conflicting temporary tables in a Pl/pgSQL function

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...

Upvotes: 3

Views: 8610

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

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:

Upvotes: 3

klin
klin

Reputation: 121604

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 (...)

Upvotes: 2

Related Questions