Re: Allowing multiple DDL commands to run simultaneously

On Mon, Jul 9, 2018 at 6:00 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Proposal would be to add a new lock mode "ShareUpdate", which does not
> conflict with itself and yet conflicts with "ShareUpdateExclusive" or
> higher. (Hence, it is a strong lock type). DDL would take a
> ShareUpdateLock on the table, then during critical portions of
> commands it would take a ShareUpdateExclusiveLock and then release it
> again before commit.

I think this would be quite prone to deadlocks.  Suppose someone tries
to grab an AccessExclusiveLock on the table during a window in which
we hold only ShareUpdateLock.  The next attempt to upgrade to
ShareUpdateExclusiveLock will cause a simple deadlock.  In general,
any approach that involves upgrading our lock strength is likely to
have this problem.

You might be able to work around this by inventing a whole new lock
type, say "Relation Maintenance".  Make a rule that you can only take
the "Relation Maintenance" lock while holding a Relation lock with
strength >= ShareUpdateLock and that you do not need to bother
acquiring it if you hold a self-exclusive lock that conflicts with
ShareUpdateLock.  I think that works out to about the same thing as
what you're proposing, except without the deadlock hazard.

In general, though, +1 for trying to do something about this.

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