Avoid deadlocks on alter table

[Christian Castelli <voodoo81people@xxxxxxxxx>]

Hi everybody,

my database is composed of multiple schemata, one for each customer, and some global views which do UNION across schemata.

I create a new customer with a single transaction, with queries like:

CREATE TABLE table1 WITHOUT OIDS AS
TABLE base_template.table1 WITH NO DATA;

base_template is just an empty schema used as template. The very first statement is:

CREATE SCHEMA :CUSTOMER_SCHEMA AUTHORIZATION user;
SET search_path TO :CUSTOMER_SCHEMA, public;

So every following statement doesn't need to be prefixed with schema. At some point I receive the following error:

Caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 385 waits for AccessExclusiveLock on relation 17248 of database 16385; blocked by process 18854.
Process 18854 waits for AccessShareLock on relation 17016 of database 16385; blocked by process 385. Hint: See server log for query details.

Process 385 seems to be the last executed statement:

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);

relation 17248 is tariff_plan and 17016 is customers, both only in public schema (shared tables).

I cannot understand why altering a table in a customer schema bumps into a deadlock with these two tables. Any hints how to solve this problem?

Thanks.

--

Christian Castelli
skype:  christrack