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************ ***********@new s2.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_vta ble.

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

CREATE SEQUENCE "co_sys_meta_vt able_sqid" start 1 increment 1
maxvalue 922337203685477 5807 minvalue 1 cache 1;

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

REVOKE ALL on "co_sys_meta_vt able_sqid" from PUBLIC;
GRANT ALL on "co_sys_meta_vt able_sqid" to "root";
GRANT ALL on "co_sys_meta_vt able_sqid" to "nobody";

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

CREATE TABLE "co_sys_meta_vt able" (
"id" integer DEFAULT nextval('co_sys _meta_vtable_sq id'::text),
"version" integer DEFAULT 0,
"deleted" integer DEFAULT 0,
"inherits_a cl" integer DEFAULT 1,
"area" text DEFAULT 'sys',
"class_name " text DEFAULT '',
"method_nam e" text DEFAULT '',
"target_are a" text DEFAULT 'sys',
"target_class_n ame" text DEFAULT '',
"id_permiss ion" integer DEFAULT '',
"id_onbefore_sc ript" integer DEFAULT 0,
"id_onbefore_me thod" integer DEFAULT 0,
"id_script" integer DEFAULT 0,
"id_onafter_scr ipt" integer DEFAULT 0,
"id_onafter_met hod" 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_sq id'::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_sq id'::text), to: "id" integer, and then after the database has started
DROP SEQUENCE 'co_sys_meta_vt able_sqid'
then
CREATE SEQUENCE 'co_sys_meta_vt able_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_sqi d', (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************ ***********@new s2.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_vta ble.

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

CREATE SEQUENCE "co_sys_meta_vt able_sqid" start 1 increment 1
maxvalue 922337203685477 5807 minvalue 1 cache 1;

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

REVOKE ALL on "co_sys_meta_vt able_sqid" from PUBLIC;
GRANT ALL on "co_sys_meta_vt able_sqid" to "root";
GRANT ALL on "co_sys_meta_vt able_sqid" to "nobody";

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

CREATE TABLE "co_sys_meta_vt able" (
"id" integer DEFAULT nextval('co_sys _meta_vtable_sq id'::text),
"version" integer DEFAULT 0,
"deleted" integer DEFAULT 0,
"inherits_a cl" integer DEFAULT 1,
"area" text DEFAULT 'sys',
"class_name " text DEFAULT '',
"method_nam e" text DEFAULT '',
"target_are a" text DEFAULT 'sys',
"target_class_n ame" text DEFAULT '',
"id_permiss ion" integer DEFAULT '',
"id_onbefore_sc ript" integer DEFAULT 0,
"id_onbefore_me thod" integer DEFAULT 0,
"id_script" integer DEFAULT 0,
"id_onafter_scr ipt" integer DEFAULT 0,
"id_onafter_met hod" integer DEFAULT 0,
"profiled" integer DEFAULT 0,
"sec_type" integer DEFAULT 0,
"force_sec_type " integer DEFAULT 0
);

TIA

[Marcin Gil]

W artykule <oI************ ***********@new s3.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************ ***********@new s3.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**@linuxwave s.com> wrote in message
news:sG******** *************** @news1.calgary. shaw.ca...

Marcin Gil wrote:

W artykule <oI************ ***********@new s3.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