This project is archived and is in readonly mode.

#5959 ✓resolved
Kieran McCusker

It is no longer possible to use Non-Integer id columns in rails 3

Reported by Kieran McCusker | November 12th, 2010 @ 12:35 PM


I am only raising this here as I have spent quite a few hours searching for why my 2.3.8 project no longer works in rails 3 and I thought someone else could find this post.

I am using Postgresql and uuids (guids) as the primary key (id) column.

This works fine in 2.3.8 but fails silently in rails 3.0.1. A cursory glance at Activerecord suggests it now insists on integer primary key id's.

e.g. On my box

PUBLISH::Document.first('e06ae583-bea5-474e-8c68-9a9d96caeefd') works fine in 2.3.8

but fails with
TypeError: can't convert String into Integer

    from /usr/lib/ruby/gems/1.8/gems/activerecord-3.0.1/lib/active_record/relation/finder_methods.rb:117:in `first'
    from /usr/lib/ruby/gems/1.8/gems/activerecord-3.0.1/lib/active_record/relation/finder_methods.rb:117:in `first'
    from /usr/lib/ruby/gems/1.8/gems/activerecord-3.0.1/lib/active_record/base.rb:439:in `__send__'

in rails 3.0.1

    from /usr/lib/ruby/gems/1.8/gems/activerecord-3.0.1/lib/active_record/base.rb:439:in `first'

PUBLISH::Document.create! creates the database record but reports the id as nil in 3.0.1

As this was supported in 2.3, but not in 3.0.1, I think this should be made more explicit in the upgrade guidance.

Many Thanks


Comments and changes to this ticket

  • Andrew White

    Andrew White November 12th, 2010 @ 01:05 PM

    • Importance changed from “” to “Low”

    PUBLISH::Document.first(n) returns the first n records - you want find(id) instead.

  • Kieran McCusker

    Kieran McCusker November 12th, 2010 @ 01:47 PM

    Yep, sorry about that, but the central problem still stands that id returns nil and relations do not work e.g.

    d = PUBLISH::Document.create!

    Does not create a document with a chapter belonging to it in the database as it does in 2.3.8

  • Jason Langenauer

    Jason Langenauer November 13th, 2010 @ 09:52 PM

    I think you need to investigate this a bit further. I've got a Rails 3.0.1 app which also uses non-Integer primary keys (in my case, just a string though), and I'm able to do things like


    which works find.

    I suspect this may be due to the string to integer coercion which ActiveRecord::Base.find does to allow use to say things like Thing.find(params[:id]), when the integer id in the params hash is actually a string. So this may be a bug specific to GUID/UUID-type identifiers, rather than all non-integer primary keys.

  • Kieran McCusker

    Kieran McCusker November 22nd, 2010 @ 06:06 PM

    I'm a little further down the line, and have updated to Activerecord 3.0.3

    I have a postgresql table with a column {id uuid} as the primary key. To get d = PUBLISH::Document.create! to work I have replaced pk_and_sequence_for in the postgresql activerecord adapter with :-

      def pk_and_sequence_for(table) #:nodoc:
        # Look for a single column primary key
        # If it is a sequence then return the sequence 
         result = query(<<-end_sql, 'PK and serial sequence')[0]
                 WHEN d.adsrc ~* '^nextval\\(' THEN
                     WHEN split_part(d.adsrc, '''', 2) ~ '.' THEN substr(split_part(d.adsrc, '''', 2), strpos(split_part(d.adsrc, '''', 2), '.')+1)
                     ELSE split_part(d.adsrc, '''', 2)
                 ELSE NULL
           FROM pg_attribute a JOIN pg_attrdef d 
           ON a.attrelid = d.adrelid AND a.attnum = d.adnum
           join pg_constraint c on c.conrelid = a.attrelid and a.attnum = any (c.conkey)
           WHERE c.contype = 'p' and a.attrelid = '#{quote_table_name(table)}'::regclass
           AND a.attnum > 0 AND NOT a.attisdropped
           group by 1
           having count(*) = 1
         # [primary_key, sequence]

    i.e. I will take as my primary key a single column defined as a primary key that also has default. If the default is a sequence I will return that as the sequence. This seems to me to be what the original code is striving for.

    As a side point in our production database the old code returned almost 1000 rows in the first select in the old function and was almost an order of magnitude slower.

    There are a couple of other changes I needed to the adapter as well

    1. In the quote function I needed

      elsif value.kind_of?(String) && column.sql_type == 'uuid'

    Otherwise when I do update_attribute I get an extra set of quotes around the uuid which causes the sql to fail.

    1. In table_exists? function I needed

      exists =
        query(<<-SQL).first[0].to_i > 0
           SELECT COUNT(*)
           FROM pg_tables
           WHERE tablename = '#{table.gsub(/(^"|"$)/,'')}'
           #{schema ? "AND schemaname = '#{schema}'" : ''}
      if !exists
        ## Check if it is actually a view
        exists = 
          query(<<-SQL).first[0].to_i > 0
             SELECT COUNT(*)
             FROM pg_views
             WHERE viewname = '#{table.gsub(/(^"|"$)/,'')}'
             #{schema ? "AND schemaname = '#{schema}'" : ''}

    as one of our tables is actually a view - Although I suppose I could recode this quite easily

  • Neeraj Singh

    Neeraj Singh November 22nd, 2010 @ 07:19 PM

    Can you show us your migration?

  • Kieran McCusker

    Kieran McCusker November 23rd, 2010 @ 09:48 AM

    Sorry this application goes back to 2006 - We've never implemented migrations

  • Neeraj Singh

    Neeraj Singh November 23rd, 2010 @ 09:49 AM

    • State changed from “new” to “resolved”
  • Kieran McCusker

    Kieran McCusker November 23rd, 2010 @ 10:23 AM


    I wonder if you could you say how this has been resolved? My original question was about changes in the way 3.0 Activerecord worked breaking current applications in an undocumented way. Are thinking this is just an application written in 2006? It has been under development ever since and is currently in production under 2.3.8

    The changes I made to the postgresql adapter resolve these issues, but I suspect that you are not thinking of looking at or merging them. Have you updated the upgrade guidance with the new requirement that postgresql now can only use serial columns as primary keys?

    Many thanks


  • Kieran McCusker

    Kieran McCusker November 23rd, 2010 @ 10:25 AM

    Sorry for posting again, but actually I didn't have any migrations anyway - I was simply trying to get the application working under Rails 3.0

  • wtn

    wtn November 23rd, 2010 @ 12:04 PM

    Can you dump the schema and show us that?

  • Kieran McCusker

    Kieran McCusker November 23rd, 2010 @ 12:22 PM

    There are loads of tables in the schema. In terms of the problem table the first three columns look like (with primary key shown) :-

    CREATE TABLE publishing.documents
    ( id uuid NOT NULL DEFAULT uuid_generate_v4(), version_no integer DEFAULT 2, created_by integer, PRIMARY KEY (id) )

    Is this what you wanted?

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>