Thread

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

   PG Bug reporting form <noreply@postgresql.org> — 2023-06-29T17:08:25Z

   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.
   
   

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

   David G. Johnston <david.g.johnston@gmail.com> — 2023-06-29T17:45:36Z

   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.
   
   David J.
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2023-06-29T17:52:42Z

   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
   
   
   
   

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

   Braiam <braiamp@gmail.com> — 2023-06-29T18:20:44Z

   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
   
   
   
   

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

   Braiam <braiamp@gmail.com> — 2023-06-29T18:23:33Z

   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
   
   
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2023-06-29T18:24:47Z

   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
   
   
   
   

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

   Braiam <braiamp@gmail.com> — 2023-06-29T18:29:21Z

   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
   
   
   
   

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

   Christophe Pettus <xof@thebuild.com> — 2023-06-29T19:25:49Z

   
   > 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 volatile inputs 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
   
   	
   
   
   

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

   Braiam <braiamp@gmail.com> — 2023-06-29T19:58:11Z

   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 volatile inputs 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