DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references;
	b=EbdJa/L5rFt2re0P4Rp7qn/0HZtSeYXQ0GJQZd/KUXbpk9A3+WCaKcdERTEoIfSdit6p7ARLY0qhC9aLauE1fSPFQ8ltpke8f3gCoqM+K6vNHMQFFZ/4j6DHt5bYoQKfcLfHGUhy2e9aTDtKZzUvff4gx7ygKAf78TQXFnjbLCo=
Message-ID: <6c21003b050228164078bb435a@mail.gmail.com>
Date: Mon, 28 Feb 2005 18:40:39 -0600
From: Don Drake <dondrake@gmail.com>
Reply-To: Don Drake <dondrake@gmail.com>
To: Michael Fuhr <mike@fuhr.org>
Subject: Re: AutoCommit and DDL
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20050228033355.GA1296@winnie.fuhr.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
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