pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
8.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>
  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 19:54  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Chris Hoover <revoohc@gmail.com>
  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 23:41  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Alvaro Herrera <alvherre@commandprompt.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-07 01:52  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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-07 02:16  Chris Hoover <revoohc@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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-07 04:35  Joshua D. Drake <jd@commandprompt.com>
  parent: Chris Hoover <revoohc@gmail.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