Thread: Why extract( ... from timestamp ) is not immutable?
hi,
Question is basically in the title, but let's show some example:
$ begin;
BEGIN
*$ set timezone = 'EST';
SET
*$ select now(), extract(epoch from now()), extract(epoch from now() at time zone 'UTC');
now │ date_part │ date_part
───────────────────────────────┼──────────────────┼──────────────────
2012-01-25 10:19:17.366139-05 │ 1327504757.36614 │ 1327522757.36614
(1 row)
*$ set timezone = 'CET';
SET
*$ select now(), extract(epoch from now()), extract(epoch from now() at time zone 'UTC');
now │ date_part │ date_part
───────────────────────────────┼──────────────────┼──────────────────
2012-01-25 16:19:17.366139+01 │ 1327504757.36614 │ 1327501157.36614
(1 row)
Why aren't the 3rd date_parts the same in both cases? I mean - I see that they
are adjusted due to timezone, but why is it happening?
Based on \dt+, I seem to see that it should be immutable:
*$ \df+ date_part
List of
functions
Schema │ Name │ Result data type │ Argument data types │ Type │ Volatility │ Owner │ Language
│ Source code │ Description
────────────┼───────────┼──────────────────┼───────────────────────────────────┼────────┼────────────┼───────┼──────────┼──────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────
pg_catalog │ date_part │ double precision │ text, abstime │ normal │ stable │ pgdba │ sql
│select pg_catalog.date_part($1, cast($2 as timestamp with time zone)) │ extract field from abstime
pg_catalog │ date_part │ double precision │ text, date │ normal │ immutable │ pgdba │ sql
│select pg_catalog.date_part($1, cast($2 as timestamp without time zone)) │ extract field from date
pg_catalog │ date_part │ double precision │ text, interval │ normal │ immutable │ pgdba │ internal
│interval_part │ extract field from interval
pg_catalog │ date_part │ double precision │ text, reltime │ normal │ stable │ pgdba │ sql
│select pg_catalog.date_part($1, cast($2 as pg_catalog.interval)) │ extract field from reltime
pg_catalog │ date_part │ double precision │ text, timestamp without time zone │ normal │ immutable │ pgdba │ internal
│timestamp_part │ extract field from timestamp
pg_catalog │ date_part │ double precision │ text, timestamp with time zone │ normal │ stable │ pgdba │ internal
│timestamptz_part │ extract field from timestamp with time zone
pg_catalog │ date_part │ double precision │ text, time without time zone │ normal │ immutable │ pgdba │ internal
│time_part │ extract field from time
pg_catalog │ date_part │ double precision │ text, time with time zone │ normal │ immutable │ pgdba │ internal
│timetz_part │ extract field from time with time zone
(8 rows)
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:
> Why aren't the 3rd date_parts the same in both cases? I mean - I see that they
> are adjusted due to timezone, but why is it happening?
Given a timestamp without time zone, timestamp_part('epoch') assumes
that it is in session timezone, and rotates it back to UTC so as to
satisfy the expectation that epoch values start from zero at midnight
UTC. In short, the calculation you're showing does the zone correction
an extra time. Don't do that.
regards, tom lane
On Wed, Jan 25, 2012 at 10:35:47AM -0500, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > Why aren't the 3rd date_parts the same in both cases? I mean - I see that they
> > are adjusted due to timezone, but why is it happening?
>
> Given a timestamp without time zone, timestamp_part('epoch') assumes
> that it is in session timezone, and rotates it back to UTC so as to
> satisfy the expectation that epoch values start from zero at midnight
> UTC. In short, the calculation you're showing does the zone correction
> an extra time. Don't do that.
ok.
how can I then have immutable epoch for given point in time?
I thought that this is what I will achieve with extract(epoch from now()
at time zone 'UTC') but clearly it doesn't work.
So what options do I have?
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On Wednesday, January 25, 2012 7:22:25 am hubert depesz lubaczewski wrote: > hi, > Question is basically in the title, but let's show some example: > > $ begin; > BEGIN > > *$ set timezone = 'EST'; > SET > > *$ select now(), extract(epoch from now()), extract(epoch from now() at > time zone 'UTC'); now │ date_part │ date_part > ───────────────────────────────┼──────────────────┼────────────────── > 2012-01-25 10:19:17.366139-05 │ 1327504757.36614 │ 1327522757.36614 > (1 row) > > *$ set timezone = 'CET'; > SET > > *$ select now(), extract(epoch from now()), extract(epoch from now() at > time zone 'UTC'); now │ date_part │ date_part > ───────────────────────────────┼──────────────────┼────────────────── > 2012-01-25 16:19:17.366139+01 │ 1327504757.36614 │ 1327501157.36614 > (1 row) > > Why aren't the 3rd date_parts the same in both cases? I mean - I see that > they are adjusted due to timezone, but why is it happening? > > Based on \dt+, I seem to see that it should be immutable: > *$ \df+ date_part Its not the extract part but the at time zone part see: http://www.postgresql.org/docs/9.0/interactive/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT -- Adrian Klaver adrian.klaver@gmail.com
hubert depesz lubaczewski <depesz@depesz.com> writes:
> how can I then have immutable epoch for given point in time?
What do you consider to be "a given point in time"? It seems like
you have not thought through what effects the timezone setting has
on your concept of "now", or at least you have not explained what
you need.
Perhaps even more to the point, why aren't you just storing the
timestamp or timestamptz value and being happy with that?
regards, tom lane
On Wednesday, January 25, 2012 7:37:27 am hubert depesz lubaczewski wrote:
> On Wed, Jan 25, 2012 at 10:35:47AM -0500, Tom Lane wrote:
> > hubert depesz lubaczewski <depesz@depesz.com> writes:
> > > Why aren't the 3rd date_parts the same in both cases? I mean - I see
> > > that they are adjusted due to timezone, but why is it happening?
> >
> > Given a timestamp without time zone, timestamp_part('epoch') assumes
> > that it is in session timezone, and rotates it back to UTC so as to
> > satisfy the expectation that epoch values start from zero at midnight
> > UTC. In short, the calculation you're showing does the zone correction
> > an extra time. Don't do that.
>
> ok.
> how can I then have immutable epoch for given point in time?
>
> I thought that this is what I will achieve with extract(epoch from now()
> at time zone 'UTC') but clearly it doesn't work.
> So what options do I have?
Isn't extract(epoch from now()) getting what you want?
>
> Best regards,
>
> depesz
--
Adrian Klaver
adrian.klaver@gmail.com
On Wed, Jan 25, 2012 at 07:37:44AM -0800, Adrian Klaver wrote: > Its not the extract part but the at time zone part see: > > http://www.postgresql.org/docs/9.0/interactive/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT not sure what you mean - timestamptz at time zone converts to timestamp (without time zone), and it shows predictable results: $ begin; BEGIN *$ set timezone = 'EST'; SET *$ select now() at time zone 'UTC'; timezone ──────────────────────────── 2012-01-25 15:43:31.048171 (1 row) *$ set timezone = 'CET'; SET *$ select now() at time zone 'UTC'; timezone ──────────────────────────── 2012-01-25 15:43:31.048171 (1 row) both timestamps returned are the same. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/
On Wed, Jan 25, 2012 at 10:43:59AM -0500, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > how can I then have immutable epoch for given point in time?
>
> What do you consider to be "a given point in time"? It seems like
> you have not thought through what effects the timezone setting has
> on your concept of "now", or at least you have not explained what
> you need.
> Perhaps even more to the point, why aren't you just storing the
> timestamp or timestamptz value and being happy with that?
This is to implement constraint exclusion, where I'm ab-using geometric
functions with base being epoch.
anyway - the point is that in \df date_part(, timestamp) says it's
immutable, while it is not.
As for "what do you consider to be "a given point in time" - value of
timestamptz type.
I have this value in database, and need to use its epoch as base for
index.
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On Wed, Jan 25, 2012 at 07:44:14AM -0800, Adrian Klaver wrote:
> > I thought that this is what I will achieve with extract(epoch from now()
> > at time zone 'UTC') but clearly it doesn't work.
> > So what options do I have?
>
> Isn't extract(epoch from now()) getting what you want?
you can't make index on it.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On Wednesday, January 25, 2012 7:44:44 am hubert depesz lubaczewski wrote: > On Wed, Jan 25, 2012 at 07:37:44AM -0800, Adrian Klaver wrote: > > Its not the extract part but the at time zone part see: > > > > http://www.postgresql.org/docs/9.0/interactive/functions-datetime.html#FU > > NCTIONS-DATETIME-ZONECONVERT > > not sure what you mean - timestamptz at time zone converts to timestamp > (without time zone), and it shows predictable results: > $ begin; > BEGIN > > *$ set timezone = 'EST'; > SET > > *$ select now() at time zone 'UTC'; > timezone > ──────────────────────────── > 2012-01-25 15:43:31.048171 > (1 row) > > *$ set timezone = 'CET'; > SET > > *$ select now() at time zone 'UTC'; > timezone > ──────────────────────────── > 2012-01-25 15:43:31.048171 > (1 row) > > both timestamps returned are the same. And therein lies the problem:) Per Toms comment, extract sees these timestamps without timezones and assumes they are local time and rotates them back to UTC. To illustrate, I am in PST: test(5432)aklaver=>select now() at time zone 'UTC'; timezone --------------------------- 2012-01-25 16:03:47.32097 test(5432)aklaver=>select extract(epoch from '2012-01-25 16:03:47.32097'::timestamp at time zone 'UTC'); date_part ------------------ 1327507427.32097 test(5432)aklaver=>SELECT extract(epoch from ('2012-01-25 16:03:47.32097'::timestamp + interval '8 hrs')); date_part ------------------ 1327565027.32097 > > Best regards, > > depesz -- Adrian Klaver adrian.klaver@gmail.com
On Wed, Jan 25, 2012 at 08:06:42AM -0800, Adrian Klaver wrote:
> And therein lies the problem:) Per Toms comment, extract sees these timestamps
> without timezones and assumes they are local time and rotates them back to UTC.
i know about it.
but - given the fact that date_part(, timestamp) is marked as immutable,
it seems to be that it's a bug.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On Wednesday, January 25, 2012 7:48:34 am hubert depesz lubaczewski wrote: > On Wed, Jan 25, 2012 at 07:44:14AM -0800, Adrian Klaver wrote: > > > I thought that this is what I will achieve with extract(epoch from > > > now() at time zone 'UTC') but clearly it doesn't work. > > > So what options do I have? > > > > Isn't extract(epoch from now()) getting what you want? > > you can't make index on it. I am afraid I am not following. So you can make an index on?: extract(epoch from now() at time zone 'UTC') > > Best regards, > > depesz -- Adrian Klaver adrian.klaver@gmail.com
On Wed, Jan 25, 2012 at 08:10:19AM -0800, Adrian Klaver wrote:
> On Wednesday, January 25, 2012 7:48:34 am hubert depesz lubaczewski wrote:
> > On Wed, Jan 25, 2012 at 07:44:14AM -0800, Adrian Klaver wrote:
> > > > I thought that this is what I will achieve with extract(epoch from
> > > > now() at time zone 'UTC') but clearly it doesn't work.
> > > > So what options do I have?
> > >
> > > Isn't extract(epoch from now()) getting what you want?
> >
> > you can't make index on it.
>
> I am afraid I am not following. So you can make an index on?:
>
> extract(epoch from now() at time zone 'UTC')
yes, I can:
$ create table z (i timestamptz);
CREATE TABLE
$ create index q on z (extract(epoch from i));
ERROR: functions in index expression must be marked IMMUTABLE
$ create index q on z (extract(epoch from i at time zone 'UTC'));
CREATE INDEX
which - given the fact that extract(epoch from timestamp) is not
immutable, shouldn't be possible.
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On Wednesday, January 25, 2012 8:08:37 am hubert depesz lubaczewski wrote: > On Wed, Jan 25, 2012 at 08:06:42AM -0800, Adrian Klaver wrote: > > And therein lies the problem:) Per Toms comment, extract sees these > > timestamps without timezones and assumes they are local time and rotates > > them back to UTC. > > i know about it. > but - given the fact that date_part(, timestamp) is marked as immutable, > it seems to be that it's a bug. http://www.postgresql.org/docs/9.0/static/xfunc-volatility.html An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments. For example, a query like SELECT ... WHERE x = 2 + 2 can be simplified on sight to SELECT ... WHERE x = 4, because the function underlying the integer addition operator is marked IMMUTABLE. http://www.postgresql.org/docs/9.0/interactive/functions- datetime.html#FUNCTIONS-DATETIME-EXTRACT epoch For date and timestamp values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for interval values, the total number of seconds in the interval The issue seems to be the definition of same arguments. Since epoch is anchored at 1970-01-01 00:00:00 UTC the timestamp passed to extract need to be normalized to UTC. Once a timestamp is in UTC then the epoch can be determined. The variability lies in the initial data fed to the function. Since time does not stand still, every time you do now() you are getting a different argument. Throw in time zone considerations and you see the results you are getting. > > Best regards, > > depesz -- Adrian Klaver adrian.klaver@gmail.com
On Wed, Jan 25, 2012 at 08:22:26AM -0800, Adrian Klaver wrote:
> The issue seems to be the definition of same arguments. Since epoch is anchored
> at 1970-01-01 00:00:00 UTC the timestamp passed to extract need to be normalized
> to UTC. Once a timestamp is in UTC then the epoch can be determined. The
> variability lies in the initial data fed to the function. Since time does not
> stand still, every time you do now() you are getting a different argument. Throw
> in time zone considerations and you see the results you are getting.
??? Sorry?
what are you talking about?
Simple:
extract(epoch from '2012-01-01 12:34:56'::timestamp)
which doesn't contain now(), is not immutable.
Personally, I think that extract(epoch from timestamp) should assume
that the timestamp is UTC.
Or that there should be a way to do it - by "it" i mean - extract epoch
value from timestamp value in immutable way.
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:
> 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
On Wednesday, January 25, 2012 8:30:17 am hubert depesz lubaczewski wrote: > On Wed, Jan 25, 2012 at 08:22:26AM -0800, Adrian Klaver wrote: > > The issue seems to be the definition of same arguments. Since epoch is > > anchored at 1970-01-01 00:00:00 UTC the timestamp passed to extract need > > to be normalized to UTC. Once a timestamp is in UTC then the epoch can > > be determined. The variability lies in the initial data fed to the > > function. Since time does not stand still, every time you do now() you > > are getting a different argument. Throw in time zone considerations and > > you see the results you are getting. > > ??? Sorry? > what are you talking about? > > Simple: > extract(epoch from '2012-01-01 12:34:56'::timestamp) > which doesn't contain now(), is not immutable. If you mean that the result will be different depending on the timezone set then yes. My argument, and it seems moot now, is that the function is immutable but the data is not. That you get different results because you pass in different data. That timestamps other than UTC are relative and with out being very specific what time you are dealing with the results can vary. I would agree that probably needs to be spelled out better. > > Personally, I think that extract(epoch from timestamp) should assume > that the timestamp is UTC. What if it isn't? > Or that there should be a way to do it - by "it" i mean - extract epoch > value from timestamp value in immutable way. Have a timezone value on the timestamp. If the data you are working with is stored as timestamp with time zone then the timestamps represent a point in time. > > Best regards, > > depesz -- Adrian Klaver adrian.klaver@gmail.com
On Wed, Jan 25, 2012 at 08:54:44AM -0800, Adrian Klaver wrote:
> > Personally, I think that extract(epoch from timestamp) should assume
> > that the timestamp is UTC.
>
> What if it isn't?
then you can always correct it with "at time zone 'some specific time
zone'"
but you can't correct it the other way.
> > Or that there should be a way to do it - by "it" i mean - extract epoch
> > value from timestamp value in immutable way.
>
> Have a timezone value on the timestamp. If the data you are working with is
> stored as timestamp with time zone then the timestamps represent a point in
> time.
I do have. But you can't have index on epoch from timestamptz.
and while you can have iundex on epoch from timestamp, it is not
correct.
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On 01/25/2012 08:57 AM, hubert depesz lubaczewski wrote: > On Wed, Jan 25, 2012 at 08:54:44AM -0800, Adrian Klaver wrote: >>> Personally, I think that extract(epoch from timestamp) should assume >>> that the timestamp is UTC. >> >> What if it isn't? > > then you can always correct it with "at time zone 'some specific time > zone'" I am going to have to think about this, 'at time zone' makes assumptions about timestamps depending on the set timezone and whether the timestamp has a tz or not. > > but you can't correct it the other way. > >>> Or that there should be a way to do it - by "it" i mean - extract epoch >>> value from timestamp value in immutable way. >> >> Have a timezone value on the timestamp. If the data you are working with is >> stored as timestamp with time zone then the timestamps represent a point in >> time. > > I do have. But you can't have index on epoch from timestamptz. > and while you can have iundex on epoch from timestamp, it is not > correct. Finally dawned on me. When you use 'at time zone' on a timestamp with tz it strips the tz which then allows the value to be indexed because: -[ RECORD 5 ]-------+------------------------------------------------------------------------- Schema | pg_catalog Name | date_part Result data type | double precision Argument data types | text, timestamp without time zone Type | normal Volatility | immutable Owner | postgres Language | internal Source code | timestamp_part Description | extract field from timestamp > > depesz > -- Adrian Klaver adrian.klaver@gmail.com
On Wed, Jan 25, 2012 at 02:07:40PM -0800, Adrian Klaver wrote:
> Finally dawned on me. When you use 'at time zone' on a timestamp
> with tz it strips the tz which then allows the value to be indexed
> because:
>
> -[ RECORD 5 ]-------+-------------------------------------------------------------------------
> Schema | pg_catalog
> Name | date_part
> Result data type | double precision
> Argument data types | text, timestamp without time zone
> Type | normal
> Volatility | immutable
> Owner | postgres
> Language | internal
> Source code | timestamp_part
> Description | extract field from timestamp
yes, but it is not correct - the value is actually stable, and not
immutable.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On Wednesday, January 25, 2012 2:46:39 pm hubert depesz lubaczewski wrote: > On Wed, Jan 25, 2012 at 02:07:40PM -0800, Adrian Klaver wrote: > > Finally dawned on me. When you use 'at time zone' on a timestamp > > with tz it strips the tz which then allows the value to be indexed > > because: > > > > -[ RECORD 5 > > ]-------+--------------------------------------------------------------- > > ---------- Schema | pg_catalog > > Name | date_part > > Result data type | double precision > > Argument data types | text, timestamp without time zone > > Type | normal > > Volatility | immutable > > Owner | postgres > > Language | internal > > Source code | timestamp_part > > Description | extract field from timestamp > > yes, but it is not correct - the value is actually stable, and not > immutable. Alright, because the epoch and timezone* fields do timezone manipulation on the supplied values. Well learned a lot. Thanks. > > Best regards, > > depesz -- Adrian Klaver adrian.klaver@gmail.com
Re: [HACKERS] 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/
On 2012-01-25, hubert depesz lubaczewski <depesz@depesz.com> wrote: > On Wed, Jan 25, 2012 at 02:07:40PM -0800, Adrian Klaver wrote: >> Finally dawned on me. When you use 'at time zone' on a timestamp >> with tz it strips the tz which then allows the value to be indexed >> because: >> >> -[ RECORD 5 ]-------+------------------------------------------------------------------------- >> Schema | pg_catalog >> Name | date_part >> Result data type | double precision >> Argument data types | text, timestamp without time zone >> Type | normal >> Volatility | immutable >> Owner | postgres >> Language | internal >> Source code | timestamp_part >> Description | extract field from timestamp > > yes, but it is not correct - the value is actually stable, and not > immutable. it's immutable for all date parts except "epoch". epoch is backwards to the other date parts. immutable for timestamptz and stable for timestamp -- ⚂⚃ 100% natural
On 2012-01-25, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Wednesday, January 25, 2012 7:48:34 am hubert depesz lubaczewski wrote: >> On Wed, Jan 25, 2012 at 07:44:14AM -0800, Adrian Klaver wrote: >> > > I thought that this is what I will achieve with extract(epoch from >> > > now() at time zone 'UTC') but clearly it doesn't work. >> > > So what options do I have? >> > >> > Isn't extract(epoch from now()) getting what you want? >> >> you can't make index on it. > > I am afraid I am not following. So you can make an index on?: > > extract(epoch from now() at time zone 'UTC') that one gets you the wrong result. -- ⚂⚃ 100% natural
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