Postgresql 7.3.2 Crash

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 i686 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 translation available', 'No translation available', 'No translation
available');

-- usage id=g_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 = 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 = ''en'') into b_en;
select ($1 = ''sv'') into b_sv;
select ($1 = ''ro'') into b_ro;
if (b_en) then
SELECT name_en from g_translate where id=$2 INTO t;
end if;
if (b_sv) then
SELECT name_sv from g_translate where id=$2 INTO t;
end if;
if (b_ro) then
SELECT name_ro from g_translate where id=$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 does 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_translate(id)
match full
on update cascade
on delete set default
);

-- init with default values
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_translate(id)
match full
on update cascade
on delete set default,
constraint p_c_title_fk_type_code foreign key(type_code) references p_titletype(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_title(id)
match full
on update cascade
on delete set default
);

------------------------------------------------------------------------------------------------------
-- now the crash stuff:
------------------------------------------------------------------------------------------------------
-- put a person in
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 it 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='99999999-9999' and title_id=1;
delete from g_translate where id=5;
commit transaction;

--****************************************************
--*************** end minimal.sql
--****************************************************

The output i get is the following:
[adrpo(at)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_name_id on table p_titletype
DROP TABLE
psql:minimal.sql:18: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit 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_person_code on table p_p2title
DROP TABLE
psql:minimal.sql:95: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit 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_type_code on table p_title
DROP TABLE
psql:minimal.sql:131: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit 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_title_id on table p_p2title
DROP TABLE
psql:minimal.sql:162: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit 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 implicit 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('99999999-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 ('99999999-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
2003-03-25 18:42:27 [3531] LOG: query: SELECT 1 FROM ONLY "public"."p_person" x WHERE "code" = $1 FOR UPDATE OF x
2003-03-25 18:42:27 [3531] LOG: query: SELECT 1 FROM ONLY "public"."p_title" x WHERE "id" = $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 person_code='99999999-9999' and title_id=1;
2003-03-25 18:42:27 [3531] LOG: query: delete from p_p2title where person_code='99999999-9999' and title_id=1;
2003-03-25 18:42:27 [3531] DEBUG: ProcessQuery
2003-03-25 18:42:27 [3531] LOG: statement: delete from p_p2title where person_code='99999999-9999' and title_id=1;
2003-03-25 18:42:27 [3531] DEBUG: CommitTransactionCommand
2003-03-25 18:42:27 [3531] LOG: statement: delete from p_p2title where person_code='99999999-9999' and title_id=1;
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=5;
2003-03-25 18:42:27 [3531] LOG: query: delete from g_translate where id=5;
2003-03-25 18:42:27 [3531] DEBUG: ProcessQuery
2003-03-25 18:42:27 [3531] LOG: statement: delete from g_translate where id=5;
2003-03-25 18:42:27 [3531] LOG: query: UPDATE ONLY "public"."p_titletype" SET "name_id" = NULL WHERE "name_id" = $1
2003-03-25 18:42:27 [3531] LOG: query: UPDATE ONLY "public"."p_title" SET "name_id" = NULL WHERE "name_id" = $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 terminated 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 processes
2003-03-25 18:42:27 [3525] LOG: all server processes terminated; reinitializing 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=1466368)
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 down; 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" SET "name_id" = NULL WHERE "name_id" = $1
Why is set to NULL when it fact it should be set to default (in this case 0) according to
the "on delete set default" from p_title definition?

Best regards,
Adrian Pop
__________________________________________________________________________
Adrian Pop http://www.ida.liu.se/~adrpo
Linköping University IDA/PELAB/DIG, bld. B, room 3B:478

ng University IDA/PELAB/DIG, bld. B, room 3B:478