Race condition using Postgres hstore

Question

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

score 1 · Accepted Answer · edited May 23 '17 at 10:27

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.

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.

could you please elaborate bit more on creating a UNIQUE constrain? As I have mention my SQL background is very weak:( — Kocur4d, Sep 23 '15 at 14:17
@Kocur4d: I think I understand your requirements better now: you want unique `company_id` keys inside the `hstore` column across the table. — Erwin Brandstetter, Sep 23 '15 at 14:22
I want uniq company_ids values inside the hstore across the table — Kocur4d, Sep 23 '15 at 14:26
@Kocur4d: You get a unique violation for a duplication attempt and can log that if you want. (It will be in the database logs either way with standard logging configuration.) — Erwin Brandstetter, Sep 23 '15 at 14:30
one last question - some records don't have the 'company_id' in the hstore but are still valid. Will this approach work with them? aka will save them as being valid records — Kocur4d, Sep 23 '15 at 14:36
Thank you mate - this is more then I could ask for! Thank you! — Kocur4d, Sep 23 '15 at 14:55

Race condition using Postgres hstore

1 Answers1

Linked