Re: BUG #2631: database locking problem

I'll see what I can get done, I'll need to get it to lockup again as
I've killed everything to try other options. I have a feeling that the
only reason it worked on 8.1.3 before was because it was on slower
servers which were also running other stuff. My new database server is
very fast and only runs the database and if I log all the sql then it
doesn't seem to screw up (at least not yet). One other thing, the
tablespace is not local storage, I have a 7TB SAN partition that runs a
SAN filesystem on it, but this is the same as my older 8.1.3 system.

--
Ross Elliott
Senior Software Engineer
Infoterra Ltd
T +44 (0)1252 362095
www.infoterra.co.uk

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 17 September 2006 22:45
To: Ross Elliott
Cc: Michael Fuhr; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #2631: database locking problem

"Ross Elliott" <ross(dot)elliott(at)infoterra-global(dot)com> writes:
> I've been playing with this a bit more by switching to 8.1.3 with
> postgis 1.1.3 and still get the locking problem.

Well, that's dang odd. PID 9403 seems to be the problem, because it's
got extend lock on topoarea_idx2, which is a lock that should be held
for only a *very* short time. Apparently it's blocked on an LWLock
inside either ReadBuffer or LockBuffer --- but the LockBuffer should
certainly not block because ReadBuffer should have returned a page that
never existed until just now, and hence couldn't be locked by anyone
else. Even more interesting, topoarea_idx2 is a plain btree, which
shouldn't be affected at all by postgis. And none of this code has
changed much recently.

Can you attach to 9403 with a debugger and get a stack trace to confirm
exactly where it's blocked?

regards, tom lane