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?