Обсуждение: 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