Rainer Tammer ([EMAIL PROTECTED]) 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
