diff options
author | Iuri de Silvio <iurisilvio@gmail.com> | 2016-04-27 10:27:00 -0500 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2016-06-08 14:41:05 -0400 |
commit | 31a0da32a8af2503c6b94123a0e869816d83c707 (patch) | |
tree | f4ee475d51518e3216ede8df3ad4bf1e5ba18c33 | |
parent | 24d29a63b6a30d1f1cd4d1097c021be0302c7922 (diff) | |
download | sqlalchemy-31a0da32a8af2503c6b94123a0e869816d83c707.tar.gz |
Support `postgresql_concurrently` on index dropping.
Also adds version detection so that DROP INDEX CONCURRENTLY
only renders if the Postgresql version is 9.2 or higher;
for CREATE INDEX CONCURRENTLY, version detection is added
to allow the phrase to omit if the Postgresql version is
less than 8.2.
Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Change-Id: I597287e0ebbbe256c957a3579b58ace6848ab4f4
Pull-request: https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/84
-rw-r--r-- | doc/build/changelog/changelog_11.rst | 11 | ||||
-rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 41 | ||||
-rw-r--r-- | test/dialect/postgresql/test_compiler.py | 26 |
3 files changed, 73 insertions, 5 deletions
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index fe56c4cf3..8d20ef257 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -22,6 +22,17 @@ :version: 1.1.0b1 .. change:: + :tags: feature, postgresql + :pullreq: bitbucket:84 + + The DDL for DROP INDEX will emit "CONCURRENTLY" if the + ``postgresql_concurrently`` flag is set upon the + :class:`.Index` and if the database in use is detected as + Postgresql version 9.2 or greater. For CREATE INDEX, database + version detection is also added which will omit the clause if + PG version is less than 8.2. Pull request courtesy Iuri de Silvio. + + .. change:: :tags: bug, orm :tickets: 3708 diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ec20c4b7a..16b22129a 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -436,11 +436,21 @@ flag ``postgresql_concurrently`` to the :class:`.Index` construct:: idx1 = Index('test_idx1', tbl.c.data, postgresql_concurrently=True) -The above index construct will render SQL as:: +The above index construct will render DDL for CREATE INDEX, assuming +Postgresql 8.2 or higher is detected or for a connection-less dialect, as:: CREATE INDEX CONCURRENTLY test_idx1 ON testtbl (data) -.. versionadded:: 0.9.9 +For DROP INDEX, assuming Postgresql 9.2 or higher is detected or for +a connection-less dialect, it will emit:: + + DROP INDEX CONCURRENTLY test_idx1 + +.. versionadded:: 1.1 support for CONCURRENTLY on DROP INDEX. The + CONCURRENTLY keyword is now only emitted if a high enough version + of Postgresql is detected on the connection (or for a connection-less + dialect). + .. _postgresql_index_reflection: @@ -1274,9 +1284,10 @@ class PGDDLCompiler(compiler.DDLCompiler): text += "UNIQUE " text += "INDEX " - concurrently = index.dialect_options['postgresql']['concurrently'] - if concurrently: - text += "CONCURRENTLY " + if self.dialect._supports_create_index_concurrently: + concurrently = index.dialect_options['postgresql']['concurrently'] + if concurrently: + text += "CONCURRENTLY " text += "%s ON %s " % ( self._prepared_index_name(index, @@ -1327,6 +1338,19 @@ class PGDDLCompiler(compiler.DDLCompiler): text += " WHERE " + where_compiled return text + def visit_drop_index(self, drop): + index = drop.element + + text = "\nDROP INDEX " + + if self.dialect._supports_drop_index_concurrently: + concurrently = index.dialect_options['postgresql']['concurrently'] + if concurrently: + text += "CONCURRENTLY " + + text += self._prepared_index_name(index, include_schema=True) + return text + def visit_exclude_constraint(self, constraint, **kw): text = "" if constraint.name is not None: @@ -1688,6 +1712,8 @@ class PGDialect(default.DefaultDialect): reflection_options = ('postgresql_ignore_search_path', ) _backslash_escapes = True + _supports_create_index_concurrently = True + _supports_drop_index_concurrently = True def __init__(self, isolation_level=None, json_serializer=None, json_deserializer=None, **kwargs): @@ -1716,6 +1742,11 @@ class PGDialect(default.DefaultDialect): "show standard_conforming_strings" ) == 'off' + self._supports_create_index_concurrently = \ + self.server_version_info >= (8, 2) + self._supports_drop_index_concurrently = \ + self.server_version_info >= (9, 2) + def on_connect(self): if self.isolation_level is not None: def connect(conn): diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index c8dc9582a..f85ff2682 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -485,6 +485,32 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): "CREATE INDEX CONCURRENTLY test_idx1 ON testtbl (data)" ) + dialect_8_1 = postgresql.dialect() + dialect_8_1._supports_create_index_concurrently = False + self.assert_compile( + schema.CreateIndex(idx1), + "CREATE INDEX test_idx1 ON testtbl (data)", + dialect=dialect_8_1 + ) + + def test_drop_index_concurrently(self): + m = MetaData() + tbl = Table('testtbl', m, Column('data', Integer)) + + idx1 = Index('test_idx1', tbl.c.data, postgresql_concurrently=True) + self.assert_compile( + schema.DropIndex(idx1), + "DROP INDEX CONCURRENTLY test_idx1" + ) + + dialect_9_1 = postgresql.dialect() + dialect_9_1._supports_drop_index_concurrently = False + self.assert_compile( + schema.DropIndex(idx1), + "DROP INDEX test_idx1", + dialect=dialect_9_1 + ) + def test_exclude_constraint_min(self): m = MetaData() tbl = Table('testtbl', m, |