9

We wrote a function get_timestamp() defined as 

CREATE OR REPLACE FUNCTION get_timestamp()
  RETURNS integer AS
$$
SELECT (FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 10) - 13885344000)::int;
$$
LANGUAGE SQL;

This was used on INSERT and UPDATE to enter or edit a value in a created and modified field in the database record. However, we found when adding or updating records consecutively it was returning the same value.

On inspecting the function in pgAdmin III we noted that on running the SQL to build the function the key word IMMUTABLE had been injected after the LANGUAGE SQL statement. The documentation states that the default is VOLATILE (If none of these appear, VOLATILE is the default assumption) so I am not sure why IMMUTABLE was injected, however, changing this to STABLE has solved the issue of repeated values.

NOTE: As stated in the accepted answer, IMMUTABLE is never added to a function by pgAdmin or Postgres and must have been added during development.

I am guessing what was happening was that this function was being evaluated and the result was being cached for optimization, as it was marked IMMUTABLE indicating to the Postgres engine that the return value should not change given the same (empty) parameter list. However, when not used within a trigger, when used directly in the INSERT statement, the function would return a distinct value FIVE times before then returning the same value from then on. Is this due to some optimisation algorithm that says something like "If an IMMUTABLE function is used more that 5 times in a session, cache the result for future calls"?

Any clarification on how these keywords should be used in Postgres functions would be appreciated. Is STABLE the correct option for us given that we use this function in triggers, or is there something more to consider, for example the docs say:

(It is inappropriate for AFTER triggers that wish to query rows modified by the current command.)

But I am not altogether clear on why.

Russell Ormes
  • 525
  • 8
  • 22
  • 3
    Have you read [Function Volatility Categories](http://www.postgresql.org/docs/9.4/static/xfunc-volatility.html)? – Mike Sherrill 'Cat Recall' Feb 17 '15 at 20:03
  • I can't reproduce this in 9.3; the function is created as `VOLATILE`, and behaves accordingly. `VOLATILE` is the correct category here, because `clock_timestamp()` is `VOLATILE`. – Nick Barnes Feb 17 '15 at 21:09
  • @MikeSherrill'CatRecall' Thanks for the link, that does explain things well. Still not sure why it worked 5 times as an IMMUTABLE function though. – Russell Ormes Feb 17 '15 at 21:52
  • @NickBarnes Yes, I am beginning to suspect it was coded like that, as it is rather strange that it would not default to the default (if you get what I mean). So you are suggesting that any function that calls another function should be set at the same volatility as the contained function? – Russell Ormes Feb 17 '15 at 21:55

1 Answers1

9

The key word IMMUTABLE is never added automatically by pgAdmin or Postgres. Whoever created or replaced the function did that.

The correct volatility for the given function is VOLATILE (also the default), not STABLE - or it wouldn't make sense to use clock_timestamp() which is VOLATILE in contrast to now() or CURRENT_TIMESTAMP which are STABLE: those return the same timestamp within the same transaction. The manual:

clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.

The manual warns that function volatility STABLE ...

is inappropriate for AFTER triggers that wish to query rows modified by the current command.

.. because repeated evaluation of the trigger function can return different results for the same row. So, not STABLE.

You ask:

Do you have an idea as to why the function returned correctly five times before sticking on the fifth value when set as IMMUTABLE?

The Postgres Wiki:

With 9.2, the planner will use specific plans regarding to the parameters sent (the query will be planned at execution), except if the query is executed several times and the planner decides that the generic plan is not too much more expensive than the specific plans.

Bold emphasis mine. Doesn't seem to make sense for an IMMUTABLE function without input parameters. But the false label is overridden by the VOLATILE function in the body (voids function inlining): a different query plan can still make sense. Related:

Aside

trunc() is slightly faster than floor() and does the same here, since positive numbers are guaranteed:

SELECT (trunc(EXTRACT(EPOCH FROM clock_timestamp()) * 10) - 13885344000)::int
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I did read the manual and was asking for clarification. I was thinking that the warning about STABLE volatility was referring to functions called within a trigger, not only the trigger function itself, but from your comment I see I was mistaken. Thanks for clearing it up, sorry if my lack of experience in these matters irks you. – Russell Ormes Feb 18 '15 at 03:24
  • @NectarSoft: Didn't mean to imply you didn't read it, just wanted to stress the importance of that link. You've obviously read the manual, you even quoted it. – Erwin Brandstetter Feb 18 '15 at 03:28
  • Do you have an idea as to why the function returned correctly five times before sticking on the fifth value when set as IMMUTABLE? – Russell Ormes Feb 18 '15 at 03:28
  • @N: I may have an idea depending on your version of Postgres. My guess is 9.3? – Erwin Brandstetter Feb 18 '15 at 03:30
  • We updated to 9.4 after we started as we are working with JSON in the database and wanted some recent functionality. So we had this issue while using the function with 9.4 – Russell Ormes Feb 18 '15 at 03:34
  • @NectarSoft: I added a bit to the answer. – Erwin Brandstetter Feb 18 '15 at 03:40
  • I appreciate you clarifying the issue. I had not come across function volatility before this case. My previous experience was with DB2 stored procedures and it didn't come up. I now see I should assess all the functions from our project and decide if some optimisation is appropriate. I also see that nested functions with different volatility can affect behaviour. I still don't see why in theory a trigger could not be IMMUTABLE though. Is it just that it is very uncommon? – Russell Ormes Feb 18 '15 at 13:54
  • I will take on your advice about using `trunc()`, thanks – Russell Ormes Feb 18 '15 at 13:56
  • what is the default category if omitted? – droid192 Apr 09 '20 at 19:55
  • @qrtLs: `VOLATILE`. – Erwin Brandstetter Apr 09 '20 at 22:22