trouble migrating from 6.3.2 (IRIX) to 7.0.2 (Linux)

George Armhold <armhold@cs.rutgers.edu> writes:

psql:/home/armhold/db.backup:13: ERROR: parser: parse error at or near "-"
psql:/home/armhold/db.backup:14: ERROR: Relation 'pfam' does not exist
psql:/home/armhold/db.backup:20: invalid command \.

So, how about showing us the first 20 or so lines of db.backup? You
can't really expect us to guess much from this amount of info ...

regards, tom lane

Tom Lane wrote:

So, how about showing us the first 20 or so lines of db.backup? You
can't really expect us to guess much from this amount of info ...

Fair enough question.

select datdba into table tmp_pg_shadow from pg_database where
datname = 't
emplate1';
delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
drop table tmp_pg_shadow;
copy pg_shadow from stdin;
nobody 60001 f t f t \N \N
seabee 10481 t t f t \N \N
yueyue 5535 t t f t \N \N
hagerty 27458 t t f t \N \N
armhold 29124 t t t t mysql \N
seredin 60002 f t f t \N
aynur 5869 t t f t \N \N
\.
\connect template1 postgres
create database armhold;
\connect armhold postgres
CREATE TABLE pgdump_oid (dummy int4);
COPY pgdump_oid WITH OIDS FROM stdin;
691497 0
\.
DROP TABLE pgdump_oid;
CREATE TABLE pfam (swiss_id varchar(16) NOT NULL, pfam_family
varchar(32), comme
nt varchar(80), sequence_data varchar(-5));
COPY pfam WITH OIDS FROM stdin;
536960 SWISS1 pfam_family1 comment data.... ABCDEFGHJ
536962 SWISS2 pfam_family1 comment data.... ABCDEFGHJ
536963 SWISS3 pfam_family1 comment data.... ABCDEFGHJ
536964 SWISS4 pfam_family1 comment data.... ABCDEFGHJ
536966 SWISS5 pfam_family2 comment data.... ABCDEFGHJ
\.
CREATE UNIQUE INDEX pfam_pkey on pfam using btree ( swiss_id
varchar_ops );
\connect template1 armhold
create database yueyue_test;
\connect yueyue_test armhold
CREATE TABLE pgdump_oid (dummy int4);
COPY pgdump_oid WITH OIDS FROM stdin;
691529 0
\.

Thanks.

--
George Armhold
Rutgers University
Bioinformatics Initiative

George Armhold <armhold@cs.rutgers.edu> writes:

So, how about showing us the first 20 or so lines of db.backup? You
can't really expect us to guess much from this amount of info ...

Fair enough question.

CREATE TABLE pfam (swiss_id varchar(16) NOT NULL, pfam_family
varchar(32), comment varchar(80), sequence_data varchar(-5));

Hrm.  varchar(-5) is pretty obviously broken :-(.  I don't want to
guess what sort of aberration prompted that output from pg_dump
--- 6.3.2 is before my time with Postgres.  Try editing the dump
file to have a more reasonable value for the varchar max width,
and see how it goes...

regards, tom lane

Tom Lane wrote:

CREATE TABLE pfam (swiss_id varchar(16) NOT NULL, pfam_family
varchar(32), comment varchar(80), sequence_data varchar(-5));

Hrm.  varchar(-5) is pretty obviously broken :-(.  I don't want to
guess what sort of aberration prompted that output from pg_dump
--- 6.3.2 is before my time with Postgres.  Try editing the dump
file to have a more reasonable value for the varchar max width,
and see how it goes...

Hmm, I seem to be getting negative values for tables that were created
without a maximum char length. Is (was) it legal postgres syntax to
say something like

create table foo (mytext varchar);

without a maximum length constraint? psql (or perhaps the Perl
interface) let me create such tables under 6.5.3 and they've worked
fine, up until this pg_dump. Would it be safe to simply edit the
dumped file, change the negative values to something reasonable and
reload the DB?

Thank you for your assistance.

--
George Armhold
Rutgers University
Bioinformatics Initiative

George Armhold <armhold@cs.rutgers.edu> writes:

Hmm, I seem to be getting negative values for tables that were created
without a maximum char length. Is (was) it legal postgres syntax to
say something like
create table foo (mytext varchar);
without a maximum length constraint?

It was and is, but it looks like 6.3.2's pg_dump did the wrong thing
with 'em. If you know that's how these fields were created, I'd say
that manually removing the (-5) from the table declarations is the
way to go.

regards, tom lane

Earlier in the week I was having problems moving from PG 6.3.2 under IRIX to
version 7.0.2 under Linux. I noticed that pg_dump was creating bogus
(negative value) entries for varchar fields that were initiallly created
with no maximum length constraint. Tom Lane suggested the following:

It was and is, but it looks like 6.3.2's pg_dump did the wrong thing
with 'em. If you know that's how these fields were created, I'd say
that manually removing the (-5) from the table declarations is the
way to go.

This fixed most of the problems. The one other thing I had to do was rename
a field "offset" to "my_offset" throughout all my tables. Apparently this
is a reserved word in the current version? I still have some more testing
to do, but I seem to have things working now. Thanks to the group, and Tom
Lane in particular.

"George Armhold" <armhold@cs.rutgers.edu> writes:

The one other thing I had to do was rename
a field "offset" to "my_offset" throughout all my tables. Apparently this
is a reserved word in the current version?

Yes, see LIMIT/OFFSET options in SELECT. You could still use it with
double-quotes around the name, but changing the column name is probably
less annoying in the long run...

regards, tom lane