This project is archived and is in readonly mode.

#6032 ✓stale
Robert Pankowecki

The postgres connection adaptor does not correctly identify Primary Keys in Views and Inherited Tables

Reported by Robert Pankowecki | November 22nd, 2010 @ 01:16 PM


 UnitCurrentAttribute.where(UnitCurrentAttribute.arel_table[:id].eq(5))
 NoMethodError: undefined method `eq' for nil:NilClass


ruby-1.9.2-head > UnitCurrentAttribute.arel_table
#<Arel::Table:0xb146080 @name="unit_attributes_closest_now", @engine=ActiveRecord::Base, @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil, @table_exists=false>

ruby-1.9.2-head > Unit.all.map(&:property)
[
    [0] #<UnitCurrentAttribute:0xb0875cc> {
                    :id => 1,
               :unit_id => "00400002",
               :name_id => "21fc433eab7b0f0298d6e164cbdd2052",
         :short_name_id => "d5d1fe8583fa57c3fe30753f49452321",
        :description_id => "bcaecf06473d29d72fe0a2d03f6cbc3d",
            :valid_from => Wed, 03 Nov 2010,
              :valid_to => Thu, 03 Nov 2011,
            :created_at => Wed, 03 Nov 2010 13:05:19 UTC +00:00,
            :updated_at => Wed, 03 Nov 2010 13:05:19 UTC +00:00
    }
]

Comments and changes to this ticket

  • rails

    rails February 23rd, 2011 @ 12:00 AM

    • State changed from “new” to “open”

    This issue has been automatically marked as stale because it has not been commented on for at least three months.

    The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

    Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

  • rails

    rails February 23rd, 2011 @ 12:00 AM

    • State changed from “open” to “stale”
  • johno

    johno March 9th, 2011 @ 01:34 AM

    I've had this same problem with updatable views and managed to find out why.

    .arel_table returns nil on primary_key (since view has no primary key definition), furthermore table_exists? returns false for views. Here is my workaround.

    module UpdatableView
      def arel_table
        @arel_table ||= Arel::UpdatableView.new(table_name, arel_engine)
      end
    end
    
    # AREL MEGAHACK
    # assume id as primary key
    class Arel::UpdatableView < Arel::Table
      def primary_key
        @primary_key ||= self[:id]
      end
    
      def table_exists?
        true
      end
    end
    

    Then use this in your view like this

    class MyView < Activerecord::Base
      extend UpdatableView
    end
    
  • bingbing
  • omarqureshi

    omarqureshi April 12th, 2011 @ 03:14 PM

    This is also a problem if you are using inherited tables. Although in that case, you can get by, through using set_primary_key "id", for views however, it doesn't look like it works so well here.

    I'm going to take some time over the weekend in order to get the arel table definition to properly recognize primary keys for views and inherited tables

  • omarqureshi

    omarqureshi April 17th, 2011 @ 03:53 PM

    • Tag changed from database views, arel, arel2, postgresql to database views, activerecord, arel, arel2, postgresql

    Problem method found:

      def primary_key(table)
        row = exec_query(<<-end_sql, 'SCHEMA', [[nil, table]]).rows.first
          SELECT DISTINCT(attr.attname)
          FROM pg_attribute  attr,
               pg_depend     dep,
               pg_namespace  name,
               pg_constraint cons
          WHERE attr.attrelid     = dep.refobjid
            AND attr.attnum       = dep.refobjsubid
            AND attr.attrelid     = cons.conrelid
            AND attr.attnum       = cons.conkey[1]
            AND cons.contype      = 'p'
            AND dep.refobjid      = $1::regclass
        end_sql
    
        row && row.first
      end
    

    as part of https://github.com/rails/rails/raw/master/activerecord/lib/active_r...

    called by Arel by @engine.connection.primary_key(name)

  • omarqureshi

    omarqureshi April 17th, 2011 @ 03:55 PM

    • Tag changed from database views, activerecord, arel, arel2, postgresql to database views, activerecord, arel, arel2, postgres-inherited-tables, postgresql
  • omarqureshi

    omarqureshi April 17th, 2011 @ 03:56 PM

    • Title changed from “Arel 2 does not work well with views on postgresql” to “The postgres connection adaptor does not correctly identify Primary Keys in Views and Inherited Tables”
  • omarqureshi

    omarqureshi April 17th, 2011 @ 04:09 PM

    I wonder if instead of the primary key fix being applied here, it should be applied closer to the adaptor - as in something like

    (row && row.first) || "id"
    

    with it yielding way if set_primary_key was used in the class?

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>

Pages