How do I override the Hobo-generated name of a database index? (Possible Hobo Bug)

[Tim Griffin]

Hi all;

I'm migrating an application from MySQL to PostgreSQL and I'm hitting cases where my lengthy model names are triggering index names that are too long for PostgreSQL:

-- add_index("document_other_related_instruments", ["document_id"], {:name=>"index_document_other_related_instruments_on_document_id"})

   -> 0.0019s

-- add_index("document_other_related_instruments", ["other_related_instrument_id"], {:name=>"index_document_other_related_instruments_on_other_related_instru"})

rake aborted!

Index name 'index_document_other_related_instruments_on_other_related_instru' on table 'document_other_related_instruments' is too long; the limit is 63 characters

The generated index name is being truncated at 64 characters long instead of 63 - perhaps Hobo is missing the truncation by one character? I will investigate for a bug. 

If it's not a bug, can anyone tell me how to override the hobo-generated index name? I can see how to do it within a Rails migration, but I don't see any reference to controlling the migrations that Hobo generates in the Hobo Manual, and this thread hasn't had any followup since 2009. 

Many thanks,

Tim

[Tim Griffin]

Hmm... seems like an issue with the activerecord PostgreSQL adapter:

https://rails.lighthouseapp.com/projects/8994/tickets/6187-postgresql-and-rails-303-migrations-fail-with-index-name-length-64-chars

and I see that Hobo simply inquires about the max length from the model's connection (in index_spec.rb):

        if check_name.length > @model.connection.index_name_length

          r += ", :name => '#{name[0,@model.connection.index_name_length]}'"

          $stderr.puts("WARNING: index name #{check_name} too long, trimming")

So, if this is the case, is there any way for me to intervene in the name that HObo's migration generator comes up with?

Or, should Hobo be using index_name_length-1 ? 

Tim

[Ignacio Huerta]

That's weird Tim,

It certainly looks like a bug in Hobo, I believe
@model.connection.index_name_length will return "63" with PostgreSQL, so
Hobo should not try to create index names with 64 characters.

Can you give me a bit more info about your model names/relationships so
I can reproduce it with the closest example as possible?

Warm regards,
Ignacio

El 26/01/14 15:11, Tim Griffin escribió:

> <https://groups.google.com/d/topic/hobousers/CzVCrHNkXoA/discussion>

> hasn't had any followup since 2009.
>
> Many thanks,
> Tim
>

> --
> You received this message because you are subscribed to the Google
> Groups "Hobo Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to hobousers+...@googlegroups.com.
> To post to this group, send email to hobo...@googlegroups.com.
> Visit this group at http://groups.google.com/group/hobousers.
> For more options, visit https://groups.google.com/groups/opt_out.

--
Ignacio Huerta Arteche
http://www.ihuerta.net
Teléfono: 0034 645 70 77 35
Email realizado con software libre

[Tim Griffin]

Hi Ignacio;

I have document.rb:

class Document < ActiveRecord::Base

  hobo_model

  ...

 # Other instruments referenced by this document

  has_many    :document_other_related_instruments, :accessible => true 

  has_many    :other_related_instruments, :class_name => "Instrument", :through => :document_other_related_instruments

end

and document_other_related_instrument.rb

class DocumentOtherRelatedInstrument < ActiveRecord::Base

  hobo_model

  ...

  belongs_to :document

  belongs_to    :other_related_instrument, :class_name => "Instrument"

...

end

Gives me:

-- add_index("document_other_related_instruments", ["other_related_instrument_id"], {:name=>"index_document_other_related_instruments_on_other_related_instru"}) rake aborted! Index name 'index_document_other_related_instruments_on_other_related_instru' on table 'document_other_related_instruments' is too long; the limit is 63 characters

Tim

--
You received this message because you are subscribed to a topic in the Google Groups "Hobo Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/hobousers/d7y2LydE1y8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to hobousers+...@googlegroups.com.

[Tim Griffin]

I should mention that this error is arising from my schema.rb when I try to do a rake db:schema:load to migrate to PostgreSQL:

  create_table "document_other_related_instruments", :force => true do |t|

    t.datetime "created_at"

    t.datetime "updated_at"

    t.integer  "document_id"

    t.integer  "other_related_instrument_id"

  end

  add_index "document_other_related_instruments", ["document_id"], :name => "index_document_other_related_instruments_on_document_id"

  add_index "document_other_related_instruments", ["other_related_instrument_id"], :name => "index_document_other_related_instruments_on_other_related_instru"

And, the name here is quite definitely 64 characters. 

But, now I'm not sure if this is an historical problem - an index created in my MySQL database some time ago that has persisted as valid for MySQL (being max of 64 characters long), but invalid for PostgreSQL (which can't exceed 63 characters)? 

I see here, that PostgreSQL docs mention for the max length of an index name:

Its length is currently defined as 64 bytes (63 usable characters plus terminator).

My obvious work-around is to change the name in my schema.rb file, so perhaps I'm just chasing my tail. 

[Ignacio Huerta]

Hi Tim,

It definitely looks like a historical problem (though very interesting,
as it might happen to others in the future).

I just tried in a Postgresql app to create a similar index, and this is
what happened:

---------- Up Migration ----------
create_table :other_related_instruments do |t|
t.datetime :created_at
t.datetime :updated_at
end

create_table :document_other_related_instruments do |t|
t.string :name
t.datetime :created_at
t.datetime :updated_at
t.integer :other_related_instrument_id
end
add_index :document_other_related_instruments,
[:other_related_instrument_id], :name =>
'index_document_other_related_instruments_on_other_related_instr'
----------------------------------


The index was created with 63 characteres instead of your 64 characters,
and everything worked. I would suggest editing your schema.rb and fixint
it manually, as it can be safely said the error was provoked when
switching databases.

Warm regards,
Ignacio

El 27/01/14 04:12, Tim Griffin escribió:

> I should mention that this error is arising from my schema.rb when I try

> to do a /rake db:schema:load/ to migrate to PostgreSQL:

>
> create_table "document_other_related_instruments", :force => true do |t|
> t.datetime "created_at"
> t.datetime "updated_at"
> t.integer "document_id"
> t.integer "other_related_instrument_id"
> end
>
> add_index "document_other_related_instruments", ["document_id"], :name
> => "index_document_other_related_instruments_on_document_id"
> add_index "document_other_related_instruments",
> ["other_related_instrument_id"], :name =>

> "*index_document_other_related_instruments_on_other_related_instru*"

>
> And, the name here is quite definitely 64 characters.
>

> But, now I'm not sure if this is an /historical/ problem - an index

> created in my MySQL database some time ago that has persisted as valid
> for MySQL (being max of 64 characters long), but invalid for PostgreSQL
> (which can't exceed 63 characters)?
>
> I see here

> <http://postgresql.1045698.n5.nabble.com/NAME-maximum-length-in-bytes-td5695932.html>,

> that PostgreSQL docs mention for the max length of an index name:
>

> /Its length is currently defined as 64 bytes (63 usable characters
> plus terminator)./

>
> My obvious work-around is to change the name in my schema.rb file, so
> perhaps I'm just chasing my tail.
>
> Tim
>
>
>
>
>
> On Sunday, January 26, 2014 3:32:38 PM UTC-5, Tim Griffin wrote:
>
> Hi Ignacio;
>

> I have *document.rb:*

> class Document < ActiveRecord::Base
> hobo_model
> ...
> # Other instruments referenced by this document
> has_many :document_other_related_instruments, :accessible => true
> has_many :other_related_instruments, :class_name =>
> "Instrument", :through => :document_other_related_instruments
> end
>

> and *document_other_related_instrument.rb*

> class DocumentOtherRelatedInstrument < ActiveRecord::Base
>
> hobo_model
> ...
> belongs_to :document
> belongs_to :other_related_instrument, :class_name => "Instrument"
> ...
> end
>
> Gives me:
>
> -- add_index("document_other_related_instruments",
> ["other_related_instrument_id"],
> {:name=>"index_document_other_related_instruments_on_other_related_instru"})
> rake aborted! Index name
> 'index_document_other_related_instruments_on_other_related_instru'
> on table 'document_other_related_instruments' is too long; the limit
> is 63 characters
>
>
>
> Tim
>
>

[Tim Griffin]

Thanks so much for the verification, Ignacio, and for confirming my suspicion. This was really my first foray into lifting the cover off the migration-generation mechanism, so I wasn't quite sure what I was looking for.  

If you switch your test model to MySQL, does it still give you an index name of 64 characters? 

If so, then should Hobo really be enforcing 63 as the maximum (so that migrations are portable between MySQL and Postgres)? 

Best regards,

Tim

--
You received this message because you are subscribed to a topic in the Google Groups "Hobo Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/hobousers/d7y2LydE1y8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to hobousers+...@googlegroups.com.

[Ignacio Huerta]

Hi Tim,

You are right, with MySQL the index name hsa *64* characters:

---------- Up Migration ----------
create_table :other_related_instruments do |t|
t.datetime :created_at
t.datetime :updated_at
end

create_table :document_other_related_instruments do |t|
t.string :name
t.datetime :created_at
t.datetime :updated_at
t.integer :other_related_instrument_id
end
add_index :document_other_related_instruments,
[:other_related_instrument_id], :name =>

'index_document_other_related_instruments_on_other_related_instru'
----------------------------------


About forcing a 63 character limit as maximum, I think it's a great
idea. I'll open an issue and if nobody complains we can implement it for
the next release.

https://github.com/Hobo/hobo/issues/65

Warm regards,
Ignacio

El 27/01/14 14:09, Tim Griffin escribió:

> Thanks so much for the verification, Ignacio, and for confirming my
> suspicion. This was really my first foray into lifting the cover off the
> migration-generation mechanism, so I wasn't quite sure what I was
> looking for.
>
> If you switch your test model to MySQL, does it still give you an index

> name of *64* characters?
>
> If so, then should Hobo really be enforcing *63* as the maximum (so that

> <mailto:hobousers%2Bunsu...@googlegroups.com>.

> > To post to this group, send email to hobo...@googlegroups.com

> <mailto:hobo...@googlegroups.com>.

> > Visit this group at http://groups.google.com/group/hobousers.
> > For more options, visit https://groups.google.com/groups/opt_out.
>
> --
> Ignacio Huerta Arteche
> http://www.ihuerta.net
> Teléfono: 0034 645 70 77 35
> Email realizado con software libre
>
> --
> You received this message because you are subscribed to a topic in
> the Google Groups "Hobo Users" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/hobousers/d7y2LydE1y8/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> hobousers+...@googlegroups.com

> <mailto:hobousers%2Bunsu...@googlegroups.com>.

> To post to this group, send email to hobo...@googlegroups.com

> <mailto:hobo...@googlegroups.com>.