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" --