pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed8.2.4 Won't Build 8.1 Functional Indexes
6+ messages / 4 participants
[nested] [flat]
* 8.2.4 Won't Build 8.1 Functional Indexes
@ 2007-06-06 18:12 Chris Hoover <revoohc@gmail.com>
2007-06-06 19:54 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 6+ messages in thread
From: Chris Hoover @ 2007-06-06 18:12 UTC (permalink / raw)
To: pgsql-admin
I am testing upgrades to 8.2.4 from 8.1.3 and am having problems with
8.2.4balking at the functional indexes I have created. These indexes
exist and
work fine in 8.1.3, so why is 8.2.4 rejecting them?
Index 1:
CREATE INDEX acceptedbilling_to_date_accepted_billing_dt_idx
ON acceptedbilling
USING btree
(to_date(accepted_billing_dt::text, 'yyyymmdd'::text));.
Reject:
ERROR: functions in index expression must be marked IMMUTABLE
SQL state: 42P17
Index 2:
CREATE INDEX header_835_billing_idx
ON ct_835.header_835
USING btree
(to_date(to_char(create_timestamp, 'yyyy-mm-dd'::text),
'yyyy-mm-dd'::text));
Reject:
ERROR: functions in index expression must be marked IMMUTABLE
SQL state: 42P17
This is on 8.2.4 installed from the official RPMS.
thanks,
Chris
RH AS 4
PG 8.2.4 from RPMS
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 8.2.4 Won't Build 8.1 Functional Indexes
2007-06-06 18:12 8.2.4 Won't Build 8.1 Functional Indexes Chris Hoover <revoohc@gmail.com>
@ 2007-06-06 19:54 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-06-06 23:41 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2007-06-06 19:54 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover escribió:
> I am testing upgrades to 8.2.4 from 8.1.3 and am having problems with
> 8.2.4balking at the functional indexes I have created. These indexes
> exist and
> work fine in 8.1.3, so why is 8.2.4 rejecting them?
>
> Index 1:
> CREATE INDEX acceptedbilling_to_date_accepted_billing_dt_idx
> ON acceptedbilling
> USING btree
> (to_date(accepted_billing_dt::text, 'yyyymmdd'::text));.
>
> Reject:
> ERROR: functions in index expression must be marked IMMUTABLE
> SQL state: 42P17
You can't do this because to_date and other functions are not immutable.
8.2 seems to be more picky about this -- the date conversions of
timestamptz columns are dependent on the current timezone. Perhaps you
could change this to avoid this kind of conversions, doing things like
create index foo on bar
(to_date(accepted_billing_dt at time zone 'GMT'), 'yyymmdd')
I am not sure if this actually works -- but you would need to change the
queries as well. The point is that the time zone is now fixed, instead
of being dependent on a GUC variable.
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"I think my standards have lowered enough that now I think 'good design'
is when the page doesn't irritate the living f*ck out of me." (JWZ)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 8.2.4 Won't Build 8.1 Functional Indexes
2007-06-06 18:12 8.2.4 Won't Build 8.1 Functional Indexes Chris Hoover <revoohc@gmail.com>
2007-06-06 19:54 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Alvaro Herrera <alvherre@commandprompt.com>
@ 2007-06-06 23:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-07 01:52 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Alvaro Herrera <alvherre@commandprompt.com>
2007-06-07 02:16 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Chris Hoover <revoohc@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2007-06-06 23:41 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Chris Hoover <revoohc@gmail.com>; pgsql-admin
Alvaro Herrera <alvherre@commandprompt.com> writes:
> You can't do this because to_date and other functions are not immutable.
> 8.2 seems to be more picky about this -- the date conversions of
> timestamptz columns are dependent on the current timezone.
The reason 8.2 is more picky is that the function is less immutable
thanks to the addition of locale-dependent functionality:
http://archives.postgresql.org/pgsql-committers/2006-11/msg00264.php
I gather that the underlying column is timestamp without tz, or it would
never have worked in 8.1 either. That being the case, these index
definitions seem pretty darn stupid anyway --- why aren't you just
indexing on date_trunc or a plain cast to date?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 8.2.4 Won't Build 8.1 Functional Indexes
2007-06-06 18:12 8.2.4 Won't Build 8.1 Functional Indexes Chris Hoover <revoohc@gmail.com>
2007-06-06 19:54 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Alvaro Herrera <alvherre@commandprompt.com>
2007-06-06 23:41 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-06-07 01:52 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2007-06-07 01:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris Hoover <revoohc@gmail.com>; pgsql-admin
Tom Lane escribió:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > You can't do this because to_date and other functions are not immutable.
> > 8.2 seems to be more picky about this -- the date conversions of
> > timestamptz columns are dependent on the current timezone.
>
> The reason 8.2 is more picky is that the function is less immutable
> thanks to the addition of locale-dependent functionality:
> http://archives.postgresql.org/pgsql-committers/2006-11/msg00264.php
Ah. It was a good try in any case I think :-P The reason I confused the
issue is that I had been fiddling with date_trunc on a timestamptz
column and found that it didn't really work as I expected. (I can't
recall exactly ATM but I think it was 8.1 anyway).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 8.2.4 Won't Build 8.1 Functional Indexes
2007-06-06 18:12 8.2.4 Won't Build 8.1 Functional Indexes Chris Hoover <revoohc@gmail.com>
2007-06-06 19:54 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Alvaro Herrera <alvherre@commandprompt.com>
2007-06-06 23:41 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-06-07 02:16 ` Chris Hoover <revoohc@gmail.com>
2007-06-07 04:35 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Joshua D. Drake <jd@commandprompt.com>
1 sibling, 1 reply; 6+ messages in thread
From: Chris Hoover @ 2007-06-07 02:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-admin
Well, the one index:
CREATE INDEX acceptedbilling_to_date_accepted_billing_dt_idx
ON acceptedbilling
USING btree
(to_date(accepted_billing_dt::text, 'yyyymmdd'::text));.
Reject:
ERROR: functions in index expression must be marked IMMUTABLE
SQL state: 42P17
Is actually a date stored in a varchar field (stupid I know, but it's a
continual fight between the me (the dba), management, and our COBOL
programmers). I was indexing it as a date so that I can treat it as a date
in some of the non-COBOL programs/utilities that we have written.
The second index is rather stupid, it was an early index before I figured
out how to split a timestamp.
Anyway, is there a way to make the first index work? Otherwise we end up
with a seq scan on our billing table which is very painful.
Thanks,
Chris
On 6/6/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > You can't do this because to_date and other functions are not immutable.
> > 8.2 seems to be more picky about this -- the date conversions of
> > timestamptz columns are dependent on the current timezone.
>
> The reason 8.2 is more picky is that the function is less immutable
> thanks to the addition of locale-dependent functionality:
> http://archives.postgresql.org/pgsql-committers/2006-11/msg00264.php
>
> I gather that the underlying column is timestamp without tz, or it would
> never have worked in 8.1 either. That being the case, these index
> definitions seem pretty darn stupid anyway --- why aren't you just
> indexing on date_trunc or a plain cast to date?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 8.2.4 Won't Build 8.1 Functional Indexes
2007-06-06 18:12 8.2.4 Won't Build 8.1 Functional Indexes Chris Hoover <revoohc@gmail.com>
2007-06-06 19:54 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Alvaro Herrera <alvherre@commandprompt.com>
2007-06-06 23:41 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
2007-06-07 02:16 ` Re: 8.2.4 Won't Build 8.1 Functional Indexes Chris Hoover <revoohc@gmail.com>
@ 2007-06-07 04:35 ` Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 6+ messages in thread
From: Joshua D. Drake @ 2007-06-07 04:35 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-admin
Chris Hoover wrote:
> Well, the one index:
> CREATE INDEX acceptedbilling_to_date
> The second index is rather stupid, it was an early index before I
> figured out how to split a timestamp.
>
> Anyway, is there a way to make the first index work? Otherwise we end
> up with a seq scan on our billing table which is very painful.
First, please do not top post:
http://www.catb.org/jargon/html/T/top-post.html
Second:
create function mydate(varchar) returns varchar AS $$ select
to_char($1::date,'YYYYMMDD'); $$ LANGUAGE SQL IMMUTABLE;
create index foobar on date_test(mydate(date));
You will need to test this, but it does allow you to create the index.
Sincerely,
Joshua D. Drake
>
> Thanks,
>
> Chris
>
>
> On 6/6/07, *Tom Lane* <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:
>
> Alvaro Herrera <alvherre@commandprompt.com
> <mailto:alvherre@commandprompt.com>> writes:
> > You can't do this because to_date and other functions are not
> immutable.
> > 8.2 seems to be more picky about this -- the date conversions of
> > timestamptz columns are dependent on the current timezone.
>
> The reason 8.2 is more picky is that the function is less immutable
> thanks to the addition of locale-dependent functionality:
> http://archives.postgresql.org/pgsql-committers/2006-11/msg00264.php
>
> I gather that the underlying column is timestamp without tz, or it would
> never have worked in 8.1 either. That being the case, these index
> definitions seem pretty darn stupid anyway --- why aren't you just
> indexing on date_trunc or a plain cast to date?
>
> regards, tom lane
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-06-07 04:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-06 18:12 8.2.4 Won't Build 8.1 Functional Indexes Chris Hoover <revoohc@gmail.com>
2007-06-06 19:54 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-06-06 23:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-07 01:52 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-06-07 02:16 ` Chris Hoover <revoohc@gmail.com>
2007-06-07 04:35 ` Joshua D. Drake <jd@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox