commit failed when calling postgresql procedure

From: nasim(dot)sindri(at)gmail(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: commit failed when calling postgresql procedure
Date: 2007-06-20 09:54:56
Message-ID: 1182333296.820506.229310@a26g2000pre.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

PLATEFORM: VC++ 7 , ADO, WINDOWS 2003, POSTGRE 8.2

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 either startTransaction failed or commit 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;
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;
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';

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;

the calling code is

CADORecordset rset(pCnn);
rset.Open("select * from sp_getnewfiles('server',10)");
strRecNo=rset.GetFieldValue(0);

the error I encountered after some number of calls is either -

Error message: Unspecified error
Engine Used: PgOleDb
Error type : StartTransaction failed

or

Error message: Unspecified error
Engine Used: PgOleDb
Error type : commit failed

Thanks in advance:
Nasim

Browse pgsql-general by date

  From Date Subject
Next Message Marco Colombo 2007-06-20 10:21:45 Re: unexpected shutdown
Previous Message scuffio 2007-06-20 09:44:20 CHECK