竞争条件使用Postgres hstore [英] Race condition using Postgres hstore

问题描述

我有一张表，其中有许多其他字段，一个 hstore

db / schema.rb

  create_tablerequests，force：true do | t | 
 t.hstore参数
 end 
  

有些记录有字段参数[company_id] 但不是全部。

我需要做的是确保使用给定的参数[company_id] 创建仅一个请求对象。可能会有多次尝试同时保存记录 - 因此是竞争条件。

我正在寻找 hstore company_id c / c>

我知道我可以运行一个事务来锁定数据库，并检查是否有给定的请求 parameters [company_id] 如果不创建它，则存在结束。如果 company_id 将是请求模型的简单字段，我可以这样做：

  Request.transaction do 
如果Request.find_by（company_id：* id *）
 log_duplication_attempt_and_quit 
 else 
 create_new_record 
 log_successful_creation 
 end 
 end 
  

不幸的是， code> hstore ，我无法更改它。在 hstore 中实现这一点的最佳方式是什么？

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

示例：

  a = Request.new（参数：{company_id：567，name：John}）
b = Request.new（parameters：{name：在hstore中没有company_id}} 
c =请求.new（参数：{company_id：567，name：Galt}）
 
 a.save // valid success 
 b.save //即使company_id没有提供
 c.save //无效请求与company_id 567已经在表
  

解决方案

即使使用普通的列，您的想法也不会保存在并发访问中。两个交易可能都可能 看到该值不在同一时间并且都尝试插入。

显然，为了这个目的，要有一个专用 company_id 列会更为干净，那么一个简单的UNIQUE约束就可以完成这个工作：

  ALTER TABLE请求ADD CONSTRAINT requests_company_id_uni UNIQUE（company_id）; 
  

这样您就可以自动使用索引 ：

• Postgres UNIQUE约束是否意味着索引？

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

使用设置 ，您仍然可以使用 功能性唯一索引 ：

  CREATE UNIQUE INDEX requests_parameters_company_id_uni 
 ON请求（（parameters->'company_id'））; - 所有括号都需要
  

两个变体允许多个NULL值，没有通常允许'company_id'键。您甚至可以使其成为 部分功能独特指数 从索引中排除不相关的行（使索引更小）：

  CREATE UNIQUE INDEX requests_parameters_company_id_uni 
 ON请求（（parameters->'company_id'））
 WHERE（parameters->'company_id'）IS NOT NULL; 
  

仅在没有 company_id

相关：

• 复合PRIMARY KEY强制对所涉及的列执行NOT NULL约束

SQL Fiddle。

无论哪种方式，Postgres都可以处理其余的事情。任何尝试插入一行已经存在的行（一种或另一种）的行将会引发唯一违规的异常并回滚整个事务。唯一性始终保证。

如果要将被拒绝的条目记录为重复，可以将INSERT封装在服务器端功能中，从而捕获唯一的违规行为写一个日志表：

你可以找到关于SO的一些例子使用此搜索。

I have a table which has amongst many other fields one hstore

db/schema.rb

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

Some of the records have a field parameters["company_id"] but not all of them.

What I need to do is to make sure that only one Request object is created with a given parameters["company_id"]. There might be multiple attempts trying to save a record at the same time - thus the race condition.

I am looking for unique company_id values inside the hstore across the table.

I figure out that I could run a transaction to lock the database and check if the request with given parameters["company_id"] exist end if not create it. If company_id would be a simple field on a Request model I could do something like this:

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

Unfortunately it is hstore and I can't change it. What would be the best way to achieve this with hstore?

I am looking for something fast as there are a lot of records in a table. Pure SQL query is OK - unfortunately I don't have enough SQL background to figure it out my self. Can this be indexed for performance?

Example:

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

解决方案

Your idea would not be save against concurrent access, even with a plain column. Two transaction might both see that the value is not there yet at the same time and both try to insert.

Obviously it would be cleaner to have a dedicated company_id column for the purpose, then a plain UNIQUE constraint would do the job:

ALTER TABLE requests ADD CONSTRAINT requests_company_id_uni UNIQUE (company_id);

This way you have an index automatically:

• Does a Postgres UNIQUE constraint imply an index?

And you could even reference the column as foreign key ...

With the setup you have you can still make it work with a functional UNIQUE index:

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

Both variants allow multiple NULL values, entries without the 'company_id' key are generally allowed. You could even make it a partial, functional UNIQUE index to exclude irrelevant rows from the index (makes the index smaller):

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

Only useful if you have more than a few without company_id.

Related:

• Composite PRIMARY KEY enforces NOT NULL constraints on involved columns

SQL Fiddle.

Either way, Postgres now handles the rest. Any transaction trying to insert a row with a company_id that's already present (one way or the other) will raise an exception for the unique violation and roll back the whole transaction. Uniqueness is guaranteed at all times.

If you want to log entries that are rejected as duplicates you could encapsulate the INSERT in a server-side function, trap the unique violation and write to a log table instead:

You'll find examples on SO with this search.

这篇关于竞争条件使用Postgres hstore的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！