Блог

Я использую базу данных postgres, и моя проблема включает в себя две таблицы, их упрощенные версии приведены ниже.

 CREATE TABLE events(
    id SERIAL PRIMARY KEY NOT NULL,
    max_persons INTEGER NOT NULL
);
  

и

 CREATE TABLE requests(
    id SERIAL PRIMARY KEY NOT NULL,
    confirmed BOOLEAN NOT NULL,
    creation_time TIMESTAMP DEFAULT NOW(),
    event_id INTEGER NOT NULL /*foreign key*/
);
  

Есть n события, и в каждом событии может быть до events.max_persons участников. Новые запросы должны быть подтверждены и действительны до 30 минут. По истечении этого периода запросы будут игнорироваться, если они не были подтверждены.

Теперь то, что я хочу сделать, это вставить новое request , только когда сумма всех подтвержденных запросов и всех запросов, которые все еще действительны, но не подтверждены, меньше events.max_persons .

У меня уже есть запрос для выбора одного события. Вот его упрощенная версия, просто чтобы дать вам представление о том, как это должно работать

 SELECT 
    e.id,
    SUM(CASE WHEN r.confirmed = 1 THEN 1 ELSE 0 END) AS number_confirmed
    SUM(CASE WHEN r.creation_time > (CURRENT_TIMESTAMP - INTERVAL '30 MINUTE') AND r.confirmed = 0 THEN 1 ELSE 0 END) AS number_reserved,
    e.max_persons
FROM events e, requests r
WHERE l.id = ? 
    AND r.event_id = e.id             
    AND (r.confirmed = 1 OR r.creation_time > (CURRENT_TIMESTAMP - INTERVAL '30 MINUTE'))
GROUP BY e.id, e.max_persons              
HAVING SUM(CASE WHEN r.confirmed = 1 OR (r.creation_time > (CURRENT_TIMESTAMP - INTERVAL '30 MINUTE')) THEN 1 ELSE 0 END) < e.max_persons";
  

Возможно ли достичь этого с помощью одной команды INSERT?

Вы могли бы сделать это следующим образом:

 INSERT INTO requests
   SELECT * FROM (VALUES (...)) row
      WHERE ...
  

и напишите WHERE предложение, которое имеет значение true только в том случае, если выполняется ваше условие.

Но с этим подходом есть фундаментальная проблема, а именно то, что он подвержен условию гонки.

Если два таких оператора выполняются одновременно, оба могут считать условие удовлетворенным, но когда каждый из них добавил свою строку и зафиксировал, условие может быть нарушено. Это потому, что ни один из операторов не может видеть эффекты другого до их фиксации.

Для этого есть два решения:

• Заблокируйте таблицу перед тестированием и вставкой. Это просто, но очень плохо для параллелизма.

• Используйте SERIALIZABLE транзакции повсюду. Тогда это должно вызвать ошибку сериализации, и один из операторов должен быть повторен и обнаружит, что условие нарушено, когда это произойдет.