Thread: Atomicity in DB transactions (Rollback related)

Hi,
I have a Pl/Perlu function in which I have a statement like this:

*******************
my $query_tbl_l_header = $dbh->prepare("SELECT sp_insert_tbl_l_header(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

my $exec_l_from =$query_tbl_l_header->execute($unmask_id,$from,$to,$sender,$subject,$replyto,$cc,$bcc,$messageid,$inreplyto,$reference,$mversion,$con_type,$con_id,$con_des,$con_enc,$con_length,$con_dis);
*******************

even if this execute, that calls a function sp_insert_tbl_l_header, fails,
subsequent trasactions continue without failing the whole perl function there and then and makes the Db inconsistent.
Shouldn't the whole function fail and exit at that particular failure and don't continue?
Please let  me know how do these transactions work in postgres.

Thanks,
Jas

Jasbinder Singh Bali escribió:
> Hi,
> I have a Pl/Perlu function in which I have a statement like this:
>
> *******************
> my $query_tbl_l_header = $dbh->prepare("SELECT
> sp_insert_tbl_l_header(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
>
> my $exec_l_from
>
=$query_tbl_l_header->execute($unmask_id,$from,$to,$sender,$subject,$replyto,$cc,$bcc,$messageid,$inreplyto,$reference,$mversion,$con_type,$con_id,$con_des,$con_enc,$con_length,$con_dis);
> *******************

You have a PL/PerlU opening an independent transaction via DBI?  That's
a bad idea and the explanation to your problem.  You should be using SPI
instead; there are methods for this in PL/Perl.

--
Alvaro Herrera                        http://www.advogato.org/person/alvherre
"Hay que recordar que la existencia en el cosmos, y particularmente la
elaboración de civilizaciones dentro de él no son, por desgracia,
nada idílicas" (Ijon Tichy)

Could you please give me some quick and helpful pointers for SPI programing in pl/perl?

Thanks,
Jas

Jasbinder Singh Bali escribió:
> Could you please give me some quick and helpful pointers for SPI programing
> in pl/perl?

http://www.postgresql.org/docs/8.2/static/plperl-database.html

--
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
"Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente"