Fix `primary_keys` across multiple schemas

Fixes #28470.

2 files changed, 9 insertions, 10 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
index a332375b78..2927a76385 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
@@ -362,16 +362,14 @@ module ActiveRecord
         end
 
         def primary_keys(table_name) # :nodoc:
-          scope = quoted_scope(table_name)
           select_values(<<-SQL.strip_heredoc, "SCHEMA")
-            SELECT column_name
-              FROM information_schema.key_column_usage kcu
-              JOIN information_schema.table_constraints tc
-             USING (table_schema, table_name, constraint_name)
-             WHERE constraint_type = 'PRIMARY KEY'
-               AND kcu.table_name = #{scope[:name]}
-               AND kcu.table_schema = #{scope[:schema]}
-             ORDER BY kcu.ordinal_position
+            SELECT a.attname FROM pg_index i
+            CROSS JOIN generate_subscripts(i.indkey, 1) k
+             JOIN pg_attribute a
+               ON a.attrelid = i.indrelid
+              AND a.attnum = i.indkey[k]
+            WHERE i.indrelid = #{quote(quote_table_name(table_name))}::regclass
+              AND i.indisprimary
           SQL
         end
 
diff --git a/activerecord/test/cases/adapters/postgresql/schema_test.rb b/activerecord/test/cases/adapters/postgresql/schema_test.rb
index 7b065ff320..75e30e4fe9 100644
--- a/activerecord/test/cases/adapters/postgresql/schema_test.rb
+++ b/activerecord/test/cases/adapters/postgresql/schema_test.rb
@@ -91,6 +91,7 @@ class SchemaTest < ActiveRecord::PostgreSQLTestCase
     @connection.execute "CREATE INDEX #{INDEX_E_NAME} ON #{SCHEMA_NAME}.#{TABLE_NAME}  USING gin (#{INDEX_E_COLUMN});"
     @connection.execute "CREATE INDEX #{INDEX_E_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME}  USING gin (#{INDEX_E_COLUMN});"
     @connection.execute "CREATE TABLE #{SCHEMA_NAME}.#{PK_TABLE_NAME} (id serial primary key)"
+    @connection.execute "CREATE TABLE #{SCHEMA2_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
@@ -361,7 +362,7 @@ class SchemaTest < ActiveRecord::PostgreSQLTestCase
   end
 
   def test_primary_key_assuming_schema_search_path
-    with_schema_search_path(SCHEMA_NAME) do
+    with_schema_search_path("#{SCHEMA_NAME}, #{SCHEMA2_NAME}") do
       assert_equal "id", @connection.primary_key(PK_TABLE_NAME), "primary key should be found"
     end
   end