How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?

by

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

More Related Contents:

  1. How do I automatically update a timestamp in PostgreSQL
  2. Keep PostgreSQL from sometimes choosing a bad query plan
  3. Copying PostgreSQL database to another server
  4. Primary keys with Apache Spark
  5. How can I put a database under git (version control)?
  6. Store query result in a variable using in PL/pgSQL
  7. Can I rollback a transaction I’ve already committed? (data loss)
  8. Kill a postgresql session/connection
  9. PostgreSQL: Drop PostgreSQL database through command line [closed]
  10. Do stored procedures run in database transaction in Postgres?
  11. Import SQL dump into PostgreSQL database
  12. how to use Blob datatype in Postgres
  13. How can I solve Postgresql SCRAM authentication problem?
  14. Convert SQLITE SQL dump file to POSTGRESQL
  15. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  16. How to login and authenticate to Postgresql after a fresh install?
  17. PostgreSQL Database Log Transaction
  18. Connect to a heroku database with pgadmin
  19. Starting and populating a Postgres container in Docker
  20. Adding a new value to an existing ENUM Type
  21. How can I import a .sql file into my Heroku postgres database?
  22. Backup/Restore a dockerized PostgreSQL database
  23. Postgres could not connect to server
  24. internationalization in JSF with ResourceBundle entries which are loaded from database
  25. Saving images: files or blobs?
  26. Still Confused About Identifying vs. Non-Identifying Relationships
  27. How to show row numbers in PostgreSQL query?
  28. How do you write a case insensitive query for both MySQL and Postgres?
  29. Predict next auto-inserted row id (SQLite)
  30. Best beginner resources for understanding the EAV database model? [closed]

Leave a Comment