This project is archived and is in readonly mode.

#6187 ✓stale
fhirzall

PostgreSQL and Rails 3.0.3 migrations fail with index name length > 64 chars

Reported by fhirzall | December 17th, 2010 @ 01:54 PM

As of 3.0.3, add_index fails if the generated index is longer than 64 chars: I've confirmed that rolling back to rails 3.0.1 does not cause the same problem:


add_index :facility_inventory_items, [:facility_id, :inventory_item_id], :name => "index_fii_facility_id_inventory_item_id"
Index name 'index_facility_inventory_items_on_facility_id_and_inventory_item_id' on table 'facility_inventory_items' is too long; the limit is 63 characters

The workaround is to specify the :name in add_index.


add_index :long_table_name, [:long_column_name, :col2], :name => "short_index_name"

Comments and changes to this ticket

  • Piotr Sarnacki

    Piotr Sarnacki December 17th, 2010 @ 02:54 PM

    • State changed from “new” to “incomplete”
    • Importance changed from “” to “Low”

    This is proper behavior. Postgres allows longer names, but cuts it anyway. Try something like that in dbconsole:

    CREATE INDEX blablablablablablablablablablablablablablablablablablablablabla_this_will_be_cut_anyway ON posts (title);
    

    and then:

    # CREATE INDEX blablablablablablablablablablablablablablablablablablablablabla ON posts (title);
    ERROR:  relation "blablablablablablablablablablablablablablablablablablablablabla" already exists
    
    # \di
                                                   List of relations
     Schema |                              Name                               | Type  | Owner  |       Table       
    --------+-----------------------------------------------------------------+-------+--------+-------------------
     public | blablablablablablablablablablablablablablablablablablablablabla | index | drogus | posts
    
  • Piotr Sarnacki

    Piotr Sarnacki December 17th, 2010 @ 02:55 PM

    • State changed from “incomplete” to “invalid”
  • fhirzall

    fhirzall December 17th, 2010 @ 03:13 PM

    I understand that PG cuts it off, but doesn't it make more sense that rails not throw an exception and continue running migrations? This behavior breaks several apps that didn't manually specify an index name for indexes on more than 2-3 decently sized columns. This was also reported on issue #3252 and was fixed as part of the patch for #3452 on rails 2.3.

  • Piotr Sarnacki

    Piotr Sarnacki December 17th, 2010 @ 04:14 PM

    • State changed from “invalid” to “open”

    I would personally leave it out like that, but I can see your point. If it was fixed earlier in 2.3, I'm opening that ticket for further discussion.

  • Piotr Sarnacki

    Piotr Sarnacki December 17th, 2010 @ 06:49 PM

    • Assigned user set to “Piotr Sarnacki”

    fhirzall: I checked history for changes in that matter and it looks like:

    rails < 2.3.4 didn't care
    2.3.4 <= rails < 2.3.6 - exception caused by #3252
    2.3.6 <= rails < 3.0.3 - ignoring long indexes with warning in logs
    rails >= 3.0.3 - error instead of warning in logs

    In that case, you probably don't want behavior from rails >= 2.3.6, as your migrations were passing, but without actually creating indexes.

    My opinion is that, although long indexes names require an action now, it's much better to notify user about the problem than ignore it or let postgres ignore it.

    When you were creating the ticket, which of these approaches have you had in mind?

  • 2kan

    2kan December 18th, 2010 @ 11:41 AM

    Oh, few days ago I was thinking to write a patch for it, because this problem is really annoying (for me). I was thinking about two strategies: first is always to raise an exception when name is too long, second is to try to cut the index name to 63 chars (for postgres) and then if there is already an index with such name raise an exception.

    Piotr, what do you think is the best way to deal with it?

  • fhirzall

    fhirzall December 18th, 2010 @ 11:52 AM

    IMO the best way to handle this is to truncate the index name and display a warning to the user. My reasoning is that an exception will break many existing apps, and not creating the actual index will throw people off (that didn't pay attention to the warning and assumed truncation).

    That's my 0.02, what did you have in mind Piotr?

    Thanks!

  • 2kan

    2kan December 19th, 2010 @ 01:06 PM

    I've made patch (with test): when index name is too long we try to cut it and create index with shorter name. If index with shorter name already exists an exception is raised.

  • Piotr Sarnacki

    Piotr Sarnacki December 21st, 2010 @ 01:34 PM

    fhirzall: I just wanted to know which behavior you consider as the appropriate one.

    2kan: I don't think that I can merge that. While it may be ok for only postgres, where index names are truncated anyway, it's not expected for the other databases.

    I'm still against removing the error, but I'll talk with someone from core team about that.

  • Yaroslav Markin

    Yaroslav Markin December 21st, 2010 @ 01:44 PM

    Piotr, any change 2kan's patch can be redone for Postgres only, then?

  • 2kan

    2kan December 21st, 2010 @ 02:11 PM

    Piotr, yes, but from another point of view it's not expected when rails generates index name which cause exception or db error. Maybe we should do it this way: we shouldn't truncate user defined index name and truncate names automatically generated by rails?

  • Joe Van Dyk

    Joe Van Dyk February 4th, 2011 @ 04:36 PM

    I'm on pgsql 9 with Rails 2.3:


    SQL (1.4ms) BEGIN Index name 'index_ratings_on_rating_type_and_rating_id_and_rated_type_and_rating' on table 'ratings' is too long; the limit is 63 characters. Skipping.
    SQL (1.4ms) INSERT INTO schema_migrations (version) VALUES ('20110204162542') SQL (1.1ms) COMMIT

    When I ran the migration, it appeared that everything went fine, but I didn't see the index created. I saw the above in my logs. It looks like it just skipped over creating the index without any complaints.

    Very surprising.

  • rails

    rails May 5th, 2011 @ 01:00 AM

    This issue has been automatically marked as stale because it has not been commented on for at least three months.

    The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

    Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

  • rails

    rails May 5th, 2011 @ 01:00 AM

    • State changed from “open” to “stale”

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Attachments

Pages