Thread: pg 7.2.3 -> pg 7.3.3: pg_atoi error

Hello!

I tried to upgrade postgresql from v. 7.2.3 to 7.3.3.
I did pg_dump of the database and tried to read it in
using new version.

Below is a cut from my out file:

-- TOC Entry ID 101 (OID 16855)
--
-- Name: co_sys_meta_vtable_sqid Type: SEQUENCE Owner: root
--

CREATE SEQUENCE "co_sys_meta_vtable_sqid" start 1 increment 1 maxvalue
9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 103 (OID 16855)
--
-- Name: co_sys_meta_vtable_sqid Type: ACL Owner:
--

REVOKE ALL on "co_sys_meta_vtable_sqid" from PUBLIC;
GRANT ALL on "co_sys_meta_vtable_sqid" to "root";
GRANT ALL on "co_sys_meta_vtable_sqid" to "nobody";

--
-- TOC Entry ID 411 (OID 16857)
--
-- Name: co_sys_meta_vtable Type: TABLE Owner: root
--

CREATE TABLE "co_sys_meta_vtable" (
 "id" integer DEFAULT nextval('co_sys_meta_vtable_sqid'::text),
 "version" integer DEFAULT 0,
 "deleted" integer DEFAULT 0,
 "inherits_acl" integer DEFAULT 1,
 "area" text DEFAULT 'sys',
 "class_name" text DEFAULT '',
 "method_name" text DEFAULT '',
 "target_area" text DEFAULT 'sys',
 "target_class_name" text DEFAULT '',
 "id_permission" integer DEFAULT '',
 "id_onbefore_script" integer DEFAULT 0,
 "id_onbefore_method" integer DEFAULT 0,
 "id_script" integer DEFAULT 0,
 "id_onafter_script" integer DEFAULT 0,
 "id_onafter_method" integer DEFAULT 0,
 "profiled" integer DEFAULT 0,
 "sec_type" integer DEFAULT 0,
 "force_sec_type" integer DEFAULT 0
);

--
-- TOC Entry ID 412 (OID 16857)
--
-- Name: co_sys_meta_vtable Type: ACL Owner:
--

REVOKE ALL on "co_sys_meta_vtable" from PUBLIC;
GRANT ALL on "co_sys_meta_vtable" to "root";
GRANT ALL on "co_sys_meta_vtable" to "nobody";


I get an pg_atoi error while reading in co_sys_meta_vtable,
I suppose at this
 "id" integer DEFAULT nextval('co_sys_meta_vtable_sqid'::text),
particular line.

Is there a way to successfully transport such a db into new version?
The dump has almost 3G, so hand-editing is not a case..

TIA,
--
  Marcin Gil
  marcin.gil @ audax.com.pl, tel. 694972082
  OIS Audax Sp. z o.o., ul. Barlickiego 4, 97-200 Tomaszów Mazowiecki
  tel/fax (44) 7247530, 7244401

> I get an pg_atoi error while reading in co_sys_meta_vtable,
> I suppose at this
>  "id" integer DEFAULT nextval('co_sys_meta_vtable_sqid'::text),
> particular line.

Could you post the exact error message you are getting?

> Is there a way to successfully transport such a db into new version?
> The dump has almost 3G, so hand-editing is not a case..

Here are  some suggestions:

1. Try dumping and importing just the schema first. In my experience,
most of the issues are in there when upgrading, and the files are much
smaller.

2. If you suspect you know what change to make, you can use command line
tools such as Perl to do a find and replace on the file. Here's an
exapmle for Perl, which also creates a ".bak" file:

    perl -pi.bak -e 's/old/new/' schema.sql

3. Check the ChangeLogs between the versions in question to see if they
provide a clue about what might have changed.

4. Some editors will handle huge files. I suspect vim will do fairly
well. Other editors may be more tailored towards this application.

I have an old document about upgrading Postgres. Some of the concepts
may still apply:

http://mark.stosberg.com/Tech/postgres/pg-65-7-upgrade.html

    Mark

--
http://mark.stosberg.com/

From PostgreSQL changelog:
* An empty string ('') is no longer allowed as the input into an
   integer field. Formerly, it was silently interpreted as 0.
-  Disallow pg_atoi('') (Bruce)

----- Original Message -----
From: "Mark Stosberg" <mark@summersault.com>
To: <pgsql-novice@postgresql.org>
Sent: Saturday, August 02, 2003 4:39 AM
Subject: Re: [NOVICE] pg 7.2.3 -> pg 7.3.3: pg_atoi error


>
> > I get an pg_atoi error while reading in co_sys_meta_vtable,
> > I suppose at this
> >  "id" integer DEFAULT nextval('co_sys_meta_vtable_sqid'::text),
> > particular line.
>
> Could you post the exact error message you are getting?
>
> > Is there a way to successfully transport such a db into new version?
> > The dump has almost 3G, so hand-editing is not a case..
>
> Here are  some suggestions:
>
> 1. Try dumping and importing just the schema first. In my experience,
> most of the issues are in there when upgrading, and the files are much
> smaller.
>
> 2. If you suspect you know what change to make, you can use command line
> tools such as Perl to do a find and replace on the file. Here's an
> exapmle for Perl, which also creates a ".bak" file:
>
> perl -pi.bak -e 's/old/new/' schema.sql
>
> 3. Check the ChangeLogs between the versions in question to see if they
> provide a clue about what might have changed.
>
> 4. Some editors will handle huge files. I suspect vim will do fairly
> well. Other editors may be more tailored towards this application.
>
> I have an old document about upgrading Postgres. Some of the concepts
> may still apply:
>
> http://mark.stosberg.com/Tech/postgres/pg-65-7-upgrade.html
>
> Mark
>
> --
> http://mark.stosberg.com/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings