Re: ALTER TABLE deadlock with concurrent INSERT

Joe Conway <mail@joeconway.com> writes:
> I'm working with a client on an application upgrade script which
> executes a function to conditionally do an:

>   ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE baz

> If this is run while the application is concurrently doing inserts into
> foo, we are occasionally seeing deadlocks. Aside from the fact that they
> are better off not altering the table amid concurrent inserts, I'm
> trying to understand why this is even able to happen. I expect one to
> block the other, not a deadlock.

Looks like the process trying to do the ALTER has already got some
lower-level lock on the table.  It evidently hasn't got
AccessExclusiveLock, but nonetheless has something strong enough to
block an INSERT, such as ShareLock.
        regards, tom lane