Merge pull request #5096 from lawso017/master

Restoring ability to derive id/sequence from tables with nonstandard sequences for primary keys

2 files changed, 43 insertions, 19 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 6b742ed858..24a0ba2612 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -932,26 +932,46 @@ module ActiveRecord
       def pk_and_sequence_for(table) #:nodoc:
         # First try looking for a sequence with a dependency on the
         # given table's primary key.
-        result = exec_query(<<-end_sql, 'SCHEMA').rows.first
-          SELECT attr.attname, ns.nspname, seq.relname
-          FROM pg_class seq
-          INNER JOIN pg_depend dep ON seq.oid = dep.objid
-          INNER JOIN pg_attribute attr ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid
-          INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1]
-          INNER JOIN pg_namespace ns ON seq.relnamespace = ns.oid
-          WHERE seq.relkind  = 'S'
-            AND cons.contype = 'p'
-            AND dep.refobjid = '#{quote_table_name(table)}'::regclass
+        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
         end_sql
 
-        # [primary_key, sequence]
-        if result.second ==  'public' then
-          sequence = result.last
-        else
-          sequence = result.second+'.'+result.last
+        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,
+              CASE
+                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)
+              END
+            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'
+          end_sql
         end
 
-        [result.first, sequence]
+        [result.first, result.last]
       rescue
         nil
       end
diff --git a/activerecord/test/cases/adapters/postgresql/schema_test.rb b/activerecord/test/cases/adapters/postgresql/schema_test.rb
index 18670b4177..18c81d2b09 100644
--- a/activerecord/test/cases/adapters/postgresql/schema_test.rb
+++ b/activerecord/test/cases/adapters/postgresql/schema_test.rb
@@ -24,6 +24,8 @@ class SchemaTest < ActiveRecord::TestCase
     'moment timestamp without time zone default now()'
   ]
   PK_TABLE_NAME = 'table_with_pk'
+  UNMATCHED_SEQUENCE_NAME = 'unmatched_primary_key_default_value_seq'
+  UNMATCHED_PK_TABLE_NAME = 'table_with_unmatched_sequence_for_pk'
 
   class Thing1 < ActiveRecord::Base
     self.table_name = "test_schema.things"
@@ -60,6 +62,8 @@ class SchemaTest < ActiveRecord::TestCase
     @connection.execute "CREATE INDEX #{INDEX_D_NAME} ON #{SCHEMA_NAME}.#{TABLE_NAME}  USING btree (#{INDEX_D_COLUMN} DESC);"
     @connection.execute "CREATE INDEX #{INDEX_D_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME}  USING btree (#{INDEX_D_COLUMN} DESC);"
     @connection.execute "CREATE TABLE #{SCHEMA_NAME}.#{PK_TABLE_NAME} (id serial primary key)"
+    @connection.execute "CREATE SEQUENCE #{SCHEMA_NAME}.#{UNMATCHED_SEQUENCE_NAME}"
+    @connection.execute "CREATE TABLE #{SCHEMA_NAME}.#{UNMATCHED_PK_TABLE_NAME} (id integer NOT NULL DEFAULT nextval('#{SCHEMA_NAME}.#{UNMATCHED_SEQUENCE_NAME}'::regclass), CONSTRAINT unmatched_pkey PRIMARY KEY (id))"
   end
 
   def teardown
@@ -241,12 +245,12 @@ class SchemaTest < ActiveRecord::TestCase
   def test_pk_and_sequence_for_with_schema_specified
     [
       %("#{SCHEMA_NAME}"."#{PK_TABLE_NAME}"),
-      %(#{SCHEMA_NAME}."#{PK_TABLE_NAME}"),
-      %(#{SCHEMA_NAME}.#{PK_TABLE_NAME})
+      %("#{SCHEMA_NAME}"."#{UNMATCHED_PK_TABLE_NAME}")
     ].each do |given|
       pk, seq = @connection.pk_and_sequence_for(given)
       assert_equal 'id', pk, "primary key should be found when table referenced as #{given}"
-      assert_equal "#{SCHEMA_NAME}.#{PK_TABLE_NAME}_id_seq", seq, "sequence name should be found when table referenced as #{given}"
+      assert_equal "#{PK_TABLE_NAME}_id_seq", seq, "sequence name should be found when table referenced as #{given}" if given == %("#{SCHEMA_NAME}"."#{PK_TABLE_NAME}")
+      assert_equal "#{UNMATCHED_SEQUENCE_NAME}", seq, "sequence name should be found when table referenced as #{given}" if given ==  %("#{SCHEMA_NAME}"."#{UNMATCHED_PK_TABLE_NAME}")
     end
   end