- Support has been improved for Postgresql reflection behavior on very old

(pre 8.1) versions of Postgresql, and potentially other PG engines
such as Redshift (assuming Redshift reports the version as < 8.1).
The query for "indexes" as well as "primary keys" relies upon inspecting
a so-called "int2vector" datatype, which refuses to coerce to an array
prior to 8.1 causing failures regarding the "ANY()" operator used
in the query.  Extensive googling has located the very hacky, but
recommended-by-PG-core-developer query to use when PG version < 8.1
is in use, so index and primary key constraint reflection now work
on these versions.

1 files changed, 27 insertions, 22 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 7b9ee799e..cea9d67b6 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2031,35 +2031,22 @@ class PGDialect(default.DefaultDialect):
         table_oid = self.get_table_oid(connection, table_name, schema,
                                        info_cache=kw.get('info_cache'))
 
-        if self.server_version_info < (8, 0):
-            # the shortcoming of this query is that it will
-            # not detect a PK constraint that has been renamed.
-            # This query was removed with #2291, however it was reported
-            # that the newer queries do not work with PG 7 so here
-            # it is restored when old PG versions are detected.
-            PK_SQL = """
-              SELECT attname FROM pg_attribute
-              WHERE attrelid = (
-                 SELECT indexrelid FROM pg_index i
-                 WHERE i.indrelid = :table_oid
-                 AND i.indisprimary = 't')
-              ORDER BY attnum
-            """
-        elif self.server_version_info < (8, 4):
-            # unnest() and generate_subscripts() both introduced in
-            # version 8.4
+        if self.server_version_info < (8, 4):
             PK_SQL = """
                 SELECT a.attname
                 FROM
                     pg_class t
                     join pg_index ix on t.oid = ix.indrelid
                     join pg_attribute a
-                        on t.oid=a.attrelid and a.attnum=ANY(ix.indkey)
+                        on t.oid=a.attrelid AND %s
                  WHERE
                   t.oid = :table_oid and ix.indisprimary = 't'
                 ORDER BY a.attnum
-            """
+            """ % self._pg_index_any("a.attnum", "ix.indkey")
+
         else:
+            # unnest() and generate_subscripts() both introduced in
+            # version 8.4
             PK_SQL = """
                 SELECT a.attname
                 FROM pg_attribute a JOIN (
@@ -2174,6 +2161,21 @@ class PGDialect(default.DefaultDialect):
             fkeys.append(fkey_d)
         return fkeys
 
+    def _pg_index_any(self, col, compare_to):
+        if self.server_version_info < (8, 1):
+            # http://www.postgresql.org/message-id/10279.1124395722@sss.pgh.pa.us
+            # "In CVS tip you could replace this with "attnum = ANY (indkey)".
+            # Unfortunately, most array support doesn't work on int2vector in
+            # pre-8.1 releases, so I think you're kinda stuck with the above
+            # for now.
+            # regards, tom lane"
+            return "(%s)" % " OR ".join(
+                        "%s[%d] = %s" % (compare_to, ind, col)
+                        for ind in range(0, 10)
+                    )
+        else:
+            return "%s = ANY(%s)" % (col, compare_to)
+
     @reflection.cache
     def get_indexes(self, connection, table_name, schema, **kw):
         table_oid = self.get_table_oid(connection, table_name, schema,
@@ -2186,14 +2188,14 @@ class PGDialect(default.DefaultDialect):
           SELECT
               i.relname as relname,
               ix.indisunique, ix.indexprs, ix.indpred,
-              a.attname, a.attnum, ix.indkey::varchar
+              a.attname, a.attnum, ix.indkey%s
           FROM
               pg_class t
                     join pg_index ix on t.oid = ix.indrelid
                     join pg_class i on i.oid=ix.indexrelid
                     left outer join
                         pg_attribute a
-                        on t.oid=a.attrelid and a.attnum=ANY(ix.indkey)
+                        on t.oid=a.attrelid and %s
           WHERE
               t.relkind = 'r'
               and t.oid = :table_oid
@@ -2201,7 +2203,10 @@ class PGDialect(default.DefaultDialect):
           ORDER BY
               t.relname,
               i.relname
-        """
+        """ % (
+                "::varchar" if self.server_version_info >= (8, 1) else "",
+                self._pg_index_any("a.attnum", "ix.indkey")
+            )
 
         t = sql.text(IDX_SQL, typemap={'attname': sqltypes.Unicode})
         c = connection.execute(t, table_oid=table_oid)