pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
cast not IMMUTABLE?
7+ messages / 7 participants
[nested] [flat]

* cast not IMMUTABLE?
@ 2004-05-06 23:37  Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 3 replies; 7+ messages in thread

From: Gaetano Mendola @ 2004-05-06 23:37 UTC (permalink / raw)
  To: pgsql-admin

Hi all,
I have a table with ~ 3e+6 rows on it.

I do select on this table in this way:


(1) select * from user_logs where login_time::date = now()::date;


consider that login_time is a TIMESTAMPTZ with an index on it.

If I use the select in this way:

select * from user_logs where login_time = now();

the the index is used.

I'm trying to use define and index in order to help the query (1):


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 ?


How can I define an index for the query (1) ?


Regards
Gaetano Mendola













^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: cast not IMMUTABLE?
@ 2004-05-07 00:33  Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
  parent: Gaetano Mendola <mendola@bigfoot.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Sam Barnett-Cormack @ 2004-05-07 00:33 UTC (permalink / raw)
  To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-admin

On Fri, 7 May 2004, Gaetano Mendola wrote:

> Hi all,
> I have a table with ~ 3e+6 rows on it.
>
> I do select on this table in this way:
>
>
> (1) select * from user_logs where login_time::date = now()::date;
>
>
> consider that login_time is a TIMESTAMPTZ with an index on it.
>
> If I use the select in this way:
>
> select * from user_logs where login_time = now();
>
> the the index is used.
>
> I'm trying to use define and index in order to help the query (1):
>
>
> 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 ?
>
>
> How can I define an index for the query (1) ?

The way I have done such queries hs been to create functions, marked
immutable, that encapsulate the cast/non-immutable internal function, if
I know that it *is* really immutable, at least for my purposes, and use
a functional index.

-- 
Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: cast not IMMUTABLE?
@ 2004-05-07 00:41  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Gaetano Mendola <mendola@bigfoot.com>
  2 siblings, 2 replies; 7+ messages in thread

From: Tom Lane @ 2004-05-07 00:41 UTC (permalink / raw)
  To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-admin; pgsql-hackers@postgresql.org

Gaetano Mendola <mendola@bigfoot.com> 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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [ADMIN] cast not IMMUTABLE?
@ 2004-05-07 01:44  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Christopher Kings-Lynne @ 2004-05-07 01:44 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-hackers@postgresql.org

> 	(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?)

Seems reasonable...seems like the kind of thing that wouldn't have been 
tested/considered...

Chris




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: cast not IMMUTABLE?
@ 2004-05-08 23:33  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Gaetano Mendola <mendola@bigfoot.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Stephan Szabo @ 2004-05-08 23:33 UTC (permalink / raw)
  To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-admin


On Fri, 7 May 2004, Gaetano Mendola wrote:

> Hi all,
> I have a table with ~ 3e+6 rows on it.
>
> I do select on this table in this way:
>
>
> (1) select * from user_logs where login_time::date = now()::date;
>
>
> consider that login_time is a TIMESTAMPTZ with an index on it.
>
> If I use the select in this way:
>
> select * from user_logs where login_time = now();
>
> the the index is used.
>
> I'm trying to use define and index in order to help the query (1):
>
>
> 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 ?

I'd think the conversion of a timestamptz -> date would be dependent on
timezone which would make it not immutable.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: cast not IMMUTABLE?
@ 2005-03-24 13:15  Enrico Weigelt <weigelt@metux.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 7+ messages in thread

From: Enrico Weigelt @ 2005-03-24 13:15 UTC (permalink / raw)
  To: pgsql-admin

* Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> > why that cast is not considered IMMUTABLE ?
> 
> Because it depends on your TimeZone setting.  Observe:

BTW: whats really the difference between timezone and 
timezonetz ? I always used to use timestamp (w/o tz) and
thought timestamptz was just an question of presentation.


cu
-- 
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux IT service

  phone:     +49 36207 519931         www:       http://www.metux.de/
  fax:       +49 36207 519932         email:     contact@metux.de
  cellphone: +49 174 7066481
---------------------------------------------------------------------
 -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: cast not IMMUTABLE?
@ 2005-03-24 13:38  Alvaro Herrera <alvherre@dcc.uchile.cl>
  parent: Enrico Weigelt <weigelt@metux.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Alvaro Herrera @ 2005-03-24 13:38 UTC (permalink / raw)
  To: Enrico Weigelt <weigelt@metux.de>; +Cc: pgsql-admin

On Thu, Mar 24, 2005 at 02:15:52PM +0100, Enrico Weigelt wrote:
> * Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 
> > > why that cast is not considered IMMUTABLE ?
> > 
> > Because it depends on your TimeZone setting.  Observe:
> 
> BTW: whats really the difference between timezone and 
> timezonetz ? I always used to use timestamp (w/o tz) and
> thought timestamptz was just an question of presentation.

The difference is that timestamptz converts the value from your local
timezone to UTC before storing; and at display time, it converts it back
to the local timezone.  So you can meaningfully compare data that was
inserted on different timezones.

Also, keep in mind that in past releases, "timestamp" was synonym for
"timestamp with time zone", while right now it is synonym for "timestamp
without time zone", and to get the former behavior you have to qualify
it with "with time zone" or use "timestamptz".  I think the switchover
was made in 7.3 but I don't remember exactly.

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"When the proper man does nothing (wu-wei),
his thought is felt ten thousand miles." (Lao Tse)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2005-03-24 13:38 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-06 23:37 cast not IMMUTABLE? Gaetano Mendola <mendola@bigfoot.com>
2004-05-07 00:33 ` Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
2004-05-07 00:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-07 01:44   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-03-24 13:15   ` Enrico Weigelt <weigelt@metux.de>
2005-03-24 13:38     ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2004-05-08 23:33 ` Stephan Szabo <sszabo@megazone.bigpanda.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