Hi hackers, There are several overloaded versions of timezone() function. One version accepts timezone name and timestamptz and returns timestamp:
=# show time zone;
TimeZone
---------------
Europe/Moscow
=# select timezone('MSK', '2021-08-30 12:34:56 MSK' :: timestamptz);
timezone
---------------------
2021-08-30 12:34:56
This function is marked as IMMUTABLE and it's possible to use it in
functional indexes. I believe it's a bug. Since the function accepts
the name of the time zone, and the rules of time zones change, this
function may return different results for the same arguments in the
future. This makes it STABLE, or at least definitely not IMMUTABLE
[1]. timezone(text, timestamp), which returns timestamptz should be
STABLE as well for the same reasons.
The proposed patch (v1) fixes this.
Other versions of timezone() seem to be fine, except:
=# \df+ timezone
...
-[ RECORD 4 ]-------+---------------------------------------
Schema | pg_catalog
Name | timezone
Result data type | time with time zone
Argument data types | text, time with time zone
Type | func
Volatility | volatile
Parallel | safe
Owner | eax
Security | invoker
Access privileges |
Language | internal
Source code | timetz_zone
Description | adjust time with time zone to new zone
...
Does anyone know the reason why, unlike other versions, it's marked
VOLATILE? I attached an alternative version of the patch (v2), which
fixes this too. None of the patches includes any regression tests. As
I understand there is little reason to re-check the volatility stated
in pg_proc.dat in runtime.
[1]: https://www.postgresql.org/docs/current/xfunc-volatility.html
--
Best regards,
Aleksander Alekseev
v1-0001-timezone_volatility.patch
Description: Binary data
v2-0001-timezone_volatility.patch
Description: Binary data
