diff options
author | Jordan Lewis <jordanthelewis@gmail.com> | 2017-02-10 02:35:24 -0500 |
---|---|---|
committer | Jordan Lewis <jordanthelewis@gmail.com> | 2017-02-13 15:20:21 -0500 |
commit | b8e3af79ed0c2121c11bbb675dbf47126e95e0a0 (patch) | |
tree | 0eef428894b74cb1d87c08c9e238846243cd8aad /activerecord/lib | |
parent | b9bee5e895e218b17a5f4ad72c3cbf6204fa4145 (diff) | |
download | rails-b8e3af79ed0c2121c11bbb675dbf47126e95e0a0.tar.gz rails-b8e3af79ed0c2121c11bbb675dbf47126e95e0a0.tar.bz2 rails-b8e3af79ed0c2121c11bbb675dbf47126e95e0a0.zip |
Simplify and optimize Postgres query for primary_keys()
primary_keys(table) needs to query various metadata tables in Postgres
to determine the primary key for the table. Previously, it did so using
a complex common table expression against pg_constraint and
pg_attribute.
This patch simplifies the query by using information_schema tables.
This simplifies the logic, making the query far easier to understand,
and additionally avoids an expensive unnest, window function query, and
common table expression.
Diffstat (limited to 'activerecord/lib')
-rw-r--r-- | activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb | 20 |
1 files changed, 10 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 eebc688686..863a0dd997 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -430,17 +430,17 @@ module ActiveRecord end def primary_keys(table_name) # :nodoc: + name = Utils.extract_schema_qualified_name(table_name.to_s) select_values(<<-SQL.strip_heredoc, "SCHEMA") - WITH pk_constraint AS ( - SELECT conrelid, unnest(conkey) AS connum FROM pg_constraint - WHERE contype = 'p' - AND conrelid = #{quote(quote_table_name(table_name))}::regclass - ), cons AS ( - SELECT conrelid, connum, row_number() OVER() AS rownum FROM pk_constraint - ) - SELECT attr.attname FROM pg_attribute attr - INNER JOIN cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.connum - ORDER BY cons.rownum + SELECT column_name + FROM information_schema.key_column_usage kcu + JOIN information_schema.table_constraints tc + ON kcu.table_name = tc.table_name + AND kcu.table_schema = tc.table_schema + AND kcu.constraint_name = tc.constraint_name + WHERE constraint_type = 'PRIMARY KEY' + AND kcu.table_name = #{quote(name.identifier)} + AND kcu.table_schema = #{name.schema ? quote(name.schema) : "ANY (current_schemas(false))"} SQL end |