как я могу выполнить условную вставку в postgres, когда могут быть одновременные вставки, которые могут создать конфликт?

Я пытаюсь написать экспериментальную структуру, в которой пользователь может планировать некоторые эксперименты на основе идентификаторов местоположения и времени.

моя схема таблицы выглядит так:

TABLE experiment (
    id INT NOT NULL PRIMARY KEY,
    name varchar(20) NOT NULL,
    locationIds varchar[] NOT NULL,
    timeStart timestamp NOT NULL,
    timeEnd timestamp NOT NULL,
    createdAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)

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

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

Немного подойдя, я попытался подумать:

Подход:

ПОДХОД-1

1. Имейте столбец enable, который сообщает, действительна ли определенная запись ИЛИ нет.

2. Я вставляю запись расписания эксперимента с параметром enable=FALSE.

3. Затем я проверяю, есть ли какая-либо другая запись, которая включена и перекрывается с текущей вставкой.

4. ЕСЛИ есть такая запись, я ничего не делаю, и этот эксперимент не запланирован. Иначе я обновляю запись до enable=TRUE.

Проблема: если есть параллельная конфликтующая вставка, то оба получают enable=TRUE, когда оба очищают шаг 3.

Я подумал, что если я позволю уровню изоляции транзакций быть прочитанным-незафиксированным, тогда я также не смогу отличить те, которые находятся в процессе, и те, которые уже enable=TRUE

Затем я подумал: если я отмечу enable как перечисление [IN_PROGRESS, ENABLED, DISABLED], то подход будет выглядеть так.

ПОДХОД-2

1. Имейте столбец enable, который сообщает, является ли определенная запись [IN_PROGRESS, ENABLED, DISABLED]

2. Я вставляю запись расписания эксперимента с помощью enable=IN_PROGRESS.

3. Затем я проверяю, есть ли какая-либо другая запись, которая enable=ENABLED ИЛИ enable=IN_PROGRESS и перекрывается с текущей вставкой.

4. ЕСЛИ есть такая запись, я обновляю enable=DISABLED, и этот эксперимент не запланирован. Иначе я обновляю запись на enable=ENABLED.

Проблема: если есть параллельная конфликтующая вставка, то оба получают enable=DISABLED, когда оба очищают шаг 3 и получают такую ​​перекрывающуюся запись.

Если уровень изоляции транзакций READ-COMMITTED, это будет работать, только если каждый шаг является транзакцией, а не весь процесс как одна транзакция. Если уровень изоляции транзакции READ-UNCOMMITTED, то это можно рассматривать как одну транзакцию, а состояние DISABLED также можно рассматривать как шаг ROLLBACK.

ПОДХОД-3

Используя решение на основе триггера, как я использую POSTGRES, я могу добавить триггер для каждой операции вставки, опубликовать вставку, где я проверяю такую ​​перекрывающуюся запись, если ее нет, тогда я обновляю строку, чтобы иметь enable=TRUE

CREATE OR REPLACE FUNCTION enable_if_unique() 
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
    UPDATE experiment
    SET NEW.enable=true
    WHERE (SELECT count(1)
           FROM experiment
           WHERE enable= true AND location_Ids && OLD.location_ids AND (OLD.timeStart, OLD.timeEnd) OVERLAPS (timeStart, timeEnd)
        ) = 0;
    RETURN NEW;
    END IF;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER enable_if_unique_trigger BEFORE INSERT ON experiment FOR EACH ROW EXECUTE PROCEDURE enable_if_unique();

INSERT INTO experiment(id, name, locationIds, timeStart, timeEnd) 
SELECT 1, 'exp-1', ARRAY[123,234,345], '2020-03-13 12:00:00' 
WHERE (
       SELECT count(1) 
       FROM EXPERIMENT 
       WHERE enable= true 
             AND 
             location_Ids && OLD.location_ids 
             AND 
             (OLD.timeStart, OLD.timeEnd) OVERLAPS (timeStart, timeEnd)
      ) = 0;