Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

postgres adapter quotes table name, breaks when non-default schema is used

#390

Our app is using some tables in non-default schemas. It used to work ok, only thing to do was to set table name manually, like: 

class PersistedProperty < ActiveRecord::Base
    set_table_name 'common.persisted_properties'
end

It broke with Rails 2.1 because AbscractAdapter now includes module abstract/quoting.rb, and that one defines quoting of table names by default. In postgres, quoting like this 'select * from "bar"' works, so is 'select * from foo."bar"', but 'select * from "foo.bar"' does NOT work.

I monkey patched this issue locally using included code, but it would be nice to have it fixed in the source. Included fix is just a quick workaround, it may be or may not be the code for longer term fix. 

module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter < AbstractAdapter
      def quote_table_name(name)
        name
      end
    end
  end
end

Reported by Olek Poplavsky · June 11th, 2008 @ 05:20 PM

State: resolved
Milestone: none
Assigned to: Tarmo Tänav Tarmo Tänav
Importance: none

Activity

  1. Jeremy Kemper
    Jeremy Kemper
    • State changed from new to open
    • Assigned user set to Jeremy Kemper

    Well, removing quoting is a 'fix' unless you need a quoted table name :) How about smartening up quoting to recognize schema-qualified table names? Or introducing 

    self.table_schema = 'common'
self.table_name = 'persisted_properties'

    June 11th, 2008 @ 08:40 PM

  2. Olek Poplavsky
    Olek Poplavsky

    Hi Jeremy

    It was nice seeing you at RailsConf :)

    The syntax you are offering is nice and I would love to have it, but it would require significant reworking of all of the database adapters, and I am not quite in position to do so, especially because I am only using PostgreSQL adapter. I am just trying to be pragmatic and do things the easiest possible way :)

    BTW - I have found 2 more problems in Rails 2.1 with non-default schema support.

    One was that column name escaping was really interfering with the way query for auto incrementing primary keys was generated. Again, I used simplest possible solution at hand - disabled column name escaping:

    def quote_column_name(name)

    name

    end

    Another problem was that rails could not create indexes on columns in tables with non-default schema. I had to make that method a bit smarter about schemas using simple regexp:

    module ActiveRecord

    module ConnectionAdapters

    module SchemaStatements

    def index_name(table_name, options) #:nodoc:

    if Hash === options # legacy support

    if options[:column]

    "index_#{table_name.sub(/^.*\./, '')}_on_#{Array(options[:column]) * '_and_'}"

    elsif options[:name]

    options[:name]

    else

    raise ArgumentError, "You must specify the index name"

    end

    else

    index_name(table_name, :column => options)

    end

    end

    end

    end

    end

    Sorry for late reply, got sick on my way back from RailsConf, and it took quite some time to recover...

    June 16th, 2008 @ 11:17 PM

  3. Torben Wölm
    Torben Wölm

    Hi

    The Oracle adapter has the same problem. But here the quoting is only activated if you use uppercase letters in the table name.

    For instance

    set_table_name 'myschema.MY_TABLE_NAME'

    will trigger the quoting. If you just write the table name in all lowercase, no quoting is done.

    This is only a problem with Rails 2.1 for some reason, even though the quoting logic in the adapter hasn't changed.

    Are anyone working on general handling of schemas in ActiveRecord -- or is it on a todo list somewhere?

    June 18th, 2008 @ 10:32 PM

  4. Sean Bowman
    Sean Bowman
    • Tag set to 2.1, activerecord, bug, patch, tested

    Try creating a lib/ file or a plugin with this in the init.rb (we have our models in their own plugin, to share between admin and public apps):

    require 'active_record/connection_adapters/postgresql_adapter'

    module FixedQuoting

    1. This is broken in the Rails 2.1.0 PostgreSQL adapter. Doesn't take schema names into account.
    2. This method fixes this...

    def quote_table_name(table_name)

    (table_name.split(/\./, 2).map { |piece| %("#{piece}") }).join('.')

    end

    end

    ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.send(:include, FixedQuoting)

    This seemed to work for me. At least my DB migrations work now.

    August 6th, 2008 @ 05:27 AM

  5. Sean Bowman
    Sean Bowman

    Whoops...those numbers are supposed to be comments!

    August 6th, 2008 @ 05:27 AM

  6. Jeremy Kemper
    Jeremy Kemper
    • Milestone cleared.
    • State changed from open to incomplete

    See the mysql adapter for a quick quoting fix for qualified table names.

    Could you do a patch + test for this?

    August 28th, 2008 @ 07:24 AM

  7. Sean Bowman
    Sean Bowman

    Yes, but it might not be for a week or two.

    August 28th, 2008 @ 08:02 AM

  8. coutinho (at mondriantecnologia)
    coutinho (at mondriantecnologia)
    • Tag changed from 2.1, activerecord, bug, patch, tested to 2.1, active, activerecord, bug, patch, plugin, postgresql, record, tested

    Hello Jeremy, I have a plugin to fix this problem in jruby. My plugin can be adapted to fix the problem in ruby. http://coutinho.mondriantecnolog...

    March 10th, 2009 @ 02:13 PM

  9. Max Lapshin
    Max Lapshin
    • Tag changed from 2.1, active, activerecord, bug, patch, plugin, postgresql, record, tested to 2.3-rc1, active, activerecord, bug, patch, plugin, postgresql, record, tested
    • Assigned user changed from Jeremy Kemper to Tarmo Tänav

    I've added patch, that makes possible to define following table_names:

    table_name "table.name" schema_name.table_name schema_name."table.name" "schema.name".table_name "schema.name"."table.name"

    March 15th, 2009 @ 06:47 PM

  10. Tarmo Tänav
    Tarmo Tänav
    • State changed from incomplete to open

    March 15th, 2009 @ 06:54 PM

  11. Max Lapshin
    Max Lapshin

    Posting patch second time. Now all test over activerecord seems to run. Appended test for proper quoting

    March 15th, 2009 @ 07:05 PM

  12. Elan Feingold
    Elan Feingold

    The patch works great over here, would love to see it in the next release of Rails.

    March 27th, 2009 @ 09:45 PM

  13. Max Lapshin
    Max Lapshin

    Thanks. I've also integrated it into several projects and use it.

    March 28th, 2009 @ 06:12 AM

  14. Pete Deffendol
    Pete Deffendol

    +! - the patch is working great over here. I just dropped it into config/initializers.

    http://gist.github.com/89789

    April 3rd, 2009 @ 04:24 PM

  15. Repository
    Repository
    • State changed from open to resolved

    (from [70de8e64e30092b2b1b77869e459b4868f5b9577]) Support multiple schemas in table names for postgresql [#390 state:resolved]

    Signed-off-by: Pratik Naik pratiknaik@gmail.com http://github.com/rails/rails/co...

    April 5th, 2009 @ 12:40 PM

  16. Repository
    Repository

    (from [60601234708b34c81dcd6a58a0cad79a7520ce10]) Support multiple schemas in table names for postgresql [#390 state:resolved]

    Signed-off-by: Pratik Naik pratiknaik@gmail.com http://github.com/rails/rails/co...

    April 21st, 2009 @ 11:51 AM