Hi all,

I'm in the process of switching frrom 7.1.3 to 7.2.

The only problem I have so far is the definition of an index.

The table contains a timestamp column. In 7.1.3 an index is defined as
such:

CREATE INDEX deb ON xxxx USING btree (date(timestamp coll) date_ops);

on 7.2 I have an error message saying that functrional indexes must but
made ISCACHABLE.

Now should date() be made iscachable, do I have an other way to create
trhis index?

CAST did'nt wok.

TIA

Regards,

-- 
Olivier PRENANT         	Tel:	+33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                      +33-6-07-63-80-64 (GSM)
FRANCE                      Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Mon, 4 Mar 2002 11:34:24 +0100 (MET)
Olivier PRENANT <ohp@pyrenet.fr>
Bug or Feature?
Olivier PRENANT <ohp@pyrenet.fr> writes:
> CREATE INDEX deb ON xxxx USING btree (date(timestamp coll) date_ops);
> on 7.2 I have an error message saying that functrional indexes must but
> made ISCACHABLE.

See previous discussion of this identical problem.  The fact is that
such an index is dangerous, because it depends on the timezone setting.

You might want to make the underlying column be timestamp without time
zone.

			regards, tom lane

Mon, 04 Mar 2002 10:08:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug or Feature? 
Hi, Tom

Thnaks for you reply; I don't need timezone in this application so it's
okay.

However, I've been trying to change the table on 7.1.3 to get ready for
the swap but there's no timestamp without timezone on 7.1.3; 

do I really have to edit the dump before reloading, ISTM that it's the
best way to generate errors...

Regards,
On Mon, 4 Mar 2002, Tom Lane wrote:

> Olivier PRENANT <ohp@pyrenet.fr> writes:
> > CREATE INDEX deb ON xxxx USING btree (date(timestamp coll) date_ops);
> > on 7.2 I have an error message saying that functrional indexes must but
> > made ISCACHABLE.
> 
> See previous discussion of this identical problem.  The fact is that
> such an index is dangerous, because it depends on the timezone setting.
> 
> You might want to make the underlying column be timestamp without time
> zone.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
Olivier PRENANT         	Tel:	+33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                      +33-6-07-63-80-64 (GSM)
FRANCE                      Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Mon, 4 Mar 2002 17:59:13 +0100
Olivier PRENANT <ohp@pyrenet.fr>
Re: Bug or Feature?
Hi, Tom,

Thanks for replying.
You were right (of course) I don't need timestamp on this app...
However I tried to change the column type on 7.1.3 to ease the
swith... AFAIK, there's no timestamp without timezone on 7.1.3; Please
tell me there's another way other than editing pg_dump by hand... That
would loose time and would call for errors...

Regards,
 On Mon, 4 Mar 2002, Tom Lane wrote:

> Olivier PRENANT <ohp@pyrenet.fr> writes:
> > CREATE INDEX deb ON xxxx USING btree (date(timestamp coll) date_ops);
> > on 7.2 I have an error message saying that functrional indexes must but
> > made ISCACHABLE.
> 
> See previous discussion of this identical problem.  The fact is that
> such an index is dangerous, because it depends on the timezone setting.
> 
> You might want to make the underlying column be timestamp without time
> zone.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
Olivier PRENANT         	Tel:	+33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                      +33-6-07-63-80-64 (GSM)
FRANCE                      Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Mon, 4 Mar 2002 22:22:18 +0100
Olivier PRENANT <ohp@pyrenet.fr>
Re: Bug or Feature?
> You were right (of course) I don't need timestamp on this app...
> However I tried to change the column type on 7.1.3 to ease the
> swith... AFAIK, there's no timestamp without timezone on 7.1.3; Please
> tell me there's another way other than editing pg_dump by hand... That
> would loose time and would call for errors...

Then import the data into your new database and use temporary tables and
drop/create to convert your existing data to the different schema. Then
create the function index you want.

There are probably other ways (as there are other schemas, such as using
DATE rather than TIMESTAMP), but as some point you should choose one and
just do it.

                       - Thomas

Mon, 04 Mar 2002 14:00:28 -0800
Thomas Lockhart <lockhart@fourpalms.org>
Re: Bug or Feature?