Re: lock level for DETACH PARTITION looks sketchy

On 2018-Dec-19, Robert Haas wrote:

> On Wed, Dec 19, 2018 at 2:44 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> > Oh, I remember eyeing that suspiciously, but was too distracted on
> > making the other thing work to realize it was actually wrong :-(
> > I agree that it's wrong.
>
> OK, cool. If you're going to push a fix for the other changes, do you
> want to do this one too, or should I fix it separately?

I can patch that one too, but it's separate -- it goes back to pg10 I
think (the other to pg11) -- and let's think about the lock mode for a
bit: as far as I can see, ShareUpdateExclusive is enough; the difference
with AccessExclusive is that it lets through modes AccessShare, RowShare
and RowExclusive. According to mvcc.sgml, that means we're letting
SELECT, SELECT FOR SHARE/UPDATE and INS/UPD/DEL in the partition being
detached, respectively. All those seem acceptable to me. All DDL is
blocked, of course.

So right now if you insert via the parent table, detaching the partition
would be blocked because we also acquire lock on the parent (and detach
acquires AccessExclusive on the parent). But after DETACH CONCURRENTLY,
inserting via the parent should let rows to the partition through,
because there's no conflicting lock to stop them. Inserting rows to the
partition directly would be let through both before and after DETACH
CONCURRENTLY.

One note about DETACH CONCURRENTLY: detaching the index from parent now
uses AccessExclusiveLock (the block I just patched). For concurrent
detach that won't work, so we'll need to reduce that lock level too. Not
sure offhand if there are any problems with that.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services