pg_dump 7.2.3, pg_restore 7.3.3

[Marcin Gil]

Hi!

I dumped a DB using pg_dump from version 7.2.3
and tried to read it into DB with psql|pg_restore of version 7.3.3.
I got an error 'pg_atoi: zero-length string' during the creation
of relations. Don't know what to do with it.

TIA
--
Marcin "Dentharg" Gil
RLU# 221865 :: email# mgil (at) vernet pl
G-G# 185057 :: JID# dentharg (at) chrome pl

[rstp]

Did it give you any indication of which line caused the error? If so,
start there. pg_atoi() is the method used to convert ascii to integer,
so you can search for the function call and see what's calling it. Also,
in the migration notes it says that "An empty string ('') is no longer
allowed as the input into an integer field. Formerly, it was silently
interpreted as 0." Could this be related?

Good luck
Ron
Marcin Gil wrote:

Hi!

I dumped a DB using pg_dump from version 7.2.3
and tried to read it into DB with psql|pg_restore of version 7.3.3.
I got an error 'pg_atoi: zero-length string' during the creation
of relations. Don't know what to do with it.

TIA

[Marcin Gil]

W artykule <l2***********************@news2.calgary.shaw.ca > rstp napisa³(a):

Did it give you any indication of which line caused the error? If so,
start there. pg_atoi() is the method used to convert ascii to integer,
so you can search for the function call and see what's calling it. Also,
in the migration notes it says that "An empty string ('') is no longer
allowed as the input into an integer field. Formerly, it was silently
interpreted as 0." Could this be related?

Following a cut from my dump.
It causes an pg_atoi() error in creation of co_sys_meta_vtable.

--
-- 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
);

TIA
--
Marcin "Dentharg" Gil
RLU# 221865 :: email# mgil (at) vernet pl
G-G# 185057 :: JID# dentharg (at) chrome pl

[rstp]

> "id" integer DEFAULT nextval('co_sys_meta_vtable_sqid'::text),
????????????
The sequence is being cast to text. I'd recommend that you change the
line in your pg_dump file from :

"id" integer DEFAULT nextval('co_sys_meta_vtable_sqid'::text), to: "id" integer, and then after the database has started
DROP SEQUENCE 'co_sys_meta_vtable_sqid'
then
CREATE SEQUENCE 'co_sys_meta_vtable_sqid' ....
Check the documentation for correct form, and you can initialize the
sequence to the next val. Maybe along the lines of
SELECT setval('co_sys_meta_vtable_sqid', (max(ID) + 1)) FROM myTable;

Unless you have this problem with all of your sequences, this is a
quick-fix.

hth
Ron
Marcin Gil wrote: W artykule <l2***********************@news2.calgary.shaw.ca > rstp napisa³(a):

Did it give you any indication of which line caused the error? If so,
start there. pg_atoi() is the method used to convert ascii to integer,
so you can search for the function call and see what's calling it. Also,
in the migration notes it says that "An empty string ('') is no longer
allowed as the input into an integer field. Formerly, it was silently
interpreted as 0." Could this be related?

Following a cut from my dump.
It causes an pg_atoi() error in creation of co_sys_meta_vtable.

--
-- 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
);

TIA

[Marcin Gil]

W artykule <oI***********************@news3.calgary.shaw.ca > rstp napisa³(a):

Unless you have this problem with all of your sequences, this is a
quick-fix.

That's the problem. The dump has almost 3G, and lotsa
fixes would be needed.

--
Marcin "Dentharg" Gil
RLU# 221865 :: email# mgil (at) vernet pl
G-G# 185057 :: JID# dentharg (at) chrome pl

[rstp]

Marcin Gil wrote:

W artykule <oI***********************@news3.calgary.shaw.ca > rstp napisa³(a):

Unless you have this problem with all of your sequences, this is a
quick-fix.

That's the problem. The dump has almost 3G, and lotsa
fixes would be needed.

Hmmm....... that's a problem. I suggest that you post your problem on
the discussion boards at postgresql.org. You have to sign up for them
first (send them your email address), but it's very quick. There's much
more activity there and there is likely to be someone who knows what to
do. If you do find a solution there, please post it back to this group.

Ron

[Sean Utt]

Hi,

I would dump the schema and the data separately, and then split the schema
into two pieces, the table creation portion, and the table modification
portion (constraints, etc.), then fix the table creation section in an
editor, and load them into the new database in the order: table creation,
then data, then modifications. This is the order a full pg_dump is in, but
it would be too big to edit easily to correct the table creation code
quickly with an editor. this assumes of course that you have 3 gig of data,
not 3 gig of schema :-D

Sean
Web Solutions That Work Developing custom web solutions designed
specifically to accomplish the unique objectives of our clients. Phone
503-639-2727 Fax [phone]
"rstp" <rs**@linuxwaves.com> wrote in message
news:sG***********************@news1.calgary.shaw. ca...

Marcin Gil wrote:

W artykule <oI***********************@news3.calgary.shaw.ca > rstp napisa³(a):

Unless you have this problem with all of your sequences, this is a
quick-fix.

That's the problem. The dump has almost 3G, and lotsa
fixes would be needed.

Hmmm....... that's a problem. I suggest that you post your problem on
the discussion boards at postgresql.org. You have to sign up for them
first (send them your email address), but it's very quick. There's much
more activity there and there is likely to be someone who knows what to
do. If you do find a solution there, please post it back to this group.

Ron