transactions on Postgresql

hicham bouzdad <h.bouzdad@inovaction.com> wrote in message news:<3B0B8A0C.B46AC073@inovaction.com>...

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 ;-)

You want to look at wrapping your do statement into an eval block you
can then capture the insert death in $@

i.e.

eval{
$res = $dbh->do($sql);
};
if ($@){
#if the do failed - do whatever logging/checking you want
}

"DR" == Dave Robinson <lachesis@ignmail.com> writes:

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 ;-)

DR> You want to look at wrapping your do statement into an eval block you
DR> can then capture the insert death in $@

Perl does not die; Postgres goes into ABORT STATE after the first
failure, and that is his problem. The only real way around this is to
not put the whole thing in a transaction.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

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>

Vivek Khera <khera@kcilink.com> wrote in message news:<x77kz4n9n3.fsf@yertle.kciLink.com>...

"DR" == Dave Robinson <lachesis@ignmail.com> writes:

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 ;-)

DR> You want to look at wrapping your do statement into an eval block you
DR> can then capture the insert death in $@

Perl does not die; Postgres goes into ABORT STATE after the first
failure, and that is his problem. The only real way around this is to
not put the whole thing in a transaction.

Doh! Sorry, somehow I missed the fact that it was in a transaction.
If the code isn't put into a transaction then the eval-capture stuff
should be done.
The other option is if the conditions which generate the BAD insert
can be captured, they can be tested for. The test can then be used to
decide whether to do the insert at all for that line.