ruby-on-rails - 尝试更新，如果更新失败则删除(RubyOnRails-PostgreSQL)

Question

我正在尝试更新记录，但是如果由于唯一验证而失败，然后我将删除该记录，因为预期的记录已经存在。

def update_dependent_model(records, foreign_key, foreign_key_id)
  records.each do |record|
    begin
      ActiveRecord::Base.transaction do
        record.update_column(foreign_key.to_sym, foreign_key_id)
      end
    rescue ActiveRecord::RecordNotUnique, PG::UniqueViolation => e
      ActiveRecord::Base.connection.execute 'ROLLBACK'
      record.delete
    end
  end
end

我不确定这段代码有什么问题。一些要删除或不删除的记录。日志也无济于事。没有显示错误。

此外，关于日志的奇怪之处在于:

  SQL (2.4ms)  UPDATE "records" SET "foriegn_key_id" = 220 WHERE "records"."id" = $1  [["id", 124080]]
  SQL (0.6ms)  UPDATE "records" SET "foriegn_key_id" = 220 WHERE "records"."id" = $1  [["id", 153032]]
  SQL (0.2ms)  UPDATE "records" SET "foriegn_key_id" = 220 WHERE "records"."id" = $1  [["id", 124099]]
  SQL (0.4ms)  UPDATE "records" SET "foriegn_key_id" = 220 WHERE "records"."id" = $1  [["id", 176549]]
  SQL (0.5ms)  UPDATE "records" SET "foriegn_key_id" = 220 WHERE "records"."id" = $1  [["id", 162725]]
  SQL (0.8ms)  UPDATE "records" SET "foriegn_key_id" = 220 WHERE "records"."id" = $1  [["id", 124109]]
   (0.1ms)  ROLLBACK
  SQL (0.3ms)  DELETE FROM "records" WHERE "records"."id" = $1  [["id", 124109]]

实际上，id 为 124080 的记录已经更新，但奇怪的是在数据库中这条记录仍然存在。

Answer 1

您可以将逻辑封装在服务器端函数中:

CREATE OR REPLACE FUNCTION f_update_or_delete(_id int, _value int) AS
$func$
BEGIN
   UPDATE records
   SET    foriegn_key_id = _value
   WHERE  id = _id;

EXCEPTION WHEN unique_violation THEN   -- error code 23505
   DELETE FROM records WHERE id _id;

END
$func$ LANGUAGE plpgsql;

但是从一开始就避免引发异常的成本更低:

WITH cte AS (
   UPDATE records
   SET    foreign_key_id = _value
   WHERE  id = _id
   AND    NOT EXISTS (
      SELECT FROM records
      WHERE  id = _id
      AND    foreign_key_id = _value
      )
   RETURNING 1
   )
DELETE FROM records
WHERE  id = _id
AND    NOT EXISTS (SELECT FROM cte);

这些都不是并发安全的。

EXISTS 子查询中的查找与实际的 UPDATE - 或以下 DELETE 之间存在竞争条件。您的事务未提交的时间越长，您最终错误删除行的可能性就越大 - 因为阻塞行已同时被并发事务删除，或者导致唯一违规的并发事务被回滚。

因为您不能锁定 Postgres 中不存在的行，完全安全的方法是使用 SERIALIZABLE transaction isolation (并在序列化失败时重试)或锁定整个表或使用 advisory locks以一致的方式。