[pgsql-jp: 28724] Re: current transaction is aborted について

[Satoshi Nagayasu]

永安です。

SAITO Masaru <daisaito ＠ lares.dti.ne.jp> wrote:
> 齋藤＠横浜です。

> > COMMITのタイミングとしては、100件単位でcommitするように
> > しているのですが、1万5000件を超えた所から、下記のようなエラーが出力されます。

これは、確実に「15,000件」なのでしょうか？
まずそれを確認すること、あとはsyslogなりを見ることです。
そして、どのレコードでエラーが生じているのかを確認することです。

> > NOTICE:  current transaction is aborted, queries ignored until end of transaction block

とあるのを見ると、1ブロック100件のどこかでエラーが出て、
以降のINSERTが無視されているようです。

> 件数はともかくとして、100件単位でcommitというのはどうなんでしょう。
> **99件目でエラーになった場合その前の98件の処理は全部捨てるんですか？
> また、失敗した行は捨てるというなら、autocommitでもよいのでは？
> 全てが成功しなければいけないなら、エラーが発生した行でエラーメッセージを
> 出して、処理を止める。(die or exit)
> でもってそのレコードを直して、retry and stop and retry ...
> 
> 
> # １レコードのボリュームが大きくないので数万件でも、全件commitしても
> # たいしてパフォーマンスには影響しないと思います。

ここら辺は、判断する前に実際に試してみた方がいいです。

以前（7.1の頃？）あるデータのINSERTで試してみたときには、
autocommitではなくトランザクションブロックにしないと、
確かに時間がかかりすぎて話になりませんでした。
確か数十万レコードのINSERTだったと思います。

で、ずっとそう思っていたわけですが、最近UPDATEのパフォーマンスを
計測してみて愕然としました。

私がやったテストは以下のようなもので、
---------------------------------------------------------------------
drop table update_test;
create table update_test ( uid integer primary key, counter integer not null );
insert into update_test values ( 1, 0 );
-- begin;
update update_test set counter = 0 where uid = 1;
update update_test set counter = 1 where uid = 1;
update update_test set counter = 2 where uid = 1;
...
update update_test set counter = 9997 where uid = 1;
update update_test set counter = 9998 where uid = 1;
update update_test set counter = 9999 where uid = 1;
-- commit;
---------------------------------------------------------------------
という1万回のupdateをトランザクションでくるんだ場合と、
autocommitで実行した場合です。

以下は、7.2.1の場合ですが、
---------------------------------------------------------------------
{32}snaga ＠ penguin:/tmp% time psql -f update.sql > /dev/null
0.780u 0.490s 7:04.23 0.2%      0+0k 0+0io 232pf+0w
{33}snaga ＠ penguin:/tmp%  ; time psql -f update_autocommit.sql > /dev/null
0.660u 0.450s 7:11.42 0.2%      0+0k 0+0io 232pf+0w
---------------------------------------------------------------------
あまり変わらないようです。

ただし、7.3ベースになるとautocommitした方が速くて、
---------------------------------------------------------------------
[snaga ＠ ia32-02s pgsql]$ time psql -f update.sql  > /dev/null 
real    2m0.007s
user    0m0.453s
sys     0m0.309s
[snaga ＠ ia32-02s pgsql]$ time psql -f update_autocommit.sql  > /dev/null 
real    0m23.056s
user    0m0.406s
sys     0m0.377s
[snaga ＠ ia32-02s pgsql]$ head update_autocommit.sql 
---------------------------------------------------------------------
となります。1回コミットするより10000回コミットした方が速い理由は、
よく分かってません（7.2.1とは違うマシンです）。

他のRDBMSは知らないし迂闊なことは言えないのですが、
PostgreSQLって単純なアプリケーションでもボトルネックとなる場所が
ガラッと変わるし、バージョンが変わると特性も大きく変わる気がします。

もっと定量的かつ実装に則した調査をしないと何とも言えないのですが、
今さら7.2.1調べるのもなぁ、という感じです。

-- 
NAGAYASU Satoshi <snaga ＠ snaga.org>