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

  • josh

    josh 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 ( and>

    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:

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

    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]}
  • 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?


  • Kristofor Selden

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

    Yes, this is still an issue. The current code in 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]}

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

    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

  • 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 »

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

The new ticket tracker is available at <a href=""></a>

Referenced by