This project is archived and is in readonly mode.

#390 ✓resolved
Olek Poplavsky

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

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

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

Comments and changes to this ticket

  • Jeremy Kemper

    Jeremy Kemper June 11th, 2008 @ 08:40 PM

    • 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'
  • Olek Poplavsky

    Olek Poplavsky June 16th, 2008 @ 11:17 PM

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

  • Torben Wölm

    Torben Wölm June 18th, 2008 @ 10:32 PM

    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?

  • Sean Bowman

    Sean Bowman August 6th, 2008 @ 05:27 AM

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

  • Sean Bowman

    Sean Bowman August 6th, 2008 @ 05:27 AM

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

  • Jeremy Kemper

    Jeremy Kemper August 28th, 2008 @ 07:24 AM

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

  • Sean Bowman

    Sean Bowman August 28th, 2008 @ 08:02 AM

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

  • coutinho (at mondriantecnologia)

    coutinho (at mondriantecnologia) March 10th, 2009 @ 02:13 PM

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

  • Max Lapshin

    Max Lapshin March 15th, 2009 @ 06:47 PM

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

  • Tarmo Tänav

    Tarmo Tänav March 15th, 2009 @ 06:54 PM

    • State changed from “incomplete” to “open”
  • Max Lapshin

    Max Lapshin March 15th, 2009 @ 07:05 PM

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

  • Elan Feingold

    Elan Feingold March 27th, 2009 @ 09:45 PM

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

  • Max Lapshin

    Max Lapshin March 28th, 2009 @ 06:12 AM

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

  • Pete Deffendol

    Pete Deffendol April 3rd, 2009 @ 04:24 PM

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

    http://gist.github.com/89789

  • Repository

    Repository April 5th, 2009 @ 12:40 PM

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

  • Repository

    Repository April 21st, 2009 @ 11:51 AM

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

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="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Attachments

Tags

Referenced by

Pages