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
