This project is archived and is in readonly mode.

#918 ✓ resolved
Simon Cantem

PostgreSQL: tables not generating correct schema list

Reported by Simon Cantem | August 27th, 2008 @ 05:56 PM | in 3.0.2

The schema_search_path function returns a string such as: "schema1, schema2"

This results in the schemas string: E'schema1',E' schema2' which breaks the loading of table lists if when you're using schema2 (which breaks migrations, it keeps trying to recreate the schema_migrations table).

I believe a strip should be added to line 523 of connection_adapters/postgresql_adapter.rb, resulting in the following:


schemas = schema_search_path.split(/,/).map { |p| quote(p.strip) }.join(',')

Comments and changes to this ticket

  • Joshua Peek

    Joshua Peek December 3rd, 2008 @ 03:26 PM

    • State changed from “new” to “stale”
  • Jon Oler

    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).

  • Jon Oler

    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

    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?

  • Kristofor Selden

    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.

  • Rodolfo Hansen
  • Kristofor Selden

    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

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

    Is this still an issue?

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

    Thanks.

  • Kristofor Selden

    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

    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. :)

  • Kristofor Selden

    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"

  • Repository

    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

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

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

Create your profile

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

Tickets have moved to Github

The new ticket tracker is available at https://github.com/rails/rails/issues

Shared Ticket Bins

Referenced by

Pages