Merge remote-tracking branch 'origin/pr/179' into pr179

3 files changed, 99 insertions, 3 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index bc1c3614e..b46c65335 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -401,6 +401,15 @@ The value passed to the keyword argument will be simply passed through to the
 underlying CREATE INDEX command, so it *must* be a valid index type for your
 version of PostgreSQL.
 
+Index Storage Parameters
+^^^^^^^^^^^^^^^^^^^^^^^^
+
+PostgreSQL allows storage parameters to be set on indexes. The storage
+parameters available depend on the index method used by the index. Storage
+parameters can be specified on :class:`.Index` using the ``postgresql_with``
+keyword argument::
+
+    Index('my_index', my_table.c.data, postgresql_with={"fillfactor": 50})
 
 .. _postgresql_index_concurrently:
 
@@ -1592,6 +1601,13 @@ class PGDDLCompiler(compiler.DDLCompiler):
                     ])
                 )
 
+        withclause = index.dialect_options['postgresql']['with']
+
+        if withclause:
+            text += " WITH (%s)" % (', '.join(
+                ['%s = %s' % storage_parameter
+                 for storage_parameter in withclause.items()]))
+
         whereclause = index.dialect_options["postgresql"]["where"]
 
         if whereclause is not None:
@@ -1921,6 +1937,7 @@ class PGDialect(default.DefaultDialect):
             "where": None,
             "ops": {},
             "concurrently": False,
+            "with": {}
         }),
         (schema.Table, {
             "ignore_search_path": False,
@@ -2609,7 +2626,8 @@ class PGDialect(default.DefaultDialect):
               SELECT
                   i.relname as relname,
                   ix.indisunique, ix.indexprs, ix.indpred,
-                  a.attname, a.attnum, NULL, ix.indkey%s
+                  a.attname, a.attnum, NULL, ix.indkey%s,
+                  i.reloptions, am.amname
               FROM
                   pg_class t
                         join pg_index ix on t.oid = ix.indrelid
@@ -2617,6 +2635,9 @@ class PGDialect(default.DefaultDialect):
                         left outer join
                             pg_attribute a
                             on t.oid = a.attrelid and %s
+                        left outer join
+                            pg_am am
+                            on i.relam = am.oid
               WHERE
                   t.relkind IN ('r', 'v', 'f', 'm')
                   and t.oid = :table_oid
@@ -2636,7 +2657,8 @@ class PGDialect(default.DefaultDialect):
               SELECT
                   i.relname as relname,
                   ix.indisunique, ix.indexprs, ix.indpred,
-                  a.attname, a.attnum, c.conrelid, ix.indkey::varchar
+                  a.attname, a.attnum, c.conrelid, ix.indkey::varchar,
+                  i.reloptions, am.amname
               FROM
                   pg_class t
                         join pg_index ix on t.oid = ix.indrelid
@@ -2649,6 +2671,9 @@ class PGDialect(default.DefaultDialect):
                             on (ix.indrelid = c.conrelid and
                                 ix.indexrelid = c.conindid and
                                 c.contype in ('p', 'u', 'x'))
+                        left outer join
+                            pg_am am
+                            on i.relam = am.oid
               WHERE
                   t.relkind IN ('r', 'v', 'f', 'm')
                   and t.oid = :table_oid
@@ -2665,7 +2690,7 @@ class PGDialect(default.DefaultDialect):
 
         sv_idx_name = None
         for row in c.fetchall():
-            idx_name, unique, expr, prd, col, col_num, conrelid, idx_key = row
+            idx_name, unique, expr, prd, col, col_num, conrelid, idx_key, options, amname = row
 
             if expr:
                 if idx_name != sv_idx_name:
@@ -2691,6 +2716,10 @@ class PGDialect(default.DefaultDialect):
                 index['unique'] = unique
                 if conrelid is not None:
                     index['duplicates_constraint'] = idx_name
+                if options:
+                    index['options'] = dict([option.split("=") for option in options])
+                if amname and amname != 'btree':
+                    index['amname'] = amname
 
         result = []
         for name, idx in indexes.items():
@@ -2701,6 +2730,10 @@ class PGDialect(default.DefaultDialect):
             }
             if 'duplicates_constraint' in idx:
                 entry['duplicates_constraint'] = idx['duplicates_constraint']
+            if 'options' in idx:
+                entry.setdefault('dialect_options', {})["postgresql_with"] = idx['options']
+            if 'amname' in idx:
+                entry.setdefault('dialect_options', {})["postgresql_using"] = idx['amname']
             result.append(entry)
         return result
 
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index d5c8d9065..731141604 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -370,6 +370,30 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             'USING hash (data)',
                             dialect=postgresql.dialect())
 
+    def test_create_index_with_with(self):
+        m = MetaData()
+        tbl = Table('testtbl', m, Column('data', String))
+
+        idx1 = Index('test_idx1', tbl.c.data)
+        idx2 = Index('test_idx2', tbl.c.data, postgresql_with={"fillfactor": 50})
+        idx3 = Index('test_idx3', tbl.c.data, postgresql_using="gist",
+                     postgresql_with={"buffering": "off"})
+
+        self.assert_compile(schema.CreateIndex(idx1),
+                            'CREATE INDEX test_idx1 ON testtbl '
+                            '(data)',
+                            dialect=postgresql.dialect())
+        self.assert_compile(schema.CreateIndex(idx2),
+                            'CREATE INDEX test_idx2 ON testtbl '
+                            '(data) '
+                            'WITH (fillfactor = 50)',
+                            dialect=postgresql.dialect())
+        self.assert_compile(schema.CreateIndex(idx3),
+                            'CREATE INDEX test_idx3 ON testtbl '
+                            'USING gist (data) '
+                            'WITH (buffering = off)',
+                            dialect=postgresql.dialect())
+
     def test_create_index_expr_gets_parens(self):
         m = MetaData()
         tbl = Table('testtbl', m, Column('x', Integer), Column('y', Integer))
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index 32e0259aa..ed8a88fd4 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -12,6 +12,7 @@ from sqlalchemy import Table, Column, MetaData, Integer, String, \
 from sqlalchemy import exc
 import sqlalchemy as sa
 from sqlalchemy.dialects.postgresql import base as postgresql
+from sqlalchemy.dialects.postgresql import ARRAY
 
 
 class ForeignTableReflectionTest(fixtures.TablesTest, AssertsExecutionResults):
@@ -673,6 +674,44 @@ class ReflectionTest(fixtures.TestBase):
         conn.close()
 
     @testing.provide_metadata
+    def test_index_reflection_with_storage_options(self):
+        """reflect indexes with storage options set"""
+
+        metadata = self.metadata
+
+        t1 = Table('t', metadata,
+                   Column('id', Integer, primary_key=True),
+                   Column('x', Integer)
+                   )
+        metadata.create_all()
+        conn = testing.db.connect().execution_options(autocommit=True)
+        conn.execute("CREATE INDEX idx1 ON t (x) WITH (fillfactor = 50)")
+
+        ind = testing.db.dialect.get_indexes(conn, "t", None)
+        eq_(ind, [{'unique': False, 'column_names': ['x'], 'name': 'idx1',
+                   'dialect_options': {"postgresql_with": {"fillfactor": "50"}}}])
+        conn.close()
+
+    @testing.provide_metadata
+    def test_index_reflection_with_access_method(self):
+        """reflect indexes with storage options set"""
+
+        metadata = self.metadata
+
+        t1 = Table('t', metadata,
+                   Column('id', Integer, primary_key=True),
+                   Column('x', ARRAY(Integer))
+                   )
+        metadata.create_all()
+        conn = testing.db.connect().execution_options(autocommit=True)
+        conn.execute("CREATE INDEX idx1 ON t USING gin (x)")
+
+        ind = testing.db.dialect.get_indexes(conn, "t", None)
+        eq_(ind, [{'unique': False, 'column_names': ['x'], 'name': 'idx1',
+                   'dialect_options': {'postgresql_using': 'gin'}}])
+        conn.close()
+
+    @testing.provide_metadata
     def test_foreign_key_option_inspection(self):
         metadata = self.metadata
         Table(