Handle id sequences with care

[volkanunsal]

I realized that after restoring the dump file generated by Polo, I still had to reset the id sequences postgres uses to determine the next id for a new record. This entails looking up the highest id number in the imported rows, and running the following command for every table:

ALTER SEQUENCE users_id_seq RESTART WITH 208

It would be great if Polo did this automatically, though.

[nettofarah]

hey, @volkanunsal. Long time no PR 😂. We missed you!

This is an interesting idea.
Though I must say this is pretty PostgreSQL specific, so I'm not sure how it fits in the scope of Polo.

At the same time, I think this is something we could try to squeeze in the postgres adapter.
What do you think?

Wanna take a stab at it?

[volkanunsal]

@nettofarah Ha, ok, I'll give it a try.

This is a feature I would also find very useful

[nettofarah]

Yeah, I was thinking about this today.
I think we could write a class for data ingestion.

We could add an import method to Polo that you could run in development/test.
That method could then delegate to some adapter specific classes.

What do you guys think?

Depends on if you want to bake this into the default Polo.explore for postgres or not. If we always want the 'ALTER SEQUENCE' statement in the end, then you can just modify the existing #ignore_transform so it tacks on the 'ALTER SEQUENCE' statement at the end.

I'm already doing something similar:

File.open('filename') do |f|
   f << Polo.explore(klass, ids).join(";\n")
   f << ";\nALTER SEQUENCE #{klass.table_name}_id_seq RESTART WITH #{ids.max+1};\n"
end

[bessey]

You can see how we handle this in Brillo (a Polo wrapper with some extra conventions) here

[nettofarah]

@bessey maybe we can join forces and try to bring this feature over to Polo?

[bessey]

Indeed, this is one of the parts of Brillo that would probably fit better in Polo. It doesn't seem like the API would be affected at all either, since you already have a notion of DB specific adapters in Polo.

Potentially we'd just need something like

https://github.com/IFTTT/polo/blob/master/lib/polo/sql_translator.rb#L23

    def to_sql
      case @configuration.on_duplicate_strategy
      when :ignore
        @adapter.ignore_transform(inserts, records)
      when :override
        @adapter.on_duplicate_key_update(inserts, records)
      else inserts
--    end
++    end + @adapter.table_footer(record.class.arel_table)
    end

[nettofarah]

niice! I like that.
We could even be a little extra defensive and do something like:

if @adapter.respond_to?(:table_footer)
  ...
end