Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B86D59FB302 for ; Wed, 6 Jun 2007 23:16:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 81317-04 for ; Wed, 6 Jun 2007 23:16:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 2E3049FB2DE for ; Wed, 6 Jun 2007 23:16:37 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id u2so633550uge for ; Wed, 06 Jun 2007 19:16:36 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Z3zxJ2THlwaUUsZW8cllcGmiBaR56mBT49mZbyEzjCCPb/htAW6abEIIHc38cwJy2ktdydYbHDRnxVjXG5/qmOAHPAp2KEASCZSvHg3NeELK+xv4uzUXU2rzWi2wDN8cDbubGcqIR3C7haoiVLW0HuFAjEim6ikYU/9x8Bus2P8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Xk/3noCAMdvS5oFSJV2JvdujaULWo8cDW+UY1VLR1/QkpMfUG4/1iKAQvRh/fKCEfmAbhQu4wVdeh+kWm4hhx4Sql05/uNkpT4THBXfDXR1P2jF/d/BixHIpKybup7/kvIdYwE7v6+Qy2yxhHEkP3G0AI36MjjeHjr2v/A0aUqk= Received: by 10.78.201.15 with SMTP id y15mr539044huf.1181182595868; Wed, 06 Jun 2007 19:16:35 -0700 (PDT) Received: by 10.78.201.16 with HTTP; Wed, 6 Jun 2007 19:16:35 -0700 (PDT) Message-ID: <1d219a6f0706061916k73deaa6bv2dd9e800e77a6f24@mail.gmail.com> Date: Wed, 6 Jun 2007 22:16:35 -0400 From: "Chris Hoover" To: "Tom Lane" Subject: Re: 8.2.4 Won't Build 8.1 Functional Indexes Cc: "Alvaro Herrera" , "pgsql-admin@postgresql.org Admin" In-Reply-To: <17138.1181173286@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_40046_1196447.1181182595844" References: <1d219a6f0706061112p355fc6cexb30a6b4b4bf187c3@mail.gmail.com> <20070606195459.GH11800@alvh.no-ip.org> <17138.1181173286@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/55 X-Sequence-Number: 25723 ------=_Part_40046_1196447.1181182595844 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 wrote: > > Alvaro Herrera 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 > ------=_Part_40046_1196447.1181182595844 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_40046_1196447.1181182595844--