Re: 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(at)depesz(dot)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(at)depesz(dot)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(dot)klaver(at)gmail(dot)com

hubert depesz lubaczewski <depesz(at)depesz(dot)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(at)depesz(dot)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(dot)klaver(at)gmail(dot)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(at)depesz(dot)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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)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(at)depesz(dot)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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com

On Wed, Jan 25, 2012 at 11:30:49AM -0500, Tom Lane wrote:
> hubert depesz lubaczewski <depesz(at)depesz(dot)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

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(at)depesz(dot)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(at)depesz(dot)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(at)agliodbs(dot)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 2012-01-25, hubert depesz lubaczewski <depesz(at)depesz(dot)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(dot)klaver(at)gmail(dot)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(at)depesz(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> On Wed, Jan 25, 2012 at 11:30:49AM -0500, Tom Lane wrote:
>> hubert depesz lubaczewski <depesz(at)depesz(dot)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(at)sss(dot)pgh(dot)pa(dot)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