Ограничение количества строк таблицы, соответствующих определенному условию, с использованием ограничения исключения Postgres?

Question

Если у меня есть схема базы данных Postgresql для такой таблицы шин (у пользователя много шин):

user_id integer
description text
size integer
color text
created_at timestamp

и я хочу ввести ограничение, которое гласит, что "у пользователя может быть только 4 шины".

Наивный способ реализовать это:

SELECT COUNT(*) FROM tires WHERE user_id='123'

, сравните результат с 4 и вставьте, если он меньше 4. Он подвержен гонке и поэтому является наивным подходом.

Я не хочу добавлять столбец подсчета. Как я могу это сделать (или могу это сделать) с помощью ограничения исключения? Если это невозможно с ограничениями исключения, каков канонический способ?

Answer 1

"Правильный" способ - использовать здесь замки. Во-первых, вам нужно заблокировать связанные строки. Во-вторых, вставьте новую запись, если у пользователя меньше 4 шин. Вот SQL:

begin;

-- lock rows
select count(*)
from tires
where user_id = 123
for update;

-- throw an error of there are more than 3

-- insert new row
insert into tires (user_id, description)
values (123, 'tire123');

commit;

Вы можете узнать больше здесь. Как выполнить условную вставку на основе количества строк?