Rails Ruby on Rails → 3.0.2

Comments and changes to this ticket

• 

  josh December 3rd, 2008 @ 03:26 PM

  • State changed from “new” to “stale”

• You flagged this item as spam.
  

  Jon Oler February 28th, 2009 @ 09:05 AM

  • Tag changed from “activerecord” to “activerecord, patch, postgres, postgresql”

  This is definitely a bug. I've attached a patch with 2 test cases that currently fail due to this bug. The patch also contains a fix to the postgres adapter that makes the 2 failing test cases succeed.
  

  Note this bug has been reported at least 2 times in the past, with patches submitted, but the fix has still mysteriously not been committed for some reason (http://dev.rubyonrails.org/ticke... and http://dev.rubyonrails.org/ticke...>

  The problem is bad because the postgres adapter queries the postgres server using "SHOW search_path" which returns a path that has spaces in it. The indexes() and tables() methods both take this string and quote each member of the search path to form a SQL "in" clause. However, only tables or indexes in the first entry in the search_path will be returned from these methods since the quoting process corrupted the remaining entries to have a leading space. So, if your search path is: "'$user', public, my_schema'", the in clause generated is "schemaname in ('''$user''', ' public', ' my_schema')".
  

  (Note that the presence of '''$user''' isn't going to work either, but the query will still return results for the remaining items in the path--the right thing to do would be to replace $user with the name of the current user enclosed in single quotes in the "like" criteria. That is a different bug that I may submit a separate patch for if it annoys me enough).

  • postgres_schema_path_fix.diff 6.4 KB

• You flagged this item as spam.
  

  Jon Oler February 28th, 2009 @ 07:16 PM

  Sorry, somehow I messed up the link to one of the previous duplicate bug reports:

  http://dev.rubyonrails.org/ticke...

• 

  Michael Koziarski March 2nd, 2009 @ 05:52 AM

  • Assigned user set to “Tarmo Tänav”
  • State changed from “stale” to “open”

  Hey tarmo,

  any chance you could take a look at this?

• You flagged this item as spam.
  

  Kristofor Selden March 19th, 2009 @ 10:23 PM

  This has a really simple fix:

  The following SQL statement is a fast, cross version, cross platform and respects all variables in the search path.

  SELECT tablename FROM pg_tables WHERE schemaname = ANY (current_schemas(false))

  The resulting code in PostgreSQLAdapter is shorter and more simple.

  http://dev.rubyonrails.org/ticke...

  db:structure:dump has the same issue.

• You flagged this item as spam.
  

  Rodolfo Hansen September 24th, 2009 @ 05:43 PM

  Any update on this bug?

• You flagged this item as spam.
  

  Kristofor Selden September 24th, 2009 @ 07:05 PM

  I don't know why this hasn't been fix but if you add the following to a file in config/initializers it will fix it. This will support finding tables in the user schema using the default search path "$user",public.

  It will also support quoting the schema name.

  ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do
    def tables(name = nil)
      query("SELECT tablename FROM pg_tables WHERE schemaname = ANY (current_schemas(false))", name).map{|row| row[0]}
    end
  end
  

• 

  Rizwan Reza March 26th, 2010 @ 01:14 PM

  Is this still an issue?

  If yes, does this apply to Rails 3 / master branch?

  Thanks.

• You flagged this item as spam.
  

  Kristofor Selden March 26th, 2010 @ 04:58 PM

  Yes, this is still an issue. The current code in http://github.com/rails/rails/blob/master/activerecord/lib/active_r... line 656 will not return all the tables in the default search path which uses "$user",public nor will it work with schema names that must be quoted.

  Instead of Rails trying to build a string of schemas, it should just use the current_schemas function in the query like the following:

    def tables(name = nil)
      query("SELECT tablename FROM pg_tables WHERE schemaname = ANY (current_schemas(false))", name).map{|row| row[0]}
    end
  

  The original patch submitted by orionheroku 2 years ago explains and solves the problem very simply.

  http://dev.rubyonrails.org/attachment/ticket/10242/postgresql_usern...

  The line numbers are no longer correct but that code still applies as well as how to test it. If you would like, I could make a patch for the current master.

• 

  Rizwan Reza March 26th, 2010 @ 07:54 PM

  • Tag changed from “activerecord, patch, postgres, postgresql” to “3.0, activerecord, patch, postgres, postgresql”
  • Milestone cleared.

  Yes Kristofor, a patch would be great. Thanks. :)

• You flagged this item as spam.
  

  Kristofor Selden March 26th, 2010 @ 10:44 PM

  Here's a patch against the current master.

  I added a test for the "$user",public search_path for the tables method, ensured it failed before my change and passed after. Tested activerecord with "rake test_postgresql"

  • postgresql_tables_current_schemas.diff 2 KB
• 

  Repository March 27th, 2010 @ 12:18 AM

  • State changed from “open” to “resolved”

  (from [afb786ad8a27ae593790a5788441a9083a516195]) In PostgreSQLAdapter, switch tables query to use current_schemas function [#918 state:resolved]

  Signed-off-by: wycats wycats@gmail.com
  http://github.com/rails/rails/commit/afb786ad8a27ae593790a5788441a9...

• 

  Jeremy Kemper October 15th, 2010 @ 11:01 PM

  • Milestone set to “3.0.2”
  • Importance changed from “” to “Low”

  [bulk edit]