I don't know about the error, but I think there's far more efficient
ways to do what you're doing.... see below:
On Jun 20, 2007, at 1:25 AM, nasim.sindri@xxxxxxxxx wrote:
I m having a problem while calling the procedure in prostgresql 8.2
from adoconnection, It gets executed for some time and after 5-10 call
it gives error startTransaction failed or CommitTransaction Failed.
CREATE OR REPLACE FUNCTION sp_getnewfiles(IN strserverid character
varying, IN nmaxcount integer, OUT stroutrecno character varying) AS
$BODY$
DECLARE
cur RECORD;
i integer;
BEGIN
i:=0;
LOCK TABLE inputtable IN ROW EXCLUSIVE MODE NOWAIT;
Why are you locking the table? You likely don't need to. I suspect
that at most you just need a serialized transaction.
FOR cur IN select recno from InputTable where FileState=0 order by
recno limit nMaxCount for update
LOOP
if i=0 then
strOutRecNo:='recno=';
else
strOutRecNo:=strOutRecNo || ' or recno=';
end if;
strOutRecNo:=strOutRecNo||cur.recno;
Rather than a giant OR clause, have you considered an IN list? I'd
look at populating an array of values, and then using array_to_string
to turn that into a list of numbers.
update inputtable set filestate=1,serverid=strServerID where
recno=cur.recno;
i:=i+1;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
--DO NOTHING
WHEN OTHERS THEN
--rollback;
RAISE EXCEPTION 'some error';
Why test for other exceptions if you're just going to re-raise them?
Having said all that, I think a function is simply the wrong way to
go about this. Instead I think you want is:
UPDATE input_table
SET file_state = 1, server_id = ...
WHERE file_state = 0
RETURNING *
;
(Sorry, my brain/fingers don't do camel case. :P)
--
Jim Nasby jim@xxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
