diff options
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 39 | ||||
| -rw-r--r-- | test/dialect/postgresql/test_compiler.py | 24 | ||||
| -rw-r--r-- | test/dialect/postgresql/test_reflection.py | 39 |
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( |