Обсуждение: 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.