Thread: Re: [GENERAL] Why extract( ... from timestamp ) is not immutable?
hubert depesz lubaczewski <depesz@depesz.com> writes:
> anyway - the point is that in \df date_part(, timestamp) says it's
> immutable, while it is not.
Hmm, you're right. I thought we'd fixed that way back when, but
obviously not. Or maybe the current behavior of the epoch case
postdates that.
regards, tom lane
Re: [GENERAL] Why extract( ... from timestamp ) is not immutable?
From
hubert depesz lubaczewski
Date:
On Wed, Jan 25, 2012 at 11:30:49AM -0500, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > anyway - the point is that in \df date_part(, timestamp) says it's
> > immutable, while it is not.
>
> Hmm, you're right. I thought we'd fixed that way back when, but
> obviously not. Or maybe the current behavior of the epoch case
> postdates that.
is there a chance something will happen with/about it?
preferably I would see extract( epoch from timestamp ) to be really
immutable, i.e. (in my opinion) it should treat incoming data as UTC
- for epoch calculation.
Alternatively - perhaps epoch extraction should be moved to specialized
function, which would have swapped mutability:
get_epoch(timestamptz) would be immutable
while
get_epoch(timestamp) would be stable
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
> preferably I would see extract( epoch from timestamp ) to be really > immutable, i.e. (in my opinion) it should treat incoming data as UTC > - for epoch calculation. > Alternatively - perhaps epoch extraction should be moved to specialized > function, which would have swapped mutability: We can't have functions which are immutable or not depending on their inputs. That way lies madness. > get_epoch(timestamptz) would be immutable > while > get_epoch(timestamp) would be stable Well, to_epoch, in order to be consistent with other conversion functions. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Re: [GENERAL] Why extract( ... from timestamp ) is not immutable?
From
hubert depesz lubaczewski
Date:
On Mon, Jan 30, 2012 at 10:35:21AM -0800, Josh Berkus wrote:
>
> > preferably I would see extract( epoch from timestamp ) to be really
> > immutable, i.e. (in my opinion) it should treat incoming data as UTC
> > - for epoch calculation.
> > Alternatively - perhaps epoch extraction should be moved to specialized
> > function, which would have swapped mutability:
>
> We can't have functions which are immutable or not depending on their
> inputs. That way lies madness.
but this is exactly what's happening now.
extract( ... from timestamp) is marked as immutable, while in some cases
(namely when you want epoch) it should be stable because the return from
function changes.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Mon, Jan 30, 2012 at 10:35:21AM -0800, Josh Berkus wrote:
>> We can't have functions which are immutable or not depending on their
>> inputs. That way lies madness.
> but this is exactly what's happening now.
Well, the current marking is clearly incorrect. What to do about that
is a bit less clear --- should we downgrade the marking, or change the
function's behavior so that it really is immutable?
I haven't formed an opinion on that myself, other than to think that
it's something that requires more than a moment's thought.
regards, tom lane
On 1/30/12 5:41 PM, Tom Lane wrote: > hubert depesz lubaczewski <depesz@depesz.com> writes: >> On Mon, Jan 30, 2012 at 10:35:21AM -0800, Josh Berkus wrote: >>> We can't have functions which are immutable or not depending on their >>> inputs. That way lies madness. > >> but this is exactly what's happening now. > > Well, the current marking is clearly incorrect. What to do about that > is a bit less clear --- should we downgrade the marking, or change the > function's behavior so that it really is immutable? AFAIK, the only case which is NOT immutable is extract(epoch FROM timestamp without time zone), no? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> On 1/30/12 5:41 PM, Tom Lane wrote:
>> Well, the current marking is clearly incorrect. What to do about that
>> is a bit less clear --- should we downgrade the marking, or change the
>> function's behavior so that it really is immutable?
> AFAIK, the only case which is NOT immutable is extract(epoch FROM
> timestamp without time zone), no?
That's the only one we currently know is not immutable. But before we
make any decisions, I think it'd be a good idea to scrutinize all the
other cases too, because obviously this area has gotten some careless
hacking (*) done on it in the past.
regards, tom lane
(*) I have a nasty feeling that the carelessness was mine.
On Wed, Jan 25, 2012 at 11:30:49AM -0500, Tom Lane wrote: > hubert depesz lubaczewski <depesz@depesz.com> writes: > > anyway - the point is that in \df date_part(, timestamp) says it's > > immutable, while it is not. > > Hmm, you're right. I thought we'd fixed that way back when, but > obviously not. Or maybe the current behavior of the epoch case > postdates that. Has this been addressed? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> On Wed, Jan 25, 2012 at 11:30:49AM -0500, Tom Lane wrote:
>> hubert depesz lubaczewski <depesz@depesz.com> writes:
> anyway - the point is that in \df date_part(, timestamp) says it's
> immutable, while it is not.
>>
>> Hmm, you're right. I thought we'd fixed that way back when, but
>> obviously not. Or maybe the current behavior of the epoch case
>> postdates that.
> Has this been addressed?
Yes:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL9_2_BR [0d9819f7e] 2012-04-10 12:04:42 -0400
Measure epoch of timestamp-without-time-zone from local not UTC midnight.
This patch reverts commit 191ef2b407f065544ceed5700e42400857d9270f
and thereby restores the pre-7.3 behavior of EXTRACT(EPOCH FROM
timestamp-without-tz). Per discussion, the more recent behavior was
misguided on a couple of grounds: it makes it hard to get a
non-timezone-aware epoch value for a timestamp, and it makes this one
case dependent on the value of the timezone GUC, which is incompatible
with having timestamp_part() labeled as immutable.
The other behavior is still available (in all releases) by explicitly
casting the timestamp to timestamp with time zone before applying EXTRACT.
This will need to be called out as an incompatible change in the 9.2
release notes. Although having mutable behavior in a function marked
immutable is clearly a bug, we're not going to back-patch such a change.
The description of this in the 9.2 release notes could perhaps use some
refinement though.
regards, tom lane