Thread: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not
BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not
From
PG Bug reporting form
Date:
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.
Re: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not
From
"David G. Johnston"
Date:
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.
Re: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not
From
Tom Lane
Date:
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
Re: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not
From
Braiam
Date:
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
Re: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not
From
Braiam
Date:
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
Re: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not
From
Tom Lane
Date:
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
Re: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not
From
Braiam
Date:
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
Re: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not
From
Christophe Pettus
Date:
> 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
Re: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not
From
Braiam
Date:
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