CODEWITHSUNDEEP
ruby-on-railspostgresqlindexingunique-index
yorbro
yorbro

Reputation: 1137

Uniqueness validation in database when validation has a condition

Using uniqueness validations in Rails is not safe when there are multiple processes unless the constraint is also enforced on the database (in my case a PostgreSQL database, so see this blog post).

In my case, the uniqueness validation is conditional: it should only be enforced if another attribute in the model becomes true. So I have

class Model < ActiveRecord::Base
  validates_uniqueness_of   :text, if: :is_published?

  def is_published?
    self.is_published
  end
end

So the model has two attributes: is_published (a boolean) and text (a text attribute). text should be unique across all models of type Model if is_published is true.

Using a unique index (as suggested in the linked blog post) is too constraining because it would enforce the constraint regardless of the value of is_published.

Is anyone aware of a "conditional" index on a PostgreSQL database? Or another way to fix this?

Upvotes: 1

Views: 2631

Answers (2)

cryptogopher
cryptogopher

Reputation: 154

I think that - given speed is not your main concern - you can achieve proper uniqueness validation without creating additional db indexes. Goal can be achieved at the application level. This is especially valuable if you want conditional uniqueness, as some dbs (e.g. versions of MySQL < 8) does not support partial indexes (or so called filtered indexes).

My solution is based on following assumption:

  • uniqueness check (validator) is run by Rails in the same transaction as save/destroy action that relies on it.

This assumption seems to be true: https://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html

Both #save and #destroy come wrapped in a transaction that ensures that whatever you do in validations or callbacks will happen under its protected cover.

transaction calls can be nested. By default, this makes all database statements in the nested transaction block become part of the parent transaction.

Having that you can use pessimistic locking (https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html) to exclusively lock records that you want to evaluate for uniqueness in validator. That will prevent another, simultaneously running validator - and actually anything that happens after it - from executing until lock is released at the end of transaction. That ensures atomicity of validate-save pair and proper uniqueness enforcement.

In your code it would look like that:

class Model < ActiveRecord::Base
  validates :text, uniqueness: {
    conditions: ->{ lock.where(is_published: true) }
  }
end

The only downside I can see is having db records locked for the whole validate-save process. That won't work well under heavy load, but then many applications don't work under such conditions anyway.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

Yes, use a partial UNIQUE index.

CREATE UNIQUE INDEX tbl_txt_is_published_idx ON tbl (text) WHERE is_published;

Example:
How to add a conditional unique index on PostgreSQL

Upvotes: 4

Related Questions