Re: Reduce lock levels for ADD and DROP COLUMN

On Mon, 2010-12-27 at 10:24 -0500, Robert Haas wrote:
> 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.

My understanding is that we access the columns directly by attid, so if
additional columns exist they would simply be ignored.

I fully expect this to be a difficult thing to believe, and I would say
there is a high potential that I am wrong about the ability to do this.
I have to say I was quite surprised when my prototype didn't immediately
explode. I'm posting before I've spent too long on the patch.

Feel free to shoot a hole in my continued optimism, anyone.

> 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.

OK, will fix.

> > 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?

The CASCADE causes other objects to be DROPped, which requires
AccessExclusiveLock. The RESTRICT case only works if nothing depends
upon that column. We already support fast drop, so the code already
allows people to ignore dropped columns they find when scanning.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services