From 4b164935481c44c126500761f873c5b0c5bdbe80 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 1 Apr 2015 16:50:32 -0400 Subject: - Fixed bug where updated PG index reflection as a result of :ticket:`3184` would cause index operations to fail on Postgresql versions 8.4 and earlier. The enhancements are now disabled when using an older version of Postgresql. fixes #3343 --- doc/build/changelog/changelog_10.rst | 9 ++++ lib/sqlalchemy/dialects/postgresql/base.py | 84 +++++++++++++++++++----------- test/dialect/postgresql/test_reflection.py | 1 + test/requirements.py | 2 +- 4 files changed, 64 insertions(+), 32 deletions(-) diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst index 1b1ab383f..597c8adcd 100644 --- a/doc/build/changelog/changelog_10.rst +++ b/doc/build/changelog/changelog_10.rst @@ -18,6 +18,15 @@ .. changelog:: :version: 1.0.0b5 + .. change:: + :tags: bug, postgresql + :tickets: 3343 + + Fixed bug where updated PG index reflection as a result of + :ticket:`3184` would cause index operations to fail on Postgresql + versions 8.4 and earlier. The enhancements are now + disabled when using an older version of Postgresql. + .. change:: :tags: bug, sql :tickets: 3346 diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 7529c6ed3..c1c0ab08e 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2601,37 +2601,59 @@ class PGDialect(default.DefaultDialect): # cast indkey as varchar since it's an int2vector, # returned as a list by some drivers such as pypostgresql - IDX_SQL = """ - SELECT - i.relname as relname, - ix.indisunique, ix.indexprs, ix.indpred, - a.attname, a.attnum, c.conrelid, ix.indkey%s - FROM - pg_class t - join pg_index ix on t.oid = ix.indrelid - join pg_class i on i.oid = ix.indexrelid - left outer join - pg_attribute a - on t.oid = a.attrelid and %s - left outer join - pg_constraint c - on (ix.indrelid = c.conrelid and - ix.indexrelid = c.conindid and - c.contype in ('p', 'u', 'x')) - WHERE - t.relkind IN ('r', 'v', 'f', 'm') - and t.oid = :table_oid - and ix.indisprimary = 'f' - ORDER BY - t.relname, - i.relname - """ % ( - # version 8.3 here was based on observing the - # cast does not work in PG 8.2.4, does work in 8.3.0. - # nothing in PG changelogs regarding this. - "::varchar" if self.server_version_info >= (8, 3) else "", - self._pg_index_any("a.attnum", "ix.indkey") - ) + if self.server_version_info < (8, 5): + IDX_SQL = """ + SELECT + i.relname as relname, + ix.indisunique, ix.indexprs, ix.indpred, + a.attname, a.attnum, NULL, ix.indkey%s + FROM + pg_class t + join pg_index ix on t.oid = ix.indrelid + join pg_class i on i.oid = ix.indexrelid + left outer join + pg_attribute a + on t.oid = a.attrelid and %s + WHERE + t.relkind IN ('r', 'v', 'f', 'm') + and t.oid = :table_oid + and ix.indisprimary = 'f' + ORDER BY + t.relname, + i.relname + """ % ( + # version 8.3 here was based on observing the + # cast does not work in PG 8.2.4, does work in 8.3.0. + # nothing in PG changelogs regarding this. + "::varchar" if self.server_version_info >= (8, 3) else "", + self._pg_index_any("a.attnum", "ix.indkey") + ) + else: + IDX_SQL = """ + SELECT + i.relname as relname, + ix.indisunique, ix.indexprs, ix.indpred, + a.attname, a.attnum, c.conrelid, ix.indkey::varchar + FROM + pg_class t + join pg_index ix on t.oid = ix.indrelid + join pg_class i on i.oid = ix.indexrelid + left outer join + pg_attribute a + on t.oid = a.attrelid and a.attnum = ANY(ix.indkey) + left outer join + pg_constraint c + on (ix.indrelid = c.conrelid and + ix.indexrelid = c.conindid and + c.contype in ('p', 'u', 'x')) + WHERE + t.relkind IN ('r', 'v', 'f', 'm') + and t.oid = :table_oid + and ix.indisprimary = 'f' + ORDER BY + t.relname, + i.relname + """ t = sql.text(IDX_SQL, typemap={'attname': sqltypes.Unicode}) c = connection.execute(t, table_oid=table_oid) diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index 0dda1fa45..0ebe68cba 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -817,6 +817,7 @@ class ReflectionTest(fixtures.TestBase): }]) @testing.provide_metadata + @testing.only_on("postgresql>=8.5") def test_reflection_with_unique_constraint(self): insp = inspect(testing.db) diff --git a/test/requirements.py b/test/requirements.py index 67bdfb8a3..3ed6bea4d 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -372,7 +372,7 @@ class DefaultRequirements(SuiteRequirements): @property def window_functions(self): return only_if([ - "postgresql", "mssql", "oracle" + "postgresql>=8.4", "mssql", "oracle" ], "Backend does not support window functions") @property -- cgit v1.2.1