Thread: Documentation of what schema modifications cause what level of table locking
Documentation of what schema modifications cause what level of table locking
From
Timothy Garnett
Date:
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
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
Re: Documentation of what schema modifications cause what level of table locking
From
Adrian Klaver
Date:
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@gmail.com
Re: Documentation of what schema modifications cause what level of table locking
From
Timothy Garnett
Date:
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
Tim
On Sat, Sep 24, 2011 at 2:23 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
http://www.postgresql.org/docs/9.1/interactive/explicit-locking.htmlOn 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