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.holl...@gmail.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.gritt...@wicourts.gov> wrote: > >> Benedict Holland <benedict.m.holl...@gmail.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 >> > >
