On Wed, 05 Nov 2008 10:53:38 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Can you put together a self-contained test case that illustrates
> >> this?
>
> > I'm trying... Tonight I just let my long transaction run all
> > night. It has been running for about 10h and it blocked on index
> > re-creation.
>
> I'd suggest just loading up some dummy data in a medium-size table
> and seeing if you can reproduce the variance in index build time.
> It's unlikely that you need a ten-hour test case for that. But
> there may be some small detail of what you're doing that is
> necessary to show the problem, so I'm not going to try to
> reproduce it here with no concrete example to go on.
I'm still testing... I'm starting to think that it is not a
coincidence that every time I rebuild the index in another
*connection*, no matter what I did to the DB before, the index get
rebuilt in around less than 7min (generally much less!).
Nearly all the time the index is built in the same *connection* of
the transaction, no matter if inside the transaction or not... it
takes forever...
I've started to disassemble the transaction and add to a new script
a bit at a time.
I can't conclude if it is some leak somewhere or a specific part of
the transaction that has some side effect since:
- starting from an empty transaction and adding back something works
- taking away something from the whole transaction doesn't work
so it can be something I still haven't stripped away or it could be
I didn't contribute enough to the leak to trigger the problem.
As soon as I'll be able to find out which part of the whole
transaction (or if just the accumulation of operations made in it)
trigger the problem I'll report it back.
Meanwhile I'm putting more heavily under test the hypothesis that
the problem is linked to connections, since that seems have solved
the problem and found its more stable place in the code.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it