Thread: transactions on Postgresql

Hi everybody,

my subject is about :   DBI / PostgreSql  &  Transactions  with Perl.
my code is :

$dbh->{AutoCommit} = 0;      #-------- begin of transaction
while (<FILE>) {
...
    $sql  =  "insert into $table ($col) values ($val)";
    $res  =  $dbh->do($sql);
}

$dbh->rollback;                    #--------- end of transaction

my problem : if an insert fail, all following insert are aborted :-(

NB :         it's not necessary to me that ALL  inserts , will be done
with succes.
my question  is :   if  a  BAD insert fail ,  how can i do  for  doing
other inserts who may be are GOOD ?

thanks a lot for help ;-)

On Monday, May, 2001-05-28 at 16:40:32, hicham bouzdad wrote:
> Hi everybody,
>
> my subject is about :   DBI / PostgreSql  &  Transactions  with Perl.
> my code is :
>
> $dbh->{AutoCommit} = 0;      #-------- begin of transaction
> while (<FILE>) {
> ...
>     $sql  =  "insert into $table ($col) values ($val)";
>     $res  =  $dbh->do($sql);
> }
>
> $dbh->rollback;                    #--------- end of transaction
>
> my problem : if an insert fail, all following insert are aborted :-(

the problem is: if an insert fails ALL following AND PRECEEDING inserts
are aborted!

> NB :         it's not necessary to me that ALL  inserts , will be done
> with succes.
> my question  is :   if  a  BAD insert fail ,  how can i do  for  doing
> other inserts who may be are GOOD ?

that's the scenario for _not using_ transactions at all! Transactions
are about _all or nothing_ scenario, when we want ALL of the changes to
be made into database or not a single one of them.

So my advice is: don't use transactions (set autocommit to 1 or do COMMIT
after every insert, I don't know Perl DBI).


regards

--
Marek Pętlicki <marpet@buy.pl>