Re: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not

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