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.

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