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.

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 — , Jun 23 '15 at 21:50
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 — Hyu Kim, Jun 23 '15 at 21:58
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.. — BvuRVKyUVlViVIc7, Jun 26 '15 at 14:32
@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 — Hyu Kim, Jun 26 '15 at 15:04

AJcodez · Answer 1 · 2015-06-26T14:57:56.330

5

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

edited Jun 26 '15 at 14:57

answered Jun 26 '15 at 14:39

AJcodez

31,780
20
84
118

thank you for answer. but if your answers true, how `DatabaseCleaner.strategy = :truncation` resets indexes? I am confused. https://github.com/DatabaseCleaner/database_cleaner#additional-activerecord-options-for-truncation gem offers option for activerecord to reset ids which I pass to make sure it does. – Hyu Kim Jun 26 '15 at 14:55
and plus, http://stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886 selected answer explains :truncation is one that resets sequence. can it be DB version specific? – Hyu Kim Jun 26 '15 at 14:58
awsome. let me quickly test – Hyu Kim Jun 26 '15 at 15:04
1

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 – Hyu Kim Jun 26 '15 at 15:22
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. – Hyu Kim Jun 26 '15 at 15:25

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

1 Answers1