Thread: Perl and AutoCommit
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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
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
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
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
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
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
-----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-----
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"
--