Thread: commit transaction failed
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;
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
CADOXSet rset(pCnn);
ostringstream ost;
ost<<"select * from
sp_getnewfiles('"<<GetServerID()<<"',"<<nMaxCount<<")";
rset.Open(ost.str());
ASSERT(rset.IsEOF()==false);
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 : commitTransaction failed
Thanks in advance:
Nasim
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)
Hi:
I am moving my data from windows-mssql to Linux-postgresql, but I'am
still accessing the data from windows using pgoledb - in order to have
compatibility with he old system. Everything is working ok, except when
transactions are involved.
Examining in this list I found the same kind of error I am having at
this moment:
"Error message: Unspecified error
Engine Used: PgOleDb
Error type : commit failed"
I did not find what solution was found.
Can anyone help me with, at least, some tips ?
Thanks in advance
Felipe
--
View this message in context: http://postgresql.1045698.n5.nabble.com/commit-transaction-failed-tp1883952p5110031.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of gato pardo
Sent: Friday, December 30, 2011 8:39 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] pgoledb transaction error
Hi:
I am moving my data from windows-mssql to Linux-postgresql, but I'am
still accessing the data from windows using pgoledb - in order to have
compatibility with he old system. Everything is working ok, except when
transactions are involved.
Examining in this list I found the same kind of error I am having at
this moment:
"Error message: Unspecified error
Engine Used: PgOleDb
Error type : commit failed"
I did not find what solution was found.
Can anyone help me with, at least, some tips ?
------------------------------------------------
Tip 1: Provide the SQL statements you are trying to execute when you get the
error; given that is it a "commit"-type error the entire transaction is
useful.
Tip 2: Make use of the PostgreSQL server logs to see exactly what the server
is (has been) attempting to execute instead of the single error message the
client is seeing.
David J.
Thanks David
Complying with your requirements:
"Tip 1: Provide the SQL statements you are trying to execute when you get
the
error; given that is it a "commit"-type error the entire transaction is
useful."
I copy some of the code used:
MyConnectionString = "Provider=PostgreSQL OLE DB Provider;Data
Source=172.21.0.85;user id=postgres;password=;location=db_13N_QJM;"
MyO_DbConnection = New System.Data.OleDb.OleDbConnection(MyConnectionString)
MyO_DbConnection.Open()
MyO_DbCommand = New System.Data.OleDb.OleDbCommand
MyO_DbCommand.Connection = MyO_DbConnection
MySQL = " UPDATE reg1001b set tipo_1001b = 'P', descr_1001b = 'PASIVO',
statu_1001b = 'A', modifi_por_1001b = 'ADMINISTRATO', fecha_mod_1001b =
'12/31/2011 09:16:20' WHERE compania_1001b = 'QJM' and tipo_1001b = 'P'"
String
Try
MyO_DbCommand.CommandText = MySQL
MyO_DbTrans = MyO_DbConnection.BeginTransaction
MyO_DbCommand.Transaction = MyO_DbTrans
MyRes_01 = MyO_DbCommand.ExecuteNonQuery()
MyO_DbTrans.Commit()
Catch e As Exception
MyO_DbTrans.Rollback()
MyMsg = "Error # " & Str(Err.Number) & " Esta Generado Por : " _
& Err.Source & ControlChars.CrLf & ControlChars.CrLf
& Err.Description
MsgBox(MyMsg, MsgBoxStyle.Information, "Error")
End Try
"Tip 2: Make use of the PostgreSQL server logs to see exactly what the
server
is (has been) attempting to execute instead of the single error message the
client is seeing."
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection
As the log shows we tried thrice to execute the above code.
I should add that the same code, and much more, is executed whout failure
when using odbc connection.
Any advice ?
Felipe
--
View this message in context: http://postgresql.1045698.n5.nabble.com/commit-transaction-failed-tp1883952p5112200.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.