On Tuesday, March 17, 2015, Воронин Дмитрий <
carriingfate92@yandex.ru> wrote:
> 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:
As I note below, you don't get to choose; you just say "CREATE TEMP TABLE schemaless_name"
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
Schemas are not global and so can vary between databases.
You do not specify the schema in which temp tables are created. The system auto-assigns them, and also creates them based on need.
Temporary objects only survive for the life of the session creating them.
Empty temp schemas are ugly but aside from ignoring/hiding them from your viewer there isn't much worth doing. The system will just recreate them if you drop them manually.
It will create numbers potentially up to the number of simultaneous connections you allow. It my have affinity but that is an implementation detail you shouldn't care about.
David J.