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: [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
