Re: Documentation of what schema modifications cause what level of table locking

[Timothy Garnett <tgarnett@xxxxxxxxxxx>]

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@xxxxxxxxx> 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.klaver@xxxxxxxxx