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

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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com
>