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 Castelliskype: christrack*
