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 <peter.schuller@infidyne.com>'
Key retrieval: Send an E-Mail to getpgpkey@scode.org
E-Mail: peter.schuller@infidyne.com Web: http://www.scode.org
