細かすぎて教えてもらえなかった、psqlでSQLスクリプトを検証しながら書く方法

約半年ぶりの登場になります、データエンジニアのt-sugaiです。
最近アスタミューゼにも仲間が増えて、なかなかブログの順番が回ってこないので油断していました。
そんなわけで、今回は軽めな上にちょっとニッチなネタですが、ご容赦いただければと思います。

f:id:astamuse:20200311125058j:plain

データパッチ、書いてますか？

最近はなかなか生のSQLを書いてデータパッチを当てるというようなオペレーションも減ってきてはいますが、やはり最後に頼りになるのは生SQL力だと信じています。とはいえ、本番DBに対してSQLを発行するのはなかなか緊張感のあるオペレーションです。できるかぎり、複数回の試験・検証を経てから投入したいですよね。

一方、ローカルや開発環境では安心して試行錯誤をしたいですね。
PostgreSQLでは、DDLやTRUNCATEにもトランザクションが有効になるので、これをうまく使うと大部分のオペレーションはトランザクションの範囲内で実施できます。したがって、明示的にトランザクションの開始とロールバックを使うことでデータパッチを安全に検証しながら作成することができます。
先日会社のSlackでこの話をしたら思いのほか反響があったので、ちょっとした話しすぎてだれも教えてくれなかったTIPSとしてまとめてみてもいいかなという試みです。

データパッチはアドホックに書きたい

ちょっとしたデータパッチを当てるときや、検証環境でアドホックにSQLを書いてデータの追加/更新/削除の処理を検証するとき、確認が取れたSQLをそのまま運用環境で実施するためにスクリプトファイルにしたりしますよね。
エディタなどで一気に書き下ろしたスクリプトを実行すると、ちょっとしたtypoをしてしまったり、うっかりカンマやセミコロンを忘れてしまうことがあります。そんなとき、デフォルトが auto commit = true な PostgreSQL ですから、中途半端なデータがコミットされないように明示的にトランザクションを指定しますよね。
ということで、トランザクションで囲んだスクリプトを psql -f オプションで読み込ませると、前述のようなちょっとしたシンタックスエラーは安全にロールバックしてくれるので、安心です。

シンタックスじゃなくて、データパッチの内容を確認したい

ところで、エラーなく実施できれば当然トランザクションがコミットされますね。ここでたとえば、AさんはこんなUPDATE文を実行するスクリプトを書いていたとします。

update_mistake.sql

START TRANSACTION;

UPDATE 
    patent_documents 
SET 
    category_id = 2
WHERE 
    patent_document_number = '12345'

COMMIT;

このスクリプトを素直に実行するならこんな感じですよね。

$ psql -f update_mistake.sql
START TRANSACTION
UPDATE 10
COMMIT
$

しかし、コミットしてしまってからデータを確認しているときに、データの不備に気づきました。
じつは変更対象は status = 1 のみだったのです。
そして、今回の例示では10行だけだったようですが、status <> 1 のレコードがたくさんあり、しかもそれらの category_id が規則性らしい規則性もなくバラバラだったとしたら…… これを戻すデータパッチを考えるのはつらいですね。
検証環境なら以前のdumpなどから戻せばいいかもしれませんがちょっと大げさです。コミットするまえにデータを確認できれば、もっと気楽にいろいろ試せますよね。

しかし、 psql -f で以下の様なコミットしないスクリプトを実施しても、当然ロールバックされてしまいます。

update_no_commit.sql

START TRANSACTION;

UPDATE 
    patent_documents 
SET 
    category_id = 2
WHERE 
    patent_document_number = '12345'

psql メタコマンド `\i` を使ってスクリプトの動作検証をする。

そこで、 psql のメタコマンド \i です。
このメタコマンドを使えば、対話コンソール上でファイルを読み込んで処理してくれるものです。

psql -d patents 

patents=>\i update_no_commit.sql 
START TRANSACTION
UPDATE 10

このようにすれば、スクリプトの終わったところまでで対話コンソールにコントロールが戻ってくるので、ここで SELECT 思う存分などして、中身を確認して、自分の意思で COMMIT or ROLLBACK を決めることができます。
当然ですが、COMMIT or ROLLBACK をするまでトランザクションをとり続けるので、特に更新の多いデータなどを扱っている場合には気をつけてください。このような手段をとることは言語道断なテーブル・データベースも世の中には少なからずあると思います。
しかし、ローカルや検証用環境などで更新クエリの試行錯誤をする際には便利だと思います。

psql メタコマンドは便利なものが多いよ。

pgAdminなどを利用しているとあまり知る機会がないかもしれませんが、psqlのメタコマンドには他にも \copy や \o など、使ってみると便利なオプションが多数あります。
（逆に、私はpsqlが便利なのでpgAdminは使わなくていいかな、と思ってしまう方です）
\? でメタコマンドの一覧がいつでも確認できます。一度眺めてみると、自分のオペレーションに合ったものが見つかるかもしれませんよ。