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.klaver@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?
>
> We are currently using PostgreSQL 9.0.3.
>
> Thanks!
> Tim
Adrian Klaver
adrian.klaver@gmail.com
