Skip site navigation (1) Skip section navigation (2)

savepoints

From: Tom(dot)Zschockelt(at)flender(dot)com
To: pgsql-odbc(at)postgresql(dot)org
Subject: savepoints
Date: 2005-02-15 06:48:53
Message-ID: OF8367FDE6.8AB212EB-ONC1256FA9.002373BD-C1256FA9.00256DBC@flender.com (view raw or flat)
Thread:
Lists: pgsql-odbc
hi,

i use pg 8.0 with odbc 8.0.0.4 for some test stuff.

My application tries to insert many records from a ascii-file  into a 
db-table.

I use a transaction to speed up this process.

The application logic is the following :

1. create savepoint
2. try to update an existing record with the same key 
3. if the update fails, do an rollback to savepoint and go to the next 
record in file
4. if SQLNumResultCols returns 0 try an insert
5. if the insert fails, do an rollback to savepoint and go to the next 
record in file

in any case do an release savepoint before creating a new savepoint.

the problem :

if an error occur while doing an update or insert the transaction will be 
abortet 

look at the following :

[744]in QR_Constructor
[744]exit QR_Constructor
[744]read 122, global_socket_buffersize=4096
[744]send_query: got id = 'P'
[744]send_query: got id = 'E'
[744]send_query: 'E' - ERROR:  insert or update on table "rueckmeldungen" 
violates foreign key constraint "rueckmeldungen_ursache_fkey"
[744]read 1, global_socket_buffersize=4096
[744]send_query: got id = 'Z'
[744]send_query(): conn=18824880, query='ROLLBACK'
[744]send_query: done sending query
[744]in QR_Constructor
[744]exit QR_Constructor
[744]read 11, global_socket_buffersize=4096
[744]send_query: got id = 'C'
[744]send_query: ok - 'C' - ROLLBACK
[744]send_query: setting cmdbuffer = 'ROLLBACK'
[744]send_query: returning res = 18852384
[744]send_query: got id = 'Z'
[744]CC_abort:  sending ABORT!
[744]QResult: in DESTRUCTOR
[744]QResult: free memory in, fcount=0
[744]QResult: free memory out
[744]QResult: exit DESTRUCTOR
[744]STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error 
while executing the query'
[744]CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: 
insert or update on table "rueckmeldungen" violates foreign key constraint 
"rueckmeldungen_ursache_fkey"'
[744]PGAPI_ExecDirect: returned -1 from PGAPI_Execute
[744][[SQLGetDiagFieldW]] Handle=(3,11f89f0) Rec=1 Id=4


The rollback is being executed (automatically) before I can do any own 
error recovery !!!

How can I solve this problem.

greetings

tom

pgsql-odbc by date

Next:From: Steven BackmanDate: 2005-02-15 14:28:32
Subject: connection woes
Previous:From: Dave PageDate: 2005-02-14 13:08:26
Subject: Re: 500 times slower

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group