Hi,

I have done what exactly explains the documentation for the migration from my databases in 7.1.3 to 7.2 ...

But during the importation in Postgresql v7.2 of the data from the pg_dumpall ...
I get sometime this message :

psql:backup:24473309: ERROR:  DefineIndex: index function must be marked iscachable

backup is my pg_dumpall file ...
Why this message ?
May I have lost index ? or data ?

Could you explain me ?

Regards,
-- 
Hervï¿½

Sun, 17 Feb 2002 01:15:40 +0100
=?iso-8859-1?B?SGVydukgUGllZHZhY2hl?= <herve@elma.fr>
Trouble with pg_dumpall import with 7.2 
On Sun, 17 Feb 2002 01:15:40 +0100
HervPiedvache <herve@elma.fr> wrote:

> I have done what exactly explains the documentation for the migration from my databases in 7.1.3 to 7.2 ...
> 
> But during the importation in Postgresql v7.2 of the data from the pg_dumpall ...
> I get sometime this message :
> 
> psql:backup:24473309: ERROR:  DefineIndex: index function must be marked iscachable
> 
> backup is my pg_dumpall file ...
> Why this message ?
> May I have lost index ? or data ?
> 
> Could you explain me ?


Have you created indices on your functions defined without "with (iscachable)"
in 7.1.3 ?   If so,  an error in 7.2 (see below) will occur while you're 
upgrading PG by pg_dumpall, etc.  I would think you need to recreate
indices on your function redefined with it before dumping.  Instead, 
it seems to be OK that you redefine functions and create indices after
restoring as well. 

   ERROR:  DefineIndex: index function must be marked iscachable


Regards,
Masaru Sugawara


Sun, 17 Feb 2002 18:06:25 +0900
Masaru Sugawara <rk73@echna.ne.jp>
Re: Trouble with pg_dumpall import with 7.2
Hi Masaru,

OK it's a bug of postgreSQL 7.2 ??

I can apply an index on the field datelog where this field is a
timestamp like :

create index ix_datelog_date on datelog (date(datelog);

ERROR:  DefineIndex: index function must be marked iscachable

Or could you explain me how to set date() iscachable ?

regards,

Masaru Sugawara a ï¿½crit :
> 
> On Sun, 17 Feb 2002 01:15:40 +0100
> HervPiedvache <herve@elma.fr> wrote:
> 
> > I have done what exactly explains the documentation for the migration from my databases in 7.1.3 to 7.2 ...
> >
> > But during the importation in Postgresql v7.2 of the data from the pg_dumpall ...
> > I get sometime this message :
> >
> > psql:backup:24473309: ERROR:  DefineIndex: index function must be marked iscachable
> >
> > backup is my pg_dumpall file ...
> > Why this message ?
> > May I have lost index ? or data ?
> >
> > Could you explain me ?
> 
> Have you created indices on your functions defined without "with (iscachable)"
> in 7.1.3 ?   If so,  an error in 7.2 (see below) will occur while you're
> upgrading PG by pg_dumpall, etc.  I would think you need to recreate
> indices on your function redefined with it before dumping.  Instead,
> it seems to be OK that you redefine functions and create indices after
> restoring as well.
> 
>    ERROR:  DefineIndex: index function must be marked iscachable
> 
> Regards,
> Masaru Sugawara
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org

-- 
Hervï¿½ Piedvache

Elma Ingenierie Informatique
6, rue du Faubourg Saint-Honorï¿½
F-75008 - Paris - France 
http://www.elma.fr
Tel: +33-1-44949901
Fax: +33-1-44949902 
Email: herve@elma.fr

Wed, 20 Feb 2002 11:20:36 +0100
=?iso-8859-1?Q?Herv=E9?= Piedvache <herve@elma.fr>
Re: Trouble with pg_dumpall import with 7.2
=?iso-8859-1?Q?Herv=E9?= Piedvache <herve@elma.fr> writes:
> OK it's a bug of postgreSQL 7.2 ??
> I can apply an index on the field datelog where this field is a
> timestamp like :
> create index ix_datelog_date on datelog (date(datelog);
> ERROR:  DefineIndex: index function must be marked iscachable

It's not a bug, and it'd not be wise of you to override the decision to
mark date(timestamp) noncachable.  The reason it's marked that way is
that timestamp-to-date conversion depends on the TimeZone setting,
not only on the input argument.

If you make a functional index as above, it will misbehave as soon as
you have users with different timezone settings.

			regards, tom lane

Wed, 20 Feb 2002 09:59:06 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Trouble with pg_dumpall import with 7.2 
On Wed, 20 Feb 2002 11:20:36 +0100
HervPiedvache <herve@elma.fr> wrote:

> OK it's a bug of postgreSQL 7.2 ??
> 
> I can apply an index on the field datelog where this field is a
> timestamp like :
> 
> create index ix_datelog_date on datelog (date(datelog);
> 
> ERROR:  DefineIndex: index function must be marked iscachable
> 
> Or could you explain me how to set date() iscachable ?


Um, date() function...  That sounds like an unavoidable error.

Recently Brent has replied to this sort of subjects on the mailing list
of sql, and Tom has implied to us that unexpected results might be caused
by depending on the timezone setting.  I would think that you're able to
create an index easily like (2), but need to inspect the results cautiously.


(1)create function mydate(timestamp) returns date as '
        select date($1);
    ' language 'sql' with (iscachable);

(2)create index ix_datelog_date on datelog(mydate(datelog));

(3)e.g.:
    select count(*) from datelog
    where mydate(datelog) >= '2002-2-1' and mydate(datelog) <= '2002-2-5'  ;

   instead of:
   select count(*) from datelog
   where date(datelog) >= '2002-2-1' and date(datelog) <= '2002-2-5'  ;




>On Fri, 15 Feb 2002 11:00:11 -0500
>Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Nick Fankhauser" <nickf@ontko.com> writes:
> > staging=# create index event_day on
> > event(date_trunc('day',event_date_time));
> > ERROR:  parser: parse error at or near "'"
> 
> You missed the fine print that says the function must be applied to
> table column name(s) only.  No constants, no expressions.
> 
> You can get around this limitation by defining a custom function that
> fills in whatever extra baggage you need.
> 
> My own first thought was that you could just use conversion to type
> date, but that falls down.  Not for syntax reasons though:
> 
> regression=# create table foo (event_date_time timestamp);
> CREATE
> regression=# create index event_day on foo (date(event_date_time));
> ERROR:  DefineIndex: index function must be marked iscachable
> 
> This raises a subtle point that you'd better think about before you go
> too far in this direction: truncating a timestamp to date is not a very
> well-defined operation, because it depends on the timezone setting.
> Indexes on functions whose values might vary depend on who's executing
> them are a recipe for disaster --- the index is almost certainly going
> to wind up corrupted (out of order).
> 
> 			regards, tom lane


Regards,
Masaru Sugawara


Thu, 21 Feb 2002 02:02:46 +0900
Masaru Sugawara <rk73@echna.ne.jp>
Re: Trouble with pg_dumpall import with 7.2
Couldn't you simply index on the cast of the timestamp to date?

create index ix_test on testtable (cast(things as date));
ERROR:  parser: parse error at or near "cast"

Evidently not...
--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Masaru Sugawara" <rk73@echna.ne.jp>
To: <herve@elma.fr>
Cc: <pgsql-hackers@postgresql.org>
Sent: Wednesday, February 20, 2002 12:02 PM
Subject: Re: [HACKERS] Trouble with pg_dumpall import with 7.2


> On Wed, 20 Feb 2002 11:20:36 +0100
> HervPiedvache <herve@elma.fr> wrote:
>
> > OK it's a bug of postgreSQL 7.2 ??
> >
> > I can apply an index on the field datelog where this field is a
> > timestamp like :
> >
> > create index ix_datelog_date on datelog (date(datelog);
> >
> > ERROR:  DefineIndex: index function must be marked iscachable
> >
> > Or could you explain me how to set date() iscachable ?
>
>
> Um, date() function...  That sounds like an unavoidable error.
>
> Recently Brent has replied to this sort of subjects on the mailing
list
> of sql, and Tom has implied to us that unexpected results might be
caused
> by depending on the timezone setting.  I would think that you're
able to
> create an index easily like (2), but need to inspect the results
cautiously.
>
>
> (1)create function mydate(timestamp) returns date as '
>         select date($1);
>     ' language 'sql' with (iscachable);
>
> (2)create index ix_datelog_date on datelog(mydate(datelog));
>
> (3)e.g.:
>     select count(*) from datelog
>     where mydate(datelog) >= '2002-2-1' and mydate(datelog) <=
'2002-2-5'  ;
>
>    instead of:
>    select count(*) from datelog
>    where date(datelog) >= '2002-2-1' and date(datelog) <= '2002-2-5'
;
>
>
>
>
> >On Fri, 15 Feb 2002 11:00:11 -0500
> >Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > "Nick Fankhauser" <nickf@ontko.com> writes:
> > > staging=# create index event_day on
> > > event(date_trunc('day',event_date_time));
> > > ERROR:  parser: parse error at or near "'"
> >
> > You missed the fine print that says the function must be applied
to
> > table column name(s) only.  No constants, no expressions.
> >
> > You can get around this limitation by defining a custom function
that
> > fills in whatever extra baggage you need.
> >
> > My own first thought was that you could just use conversion to
type
> > date, but that falls down.  Not for syntax reasons though:
> >
> > regression=# create table foo (event_date_time timestamp);
> > CREATE
> > regression=# create index event_day on foo
(date(event_date_time));
> > ERROR:  DefineIndex: index function must be marked iscachable
> >
> > This raises a subtle point that you'd better think about before
you go
> > too far in this direction: truncating a timestamp to date is not a
very
> > well-defined operation, because it depends on the timezone
setting.
> > Indexes on functions whose values might vary depend on who's
executing
> > them are a recipe for disaster --- the index is almost certainly
going
> > to wind up corrupted (out of order).
> >
> > regards, tom lane
>
>
> Regards,
> Masaru Sugawara
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


Wed, 20 Feb 2002 13:49:21 -0500
"Rod Taylor" <rbt@zort.ca>
Re: Trouble with pg_dumpall import with 7.2
Masaru Sugawara <rk73@echna.ne.jp> writes:
> (1)create function mydate(timestamp) returns date as '
>         select date($1);
>     ' language 'sql' with (iscachable);

If you do it that way then you are simply opening yourself up to exactly
the error that the noncachability check is trying to save you from
making.

You could probably do it safely by hard-wiring the time zone to be used
into the function.  I think something like this would work:

create function mydate(timestamp with time zone) returns date as '
      select date($1 AT TIME ZONE ''EST'');
' language 'sql' with (iscachable);

(substitute time zone of your choice, of course).

BTW, if the table is at all large then you'd probably be better off to
use a plpgsql function instead.  SQL-language functions are rather
inefficient IIRC.

			regards, tom lane

Wed, 20 Feb 2002 14:06:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Trouble with pg_dumpall import with 7.2 
As always, wisdom personified by Tom Lane said :

> > regression=# create table foo (event_date_time timestamp);
> > CREATE
> > regression=# create index event_day on foo (date(event_date_time));
> > ERROR:  DefineIndex: index function must be marked iscachable
> >
> > This raises a subtle point that you'd better think about before you go
> > too far in this direction: truncating a timestamp to date is not a very
> > well-defined operation, because it depends on the timezone setting.
> > Indexes on functions whose values might vary depend on who's executing
> > them are a recipe for disaster --- the index is almost certainly going
> > to wind up corrupted (out of order).


Tom, I clearly understand the problem but it is your developer's (I
should say "your designer's") POV.

Most of us, users of PG (app developers I mean) never have to deal
with timezones and that's where we conflict : we can't use (I mean as
efficiently as could be) date indexes because of timezones which WE
don't care about (at least in, say, 90% of the apps that use DB).

Can't we find a middle point ? I mean keep the current restrictions
regarding timezones but be able to create, say "noTZdate" field types
that would be cachable ?

Today we have only the options of :

- using no date index
- use inefficient date indexes
- convert dates to integers (eg: Julian) and index the integer
- convert dates to ISO strings and index the string

Same restrictions for date+time fields.

There's still something I don't understand : how are timestamps stored?

Don't you store :
  1)universaltime or gmt
  2)timezone ?
This way, timezones are only used to display a local date from a
universal value (which can be sorted normally)

Is it :
  1)localtime
  2)timezone

I guess I should RTFM or RTFS(ources)... Got a URL for dummies like me?

   Oops! After re-reading my writing, I realize timezones are
   important in the US though it does not change the problem.

Regards,
-- 
Hervï¿½ Piedvache

Elma Ingenierie Informatique
6, rue du Faubourg Saint-Honorï¿½
F-75008 - Paris - France 
http://www.elma.fr
Tel: +33-1-44949901
Fax: +33-1-44949902 
Email: herve@elma.fr

Thu, 21 Feb 2002 15:26:23 +0100
=?iso-8859-1?Q?Herv=E9?= Piedvache <herve@elma.fr>
Re: Trouble with pg_dumpall import with 7.2
=?iso-8859-1?Q?Herv=E9?= Piedvache <herve@elma.fr> writes:
> Most of us, users of PG (app developers I mean) never have to deal
> with timezones and that's where we conflict : we can't use (I mean as
> efficiently as could be) date indexes because of timezones which WE
> don't care about (at least in, say, 90% of the apps that use DB).

If you don't care about timezone handling, you should be using timestamp
without time zone.  Observe:

regression=# create table foo (tnz timestamp without time zone,
regression(#                   tz timestamp with time zone);
CREATE
regression=# create index fooi on foo(date(tz));
ERROR:  DefineIndex: index function must be marked iscachable
regression=# create index fooi on foo(date(tnz));
CREATE
regression=#

timestamp-with-timezone is really GMT under the hood; it's rotated to
your local timezone (as shown by TimeZone) before conversion to date,
and that's why timestamp-with-timezone-to-date is, and should be,
noncachable.

On the other hand, timestamp without time zone is not assumed to be
in any particular zone, and there's never any rotation to local or to
GMT.  So that conversion to date is deterministic.

Some examples (I'm in EST, ie GMT-5):

regression=# select '2002-02-21 08:00-05'::timestamp with time zone;
      timestamptz
------------------------
 2002-02-21 08:00:00-05
(1 row)

regression=# select '2002-02-21 08:00+09'::timestamp with time zone;
      timestamptz
------------------------
 2002-02-20 18:00:00-05
(1 row)

regression=# select date('2002-02-21 08:00+09'::timestamp with time zone);
    date
------------
 2002-02-20
(1 row)

regression=# select '2002-02-21 08:00+09'::timestamp without time zone;
      timestamp
---------------------
 2002-02-21 08:00:00           -- the timezone indication is simply dropped
(1 row)

regression=# select date('2002-02-21 08:00+09'::timestamp without time zone);
    date
------------
 2002-02-21
(1 row)

BTW, 7.2 assumes plain "timestamp" to denote "timestamp with time zone";
this is for backwards compatibility with the behavior of previous
releases' timestamp datatype.  However, the SQL spec says that
"timestamp" should mean ""timestamp without time zone", so we are
probably going to change over eventually.

(Hey Thomas, did I get all that right?)

			regards, tom lane

Thu, 21 Feb 2002 09:47:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Trouble with pg_dumpall import with 7.2 
...
> BTW, 7.2 assumes plain "timestamp" to denote "timestamp with time zone";
> this is for backwards compatibility with the behavior of previous
> releases' timestamp datatype.  However, the SQL spec says that
> "timestamp" should mean ""timestamp without time zone", so we are
> probably going to change over eventually.
> (Hey Thomas, did I get all that right?)

Yes, including the change in default in an upcoming release. Well,
actually I have to admit I lost concentration somewhere in the middle of
the "power examples" so didn't check those carefully ;) :))

                - Thomas

Thu, 21 Feb 2002 15:22:31 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Trouble with pg_dumpall import with 7.2
On Wed, 20 Feb 2002 14:06:46 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Masaru Sugawara <rk73@echna.ne.jp> writes:
> > (1)create function mydate(timestamp) returns date as '
> >         select date($1);
> >     ' language 'sql' with (iscachable);
> 
> If you do it that way then you are simply opening yourself up to exactly
> the error that the noncachability check is trying to save you from
> making.

 Okey.
 It turned out that the setting time zone was insufficient -- but I also
 understand that users need to avoid the operations for which 
 robustness/reliability is lost.  

> 
> You could probably do it safely by hard-wiring the time zone to be used
> into the function.  I think something like this would work:
> 
> create function mydate(timestamp with time zone) returns date as '
>       select date($1 AT TIME ZONE ''EST'');
> ' language 'sql' with (iscachable);
> 
> (substitute time zone of your choice, of course).

 Thanks a lot. there are likely to be opportunities of making frequent
 use of it.

> 
> BTW, if the table is at all large then you'd probably be better off to
> use a plpgsql function instead.  SQL-language functions are rather
> inefficient IIRC.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly





Regards,
Masaru Sugawara


Fri, 22 Feb 2002 01:59:09 +0900
Masaru Sugawara <rk73@echna.ne.jp>
Re: Trouble with pg_dumpall import with 7.2
On Wed, 20 Feb 2002 13:49:21 -0500
"Rod Taylor" <rbt@zort.ca> wrote:

> Couldn't you simply index on the cast of the timestamp to date?
> 
> create index ix_test on testtable (cast(things as date));
> ERROR:  parser: parse error at or near "cast"
> 
> Evidently not...

  I'm sorry.

>> I would think that you're able to create an index easily like (2), but need to >> inspect the results cautiously.

 This means "create an index on an function", not "create an index on a
 date()/cast() directly".    It seemed ambiguous.



Regards,
Masaru Sugawara


Fri, 22 Feb 2002 02:37:21 +0900
Masaru Sugawara <rk73@echna.ne.jp>
Re: Trouble with pg_dumpall import with 7.2