Avoid deadlocks on alter table

From: Christian Castelli <voodoo81people(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Avoid deadlocks on alter table
Date: 2016-07-05 13:30:54
Message-ID: CAN7CK_yNvB9RwFjXSrD8b0V8Z+khsci8+5M+PCjAUu9S7mTRTA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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*

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kaixi Luo 2016-07-05 13:31:48 Re: How safe is pg_basebackup + continuous archiving?
Previous Message Aurelien Praga 2016-07-05 13:28:11 Materialized view not created with import