Rails Ruby on Rails → 2.x

Comments and changes to this ticket

• You flagged this item as spam.
  

  Hongli Lai September 24th, 2008 @ 10:37 AM

  Nice. But I think you should explicitly document that PostgreSQL supports functional indexes while MySQL doesn't, so that people don't have to Google that information themselves.

• 

  Michael Koziarski September 24th, 2008 @ 12:25 PM

  I like this, or something like it, for post 2.2.

  You've changed the schema info query for postgresql though, why is that?

• 

  Tarmo Tänav September 24th, 2008 @ 03:33 PM

  Michael, looks like the query is changed so it would return the definition of the function so it would be included in the schema dump, not sure why the select conditions was changed though.

• You flagged this item as spam.
  

  Philip Hallstrom September 24th, 2008 @ 04:06 PM

  I will admit to not being a postgresql expert and understanding all the changes in that query. I asked on the PG mailing list for what I wanted (the SQL to return both simple and functional indexes -- specifically the function itself) and what query I currently had.

  Here's the post here:

  http://archives.postgresql.org/p...

  Was hoping there would be some other postgres folks here that could confirm that :)

  In the tests I setup the query does do what it should as far as I can tell...

• You flagged this item as spam.
  

  Steve Purcell September 27th, 2008 @ 09:31 AM

  +1

  This would make my life so much easier!

  I'd suggest using :expression or :function rather than :functional in #create_index.

• 

  Mikel Lindsaar September 29th, 2008 @ 01:27 PM

  +1 have had a couple of cases where this would have been useful, end up dropping into SQL in the migration to handle it.

  Code looks good and all tests pass on edge against PG 8.3.4

• You flagged this item as spam.
  

  Steve Purcell September 29th, 2008 @ 01:53 PM

  I checked that the pg_get_indexdef() function needed by this patch is available in older PostgreSQL versions -- there's no note in the usually-thorough PG docs to indicate otherwise, so I imagine it's safe for at least all 8.x releases.

  @mikel: I have no problem with dropping into SQL for this, but the reason I'd really like to see this patch integrated is that it will cause functional indexes to get copied to the test database too, 'cos I want to write a full-text-search plugin that reflects on functional indexes.

  It's a general problem that only a subset of a DB schema is automatically cloned to the test database. Setting dump_format to :sql sucks because it breaks fixture loading horribly once you have any foreign key constraints in your DB.)

  For that reason I'd be inclined to submit a patch that also dumps view definitions to schema.rb... but...

  Perhaps an alternative for us PostgreSQL users would be to make :sql dumps and fixtures play nicely together, e.g. by providing database adaptor methods to disable and enable constraints. Just a thought.

• 

  Mikel Lindsaar September 29th, 2008 @ 01:59 PM

  @Steve: I haven't tried it, but doesn't rake db:test:clone_structure work on the functional indicies?

  I use that all the time on my app because I use views and triggers on some legacy code.

  Mikel

• You flagged this item as spam.
  

  Steve Purcell September 29th, 2008 @ 03:53 PM

  @Mikel - that works like the :sql dump format, and does indeed clone the functional indexes, not just the views and triggers.

  Of course the problem is then that the foreign keys will prevent fixtures from loading properly in the test database.

• 

  Pratik January 5th, 2009 @ 05:46 PM

  • Milestone cleared.
  • Tag changed from “activerecord, enhancement, migrations, patch, postgresql” to “activerecord, enhancement, migrations, patch, postgresql, verified”
  • Assigned user set to “Michael Koziarski”
• 

  Michael Koziarski February 1st, 2009 @ 02:08 AM

  • Milestone set to “2.x”

  Not sure why this was marked 2.3

  @Steve: Doesn't the new fixtures code disable the constraints while loading fixtures?

• You flagged this item as spam.
  

  Steve Purcell February 1st, 2009 @ 08:41 AM

  @Koz - yes, that was an uninformed/confused comment on my part. I have since had the happy experience of :sql dumps Just Working for me.

  And in this case, then using :sql dumps is likely a better option than adding support for functional indexes. At a certain point, you reasonably have to expect to drop into SQL in your migrations.

• 

  Michael Koziarski February 1st, 2009 @ 09:46 PM

  • State changed from “new” to “wontfix”

  OK, let's resolve this one then. As you can already create them in migrations and use :sql for testing support

• You flagged this item as spam.
  

  Philip Hallstrom February 2nd, 2009 @ 10:41 PM

  I don't want to argue too much, but I'm not sure I understand why this wouldn't be a useful addition? Following Steve's logic above, couldn't one say that we don't need any Rails/migration magic because all of it can be done in SQL?

  I realize that right now this only supports creating functional indexes in PostgreSQL, but it paves the way for someone to add Oracle/Sybase/etc functionality more easily later on.
  

  Functional indexes can be pretty handy, particularly when doing things like "show me all the foobar's starting with the letter 'A'".

  Anyway, my last 2 cents on the issue :)

• 

  Michael Koziarski February 2nd, 2009 @ 10:47 PM

  I just think it's not quite worth it if it'll only be supported in one database that we ship out of the box. However if a plugin along those lines gets popular with other DBs. Then we can revisit the decision