Re: Avoid deadlocks on alter table

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> On 07/05/2016 06:30 AM, Christian Castelli wrote:
>> ALTER TABLE smartphone
>> ADD CONSTRAINT pk_smartphone PRIMARY KEY (id),
>> ADD CONSTRAINT fk1 FOREIGN KEY (id_contact)
>> REFERENCES contact (id) MATCH SIMPLE
>> ON UPDATE RESTRICT ON DELETE RESTRICT,
>> ADD CONSTRAINT fk_plan FOREIGN KEY (id_tf)
>> REFERENCES public.tariff_plan(id) MATCH SIMPLE
>> ON UPDATE RESTRICT ON DELETE RESTRICT,
>> ADD CONSTRAINT fk_ram FOREIGN KEY (ret_id)
>> REFERENCES ram (id) MATCH SIMPLE
>> ON UPDATE RESTRICT ON DELETE RESTRICT,
>> ADD CONSTRAINT u_imei UNIQUE (imei_code);

> Does the table smartphone have data in it when you do the above ALTER?

I doubt it would matter. The problem with this is that it needs to take
exclusive lock on each one of the referenced tables (so as to add a new
foreign-key enforcement trigger). So any other transaction that is
accessing any two of those tables in a different order than this does
creates a deadlock hazard.

> Have you looked at separating the FK creation and validation?:

I think it'd likely be enough to add the FKs one at a time, rather
than all in one transaction.

regards, tom lane