pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedA couple of errors encountered in 7.1.3=>7.2.1-2 data migration
5+ messages / 3 participants
[nested] [flat]
* A couple of errors encountered in 7.1.3=>7.2.1-2 data migration
@ 2002-05-10 14:11 Nick Fankhauser <nickf@ontko.com>
0 siblings, 1 reply; 5+ messages in thread
From: Nick Fankhauser @ 2002-05-10 14:11 UTC (permalink / raw)
To: pgsql-admin; +Cc: Ray Ontko <rayo@ontko.com>
Hi-
The background info:
Debian 2.4.14 on both source & destination boxes
PostgreSQL 7.1.3 on source
PostgreSQL 7.2.1-2 on destination
We are testing 7.2 with our application by installing it on a separate box &
accessing it via the network. The 7.2 install is just a plain vanilla
install using the Debian package which went well. (Well done, as usual
Oliver!)
The export of data from the old system was simply a pg_dumpall, and the
import was a load of the dump file using psql and an empty newly created
database.
The load returned two errors that I don't understand:
(1)
ERROR: DefineIndex: index function must be marked iscachable
unfortunately, I don't know which line of the dumpfile we were on when this
occurred, but It only happened once, and the only indexes I can think of
that might be different from the rest are functional indexes. One of them is
an index on upper(field), and the other is based on a function defined in
psql that uses the translate function.
(2)
ERROR: cannot find attribute 1 of relation schedule_notification_log
ERROR: cannot find attribute 1 of relation schedule_notification_log
We got this one twice, just as shown above. When I do a \d
schedule_notification_log, I get this result:
develop=# \d schedule_notification_log
Table "schedule_notification_log"
Column | Type | Modifiers
--------+------+-----------
Other than these errors, the data migration went very smoothly, and our
applications have functioned at least partially without modification. We
haven't been able to test thoroughly yet, because a few queries are very
slow, and the second error above seems to keep vacuum analyze from running
as well.
Any thoughts on these errors are appreciated.
Regards,
-Nick
--------------------------------------------------------------------------
Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: A couple of errors encountered in 7.1.3=>7.2.1-2 data migration
@ 2002-05-10 15:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nick Fankhauser <nickf@ontko.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2002-05-10 15:44 UTC (permalink / raw)
To: nickf@ontko.com; +Cc: pgsql-admin; Ray Ontko <rayo@ontko.com>
"Nick Fankhauser" <nickf@ontko.com> writes:
> The load returned two errors that I don't understand:
> ERROR: DefineIndex: index function must be marked iscachable
This means what it says: add "with (iscachable)" to your function
declaration. This is intended to remind you that it is not sensible
to build an index on a function that has time-varying results...
> ERROR: cannot find attribute 1 of relation schedule_notification_log
This is more disturbing. Can you provide the sequence of commands that
led up to it?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: A couple of errors encountered in 7.1.3=>7.2.1-2 data migration
@ 2002-05-10 16:35 Ray Ontko <rayo@ontko.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Ray Ontko @ 2002-05-10 16:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin; Ray Ontko <rayo@ontko.com>
Tom, et al,
> > ERROR: cannot find attribute 1 of relation schedule_notification_log
>
> This is more disturbing. Can you provide the sequence of commands that
> led up to it?
Here are a few related datapoints.
1) We think that schedule_notification_log is the only table that
we loaded from the dump that had _no_ rows in it.
2) We encountered the same "cannot find attribute" error when doing
something like this:
develop=# create table temp_event as select * from event ;
...
develop=# \d temp_event
Table "temp_event"
Attribute | Type | Modifier
-----------+------+----------
develop=# select count(*) from temp_event ;
ERROR: cannot find attribute 1 of relation temp_event
develop=# drop table temp_event ;
ERROR: cannot find attribute 1 of relation temp_event
Note that "event" has plenty of rows. It may be our only table
that includes the INTERVAL datatype.
BTW, How do I get rid of temp_event?
So: Is there something about a table that might cause it to NOT
load correctly? Is there something about a table that might
cause it to not copy correctly (using create as select * from )?
Ray
----------------------------------------------------------------------
Ray Ontko rayo@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: A couple of errors encountered in 7.1.3=>7.2.1-2 data migration
@ 2002-05-10 16:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ray Ontko <rayo@ontko.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2002-05-10 16:48 UTC (permalink / raw)
To: Ray Ontko <rayo@ontko.com>; +Cc: pgsql-admin
Ray Ontko <rayo@ontko.com> writes:
> develop=# select count(*) from temp_event ;
> ERROR: cannot find attribute 1 of relation temp_event
I'm beginning to think there is something seriously messed up about your
installation. The simplest theory is that the indexes on pg_attribute
are corrupted. You could try a query that won't use the indexes, eg
select (select relname from pg_class where oid = attrelid)
from pg_attribute where attnum = 1 order by 1;
and look through the output to see if temp_event and the other broken
table are mentioned. If so, then the rows are there but the index is
wrong. You should be able to recover using REINDEX --- read the
reference page for it carefully before executing the recovery procedure!
If that doesn't work, re-initdb and re-importation of your dump would be
a good next step. Something must have dropped a bit somewhere...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: A couple of errors encountered in 7.1.3=>7.2.1-2 data migration
@ 2002-05-10 17:24 Nick Fankhauser <nickf@ontko.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Nick Fankhauser @ 2002-05-10 17:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Ray Ontko <rayo@ontko.com>; +Cc: pgsql-admin
Tom-
Thanks! your diagnosis was correct & the repair worked.
-Nick & Ray
> I'm beginning to think there is something seriously messed up about your
> installation. The simplest theory is that the indexes on pg_attribute
> are corrupted.
...
> You should be able to recover using REINDEX
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-05-10 17:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-10 14:11 A couple of errors encountered in 7.1.3=>7.2.1-2 data migration Nick Fankhauser <nickf@ontko.com>
2002-05-10 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-05-10 16:35 ` Ray Ontko <rayo@ontko.com>
2002-05-10 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-05-10 17:24 ` Nick Fankhauser <nickf@ontko.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