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

Rainer Tammer (tammer@tammer.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