Re: Update blocking a select count(*)?

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(dot)m(dot)holland(at)gmail(dot)com> 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(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
>>
>
>