Postgresql adapter has lost ability to detect sequences based upon primary key default values #5094

lawso017 opened this Issue Feb 20, 2012 · 3 comments

2 participants


After a Rails 3.0.10 -> 3.1.3 upgrade, noticed that my generated schema.rb file was no longer showing primary keys (i.e. was generating :id => false) for tables where the underlying Postgresql sequence no longer shared the name of the table.

This occurred in a long-running Rails app where these tables had been renamed during refactorings. As a result, the name of the sequence was encoded in the table's primary key default value and no longer corresponds to the table.

The below well-intentioned commit:


removed the decision branch from pk_and_sequence_for necessary to infer sequence names from a primary key's default value.

The method pk_and_sequence_for needs to be restored to the state prior to this commit. The logic removed is not specific to Postgres < 8.2, but rather compatible with Postgres > 8.1.

This is my first time spelunking through the AR code. I'll fork and see if I can figure out how to test this change, but otherwise am monkey-patching to get things running again.

The change above affects 3.1 stable as well as Master.


This patch restores prior functionality:

module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter
      # Returns a table's primary key and belonging sequence.
      def pk_and_sequence_for(table) #:nodoc:
        # First try looking for a sequence with a dependency on the
        # given table's primary key.
        result = query(<<-end_sql, 'PK and serial sequence')[0]
          SELECT attr.attname, seq.relname
          FROM pg_class      seq,
               pg_attribute  attr,
               pg_depend     dep,
               pg_namespace  name,
               pg_constraint cons
          WHERE seq.oid           = dep.objid
            AND seq.relkind       = 'S'
            AND 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      = '#{quote_table_name(table)}'::regclass

        if result.nil? or result.empty?
          # If that fails, try parsing the primary key's default value.
          # Support the 7.x and 8.0 nextval('foo'::text) as well as
          # the 8.1+ nextval('foo'::regclass).
          result = query(<<-end_sql, 'PK and custom sequence')[0]
            SELECT attr.attname,
                WHEN split_part(def.adsrc, '''', 2) ~ '.' THEN
                  substr(split_part(def.adsrc, '''', 2),
                         strpos(split_part(def.adsrc, '''', 2), '.')+1)
                ELSE split_part(def.adsrc, '''', 2)
            FROM pg_class       t
            JOIN pg_attribute   attr ON (t.oid = attrelid)
            JOIN pg_attrdef     def  ON (adrelid = attrelid AND adnum = attnum)
            JOIN pg_constraint  cons ON (conrelid = adrelid AND adnum = conkey[1])
            WHERE t.oid = '#{quote_table_name(table)}'::regclass
              AND cons.contype = 'p'
              AND def.adsrc ~* 'nextval'

        # [primary_key, sequence]
        [result.first, result.last]
Ruby on Rails member

Closing this as #5096 merged.

Thanks @lawso017

Do we need to backport this change into 3-1-stable and 3-2-stable also?

@arunagw arunagw closed this Feb 20, 2012
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment