Re: [GENERAL] [8.1.4] Create index on timestamp fails
On Aug 22, 2006, at 5:35 PM, Tom Lane wrote:
Arturo Perez [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] [8.1.4] Create index on timestamp fails
Hi
Chris,
user_tracking is not a function, it's the name of the table containing
the column entry_date. Is my syntax that far off?!
-arturo
-Original Message-From: Chris Hoover
[mailto:[EMAIL PROTECTED]Sent: Tuesday, August 22, 2006 3:02
PMTo: Arturo PerezCc:
pgsql-general@postgresql.orgSubject: Re: [GENERAL] [8.1.4] Create
index on timestamp failsIt 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 [EMAIL PROTECTED]
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
Re: [GENERAL] [8.1.4] Create index on timestamp fails
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [8.1.4] Create index on timestamp fails
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [8.1.4] Create index on timestamp fails
Karsten Hilbert [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] [8.1.4] Create index on timestamp fails
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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] [8.1.4] Create index on timestamp fails
Title: [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
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 [EMAIL PROTECTED] 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
Re: [GENERAL] [8.1.4] Create index on timestamp fails
Arturo Perez [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
