Re: Reduce lock levels for ADD and DROP COLUMN

On Mon, Dec 27, 2010 at 6:42 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Idea is to reduce lock level of ADD/DROP COLUMN from AccessExclusiveLock
> down to ShareRowExclusiveLock.
>
> To make it work, we need to recognise that we are adding a column
> without rewriting the table.

Can you elaborate on why you think that's the right test? It seems to
me there could be code out there that assumes that the tuple
descriptor won't change under it while it holds an AccessShareLock.
What will happen if we're in the middle of returning tuples from a
large SELECT statement and we start seeing tuples with additional
attributes that we're not expecting? I'm particularly concerned about
cases where the user is doing "SELECT * FROM table" and the scan is
returning pointers to in-block tuples. If the schema suddenly changes
under us, we might need to start doing a projection step, but I think
the executor isn't going to know that.

If that's not a problem (how can we be confident of that?), then
perhaps ShareUpdateExclusive is just as good - if selects are OK, why
not inserts, updates, and deletes? There may be a reason, but I think
some analysis is needed here.

Incidentally, I notice that explicit-locking.html mentions that ALTER
TABLE may sometimes acquire AccessExclusiveLock and other times
ShareRowExclusiveLock, but it doesn't mention that it may sometimes
acquire only ShareUpdateExclusiveLock.

> DROP ... RESTRICT works fine at reduced lock level, assuming I'm not
> missing anything...

Same general issues here. Also, why is DROP .. RESTRICT different
from DROP .. CASCADE?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company