Thread: AutoCommit and DDL

AutoCommit and DDL

From
Don Drake
Date:
I have a generic function that creates partition tables and their
corresponding indexes and constraints.

I've tested the function using a GUI and it works great.  We
implemented in our data loaders (written in perl) and the DB
connections have AutoCommit turned off.  When we run it in the perl
script, I see the code being execute to create the tables, etc. but
they are never created, even with a commit at the end.  It appears
something is causing the table creation to get rolled back.

I turned AutoCommit on and re-ran the perl script and the tables get
created.  Why won't these table's get created when AutoCommit is off? 
Why do I have to commit DDL?

I'm running 7.4.3.

Thanks.

-Don

-- 
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574


Re: AutoCommit and DDL

From
Michael Fuhr
Date:
On Sat, Feb 26, 2005 at 02:56:52PM -0600, Don Drake wrote:

> I turned AutoCommit on and re-ran the perl script and the tables get
> created.  Why won't these table's get created when AutoCommit is off? 
> Why do I have to commit DDL?

If you're using DBI, are you using PrintError or RaiseError or
otherwise checking the success of each command?  My first guess
would be that some command is failing (e.g., a DROP of an object
that doesn't exist), so the transaction is automatically rolled
back even though you requested a commit.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: AutoCommit and DDL

From
Don Drake
Date:
I know it's not failing, I have the server logging the commands and
there are no errors.

The only change made was turning AutoCommit on.

-Don


On Sat, 26 Feb 2005 21:20:43 -0700, Michael Fuhr <mike@fuhr.org> wrote:
> On Sat, Feb 26, 2005 at 02:56:52PM -0600, Don Drake wrote:
> 
> > I turned AutoCommit on and re-ran the perl script and the tables get
> > created.  Why won't these table's get created when AutoCommit is off?
> > Why do I have to commit DDL?
> 
> If you're using DBI, are you using PrintError or RaiseError or
> otherwise checking the success of each command?  My first guess
> would be that some command is failing (e.g., a DROP of an object
> that doesn't exist), so the transaction is automatically rolled
> back even though you requested a commit.
> 
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
> 


-- 
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574


Re: AutoCommit and DDL

From
Don Drake
Date:
I did some traces and it all looks OK.

The problem has to do with multiple concurrent connections to the
server causing problems.  I've removed the concurrent connections and
now this works.  Strange.

-Don


On Sun, 27 Feb 2005 12:54:34 -0700, Michael Fuhr <mike@fuhr.org> wrote:
> On Sun, Feb 27, 2005 at 11:55:37AM -0600, Don Drake wrote:
> 
> > I know it's not failing, I have the server logging the commands and
> > there are no errors.
> >
> > The only change made was turning AutoCommit on.
> 
> Have you used any of DBI's tracing capabilities?  Could you post a
> simple test case?  The following works for me with Perl 5.8.6, DBI
> 1.47, DBD::Pg 1.32, and PostgreSQL 7.4.7 on FreeBSD 4.11-STABLE:
> 
> #!/usr/bin/perl
> 
> use strict;
> use warnings;
> use DBI;
> 
> my $dbh = DBI->connect("dbi:Pg:dbname=test", "mfuhr", "", {AutoCommit => 0});
> $dbh->do("CREATE TABLE foo (x integer)");
> $dbh->commit;
> $dbh->disconnect;
> 
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
> 


-- 
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574


Re: AutoCommit and DDL

From
Michael Fuhr
Date:
On Sun, Feb 27, 2005 at 07:55:35PM -0600, Don Drake wrote:

> The problem has to do with multiple concurrent connections to the
> server causing problems.  I've removed the concurrent connections and
> now this works.  Strange.

Can you elaborate?  Can you describe the scenario with enough detail
that somebody else could attempt to duplicate it?  It's certainly
possible to issue DDL statements concurrent with other connections,
so we still haven't identified what's really causing the problem.
Investigation shouldn't end until "strange" becomes "aha!"

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: AutoCommit and DDL

From
Michael Fuhr
Date:
On Sun, Feb 27, 2005 at 11:55:37AM -0600, Don Drake wrote:

> I know it's not failing, I have the server logging the commands and
> there are no errors.
> 
> The only change made was turning AutoCommit on.

Have you used any of DBI's tracing capabilities?  Could you post a
simple test case?  The following works for me with Perl 5.8.6, DBI
1.47, DBD::Pg 1.32, and PostgreSQL 7.4.7 on FreeBSD 4.11-STABLE:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=test", "mfuhr", "", {AutoCommit => 0});
$dbh->do("CREATE TABLE foo (x integer)");
$dbh->commit;
$dbh->disconnect;

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: AutoCommit and DDL

From
Don Drake
Date:
I don't think it would be easy to duplicate since our code base is
quite extensive.

Basically, what was happening was a script would first open a database
connection (AutoCommit turned off by default), create a few objects
(that also opened independent db connections), the objects would run
queries so they have data populated, an insert is done and committed,
then we call a generic function that will create a new table (using
inherits, part of our partitioning) as well as adding indexes and
constraints to this new table.  It would get to a point in the
function where it was adding a FK constraint and every query against
the table would "hang" which appeared to be some exclusive lock not
being released.  Activity on the DB would be 100% idle during this
period, the alter table never came back so we killed it each time.  I
commented out the code doing the FK constraint add and everything
worked just fine.

As a test I moved the partition function call to the beginning of the
script (before the objects  were created) and it worked just fine.  I
then changed the object declarations passing in the single DB handle,
and every now works just fine.

I ran DBI traces and everything looked just fine.  This was a strange
problem, I'm just happy everything is working.

-Don


On Sun, 27 Feb 2005 20:33:55 -0700, Michael Fuhr <mike@fuhr.org> wrote:
> On Sun, Feb 27, 2005 at 07:55:35PM -0600, Don Drake wrote:
> 
> > The problem has to do with multiple concurrent connections to the
> > server causing problems.  I've removed the concurrent connections and
> > now this works.  Strange.
> 
> Can you elaborate?  Can you describe the scenario with enough detail
> that somebody else could attempt to duplicate it?  It's certainly
> possible to issue DDL statements concurrent with other connections,
> so we still haven't identified what's really causing the problem.
> Investigation shouldn't end until "strange" becomes "aha!"
> 
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
> 


-- 
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574


Re: AutoCommit and DDL

From
Andrew - Supernews
Date:
On 2005-03-01, Don Drake <dondrake@gmail.com> wrote:
> I don't think it would be easy to duplicate since our code base is
> quite extensive.
>
> Basically, what was happening was a script would first open a database
> connection (AutoCommit turned off by default), create a few objects
> (that also opened independent db connections), the objects would run
> queries so they have data populated, an insert is done and committed,
> then we call a generic function that will create a new table (using
> inherits, part of our partitioning) as well as adding indexes and
> constraints to this new table.  It would get to a point in the
> function where it was adding a FK constraint and every query against
> the table would "hang" which appeared to be some exclusive lock not
> being released.  Activity on the DB would be 100% idle during this
> period, the alter table never came back so we killed it each time.  I
> commented out the code doing the FK constraint add and everything
> worked just fine.

This sounds as though your application deadlocked against itself - by
using multiple connections without autocommit, you can easily get into
situations where you are waiting for completion on one connection, which
is blocked waiting for a lock held by another connection - the lock remains
until the second connection commits, which never happens since the app is
waiting on the first. The DB can't detect this as a deadlock because it
does not know that one session is waiting on another on the client side;
deadlock detection considers only sessions waiting _inside the server_.

This situation isn't specific to DDL, but is easier to produce that way
since most DDL operations acquire very high level locks (often
AccessExclusive, which blocks queries).

> As a test I moved the partition function call to the beginning of the
> script (before the objects  were created) and it worked just fine.  I
> then changed the object declarations passing in the single DB handle,
> and every now works just fine.

This is consistent with it being a client-side deadlock.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services