From ecdfc31774e226b9f57701ae197d1bcbaf6afa24 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 18 Feb 2014 18:35:23 -0500 Subject: [PATCH] - Support has been improved for Postgresql reflection behavior on very old (pre 8.1) versions of Postgresql, and potentially other PG engines such as Redshift (assuming Redshift reports the version as < 8.1). The query for "indexes" as well as "primary keys" relies upon inspecting a so-called "int2vector" datatype, which refuses to coerce to an array prior to 8.1 causing failures regarding the "ANY()" operator used in the query. Extensive googling has located the very hacky, but recommended-by-PG-core-developer query to use when PG version < 8.1 is in use, so index and primary key constraint reflection now work on these versions. --- doc/build/changelog/changelog_09.rst | 18 +++++++- lib/sqlalchemy/dialects/postgresql/base.py | 49 ++++++++++++---------- test/dialect/postgresql/test_dialect.py | 1 + test/dialect/postgresql/test_query.py | 6 ++- test/dialect/postgresql/test_reflection.py | 8 ++-- test/dialect/postgresql/test_types.py | 4 +- 6 files changed, 55 insertions(+), 31 deletions(-) diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst index caf5ced079..f8a9c173e8 100644 --- a/doc/build/changelog/changelog_09.rst +++ b/doc/build/changelog/changelog_09.rst @@ -14,6 +14,20 @@ .. changelog:: :version: 0.9.3 + .. change:: + :tags: postgresql, bug + + Support has been improved for Postgresql reflection behavior on very old + (pre 8.1) versions of Postgresql, and potentially other PG engines + such as Redshift (assuming Redshift reports the version as < 8.1). + The query for "indexes" as well as "primary keys" relies upon inspecting + a so-called "int2vector" datatype, which refuses to coerce to an array + prior to 8.1 causing failures regarding the "ANY()" operator used + in the query. Extensive googling has located the very hacky, but + recommended-by-PG-core-developer query to use when PG version < 8.1 + is in use, so index and primary key constraint reflection now work + on these versions. + .. change:: :tags: sqlite, bug @@ -58,8 +72,8 @@ Added server version detection to the newly added dialect startup query for "show standard_conforming_strings"; as this variable was - added as of PG 8.2, we skip the query for PG versions older than - that as well as for backends like Redshift. + added as of PG 8.2, we skip the query for PG versions who report a + version string earlier than that. .. change:: :tags: bug, orm, declarative diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 7b9ee799e1..cea9d67b69 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2031,35 +2031,22 @@ class PGDialect(default.DefaultDialect): table_oid = self.get_table_oid(connection, table_name, schema, info_cache=kw.get('info_cache')) - if self.server_version_info < (8, 0): - # the shortcoming of this query is that it will - # not detect a PK constraint that has been renamed. - # This query was removed with #2291, however it was reported - # that the newer queries do not work with PG 7 so here - # it is restored when old PG versions are detected. - PK_SQL = """ - SELECT attname FROM pg_attribute - WHERE attrelid = ( - SELECT indexrelid FROM pg_index i - WHERE i.indrelid = :table_oid - AND i.indisprimary = 't') - ORDER BY attnum - """ - elif self.server_version_info < (8, 4): - # unnest() and generate_subscripts() both introduced in - # version 8.4 + if self.server_version_info < (8, 4): PK_SQL = """ SELECT a.attname FROM pg_class t join pg_index ix on t.oid = ix.indrelid join pg_attribute a - on t.oid=a.attrelid and a.attnum=ANY(ix.indkey) + on t.oid=a.attrelid AND %s WHERE t.oid = :table_oid and ix.indisprimary = 't' ORDER BY a.attnum - """ + """ % self._pg_index_any("a.attnum", "ix.indkey") + else: + # unnest() and generate_subscripts() both introduced in + # version 8.4 PK_SQL = """ SELECT a.attname FROM pg_attribute a JOIN ( @@ -2174,6 +2161,21 @@ class PGDialect(default.DefaultDialect): fkeys.append(fkey_d) return fkeys + def _pg_index_any(self, col, compare_to): + if self.server_version_info < (8, 1): + # http://www.postgresql.org/message-id/10279.1124395722@sss.pgh.pa.us + # "In CVS tip you could replace this with "attnum = ANY (indkey)". + # Unfortunately, most array support doesn't work on int2vector in + # pre-8.1 releases, so I think you're kinda stuck with the above + # for now. + # regards, tom lane" + return "(%s)" % " OR ".join( + "%s[%d] = %s" % (compare_to, ind, col) + for ind in range(0, 10) + ) + else: + return "%s = ANY(%s)" % (col, compare_to) + @reflection.cache def get_indexes(self, connection, table_name, schema, **kw): table_oid = self.get_table_oid(connection, table_name, schema, @@ -2186,14 +2188,14 @@ class PGDialect(default.DefaultDialect): SELECT i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, - a.attname, a.attnum, ix.indkey::varchar + a.attname, a.attnum, 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 a.attnum=ANY(ix.indkey) + on t.oid=a.attrelid and %s WHERE t.relkind = 'r' and t.oid = :table_oid @@ -2201,7 +2203,10 @@ class PGDialect(default.DefaultDialect): ORDER BY t.relname, i.relname - """ + """ % ( + "::varchar" if self.server_version_info >= (8, 1) else "", + self._pg_index_any("a.attnum", "ix.indkey") + ) t = sql.text(IDX_SQL, typemap={'attname': sqltypes.Unicode}) c = connection.execute(t, table_oid=table_oid) diff --git a/test/dialect/postgresql/test_dialect.py b/test/dialect/postgresql/test_dialect.py index fd6df2c98e..7f22c6a6b1 100644 --- a/test/dialect/postgresql/test_dialect.py +++ b/test/dialect/postgresql/test_dialect.py @@ -205,6 +205,7 @@ class MiscTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL): : Numeric}) assert_raises(exc.InvalidRequestError, testing.db.execute, stmt) + @testing.only_if("postgresql >= 8.2", "requires standard_conforming_strings") def test_serial_integer(self): for version, type_, expected in [ diff --git a/test/dialect/postgresql/test_query.py b/test/dialect/postgresql/test_query.py index ee5800db0f..82b49d974b 100644 --- a/test/dialect/postgresql/test_query.py +++ b/test/dialect/postgresql/test_query.py @@ -70,6 +70,7 @@ class InsertTest(fixtures.TestBase, AssertsExecutionResults): metadata.create_all() self._assert_data_with_sequence(table, 'my_seq') + @testing.requires.returning def test_sequence_returning_insert(self): table = Table('testtable', metadata, Column('id', Integer, Sequence('my_seq'), primary_key=True), @@ -84,6 +85,7 @@ class InsertTest(fixtures.TestBase, AssertsExecutionResults): metadata.create_all() self._assert_data_autoincrement(table) + @testing.requires.returning def test_opt_sequence_returning_insert(self): table = Table('testtable', metadata, Column('id', Integer, Sequence('my_seq', optional=True), @@ -97,6 +99,7 @@ class InsertTest(fixtures.TestBase, AssertsExecutionResults): metadata.create_all() self._assert_data_autoincrement(table) + @testing.requires.returning def test_autoincrement_returning_insert(self): table = Table('testtable', metadata, Column('id', Integer, primary_key=True), Column('data', String(30))) @@ -592,8 +595,7 @@ class ServerSideCursorsTest(fixtures.TestBase, AssertsExecutionResults): class MatchTest(fixtures.TestBase, AssertsCompiledSQL): - __only_on__ = 'postgresql' - __excluded_on__ = ('postgresql', '<', (8, 3, 0)), + __only_on__ = 'postgresql >= 8.3' @classmethod def setup_class(cls): diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index 705a64c8e8..aefb6a0ba3 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -20,7 +20,7 @@ class DomainReflectionTest(fixtures.TestBase, AssertsExecutionResults): """Test PostgreSQL domains""" - __only_on__ = 'postgresql' + __only_on__ = 'postgresql > 8.2' @classmethod def setup_class(cls): @@ -128,8 +128,8 @@ class DomainReflectionTest(fixtures.TestBase, AssertsExecutionResults): class ReflectionTest(fixtures.TestBase): __only_on__ = 'postgresql' - @testing.fails_if(('postgresql', '<', (8, 4)), - "newer query is bypassed due to unsupported SQL functions") + @testing.fails_if("postgresql < 8.4", + "Better int2vector functions not available") @testing.provide_metadata def test_reflected_primary_key_order(self): meta1 = self.metadata @@ -170,6 +170,8 @@ class ReflectionTest(fixtures.TestBase): eq_( t.c.x.server_default.arg.text, "'%s'::character varying" % ("abcd" * 40) ) + + @testing.fails_if("postgresql < 8.1", "schema name leaks in, not sure") @testing.provide_metadata def test_renamed_sequence_reflection(self): metadata = self.metadata diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py index ba4b63e1ae..4540b75372 100644 --- a/test/dialect/postgresql/test_types.py +++ b/test/dialect/postgresql/test_types.py @@ -102,7 +102,7 @@ class FloatCoercionTest(fixtures.TablesTest, AssertsExecutionResults): class EnumTest(fixtures.TestBase, AssertsExecutionResults): - __only_on__ = 'postgresql' + __only_on__ = 'postgresql > 8.3' @testing.fails_on('postgresql+zxjdbc', @@ -908,7 +908,7 @@ class SpecialTypesTest(fixtures.TestBase, ComparesTables, AssertsCompiledSQL): class UUIDTest(fixtures.TestBase): """Test the bind/return values of the UUID type.""" - __only_on__ = 'postgresql' + __only_on__ = 'postgresql >= 8.3' @testing.fails_on('postgresql+zxjdbc', 'column "data" is of type uuid but expression is of type character varying') -- 2.47.3