Триггер для обеспечения того, чтобы совокупная сумма была меньше значения в другой таблице

Единственное решение, о котором я могу думать, это денормализация.

Добавьте столбец integertotal_returns в order_item, который изменяется с помощью триггера на return_item всякий раз, когда строки добавляются или удаляются или изменяется quantity.

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

Некоторый пример кода:

BEGIN;

/* for consistency */
ALTER TABLE order_item
   ALTER quantity SET NOT NULL
   ALTER quantity SET DEFAULT 0;

ALTER TABLE order_item
   ADD total_returns bigint DEFAULT 0 NOT NULL;

ALTER TABLE order_item
   ADD CONSTRAINT not_too_many_returns
      CHECK (total_returns <= quantity);

/* trigger function */
CREATE FUNCTION requrn_order_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
      UPDATE order_item
      SET total_returns = total_returns + NEW.quantity;
      WHERE id = NEW.order_item_id;
   END IF;

   IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
      UPDATE order_item
      SET total_returns = total_returns - OLD.quantity;
      WHERE id = OLD.order_item_id;
   END IF;

   RETURN NULL;
END;$$;

CREATE TRIGGER requrn_order_trig
   AFTER INSERT OR UPDATE OR DELETE ON return_item
   FOR EACH ROW EXECUTE PROCEDURE requrn_order_trig();

UPDATE order_item AS oi
SET total_returns = (SELECT sum(quantity)
                     FROM return_item AS r
                     WHERE r.order_item_id = oi.id);

COMMIT;

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

Вместо этого вы можете реализовать логику в запросе DML. Рассмотрим следующий запрос для регистрации нового возвращенного элемента:

insert into return_item (order_item_id, quantity)
select v.*
from (values ($1, $2)) as v(order_item_id, quantity)
inner join order_items oi on oi.id = v.order_item_id
where oi.quantity >= v.quantity + (
    select coalesce(sum(ri.quantity), 0) from return_item ri where ri.order_item_id = v.order_item_id
)

Входные значения задаются как $1 и $2. Запрос выводит соответствующую строку в order_items и проверяет, превышает ли общее возвращенное количество этого товара заказанное количество.

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

Из вашего приложения вы можете проверить, затронул ли запрос какую-либо строку. Если это не так, то вы знаете, что возврат был отклонен.

Вы можете поместить запрос в хранимую процедуру, если собираетесь использовать ее на регулярной основе.

Вы просите конкретно триггерное решение. Кстати, вы можете добиться того же и с помощью простого SQL, если вы можете убедиться, что все клиенты используют необходимые операторы. Связанный пример:

• Удалить родителя, если на него не ссылается какой-либо другой дочерний элемент

Триггерное решение

Вы упомянули, что возможен одновременный доступ для записи. Это делает его более сложным. Например, две транзакции могут попытаться вернуть элемент из одного и того же order_item одновременно. Оба проверяют и обнаруживают, что можно вернуть еще один элемент, и делают это, тем самым превышая количество order_item.quantity на 1. Предостережение классического параллелизма.

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

В качестве альтернативы снимите стратегические блокировки строк на уровне изоляции по умолчанию READ COMMITTED. Вот базовая реализация:

Триггерная функция:

CREATE FUNCTION trg_return_item_insup_bef()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   _ordered_items int;
   _remaining_items int;
BEGIN
   SELECT quantity
   FROM   order_item
   WHERE  id = NEW.order_item_id
   FOR    NO KEY UPDATE                -- lock the parent row first ... (!!!)
   INTO   _ordered_items;              --  ... while fetching quantity

   SELECT _ordered_items - COALESCE(sum(quantity), 0)
   FROM   return_item
   WHERE  order_item_id = NEW.order_item_id
   INTO   _remaining_items;

   IF NEW.quantity > _remaining_items THEN
      RAISE EXCEPTION 'Tried to return % items, but only % of % are left.'
                     , NEW.quantity, _remaining_items, _ordered_items;
   END IF;
   
   RETURN NEW;
END
$func$;

Курок:

CREATE TRIGGER insup_bef
BEFORE INSERT OR UPDATE ON return_item
FOR EACH ROW
EXECUTE PROCEDURE trg_return_item_insup_bef();

db<>рабочий пример здесь

Любая попытка вернуть элементы сначала блокирует родительскую строку в order_item. Конкурирующие транзакции должны дождаться, пока эта будет зафиксирована, и тогда они увидят новые зафиксированные строки. Это устраняет состояние гонки. FOR NO KEY UPDATE правильная сила блокировки. Ни в слабую, ни в слишком сильную.

Запись в order_item также может повлиять на общее количество предметов. Но они также снимают блокировку записи (неявно) и вынуждены стоять в очереди таким же образом. Но если возможны более поздние обновления order_item.quantity, вам придется добавить туда аналогичные проверки в триггере (на случай, если он будет понижен).

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

Пример установки может быть оптимизирован. "заказ" - зарезервированное слово. Таблица return в примере бесполезна, как и return_item.return_id. ПК отсутствует в return_item. order_item.quantity должно быть NOT NULL CHECK (quantity > 0). COALESCE в функции триггера является избыточным при правильной реализации. Но это второстепенные заметки.