Rails + postgres autoincrement not updated on explicit id inserts

Question

I'm with the same problem of this post. But I'm using Rails and don't know how to do this workaround in activerecord.

I'm inserting some data using seeds.rb:

device_platforms = DevicePlatform.create([{id: 1, name: 'Android'}, {id: 2, name: 'IOS'}, {id: 3, name: 'Windows Phone'}])

When I insert another DevicePlatform, I get this exception:

Failure/Error: @device_platform = FactoryGirl.create(:device_platform)
 ActiveRecord::RecordNotUnique:
   PG::UniqueViolation: ERRO:  duplicar valor da chave viola a restrição de unicidade "device_platforms_pkey"                                                                 
   DETAIL:  Chave (id)=(2) já existe.
   : INSERT INTO "device_platforms" ("created_at", "name", "updated_at") VALUES ($1, $2, $3) RETURNING "id"

The message is in portuguese, it says that a row with that ID already exists.

Thanks.

I'm not sure what you mean, you're calling `create`, so you're "going to the database". What about your code would not work if you just had `{ name: 'Android' }, { name: 'IOS' }`, etc in the seeds create call? — Nick Veys, Sep 12 '14 at 17:35
Don't include `id`s in your seeds. If you must include `id`s in your seeds then you'll have to `connection.execute` the SQL in the other question after adding your seeds. — mu is too short, Sep 12 '14 at 17:40
I'm building a rails API. According to the request user_agent, I know from what platform the user request is comming. If I don't especify the id, I need on every request to go to the database and search by the name to get this ID. Don't know if it's the best way. If not, please let me know. — William Weckl, Sep 12 '14 at 17:40
Thanks for including the exact text of the error. I'd +100 if I could. — Craig Ringer, Sep 12 '14 at 17:53
@muistooshort You think include ids on seeds file is a bad practice? — William Weckl, Sep 12 '14 at 18:03
@NickVeys My "going to the database" message was sent wrong. Hitted the enter button unintentionally. :) — William Weckl, Sep 12 '14 at 18:04
Depending on `id`s being anything in particular is a bad practice. You can do it if you need to but you will need to run some SQL to patch up the sequences that are used to supply `id` values. — mu is too short, Sep 12 '14 at 18:10
@muistooshort Thank you. Please, post it as an answer to I can set as accepted. — William Weckl, Sep 12 '14 at 18:14
@WilliamWeckl you should be hitting the db with a `find_by(name: 'xxx')` if you really want the id. The real question is why do you have a table with just that in it? If it is to define the apps behaviour then you already have the information in the request, as you have stated. — MarkJL, Jul 26 '18 at 11:44

score 6 · Answer 1 · answered Jul 26 '18 at 10:53

6

I believe that although this is a bad habit, many times it is the only resort

You can define this method :

def fix_self_increment(model)
  ActiveRecord::Base.connection.execute(
    "BEGIN; " \
    "LOCK TABLE #{model.table_name} IN EXCLUSIVE MODE; " \
    "SELECT setval('#{model.table_name}_id_seq', COALESCE((SELECT MAX(id)+1 FROM #{model.table_name}), 1), false); " \
    "COMMIT;"
  )
end

and then call

fix_self_increment(DevicePlatform)

Enjoy

answered Jul 26 '18 at 10:53

Oz Ben-David

1,589
1
16
26

1

I'd put this code in every migration of data that does this kind of insert rather than having a method that does it. It quickly teaches you it's a bad idea to specify id. This is the correct answer btw. – MarkJL Jul 26 '18 at 11:39

score -3 · Accepted Answer · answered Nov 05 '14 at 13:06

-3

According to the comments, using id in seed is a bad practice. I'm avoiding bad practices and that's enough for me. Changed my code to avoiding this.

answered Nov 05 '14 at 13:06

William Weckl

2,435
4
26
43

1

There are still valid use cases where this can happen (for example, reading in a CSV file in your rake task), so it'd be nice to see a valid answer for how to solve this. – iandouglas Apr 16 '18 at 16:19
Please accept the answer above as it does answer the question you have asked, even though it is a bad idea. Sometimes doing bad things is necessary. – MarkJL Jul 26 '18 at 11:45

Rails + postgres autoincrement not updated on explicit id inserts

2 Answers2