Bug #578: pg_dumpall from 7.1.3 can not be imported in 7.2.0

Rainer Tammer (tammer(at)tammer(dot)net) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
pg_dumpall from 7.1.3 can not be imported in 7.2.0

Long Description
The DB dump from 7.1.3 can not be imported in 7.2.0.

1. error the user root has id 0 and the import says:
You are now connected to database template1.

DELETE 0
psql:old713:7: ERROR: user id must be positive

2. tables with complex foreign key constrains will not be imported

see code example (build script)

How can I upgrade from 7.1.3 to 7.2.0 ?????

Bye
Rainer Tammer

Sample Code
-- ------------------------------------------------------------------------
-- Create all tables for SHD
--
-- Ver.: 1.0.1
-- ------------------------------------------------------------------------

--
-- org
--

-- drop old stuff
DROP FUNCTION org_o_id_max();
DROP SEQUENCE org_o_id_seq;
DROP TABLE org;

-- create new tables
CREATE SEQUENCE org_o_id_seq;
CREATE TABLE org (
o_id INT4 DEFAULT nextval('org_o_id_seq') PRIMARY KEY,
o_name TEXT NOT NULL UNIQUE CHECK (o_name <> ''),
o_short TEXT NOT NULL UNIQUE CHECK (o_short <> '')
);
CREATE FUNCTION org_o_id_max() RETURNS INT4 AS 'SELECT max(o_id) FROM org' LANGUAGE 'sql';
COPY org FROM '/daten/source/shd-1.0.1/pgsql/org.dat' USING DELIMITERS ';';
SELECT setval('org_o_id_seq', org_o_id_max());

--
-- usr
--

-- drop old stuff
DROP FUNCTION usr_u_id_max();
DROP SEQUENCE usr_u_id_seq;
DROP TABLE usr;

-- create new tables
CREATE SEQUENCE usr_u_id_seq;
CREATE TABLE usr (
u_id INT4 DEFAULT nextval('usr_u_id_seq') PRIMARY KEY,
u_name TEXT NOT NULL UNIQUE CHECK (u_name <> ''),
u_password TEXT,
u_group TEXT,
u_dep TEXT,
u_org_id INT4,
u_email TEXT,
u_telefon TEXT,
u_own_queue BOOL,
u_del_tiket BOOL,
u_edit_usr BOOL,
FOREIGN KEY (u_org_id) REFERENCES org (o_id)
);
CREATE FUNCTION usr_u_id_max() RETURNS INT4 AS 'SELECT max(u_id) FROM usr' LANGUAGE 'sql';
COPY usr FROM '/daten/source/shd-1.0.1/pgsql/usr.dat' USING DELIMITERS ';';
SELECT setval('usr_u_id_seq', usr_u_id_max());

--
-- queue
--

-- drop old stuff
DROP FUNCTION queue_q_id_max();
DROP SEQUENCE queue_q_id_seq;
DROP TABLE queue;

-- create new tables
CREATE SEQUENCE queue_q_id_seq;
CREATE TABLE queue (
q_id INT4 DEFAULT nextval('queue_q_id_seq') PRIMARY KEY,
q_name TEXT NOT NULL UNIQUE CHECK (q_name <> ''),
q_group TEXT NOT NULL CHECK (q_group <> '')
);
CREATE FUNCTION queue_q_id_max() RETURNS INT4 AS 'SELECT max(q_id) FROM queue' LANGUAGE 'sql';
COPY queue FROM '/daten/source/shd-1.0.1/pgsql/queue.dat' USING DELIMITERS ';';
SELECT setval('queue_q_id_seq', queue_q_id_max());

--
-- category
--

-- drop old stuff
DROP FUNCTION category_c_id_max();
DROP SEQUENCE category_c_id_seq;
DROP TABLE category;

-- create new tables
CREATE SEQUENCE category_c_id_seq;
CREATE TABLE category (
c_id INT4 DEFAULT nextval('category_c_id_seq') PRIMARY KEY,
c_name TEXT NOT NULL UNIQUE CHECK (c_name <> ''),
c_group TEXT NOT NULL CHECK (c_group <> '')
);
CREATE FUNCTION category_c_id_max() RETURNS INT4 AS 'SELECT max(c_id) FROM category' LANGUAGE 'sql';
COPY category FROM '/daten/source/shd-1.0.1/pgsql/category.dat' USING DELIMITERS ';';
SELECT setval('category_c_id_seq', category_c_id_max());

--
-- queue to user allocation
--

-- drop old stuff
DROP FUNCTION aqu_aqu_id_max();
DROP SEQUENCE aqu_aqu_id_seq;
DROP TABLE aqu;

-- create new tables
CREATE SEQUENCE aqu_aqu_id_seq;
CREATE TABLE aqu (
aqu_id INT4 DEFAULT nextval('aqu_aqu_id_seq') PRIMARY KEY,
aqu_q_id INT4 NOT NULL CHECK (aqu_q_id <> ''),
aqu_u_id INT4 NOT NULL CHECK (aqu_u_id <> ''),
FOREIGN KEY (aqu_q_id) REFERENCES queue (q_id),
FOREIGN KEY (aqu_u_id) REFERENCES usr (u_id)
);
CREATE FUNCTION aqu_aqu_id_max() RETURNS INT4 AS 'SELECT max(aqu_id) FROM aqu' LANGUAGE 'sql';
COPY aqu FROM '/daten/source/shd-1.0.1/pgsql/aqu.dat' USING DELIMITERS ';';
SELECT setval('aqu_aqu_id_seq', aqu_aqu_id_max());

--
-- queue to calss allocation
--

-- drop old stuff
DROP FUNCTION aqc_aqc_id_max();
DROP SEQUENCE aqc_aqc_id_seq;
DROP TABLE aqc;

-- create new tables
CREATE SEQUENCE aqc_aqc_id_seq;
CREATE TABLE aqc (
aqc_id INT4 DEFAULT nextval('aqc_aqc_id_seq') PRIMARY KEY,
aqc_q_id INT4 NOT NULL CHECK (aqc_q_id <> ''),
aqc_c_id INT4 NOT NULL UNIQUE CHECK (aqc_c_id <> ''),
FOREIGN KEY (aqc_q_id) REFERENCES queue (q_id),
FOREIGN KEY (aqc_c_id) REFERENCES category (c_id)
);
CREATE FUNCTION aqc_aqc_id_max() RETURNS INT4 AS 'SELECT max(aqc_id) FROM aqc' LANGUAGE 'sql';
COPY aqc FROM '/daten/source/shd-1.0.1/pgsql/aqc.dat' USING DELIMITERS ';';
SELECT setval('aqc_aqc_id_seq', aqc_aqc_id_max());

--
-- tiket
--

-- drop old stuff
DROP FUNCTION tiket_t_id_max();
DROP SEQUENCE tiket_t_id_seq;
DROP TABLE tiket;

-- create new tables
CREATE SEQUENCE tiket_t_id_seq;
CREATE TABLE tiket (
t_id INT4 DEFAULT nextval('tiket_t_id_seq') PRIMARY KEY,
t_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
t_usr_id INT4 NOT NULL CHECK (t_usr_id <> ''),
t_abstract TEXT NOT NULL CHECK (t_abstract <> ''),
t_priority INT4 NOT NULL CHECK (t_priority <> ''),
t_queue_id_akt INT4,
t_category_id INT4 NOT NULL CHECK (t_category_id <> ''),
t_asset_no INT4,
t_change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
t_state TEXT NOT NULL CHECK (t_state <> ''),
t_email BOOL,
FOREIGN KEY (t_usr_id) REFERENCES usr (u_id),
FOREIGN KEY (t_queue_id_akt) REFERENCES queue (q_id),
FOREIGN KEY (t_category_id) REFERENCES category (c_id)
);
CREATE FUNCTION tiket_t_id_max() RETURNS INT4 AS 'SELECT max(t_id) FROM tiket' LANGUAGE 'sql';

--
-- tiket record
--

-- drop old stuff
DROP FUNCTION rec_r_id_max();
DROP SEQUENCE rec_r_id_seq;
DROP TABLE rec;

-- create new tables
CREATE SEQUENCE rec_r_id_seq;
CREATE TABLE rec (
r_id INT4 DEFAULT nextval('rec_r_id_seq') PRIMARY KEY,
r_t_id INT4 NOT NULL CHECK (r_t_id <> ''),
r_text TEXT,
r_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
r_duration INT4 DEFAULT 0,
r_bill BOOL DEFAULT 'f',
r_queue_id INT4 NOT NULL CHECK (r_queue_id <> ''),
r_usr_id INT4 NOT NULL CHECK (r_usr_id <> ''),
r_internal BOOL DEFAULT 'f',
r_file TEXT,
FOREIGN KEY (r_t_id) REFERENCES tiket (t_id),
FOREIGN KEY (r_queue_id) REFERENCES queue (q_id),
FOREIGN KEY (r_usr_id) REFERENCES usr (u_id)
);
CREATE FUNCTION rec_r_id_max() RETURNS INT4 AS 'SELECT max(r_id) FROM rec' LANGUAGE 'sql';

--
-- software
--

-- drop old stuff
DROP FUNCTION sw_s_id_max();
DROP SEQUENCE sw_s_id_seq;
DROP TABLE sw;

-- create new tables
CREATE SEQUENCE sw_s_id_seq;
CREATE TABLE sw (
s_id INT4 DEFAULT nextval('sw_s_id_seq') PRIMARY KEY,
s_manufacturer TEXT NOT NULL CHECK (s_manufacturer <> ''),
s_name TEXT NOT NULL CHECK (s_name <> ''),
s_version TEXT NOT NULL CHECK (s_version <> ''),
s_release BOOL DEFAULT 'f',
s_application TEXT,
s_os TEXT,
s_pay BOOL DEFAULT 'f',
s_maintainer TEXT,
s_change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE FUNCTION sw_s_id_max() RETURNS INT4 AS 'SELECT max(s_id) FROM sw' LANGUAGE 'sql';
CREATE UNIQUE INDEX sw_s_prog_uni ON sw (s_manufacturer, s_name, s_version);
COPY sw FROM '/daten/source/shd-1.0.1/pgsql/sw.dat' USING DELIMITERS ';';
SELECT setval('sw_s_id_seq', sw_s_id_max());

--
-- field
--

-- drop old stuff
DROP FUNCTION field_f_id_max();
DROP SEQUENCE field_f_id_seq;
DROP TABLE field;

-- create new tables
CREATE SEQUENCE field_f_id_seq;
CREATE TABLE field (
f_id INT4 DEFAULT nextval('field_f_id_seq') PRIMARY KEY,
f_name TEXT NOT NULL UNIQUE CHECK (f_name <> '')
);
CREATE FUNCTION field_f_id_max() RETURNS INT4 AS 'SELECT max(f_id) FROM field' LANGUAGE 'sql';

--
-- tiket activity record
--

-- drop old stuff
DROP FUNCTION act_a_id_max();
DROP SEQUENCE act_a_id_seq;
DROP TABLE act;

-- create new tables
CREATE SEQUENCE act_a_id_seq;
CREATE TABLE act (
a_id INT4 DEFAULT nextval('act_a_id_seq') PRIMARY KEY,
a_t_id INT4 NOT NULL CHECK (a_t_id <> ''),
a_field_id INT4 NOT NULL CHECK (a_field_id <> ''),
a_orderer TEXT,
a_due_date DATE,
FOREIGN KEY (a_t_id) REFERENCES tiket (t_id),
FOREIGN KEY (a_field_id) REFERENCES field (f_id)
);
CREATE FUNCTION act_a_id_max() RETURNS INT4 AS 'SELECT max(a_id) FROM act' LANGUAGE 'sql';

No file was uploaded with this report