Re: [HACKERS] Question about TEMP tables
On Tuesday, March 17, 2015, Воронин Дмитрий carriingfat...@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.
[HACKERS] Question about TEMP tables
Hello, all. We can create temp namespaces and temp objects that contains it. So, for example, temp table will be create at pg_temp_N (N - backendID). But afrer cluster init we have pg_temp_1 and pg_toast_temp_1 namespaces with OIDs 11333 and 11334. Those namespaces are visible from any cluster database, but we cannot create any temp objects (please, correct me). So, how can we use those namespaces and what are needed for? Thank you. -- Best regards, Dmitry Voronin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about TEMP tables
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Question about TEMP tables
Hello, all. We can create temp namespaces and temp objects that contains it. So, for example, temp table will be create at pg_temp_N (N - backendID). But afrer cluster init we have pg_temp_1 and pg_toast_temp_1 namespaces with OIDs 11333 and 11334. Those namespaces are visible from any cluster database, but we cannot create any temp objects (please, correct me). So, how can we use those namespaces and what are needed for? Thank you. -- Best regards, Dmitry Voronin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about TEMP tables
On 16 March 2015 at 16:31, Dmitry Voronin carriingfat...@yandex.ru wrote: Hello, all. We can create temp namespaces and temp objects that contains it. So, for example, temp table will be create at pg_temp_N (N - backendID). But afrer cluster init we have pg_temp_1 and pg_toast_temp_1 namespaces with OIDs 11333 and 11334. Those namespaces are visible from any cluster database, but we cannot create any temp objects (please, correct me). This is better suited to the pgsql-general or pgsql-admin mailing lists. Make sure to show your full command(s) and the full, exact text of any errors. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
[HACKERS] Question about TEMP tables
Hello, all.We can create temp namespaces and temp objects that contains it. So, for example, temp table will be create at pg_temp_N (N - backendID). But afrer cluster init we have pg_temp_1 and pg_toast_temp_1 namespaces with OIDs 11333 and 11334. Those namespaces are visible from any cluster database, but we cannot create any temp objects (please, correct me). So, how can we use those namespaces and what are needed for? Thank you. -- Best regards, Dmitry Voronin
