psql -c command と psql -f filename のトランザクション制御の違い

psql -c command と psql -f filename はコマンドとSQLファイルをシェルから実行するという点で似ているが、AUTOCOMMIT offのとき、トランザクション制御に違いが出てくる。
PostgreSQLは、伝統的に自動コミット有効モードで動作しているが、自動コミット無効モードの方がよりSQLの仕様に近いので、.psqlrcにAUTOCOMMIT offを設定している場合が多々あると思う。

まずAUTOCOMMIT offにするため、.psqlrcに設定する。

例としてinsert_lock.sql(pgpool-Ⅱのレプリケーションモードで insert_lock を利用するための設定SQL)を実行する。

確認用コマンドの結果は、作成したはずの"pgpool_catalog.insert_lock"が見つからない。
原因は、AUTOCOMMIT offだから psql -f を実行した後 psql がコミットを行わずに終了したため。

では psql -c を実行するとどうなるか。

確認用コマンドの結果を見ると、"pgpool_catalog.insert_lock"の作成に成功している。
成功した原因は、-c オプションを使用すると起動ファイル(psqlrc and ~/.psqlrc)が無視されるから。今回の場合はAUTOCOMMIT offが無視され、AUTOCOMMITのデフォルト(on)が有効になった。

更新系SQLをpsqlコマンドを通して実行する場合、-c オプションのようにAUTOCOMMITしてくれないと、SQLファイルそのものに"commit;"を書かなければならない。
今回例に出したinsert_lock.sqlはpgpool-Ⅱをインストール時からあるSQLファイルで、AUTOCOMMITがデフォルトを想定しているからだと思うが、"commit;"は書いていない。
insert_lock.sqlを修正せずに -f オプションでもコミットをするには、このオプションをつければいい。

参考:https://www.postgresql.jp/document/9.2/html/app-psql.html

-PostgreSQL