Re: Update blocking a select count(*)?

[Benedict Holland <benedict.m.holland@xxxxxxxxx>]

For kicks I stopped the full vacuum and the status of the remaining processes has not changed. The select count(*) is still blocked by the update. 

~Ben

On Fri, Jun 15, 2012 at 3:12 PM, Benedict Holland <benedict.m.holland@xxxxxxxxx> wrote:

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.Grittner@xxxxxxxxxxxx> wrote:

Benedict Holland <benedict.m.holland@xxxxxxxxx> 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