Hello,
I have a postgresql 7.3.2 crash.
Below you have the details.
All the files included here can be found at:
http://www.ida.liu.se/~adrpo/postgresqlbug/
Operating systems (uname -a)
1: SunOS xxx.xxx.liu.se 5.8 Generic_108528-13 sun4u sparc SUNW,Ultra-5_10
2: Linux dostoievsky 2.4.18-27.8.0 #1 Fri Mar 14 06:45:49 EST 2003 i686 i68=
6 i386 GNU/Linux
For linux i used the 7.3.2 rpms
For solaris it was compiled.
Before the script, these were the commands used to create the users/database
initdb -D /home/adrpo/postgresql/data
createuser -P -h localhost pgadmin
createdb -h localhost ida
createlang -h localhost -d ida -pglib /usr/lib/pgsql/ plpgsql
Here is the script that crashes the postgresql, name:minimal.sql
--****************************************************
--*************** start minimal.sql
--****************************************************
-- sequence for translation id
drop sequence g_sqtranslate;
create sequence g_sqtranslate;
-- translate table (table with identifiers in different languages)
drop table g_translate cascade;
create table g_translate
(
-- unique generated id
id bigint not null primary key,
-- english translation for this symbol
name_en text null,
-- swedish translation for this symbol
name_sv text null,
-- romanian translation for this symbol
name_ro text null
-- here more translation can be added needed.
);
-- init with default values
insert into g_translate(id, name_en, name_sv, name_ro) values(0, 'No transl=
ation available', 'No translation available', 'No translation=20
available');
-- usage id=3Dg_in_t('English','Swedish');
drop FUNCTION g_in_t(text,text);
CREATE FUNCTION g_in_t(text,text) RETURNS bigint AS
'DECLARE
id bigint;
BEGIN
select nextval(''g_sqtranslate'') into id;
insert into g_translate values(id, $1, $2, $2);
RETURN id;
END;'
language 'plpgsql';
-- usage: translatedstring =3D g_out_t('en',id);
drop FUNCTION g_out_t(text, bigint);
CREATE FUNCTION g_out_t(text, bigint) RETURNS text AS
'DECLARE
t text;
key text;
b_en boolean;
b_sv boolean;
b_ro boolean;
BEGIN
select ($1 =3D ''en'') into b_en;
select ($1 =3D ''sv'') into b_sv;
select ($1 =3D ''ro'') into b_ro;
if (b_en) then
SELECT name_en from g_translate where id=3D$2 INTO t;
end if;
if (b_sv) then
SELECT name_sv from g_translate where id=3D$2 INTO t;
end if;
if (b_ro) then
SELECT name_ro from g_translate where id=3D$2 INTO t;
end if;
RETURN t;
END;'
language 'plpgsql';
-- type_code, en, sv
drop FUNCTION p_in_title(text,text,text);
CREATE FUNCTION p_in_title(text,text,text) RETURNS bigint AS
'DECLARE
zid bigint;
BEGIN
select nextval(''p_sqtitle'') into zid;
insert into p_title values(zid, $1, g_in_t($2, $3));
RETURN zid;
END;'
language 'plpgsql';
-- person table is the root table for person database
drop table p_person cascade;
create table p_person
(
-- unique code for a person (personalno, or some other code if it d=
oes not have any login)
code varchar(200) not null primary key,
-- name
firstname varchar(80) not null,
lastname varchar(80) not null,
-- personal no
personalno varchar(15) null,
-- the code a person has in the schedule (schema)
schedule_code varchar(200) null,
-- the key for the doors
doorkey varchar(500) null
);
-- indexes defined on person table
create index p_ndxperson0 on p_person
(
firstname
);
create index p_ndxperson1 on p_person
(
lastname
);
create index p_ndxperson2 on p_person
(
personalno
);
-- titletype (table for types of titles)
-- looks like this
-- code | name_id | g_out_t
-------------+---------+-----------------
-- AT | 177 | Academic title
-- ET | 178 | Education title
drop table p_titletype cascade;
create table p_titletype
(
-- code for this type of title
code varchar(500) not null primary key,
-- translation for that
name_id bigint not null default 0,
constraint p_c_titletype_fk_name_id foreign key(name_id) references g_tran=
slate(id)
match full
on update cascade
on delete set default
);
-- init with default values=20
insert into p_titletype(code, name_id) values('--', g_in_t('None', 'sv None=
'));
insert into p_titletype(code, name_id) values('AT', g_in_t('Academic title'=
, 'sv AT'));
insert into p_titletype(code, name_id) values('ET', g_in_t('Education title=
', 'sv AT'));
-- after this insert the g_sqtranslate will go up to 3
-- sequence for table title
drop sequence p_sqtitle;
create sequence p_sqtitle;
-- title table (table that tell us the titles available for a person)
drop table p_title cascade;
create table p_title
(
-- unique id generated from sequence
id bigint not null primary key default nextval('p_sqtitle'),
-- what kind of title it is (type)
type_code varchar(200) not null default '--',
-- translation for this title.
name_id bigint not null default 0,
constraint p_c_title_fk_name_id foreign key(name_id) references g_translat=
e(id)
match full
on update cascade
on delete set default,
constraint p_c_title_fk_type_code foreign key(type_code) references p_titl=
etype(code)
match full
on update cascade
on delete set default
);
-- init with default values
insert into p_title(id, type_code, name_id) values(0, '--', g_in_t('None', =
'sv None'));
-- after this insert the g_sqtranslate will go up to 4
-- person to title (defines relation between a person and several titles)
drop table p_p2title cascade;
create table p_p2title
(
person_code varchar(200) not null,
title_id bigint not null default 0,
constraint p_c_p2title_pk primary key(person_code, title_id),
constraint p_c_p2title_fk_person_code foreign key(person_code) references =
p_person(code)
match full
on update cascade
on delete cascade,
constraint p_c_p2title_fk_title_id foreign key(title_id) references p_titl=
e(id)
match full
on update cascade
on delete set default
);
---------------------------------------------------------------------------=
---------------------------
-- now the crash stuff:
---------------------------------------------------------------------------=
---------------------------
-- put a person in=20
insert into p_person values('99999999-9999', 'Adrian', 'Pop', '999999-9999'=
, 'ADPOP', 'XXX');
-- now add a title in the p_title and relate the newly inserted person to i=
t in the p_p2title table
insert into p_p2title values ('99999999-9999', p_in_title('AT','PhD Student=
','Doktorand'));
-- after this insert the g_sqtranslate will go up to 5 and p_sqtitle up to 1
-- now let's delete what we put in
start transaction;
delete from p_p2title where person_code=3D'99999999-9999' and title_id=3D1;
delete from g_translate where id=3D5;
commit transaction;
--****************************************************
--*************** end minimal.sql
--****************************************************
The output i get is the following:
[adrpo@dostoievsky init]$ psql -h localhost -U pgadmin -d ida -f minimal.sql
DROP SEQUENCE
CREATE SEQUENCE
psql:minimal.sql:6: NOTICE: Drop cascades to constraint p_c_title_fk_name_=
id on table p_title
psql:minimal.sql:6: NOTICE: Drop cascades to constraint p_c_titletype_fk_n=
ame_id on table p_titletype
DROP TABLE
psql:minimal.sql:18: NOTICE: CREATE TABLE / PRIMARY KEY will create implic=
it index 'g_translate_pkey' for table 'g_translate'
CREATE TABLE
INSERT 25180 1
DROP FUNCTION
CREATE FUNCTION
psql:minimal.sql:39: ERROR: RemoveFunction: function g_out_t(text, bigint)=
does not exist
psql:minimal.sql:80: NOTICE: Drop cascades to constraint p_c_p2title_fk_pe=
rson_code on table p_p2title
DROP TABLE
psql:minimal.sql:95: NOTICE: CREATE TABLE / PRIMARY KEY will create implic=
it index 'p_person_pkey' for table 'p_person'
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
psql:minimal.sql:120: NOTICE: Drop cascades to constraint p_c_title_fk_typ=
e_code on table p_title
DROP TABLE
psql:minimal.sql:131: NOTICE: CREATE TABLE / PRIMARY KEY will create impli=
cit index 'p_titletype_pkey' for table 'p_titletype'
psql:minimal.sql:131: NOTICE: CREATE TABLE will create implicit trigger(s)=
for FOREIGN KEY check(s)
CREATE TABLE
INSERT 25199 1
INSERT 25201 1
INSERT 25203 1
DROP SEQUENCE
CREATE SEQUENCE
psql:minimal.sql:145: NOTICE: Drop cascades to constraint p_c_p2title_fk_t=
itle_id on table p_p2title
DROP TABLE
psql:minimal.sql:162: NOTICE: CREATE TABLE / PRIMARY KEY will create impli=
cit index 'p_title_pkey' for table 'p_title'
psql:minimal.sql:162: NOTICE: CREATE TABLE will create implicit trigger(s)=
for FOREIGN KEY check(s)
CREATE TABLE
INSERT 25222 1
DROP TABLE
psql:minimal.sql:189: NOTICE: CREATE TABLE / PRIMARY KEY will create impli=
cit index 'p_c_p2title_pk' for table 'p_p2title'
psql:minimal.sql:189: NOTICE: CREATE TABLE will create implicit trigger(s)=
for FOREIGN KEY check(s)
CREATE TABLE
INSERT 25236 1
INSERT 25239 1
START TRANSACTION
DELETE 1
psql:minimal.sql:206: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:minimal.sql:206: connection to server was lost
The database log tells the folowing:
2003-03-25 18:42:27 [3531] DEBUG: StartTransactionCommand
2003-03-25 18:42:27 [3531] LOG: statement: insert into p_person values('=
99999999-9999', 'Adrian', 'Pop', '999999-9999', 'ADPOP', 'XXX');
2003-03-25 18:42:27 [3531] LOG: query: insert into p_person values('9999=
9999-9999', 'Adrian', 'Pop', '999999-9999', 'ADPOP', 'XXX');
2003-03-25 18:42:27 [3531] DEBUG: ProcessQuery
2003-03-25 18:42:27 [3531] LOG: statement: insert into p_person values('=
99999999-9999', 'Adrian', 'Pop', '999999-9999', 'ADPOP', 'XXX');
2003-03-25 18:42:27 [3531] DEBUG: CommitTransactionCommand
2003-03-25 18:42:27 [3531] LOG: statement: insert into p_person values('=
99999999-9999', 'Adrian', 'Pop', '999999-9999', 'ADPOP', 'XXX');
2003-03-25 18:42:27 [3531] LOG: duration: 0.014517 sec
2003-03-25 18:42:27 [3531] DEBUG: StartTransactionCommand
2003-03-25 18:42:27 [3531] LOG: statement: insert into p_p2title values =
('99999999-9999', p_in_title('AT','PhD Student','Doktorand'));
2003-03-25 18:42:27 [3531] LOG: query: insert into p_p2title values ('99=
999999-9999', p_in_title('AT','PhD Student','Doktorand'));
2003-03-25 18:42:27 [3531] DEBUG: ProcessQuery
2003-03-25 18:42:27 [3531] LOG: statement: insert into p_p2title values =
('99999999-9999', p_in_title('AT','PhD Student','Doktorand'));
2003-03-25 18:42:27 [3531] LOG: query: SELECT nextval('p_sqtitle')
2003-03-25 18:42:27 [3531] LOG: query: insert into p_title values( $1 , =
$2 , g_in_t( $3 , $4 ))
2003-03-25 18:42:27 [3531] LOG: query: SELECT $1=20
2003-03-25 18:42:27 [3531] LOG: query: SELECT 1 FROM ONLY "public"."p_pe=
rson" x WHERE "code" =3D $1 FOR UPDATE OF x
2003-03-25 18:42:27 [3531] LOG: query: SELECT 1 FROM ONLY "public"."p_ti=
tle" x WHERE "id" =3D $1 FOR UPDATE OF x
2003-03-25 18:42:27 [3531] DEBUG: CommitTransactionCommand
2003-03-25 18:42:27 [3531] LOG: statement: insert into p_p2title values =
('99999999-9999', p_in_title('AT','PhD Student','Doktorand'));
2003-03-25 18:42:27 [3531] LOG: duration: 0.025779 sec
2003-03-25 18:42:27 [3531] DEBUG: StartTransactionCommand
2003-03-25 18:42:27 [3531] LOG: statement: start transaction;
2003-03-25 18:42:27 [3531] LOG: query: start transaction;
2003-03-25 18:42:27 [3531] DEBUG: ProcessUtility
2003-03-25 18:42:27 [3531] LOG: statement: start transaction;
2003-03-25 18:42:27 [3531] DEBUG: CommitTransactionCommand
2003-03-25 18:42:27 [3531] LOG: statement: start transaction;
2003-03-25 18:42:27 [3531] LOG: duration: 0.000268 sec
2003-03-25 18:42:27 [3531] DEBUG: StartTransactionCommand
2003-03-25 18:42:27 [3531] LOG: statement: delete from p_p2title where p=
erson_code=3D'99999999-9999' and title_id=3D1;
2003-03-25 18:42:27 [3531] LOG: query: delete from p_p2title where perso=
n_code=3D'99999999-9999' and title_id=3D1;
2003-03-25 18:42:27 [3531] DEBUG: ProcessQuery
2003-03-25 18:42:27 [3531] LOG: statement: delete from p_p2title where p=
erson_code=3D'99999999-9999' and title_id=3D1;
2003-03-25 18:42:27 [3531] DEBUG: CommitTransactionCommand
2003-03-25 18:42:27 [3531] LOG: statement: delete from p_p2title where p=
erson_code=3D'99999999-9999' and title_id=3D1;
2003-03-25 18:42:27 [3531] LOG: duration: 0.002086 sec
2003-03-25 18:42:27 [3531] DEBUG: StartTransactionCommand
2003-03-25 18:42:27 [3531] LOG: statement: delete from g_translate where=
id=3D5;
2003-03-25 18:42:27 [3531] LOG: query: delete from g_translate where id=
=3D5;
2003-03-25 18:42:27 [3531] DEBUG: ProcessQuery
2003-03-25 18:42:27 [3531] LOG: statement: delete from g_translate where=
id=3D5;
2003-03-25 18:42:27 [3531] LOG: query: UPDATE ONLY "public"."p_titletype=
" SET "name_id" =3D NULL WHERE "name_id" =3D $1
2003-03-25 18:42:27 [3531] LOG: query: UPDATE ONLY "public"."p_title" SE=
T "name_id" =3D NULL WHERE "name_id" =3D $1
2003-03-25 18:42:27 [3525] DEBUG: reaping dead processes
2003-03-25 18:42:27 [3525] DEBUG: child process (pid 3531) was terminate=
d by signal 11
2003-03-25 18:42:27 [3525] LOG: server process (pid 3531) was terminated=
by signal 11
2003-03-25 18:42:27 [3525] LOG: terminating any other active server proc=
esses
2003-03-25 18:42:27 [3525] LOG: all server processes terminated; reiniti=
alizing shared memory and semaphores
2003-03-25 18:42:27 [3525] DEBUG: shmem_exit(0)
2003-03-25 18:42:27 [3525] DEBUG: invoking IpcMemoryCreate(size=3D146636=
8)
2003-03-25 18:42:28 [3532] LOG: database system was interrupted at 2003-=
03-25 18:42:01 CET
2003-03-25 18:42:28 [3532] LOG: checkpoint record is at 0/904D30
2003-03-25 18:42:28 [3532] LOG: redo record is at 0/904D30; undo record =
is at 0/0; shutdown TRUE
2003-03-25 18:42:28 [3532] LOG: next transaction id: 568; next oid: 25171
2003-03-25 18:42:28 [3532] LOG: database system was not properly shut do=
wn; automatic recovery in progress
2003-03-25 18:42:28 [3532] LOG: redo starts at 0/904D70
2003-03-25 18:42:28 [3532] LOG: ReadRecord: record with zero length at 0=
/97CEC4
2003-03-25 18:42:28 [3532] LOG: redo done at 0/97CEA0
2003-03-25 18:42:30 [3532] LOG: database system is ready
2003-03-25 18:42:30 [3532] DEBUG: proc_exit(0)
2003-03-25 18:42:30 [3532] DEBUG: shmem_exit(0)
2003-03-25 18:42:30 [3532] DEBUG: exit(0)
2003-03-25 18:42:30 [3525] DEBUG: reaping dead processes
2003-03-25 18:42:48 [3525] DEBUG: pmdie 15
2003-03-25 18:42:48 [3525] LOG: smart shutdown request
2003-03-25 18:42:48 [3544] LOG: shutting down
2003-03-25 18:42:50 [3544] LOG: database system is shut down
2003-03-25 18:42:50 [3544] DEBUG: proc_exit(0)
2003-03-25 18:42:50 [3544] DEBUG: shmem_exit(0)
2003-03-25 18:42:50 [3544] DEBUG: exit(0)
2003-03-25 18:42:50 [3525] DEBUG: reaping dead processes
2003-03-25 18:42:50 [3525] DEBUG: proc_exit(0)
2003-03-25 18:42:50 [3525] DEBUG: shmem_exit(0)
2003-03-25 18:42:50 [3525] DEBUG: exit(0)
Now a question: What is with the folowing statement in the log?
2003-03-25 18:42:27 [3531] LOG: query: UPDATE ONLY "public"."p_title" SE=
T "name_id" =3D NULL WHERE "name_id" =3D $1
Why is set to NULL when it fact it should be set to default (in this case 0=
) according to=20
the "on delete set default" from p_title definition?
Best regards,
Adrian Pop
__________________________________________________________________________
Adrian Pop http://www.ida.liu.se/~adrpo
Link=F6ping University IDA/PELAB/DIG, bld. B, room 3B:478
ng University IDA/PELAB/DIG, bld. B, room 3B:478
=09=09