DatabaseCleaner.clean_with(:truncate) does not reset auto incremented id

Question

I am currently using DatabaseCleaner in my Rails project with PostgreSQL running, and set it up as below.

RSpec.configure do |config|
  config.before(:suite) do
    DatabaseCleaner.clean_with(:truncation, { pre_count: true, reset_ids: true })
  end

  config.before(:each, js: true) do
    DatabaseCleaner.strategy = :truncation
  end

  config.before(:each) do
    DatabaseCleaner.strategy = :transaction
  end

  config.before(:each) do
    DatabaseCleaner.start
  end

  config.after(:each) do
    DatabaseCleaner.clean
  end
end

in one of my rails test suite, I printed out id of an instance. I assume it should be relatively small number since clean_with(:truncate) suppose to clear db and run vacuum on it. but it gets increased every time I run the test.

test passes and it doesn't matter what sequence it uses. but why clean_with(:truncation) doesn't work in a way it should?

====== EDIT ======

this is in the scope of RSpec test. I understand sequence numbering has no impact on performance, but expensive cleaning (:truncation) on each :suite and use cheap and quick cleaning (:transaction) does. so I want to understand why clean_with(:truncation) does not reset id for me to obtain clean db state before running test suite.

Knighthood

@muistooshort you are right. I will edit my question. – Knighthood

I don't know rails, but truncate on itself does not reset any sequence in Postgres. But Postgres supports truncate .. restart identity. Maybe in rails this can be configured somehow – Aright

the option reset_ids suppose to ensure the sequence reset. in fact. by default truncate resets sequence in database cleaner. I just added that option to clear myself. If I do DatabaseCleaner.strategy = :truncate, it resets properly. but I want to use transaction for each to optimize performance – Knighthood

If you have an index over your identities, it doesn't matter if they are high or low... You don't get any performance increase just because of low numbers.. – Phosphoresce

@Lichtamberg right. I understand index numbering has nothing to do with performance. but using transaction strategy does. running truncation for each test case is slower than running transaction. that's why I want to perform truncation on each :suite – Knighthood

Answer 1

That's how the database works.

$ createdb test1
$ psql -d test1

> create table numbers (id serial, x integer);
> insert into numbers (x) values (1), (2), (3);
> select id from numbers order by id desc limit 1;

# 3

> truncate numbers;
> insert into numbers (x) values (1), (2);
> select id from numbers order by id desc limit 1;

# 5

As you can see, :truncate for database cleaner means truncate. Hope that makes sense.

EDIT -- missed the question completely.

The reason :reset_ids wouldn't work is a low postgresql version. Find out your version with psql --version, and from the database cleaner source you need 8.4 or higher.

@restart_identity ||= db_version >=  80400 ? 'RESTART IDENTITY' : ''

I'm running 9.3.5, which works fine.

> truncate numbers restart identity;
> insert into numbers (x) values (1);
> select * from numbers;

#  id | x 
# ----+---
#   1 | 1

To make sure, database cleaner works fine too.

require 'rails/all'
require 'database_cleaner'
ActiveRecord::Base.establish_connection('postgres://localhost/test1')
class Number < ActiveRecord::Base; end
Number.count
# => 1
DatabaseCleaner.clean_with(:truncation, reset_ids: true)

It resets the serial columns.

$ psql -d test1
> insert into numbers (x) values (1);
> select * from numbers;

#  id | x 
# ----+---
#   1 | 1

Amygdaline

thank you for answer. but if your answers true, how DatabaseCleaner.strategy = :truncation resets indexes? I am confused. github.com/DatabaseCleaner/… gem offers option for activerecord to reset ids which I pass to make sure it does. – Knighthood

and plus, #11420036 selected answer explains :truncation is one that resets sequence. can it be DB version specific? – Knighthood

awsome. let me quickly test – Knighthood

it resets sequence if I run clean_with in console. but it doesn't when I do it as part of RSpec test suite. postgres version is 9.3.3. DatabaseCleaner gem v1.4.1. RSpec v3.2.0 – Knighthood

your answer didn't solve the issue I have, but if I don't get anything else in next 24 hours, I will mark yours as right one. many thanks. – Knighthood