Thread: Trouble with pg_dumpall import with 7.2
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é
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
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
Hervé 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
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
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
>
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
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
Hervé 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
...
> 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
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 alsounderstand that users need to avoid the operationsfor 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 frequentuse 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
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 adate()/cast() directly". It seemed ambiguous. Regards, Masaru Sugawara