Обсуждение: Stored procedure failure
hello, I have interesting problem... I have stored procedure, which works good, but only if input is "correct". Correct input is, when $1 is id, which exists in table... If I put non exists id, database fall down and restart with this error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. I think, that problem is in line: FOR tmp_row IN SELECT v_name FROM c_part_cat WHERE v_path @> my_path ORDER BY v_path LOOP because without them it works well... Version: 7.4.5 and 7.4.3 (both versions have this problem) thanx, hlavki source: /*==============================================================*/ /* Table: c_part_cat */ /*==============================================================*/ create table c_part_cat ( i_part_cat_id integer default nextval('c_part_cat_seq') not null, c_code varchar(32) not null, v_name varchar(128) null, v_path ltree not null, i_parent_id int4 null, constraint pk_c_part_cat primary key (i_part_cat_id) ); /*==============================================================*/ /* Index: index_13 */ /*==============================================================*/ create index index_13 on c_part_cat ( i_parent_id ); /*==============================================================*/ /* Index: index_22 */ /*==============================================================*/ create unique index index_22 on c_part_cat ( c_code ); /*==============================================================*/ /* Index: index_4 */ /*==============================================================*/ create index index_4 on c_part_cat using gist ( v_path ); alter table c_part_cat add constraint fk_c_part_cat_ref_c_part_cat foreign key (i_parent_id) references c_part_cat (i_part_cat_id) on delete restrict on update restrict; CREATE OR REPLACE FUNCTION "public"."get_part_cat_path" (integer) RETURNS text AS' DECLARE my_path ltree; result text; tmp_row RECORD; first bool; BEGIN SELECT v_path INTO my_path FROM c_part_cat WHERE i_part_cat_id = $1; result := ''''; first := true; FOR tmp_row IN SELECT v_name FROM c_part_cat WHERE v_path @> my_path ORDER BY v_path LOOP IF first THEN result := tmp_row.v_name; first := false; ELSE result := tmp_row.v_name || ''->'' || result; END IF; END LOOP; RETURN result; END; 'LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; -- [ miso hlavac ][ hlavki@medium13.sk ][ http://www.medium13.sk ] [ icq:94900232 ][ callto://hlavki ]
Michal Hlavac wrote: > hello, I have interesting problem... > > I have stored procedure, which works good, but only if input is "correct". > Correct input is, when $1 is id, which exists in table... > If I put non exists id, database fall down and restart with this error: > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > > I think, that problem is in line: > FOR tmp_row IN SELECT v_name FROM c_part_cat WHERE v_path @> my_path > ORDER BY v_path LOOP > because without them it works well... > CREATE OR REPLACE FUNCTION "public"."get_part_cat_path" (integer) > RETURNS text AS' > DECLARE > my_path ltree; ... > BEGIN > SELECT v_path INTO my_path FROM c_part_cat WHERE i_part_cat_id = $1; > result := ''''; first := true; > FOR tmp_row IN SELECT v_name FROM c_part_cat WHERE v_path @> my_path I'm guessing a problem with the ltree code. Presumably in handling the null my_path. > Version: 7.4.5 and 7.4.3 (both versions have this problem) Looking at the 8.0beta source, the functions (...ltree_isparent) are all marked strict so they should just return null on a null parameter. What happens if you set my_path to some non-null but un-matched value before the problem line? -- Richard Huxton Archonet Ltd
Michal Hlavac wrote: > hello, I have interesting problem... > > I have stored procedure, which works good, but only if input is "correct". > Correct input is, when $1 is id, which exists in table... > If I put non exists id, database fall down and restart with this error: > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > > I think, that problem is in line: > FOR tmp_row IN SELECT v_name FROM c_part_cat WHERE v_path @> my_path > ORDER BY v_path LOOP > because without them it works well... > > Version: 7.4.5 and 7.4.3 (both versions have this problem) I am sorry... I've got it... my_path cannot be null... ;) thanx, hlavki -- [ miso hlavac ][ hlavki@medium13.sk ][ http://www.medium13.sk ] [ icq:94900232 ][ callto://hlavki ]
Richard Huxton wrote: > > Looking at the 8.0beta source, the functions (...ltree_isparent) are all > marked strict so they should just return null on a null parameter. > > What happens if you set my_path to some non-null but un-matched value > before the problem line? > When my_path have non-null value, everything is OK... I don't understand why one null value would crash the server??? -- [ miso hlavac ][ hlavki@medium13.sk ][ http://www.medium13.sk ] [ icq:94900232 ][ callto://hlavki ]
Michal Hlavac wrote: > Richard Huxton wrote: > >> >> Looking at the 8.0beta source, the functions (...ltree_isparent) are >> all marked strict so they should just return null on a null parameter. >> >> What happens if you set my_path to some non-null but un-matched value >> before the problem line? >> > > When my_path have non-null value, everything is OK... > > I don't understand why one null value would crash the server??? Because it attempts to follow a pointer that isn't there. If you installed from source, can you check contrib/ltree/ltree.sql.in and check the "isstrict" attribute is set: CREATE FUNCTION ltree_isparent(ltree,ltree) RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE 'C' WITH (isstrict,iscachable); If it is, then you'll have to wait for a developer to take an interest, I'm stumped. Shouldn't take long, but if you wanted to be thorough you could report a bug via the bugs mailing list or the website. -- Richard Huxton Archonet Ltd
Michal Hlavac <hlavki@medium13.sk> writes: > I have stored procedure, which works good, but only if input is "correct". > Correct input is, when $1 is id, which exists in table... > If I put non exists id, database fall down and restart with this error: > server closed the connection unexpectedly I can't reproduce a crash here, but perhaps that's because you have not supplied any sample data... regards, tom lane
Michal Hlavac <hlavki@medium13.sk> writes: > Tom Lane wrote: >> I can't reproduce a crash here, but perhaps that's because you have not >> supplied any sample data... > I attached file with data, where it fails... Thanks. It turns out the main reason I couldn't reproduce it was I was testing in CVS tip, where the bug had already been repaired. I've applied the attached patch to the 7.4 branch to fix it there. regards, tom lane Index: gistget.c =================================================================== RCS file: /cvsroot/pgsql-server/src/backend/access/gist/gistget.c,v retrieving revision 1.36 diff -c -r1.36 gistget.c *** gistget.c 4 Aug 2003 02:39:57 -0000 1.36 --- gistget.c 27 Aug 2004 17:44:04 -0000 *************** *** 234,249 **** key[0].sk_attno, giststate->tupdesc, &isNull); if (isNull) { /* XXX eventually should check if SK_ISNULL */ return false; } ! ! /* this code from backend/access/common/indexvalid.c. But why and what??? if (key[0].sk_flags & SK_ISNULL) return false; ! */ gistdentryinit(giststate, key[0].sk_attno - 1, &de, datum, r, p, offset, IndexTupleSize(tuple) - sizeof(IndexTupleData), --- 234,249 ---- key[0].sk_attno, giststate->tupdesc, &isNull); + /* is the index entry NULL? */ if (isNull) { /* XXX eventually should check if SK_ISNULL */ return false; } ! /* is the compared-to datum NULL? */ if (key[0].sk_flags & SK_ISNULL) return false; ! gistdentryinit(giststate, key[0].sk_attno - 1, &de, datum, r, p, offset, IndexTupleSize(tuple) - sizeof(IndexTupleData),