BUG #13812: 'now' acting differently than now() in function
The following bug has been logged on the website:
Bug reference: 13812
Logged by: Darin Howard
Email address: darin@darinhoward.com
PostgreSQL version: 9.4.5
Operating system: Linux
Description:
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Thu, Dec 10, 2015 at 12:52 PM, <darin@darinhoward.com> wrote:
The following bug has been logged on the website:
Bug reference: 13812
Logged by: Darin Howard
Email address: darin@darinhoward.com
PostgreSQL version: 9.4.5
Operating system: Linux
Description:
[...]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;
WithUPDATE test_date_bug SET date2 = now() WHERE id = id_param;
Sets new date as I would expect:
*Quick answer: *'now' is a string literal that is resolved once and
embedded (i.e., parsed) into the query for the life of the session due to
caching; now() is a function that gets executed each time the query is
executed.
*Additional considerations:*
I may be missing a nuance here, this could
maybe use better documentation surrounding implications, but the basic
answer is that 'now' is a string literal that ends up getting resolved at
parse time during the first invocation of the function and then its value
is cached and re-used during subsequent executions. The "now()" volatile
function is instead left alone by the parser and instead is invoked during
each execution of the UPDATE.
I am going from memory here since I cannot locate the documentation that
points out this fact...
The same behavior is seen if you trying to use "now" and "now()" as part of
the column default (SQL functions that can be written without parens but
that can only be functions do not exhibit this behavior). Using "now" you
end up getting the time the table was created instead of the time the row
was inserted;
CREATE TABLE now_test (id serial, tm timestamptz DEFAULT 'now');
INSERT INTO now_test DEFAULT VALUES; --repeat manually
SELECT * FROM now_test; --all same values for tm
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Thu, Dec 10, 2015 at 12:52 PM, <darin@darinhoward.com> wrote:
I would not expect the date2 on row 2 to be the same date2 as row 1.
I may be missing a nuance here, this could
maybe use better documentation surrounding implications, but the basic
answer is that 'now' is a string literal that ends up getting resolved at
parse time during the first invocation of the function and then its value
is cached and re-used during subsequent executions. The "now()" volatile
function is instead left alone by the parser and instead is invoked during
each execution of the UPDATE.
I am going from memory here since I cannot locate the documentation that
points out this fact...
There's some discussion near the bottom of this page:
http://www.postgresql.org/docs/9.4/static/plpgsql-implementation.html
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs