どうも 村上です。
最近PostgreSQLを使ってて「おぅ！！」ってなった内容です。
トランザクション中におけるnow()関数について
データコンバートをするためにSQLをちょこちょこ実行してたら不思議なことが起こりました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
postgres=# begin; BEGIN postgres=# select now(); now ------------------------------- 2013-08-05 10:46:14.183706+09 (1 行) postgres=# select now(); now ------------------------------- 2013-08-05 10:46:14.183706+09 (1 行) postgres=# select now(); now ------------------------------- 2013-08-05 10:46:14.183706+09 (1 行) postgres=# select now(); now ------------------------------- 2013-08-05 10:46:14.183706+09 (1 行) |
全部同じ時間が返る！？
※ 高速でコマンドを叩いているわけではないです！！
こんな記述が
9.9. 日付/時刻関数と演算子
一部抜粋
これらの関数は、現在のトランザクションの開始時刻を返します。 この値は、トランザクションが実行されている間は変化しません。 これは、次の機能を検討した結果です。 単一トランザクションで、"current"時間を一貫性を持った表現を行うことができるようにすることを目的とし、このため、同一トランザクションで何回変更を行っても同一のタイムスタンプを生成します。
そして注意書きに
注意: 他のデータベースシステムでは、これらの値をより頻繁に増加させることがあります。
ともありました。
トランザクション中に現在時刻を取得するには？
PostgreSQLにはいろいろと時間を取得する関数があります。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 10:58:14.271624+09 now | 2013-08-05 10:58:14.271624+09 transaction_timestamp | 2013-08-05 10:58:14.271624+09 statement_timestamp | 2013-08-05 10:58:14.271624+09 clock_timestamp | 2013-08-05 10:58:14.271765+09 |
マニュアルによると、
CURRENT_TIMESTAMP = now() = transaction_timestamp()
です。
また
statement_timestamp()は現在の文の実行開始時刻を返す
clock_timestamp()は実際の現在時刻を返す
とあります。
ではやってみよう！！
トランザクションなし
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 14:55:08.258797+09 now | 2013-08-05 14:55:08.258797+09 transaction_timestamp | 2013-08-05 14:55:08.258797+09 statement_timestamp | 2013-08-05 14:55:08.258797+09 clock_timestamp | 2013-08-05 14:55:08.25894+09 postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 14:55:12.553793+09 now | 2013-08-05 14:55:12.553793+09 transaction_timestamp | 2013-08-05 14:55:12.553793+09 statement_timestamp | 2013-08-05 14:55:12.553793+09 clock_timestamp | 2013-08-05 14:55:12.55396+09 postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 14:55:16.442793+09 now | 2013-08-05 14:55:16.442793+09 transaction_timestamp | 2013-08-05 14:55:16.442793+09 statement_timestamp | 2013-08-05 14:55:16.442793+09 clock_timestamp | 2013-08-05 14:55:16.442935+09 postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 14:55:20.489792+09 now | 2013-08-05 14:55:20.489792+09 transaction_timestamp | 2013-08-05 14:55:20.489792+09 statement_timestamp | 2013-08-05 14:55:20.489792+09 clock_timestamp | 2013-08-05 14:55:20.489933+09 postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 14:55:24.474795+09 now | 2013-08-05 14:55:24.474795+09 transaction_timestamp | 2013-08-05 14:55:24.474795+09 statement_timestamp | 2013-08-05 14:55:24.474795+09 clock_timestamp | 2013-08-05 14:55:24.474937+09 |
間隔は村上体内時計の5秒です。
clock_timestampとそれ以外で時間が0.0002秒ほど違います。
トランザクションあり
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
postgres=# begin; BEGIN postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 14:58:09.65083+09 now | 2013-08-05 14:58:09.65083+09 transaction_timestamp | 2013-08-05 14:58:09.65083+09 statement_timestamp | 2013-08-05 14:58:11.66579+09 clock_timestamp | 2013-08-05 14:58:11.665929+09 postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 14:58:09.65083+09 now | 2013-08-05 14:58:09.65083+09 transaction_timestamp | 2013-08-05 14:58:09.65083+09 statement_timestamp | 2013-08-05 14:58:15.986794+09 clock_timestamp | 2013-08-05 14:58:15.986931+09 postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 14:58:09.65083+09 now | 2013-08-05 14:58:09.65083+09 transaction_timestamp | 2013-08-05 14:58:09.65083+09 statement_timestamp | 2013-08-05 14:58:20.282797+09 clock_timestamp | 2013-08-05 14:58:20.282935+09 postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 14:58:09.65083+09 now | 2013-08-05 14:58:09.65083+09 transaction_timestamp | 2013-08-05 14:58:09.65083+09 statement_timestamp | 2013-08-05 14:58:24.802797+09 clock_timestamp | 2013-08-05 14:58:24.802935+09 postgres=# select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp(); -[ RECORD 1 ]---------+------------------------------ CURRENT_TIMESTAMP | 2013-08-05 14:58:09.65083+09 now | 2013-08-05 14:58:09.65083+09 transaction_timestamp | 2013-08-05 14:58:09.65083+09 statement_timestamp | 2013-08-05 14:58:29.554777+09 clock_timestamp | 2013-08-05 14:58:29.554913+09 |
こちらも村上体内時計で5秒間隔です。
マニュアル通りCURRENT_TIMESTAMP、now()、transaction_timestamp()はトランザクション開始時刻を返しています。
statement_timestamp()とclock_timestamp()は実行されている時間を返しているようですが、時間が0.0002秒ほど時間が違います。
INSERTとUPDATEでは！？
SELECTをするとINSERTとUPDATEが気になりますよね？
やってみました。
|
1 2 3 4 5 6 7 8 9 10 |
postgres=# \d timestamp_table; テーブル "public.timestamp_table" 列 | 型 | 修飾語 -----------------------+-----------------------------+-------------------------------------------------------------- id | integer | not null default nextval('timestamp_table_id_seq'::regclass) current_time_stamp | timestamp without time zone | now | timestamp without time zone | transaction_timestamp | timestamp without time zone | statement_timestamp | timestamp without time zone | clock_timestamp | timestamp without time zone | |
INSERT
というテーブルの下記のSQLを流します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
insert into timestamp_table( current_time_stamp, now, transaction_timestamp, statement_timestamp, clock_timestamp) select CURRENT_TIMESTAMP, now(), transaction_timestamp(), statement_timestamp(), clock_timestamp() from GENERATE_SERIES(1, 1000000); |
以前鈴木さんが書かれていた集合関数を使いました。
PostgreSQL: 集合を返すGENERATE_SERIES関数で大量データを生成して集計する
すべてのレコードは確認できないので、それぞれのminとmaxを取ってみました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
select min(current_time_stamp) as current_time_stamp_min, max(current_time_stamp) as current_time_stamp_max, min(now) as now_min, max(now) as now_max, min(transaction_timestamp) as transaction_timestamp_min, max(transaction_timestamp) as transaction_timestamp_max, min(statement_timestamp) as statement_timestamp_min, max(statement_timestamp) as statement_timestamp_max, min(clock_timestamp) as clock_timestamp_min, max(clock_timestamp) as clock_timestamp_max from timestamp_table; -[ RECORD 1 ]-------------+--------------------------- current_time_stamp_min | 2013-08-05 15:26:51.879825 current_time_stamp_max | 2013-08-05 15:26:51.879825 now_min | 2013-08-05 15:26:51.879825 now_max | 2013-08-05 15:26:51.879825 transaction_timestamp_min | 2013-08-05 15:26:51.879825 transaction_timestamp_max | 2013-08-05 15:26:51.879825 statement_timestamp_min | 2013-08-05 15:26:51.879825 statement_timestamp_max | 2013-08-05 15:26:51.879825 clock_timestamp_min | 2013-08-05 15:26:52.030024 clock_timestamp_max | 2013-08-05 15:26:59.41853 |
clock_timestamp()とそれ以外で時間が違います。
また、clock_timestamp()のminとmaxでも値が違います。
UPDATE
続いてUPDATE
|
1 2 3 4 5 6 |
UPDATE timestamp_table SET current_time_stamp = CURRENT_TIMESTAMP, now = now(), transaction_timestamp = transaction_timestamp(), statement_timestamp = statement_timestamp(), clock_timestamp = clock_timestamp(); |
そして結果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
select min(current_time_stamp) as current_time_stamp_min, max(current_time_stamp) as current_time_stamp_max, min(now) as now_min, max(now) as now_max, min(transaction_timestamp) as transaction_timestamp_min, max(transaction_timestamp) as transaction_timestamp_max, min(statement_timestamp) as statement_timestamp_min, max(statement_timestamp) as statement_timestamp_max, min(clock_timestamp) as clock_timestamp_min, max(clock_timestamp) as clock_timestamp_max from timestamp_table; -[ RECORD 1 ]-------------+--------------------------- current_time_stamp_min | 2013-08-05 15:30:30.802788 current_time_stamp_max | 2013-08-05 15:30:30.802788 now_min | 2013-08-05 15:30:30.802788 now_max | 2013-08-05 15:30:30.802788 transaction_timestamp_min | 2013-08-05 15:30:30.802788 transaction_timestamp_max | 2013-08-05 15:30:30.802788 statement_timestamp_min | 2013-08-05 15:30:30.802788 statement_timestamp_max | 2013-08-05 15:30:30.802788 clock_timestamp_min | 2013-08-05 15:30:30.845056 clock_timestamp_max | 2013-08-05 15:30:49.26184 |
INSERTと結果が同じですね。(当然ちゃ当然。。。)
まとめ
ということで、PostgreSQLの現在時刻を取得する関数を見て来ました。
結果として、トランザクション開始時の時刻を返すものと関数実行時の時刻を返すものがあるということ。
statement_timestamp()とclock_timestamp()は同じものを返さない。(なぜかは謎。。。)
ということがわかりました。
他のDBMSではトランザクション内では現在時刻を返していたので、この仕様を知った時には驚きました。
PostgreSQLを初めて使う時には注意ですね。
Comments
ちょっと古い記事にコメントですが。
まとめに
「statement_timestamp()とclock_timestamp()は同じものを返さない。(なぜかは謎。。。)」
とありますが、仕様なので当然ですよね。名前のまんま。
statement_timestampはステートメント開始時刻なので、同一トランザクション内で2つSQLあった場合、
1と2では返す値が違うのでは？clock_timestamp()は現在時刻。
マニュアルに書いてあります。
厳密にいうと、トランザクション開始時の時刻、ステートメント開始時刻、関数実行時の時刻の3種類ですね！