Re: second concurrent update takes forever

Janning Vygen <vygen(at)kicktipp(dot)de> writes:
> We are running a rather complicated Update statement from time to time which
> is only triggered by administrators. The statement updates about 50.000 rows.
> It takes usually about 10-30 seconds to execute and that's fine for us.

> This time two administrator did run this update at approximately the same
> time, so the second update started before the first finished.

> The first update took about 30 seconds which is quite long but not a problem
> for us. the second update was useless, because everything was already updated.
> Anyway, the second statement took 5 hours!

> ...

> so the essence is: the statement has a rather long executing sub-select
> statement which takes about a 800ms. I "suspect" postgresql to do the sub-
> selection and while updating the first row of this sub-select it sees that this
> row was updated since the update statement (or the transaction) was started.
> And then it re-checks the condition just for this row (or maybe for a bunch of
> rows on the same page). if it comes to updating the next row it happens again.

> re-checking 40000+ rows with a very expensive sub-select plan is not good at
> all. 40000 times 800 ms = 10 hours.

> Am I right with my suspicion?

Yeah, that's a fairly accurate description of how EvalPlanQual works in
current releases.

> If yes, how can I fix it?

Don't do that ;-).

If there's only one of these that's supposed to run at a time, you might
try taking out some self-exclusive lock type before running it.

There is a rewritten version of EvalPlanQual in CVS tip --- if you have
the ability to test your problem situation on 8.5alpha3 or newer, I'd be
interested to know whether it (a) works faster and (b) gets the right
answers.

regards, tom lane