PostgreSQL の SELECT は列が空でもよかった
以前書いたコードを見てたとき

SELECT FROM table;

と言う感じで SELECT のあとに列名が抜けてるのがあった
「*」 が抜けてて動いてなさそう……と思ったけど実行されてるはずのコード
psql で試しに実行してみたらエラーにならず成功した

列名は省略可能みたい
昔からそうだっけと思って調べたら 9.4 からの変更だった

https://www.postgresql.jp/document/9.3/html/sql-select.html
https://www.postgresql.jp/document/9.4/html/sql-select.html

リリースノートには この変更で 0 個の列のビューのダンプ・リストアが正常に出来るようになるって書いてるから このために許可したみたい
https://www.postgresql.jp/document/9.4/html/release-9-4.html

列なしの SELECT なんてしないと思ったけど実際に使われていたわけで EXISTS の中で使う SELECT 文には 「*」 書かなくていいからありなのかも

ちなみに SQL 標準ではないみたいで mysql だと構文エラーでした
PostgreSQL でデータベース・ロール固有の設定を表示する
alter database や alter role で設定した内容を表示したいです
show を使えば現在有効な設定を確認できますが alter database などで変更したものかはわかりません

pg_db_role_setting を見ればその情報が取れます
https://www.postgresql.jp/document/14/html/catalog-pg-db-role-setting.html

データベースとロールを用意します

postgres=# create database db1;
CREATE DATABASE
postgres=# create role foo;
CREATE ROLE

データベースとロールの設定を変更します

postgres=# alter database db1 set timezone to 'asia/tokyo';
ALTER DATABASE
postgres=# alter role foo set search_path to a, b, c;
ALTER ROLE
postgres=# alter role foo in database db1 set search_path to public;
ALTER ROLE

一応変更されてることの確認です

postgres=# show timezone;
TimeZone
----------
Etc/UTC
(1 row)

postgres=# \c db1
You are now connected to database "db1" as user "postgres".
db1=# show timezone;
TimeZone
------------
Asia/Tokyo
(1 row)

pg_db_role_setting の中身を表示します

db1=# select * from pg_db_role_setting;
setdatabase | setrole | setconfig
-------------+---------+-------------------------
16388 | 0 | {TimeZone=asia/tokyo}
0 | 16391 | {"search_path=a, b, c"}
16388 | 16391 | {search_path=public}
(3 rows)

設定した内容が表示されています
片方のみだと未設定の部分は 0 です
両方の組み合わせに対して設定すると両方に oid が入ります
setdatabase と setrole の数値は pg_database の oid と pg_roles の oid になっています

db1=# select oid, datname from pg_database;
oid | datname
-------+-----------
5 | postgres
16388 | db1
1 | template1
4 | template0
(4 rows)

db1=# select oid, rolname from pg_roles;
oid | rolname
-------+---------------------------
6171 | pg_database_owner
6181 | pg_read_all_data
6182 | pg_write_all_data
3373 | pg_monitor
3374 | pg_read_all_settings
3375 | pg_read_all_stats
3377 | pg_stat_scan_tables
4569 | pg_read_server_files
4570 | pg_write_server_files
4571 | pg_execute_server_program
4200 | pg_signal_backend
4544 | pg_checkpoint
10 | postgres
16391 | foo
(14 rows)
PostgreSQL の正規表現が速い
前に SQL のクエリでわざと少し遅くしようとしたことがあり 遅くなるものといえば正規表現かなと試してみましたが 想像以上に速かったです
普通の正規表現だと遅いと言ってもそんなに変わらないので 遅くなるような正規表現を探したところシンプルなのだとこういうのが見つかりました

^(\d+)*$
.+a

まずは Node.js でどれくらいかかるのか調べると

/^(\d+)*$/.test("1234567890123456789012345678a")

が 17 秒ほど
最適化されるのか 3 回目くらいから 1 秒程度になりましたけど

/.+a/.test("1".repeat(100000))

が 9.8 秒ほど
repeat で 10 万文字もありますが 「.+a」 ではなく 「a.+」 だと 1 ミリ秒未満なので 文字数が多いせいというわけではないです

これを PostgreSQL で試したのですが

postgres=# \timing
Timing is on.
postgres=# select '1234567890123456789012345678a' ~ '^(\d+)*$';
?column?
----------
f
(1 row)

Time: 2.068 ms
postgres=# select '1234567890123456789012345678a' ~ '^(\d+)*$';
?column?
----------
f
(1 row)

Time: 0.538 ms
postgres=# select '1234567890123456789012345678a' ~ '^(\d+)*$';
?column?
----------
f
(1 row)

Time: 0.675 ms
postgres=# \timing
Timing is on.
postgres=# select repeat('1', 100000) ~ '.+a';
?column?
----------
f
(1 row)

Time: 5.444 ms
postgres=# select repeat('1', 100000) ~ '.+a';
?column?
----------
f
(1 row)

Time: 5.588 ms
postgres=# select repeat('1', 100000) ~ '.+a';
?column?
----------
f
(1 row)

Time: 5.080 ms

速すぎです
数ミリ秒程度です

遅い正規表現はエンジンの実装次第なところがあるらしいですが パフォーマンスには力を入れているはずの V8 でも遅いものをこれだけの速度で実行できるというのはすごいです
ユーザ入力で遅くなりうる正規表現を実行するなら SQL として PostgreSQL 側で実行するのもありかも?
Docker コンテナ内で mariadb と postgresql をインストールして起動
mariadb と postgresql で機能比較しようとしたとき とりあえず Docker 環境で動かそうと思ったけど それぞれを別コンテナにしてネットワーク関係の設定をして というのは面倒だったので 1 つの fedora のコンテナにまとめて入れることに
だけどそうすると systemctl が使えなくていつもの方法が使えず困ったので systemd なしで初期化と起動する方法
コンテナは fedora34 を使用

◯ postgresql

インストール

dnf install postgresql postgresql-server

バージョンは 13
最初は initdb が必要だけど

postgresql-setup --initdb

は systemd が必要でエラーになる
代わりに pg_ctl コマンドを使う
-D で data フォルダの場所の指定が必要
postgres ユーザの必要があるので su コマンドを通す

su postgres -c "pg_ctl -D /var/lib/pgsql/data initdb"
su postgres -c "pg_ctl -D /var/lib/pgsql/data start"

これで起動できたので

psql -U postgres

で接続できる

◯ mariadb

インストール

dnf install mariadb mariadb-server

バージョンは 10.5
mariadb も root ユーザじゃなく mysql ユーザに変更が必要
root で mariadb-install-db を実行すると root ユーザのファイルになるので 起動時に権限不足でファイルが読めなくてエラーになって即終了してた
mysql ユーザはシェルが指定されてないみたいで -s で bash 指定が必要だった

su mysql -s /usr/bin/bash -c "mariadb-install-db"
su mysql -s /usr/bin/bash -c "/usr/bin/mysqld_safe --datadir=/var/lib/mysql"

上側の mariadb-install-db コマンドの出力に起動方法のコマンド (下側) が表示される
直列化異常 (Serialization Anomaly) ってなに
RDB のトランザクション分離レベルをみていたときのこと
一般的には

分離レベルDirty ReadNonrepeatable ReadPhantom Read
Read uncommitted
Read committed
Repeatable read
Serializable

◯は安全という意味ではなくて その現象が発生する可能性があるという意味
✕が多いほど安全

PostgreSQL だと Read uncommitted の Dirty Read と Repeatable read の Phantom Read は発生しないみたい
つまり

分離レベルDirty ReadNonrepeatable ReadPhantom Read
Read uncommitted
Read committed
Repeatable read
Serializable

じゃあ 上 2 つと 下 2 つは一緒で実質 2 種類だけかと思ったら
ドキュメント的には Read uncommitted は Read committed と一緒と書いてるけど Repeatable read と Serializable は違いがあるみたい

ドキュメントの表ではもうひとつ Serialization Anomaly (直列化異常) という列があってこれが違うらしい

分離レベルDirty ReadNonrepeatable ReadPhantom ReadSerialization Anomaly
Read uncommitted
Read committed
Repeatable read
Serializable

Serialization Anomaly って何?と思ってドキュメントを見ると
トランザクションのグループのコミットしたときに そのコミット順が入れ替わると結果が変わる場合が Serialization Anomaly ということみたい



詳細はドキュメントに詳しく書いてるけど長すぎて読む気がしないのでとりあえず実際に試してみる

テーブル用意

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (name text);
INSERT INTO t1 VALUES ('first');

psql を 2 つ同時に実行して それぞれの操作を A と B とする
まずは Serialization Anomaly が発生しない Serializable

-- A
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- B
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- A
INSERT INTO t1 VALUES ('A1');

-- B
INSERT INTO t1 VALUES ('B1');

-- A
COMMIT;

-- B
COMMIT;

postgres=# SELECT * FROM t1;
name
-------
first
A1
B1
(3 rows)

並行して INSERT してコミットしたけど問題なし

SELECT が入ると

-- A
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- B
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- A
SELECT * FROM t1;
name
-------
first
A1
B1
(3 rows)

INSERT INTO t1 VALUES ('A2');

-- B
SELECT * FROM t1;
name
-------
first
A1
B1
(3 rows)

INSERT INTO t1 VALUES ('B2');

-- A
COMMIT;

-- B
COMMIT;
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.

postgres=# SELECT * FROM t1;
name
-------
first
A1
B1
A2
(4 rows)

あとからコミットする B でエラーになる
SELECT で今の値を取得してる以上 取得したものを使って INSERT されたものとして扱われて A がコミットされてから B を開始していたら別の結果になったかもしれないと考えられる
B が A のコミット前の状態をもとに更新してるという扱いでエラーのよう

A,B 問わずに数字を連番にしてたとして最後が A10 だとする
A → B の順で並列させずに実行していたら A 側では A11 を INSERT して B 側では A11 があるので B12 を INSERT することになるはず
B → A の順で並列させずに実行していたら B 側では B11 を INSERT して A 側では B11 があるので A12 を INSERT することになるはず
A,B を並列で実行させていたら どちらも SELECT 時は A10 が最後にあるので A11 と B11 を INSERT することになるはず

こういう順番で結果が変わりうる場合にあとからコミットされたほうがエラーになるのが Serializable のはず

これを気にしなくていいなら Repeatable read

-- A
BEGIN ISOLATION LEVEL REPEATABLE READ;

-- B
BEGIN ISOLATION LEVEL REPEATABLE READ;

-- A
SELECT * FROM t1;
name
-------
first
A1
B1
(3 rows)

INSERT INTO t1 VALUES ('A3');

-- B
SELECT * FROM t1;
name
-------
first
A1
B1
(3 rows)

INSERT INTO t1 VALUES ('B3');

-- A
COMMIT;

-- B
COMMIT;

postgres=# SELECT * FROM t1;
name
-------
first
A1
B1
A2
A3
B3
(6 rows)

SELECT があっても A,B の両方がコミットに成功してる



ドキュメントの URL
https://www.postgresql.org/docs/13/transaction-iso.html
https://www.postgresql.jp/document/13/html/transaction-iso.html

PostgreSQL の日付型は now という文字列が自動で現在時刻に変換される
現在時刻を設定したいけど アプリケーションサーバのタイムスタンプじゃなくてデータベースサーバのタイムスタンプに統一したいところ
Node.js で knex を使っていて knex だと現在時刻を設定したいところは pg.fn.now() を入れておけば良い

そのとき update するためのオブジェクトを作るところは別モジュールで pg を参照できなくて とりあえず "now" という文字列で渡しておいて update を実行するところで置換すればいいやと思ってた
その後 pg.fn.now に置き換える処理を追加するのを忘れてたけどなぜか普通に動いてる
調べてみると "now" という文字列を日付型に保存しようとすると自動で現在時刻として扱われるみたい
knex 固有の機能というわけではなくて psql で SQL を書くときでも一緒
単にキャストすればそうなる

postgres=# select 'now'::timestamp;
timestamp
----------------------------
2021-09-18 06:33:46.890335
(1 row)

すごく便利
pg.fn.now() なんて長いの書く必要なかった
データベースでタイムスタンプの一致が検索できない
RDB のクエリで WHERE にタイムスタンプ型の列を対象にするとなぜか毎回何も一致しません
レコードが存在するのは確認していて SELECT で取得したデータをそのまま WHERE に入れても一致しません

これに結構苦戦させられたのですが 単純に精度の問題でした
環境は Node.js なのでタイムスタンプ型は Date 型として取得できます
node-pg でもそれをラップした knex 等でも同じです

Date 型はミリ秒までですが PostgreSQL で timestamp と指定したときの精度はマイクロ秒まであります
なので Node.js で受け取った段階でマイクロ秒は切り捨てられて その値で検索しても一致はしないわけです
大きな数値の ID などで数値型でも有り得そうな問題ですが 受け取ったタイムスタンプが見た目上問題なさそうなので原因特定に時間がかかりました

言語の都合でデータベースの定義を変更するのはどうかなという気はするものの その Node.js のアプリ専用なデータベースですし 実際にはミリ秒以下は必要になることはないので秒までにすることで対処しました
タイムスタンプ型のオプションで精度は変更できます
また CURRENT_TIMESTAMP などを使わず Node.js 側で現在時刻を取得して保存すればマイクロ秒は常に 0 になるので定義を変更せずに対処することもできます

もしマイクロ秒まで必要となると select 時にキャストして文字列型で取得するしかなさそうです

pg("table")
.select("id", pg.raw("xxx_timestamp::text"))
PostgreSQL で EXPLAIN ANALYZE を JSON で取得
EXPLAIN するとき ANALYZE と FORMAT JSON を両方かけないと思ってたけど構文調べたら 「()」を使ってカンマ区切りにすれば両方指定できた

test1=# explain (analyze true, format json) select * from table1 where id = 1;
QUERY PLAN
------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Index Scan", +
"Parallel Aware": false, +
"Scan Direction": "Forward", +
"Index Name": "table1_pkey", +
"Relation Name": "table1", +
"Alias": "table1", +
"Startup Cost": 0.28, +
"Total Cost": 16.30, +
"Plan Rows": 1, +
"Plan Width": 1049, +
"Actual Startup Time": 0.048, +
"Actual Total Time": 0.048, +
"Actual Rows": 0, +
"Actual Loops": 1, +
"Index Cond": "(id = 1)", +
"Rows Removed by Index Recheck": 0+
}, +
"Planning Time": 0.475, +
"Triggers": [ +
], +
"Execution Time": 0.114 +
} +
]
PostgreSQL のユーザとグループ
PostgreSQL だとユーザもグループもロールで管理される
ログイン許可すればユーザ扱いでログインを許可しなければグループ扱い
テーブルの所有者のロールをグループにしておけば ユーザをそのグループに所属させてテーブルへの権限を与えられる
グループに所属させる GRANT コマンドは「GRANT グループ名 TO ユーザ名」でグループをユーザへ GRANT (付与)

CREATE ROLE "test_group";
CREATE ROLE "test_user1" WITH LOGIN PASSWORD 'PASSWORD';
GRANT "test_group" TO "test_user1";

CREATE TABLE kv
(
key text NOT NULL,
value text,
CONSTRAINT common_setting_pkey PRIMARY KEY (key)
);
ALTER TABLE kv OWNER TO "test_group";

postgres=# \du
ロール一覧
ロール名 | 属性 | 所属グループ
------------+----------------------------------------------------------------------------+--------------
postgres | スーパーユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
test_group | ログインできません | {}
test_user1 | | {test_group}
PL/V8 みつけた
sql ファイルでちょっと複雑な処理したいけど SQL でできるの?
→ぐぐったらできそうなコードがあるけどどう動かすのか調べてみたら PL/pgSQL

DO statement で使えるぽいけどあんまり使いたくない
他になにかないのかな
→PL/Python があった

良さげだけど Python のインストールとか準備面倒そう
Python あるなら JavaScript もあるんじゃないの?
PL/V8 があった

11 時点のドキュメントだと Python や Perl は PL/* に載ってるけど V8 はない
まだマイナーどころかも

JavaScript でやるなら node-postgres を使って Node.js でやればいいんだけど ちょっと使った感じではそれらを準備するより楽に使える
Node.js で使うとコネクションとか非同期クエリ実行とか色々考えるところや面倒なところあり
PL/V8 だと DB 内で処理されるわけだからクエリは同期的だし 面倒な準備も不要
psql で接続して SELECT や INSERT 文の代わりに JavaScript 書く感じ
普通に Node.js でやるよりはいいかも

それに その場での実行だけじゃなくて関数も作れる
複雑な条件の SQL 処理は全部事前に関数を作っておいて クライアントでは関数呼び出すだけで済むようにすると便利そう
複数回 SQL を実行して結果をまとめたりするのを Node.js でやるよりは PostgreSQL 側でやってしまったほうが無駄な通信も減らせる
PostgreSQL は JSON でデータ返せるから JavaScript で処理した結果を返すのにも向いてる
なにより SQL ではやりたいことの関数名と引数だけで済むほうが Node.js 側がスッキリする

ただ PL/V8 内の SQL の実行は関数呼び出しになるので if 文で分岐や変数埋め込みしたい程度ならあんまり見やすくならない
SQL を直接書ける PL/pgSQL のほうが見やすくかけそう
クエリ結果のデータを処理する部分で JavaScript 使いたいくらいじゃないとメリットは少ないかも
PostgreSQL で date $1 はできない
プログラムから SQL を実行するときに変数を埋め込むことができますが 使える場所と使えない場所があります

select date '2020-01-01 10:20:30'

のようなリテラルの場合は date を前に書くだけで date 型にキャストされますが ここを $1 にすることはできないです
date($1) や $1::date でキャストする必要があります

実際の値で SQL を書いてから変数化するとハマりやすいんですよね

/// c は pg.Client

> c.query("select date '2020-01-01 10:20:30'").then(x => console.log(x.rows)).catch(console.error)
// [ { date: 2019-12-31T15:00:00.000Z } ]

> c.query("select date $1", [new Date()]).then(x => console.log(x.rows)).catch(console.error)
// error: syntax error at or near "$1"

> c.query("select date($1)", [new Date()]).then(x => console.log(x.rows)).catch(console.error)
// [ { date: 2020-01-05T15:00:00.000Z } ]

> c.query("select $1::date", [new Date()]).then(x => console.log(x.rows)).catch(console.error)
// [ { date: 2020-01-05T15:00:00.000Z } ]
PostgreSQL の Unicode
E プレフィックスを付けると書ける
ないと 「\」 がエスケープ扱いされない

postgres=# select E'\u3042\u3044\u3046';
?column?
----------
あいう
(1 行)

x プレフィックスで一文字ずつ文字コードから文字変換もできる

postgres=# select chr(unnest(array[x'3042', x'3044', x'3046']::int[]));
chr
-----



(3 行)

x つけたのは型的には bit で int キャストすると 16 進数の数値リテラルみたいに使える

postgres=# select pg_typeof(x'3042'), x'3042';
pg_typeof | ?column?
-----------+------------------
bit | 0011000001000010
(1 行)