CODEWITHSUNDEEP
postgresqlnpgsql
mdmnd18
mdmnd18

Reputation: 51

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?

Upvotes: 1

Views: 1073

Answers (1)

Schwern
Schwern

Reputation: 165536

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

Upvotes: 3

Related Questions