Re: Update blocking a select count(*)?

Yes. I needed to do a full vacuum. Again, the database is very large. I
batch inserted quite a lot of data and then modified that data. The vacuum
isn't blocking anything. It was blocking other tables (as expected) but
continues to run and clean. My tables in general are around 10GB, each
update seems to nearly double the size of it so I required a full vacuum.
The blocked statements are the select count(*) and the alter table. Both
are blocked on the update table command. The alter table command SHOULD be
blocked and that is fine. The select count(*) should never be blocked as
that is the whole point of running an MVCC operation at least to my
understanding. I can even accept the use case that the select should block
with an Alter Table operation if data is retrieved from the table, but a
select count(*) only returns the number of rows and should be table space
independent. I also don't understand why a select count(*) requires an
AccessShareLock. I don't understand why a select should lock anything at
all.

~Ben

On Fri, Jun 15, 2012 at 3:03 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com> wrote:
>
> > 10:25:08.329-04 vacuum (analyze, verbose, full)
> > 2096 rmv 33528 postgres 8/151
> > AccessExclusiveLock
> > Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose,
> > full)
> > 2096 rmv 50267 postgres 8/151
> > AccessExclusiveLock
> > Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose,
> > full)
>
> You have three VACUUM FULL commands running? VACUUM FULL is very
> aggressive maintenance, which is only needed for cases of extreme
> bloat. It does lock the table against any concurrent access, since
> it is completely rewriting it.
>
> Now, if you are running UPDATE statements which affect all rows in a
> table, you will *get* extreme bloat. You either need to do such
> updates as a series of smaller updates with VACUUM commands in
> between, or schedule your aggressive maintenance for a time when it
> can have exclusive access to the tables with minimal impact.
>
> Reporting the other issues without mentioning the VACUUM FULL
> processes is a little bit like calling from the Titanic to mention
> that the ship isn't going as fast as it should and neglecting to
> mention the iceberg. :-)
>
> -Kevin
>