Thread: [8.1.4] Create index on timestamp fails

Hi all,

Using postgresql 8.1.4....

I have a table with an column:
        entry_date | timestamp with time zone| not null

And when I try to create an index on it like so:
        create index entry_date_idx on user_tracking(date_part('year',entry_date));

I get a
        ERROR: functions in index expression must be marked IMMUTABLE

According to the mailing lists, this has been working since 7.4.  What am I doing wrong?

tia,
arturo

It appears that 8.1 is stricter on checking the type of function.  Look at your user_tracking function.  It is probably set as volatile.  You need to change it to be immutable.

This should fix the issue.

Chris

"Arturo Perez" <aperez@hayesinc.com> writes:
> I have a table with an column:
>     entry_date | timestamp with time zone| not null

> And when I try to create an index on it like so:
>     create index entry_date_idx on =
> user_tracking(date_part('year',entry_date));

> I get a
>     ERROR: functions in index expression must be marked IMMUTABLE

> According to the mailing lists, this has been working since 7.4.

I seriously doubt that.  date_part on a timestamptz is stable, not
immutable, and AFAICT has been marked that way since 7.3.  The problem
is that the results depend on your current TimeZone setting --- for
instance, 2AM 2006-01-01 in London is 9PM 2005-12-31 where I live.

If you only need day precision, try storing entry_date as a date instead
of a timestamptz.  Or perhaps consider timestamp without tz.  But you
need something that's not timezone-dependent to make this work.

            regards, tom lane

-----Original Message-----
From: Chris Hoover [mailto:revoohc@gmail.com]
Sent: Tuesday, August 22, 2006 3:02 PM
To: Arturo Perez
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] [8.1.4] Create index on timestamp fails

It appears that 8.1 is stricter on checking the type of function.  Look at your user_tracking function.  It is probably set as volatile.  You need to change it to be immutable.

This should fix the issue.

Chris

On 8/21/06, Arturo Perez <aperez@hayesinc.com> wrote:

Hi all,

Using postgresql 8.1.4....

I have a table with an column:
        entry_date | timestamp with time zone| not null

And when I try to create an index on it like so:
        create index entry_date_idx on user_tracking(date_part('year',entry_date));

I get a
        ERROR: functions in index expression must be marked IMMUTABLE

According to the mailing lists, this has been working since 7.4.  What am I doing wrong?

tia,
arturo

On Aug 22, 2006, at 5:35 PM, Tom Lane wrote:

> "Arturo Perez" <aperez@hayesinc.com> writes:
>> I have a table with an column:
>>     entry_date | timestamp with time zone| not null
>
>> And when I try to create an index on it like so:
>>     create index entry_date_idx on =
>> user_tracking(date_part('year',entry_date));
>
>> I get a
>>     ERROR: functions in index expression must be marked IMMUTABLE
>
>> According to the mailing lists, this has been working since 7.4.
>
> I seriously doubt that.  date_part on a timestamptz is stable, not
> immutable, and AFAICT has been marked that way since 7.3.  The problem
> is that the results depend on your current TimeZone setting --- for
> instance, 2AM 2006-01-01 in London is 9PM 2005-12-31 where I live.
>
> If you only need day precision, try storing entry_date as a date
> instead
> of a timestamptz.  Or perhaps consider timestamp without tz.  But you
> need something that's not timezone-dependent to make this work.
>
>             regards, tom lane


Ah, I knew it was something I was overlooking.  Thanks a ton.  We need
sub-day granularity (it's for a sort of weblog).  Without a TZ sounds
llke
a winner.

Thanks again,
arturo

Arturo Perez wrote:
>
> On Aug 22, 2006, at 5:35 PM, Tom Lane wrote:

> >I seriously doubt that.  date_part on a timestamptz is stable, not
> >immutable, and AFAICT has been marked that way since 7.3.  The problem
> >is that the results depend on your current TimeZone setting --- for
> >instance, 2AM 2006-01-01 in London is 9PM 2005-12-31 where I live.
> >
> >If you only need day precision, try storing entry_date as a date
> >instead
> >of a timestamptz.  Or perhaps consider timestamp without tz.  But you
> >need something that's not timezone-dependent to make this work.
>
> Ah, I knew it was something I was overlooking.  Thanks a ton.  We need
> sub-day granularity (it's for a sort of weblog).  Without a TZ sounds
> llke a winner.

Another idea would be to separate the date column (which would have the
index) from the time column (which would have the timezone).  The
timezone is important -- if you have "bloggers" from all around the
world you're gonna have serious problems with the archived time.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, Aug 23, 2006 at 09:07:35AM -0400, Alvaro Herrera wrote:

> Another idea would be to separate the date column (which would have the
> index) from the time column (which would have the timezone).  The
> timezone is important -- if you have "bloggers" from all around the
> world you're gonna have serious problems with the archived time.

Would that indeed work ? I mean, depending on the time zone
the *date* might be different by +/-1, too ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> On Wed, Aug 23, 2006 at 09:07:35AM -0400, Alvaro Herrera wrote:
>> Another idea would be to separate the date column (which would have the
>> index) from the time column (which would have the timezone).  The
>> timezone is important -- if you have "bloggers" from all around the
>> world you're gonna have serious problems with the archived time.

> Would that indeed work ? I mean, depending on the time zone
> the *date* might be different by +/-1, too ?

It sounds a bit bogus to me too.  Another possibility is to keep the
data storage as timestamptz (which is really the recommended type for
any sort of real time values), and define the index on

    date_part('day', entry_time AT TIME ZONE 'GMT')

(or whatever zone you choose to use for reference).  However, to use the
index you'd have to spell the queries exactly like that, so the PITA
factor might be too high.

            regards, tom lane

On Wed, Aug 23, 2006 at 09:42:00AM -0400, Tom Lane wrote:

> It sounds a bit bogus to me too.  Another possibility is to keep the
> data storage as timestamptz (which is really the recommended type for
> any sort of real time values), and define the index on
>
>     date_part('day', entry_time AT TIME ZONE 'GMT')
That definitely sounds reasonable.

> (or whatever zone you choose to use for reference).  However, to use the
> index you'd have to spell the queries exactly like that, so the PITA
> factor might be too high.
An SQL function "gmt_tz(timestamptz)" might help to cut down
on the fuss:

 select ... from tbl where gmt_tz(tbl.a_tz) between ...;

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346