fix pg indexes query

[Mike Bayer]

pg devs have said we're doing the index column names wrong. the attnames linked to pg_index aren't updated if the column name is changed.

Here's a query that works better:

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname ='sometable'
order by
    t.relname,
    i.relname;

[Former user]

Would something like this (get_indexes in lib/sqlalchemy/dialects/postgresql/base.py, near line 1318) work for IDX_SQL?

          SELECT                                       
              i.relname as relname,   
              ix.indisunique, ix.indexprs, ix.indpred,
              a.attname as column_name
          FROM                        
              pg_class t,
              pg_class i,
              pg_index ix,
              pg_attribute a
          WHERE             
              t.oid = ix.indrelid
              and i.oid = ix.indexrelid
              and a.attrelid = t.oid
              and a.attnum = ANY(ix.indkey)
              and t.relkind = 'r'
              and t.oid = :table_oid
          ORDER BY
              t.relname,
              i.relname

[Former user]

Of course I just realized that now the call to obtain the table_oid is unnecessary if the t.oid = :table_oid part is changed to t.relname = :tablename.

[Former user]

Patch:

diff -r dac31fa95b4416316e8b7c419b08410c42c0b287 lib/sqlalchemy/dialects/postgresql/base.py
--- a/lib/sqlalchemy/dialects/postgresql/base.py        Fri Apr 15 00:43:01 2011 -0400
+++ b/lib/sqlalchemy/dialects/postgresql/base.py        Thu Apr 21 13:17:52 2011 -0500
@@ -1314,13 +1314,27 @@
     def get_indexes(self, connection, table_name, schema, **kw):
         table_oid = self.get_table_oid(connection, table_name, schema,
                                        info_cache=kw.get('info_cache'))
         IDX_SQL = """
-          SELECT c.relname, i.indisunique, i.indexprs, i.indpred,
-            a.attname
-          FROM pg_index i, pg_class c, pg_attribute a
-          WHERE i.indrelid = :table_oid AND i.indexrelid = c.oid
-            AND a.attrelid = i.indexrelid AND i.indisprimary = 'f'
-          ORDER BY c.relname, a.attnum
+          SELECT
+              i.relname as relname,
+              ix.indisunique, ix.indexprs, ix.indpred,
+              a.attname as column_name
+          FROM
+              pg_class t,
+              pg_class i,
+              pg_index ix,
+              pg_attribute a
+          WHERE
+              t.oid = ix.indrelid
+              and i.oid = ix.indexrelid
+              and a.attrelid = t.oid
+              and a.attnum = ANY(ix.indkey)
+              and t.relkind = 'r'
+              and t.oid = :table_oid
+          ORDER BY
+              t.relname,
+              i.relname
         """
         t = sql.text(IDX_SQL, typemap={'attname':sqltypes.Unicode})
         c = connection.execute(t, table_oid=table_oid)

using table_oid is probably better as it allows us to specify the right schema, too.

[Former user]

The following was suggested from the PG folks (as an example), since the above SQL does not work on expression-based indices. Maybe SA wants to exclude those anyway.

create table t (id int not null primary key, txt text);
create index t_weird on t ((substring(txt, 1, id)));
create index t_txt_notnull on t (txt) where txt is not null;
SELECT
    i.relname as relname,
    ix.indisunique, ix.indexprs, ix.indpred,
    a.attname as column_name
  FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
  WHERE
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.oid = (select oid from pg_class where relname = 't')
  ORDER BY
    t.relname,
    i.relname
;

[Mike Bayer]

we exclude expression indexes. the query I have at the top from stackoverflow seems fine though will try your example as well.

[Former user]

Also a problem for 0.7 (just tested latest)

[Mike Bayer]

key aspect #1 on this - this is PG 9 only

[Mike Bayer]

needed to get a little more creative but the patch was mostly there, thanks !

ef654af35ab2443172eb79e6f279792b72980539 f50e686f77e520e1c8a93de47f9ad633d2777400

[Mike Bayer]

Removing milestone: 0.6.8 (automated comment)