Thread: 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
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: > =E2=80=8B[...]=E2=80=8B > > I would not expect the date2 on row 2 to be the same date2 as row 1. > > Replacing > > UPDATE test_date_bug SET date2 =3D 'now' WHERE id =3D id_param; > With > > UPDATE test_date_bug SET date2 =3D now() WHERE id =3D id_param; > Sets new date as I would expect: > *=E2=80=8BQuick answer: *'now' is a string literal that is resolved once an= d 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.=E2=80=8B *Additional considerations:*=E2=80=8B =E2=80=8BI 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