Я на PostgreSQL 9.3. Я единственный, кто работает с базой данных, и мой код последовательно запускает запросы для модульных тестов.
В большинстве случаев следующий запрос UPDATE выполняется без проблем, но иногда он создает блокировки на сервере PostgreSQL. А то запрос вроде никогда не заканчивается, хотя обычно занимает всего 3 сек. Я должен уточнить, что запрос выполняется в контексте модульного теста, т.е. данные точно такие же, независимо от того, происходит ли блокировка или нет. Код — это единственный процесс, который обновляет данные.
Я знаю, что могут возникнуть проблемы с блокировкой PostgreSQL при использовании запроса на обновление для самообновляющейся таблицы. И больше всего, когда используется LEFT JOIN.
Я также знаю, что запрос LEFT JOIN можно заменить запросом NOT EXISTS для UPDATE, но в моем случае LEFT JOIN намного быстрее, потому что данных для обновления мало, а NOT EXISTS должен посетить почти все кандидаты строк.
Итак, мой вопрос: какие команды PostgreSQL (например, явная блокировка LOCK
для таблицы) или параметры (например, SELECT FOR UPDATE
) я должен использовать, чтобы обеспечить выполнение моего запроса без бесконечной блокировки.
Запрос:
-- for each places of scenario #1 update all owners that
-- are different from scenario #0
UPDATE t_territories AS upt
SET id_owner = diff.id_owner
FROM (
-- list of owners in the source that are different from target
SELECT trg.id_place, src.id_owner
FROM t_territories AS trg
LEFT JOIN t_territories AS src
ON (src.id_scenario = 0)
AND (src.id_place = trg.id_place)
WHERE (trg.id_scenario = 1)
AND (trg.id_owner IS DISTINCT FROM src.id_owner)
-- FOR UPDATE -- bug SQL : FOR UPDATE cannot be applied to the nullable side of an outer join
) AS diff
WHERE (upt.id_scenario = 1)
AND (upt.id_place = diff.id_place)
Структура таблицы:
CREATE TABLE t_territories
(
id_scenario integer NOT NULL,
id_place integer NOT NULL,
id_owner integer,
CONSTRAINT t_territories_pk PRIMARY KEY (id_scenario, id_place),
CONSTRAINT t_territories_fkey_owner FOREIGN KEY (id_owner)
REFERENCES t_owner (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT
)
pg_locks
из второго сеанса. Также проверьте, выполняется ли запрос, т. е. потребляет ли процесс процессорное время. - person Laurenz Albe   schedule 29.01.2018trg
-кортеж в подзапросе ссылается на точно такой же кортеж, что иupt
в целевом RTE) - person joop   schedule 29.01.2018upt.id_owner
в NULL, если не существует соответствующей строки сid_scenario=1
, было бы проще обновить IMO из скалярного (коррелированного) подзапроса. - person joop   schedule 29.01.2018idle
. Но это запросы SELECT и плюс есть неактивные. Вот информация о блокировках отpg_locks
и pgAdmin 4. Для меня не очень полезная. imgur.com/a/JBYha imgur.com/a/kUIeu - person Skrol29   schedule 29.01.2018granted = false
). Может быть, заявление просто занимает много времени. С другой стороны, возможно, вы смотрели наpg_locks
, когда ничего не было заблокировано. - person Laurenz Albe   schedule 29.01.2018