Exclude constraint across many tables?

Question

Consider the following table:

todos:

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

to prevent 2 elevators on the same floor I can go with:

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

In this case 3 will conflict with 4.

However I do have a joining table:

occupations:

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

so (3) is done in room_id = 1 and (4) is done in room_id = 2 and they will not conflict.

1 & 2 do not have an entry in the joining table, so all rooms are occupied.

I understand that exclude will work only in the scope of the current table - how can I deal with it? Should I make redundant columns?

Adding room_id to todos is not an option because this is just a minimal example, and in the real life app I have more 0..N joins.

Answer 1

You can write an AFTER INSERT OR UPDATE trigger that checks for the condition and throws an error if it isn't met.

But beware that such triggers have a race condition — two concurrent data modifications cannot see each other's effects. So you either have to use the SERIALIZABLE isolation level or lock the affected rows in the trigger with SELECT ... FOR UPDATE.