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