Postgresql 7.3.2 Crash - Mailing list pgsql-bugs

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