Re: [HACKERS] TRANSACTION "WARNINGS"

<tt>Hi,</tt><tt></tt><p><tt>Its me again,</tt><tt></tt><p><tt>I'm trying to use transactions thru ODBC but it seems to
beimpossible.</tt><br /><tt>I'm populating my tables using transactions thru ODBC and before to INSERT a row to a
table</tt><br/><tt>I check if such row already exist in that table.</tt><br /><tt>if result is FALSE I insert the row
intothe table otherwise I skip the INSERT operation.</tt><br /><tt>I have a log in which ODBC checks for an unexistent
rowbut when I try to INSERT the row</tt><br /><tt>I cannot insert it, there's a duplicate index error.</tt><br /><tt>I
haveonly two index in that table and only one of them is UNIQUE and I know there is no</tt><br /><tt>other row with the
sameindex in that table.</tt><br /><tt>If I use the same program without transactions it works
fine.</tt><tt></tt><p><tt>Anyideas?</tt><tt></tt><p><tt>here the log:</tt><tt></tt><p><tt><DELETED></tt><br
/><tt>conn=61438304,SQLDriverConnect(out)='DSN=PostgreSQL;DATABASE=hygea;SERVER=verde</tt><br /><tt>conn=61438304,
query='SELECT"utenti"."azienda","utenti"."inizio_attivita"</tt><br /><tt>FROM "utenti" WHERE ("azienda" = '01879540308'
)'</tt><br /><tt>    [ fetched 0 rows ]</tt><br /><tt>conn=61438304, SQLDisconnect</tt><br /><tt>conn=61284284,
query='INSERTINTO  "utenti"
("azienda","ragione_sociale","istat","cap","indirizzo","partita_iva","istat_nascita","distretto","data_aggiornamento")</tt><br
/><tt>VALUES('01879540308','FONZAR PAOLO-LUCA-LUCIANO E DANIELA','030120','33050','VIA PROVINCIALE
N.4','01879540308','000000','G10500','1999-11-1700:00:00')'</tt><br /><tt>ERROR from backend during send_query: 
'ERROR: Cannot insert a duplicate key into a unique index'</tt><br /><tt>conn=61284284, query='ABORT'</tt><br
/><tt><DELETED></tt><tt></tt><p><tt>andhere the table structure:</tt><tt></tt><p><tt>Table    = utenti</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br
/><tt>|Field                            |Type                              | Length|</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>|
azienda                         | char() not null                  |    16 |</tt><br /><tt>|
ragione_sociale                 | varchar() not null               |    45 |</tt><br /><tt>|
istat                           | char() not null                  |     6 |</tt><br /><tt>|
cap                             | char()                           |     5 |</tt><br /><tt>|
indirizzo                       | char()                           |    40 |</tt><br /><tt>|
civico                          | char()                           |    10 |</tt><br /><tt>|
distretto_interno               | char()                           |     3 |</tt><br /><tt>|
frazione                        | char()                           |    25 |</tt><br /><tt>|
telefono                        | char()                           |    15 |</tt><br /><tt>|
fax                             | char()                           |    15 |</tt><br /><tt>|
email                           | char()                           |    15 |</tt><br /><tt>|
codice_fiscale                  | char()                           |    16 |</tt><br /><tt>|
partita_iva                     | char()                           |    11 |</tt><br /><tt>|
cciaa                           | char()                           |     8 |</tt><br /><tt>|
data_ccia                       | date                             |     4 |</tt><br /><tt>|
data_nascita                    | date                             |     4 |</tt><br /><tt>|
istat_nascita                   | char()                           |     6 |</tt><br /><tt>|
stato_attivita                  | char()                           |     2 |</tt><br /><tt>|
fuori_usl                       | char() default 'N'               |     1 |</tt><br /><tt>|
assegnazione_codice             | date                             |     4 |</tt><br /><tt>|
inizio_attivita                 | date not null default date( 'cur |     4 |</tt><br /><tt>|
fine_attivita                   | date                             |     4 |</tt><br /><tt>|
dpr317                          | char() default 'N'               |     1 |</tt><br /><tt>|
distretto                       | char()                           |     6 |</tt><br /><tt>|
data_aggiornamento              | timestamp default now()          |     4 |</tt><br /><tt>|
aggiornato_da                   | char() default CURRENT_USER      |    10 |</tt><br /><tt>|
data_esportazione               | date                             |     4 |</tt><br /><tt>|
data_precedente_esp             | date                             |     4 |</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>Indices: 
utenti_pkey</tt><br/><tt>          utenti_ragione_idx</tt><br /><tt></tt>  <p>Thanks for any help. <br />Jose' <br /> 
<p>josesoares ha scritto: <blockquote type="CITE">Hi all, <p>I have again a problem about TRANSACTIONS. <br />I had
someanswers about this matter some time ago, but unfortunately the solution wasn't yet found. <br />Transaction are
essentialsfor a relational database but in the case of PostgreSQL some times it's <br />impossible <br />to use them.
Rightnow I'm in a middle of a work and I need to use transactions but I can't go on because <br />there are some
"warnings"that I would like to avoid but I can't. <p>Problem: <p>    PostgreSQL automatically ABORTS at every error,
evena syntax error. <br />    I know that a transaction is a sequence of operations which either all succeed, or all
fail,and <br />    this behavior is correct for batch mode operations,  but  it is not useful in interactive mode where
<br/>the user <br />    could decide if the transaction should be COMMITed or ROLLBACKed even in presence of  errors.
<br/>    Other databases have such behavior. <p>What about to have a variable to set like: <p>SET TRANSACTION MODE TO
{BATCH| INTERACTIVE} <p>where: <br />        BATCH:              the transaction ROLLBACK at first error and COMMIT
onlyif all operations <br />succeed. <br />        INTERACTIVE:  leaves the final decision to user to COMMIT or
ROLLBACKeven if some error occurred. <p>Comments... <p>Jose' <p>************</blockquote>