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@gmail.com 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@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
