From dff81500b161cf41ff5d9e77c21a24010e1bd93b Mon Sep 17 00:00:00 2001 From: Pete Hollobon Date: Wed, 3 Jun 2015 16:55:58 +0100 Subject: Add support for PostgreSQL index storage parameters Add support for specifying PostgreSQL index storage paramters (e.g. fillfactor). --- lib/sqlalchemy/dialects/postgresql/base.py | 17 +++++++++++++++++ test/dialect/postgresql/test_compiler.py | 24 ++++++++++++++++++++++++ 2 files changed, 41 insertions(+) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 73fe5022a..e0926a852 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: @@ -1919,6 +1935,7 @@ class PGDialect(default.DefaultDialect): "where": None, "ops": {}, "concurrently": False, + "with": {} }), (schema.Table, { "ignore_search_path": False, diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index aa3f80fdc..706b60bd8 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -369,6 +369,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)) -- cgit v1.2.1 From b03ee45f32e53416ce1c73fa15966db17ed4bbf9 Mon Sep 17 00:00:00 2001 From: Pete Hollobon Date: Thu, 4 Jun 2015 15:12:09 +0100 Subject: Add reflection of PostgreSQL index storage options --- lib/sqlalchemy/dialects/postgresql/base.py | 12 +++++++++--- test/dialect/postgresql/test_reflection.py | 20 ++++++++++++++++++++ 2 files changed, 29 insertions(+), 3 deletions(-) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index e0926a852..b9690e214 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2624,7 +2624,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 FROM pg_class t join pg_index ix on t.oid = ix.indrelid @@ -2651,7 +2652,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 FROM pg_class t join pg_index ix on t.oid = ix.indrelid @@ -2680,7 +2682,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 = row if expr: if idx_name != sv_idx_name: @@ -2706,6 +2708,8 @@ 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]) result = [] for name, idx in indexes.items(): @@ -2716,6 +2720,8 @@ 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'] result.append(entry) return result diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index 0ebe68cba..70b81219e 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -672,6 +672,26 @@ class ReflectionTest(fixtures.TestBase): eq_(ind, [{'unique': False, 'column_names': ['y'], 'name': 'idx1'}]) 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_foreign_key_option_inspection(self): metadata = self.metadata -- cgit v1.2.1 From af19435b9c1cec28d0ac93aa832b45601af51597 Mon Sep 17 00:00:00 2001 From: Pete Hollobon Date: Thu, 4 Jun 2015 16:45:41 +0100 Subject: Add reflection of PostgreSQL index access methods (USING clause) --- lib/sqlalchemy/dialects/postgresql/base.py | 16 +++++++++++++--- test/dialect/postgresql/test_reflection.py | 19 +++++++++++++++++++ 2 files changed, 32 insertions(+), 3 deletions(-) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index b9690e214..fa11956ad 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2625,7 +2625,7 @@ class PGDialect(default.DefaultDialect): i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, a.attname, a.attnum, NULL, ix.indkey%s, - i.reloptions + i.reloptions, am.amname FROM pg_class t join pg_index ix on t.oid = ix.indrelid @@ -2633,6 +2633,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 @@ -2653,7 +2656,7 @@ class PGDialect(default.DefaultDialect): i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, a.attname, a.attnum, c.conrelid, ix.indkey::varchar, - i.reloptions + i.reloptions, am.amname FROM pg_class t join pg_index ix on t.oid = ix.indrelid @@ -2666,6 +2669,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 @@ -2682,7 +2688,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, options = row + idx_name, unique, expr, prd, col, col_num, conrelid, idx_key, options, amname = row if expr: if idx_name != sv_idx_name: @@ -2710,6 +2716,8 @@ class PGDialect(default.DefaultDialect): 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(): @@ -2722,6 +2730,8 @@ class PGDialect(default.DefaultDialect): 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_reflection.py b/test/dialect/postgresql/test_reflection.py index 70b81219e..2321a3054 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): @@ -691,6 +692,24 @@ class ReflectionTest(fixtures.TestBase): '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): -- cgit v1.2.1