Thread: Wrong provolatile value for to_timestamp (1 argument)
I found that provolatile attribute of to_timestamp in pg_proc is wrong: test=# select provolatile, proargtypes from pg_proc where proname = 'to_timestamp' and proargtypes[0] = 701; provolatile | proargtypes -------------+------------- i | 701 (1 row) 'i' (immutable) is clearly wrong since the function's return value can be changed depending on the time zone settings. Actually the manual says functions depending on time zone settings should be labeled STABLE. https://www.postgresql.org/docs/14/xfunc-volatility.html "A common error is to label a function IMMUTABLE when its results depend on a configuration parameter. For example, a function that manipulates timestamps might well have results that depend on the TimeZone setting. For safety, such functions should be labeled STABLE instead." It's intersting that two arguments form of to_timestamp has correct attribute value ('s': stable) for provolatile in pg_proc. Do we want to fix this for PG16? I think it's too late for 15. Best reagards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
On Tue, 2022-07-05 at 17:29 +0900, Tatsuo Ishii wrote: > I found that provolatile attribute of to_timestamp in pg_proc is > wrong: > > test=# select provolatile, proargtypes from pg_proc where proname = 'to_timestamp' and proargtypes[0] = 701; > provolatile | proargtypes > -------------+------------- > i | 701 > (1 row) > > 'i' (immutable) is clearly wrong s Are you sure? I'd say that "to_timestamp(double precision)" always produces the same timestamp for the same argument. What changes with the setting of "timezone" is how that timestamp is converted to a string, but that's a different affair. Yours, Laurenz Albe
> Are you sure? I'd say that "to_timestamp(double precision)" always > produces the same timestamp for the same argument. What changes with > the setting of "timezone" is how that timestamp is converted to a > string, but that's a different affair. Of course the internal representation of timestamp with time zone data type is not affected by the time zone setting. But why other form of to_timestamp is labeled as stable? If your theory is correct, then other form of to_timestamp shouldn't be labeled immutable as well? Best reagards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
On Tue, 2022-07-05 at 19:37 +0900, Tatsuo Ishii wrote:
> > Are you sure? I'd say that "to_timestamp(double precision)" always
> > produces the same timestamp for the same argument. What changes with
> > the setting of "timezone" is how that timestamp is converted to a
> > string, but that's a different affair.
>
> Of course the internal representation of timestamp with time zone data
> type is not affected by the time zone setting. But why other form of
> to_timestamp is labeled as stable? If your theory is correct, then
> other form of to_timestamp shouldn't be labeled immutable as well?
The result of the two-argument form of "to_timestamp" can depend on
the setting of "lc_time":
test=> SET lc_time = 'en_US.utf8';
SET
test=> SELECT to_timestamp('2022-July-05', 'YYYY-TMMonth-DD');
to_timestamp
════════════════════════
2022-07-05 00:00:00+02
(1 row)
test=> SET lc_time = 'de_DE.utf8';
SET
test=> SELECT to_timestamp('2022-July-05', 'YYYY-TMMonth-DD');
ERROR: invalid value "July-05" for "Month"
DETAIL: The given value did not match any of the allowed values for this field.
Yours,
Laurenz Albe
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Tue, 2022-07-05 at 19:37 +0900, Tatsuo Ishii wrote:
>> Of course the internal representation of timestamp with time zone data
>> type is not affected by the time zone setting. But why other form of
>> to_timestamp is labeled as stable? If your theory is correct, then
>> other form of to_timestamp shouldn't be labeled immutable as well?
> The result of the two-argument form of "to_timestamp" can depend on
> the setting of "lc_time":
It also depends on the session's timezone setting, in a way that
the single-argument form does not.
regression=# show timezone;
TimeZone
------------------
America/New_York
(1 row)
regression=# select to_timestamp(0);
to_timestamp
------------------------
1969-12-31 19:00:00-05
(1 row)
regression=# select to_timestamp('1970-01-01', 'YYYY-MM-DD');
to_timestamp
------------------------
1970-01-01 00:00:00-05
(1 row)
regression=# set timezone = 'utc';
SET
regression=# select to_timestamp(0);
to_timestamp
------------------------
1970-01-01 00:00:00+00
(1 row)
regression=# select to_timestamp('1970-01-01', 'YYYY-MM-DD');
to_timestamp
------------------------
1970-01-01 00:00:00+00
(1 row)
The two results of to_timestamp(0) represent the same UTC
instant, but the other two are different instants.
regards, tom lane