Re: commit so slow program looks frozen

On 10/28/06, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Thu, 2006-10-26 at 11:06 -0400, Merlin Moncure wrote:
> > On 10/26/06, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> > > This is pretty interesting - where can I read more on this? Windows isn't
> > > actually hanging, one single command line window is - from its behaviour, it
> > > looks like the TCL postgresql package is waiting for pg_exec to come back
> > > from the commit (I believe the commit has actually gone through).
> > >
> > > It could even be that there's something wrong with the TCL package, but from
> > > my understanding it is one of the most complete interfaces out there - which
> > > is weird, because TCL seems to be the most unpopular language in the
> > > community.
> >
> > when it happens, make sure to query pg_locks and see what is going on
> > there lock issues are not supposed to manifest on a commit, which
> > releases locks, but you never know.  There have been reports of
> > insonsistent lock ups on windows (espeically multi-processor) which
> > you might be experiencing. Make sure you have the very latest version
> > of pg 8.1.x.  Also consider checking out 8.2 and see if you can
> > reproduce the behavior there...this will require compiling postgresql.
>
> Merlin,
>
> Rumour has it you managed to get a BT from Windows. That sounds like it
> would be very useful here.
>
> Carlo,
>
> Many things can happen at commit time. Temp tables dropped, TRUNCATEd
> old relations unlinked, init files removed, deferred foreign key checks
> (and subsequent cascading), dropped tables flushed. The assumption that
> COMMIT is a short request may not be correct according to the wide range
> of tasks that could occur according to standard SQL:2003 behaviour.
>
> Some of those effects take longer on larger systems. Any and all of
> those things have potential secondary effects, all of which can also
> conflict with other user tasks and especially with a CHECKPOINT. Then
> there's various forms of contention caused by misconfiguration.
>
> I do think we need some better instrumentation for this kind of thing.
>
> --
>   Simon Riggs
>   EnterpriseDB   http://www.enterprisedb.com

start here:
http://beta.linuxports.com/pgsql-hackers-win32/2005-08/msg00051.php

merlin