Hello, my understanding, and generally my experience, has been that VACUUM and VACUUM ANALYZE (but not VACUUM FULL) are never supposed to block neither SELECT:s nor UPDATE:s/INSERT:s/DELETE:s to a table.
This is seemingly confirmed by reading the "explicit locking" documentation, in terms of the locks acquired by various forms of vacuuming, and with which other lock modes they conflict. I have now seen it happen twice that a VACUMM ANALYZE has seemingly been the triggering factor to blocking queries. In the first instance, we had two particularly interesting things going on: VACUUM ANALYZE thetable LOCK TABLE thetable IN ACCESS SHARE MODE In addition there was one SELECT from the table, and a bunch of INSERT:s (this is based on pg_stat_activity). While I am unsure of why there is an explicit LOCK going on with ACCESS SHARE MODE (no explicit locking is ever done on this table by the application), it is supposed to be the locking used for selects. I suspect it may be a referential integrity related acquisition generated by PG. The second time it happned, there was again a single SELECT, a bunch of INSERT:s, and then: VACUUM ANALYZE thetable This time there was no explicit LOCK visible. In both cases, actitivy was completely blocked until the VACUUM ANALYZE completed. Does anyone have input on why this could be happening? The PostgreSQL version is 8.2.4[1]. Am I correct in that it *should* not be possible for this to happen? For the next time this happens I will try to have a query prepared that will dump as much relevant information as possible regarding acquired locks. If it makes a difference the SELECT does have a subselect that also selcts from the same table - a MAX(colum) on an indexed column. [1] I did check the ChangeLog for 8.2.x releases above .4, and the 8.3 releases, but did not see anything that indicated locking/conflict related fixes in relation to vacuums. -- / Peter Schuller PGP userID: 0xE9758B7D or 'Peter Schuller <[EMAIL PROTECTED]>' Key retrieval: Send an E-Mail to [EMAIL PROTECTED] E-Mail: [EMAIL PROTECTED] Web: http://www.scode.org
pgpzyHqvAItZX.pgp
Description: PGP signature