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...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 ]
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
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),Import Notes
Reply to msg id not found: 412F4F73.8030909@medium13.sk