Postgres Pro
Downloads
Home   >   mailing lists

Thread: vacuum bug

vacuum bug

From
"Christopher Kings-Lynne"
Date:
Hi,

I was running a long-running vacuum full, and then halfway thru that our
background vacuum process started.  As well as this, there was light
activity on a users table from which vacuum full was removing 90000 rows.

Then vacuum full failed after a while:

ERROR:  simple_heap_update: tuple concurrently updated

Chris

Re: vacuum bug

From
Tom Lane
Date:
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I was running a long-running vacuum full, and then halfway thru that our
> background vacuum process started.  As well as this, there was light
> activity on a users table from which vacuum full was removing 90000 rows.

There would be *zero* activity on a table undergoing vacuum full, unless
your app has found a way around vacuum full's exclusive lock.  You sure
this wasn't a plain vacuum?

> Then vacuum full failed after a while:
> ERROR:  simple_heap_update: tuple concurrently updated

Were you doing VACUUM ANALYZEs?

It's possible for two concurrent VACUUM ANALYZEs of the same table
to get this failure from trying to concurrently update the same row in
pg_statistic.  (The cure for this seems worse than the disease: AFAICS
you'd have to prevent *all* concurrent updates of pg_statistic by
grabbing a table-level lock.  So we just live with one of the analyzes
reporting a failure.  All the useful work gets done anyway, by one
transaction or the other.)
        regards, tom lane

Re: vacuum bug

From
"Christopher Kings-Lynne"
Date:
> There would be *zero* activity on a table undergoing vacuum full, unless
> your app has found a way around vacuum full's exclusive lock.  You sure
> this wasn't a plain vacuum?

Hmm...correct.  So I don't know what happened.

> > Then vacuum full failed after a while:
> > ERROR:  simple_heap_update: tuple concurrently updated
> 
> Were you doing VACUUM ANALYZEs?

The background vacuum was doing analyze, the full one was not.

> It's possible for two concurrent VACUUM ANALYZEs of the same table
> to get this failure from trying to concurrently update the same row in
> pg_statistic.  (The cure for this seems worse than the disease: AFAICS
> you'd have to prevent *all* concurrent updates of pg_statistic by
> grabbing a table-level lock.  So we just live with one of the analyzes
> reporting a failure.  All the useful work gets done anyway, by one
> transaction or the other.)

Hmmm...I don't see why I would have had two concurrent analyzes going on...

I guess there's not enough info to diagnose it anyway...

Chris