Thread: Perl and AutoCommit

Perl and AutoCommit

From
Madison Kelly
Date:
Hi all,

   After looking at the postres and perl docs plus some Googling I can't
seem to find the answer I am looking for. I hope maybe someone here can
help. ^_^

   What I am trying to do is turn off autocommit for one particular task
in my program. I realize I can turn off AutoCommit when I connect to the
database but in this case that is not what I want to do. This is a
one-off task.

   What I thought would work was:

$DB->begin_work() || die...
# a lot of transactions
$DB->commit() || die...

   But that doesn't seem to be it, either...

   Any advice would be much appreciated. Even if it's just a pointer to
the right docs with the answer that I probably missed while searching. :)

Madison

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Re: Perl and AutoCommit

From
Dan Sugalski
Date:
At 12:31 AM -0500 3/27/05, Madison Kelly wrote:
>Hi all,
>
>   After looking at the postres and perl docs plus some Googling I
>can't seem to find the answer I am looking for. I hope maybe someone
>here can help. ^_^
>
>   What I am trying to do is turn off autocommit for one particular
>task in my program. I realize I can turn off AutoCommit when I
>connect to the database but in this case that is not what I want to
>do. This is a one-off task.

Set the AutoCommit attribute of the database handle. Pull up the docs
for DBI and search for the "Database Handle Attributes" section.
--
                Dan

--------------------------------------it's like this-------------------
Dan Sugalski                          even samurai
dan@sidhe.org                         have teddy bears and even
                                       teddy bears get drunk

Re: Perl and AutoCommit

From
Yanni Chiu
Date:
Madison Kelly wrote:
>
>    What I am trying to do is turn off autocommit for one particular task
> in my program. I realize I can turn off AutoCommit when I connect to the
> database but in this case that is not what I want to do. This is a
> one-off task.
>
>    What I thought would work was:
>
> $DB->begin_work() || die...
> # a lot of transactions
> $DB->commit() || die...
>
>    But that doesn't seem to be it, either...

The way I read the postgres docs, there is no autocommit.
Every sql command is wrapped in a transaction, unless
you issue a "BEGIN" (or "START TRANSACTION").

What makes you think that the code above did not work?
What Perl library are you using?
What does $DB->begin_work() actually do?
--I'm thinking there could be a $DB->begin_transaction()
You could turn on some trace output in the postgres
server (i.e. postmaster), and observe the sequence
of SQL that is being sent. HTH.

--yanni

Re: Perl and AutoCommit

From
Madison Kelly
Date:
Dan Sugalski wrote:
> At 12:31 AM -0500 3/27/05, Madison Kelly wrote:
>
>> Hi all,
>>
>>   After looking at the postres and perl docs plus some Googling I
>> can't seem to find the answer I am looking for. I hope maybe someone
>> here can help. ^_^
>>
>>   What I am trying to do is turn off autocommit for one particular
>> task in my program. I realize I can turn off AutoCommit when I connect
>> to the database but in this case that is not what I want to do. This
>> is a one-off task.
>
>
> Set the AutoCommit attribute of the database handle. Pull up the docs
> for DBI and search for the "Database Handle Attributes" section.

Hmm... I've read that now (thank you!) and I have added:

$acw=$DB->{AutoCommit};
print " |- AutoCommit was: [$acw]\n";
$DB->{AutoCommit} = 0 || die...
$acn=$DB->{AutoCommit};
print " |- AutoCommit now: [$acw]\n";
$DB->begin_work() || die...
#lot of transactions
$DB->commit() || die...

For some reason though my program dies on the '$DB->{AutoCommit}=0 ...'
line. I usually print '$DBI::errstr' in the 'die' to see what happened
but that is blank. I read in that section that some databases simply
don't support turning off AC and will die with a fatal error if you try
to turn it off. Could this be what is happening? If so, shouldn't there
be a more verbose error? Also, I know pgSQL supports AC being turned off
because I first read about this feature in there docs. Am I making an
mistake?

Many thanks!

Madison

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Re: Perl and AutoCommit

From
Dan Sugalski
Date:
At 1:34 AM -0500 3/27/05, Madison Kelly wrote:
>Dan Sugalski wrote:
>>At 12:31 AM -0500 3/27/05, Madison Kelly wrote:
>>
>>>Hi all,
>>>
>>>   After looking at the postres and perl docs plus some Googling I
>>>can't seem to find the answer I am looking for. I hope maybe
>>>someone here can help. ^_^
>>>
>>>   What I am trying to do is turn off autocommit for one particular
>>>task in my program. I realize I can turn off AutoCommit when I
>>>connect to the database but in this case that is not what I want
>>>to do. This is a one-off task.
>>
>>
>>Set the AutoCommit attribute of the database handle. Pull up the
>>docs for DBI and search for the "Database Handle Attributes"
>>section.
>
>Hmm... I've read that now (thank you!) and I have added:
>
>$acw=$DB->{AutoCommit};
>print " |- AutoCommit was: [$acw]\n";
>$DB->{AutoCommit} = 0 || die...
>$acn=$DB->{AutoCommit};
>print " |- AutoCommit now: [$acw]\n";
>$DB->begin_work() || die...
>#lot of transactions
>$DB->commit() || die...
>
>For some reason though my program dies on the '$DB->{AutoCommit}=0 ...' line.

Well.. yeah. Assignment's an expression, with a value of the right
hand side of the expression. Since the assignment part of the logical
or is false, the right hand side's evaluated and you die, not because
anything failed.

The docs say it's a fatal error to use an unsupported value, so I'd
assume it actually dies and you'd need to trap with eval.
--
                Dan

--------------------------------------it's like this-------------------
Dan Sugalski                          even samurai
dan@sidhe.org                         have teddy bears and even
                                       teddy bears get drunk

Re: Perl and AutoCommit

From
Ragnar Hafstað
Date:
On Sun, 2005-03-27 at 00:31 -0500, Madison Kelly wrote:

>    What I thought would work was:
>
> $DB->begin_work() || die...
> # a lot of transactions
> $DB->commit() || die...
>

maybe a more complete testcase would be in order.

gnari@gnari:~/test $ cat trans.pl
use DBI;
our $dbh = DBI->connect('dbi:Pg:dbname=test') or die 'no database';
$dbh->do('create table transtest(a text)');
$dbh->begin_work() or die 'error in begin';
$dbh->do("insert into transtest values ('foo')");
$dbh->do("insert into transtest values ('bar')");
$dbh->commit() or die 'error in commit' ;
system("psql -d test -c 'select * from transtest;'")

gnari@gnari:~/test $ perl trans.pl
  a
-----
 foo
 bar
(2 rows)

gnari@gnari:~/test $


gnari@gnari:~/test $ perl -v

This is perl, v5.8.4 built for x86_64-linux-thread-multi

gnari@gnari:~/test $ perl -MDBI -le 'print $DBI::VERSION'
1.42
gnari@gnari:~/test $


perhaps a database operation is failing between your begin_work()
and your commit() ?

gnari



Re: Perl and AutoCommit

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> $DB->{AutoCommit} = 0 || die...

As someone pointed out, this always dies. In general, you don't
need (or want) to test the results of setting a variable. Also be
aware that in this case you probably want "or" and not "|" - the former
tests the result of "$DB->{AutoCommit} = 0", while the latter actually
tries to set $DB->{AutoCommit} to first "0" and then to the result
of "die". What you want is really

($DB->{AutoCommit} = 0) || die;

which is the same thing, and better written as:

$DB->{AutoCommit} = 0 or die;

Best of all is just to remove the line entirely, as there is no point in
testing the assignment: simply view the contents afterwards:

$DB->{AutoCommit} = 0;
print " |- AutoCommit now: $DB->{AutoCommit}\n";

Also be aware that for DBD::Pg, the following is a known bug
(will be fixed in the upcoming 1.41 release):

> $DB->commit() || die...

In some circumstances commit() returns false when it should return true
(even though it still committed). If you find that happening, simply change
it to $DB->commit(); and trust that the correct thing is indeed happening.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200503271548
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCRx7hvJuQZxSWSsgRAntrAKDyjLhxsSMjIhsjMVtuEwbXXdeqSACg+ri1
F65YHQhWg2c0j8ZwT9LMDP4=
=gTE2
-----END PGP SIGNATURE-----



Re: Perl and AutoCommit

From
Frnak McKenney
Date:
On Sun, 27 Mar 2005 01:34:23 -0500, Madison Kelly <linux@alteeve.com> wrote:
> Dan Sugalski wrote:
>> At 12:31 AM -0500 3/27/05, Madison Kelly wrote:
>>>   What I am trying to do is turn off autocommit for one particular
>>> task in my program. I realize I can turn off AutoCommit when I connect
>>> to the database but in this case that is not what I want to do. This
>>> is a one-off task.
>>
>> Set the AutoCommit attribute of the database handle. Pull up the docs
>> for DBI and search for the "Database Handle Attributes" section.
>
> Hmm... I've read that now (thank you!) and I have added:
>
> $acw=$DB->{AutoCommit};
> print " |- AutoCommit was: [$acw]\n";
> $DB->{AutoCommit} = 0 || die...
> $acn=$DB->{AutoCommit};
> print " |- AutoCommit now: [$acw]\n";

Or maybe:
  print " |- AutoCommit now: [$acn]\n";
                            -----^

(Minor typo, may not be relevant).


Frank McKenney

Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)
--
    One new phenomenon of our times was the establishment of English
    schools and departments in the universities at about the same time
    as "modernism" arose.  For the first time, we had a specific and
    separate group that was supposed to be exceptionally qualified to
    judge literature, as against the larger, more heterogenous set of
    people constituting the cultural community.  ...  Academic critics
    claimed to be the only ones competent to discuss poetry properly
    and indeed to prescribe its forms, methods, and contents.  This is
    as if a claim should be put forward that only professors of
    ballistics should discuss cricket or football.
                   -- Robert Conquest, "The Dragons of Expectation"
--