Trying to understand the scope of a Temp Table in a Postgres query

Question

In this SO Answer about PostgreSQL and Temp tables, they say this about temp tables:

It is only visible within the current session and dies at the end of it. When created with ON COMMIT DROP it dies at the end of the transaction.

given a sample sql like this:

CREATE TEMP TABLE temp1 AS
SELECT dataid
     , register_type
     , timestamp_localtime
     , read_value_avg
FROM   rawdata.egauge
WHERE  register_type LIKE '%gen%'
ORDER  BY dataid, timestamp_localtime;

Does this mean that I could have a query that is ran multiple times at the same time and each query that is ran .. which would have it's own scope .. would have it's own copy/version of it's temp table? So query_1's temp table wouldn't mess with query_2's temp table, if both are running "at the same time".

For example:

20 people all request the same web page "at the same time". The webserver then executes the same query for each request, which means 20 queries are ran at the same time. (of course the data might be different, per request .. etc).

Is my understanding of this correct? Is there way I can test this using pgAdmin4?

@LaurenzAlbe cheers! but if i open 2x query tools.. i can't "run them at the same time" to check this. is there a trick? like .. can i put a DELAY in the query for 5 secs .. and then tab over to the other window and run the 2nd query there? — Learning AWS and PostgreSQL, May 03 '23 at 16:07
`SELECT col, pg_sleep(1) FROM temp_table;` will take one second per row returned. — Laurenz Albe, May 03 '23 at 16:45
Instead if depending on a delay you could do the following: open two query tools; create temp tables using the same name and without `ON COMMIT DROP`, insert different values in each, ensure that the transactions are committed, query the tables to confirm that neither sees the values from the other. — JohnH, May 03 '23 at 16:51

Adrian Klaver · Accepted Answer · 2023-05-03T17:40:22.593

Per docs Create Table

Temporary tables exist in a special schema, so a schema name cannot be given when creating a temporary table.

Using psql

\dnS pg_temp_*
   List of schemas
   Name    |  Owner   
-----------+----------
 pg_temp_3 | postgres
 pg_temp_4 | postgres

This may not exactly what be what you see. The point is Postgres may have some of the temporary schemas already created in anticipation of having temporary tables being created in them. Otherwise it will create them on demand.

--Session 1
create temp table temp(id integer);
CREATE TABLE

select * from pg_temp_3.temp;
 id 
----
(0 rows)

--Session 2
create temp table temp(id integer);
CREATE TABLE

 select * from pg_temp_4.temp ;
 id 
----
(0 rows)

--Session 1
insert into temp values (1);
INSERT 0 1
select * from pg_temp_3.temp;
 id 
----
  1
(1 row)

--Session 2
insert into temp values (2);
INSERT 0 1
select * from pg_temp_4.temp ;
 id 
----
  2

So as you see each temporary table has it's own namespace(schema) which is how each session/transaction can be operating on its own version of the table. To prove it:

--Session 2
select * from pg_temp_3.temp ;
ERROR:  cannot access temporary tables of other sessions

Also what happens if another session creates the table.

--Session 3
create temp table temp(id integer);

select * from pg_temp_5.temp ;
 id 
----
(0 rows)

insert into temp values (3);
INSERT 0 1
select * from pg_temp_5.temp ;
 id 
----
  3

Another pg_temp_* schema is created to hold it.

Love the answer - thank you! So even though the examples created a temp tabled called `temp` (in the scope of the query), behind the scenes it was given a unique name "`pg_temp_`" which is the _real_ table used and of course, it was shown that they are unique to the scope of the query. not global (in this scenario). Awesome! — Learning AWS and PostgreSQL, May 04 '23 at 21:19
@LearningPostgreSQL. Not quite. What happens is a [Schema](https://www.postgresql.org/docs/current/sql-createschema.html) is created as `pg_temp_ `. Then the temporary table is created in that `schema`. This makes its fully qualified name `pg_temp_.`. The server does the schema creation on it's own, that is why you cannot do something like `CREATE TEMP TABLE .temp_tbl ...`. — Adrian Klaver, May 04 '23 at 23:27

Trying to understand the scope of a Temp Table in a Postgres query

1 Answers1