この記事はPostgreSQL Advent Calendar 2022の18日目の記事です。
はじめに
昨年のAdvent Calendarで散財(粘土とかロボットアームとか)してしまい、かみさんに凄く文句をいわれました。
そこで、今年は仮想空間に逃げて遊ぶことにしました。デジタルツインです！
DBのデモとかも一般的に地味なのが多いので、少しでも分かりやすくなるとよいな。
デジタルツインとは
WikiPediaより：
デジタルツイン(英: Digital twin)とは、さまざまな目的で使用できる物理的資産、プロセス、人、場所、システムおよびデバイスのデジタル複製を指す。
PostgreSQLを物理空間と見立て、仮想空間にデータベースオブジェクトを可視化して、PostgreSQLの挙動をお勉強しましょう。
PostgreSQL(物理空間)の登場人物
以下のcyabudaiテーブルを用意して、PostgreSQLの追記型の挙動を可視化していきます。
テーブル構成
| テーブル名 | 列名 | データ型 | 備考 |
|---|---|---|---|
| cyabudai | id | INTEGER | Primary key |
| value | char(2000) | Index作成済 |
ユーザ
物理空間を操作したときと仮想空間を操作したときでユーザを分ける必要がありそうだったので、仮想空間操作時のユーザとして「unity」さんを作成しました。
物理空間操作時は面倒なので、postgresユーザを使います。
どちらもスーパーユーザ権限。
PS D:\work\2022\AdventCalendar\script> createuser -P unity
トリガ関数
物理空間を操作したら仮想空間のオブジェクトを移動するために、トリガ関数を用意しました。
UnityのオブジェクトはWolfram+UnityLinkで操作できそうなので、とりあえずインストールしてみました。
関数はPLPythonで書き、外部コマンドとしてwolframscriptを呼び出す形にしました。
初期構築スクリプト
DB：pgdtを作って、次のトリガ関数読み込んでおきます。
CREATE DATABASE pgdt;
\connect pgdt
CREATE EXTENSION plpython3u;
\i 03_truncate_trigger.sql
TRUNCATE実行時のトリガ関数
TRUNCATE実行時に呼び出すトリガ関数を定義します。
TRUNCATE実行したユーザにより、処理を変えてます。unityユーザの場合は「何もしない」、それ以外のユーザの場合はwolframscriptでオブジェクトを移動させます。
CREATE OR REPLACE FUNCTION unity_operation() RETURNS trigger AS $unity_operation$
import subprocess
res = plpy.execute("SELECT current_user")
current_user = res[0]["current_user"]
if current_user == "unity":
return None
command = "wolframscript.exe -wstpserver -f D:\work\2022\AdventCalendar\02_wolframscripts\05_cyabudaitruncate .wls"
proc = subprocess.Popen(command, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
stdout, stderr = proc.communicate()
ret = proc.returncode
print(stdout)
$unity_operation$ LANGUAGE plpython3u;
Unity(仮想空間)の登場人物
以下のオブジェクトを用意して、物理空間のデータと対応させてます。
オブジェクト構成
| オブジェクト名 | 形状 | 物理空間との対応 | 備考 |
|---|---|---|---|
| cyabudai | ちゃぶ台 | テーブル | 元は丸太 |
| rice | ごはん | データのINSERT/UPDATE | id=1の想定 |
| miso | 噌汁 | データのINSERT/UPDATE | id=2の想定 |
| beer | ビール | データのINSERT/UPDATE | id=3の想定 |
| fish | お魚 | データのINSERT/UPDATE | id=4の想定 |
| humberg | ハンバーグ | データのINSERT/UPDATE | id=5の想定 |
| fire | 焚火 | データの削除 |
衝突判定
オブジェクト同士が衝突したらアクションを行うように、OnCollisionEnterメソッドで愚直にSQLを発行したりします。
...
void OnCollisionEnter(Collision collision)
{
GameObject go = collision.gameObject;
if(go.CompareTag("important")) return;
string insert_sql = "";
Vector3 ctid_pos;
if (go.CompareTag("rice"))
{
if (flg[0] == true)
{
go.SetActive(true);
return;
}
insert_sql = "INSERT INTO cyabudai VALUES (1, 'rice') ON CONFLICT (id) DO UPDATE SET value = excluded.value RETURNING (ctid)";
flg[0] = true;
}
...
wolframscript
トリガ関数の中で呼び出してたスクリプトです。
仮想空間のcyabudaiのTransform(位置や角度)を変更するだけです。
#!/usr/bin/env wolframscript
cyabudai = FindUnityGameObject["cyabudai"]
cyabudaitransform = cyabudai["Transform"]
Print[cyabudaitransform[["position"]]]
実践
諸々用意できたので、お勉強していきます。
仮想空間の挙動（１）
- 丸太をクリックするとちゃぶ台がでてきます。
- ごはん、味噌汁をちゃぶ台に配膳してます。（適当においても位置調整されます）
物理空間の挙動（１）
上記挙動の裏（物理空間）では、何が起きてるかログを見てみます。
2022-12-18 15:51:53.705 JST [23884] LOG: duration: 507.161 ms statement: CREATE TABLE IF NOT EXISTS cyabudai(id INT PRIMARY KEY, value CHAR(2000))
2022-12-18 15:51:54.069 JST [29376] LOG: duration: 81.651 ms statement: CREATE INDEX IF NOT EXISTS idx_value ON cyabudai(value);
2022-12-18 15:51:54.348 JST [13268] LOG: duration: 21.136 ms statement: CREATE TRIGGER unity_operation_truncate BEFORE TRUNCATE ON cyabudai FOR EACH STATEMENT EXECUTE FUNCTION unity_operation();
2022-12-18 15:52:03.233 JST [22652] LOG: duration: 71.091 ms statement: INSERT INTO cyabudai VALUES (1, 'rice') ON CONFLICT (id) DO UPDATE SET value = excluded.value RETURNING (ctid)
2022-12-18 15:52:13.201 JST [27076] LOG: duration: 6.595 ms statement: INSERT INTO cyabudai VALUES (2, 'miso') ON CONFLICT (id) DO UPDATE SET value = excluded.value RETURNING (ctid)
- CREATE TABLE文を発行してます。
- INSERT文を発行してます。
ちゃんと2件のデータが入ってます。
PS D:\work\2022\AdventCalendar\03_psqlscripts> psql -U postgres pgdt
psql (15.1)
"help"でヘルプを表示します。
pgdt=# select ctid, id, text(value) from cyabudai;
ctid | id | text
-------+----+------
(0,1) | 1 | rice
(0,2) | 2 | miso
(2 行)
仮想空間の挙動（２）
- ごはんを持ち上げてます。
- ごはんを再度ちゃぶ台においてますが、元の位置に戻りません。
ごはんは左手前にないと落ち着かない派です（；＿；
物理空間の挙動（２）
上記挙動の裏（物理空間）では、何が起きてるかログを見てみます。
2022-12-18 15:56:37.114 JST [28564] LOG: duration: 2.631 ms statement: INSERT INTO cyabudai VALUES (1, 'rice') ON CONFLICT (id) DO UPDATE SET value = excluded.value RETURNING (ctid)
- この時点では何も実施しません。
- UPDATE文(INSERT ... ON CONFLICT)を発行してます。
ちゃんとごはんはUPDATEされてますが、ctidが(0,1)から(0,3)になってます。元の位置(0,1)には旧データが残ったままということです。
pgdt=# select ctid, id, text(value) from cyabudai;
ctid | id | text
-------+----+------
(0,2) | 2 | miso
(0,3) | 1 | rice
(2 行)
仮想空間の挙動（３）
- ちゃぶ台返しをします。
- ごはんや味噌汁は元の位置にもどり、綺麗なちゃぶ台が復活します。
物理空間の挙動（３）
上記挙動の裏（物理空間）では、何が起きてるかログを見てみます。
2022-12-18 17:00:49.267 JST [13412] LOG: duration: 206.626 ms statement: TRUNCATE cyabudai
2022-12-18 17:00:49.968 JST [24792] LOG: duration: 309.302 ms statement: TRUNCATE cyabudai
- TRUNCATE文を発行してます。
- トリガ関数を実行してますが、unityユーザで実行のため何も起こりません。
ビールがないからといって、ちゃぶ台返ししてはいけません。
現場からは以上です。
まとめ
今年は、PostgreSQLの内部挙動をデジタルツインで表現することを行いました。
学びとしては、
- 物理空間⇔仮想空間のデジタルツインを構築できた。まぁ、ツインというにはかなり恣意的なんで、デジタルブラザーくらいかな・・・。
- RETURNING句がMERGE文だと使えないことを知った（MERGE文）。ctidを取得したいだけに2回クエリ発行するのも面倒だったので、今回はINSERT ... ON CONFLICTで対応した。
- wolframscriptでUnityオブジェクト操作も楽々できた。
これからも楽しくPostgreSQLライフを送りたいと思います！