Rails Postgres Query Fails to Return New Records

One way to prevent duplicates in Rails is with validations: Correct way of prevent duplicate records in Rails

However your criteria is more complex as it deals with spanning more than one row. I believe your criteria is, don't allow entry of a transit record if the most recent transit record was created less than 20 seconds ago. Is that right?

Trying to enforce a constraint that involves looking at data from many rows is mentioned as undesirable here: SQL Sub queries in check constraint

A trigger could be used to enforce your constraint at the database level. One could catch the trigger in an exception. There's a gem named HairTrigger that might be useful, not sure.

Taking ideas from here: https://karolgalanciak.com/blog/2016/05/06/when-validation-is-not-enough-postgresql-triggers-for-data-integrity/

Example with Postgresql trigger:

bin/rails generate model transit tag:text
rails generate migration add_validation_trigger_for_transit_creation

class AddValidationTriggerForTransitCreation < ActiveRecord::Migration[5.2]
  def up
    execute <<-CODE
      CREATE FUNCTION validate_transit_create_time() returns trigger as $$
      DECLARE
      age int;
      BEGIN
        age := (select extract(epoch from current_timestamp - t.created_at)
        from transits t
        where t.tag = NEW.tag
        and t.id in (select id from transits u
           where u.id = t.id
           and u.tag = t.tag
           and u.created_at = (select max(v.created_at) from transits v where v.tag = u.tag)
        ));
        IF (age < 20) THEN
          RAISE EXCEPTION 'created_at too early: %', NEW.created_at;
        END IF;
        RETURN NEW;
      END;
      $$ language plpgsql;

      CREATE TRIGGER validate_transit_create_trigger BEFORE INSERT OR UPDATE ON transits
      FOR EACH ROW EXECUTE PROCEDURE validate_transit_create_time();
    CODE
  end

  def down
    execute <<-CODE
    drop function validate_transit_create_time() cascade;
    CODE
  end
end


user1@debian8 /home/user1/rails/dup_test > ../transit_test.rb ; sleep 20; ../transit_test.rb 

dup_test_development=> select * from transits;
 id  |   tag    |         created_at         |         updated_at         
-----+----------+----------------------------+----------------------------
 158 | test_tag | 2019-01-31 18:38:10.115891 | 2019-01-31 18:38:10.115891
 159 | test_tag | 2019-01-31 18:38:30.609125 | 2019-01-31 18:38:30.609125
(2 rows)

Here is the portion of our query that gives the latest transit entry with our tag

dup_test_development=> select * from transits t
where t.tag = 'test_tag' and t.id in
(select id from transits u where u.id = t.id and u.tag = t.tag and u.created_at =
(select max(v.created_at) from transits v where v.tag = u.tag));

 id  |   tag    |         created_at         |         updated_at         
-----+----------+----------------------------+----------------------------
 159 | test_tag | 2019-01-31 18:38:30.609125 | 2019-01-31 18:38:30.609125
(1 row)

Modifying to give the difference between the current_timestamp (now) and the latest transit entry with our tag. This difference is an interval in postgresql. Using UTC to match Rails:

dup_test_development=> select current_timestamp at time zone 'utc' - created_at
from transits t  where t.tag = 'test_tag' and t.id in
(select id from transits u where u.id = t.id and u.tag = t.tag and u.created_at =
(select max(v.created_at) from transits v where v.tag = u.tag));
    ?column?     
-----------------
 00:12:34.146536
(1 row)

Adding Extract(epoch) to convert this to seconds:

dup_test_development=> select extract(epoch from current_timestamp at time zone 'utc' - created_at)
from transits t  where t.tag = 'test_tag' and t.id in
(select id from transits u where u.id = t.id and u.tag = t.tag and u.created_at =
(select max(v.created_at) from transits v where v.tag = u.tag));
 date_part  
------------
 868.783503
(1 row)

We store the seconds as age, and if the age is < 20, we raise a database exception

Running 2 inserts with a second delay less than 20:

user1@debian8 /home/user1/rails/dup_test > ../transit_test.rb ; sleep 5; ../transit_test.rb 
#<ActiveRecord::StatementInvalid: PG::RaiseException: ERROR:  created_at too early: 2019-01-31 18:54:48.95695
: INSERT INTO "transits" ("tag", "created_at", "updated_at") VALUES ($1, $2, $3) RETURNING "id">
"ERROR:  created_at too early: 2019-01-31 18:54:48.95695\n"

Short test outside of rails:

#!/usr/bin/env ruby

require 'active_record'
require 'action_view'

path = "/home/user1/rails/dup_test/app/models"
require "#{path}/application_record.rb"
Dir.glob(path + "/*.rb").sort.each do | file |
  require file
end

ActiveRecord::Base.establish_connection(
  :adapter => "postgresql",
  :database  => 'dup_test_development',
  encoding: "unicode",
  username: "user1",
  password: nil
)
class Test
  def initialize()
  end
  def go()
    begin
      t = Transit.new(tag: 'test_tag')
      t.save
    rescue ActiveRecord::StatementInvalid => e
      p e
      p e.cause.message
    end
  end
end

def main
  begin
    t = Test.new()
    t.go()
  rescue Exception => e
    puts e.message
  end
end

main

Using someting like Redis has been mentioned - may be better for peformance

This is what testing is for.

class Transit <  ActiveRecord::Base
  def new_transit(tag: tag)
  <your code>
  end
end

You test code:

  test 'it saves once' do
    <save it once.  check the count, etc>
  end

  test 'it does not save within 10 seconds' do
    <save it once.  Set the created at to 10 seconds ago.  try to save again.  check the count, etc>
  end

etc

p.s. Consider using redis or something like that. Otherwise you're wanting to do something like table locks to make sure you're not stepping on yourself. And you probably don't want to do table locks.