If I have a table with two indexes:
"networks_users_network_id_index" unique, btree (network_id, user_id)
"networks_users_user_id_index" btree (user_id, network_id)
This is quite common for join tables.
The schema dump will contain
add_index "networks_users", ["network_id", "user_id"], :name => "networks_users_network_id_index", :unique => true
add_index "networks_users", ["network_id", "user_id"], :name => "networks_users_user_id_index"
Note how the order of the fields in the indexes is the same.
This is because the query that the postgres connector uses doesn't look at the order of the results.
If we change the query from:
SELECT i.relname, d.indisunique, a.attname
FROM pg_class t, pg_class i, pg_index d, pg_attribute a
WHERE i.relkind = 'i'
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = '#{table_name}'
AND a.attrelid = t.oid
AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum
OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum
OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum
OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum
OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )
ORDER BY i.relname
to
SELECT i.relname, d.indisunique, a.attname
FROM pg_class t, pg_class i, pg_index d, pg_attribute a
WHERE i.relkind = 'i'
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = '#{table_name}'
AND a.attrelid = t.oid
AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum
OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum
OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum
OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum
OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )
ORDER BY i.relname, CASE
WHEN d.indkey[0]=a.attnum THEN 0
WHEN d.indkey[1]=a.attnum THEN 1
WHEN d.indkey[2]=a.attnum THEN 2
WHEN d.indkey[3]=a.attnum THEN 3
WHEN d.indkey[4]=a.attnum THEN 4
WHEN d.indkey[5]=a.attnum THEN 5
WHEN d.indkey[6]=a.attnum THEN 6
WHEN d.indkey[7]=a.attnum THEN 7
WHEN d.indkey[8]=a.attnum THEN 8
WHEN d.indkey[9]=a.attnum THEN 9 END
Then the indexes get created in the right order.
I don't like this code, is there a better way of ordering the responses by their position in the indkey array?
