Search code examples
postgresqlnpgsql

Do Postgres temporary tables exist between multiple connections open at once?

Say I open a new npgsqlconnection and create a new temporary table temp1, and then open another new connection. From my understanding a temporary table is only available to the session that opened it, and two open connections shouldn't share the same session. Here the connection strings are identical, and I tried turning pooling off, but that didn't change anything. The pseudo-code is:

var conn1 = new NpgsqlConnection(MyConnectionString)
var conn2 = new NpgsqlConnection(MyConnectionString)
conn1.Open()
conn2.Open()
conn1.Execute("CREATE TEMP TABLE temp1(idx int)")

If I execute the query SELECT COUNT(*) FROM pg_tables WHERE tablename = 'temp1' for both connections this query returns 1. Why would conn2 be able to access the temporary table created on conn1? Is there anyway to prevent this?

Solution

  • Why would conn2 be able to access the temporary table created on conn1?

    It can't.

    The other connections can see that there is a table via the system catalog, but they cannot access it.

    -- Connection 1
test=# SELECT schemaname FROM pg_tables WHERE tablename = 'temp1';
 schemaname 
------------
 pg_temp_3
(1 row)

-- Connection 2
test=# select * from pg_temp_3.temp1;
ERROR:  cannot access temporary tables of other sessions