postgresql - Postgres now() 与函数中的 'now'

Question

遇到 now() 在 Postgres 中的函数中使用时的行为与“now”不同的问题。

drop table if exists test_date_bug;
CREATE TABLE test_date_bug
(
id serial NOT NULL,
date1 timestamp with time zone NOT NULL DEFAULT current_timestamp,
date2 timestamp with time zone NOT NULL DEFAULT 'infinity'
)
WITH (
OIDS=FALSE
);

drop function if exists test_date_bug_function(id_param bigint); 
CREATE OR REPLACE FUNCTION test_date_bug_function(id_param bigint)
RETURNS void AS
$$ 
BEGIN 
   UPDATE test_date_bug SET date2 = 'now' WHERE id = id_param;  
END;
$$
LANGUAGE 'plpgsql' VOLATILE
SECURITY DEFINER
SET search_path = public, pg_temp;  

insert into test_date_bug DEFAULT VALUES; 
insert into test_date_bug DEFAULT VALUES;  
insert into test_date_bug DEFAULT VALUES; 

select 1 from test_date_bug_function(1);

等一下

select 1 from test_date_bug_function(2);

结果:

select * from test_date_bug; 
id |             date1             |             date2             
----+-------------------------------+-------------------------------
3 | 2015-12-10 12:42:01.931554-06 | infinity
1 | 2015-12-10 12:42:01.334465-06 | 2015-12-10 12:42:09.491183-06
2 | 2015-12-10 12:42:01.335665-06 | 2015-12-10 12:42:09.491183-06
(3 rows)

我不希望第 2 行的 date2 与第 1 行的 date2 相同。

替换

 UPDATE test_date_bug SET date2 = 'now' WHERE id = id_param;

与

 UPDATE test_date_bug SET date2 = now() WHERE id = id_param;

按照我的预期设置新日期:

select * from test_date_bug; 
id |             date1             |             date2             
----+-------------------------------+-------------------------------
3 | 2015-12-10 12:43:29.480242-06 | infinity
1 | 2015-12-10 12:43:28.451195-06 | 2015-12-10 12:43:38.496625-06
2 | 2015-12-10 12:43:28.451786-06 | 2015-12-10 12:43:43.447715-06

想法？

Answer 1

这不是错误，这是一个特性……这里有两点。

1. “现在”的替代

   让我们看一下文档 ( Date/Time Functions and Operators ):

   All the date/time data types also accept the special literal value now to specify the current date and time (again, interpreted as the transaction start time). Thus, the following three all return the same result:

   SELECT CURRENT_TIMESTAMP;

   SELECT now();

   SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT

   Tip: You do not want to use the third form when specifying a DEFAULT clause while creating a table. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used! The first two forms will not be evaluated until the default value is used, because they are function calls. Thus they will give the desired behavior of defaulting to the time of row insertion.

   因此 'now' 在解析时被转换为时间戳。

2. 准备好的语句

   好的，但是它在函数方面意味着什么？很容易证明每次调用时都会解释一个函数:

   t=# create function test() returns timestamp as $$
   begin
    return 'now';
   end;
   $$ language plpgsql;
   CREATE FUNCTION
   
   t=# select test();
              test            
   ----------------------------
    2015-12-11 11:14:43.479809
   (1 row)
   
   t=# select test();
              test            
   ----------------------------
    2015-12-11 11:14:47.350266
   (1 row)
   

   在此示例中，'now' 的行为符合您的预期。

   有什么区别？您的函数使用 SQL 语句，而 test() 不使用。让我们再次查看文档 ( PL/pgSQL Plan Caching ):

   As each expression and SQL command is first executed in the function, the PL/pgSQL interpreter parses and analyzes the command to create a prepared statement.

   这里 ( Prepare Statement ):

   PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.

   因此，在解析准备好的语句时，'now' 被转换为时间戳。让我们通过在函数外部创建准备好的语句来证明这一点:

   t=# prepare s(integer) as UPDATE test_date_bug SET date2 = 'now' WHERE id = $1;
   PREPARE
   
   t=# execute s(1);
   UPDATE 1
   t=# execute s(2);
   UPDATE 1
   
   t=# select * from test_date_bug;
    id |             date1             |             date2
   ----+-------------------------------+-------------------------------
     3 | 2015-12-11 11:01:38.491656+03 | infinity
     1 | 2015-12-11 11:01:37.91818+03  | 2015-12-11 11:40:44.339623+03
     2 | 2015-12-11 11:01:37.931056+03 | 2015-12-11 11:40:44.339623+03
   (3 rows)
   

事情就是这样。 'now' 被转换为时间戳一次(当准备好的语句被解析时)，并且 now() 被调用了两次。