Thread

Re: [HACKERS] TRANSACTION "WARNINGS"

Jose Soares <jose@sferacarta.com> — 1999-12-07T09:47:01Z

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'
>
> ************