Thread: Question about TEMP tables

<div>Hello, all.<br /><br />We can create temp namespaces and temp objects that contains it. So, for example, temp
tablewill be create at pg_temp_N (N - backendID). But afrer cluster init we have pg_temp_1 and pg_toast_temp_1
namespaceswith OIDs 11333 and 11334. Those namespaces are <span lang="en"><span>visible from any cluster database, but
wecannot create any temp objects (please, correct me). <br /><br />So, how can we use those namespaces and
</span></span><spanlang="en"><span>what are needed for?</span></span></div><div> </div><div><span lang="en"><span>Thank
you.</span></span></div><div> </div><div>-- </div><div>Bestregards, Dmitry Voronin</div><div> </div> 

>  Make sure to show your full command(s) and the full, exact text of any errors.

OK, I use PostgreSQL version 9.4.1.

I create cluster 'main' and connect to it. After cluster init we have those shemas:

postgres=# SELECT nspname FROM pg_namespace ;     nspname       
--------------------pg_toastpg_temp_1pg_toast_temp_1pg_catalogpublicinformation_schema
(6 rows)

Owner of those schemas is postgres (OID 10). 

Now we try to create TEMP TABLE, for example:

postgres=# CREATE TEMP TABLE temptable();
CREATE TABLE

Show namespaces:

postgres=# SELECT nspname FROM pg_namespace ;     nspname       
--------------------pg_toastpg_temp_1pg_toast_temp_1pg_catalogpublicinformation_schemapg_temp_2pg_toast_temp_2
(8 rows)

Now we create a new database testdb and connect to it:

CREATE DATABASE testdb;
\c testdb

SHOW namespaces of testdb (we already connect to it):

testdb=# SELECT nspname FROM pg_namespace ;     nspname       
--------------------pg_toastpg_temp_1pg_toast_temp_1pg_catalogpublicinformation_schema
(6 rows)

OK, namespaces pg_temp_2 and pg_toast_temp_2 are not visible. But pg_temp_1 and pg_toast_temp_1 are visible. WHY?

If we create some temp objects in testdb Postgres wiil create namespaces pg_temp_3 and pg_toast_temp_3.

Try to create temp table at pg_temp_1:

CREATE TEMP TABLE pg_temp_1.temptable();
ERROR: cannot create relations in temporary schemas of other sessions

I catch those error if I create some TEMP objects in postgres database. 

-- 
Best regards, Dmitry Voronin

>  Make sure to show your full command(s) and the full, exact text of any errors.

OK, I use PostgreSQL version 9.4.1.

I create cluster 'main' and connect to it. After cluster init we have those shemas:

postgres=# SELECT nspname FROM pg_namespace ;
      nspname
--------------------
 pg_toast
 pg_temp_1
 pg_toast_temp_1
 pg_catalog
 public
 information_schema
(6 rows)

Owner of those schemas is postgres (OID 10).

Now we try to create TEMP TABLE, for example:

postgres=# CREATE TEMP TABLE temptable();
CREATE TABLE

Show namespaces:

postgres=# SELECT nspname FROM pg_namespace ;
      nspname
--------------------
 pg_toast
 pg_temp_1
 pg_toast_temp_1
 pg_catalog
 public
 information_schema
 pg_temp_2
 pg_toast_temp_2
(8 rows)

Now we create a new database testdb and connect to it:

CREATE DATABASE testdb;
\c testdb

SHOW namespaces of testdb (we already connect to it):

testdb=# SELECT nspname FROM pg_namespace ;
      nspname
--------------------
 pg_toast
 pg_temp_1
 pg_toast_temp_1
 pg_catalog
 public
 information_schema
(6 rows)

OK, namespaces pg_temp_2 and pg_toast_temp_2 are not visible. But pg_temp_1 and pg_toast_temp_1 are visible. WHY?

If we create some temp objects in testdb Postgres wiil create namespaces pg_temp_3 and pg_toast_temp_3.

Try to create temp table at pg_temp_1:

CREATE TEMP TABLE pg_temp_1.temptable();
ERROR: cannot create relations in temporary schemas of other sessions

I catch those error if I create some TEMP objects in postgres database.

-- 
Best regards, Dmitry Voronin