Re: AutoCommit and DDL

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