PostgreSQL: tables not generating correct schema list

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

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

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.

Any update on this bug?

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

Is this still an issue?

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

Thanks.

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.

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"