0

I have a simple immutable function:

CREATE OR REPLACE FUNCTION public.immutable_now()
  RETURNS timestamp with time zone
IMMUTABLE AS
$$
  SELECT now()::timestamptz;
$$
LANGUAGE 'sql';

When I'm running it from java application via jdbc, the results at some point are cached (SELECT public.immutable_now()):

+--------------------------------+
|immutable_now                   |
+--------------------------------+
|2018-05-29T09:12:06.424651+02:00|
+--------------------------------+
2
+--------------------------------+
|immutable_now                   |
+--------------------------------+
|2018-05-29T09:12:06.525927+02:00|
+--------------------------------+
3
+--------------------------------+
|immutable_now                   |
+--------------------------------+
|2018-05-29T09:12:06.529268+02:00|
+--------------------------------+
4
+--------------------------------+
|immutable_now                   |
+--------------------------------+
|2018-05-29T09:12:06.531329+02:00|
+--------------------------------+
5
+--------------------------------+
|immutable_now                   |
+--------------------------------+
|2018-05-29T09:12:06.533217+02:00|
+--------------------------------+
6
+--------------------------------+
|immutable_now                   |
+--------------------------------+
|2018-05-29T09:12:06.533217+02:00|
+--------------------------------+

After 5th time it's starting returning the same result. It's expected, that postgresql try to optimize the query.

But then I would like to reproduce this behaviour via psql. I'm opening psql session and start repeating the query:

# select immutable_now();
immutable_now
-------------------------------
2018-05-30 09:18:35.507229+00
(1 row)

# select immutable_now();
immutable_now
-------------------------------
2018-05-30 09:18:36.495322+00
(1 row)

# select immutable_now();
immutable_now
-------------------------------
2018-05-30 09:18:36.931542+00
(1 row)

# select immutable_now();
immutable_now
-------------------------------
2018-05-30 09:18:37.307167+00
(1 row)

# select immutable_now();
immutable_now
-------------------------------
2018-05-30 09:18:37.637495+00
(1 row)

# select immutable_now();
immutable_now
-------------------------------
2018-05-30 09:18:38.172902+00

...and it's imposible to reproduce it on this level. Is there any explanation why I can't do it via psql? Blind guess: statistics are not collected ?

  • 1
    now() will return the same value inside the same transaction. I'd start looking into that direction - JDBC can cache prepared statements, but not their returned sets (AFAIK, at least). – dezso May 30 '18 at 10:16
  • It's separate transactions (1 call = 1 transaction). If this is the same transaction then it will return the same value from the beginning, not after 5th call. – chris May 30 '18 at 10:38
  • 2
    Just because your marking of the function as immutable means that Postgres can cache the result, doesn't mean that it will cache the result. – Colin 't Hart May 30 '18 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.