From: Don Drake <dondrake@gmail.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: AutoCommit and DDL
Date: Mon, 28 Feb 2005 18:40:39 -0600
Message-ID: <6c21003b050228164078bb435a@mail.gmail.com> (raw)
In-Reply-To: <20050228033355.GA1296@winnie.fuhr.org>
References: <6c21003b050226125634e9964@mail.gmail.com>
<20050227042043.GB77382@winnie.fuhr.org>
<6c21003b0502270955673432e7@mail.gmail.com>
<20050227195434.GA21745@winnie.fuhr.org>
<6c21003b0502271755fb9b917@mail.gmail.com>
<20050228033355.GA1296@winnie.fuhr.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dondrake@gmail.com, mike@fuhr.org
Subject: Re: AutoCommit and DDL
In-Reply-To: <6c21003b050228164078bb435a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox