kotememo

【PostgreSQL】一時テーブルは別セッションで同時に作成できるか

作成日：2023/04/01

はじめに

セッション中のみ保持される一時テーブルが別セッションでも同名で作成できるのか試してみました。

結論としては別セッションであれば作成できるようです。

通常テーブルと各セッションの一時テーブルでそれぞれ登録先のスキーマが異なりました。

概要

2個のセッションで同時接続し、トランザクション中に同名テーブル作成を試しました。

各パターンは試した後にテーブルを削除しています。

    後セッション
先セッション		通常テーブル一時テーブル
通常テーブル×○
一時テーブル○○
元々これを確認したかった
  1. ×【先】通常テーブル作成→【後】通常テーブル作成
  2. ○【先】通常テーブル作成→【後】一時テーブル作成
  3. ○【先】一時テーブル作成→【後】通常テーブル作成
  4. ○【先】一時テーブル作成→【後】一時テーブル作成

環境

  • Windows 10 Home
  • PostgreSQL 14.6

1. ×【先】通常テーブル作成→【後】通常テーブル作成

別セッションでテーブル作成する時は待ち状態になります。 commit後はテーブル重複でエラーになります。

セッション1 (通常テーブル作成)

postgres=# begin;
BEGIN
postgres=*# create table testtable (id numeric);
CREATE TABLE
postgres=*#

セッション2 (通常テーブル作成時に待ち状態で待機)

postgres=# begin;
BEGIN
postgres=*# create table testtable (id numeric);
（待ち状態）

セッション1 (commit)

postgres=*# commit;
COMMIT
postgres=#

セッション2 (待ち状態解放、エラー)

postgres=*# create table testtable (id numeric);
ERROR:  duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(testtable, 2200) already exists.
postgres=!#

2. ○【先】通常テーブル作成→【後】一時テーブル作成

作成可能ですがスキーマがpublicとpg_tempに分かれます。

セッション1 (通常テーブルがpublicスキーマに作成)

postgres=# begin;
BEGIN
postgres=*# create table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ |   名前    |  タイプ  |  所有者
----------+-----------+----------+----------
 public   | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(1);
INSERT 0 1
postgres=*#

セッション2 (一時テーブルはpg_temp_4スキーマに作成)

postgres=# begin;
BEGIN
postgres=*# create temporary table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_4 | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(2);
INSERT 0 1
postgres=*# select * from testtable;
 id
----
  2
(1 行)


postgres=*# \dt public.*
"public.*"という名前のリレーションは見つかりませんでした。
postgres=*#

セッション1 (commit)

postgres=*# commit;
COMMIT
postgres=#

セッション2 (スキーマの指定無しだと一時テーブルのほうを参照)

postgres=*# \dt public.*
              リレーション一覧
 スキーマ |   名前    |  タイプ  |  所有者
----------+-----------+----------+----------
 public   | testtable | テーブル | postgres
(1 行)


postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_4 | testtable | テーブル | postgres
(1 行)


postgres=*# select * from public.testtable;
 id
----
  1
(1 行)


postgres=*# select * from testtable;
 id
----
  2
(1 行)


postgres=*#

3. ○【先】一時テーブル作成→【後】通常テーブル作成

こちらも作成時にスキーマがpublicとpg_tempに分かれます。

セッション1 (一時テーブル作成)

postgres=# begin;
BEGIN
postgres=*# create temporary table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_3 | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(1);
INSERT 0 1
postgres=*# select * from testtable;
 id
----
  1
(1 行)


postgres=*#

セッション2 (通常テーブル作成)

postgres=# begin;
BEGIN
postgres=*# create table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ |   名前    |  タイプ  |  所有者
----------+-----------+----------+----------
 public   | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(2);
INSERT 0 1
postgres=*# select * from testtable;
 id
----
  2
(1 行)


postgres=*#

セッション2 (先に通常テーブル作成のセッションBをcommit)

postgres=*# commit;
COMMIT
postgres=#

セッション1 (やはりスキーマの指定無しでは一時テーブルが優先されている)

postgres=*# select * from testtable;
 id
----
  1
(1 行)


postgres=*# select * from public.testtable;
 id
----
  2
(1 行)


postgres=*#

4. ○【先】一時テーブル作成→【後】一時テーブル作成

一時テーブル作成時にpg_temp_3とpg_temp_4と異なるスキーマに分かれます。

セッション1 (一時テーブル作成)

postgres=# begin;
BEGIN
postgres=*# create temporary table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_3 | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(1);
INSERT 0 1
postgres=*# select * from testtable;
 id
----
  1
(1 行)


postgres=*#

セクション2 (一時テーブル作成)

postgres=# begin;
BEGIN
postgres=*# create temporary table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_4 | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(2);
INSERT 0 1
postgres=*# select * from testtable;
 id
----
  2
(1 行)


postgres=*#

セクション1 (commit)

postgres=*# commit;
COMMIT
postgres=#

セクション2 (スキーマ指定無しだとセクション2で作成した一時テーブルを参照)

postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_4 | testtable | テーブル | postgres
(1 行)


postgres=*# select * from testtable;
 id
----
  2
(1 行)


postgres=*#

ちなみに別セッションの一時テーブルの存在は確認できますが、selectは出来ないです。

セッション2 (別セッションの一時テーブルの中身は覗けない)

postgres=*# \dt pg_temp_3.*
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_3 | testtable | テーブル | postgres
(1 行)


postgres=*# select * from pg_temp_3.testtable;
ERROR:  cannot access temporary tables of other sessions
postgres=!#