Обсуждение: DBI, savepoints, transactions, and AutoCommit
I understand the concepts of savepoints, and I have played with them
in psql enough to understand that yes, as the fine manual says, they
must be inside a transaction to work. But how does this relate to DBI
and DBD::Pg with AutoCommit off? I learned the slow way that if
AutoCommit is disabled, you don't issue a BEGIN WORK because that
looks like trying to start a transaction inside a transaction. But I
I have AutoCommit off and try SAVEPOINT xyzzy, it tells me savepoints
must be inside a transaction. And of course, I can't get inside a
transaction because I can't issue a BEGIN WORK because I am already
inside a transaction.
Is this a case of what's sauce for the goose not being sauce for the
gander?
How do I use savepoints in DBI/DBD::Pg without enabling AutoCommit?
Do I turn AutoCommit on, BEGIN WORK, SAVEPOINT xyzzy, and disable
AutoCommit again? That sure doesn't sound reasonable.
(I also have tis problem with SQLite, so I suspect it's a brain
failure on my part rather than the rest of the world conspiring to
confuse me, which isn't even very hard to do.)
--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
--As of November 2, 2011 1:14:40 PM -0700, felix@crowfix.com is alleged to have said: > I understand the concepts of savepoints, and I have played with them > in psql enough to understand that yes, as the fine manual says, they > must be inside a transaction to work. But how does this relate to DBI > and DBD::Pg with AutoCommit off? I learned the slow way that if > AutoCommit is disabled, you don't issue a BEGIN WORK because that > looks like trying to start a transaction inside a transaction. But I > I have AutoCommit off and try SAVEPOINT xyzzy, it tells me savepoints > must be inside a transaction. And of course, I can't get inside a > transaction because I can't issue a BEGIN WORK because I am already > inside a transaction. --As for the rest, it is mine. Sounds like you are doing things right. What versions of the following are you using? DBI DBD::Pg Postgres Also, do you have any example code that shows the problem? Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
On Wed, Nov 02, 2011 at 06:05:56PM -0400, Daniel Staal wrote:
> Sounds like you are doing things right. What versions of the following are
> you using?
> DBI
DBI-1.616.0
> DBD::Pg
DBD-Pg-2.18.1
> Postgres
psql (9.0.4)
> Also, do you have any example code that shows the problem?
Weeeelllll ... no, not exactly. It's part of a project and not
centralized. I have enough tracing and debug code to satisfy myself
that I know what it's doing, but maybe that's not enough. So I'll
work up some stupid little test program. Should have thought of that
sooner. Might actually have a logic flaw I don't see.
--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
On Wed, Nov 02, 2011 at 06:05:56PM -0400, Daniel Staal wrote:
> Also, do you have any example code that shows the problem?
Well shiver me timbers! I wrote a sample program ... and it works. I
could have sworn I was logging all SQL executed, but it looks like
something is slipping by. I've attached the program just to be
thorough.
Sorry for not discovering before I posted. You guys get enough noise.
================
#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::Pg;
my $connect = "dbi:Pg:dbname=$ENV{LOGNAME}";
my $schema = 'xyzzy';
my $svname = 'plugh';
die $DBI::errstr unless my $dbh = DBI->connect($connect, undef, undef, { RaiseError => 0, PrintError => 0, AutoCommit
=>0 });
die $DBI::errstr unless $dbh->do("CREATE SCHEMA $schema");
die $DBI::errstr unless $dbh->do("SET search_path = $schema");
die $DBI::errstr unless $dbh->do("SET client_min_messages='warning'");
die $DBI::errstr unless $dbh->do("CREATE TABLE teste (kolum INT UNIQUE)");
die $DBI::errstr unless $dbh->do("COMMIT");
die $DBI::errstr unless $dbh->do("INSERT INTO teste (kolum) VALUES (1)");
die $DBI::errstr unless $dbh->pg_savepoint($svname);
die $DBI::errstr unless $dbh->do("INSERT INTO teste (kolum) VALUES (2)");
die "Dup insert did not fail" if $dbh->do("INSERT INTO teste (kolum) VALUES (2)");
print STDERR "$DBI::errstr\n";
die $DBI::errstr unless $dbh->pg_rollback_to($svname);
die $DBI::errstr unless $dbh->do("INSERT INTO teste (kolum) VALUES (3)");
die $DBI::errstr unless $dbh->pg_release($svname);
die $DBI::errstr unless $dbh->do("COMMIT");
#die $DBI::errstr unless my $rows = $dbh->selectall_arrayref("SELECT kolum FROM teste ORDER BY kolum");
die $DBI::errstr unless my $sth = $dbh->prepare("SELECT kolum FROM teste ORDER BY kolum");
die $DBI::errstr unless $sth->execute();
die $DBI::errstr unless my $rows = $sth->fetchall_arrayref();
print STDERR join("\t", 'FETCHED', @$_), "\n" foreach @$rows;
die $DBI::errstr unless $dbh->disconnect();
================
--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
--As of November 2, 2011 4:53:18 PM -0700, felix@crowfix.com is alleged to have said: > Well shiver me timbers! I wrote a sample program ... and it works. I > could have sworn I was logging all SQL executed, but it looks like > something is slipping by. I've attached the program just to be > thorough. > > Sorry for not discovering before I posted. You guys get enough noise. Sometimes you just need a different voice. ;) Not a problem, I think. > ================ ># !/usr/bin/perl -w As the Perl guy at my office: You *do* know the difference between this and 'use warnings;', right? And why the latter is almost always what you want? (Sorry, it's one of those things I've learned to jump on, as it's often just Cargo Cult Programming...) Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
On Wed, Nov 02, 2011 at 08:23:09PM -0400, Daniel Staal wrote:
> --As of November 2, 2011 4:53:18 PM -0700, felix@crowfix.com is alleged to
> ># !/usr/bin/perl -w
>
> As the Perl guy at my office: You *do* know the difference between this and
> 'use warnings;', right? And why the latter is almost always what you want?
> (Sorry, it's one of those things I've learned to jump on, as it's often
> just Cargo Cult Programming...)
It's been ages since I settled on -w instead of use warnings. I know
there's a difference, I have some vague recollection of -w being
"better", whatever that means, but it's one of those things that I
have "known" for so long that I no longer know what I once knew :-)
All my perl books -- scratch that, I just googled for it. -w is all
or nothing, and especially it enables warnings for modules which might
be better off without warnings. So it says.
But that advice seems a mite stale nowadays. It says use warnings was
introduced in 5.6, so any older modules will possibly barf, or any
newer modules which turn warnings off for their own reasons. So why
would I care about this now? I don't recollect any untoward warnings
in years of programming Perl.
--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
On Wed, Nov 02, 2011 at 04:53:18PM -0700, felix@crowfix.com wrote:
> On Wed, Nov 02, 2011 at 06:05:56PM -0400, Daniel Staal wrote:
>
> > Also, do you have any example code that shows the problem?
>
> Well shiver me timbers! I wrote a sample program ... and it works. I
> could have sworn I was logging all SQL executed, but it looks like
> something is slipping by. I've attached the program just to be
> thorough.
Found the logic misflow -- some ancient old code was trying to be
helpful and rolled back the entire transaction after a dup was
detected. Works better without that ...
--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
felix@crowfix.com wrote: > On Wed, Nov 02, 2011 at 08:23:09PM -0400, Daniel Staal wrote: >> --As of November 2, 2011 4:53:18 PM -0700, felix@crowfix.com is alleged to > >>> # !/usr/bin/perl -w >> As the Perl guy at my office: You *do* know the difference between this and >> 'use warnings;', right? And why the latter is almost always what you want? >> (Sorry, it's one of those things I've learned to jump on, as it's often >> just Cargo Cult Programming...) > > It's been ages since I settled on -w instead of use warnings. I know > there's a difference, I have some vague recollection of -w being > "better", whatever that means, but it's one of those things that I > have "known" for so long that I no longer know what I once knew :-) > All my perl books -- scratch that, I just googled for it. -w is all > or nothing, and especially it enables warnings for modules which might > be better off without warnings. So it says. > > But that advice seems a mite stale nowadays. It says use warnings was > introduced in 5.6, so any older modules will possibly barf, or any > newer modules which turn warnings off for their own reasons. So why > would I care about this now? I don't recollect any untoward warnings > in years of programming Perl. There are numerous reasons why "use warnings;" is far better than "-w". First of all, the former is localized. Well known modules turn warnings off for the duration so they won't confuse you. You can do the same with code that you are certain is bug free. Second, it is more granular. You can turn specific classes of warnings off or on (see 'perldoc warnings'). Third, when combined with "use strict;" it can lead you to those simple, but difficult to uncover, typographic errors we all make. You know, the misspelled variable, the variable where you capitalized the wrong letter, or left off the dollar sign. You should probably get to know both of these a bit better. Current best practices is to start every script with these two lines: use warnings; use strict; That simple change will identify a lot of problems that you might not even know you are tripping over. Bob McConnell N2SPP