Re: [HACKERS] TRANSACTION "WARNINGS"

Hi,

Its me again,

I'm trying to use transactions thru ODBC but it seems to be impossible.
I'm populating my tables using transactions thru ODBC and before to INSERT a row to a table
I check if such row already exist in that table.
if result is FALSE I insert the row into the table otherwise I skip the INSERT operation.
I have a log in which ODBC checks for an unexistent row but when I try to INSERT the row
I cannot insert it, there's a duplicate index error.
I have only two index in that table and only one of them is UNIQUE and I know there is no
other row with the same index in that table.
If I use the same program without transactions it works fine.

Any ideas?

here the log:

<DELETED>
conn=61438304, SQLDriverConnect(out)='DSN=PostgreSQL;DATABASE=hygea;SERVER=verde
conn=61438304, query='SELECT "utenti"."azienda","utenti"."inizio_attivita"
FROM "utenti" WHERE ("azienda" = '01879540308' ) '
[ fetched 0 rows ]
conn=61438304, SQLDisconnect
conn=61284284, query='INSERT INTO "utenti"
("azienda","ragione_sociale","istat","cap","indirizzo","partita_iva","istat_nascita","distretto","data_aggiornamento")

VALUES ('01879540308','FONZAR PAOLO-LUCA-LUCIANO E DANIELA','030120','33050','VIA PROVINCIALE
N.4','01879540308','000000','G10500','1999-11-17 00:00:00')'
ERROR from backend during send_query: 'ERROR: Cannot insert a duplicate key into a unique index'
conn=61284284, query='ABORT'
<DELETED>

and here the table structure:

Table = utenti
+----------------------------------+----------------------------------+-------+
|Field |Type | Length|
+----------------------------------+----------------------------------+-------+
| azienda | char() not null | 16 |
| ragione_sociale | varchar() not null | 45 |
| istat | char() not null | 6 |
| cap | char() | 5 |
| indirizzo | char() | 40 |
| civico | char() | 10 |
| distretto_interno | char() | 3 |
| frazione | char() | 25 |
| telefono | char() | 15 |
| fax | char() | 15 |
| email | char() | 15 |
| codice_fiscale | char() | 16 |
| partita_iva | char() | 11 |
| cciaa | char() | 8 |
| data_ccia | date | 4 |
| data_nascita | date | 4 |
| istat_nascita | char() | 6 |
| stato_attivita | char() | 2 |
| fuori_usl | char() default 'N' | 1 |
| assegnazione_codice | date | 4 |
| inizio_attivita | date not null default date( 'cur | 4 |
| fine_attivita | date | 4 |
| dpr317 | char() default 'N' | 1 |
| distretto | char() | 6 |
| data_aggiornamento | timestamp default now() | 4 |
| aggiornato_da | char() default CURRENT_USER | 10 |
| data_esportazione | date | 4 |
| data_precedente_esp | date | 4 |
+----------------------------------+----------------------------------+-------+
Indices: utenti_pkey
utenti_ragione_idx

Thanks for any help.
Jose'

jose soares ha scritto:

> Hi all,
>
> I have again a problem about TRANSACTIONS.
> I had some answers about this matter some time ago, but unfortunately the solution wasn't yet found.
> Transaction are essentials for a relational database but in the case of PostgreSQL some times it's
> impossible
> to use them. Right now I'm in a middle of a work and I need to use transactions but I can't go on because
> there are some "warnings" that I would like to avoid but I can't.
>
> Problem:
>
> PostgreSQL automatically ABORTS at every error, even a syntax error.
> I know that a transaction is a sequence of operations which either all succeed, or all fail, and
> this behavior is correct for batch mode operations, but it is not useful in interactive mode where
> the user
> could decide if the transaction should be COMMITed or ROLLBACKed even in presence of errors.
> Other databases have such behavior.
>
> What about to have a variable to set like:
>
> SET TRANSACTION MODE TO {BATCH | INTERACTIVE}
>
> where:
> BATCH: the transaction ROLLBACK at first error and COMMIT only if all operations
> succeed.
> INTERACTIVE: leaves the final decision to user to COMMIT or ROLLBACK even if some error occurred.
>
> Comments...
>
> Jose'
>
> ************