Re: ALTER TABLE deadlock with concurrent INSERT

On 03/03/2011 11:36 PM, Noah Misch wrote:
> Does relation 16902 (attribute_summary) have a foreign key constraint over the
> sequence_number column, in either direction, with relation 16896? That would
> explain it:
>
> session 1: ALTER TABLE attribute_summary ... <sleeps after relation_openrv in transformAlterTableStmt>
> session 2: SELECT 1 FROM rel16896 LIMIT 0;
> session 2: SELECT 1 FROM attribute_summary LIMIT 0; <blocks>
> session 1: <wakes up; continues ALTER TABLE: deadlock upon locking rel16896>

Ah, OK -- then that would explain it as there are foreign keys on that
column. Thanks, hadn't thought about that aspect.

> Granted, the cure may be worse than the disease.

Right. I've already advised they shut down the application during the
alter table, which they can do (and in fact already do -- they were
restarting the application just prior to this step, which really makes
no sense anyway).

Thanks,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support