S-JIS[2023-10-09/2024-08-31]

Tsurugi insert

TsurugiのSQLのinsertのメモ。

概要

TsurugiのSQLでテーブルにデータを追加するのがinsert文。

通常のinsert文では、同一プライマリキーのデータが既に存在していると、一意制約違反（UNIQUE_CONSTANT_VIOLATION_EXCEPTION）のエラーになる。[2024-02-24]
このエラーが発生すると、そのトランザクションはもう使えない。（この状態でSQLやcommitを実行するとINACTIVE_TRANSACTION_EXCEPTIONが発生する。こうなったら、rollbackでトランザクションを終了させる）

insertの例

insert into test (foo, bar, zzz) values(1, 123, 'abc');

insert into test values(2, 456, 'def');

→指定できるリテラル（値）

Tsurugi 1.0.0-BETA5で、valuesに複数書けるようになった。[2024-07-11]

tgsql> insert into test values(1, 123, 'abc'), (2, 456, 'def');
(2 rows inserted)

insert or replaceの例

insert or replaceは、テーブルにデータが無い場合は追加し、有る場合は更新する。
データの有無はプライマリキーで判断する。

insert or replace into test values(1, 111, 'overwrite');

なお、insert or replaceのエイリアスとして、update or insertとも書ける。[2023-10-22]

単なるinsertの場合、書き込み前に、テーブル内に既にデータが存在しているかどうかチェックする。（存在していたら一意制約違反になる）[2023-12-15]

insert or replaceの場合、既存データの有無に関わらず、問答無用でデータを書き込む。
このため、insert or replaceは（存在チェックを行わない分、）insertよりも高速。
また、同じ理由で、insert or replaceでは一意制約違反は発生しない。

この挙動は、Tsurugiの内部の仕組みを知っていると理解しやすい。[2024-02-24]

Tsurugiでは「トランザクション機能を持つKVS」という構造（Masstree）でデータを保持している。
これは例えるならJavaのTreeMap（キー順に並んでいるMap）のようなもの。
insertとinsert or replaceは、以下のような処理を行っている。

実際、Tsurugi内部ではSQLをKVSの処理に変換して実行している。
insertはgetとputを行い、insert or replaceはputのみを行っている。

insertとinsert or replaceの違いは、トランザクションの競合（シリアライザブルのチェック）にも現れてくる。[2024-02-24]

Tsurugiのトランザクションの用語では、KVSのgetのことをread（略してr）、putのことをwrite（略してw）と呼ぶらしい。
それを使って、insertはreadしてからwriteするのでrw、insert or replaceはwriteのみなのでwと表記する。
updateやdeleteも処理対象を読んでからそれに対して変更する（書き込む）のでrwである。

OCCでreadを行った場合、コミット時に「読んだデータが他トランザクションによって変更（およびコミット）されていないかどうか」 のチェックが入り、変更されていた場合はシリアライゼーションエラー（CC_EXCEPTION）になる。
したがって、rwであるinsert・update・deleteはreadに関するシリアライゼーションエラーになる可能性がある。
しかしwであるinsert or replaceはreadしていないので、readに関するシリアライゼーションエラーになることは無い。

例えば、2つのトランザクションT1とT2が同じデータを更新するとき、以下のような違いが出る。
（create table tb (pk int primary key, value int);で、pk=1のデータが入っているものとする）

insert if not existsの例

insert if not existsは、テーブルにデータが存在しない場合だけ追加する。（データの有無はプライマリキーで判断する）
データが有る場合は何もしない。（単なるinsertだと一意制約違反が発生するが、if not existsの場合はPKの一意制約違反は発生しない）

insert if not exists into test values(3, 456, 'not exists');

Tsurugi 1.0.0-BETA2（tgsql 1.1.0やIceaxe 1.1.0）以降では、実際にインサートした件数が返る。[2024-02-24]
データが無かった場合は（インサートして）1が返るが、データが既に存在していた場合は（何もせず）0が返る。

insert or ignoreの例

Tsurugi 1.0.0-BETA5では、insert if not existsをinsert or ignoreと書くことも出来る。[2024-07-11]

insert or ignore into test values(3, 456, 'not exists');

insert into〜selectの例

Tsurugi 1.0.0-BETA6で、insert into〜selectが使えるようになった。[2024-08-16]

insert            into test select * from test2;
insert or replace into test select * from test2;
insert or ignore  into test select * from test2;

default valuesの例

Tsurugi 1.0.0で、default valuesが使えるようになった。[2024-08-31]
これは、全カラムをデフォルト値（create tableの各カラムのdefaultやgenerated〜as identityで指定された値）でinsertするもの。

create table test (
  pk              int                      primary key generated always as identity (start with 1),
  value           bigint                   default 100,
  create_datetime timestamp with time zone default current_timestamp
);

insert into test default values;
insert into test default values;

tgsql> select * from test;
[pk: INT4, value: INT8, create_datetime: TIME_POINT_WITH_TIME_ZONE]
[1, 100, 2024-08-31 10:21:17.610998088+09:00]
[2, 100, 2024-08-31 10:21:18.435820037+09:00]
(2 rows)

※この例では、tsurugi.iniのzone_offsetを+09:00にしてある。