Re: commit transaction failed

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)