Postgresql 7.3.2 Crash

[Adrian Pop <adrpo(at)ida(dot)liu(dot)se>]

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