BUG #13812: 'now' acting differently than now() in function

From: darin(at)darinhoward(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13812: 'now' acting differently than now() in function
Date: 2015-12-10 19:52:22
Message-ID: 20151210195222.2683.3870@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13812
Logged by: Darin Howard
Email address: darin(at)darinhoward(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message John R Pierce 2015-12-10 20:52:39 Re: BUG #13811: Default ordering colums
Previous Message Paul Moore 2015-12-10 17:27:08 Re: BUG #13788: compile error in generic_msvc.h