PostgreSQL 函数/存储过程 CURRENT_TIMESTAMP 不会改变 [英] PostgreSQL Function / Stored Procedure CURRENT_TIMESTAMP does not change

问题描述

我想跟踪函数内的执行时间.例如，我有以下状态跟踪表:

 CREATE TABLE status_table(run_id 数字非空，start_ts 时间戳(6)不带时区 NOT NULL，end_ts 时间戳(6) 不带时区 NOT NULL)和 (OIDS=FALSE)随机分布；

我有以下示例函数，它使用 PG_SLEEP(5) 来模拟我的函数实际执行的操作:

CREATE OR REPLACE FUNCTION wrapper_func(p_run_id numeric)返回数字 AS$BODY$宣布tmp_start_ts 时间戳(6) 不带时区；tmp_end_ts 时间戳(6) 不带时区；开始tmp_start_ts := CURRENT_TIMESTAMP;执行 PG_SLEEP(5);tmp_end_ts := CURRENT_TIMESTAMP;INSERT INTO status_table (run_id,start_ts,end_ts) VALUES (p_run_id,tmp_start_ts,tmp_end_ts);返回 0;结尾;$BODY$语言 plpgsql 易失性；

然后我调用了这个函数 3 次，每次调用之间等待大约 20 秒:

SELECT * FROM wrapper_func(1);SELECT * FROM wrapper_func(2);SELECT * FROM wrapper_func(3);

但是，当我查看 status_table 中的结果时，时间戳都相同:

SELECT * FROM status_table ORDER BY run_id;run_id|start_ts|end_ts1|2015-01-18 17:54:43.641294|2015-01-18 17:54:43.6412942|2015-01-18 17:54:43.641294|2015-01-18 17:54:43.6412943|2015-01-18 17:54:43.641294|2015-01-18 17:54:43.641294

有人可以指出我做错了什么或实现此目的的其他方法吗?

谢谢

解决方案

在你的函数中使用 clock_timestamp() 而不是 CURRENT_TIMESTAMP

CURRENT_TIMESTAMP 将根据当前事务的开始时间返回值.

请参阅文档

I want to track the execution time within a function. As an example, I have the following status tracking table:

    CREATE TABLE status_table
    (
    run_id numeric NOT NULL,
      start_ts timestamp(6) without time zone NOT NULL,
      end_ts timestamp(6) without time zone NOT NULL
    )
WITH (
  OIDS=FALSE
)
DISTRIBUTED RANDOMLY;

I have the following example function that uses PG_SLEEP(5) as the simulation of what my function will actually do:

CREATE OR REPLACE FUNCTION wrapper_func(p_run_id numeric)
  RETURNS numeric AS
$BODY$
Declare  
tmp_start_ts timestamp(6) without time zone;
tmp_end_ts timestamp(6) without time zone;

BEGIN
tmp_start_ts := CURRENT_TIMESTAMP;
PERFORM PG_SLEEP(5);
tmp_end_ts := CURRENT_TIMESTAMP;
INSERT INTO status_table (run_id,start_ts,end_ts) VALUES (p_run_id,tmp_start_ts,tmp_end_ts);
RETURN 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

I then called this function three times, waiting about 20 seconds between each invocation:

SELECT * FROM wrapper_func(1);
SELECT * FROM wrapper_func(2);
SELECT * FROM wrapper_func(3);

However, when I look at the results in my status_table, the timestamps are all the same:

SELECT * FROM status_table ORDER BY run_id;

run_id|start_ts|end_ts
1|2015-01-18 17:54:43.641294|2015-01-18 17:54:43.641294
2|2015-01-18 17:54:43.641294|2015-01-18 17:54:43.641294
3|2015-01-18 17:54:43.641294|2015-01-18 17:54:43.641294

Could someone point out what I'm doing wrong or another approach to accomplish this?

Thanks

解决方案

use clock_timestamp() instead of CURRENT_TIMESTAMP in your function

CURRENT_TIMESTAMP will return values based on the start time of the current transaction.

see document

这篇关于PostgreSQL 函数/存储过程 CURRENT_TIMESTAMP 不会改变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！