Блог

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

Упрощенная версия моей таблицы выглядит следующим образом: три поля — int, bool и timestamp соответственно:

  ---- ------- -------------------------- 
| id | done  |      done_timestamp      |
 ---- ------- -------------------------- 
|  1 | false | null                     |
|  2 | false | null                     |
|  3 | true  | 2020-11-24T21:23:04.008Z |
 ---- ------- -------------------------- 
 

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

 UPDATE operations AS o
SET done = TRUE,
done_timestamp = o2.done_timestamp
FROM (VALUES (1, NOW()), (2, NOW()), (3, NOW())) AS o2(id, done_timestamp)
WHERE o2.id = o.id AND NOT o.done;
 

Пока все хорошо. Я обновляю 2 из трех строк, и я не обновляю 3-ю, потому что что-то уже это сделало, и я не хочу обновлять там временную метку. Вот что я получаю в своей таблице:

  ---- ------ -------------------------- 
| id | done |      done_timestamp      |
 ---- ------ -------------------------- 
|  1 | true | 2020-11-24T21:38:29.871Z |
|  2 | true | 2020-11-24T21:38:29.871Z |
|  3 | true | 2020-11-24T21:23:04.008Z |
 ---- ------ -------------------------- 
 

Последняя часть головоломки заключается в том, что я хочу вернуть все обновленные идентификаторы, чтобы я мог выполнить некоторую проверку ошибок. Моей первой мыслью было добавить RETURNING оператор, но, делая это, я возвращаю только идентификаторы, обновленные этим запросом, а не все строки, которые выполнены. Запрос выглядит следующим образом:

 UPDATE operations AS o
SET done = TRUE,
done_timestamp = o2.done_timestamp
FROM (VALUES (1, NOW()), (2, NOW()), (3, NOW())) AS o2(id, done_timestamp)
WHERE o2.id = o.id AND NOT o.done
RETURNING o.id;
 

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

Заранее спасибо, F

Насколько я могу судить, вам нужно будет обмануть ваш сервер, чтобы сделать это. Заставьте его думать, что обновление было, когда его не было, переместив логическую проверку в оператор CASE; если done = TRUE уже, установите для него ту же временную метку, которая уже существовала.

Примечание: это приводит к ненужным ОБНОВЛЕНИЯМ таблицы и приведет к замедлению запроса, но не похоже, что Postgres поддерживает какое-либо СЛИЯНИЕ, которое вернет вам идентификаторы, как это было бы в некоторых других БД.

 UPDATE operations AS o
SET done_ts = case when done = false then o2.done_ts else o.done_ts end,
    done = TRUE
FROM (VALUES (1, NOW()), (2, NOW()), (3, NOW())) AS o2(id, done_ts)
WHERE o2.id = o.id 
RETURNING o.id;
 

Не проверено на ваших данных, но возможное решение с использованием [CTE] s(https://www.postgresql.org/docs/current/sql-select.html#SQL-WITH ):

 WITH val(id, done_timestamp) AS 
        (VALUES (1, NOW()), (2, NOW()), (3, NOW())) , 
updated AS 
    (UPDATE operations AS o
        SET done = TRUE, done_timestamp = val.done_timestamp
    WHERE val.id = o.id AND NOT o.done) 
SELECT id FROM val;