В ожидании 9.1 - Снижаем уровни блокировок для ALTER TABLE

Перевод Waiting for 9.1 – Reduced lock levels for ALTER TABLE с select * from depesz;

28 июля Simon Riggs применил патч:

Снижает уровни блокировок CREATE TRIGGER и некоторых действий ALTER TABLE,
CREATE RULE. Убирает прописанные на прямую в коде режимы блокировок, используемые во
множестве команд изменения DDL, позволяя более легко менять уровни блокировок в
будущем. Реализован начальный анализ DDL подкомманд, так что многие уровни блокировок
теперь будут ShareUpdateExclusiveLock или ShareRowExclusiveLock, позволяя конкретным
коммандам не блокировать чтение/запись. Это первое изменение из числа запланированных
в этом направлении; будет нужна дополнительная документация когда весь проект
завершится.

Во первых - это только начало. Конечная цель - сделать все (большинство?) выражения ALTER TABLE менее навязчивыми.

Ранее все ALTER TABLE блокировали всякий доступ к таблице:

Сессия 1: # begin;
Сессия 2: # begin;
Сессия 1: *# create trigger q after insert on table_name
for each row execute procedure test();
...тут засыпает на 10 минут...
Сессия 2: *# select * from table_name;
...а тут ждёт 10 минут...

Это было потому что все ALTER TABLE и CREATE TRIGGER требовали AccessExclusiveLock на таблицу:

*# create trigger q after insert on table_name for each row execute procedure test();
CREATE TRIGGER

*# SELECT * FROM pg_locks where pid = pg_backend_pid();
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted
---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+------+---------------------+---------
virtualxid | [null] | [null] | [null] | [null] | 13/674866 | [null] | [null] | [null] | [null] | 13/674866 | 9492 | ExclusiveLock | t
relation | 48535 | 69187 | [null] | [null] | [null] | [null] | [null] | [null] | [null] | 13/674866 | 9492 | AccessExclusiveLock | t
transactionid | [null] | [null] | [null] | [null] | [null] | 632704 | [null] | [null] | [null] | 13/674866 | 9492 | ExclusiveLock | t
relation | 48535 | 10969 | [null] | [null] | [null] | [null] | [null] | [null] | [null] | 13/674866 | 9492 | AccessShareLock | t
(4 rows)

И как мы знаем (или можем найти в документации) данный тип блокировки конфликтует со всем.

В 9.1 полученная блокировка будет другой:

*$ SELECT * FROM pg_locks where pid = pg_backend_pid();
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted
---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+-------+-----------------------+---------
transactionid | [null] | [null] | [null] | [null] | [null] | 752 | [null] | [null] | [null] | 2/386 | 27199 | ExclusiveLock | t
relation | 16398 | 16419 | [null] | [null] | [null] | [null] | [null] | [null] | [null] | 2/386 | 27199 | ShareRowExclusiveLock | t
virtualxid | [null] | [null] | [null] | [null] | 2/386 | [null] | [null] | [null] | [null] | 2/386 | 27199 | ExclusiveLock | t
relation | 16398 | 10987 | [null] | [null] | [null] | [null] | [null] | [null] | [null] | 2/386 | 27199 | AccessShareLock | t
(4 rows)

(depesz@[local]:5900) 15:12:55 [depesz]
*$ select 16419::regclass, 10987::regclass;
regclass | regclass
------------+----------
table_name | pg_locks
(1 row)

В этот раз мы видим, что CREATE TRIGGER получает только ShareRowExclusiveLock, что разрешает делать SELECT запросы. Операции модификации (UPDATE, INSERT, DELETE) и другие DDL запросы к этой таблице всё ещё не будут разрешены. Но возможность хотя бы делать операции чтения это уже большой выигрыш.

Я не нашел окончательный список всех вариантов ALTER TABLE, которые получают такой уровень блокировки, но это и не так важно - всё ещё поменяется, и новые варианты будут добавляться. Сейчас я знаю что добавление триггеров и установка значения по умолчанию используют ShareUpdateExclusiveLock вместо AccessExclusiveLock. Что будет следующим - посмотрим.