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
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 |