Исключить ограничение для многих таблиц?

Question

Рассмотрим следующую таблицу:

todos:

 id | floor_start | floor_end
----+-------------+-------------
 1  | 10          | 20
 2  | 20          | 30
 3  | 30          | 40
 4  | 35          | 45

чтобы предотвратить 2 лифта на одном этаже, я могу пойти с:

EXCLUDE USING gist(int4range(start,end) with &&)

В этом случае 3 будет конфликтовать с 4.

Однако у меня есть таблица присоединения:

occupations:

 todo_id | room_id
---------+----------
 3       | 1
 4       | 2

так (3) сделано в room_id = 1 и (4) сделано в room_id = 2 и они не будут конфликтовать.

1 и 2 не имеют записи в соединительном столе, поэтому все комнаты заняты.

Я это понимаю exclude будет работать только в рамках текущей таблицы - как с этим справиться? Должен ли я сделать лишние столбцы?

Добавление room_id в todos это не вариант, потому что это лишь минимальный пример, и в реальном приложении у меня больше 0..N присоединений.

Answer 1

Вы можете написать AFTER INSERT OR UPDATE триггер, который проверяет условие и выдает ошибку, если оно не выполнено.

Но имейте в виду, что такие триггеры имеют состояние гонки - две одновременные модификации данных не могут видеть эффекты друг друга. Таким образом, вы должны использовать SERIALIZABLE уровень изоляции или заблокировать соответствующие строки в триггере с помощью SELECT ... FOR UPDATE,