Re: Question about TEMP tables

On Tuesday, March 17, 2015, Воронин Дмитрий <carriingfate92(at)yandex(dot)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.