Обсуждение: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not

The following bug has been logged on the website:

Bug reference:      18007
Logged by:          Braiam Peguero
Email address:      braiamp+pg@gmail.com
PostgreSQL version: 15.3
Operating system:   Debian
Description:

There's no much difference between timestamp and dateT00:00:00.000, yet
using age(date, date) for some reason internally doesn't type coerce
correctly into the appropriated types. I remember that on a previous
versions (not sure if it was 14) this wasn't the case, so I would consider
this a regression. I skimmed the release notes for 15 and only saw this note
"Mark the interval output function as stable, not immutable, since it
depends on IntervalStyle (Tom Lane) This will, for example, cause creation
of indexes relying on the text output of interval values to fail", which
doesn't seem to be relevant, since age(date::timestamp, date::timestamp)
doesn't seems to complain.

PG Bug reporting form <noreply@postgresql.org> writes:
> There's no much difference between timestamp and dateT00:00:00.000, yet
> using age(date, date) for some reason internally doesn't type coerce
> correctly into the appropriated types.

There is no age(date, date) function.  What we have is age(timestamp,
timestamp) and age(timestamptz, timestamptz), so the parser has to
choose which type to coerce to --- and it prefers timestamptz.
Perhaps this is surprising as an isolated fact, but I believe what
it stems from is that timestamptz is the "preferred" type in this
type category.  That's not something that's likely to change.
Then what you have within the expression is a coercion from date
to timestamptz, which depends on the time zone, so it's not
immutable.

Bottom line is that you'd better cast the dates to timestamp
explicitly.  Or you could make an age(date, date) wrapper
function that does that.

> I remember that on a previous
> versions (not sure if it was 14) this wasn't the case,

Doubt it.  Nothing here has changed in a couple of decades.
Maybe you had a wrapper function that you forgot to bring over?

            regards, tom lane

On Thu, Jun 29, 2023 at 1:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > There's no much difference between timestamp and dateT00:00:00.000, yet
> > using age(date, date) for some reason internally doesn't type coerce
> > correctly into the appropriated types.
>
> There is no age(date, date) function.  What we have is age(timestamp,
> timestamp) and age(timestamptz, timestamptz), so the parser has to
> choose which type to coerce to --- and it prefers timestamptz.

According to \df+ age both timestamptz and timestamp are immutable:

-[ RECORD 2 ]-------+--------------------------------------------------------------------
Schema              | pg_catalog
Name                | age
Result data type    | interval
Argument data types | timestamp without time zone, timestamp without time zone
Type                | func
Volatility          | immutable
Parallel            | safe
Owner               | postgres
Security            | invoker
Access privileges   |
Language            | internal
Source code         | timestamp_age
Description         | date difference preserving months and years
-[ RECORD 4 ]-------+--------------------------------------------------------------------
Schema              | pg_catalog
Name                | age
Result data type    | interval
Argument data types | timestamp with time zone, timestamp with time zone
Type                | func
Volatility          | immutable
Parallel            | safe
Owner               | postgres
Security            | invoker
Access privileges   |
Language            | internal
Source code         | timestamptz_age
Description         | date difference preserving months and years

So, whatever type is coerced into pre-function evaluation comes
with strange results. I'm not aware of a way that I can see what
kind of type is being coerced into.

> Perhaps this is surprising as an isolated fact, but I believe what
> it stems from is that timestamptz is the "preferred" type in this
> type category.  That's not something that's likely to change.
> Then what you have within the expression is a coercion from date
> to timestamptz, which depends on the time zone, so it's not
> immutable.
>
> Bottom line is that you'd better cast the dates to timestamp
> explicitly.  Or you could make an age(date, date) wrapper
> function that does that.
> > I remember that on a previous
> > versions (not sure if it was 14) this wasn't the case,
>
> Doubt it.  Nothing here has changed in a couple of decades.
> Maybe you had a wrapper function that you forgot to bring over?
>
>                         regards, tom lane



--
Braiam

On Thu, Jun 29, 2023 at 1:45 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Thu, Jun 29, 2023 at 10:36 AM PG Bug reporting form <noreply@postgresql.org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference:      18007
>> Logged by:          Braiam Peguero
>> Email address:      braiamp+pg@gmail.com
>> PostgreSQL version: 15.3
>> Operating system:   Debian
>> Description:
>>
>> There's no much difference between timestamp and dateT00:00:00.000, yet
>> using age(date, date)
>
>
> There is no "age(date, date)" function.  Only age(timestamp, timestamp)
>
>> for some reason internally doesn't type coerce
>> correctly into the appropriated types.
>
>
> Nope, type coercion happens before the function call, while figuring out which function signature to choose.
>
>>
>> I remember that on a previous
>> versions (not sure if it was 14) this wasn't the case, so I would consider
>> this a regression.
>
>
> You haven't provided any code demonstrating what you think is incorrect.

create temporary table test (
start_date date not null,
end_date date not null
);
alter table test add column time_elapsed interval generated always as
(age(end_date, start_date)) stored;

> David J.
>


--
Braiam

Braiam <braiamp@gmail.com> writes:
> On Thu, Jun 29, 2023 at 1:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> There is no age(date, date) function.  What we have is age(timestamp,
>> timestamp) and age(timestamptz, timestamptz), so the parser has to
>> choose which type to coerce to --- and it prefers timestamptz.

> According to \df+ age both timestamptz and timestamp are immutable:

True, but not very relevant: it's the coercion from date that's
giving you trouble.

> So, whatever type is coerced into pre-function evaluation comes
> with strange results. I'm not aware of a way that I can see what
> kind of type is being coerced into.

EXPLAIN will show that, eg

=# explain verbose select age(current_date, current_date);
                                            QUERY PLAN

---------------------------------------------------------------------------------------------------
 Result  (cost=0.00..0.02 rows=1 width=16)
   Output: age((CURRENT_DATE)::timestamp with time zone, (CURRENT_DATE)::timestamp with time zone)
(2 rows)

            regards, tom lane

Thanks. That

On Thu, Jun 29, 2023 at 2:24 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Braiam <braiamp@gmail.com> writes:
> > On Thu, Jun 29, 2023 at 1:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> There is no age(date, date) function.  What we have is age(timestamp,
> >> timestamp) and age(timestamptz, timestamptz), so the parser has to
> >> choose which type to coerce to --- and it prefers timestamptz.
>
> > According to \df+ age both timestamptz and timestamp are immutable:
>
> True, but not very relevant: it's the coercion from date that's
> giving you trouble.
>
> > So, whatever type is coerced into pre-function evaluation comes
> > with strange results. I'm not aware of a way that I can see what
> > kind of type is being coerced into.
>
> EXPLAIN will show that, eg
>
> =# explain verbose select age(current_date, current_date);
>                                             QUERY PLAN
>
> ---------------------------------------------------------------------------------------------------
>  Result  (cost=0.00..0.02 rows=1 width=16)
>    Output: age((CURRENT_DATE)::timestamp with time zone, (CURRENT_DATE)::timestamp with time zone)
> (2 rows)

Thanks. Then this is still wrong.

=# explain verbose select age('2022-02-01'::date, '2022-01-01'::date);
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..0.02 rows=1 width=16)
   Output: age(('2022-02-01'::date)::timestamp with time zone,
('2022-01-01'::date)::timestamp with time zone)
(2 rows)

Function age(timestamp with time zone, timestamp with time zone) is
marked as immutable. Postgres shouldn't complain about it.


>                         regards, tom lane



--
Braiam

> On Jun 29, 2023, at 11:29, Braiam <braiamp@gmail.com> wrote:
> Function age(timestamp with time zone, timestamp with time zone) is
> marked as immutable. Postgres shouldn't complain about it.

It's not the age() function as such that is the problem, but the (stable) cast from date to timestamptz.  Stable or
volatileinputs to an immutable function make the expression result non-immutable. 

For example, abs() is marked as immutable, but you can't do this:

xof=# CREATE TABLE t (i integer);
CREATE TABLE
xof=# CREATE INDEX ON t((abs(random()+i)));
ERROR:  functions in index expression must be marked IMMUTABLE

On Thu, Jun 29, 2023 at 3:26 PM Christophe Pettus <xof@thebuild.com> wrote:
>
>
>
> > On Jun 29, 2023, at 11:29, Braiam <braiamp@gmail.com> wrote:
> > Function age(timestamp with time zone, timestamp with time zone) is
> > marked as immutable. Postgres shouldn't complain about it.
>
> It's not the age() function as such that is the problem, but the (stable) cast from date to timestamptz.  Stable or
volatileinputs to an immutable function make the expression result non-immutable. 
>
> For example, abs() is marked as immutable, but you can't do this:
>
> xof=# CREATE TABLE t (i integer);
> CREATE TABLE
> xof=# CREATE INDEX ON t((abs(random()+i)));
> ERROR:  functions in index expression must be marked IMMUTABLE
>
>

I kinda guessed that that would be the case, but became confused
because explicit type casting doesn't seem to affect the result:

create temporary table test (
start_date date not null,
end_date date not null,
);

# fails
alter table test add column time_elapsed interval generated always as
(age(end_date, start_date)) stored;
# doesn't fail
alter table test add column time_elapsed interval generated always as
(age(end_date::timestamp, start_date::timestamp)) stored;

I believe that the ideal solution would be to create an explicit
age(date, date) for all date related types, since the name of the
function implies that you can use it for "age" related time/date
operations and such data is rarely stored as timestamps.

--
Braiam