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 +++++++++++++++++ 1 file changed, 17 insertions(+) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') 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, -- 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 +++++++++--- 1 file changed, 9 insertions(+), 3 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') 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 -- 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 +++++++++++++--- 1 file changed, 13 insertions(+), 3 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') 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 -- cgit v1.2.1