仮想空間で学ぶPostgreSQL

この記事は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_pgdt.sql

CREATE DATABASE pgdt;
\connect pgdt
CREATE EXTENSION plpython3u;
\i 03_truncate_trigger.sql

TRUNCATE実行時のトリガ関数

TRUNCATE実行時に呼び出すトリガ関数を定義します。
TRUNCATE実行したユーザにより、処理を変えてます。unityユーザの場合は「何もしない」、それ以外のユーザの場合はwolframscriptでオブジェクトを移動させます。

PLPythonトリガ関数

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(位置や角度)を変更するだけです。

wolfscript

#!/usr/bin/env wolframscript
cyabudai = FindUnityGameObject["cyabudai"] 
cyabudaitransform = cyabudai["Transform"]
Print[cyabudaitransform[["position"]]]

　
　

実践

諸々用意できたので、お勉強していきます。

仮想空間の挙動（１）

1. 丸太をクリックするとちゃぶ台がでてきます。
2. ごはん、味噌汁をちゃぶ台に配膳してます。（適当においても位置調整されます）

物理空間の挙動（１）

上記挙動の裏（物理空間）では、何が起きてるかログを見てみます。

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)

1. CREATE TABLE文を発行してます。
2. 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 行)

仮想空間の挙動（２）

1. ごはんを持ち上げてます。
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)

1. この時点では何も実施しません。
2. 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 行)

仮想空間の挙動（３）

1. ちゃぶ台返しをします。
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

1. TRUNCATE文を発行してます。
2. トリガ関数を実行してますが、unityユーザで実行のため何も起こりません。

ビールがないからといって、ちゃぶ台返ししてはいけません。

現場からは以上です。

まとめ

今年は、PostgreSQLの内部挙動をデジタルツインで表現することを行いました。
学びとしては、

1. 物理空間⇔仮想空間のデジタルツインを構築できた。まぁ、ツインというにはかなり恣意的なんで、デジタルブラザーくらいかな・・・。
2. RETURNING句がMERGE文だと使えないことを知った（MERGE文）。ctidを取得したいだけに2回クエリ発行するのも面倒だったので、今回はINSERT ... ON CONFLICTで対応した。
3. wolframscriptでUnityオブジェクト操作も楽々できた。

これからも楽しくPostgreSQLライフを送りたいと思います！