Postgres now（）vs'now'in function [英] Postgres now() vs 'now' in function
问题描述
drop表如果存在test_date_bug;
CREATE TABLE test_date_bug
（
id serial NOT NULL，
date1 timestamp with time zone NOT NULL DEFAULT current_timestamp，
date2带有时区的时间戳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
更新test_date_bug SET date2 ='now'WHERE id = id_param;
END;
$$
LANGUAGE'plpgsql'VOLATILE
安全定义
SET search_path = public，pg_temp;
插入test_date_bug DEFAULT VALUES;
insert into test_date_bug DEFAULT VALUES;
insert into test_date_bug DEFAULT VALUES;
从test_date_bug_function（1）中选择1;
等待几秒钟
select test from test_date_bug_function（2）;
结果：
code> select * from test_date_bug;
id | date1 | date2
---- + ------------------------------- + -------- -----------------------
3 | 2015-12-10 12：42：01.931554-06 |无限
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行）
I不会期望第2行的date2与第1行的日期相同。
替换
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 |无限
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
想法？
这不是一个bug，这是一个功能...这里有两点。
-
替换'now'
我们来看看文档（日期/时间函数和运算符）：
所有日期/时间数据类型也接受现在的特殊字面值
来指定当前日期和时间（再次解释为
事务开始时间）。因此，以下三个都返回相同的
结果：
SELECT CURRENT_TIMESTAMP;
（）;
选择TIMESTAMP'now'; - 不正确使用DEFAULT
提示：在创建表时指定DEFAULT子句时，不想使用第三个表单。一旦常量被解析，系统将立即转换为
一个时间戳，这样当需要
默认值时，将使用创建表的时间！
前两个表单将不被评估，直到默认值为
，因为它们是函数调用。因此，他们将给出所需的
行为，默认为插入行。
所以
-
准备的语句
好的，但是功能上有什么意思？很容易证明每次调用它时都会解释一个函数：
t =＃create function test（）返回时间戳为$$
begin
return'now';
结束
$$ 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行）
在这个例子中，
有什么区别？你的函数使用SQL语句，而test（）没有。让我们再次查看文档（ PL / pgSQL计划缓存）：
由于函数中首先执行每个表达式和SQL命令，所以
PL / pgSQL解释器解析并分析命令以创建
预备语句。
而这里（准备声明）：
PREPARE创建一个准备好的语句。一个准备好的语句是一个
的服务器端对象，可以用来优化性能。当执行
PREPARE语句时，将分析指定的语句，分析
并重写。当EXECUTE命令随后发出
时，准备好的语句将被计划并执行。这个分工
避免重复解析分析工作，同时允许
执行计划取决于提供的具体参数值。
因此，当准备语句被解析时，
'now'已转换为时间戳。让我们通过在函数之外创建一个准备的语句来演示这一点：t =＃prepare s（integer）as UPDATE test_date_bug SET date2 ='now'WHERE id = $ 1;
PREPARE
t =＃执行s（1）;
更新1
t =＃execute s（2）;
更新1
t =＃select * from test_date_bug;
id | date1 | date2
---- + ------------------------------- + -------- -----------------------
3 | 2015-12-11 11：01：38.491656 + 03 |无限
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行）
这是发生了什么。 'now'被转换为时间戳一次（当准备好的语句被解析时），而 now（）被调用了两次。
Running into an issue where now() behaves different than 'now' when used in a function in Postgres.
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);
wait a couple seconds
select 1 from test_date_bug_function(2);
Results:
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)
I would not expect the date2 on row 2 to be the same date2 as row 1.
Replacing
UPDATE test_date_bug SET date2 = 'now' WHERE id = id_param;
With
UPDATE test_date_bug SET date2 = now() WHERE id = id_param;
Sets new date as I would expect:
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
Thoughts ?
It's not a bug, it's a feature... There are two points here.
Substitution of 'now'
Let's have a look into the documentation (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.
So
'now'is converted to a timestamp at parse time.Prepared statements
Okay, but what does it mean in regard to functions? It's easy to demonstrate that a function is interpreted each time you call it:
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)In this example
'now'behaves as you expected.What is the difference? Your function uses SQL statements, and test() does not. Lets look into the documentation again (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.
And here (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.
Hence
'now'was converted to a timestamp when prepared statement was parsed. Let's demonstrate this by creating a prepared statement outside of a function: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)
That's what happend. 'now' was converted to a timestamp once (when prepared statement was parsed), and now() was called twice.
这篇关于Postgres now（）vs'now'in function的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
