transactions - Issue with PostgreSQL: 'now' keeps returning same old value -

i have old web app, relevant current stack is: java 8, tomcat 7, apache commons dbcp 2.1, spring 2.5 (for transactions), ibatis, postgresql 9.2 postgresql-9.4.1208.jar

part of code inserts new records in incidents table, field begin_date (timestamp(3) time zone) creation timestamp, filled now:

    insert incidents     (...., begin_date,    )     values     (..., 'now' ....) 

all executed via ibatis, transactions managed programatically via spring, connections acquired via dbcp pool. webapp (actually pair, clientside , backoffice, share of code , jars) has been working since years.

lately, perhaps after libraries updates , reorganization (nothing important, seemed), i've been experiencing (intermitent, hard reproduce) nasty problem: now seems freeze, , begins returning same "old" value. then, many records appear same creation timestamp, hours or days ago:

db=# select 'now'::timestamptz;         timestamp -------------------------  2016-06-10 21:59:03.637+00  db=#  select rid,begin_date incidents order rid desc limit 6;   rid  |         begin_date -------+----------------------------  85059 | 2016-06-08 00:11:06.503+00  85058 | 2016-06-08 00:11:06.503+00  85057 | 2016-06-08 00:11:06.503+00  85056 | 2016-06-08 00:11:06.503+00  85055 | 2016-06-08 00:11:06.503+00  85054 | 2016-06-08 00:11:06.503+00 

(all above records created minutes before 2016-06-10 21:50)

how can happen? might problem related transactions and/or connection pooling, can't figure out what. know 'now()' alias of transaction_timestamp(), returns time @ start of transaction. suggest transaction not closed, , records inserts above written (unintentionally) in single long transaction. looks rather incredible me.

first, can insert new record (via webapp) and, using psql console, see has been written same begin_date (if transaction uncommited, should not see new record, have default serialization level).

furthermore, pg_stat_activity view shows idle connections.

any cues?

there's constant (special timestamp value) 'now'.
, there's function now().

the fact mixing them freely suggests unaware of all-important difference. the manual:

special values

postgresql supports several special date/time input values convenience, shown in table 8-13. values infinity , -infinity specially represented inside system , displayed unchanged; others notational shorthands converted ordinary date/time values when read. (in particular, now , related strings converted specific time value read.) of these values need enclosed in single quotes when used constants in sql commands.

bold emphasis mine.

and (like mentioned already), quoting the manual:

now() traditional postgresql equivalent transaction_timestamp().

and:

transaction_timestamp() equivalent current_timestamp

there more, read whole chapter.

now (no pun intended), since using special value instead of function, different (unexpected you) behavior prepared statements.

consider demo:

test=# begin; begin test=# prepare foo test-# select timestamptz 'now' now_constant, now() now_function; prepare test=# execute foo;          now_constant          |         now_function -------------------------------+-------------------------------  2016-06-11 03:09:05.622783+02 | 2016-06-11 03:09:05.622783+02 -- identical (1 row)  test=# commit; commit test=# execute foo;          now_constant          |         now_function -------------------------------+------------------------------  2016-06-11 03:09:05.622783+02 | 2016-06-11 03:10:00.92488+02  -- different! (1 row)

while run both in same transaction, 'now' , now() produce same value. prepared statement designed last duration of session (possibly across many transactions). next time execute prepared statement, you'll see difference.

in other words: 'now' implements "early binding", while now() implements "late binding".

you may have introduced prepared statements , / or connection pooling (which can preserve prepared statements longer period of time) - both ideas. hidden problem in insert starts kicking.

the "idle connections" see indicate much: connections stay open, preserving prepared statements.

in short: use now().

alternatively, set column default of begin_date now() (not 'now'!) , don't mention column in insert. "creation timestamp" saved automatically.