X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 85119D1F345; Thu, 6 May 2004 21:42:16 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 25467-05; Thu, 6 May 2004 21:41:58 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 50664D1F366; Thu, 6 May 2004 21:41:54 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i470fsMn026504; Thu, 6 May 2004 20:41:54 -0400 (EDT) To: Gaetano Mendola Cc: pgsql-admin@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: cast not IMMUTABLE? In-reply-to: References: Comments: In-reply-to Gaetano Mendola message dated "Fri, 07 May 2004 01:37:29 +0200" Date: Thu, 06 May 2004 20:41:54 -0400 Message-ID: <26503.1083890514@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/64 X-Sequence-Number: 13380 Gaetano Mendola writes: > consider that login_time is a TIMESTAMPTZ with an index on it. > test# create index idx on user_logs ( (login_time::date) ); > ERROR: functions in index expression must be marked IMMUTABLE > why that cast is not considered IMMUTABLE ? Because it depends on your TimeZone setting. Observe: regression=# begin; BEGIN regression=# select now(), now()::date; now | now -------------------------------+------------ 2004-05-06 20:40:16.038307-04 | 2004-05-06 (1 row) regression=# set timezone to 'GMT'; SET regression=# select now(), now()::date; now | now -------------------------------+------------ 2004-05-07 00:40:16.038307+00 | 2004-05-07 (1 row) now() didn't change, but now()::date did. > How can I define an index for the query (1) ? What behavior are you really after here? You could change the column to be timestamp without time zone (which would have an immutable conversion to date), but if you want login_time to reflect some sort of absolute reality then that's likely not the right answer. Another possibility is to force the date conversion to occur with respect to a particular time zone. I was going to suggest something like (login_time at time zone 'GMT')::date but upon experimenting I see that that isn't considered immutable either :-(. Offhand I think this may be an oversight --- I can't see any reason for the various flavors of AT TIME ZONE (a/k/a timezone() function) not to be considered immutable. (Hackers, any comments?) regards, tom lane