Thanks, this is exactly what I was looking for. The listed commands that grab the ACCESS EXCLUSIVE lock are the ones we have to watch out for.
Tim On Sat, Sep 24, 2011 at 2:23 PM, Adrian Klaver <adrian.kla...@gmail.com>wrote: > On Friday, September 23, 2011 3:52:54 pm Timothy Garnett wrote: > > Hi all, > > > > I was wondering if there was some good documentation on what kinds of > > schema modifications block reads vs. which ones don't. For ex. we > > recently had an issue where someone ran as part of a migration > > > > ALTER TABLE tname ALTER COLUMN cname SET NOT NULL; > > > > on a large table that is not inserted to or updated. While we'd expect > > such an operation to block inserts/updates (writes) to the table, we were > > surprised to observe that it also blocked selects (reads) from the table > > as well, which we would not have naively expected (and caused a great > deal > > of headache). On the other hand creating an index on a table blocks > > writes, but still allows reads (even a unique index), as documented in > the > > create index docs. Is there a list somewhere of what operations block > > selects (reads) to a table that we should watch out for? > > http://www.postgresql.org/docs/9.1/interactive/explicit-locking.html > > > > > We are currently using PostgreSQL 9.0.3. > > > > Thanks! > > Tim > > -- > Adrian Klaver > adrian.kla...@gmail.com >
