Блог

Предположим, у меня есть две таблицы, подобные этой:

 CREATE TABLE IF NOT EXISTS assets (
  id uuid DEFAULT uuid_generate_v4() NOT NULL PRIMARY KEY,
  created timestamp DEFAULT now() NOT NULL,
  customer uuid NOT NULL
);

CREATE TABLE IF NOT EXISTS asset_attributes (
  id uuid DEFAULT uuid_generate_v4() NOT NULL PRIMARY KEY,
  created timestamp DEFAULT now() NOT NULL,
  asset uuid NOT NULL,
  attributes jsonb NOT NULL
);
  

У меня есть assets которые принадлежат клиентам; assets могут быть attributes , которые могут меняться со временем. Мне нужно иметь возможность ссылаться на старые версии attributes so вместо UPDATE столбца в assets таблице, я вместо INSERT новой строки в asset_attributes таблице. Это иногда называют «неразрушающими обновлениями».

 INSERT INTO asset_attributes 
  (
    asset,
    attributes
  ) 
VALUES 
  (
    '92675e0c-7473-435f-b48e-8de1feb2164b',
    '{ "foo": "bar" }'::jsonb
  ) 
RETURNING id
  

Я могу получить последнее asset_attributes значение для данного asset использования GROUP BY , общего порядка и т.д.

Мой сервер работает, извлекая последнюю asset_attributes для данного asset файла, выполняя некоторую обработку, а затем вводя новую строку.

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

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

Псевдокод:

 process_attributes(asset_id): 
  let latest_attributes = fetch_latest_attributes(asset_id)

  let next_attributes = do_work(latest_attributes)

  let did_update_succeed = update_attributes_from_previous(asset_id, latest_attributes, next_attributes)

  if not did_update_succeed
  then
    // Try again from the beginning
    process_attributes(asset_id)
  

Я не уверен, какой лучший способ написать SQL для update_attributes_from_previous .

Как я могу достичь этого с помощью Postgres?

Моя попытка решения:

 INSERT INTO asset_attributes (asset, attributes)
SELECT
  '92675e0c-7473-435f-b48e-8de1feb2164b' AS asset,
  '{ "foo": "bar" }'::jsonb AS attributes
WHERE 
  NOT EXISTS
    (
      SELECT id
      FROM asset_attributes
      WHERE asset = '92675e0c-7473-435f-b48e-8de1feb2164b'
    )
  OR 
  EXISTS 
    (
      SELECT id
      FROM
        (
          SELECT id
          FROM asset_attributes
          WHERE asset = '92675e0c-7473-435f-b48e-8de1feb2164b'
          ORDER BY created DESC, id
          LIMIT 1
        ) AS latest
      WHERE 
        id = 'fc114de7-93a2-44dc-be5c-92999caa0eb0' -- ID of latest asset_attributes at start of processing
    )
RETURNING id, created, asset, attributes ;
  

Я бы убил двух зайцев одним выстрелом, добавив current_assets_attributes_id в assets таблицу.

Затем я бы использовал assets строку в качестве блокировки следующим образом:

 select a.id, aa.attributes 
  from assets a
       join asset_attributes aa 
         on aa.id = a.current_assets_attributes_id
 where a.id = xxx
for update of assets;
  

Обработка псевдокода:

1. Измените извлеченный attributes
2. Вставить новую asset_attributes запись returning id
3. update assets set current_assets_attributes_id = yyy where id = xxx
4. commit (или rollback ), тем самым снимая блокировку

Другие процессы, пытающиеся изменить то же самое, asset будут заблокированы на select. . . for update .

Это также будет работать без добавления current_assets_attributes_id в assets таблицу, пока вы используете select. . . where id = xxx. . . for update of assets , поскольку блокировке все равно, выполняете ли вы на самом деле update , и не позволит любому другому процессу заблокировать эту строку, пока транзакция, владеющая блокировкой, не завершится.

Простым способом для этого было бы включить как идентификатор самой последней записи, так и условие для самой последней записи в условие WHERE для ВСТАВКИ. Это можно было бы сделать в предложении EXISTS( ), которое принимает значение TRUE тогда и только тогда, когда идентификатор по-прежнему является самой последней записью.

Чтобы эта схема работала, после ВСТАВКИ должна быть немедленная ФИКСАЦИЯ.