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*
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 |