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

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

$ cat /var/lib/pgsql/.psqlrc
\set AUTOCOMMIT off

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

$ psql -f insert_lock.sql template1

#-- 確認用コマンド
$ psql template1 -c "\d pgpool_catalog.insert_lock"
Did not find any relation named "pgpool_catalog.insert_lock".

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

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

$ psql -c "CREATE TABLE pgpool_catalog.insert_lock(reloid OID PRIMARY KEY)" template1
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "insert_lock_pkey" for table "insert_lock"
CREATE TABLE

#-- 確認用コマンド
$ psql template1 -c "\d pgpool_catalog.insert_lock"
Table "pgpool_catalog.insert_lock"
 Column | Type | Modifiers
--------+------+-----------
 reloid | oid  | not null
Indexes:
    "insert_lock_pkey" PRIMARY KEY, btree (reloid)

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

更新系SQLをpsqlコマンドを通して実行する場合、-c オプションのようにAUTOCOMMITしてくれないと、SQLファイルそのものにcommit;を書かなければならない。

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

-1, --single-transaction

-fオプションを使用してpsqlがスクリプトを実行する時、このオプションを併記すると、スクリプトをBEGIN/COMMITで囲み、単一トランザクション内でスクリプトを実行します。

スクリプト内部でBEGIN、COMMIT、ROLLBACKを使用している場合、このオプションは想定した効果をもたらしません。また、スクリプト内部にトランザクションブロック内部で実行することができないコマンドが含まれている場合、このオプションを指定することで、そのコマンドは失敗（そしてそのためにトランザクション全体が失敗）します。

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

$ psql -f insert_lock.sql template1 -1
#-- or
$ psql -f insert_lock.sql template1 --single-transaction

#-- 確認用コマンド
$ psql template1 -c "\d pgpool_catalog.insert_lock"
Table "pgpool_catalog.insert_lock"
 Column | Type | Modifiers
--------+------+-----------
 reloid | oid  | not null
Indexes:
    "insert_lock_pkey" PRIMARY KEY, btree (reloid)