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(at)elma(dot)fr