ruby-on-rails - 使用 Postgres hstore 的竞争条件

Question

我有一个表，其中有一个 hstore

db/schema.rb

create_table "requests", force: true do |t|
  t.hstore "parameters"
end

有些记录有字段 parameters["company_id"] 但不是全部。

我需要做的是确保只有一个 Request 对象是用给定的 parameters["company_id"] 创建的。可能有多次尝试同时保存记录 - 因此是竞争条件。

我正在整个表的 hstore 中寻找唯一的 company_id 值。

我发现我可以运行一个事务来锁定数据库并检查具有给定 parameters["company_id"] 的请求是否存在(如果不创建它)。如果 company_id 是 Request 模型上的一个简单字段，我可以这样做:

Request.transaction do
  if Request.find_by(company_id: *id* )
    log_duplication_attempt_and_quit 
  else
    create_new_record
    log_successful_creation
  end
end

很遗憾，它是 hstore，我无法更改它。使用 hstore 实现此目的的最佳方法是什么？

我正在寻找快速的东西，因为表格中有很多记录。纯 SQL 查询是可以的 - 不幸的是我没有足够的 SQL 背景来弄清楚我自己。可以将其编入索引以提高性能吗？

示例:

a = Request.new(parameters: {company_id: 567, name: "John"})
b = Request.new(parameters: {name: "Doesn't have company_id in the hstore"})
c = Request.new(parameters: {company_id: 567, name: "Galt"})

a.save // valid success
b.save // valid success even if company_id hasn't been provided
c.save // not valid Request with company_id 567 already in the table

Answer 1

即使使用普通列，您的想法也不会针对并发访问进行保存。两个事务可能都同时看到该值还不存在，并且都尝试插入。

显然，为此目的使用一个专用company_id列会更简洁，然后一个简单的 UNIQUE 约束就可以完成这项工作:

ALTER TABLE requests ADD CONSTRAINT requests_company_id_uni UNIQUE (company_id);

这样你就有了一个索引自动:

• Does a Postgres UNIQUE constraint imply an index?

您甚至可以将该列作为外键引用...

使用您拥有的设置，您仍然可以让它与functional UNIQUE index一起工作:

CREATE UNIQUE INDEX requests_parameters_company_id_uni
ON requests ((parameters->'company_id'));  -- all parentheses required

这两种变体都允许多个 NULL 值，通常允许没有 'company_id' 键的条目。你甚至可以把它变成 partial, functional UNIQUE index从索引中排除不相关的行(使索引更小):

CREATE UNIQUE INDEX requests_parameters_company_id_uni
ON requests ((parameters->'company_id'))
WHERE (parameters->'company_id') IS NOT NULL;

仅当您有多个没有 company_id 的公司时才有用。

相关:

• Composite PRIMARY KEY enforces NOT NULL constraints on involved columns

SQL Fiddle.

无论哪种方式，Postgres 现在都会处理剩下的事情。任何事务试图插入一个具有已经存在的 company_id 的行(无论哪种方式)都会引发唯一违规的异常并回滚整个事务。始终保证唯一性。

如果您想记录被拒绝为重复项的条目，您可以将 INSERT 封装在服务器端函数中，捕获唯一违规并改为写入日志表:

您会在 SO with this search 上找到示例.