Typically it is price to check your framework options

Rails 6 launched upsert_all which was a terrific various to uncooked SQL for inserting or updating a number of information without delay. There have been gems offering this function for earlier variations of Rails like activerecord-import, it did a terrific job in Rails Occasion Retailer.

Inconvenience in Rails 6

There was one minor drawback, the timestamps columns: created_at and updated_at weren’t up to date robotically inflicting inserts to fail due to NOT NULL constraints within the database.

It needed to be achieved manually:

timestamp = Time.present

FancyModel.upsert_all([{ foo: :bar, created_at: timestamp, updated_at: timestamp }], unique_by: [:custom_unique_index])

It labored nice for brand new objects, however not essentially for the prevailing ones which had been up to date. We had discovered this out whereas investigating concern within the system. These information which we knew that had been up to date had equal created_at and updated_at.

We wished to repair this case, so we began with a check:

class FancyModelTest < ActiveSupport::TestCase
  def test_timestampz
    FancyModel.create!(foo: :bar)

    timestamp = Time.present
    FancyModel.upsert_all(
      [{ foo: :baz, created_at: timestamp, updated_at: timestamp }],
      unique_by: [:custom_unique_index],
    )

    fancy = FancyModel.find_by!(foo: :baz)

    assert(fancy.updated_at > fancy.created_at)
  finish
finish

It failed, clearly.

Rails 7 to the rescue

We had few concepts tips on how to repair this. The simplest answer was on the desk since we had been on Rails 7 already. They’ll deal with timestamps in your behalf until you disable it.

Dangerous code setting an identical timestamp for each columns was eliminated and ActiveRecord took care of timestamps dealing with once more. Sadly, the check was consistently crimson:

class FancyModelTest < ActiveSupport::TestCase
  def test_timestampz
    FancyModel.create!(foo: :bar)

    FancyModel.upsert_all([{ foo: :baz }], unique_by: [:custom_unique_index])

    fancy = FancyModel.find_by!(foo: :baz)

    assert fancy.updated_at > fancy.created_at
  finish
finish

Too quick for you?

What if it occurs so quick, that assertion gained’t even discover — we thought.

Put a sleep(1) on it, make it cross:

class FancyModelTest < ActiveSupport::TestCase
  def test_timestampz
    FancyModel.create!(foo: :bar)

    sleep(1)

    FancyModel.upsert_all([{ foo: :baz }], unique_by: [:custom_unique_index])

    fancy = FancyModel.find_by!(foo: :baz)

    assert(fancy.updated_at > fancy.created_at)
  finish
finish

Nope, not gonna occur.

What about time journey, Marty?

Let’s create a file up to now, for certain this may work:

class FancyModelTest < ActiveSupport::TestCase
  def test_timestampz
    travel_to Time.zone.native(1985, 10, 26, 1, 24) do
      FancyModel.create!(foo: :bar)
    finish

    FancyModel.upsert_all([{ foo: :baz }], unique_by: [:custom_unique_index])

    fancy = FancyModel.find_by!(foo: :baz)

    assert(fancy.updated_at > fancy.created_at)
  finish
finish

Pink.

Scratching head, dropping religion in personal expertise second seems.

Transactional checks

After digging all through the Rails code, we had instinct that updated_at not being set to a special worth might need one thing in frequent with the truth that checks are wrapped in a database transaction. Transaction is rolled again on the finish of the check case to make each different check impartial from one another

We created a separate instance not utilizing transactions to show our speculation:

class FancyModelTest < ActiveSupport::TestCase
  self.use_transactional_tests = false

  def test_timestampz
    FancyModel.create!(foo: :bar)

    FancyModel.upsert_all([{ foo: :baz }], unique_by: [:custom_unique_index])

    fancy = FancyModel.find_by!(foo: :baz)

    assert(fancy.updated_at > fancy.created_at)
  finish
finish

Inexperienced.

We all know the reply

It turned out that PostgreSQL CURRENT_TIMESTAMP returns time at the beginning of the transaction (in our case the check–wrapping one). There’s no likelihood that created_at and updated_at will differ from one another after working upsert_all throughout the check. As PostgreSQL docs state:

Since these features return the beginning time of the present transaction, their values don’t change throughout the transaction. That is thought-about a function: the intent is to permit a single transaction to have a constant notion of the „present” time, in order that a number of modifications throughout the identical transaction bear the identical time stamp.

NOW() in MySQL does the identical.

Take a look in a Rails codebase if you happen to’re curious how CURRENT_TIMESTAMP is utilised.