1. Mike Bayer Avatar Mike Bayer
  2. Untitled project
  3. sqlalchemy_old
  4. Issues

cannot cast type int2vector to character varying, post sqlalchemy v0.8.3, for PostgreSQL 8.2.4

Issue #3000 resolved
Cristian Codorean created an issue

I have the following piece of code:

from sqlalchemy import Table, MetaData, create_engine
engine = create_engine('postgresql://...@.../...')
metadata = MetaData(bind=engine)
mytable = Table('...', metadata, autoload=True)

Running on:

$ psql
# select version();
                                          version                                           
--------------------------------------------------------------------------------------------
 PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux)
(1 row)

Any version of sqlalchemy > 0.8.3 seems to fail when running this code with:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) cannot cast type int2vector to character varying

Have tried the following versions: 0.8.4, 0.8.5, 0.9.3 and got the failure.

See below the whole traceback with sqlalchemy 0.9.3:

Traceback (most recent call last):
  File "x.py", line 4, in <module>
    mytable = Table('...', metadata, autoload=True)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 350, in __new__
    table._init(name, metadata, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 423, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 446, in _autoload
    self, include_columns, exclude_columns
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1632, in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in run_callable
    return callable_(self, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 345, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 558, in reflecttable
    **reflection_options
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 350, in __new__
    table._init(name, metadata, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 423, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 435, in _autoload
    self, include_columns, exclude_columns
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in run_callable
    return callable_(self, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 345, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 558, in reflecttable
    **reflection_options
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 350, in __new__
    table._init(name, metadata, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 423, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 435, in _autoload
    self, include_columns, exclude_columns
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in run_callable
    return callable_(self, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 345, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 558, in reflecttable
    **reflection_options
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 350, in __new__
    table._init(name, metadata, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 423, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 435, in _autoload
    self, include_columns, exclude_columns
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in run_callable
    return callable_(self, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 345, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 558, in reflecttable
    **reflection_options
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 350, in __new__
    table._init(name, metadata, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 423, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 435, in _autoload
    self, include_columns, exclude_columns
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in run_callable
    return callable_(self, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 345, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 571, in reflecttable
    indexes = self.get_indexes(table_name, schema)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 383, in get_indexes
    info_cache=self.info_cache, **kw)
  File "<string>", line 2, in get_indexes
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 53, in cache
    ret = fn(self, con, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2212, in get_indexes
    c = connection.execute(t, table_oid=table_oid)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 717, in execute
    return meth(self, multiparams, params)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 814, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 927, in _execute_context
    context)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1076, in _handle_dbapi_exception
    exc_info
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 920, in _execute_context
    context)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 425, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) cannot cast type int2vector to character varying
 "\n          SELECT\n              i.relname as relname,\n              ix.indisunique, ix.indexprs, ix.indpred,\n              a.attname, a.attnum, ix.indkey::varchar\n          FROM\n              pg_class t\n                    join pg_index ix on t.oid = ix.indrelid\n                    join pg_class i on i.oid=ix.indexrelid\n                    left outer join\n                        pg_attribute a\n                        on t.oid=a.attrelid and a.attnum = ANY(ix.indkey)\n          WHERE\n              t.relkind = 'r'\n              and t.oid = %(table_oid)s\n              and ix.indisprimary = 'f'\n          ORDER BY\n              t.relname,\n              i.relname\n        " {'table_oid': 410127}

Comments (6)

  1. Mike Bayer repo owner

    well it's likely ecdfc317 where things happened, I was pretty careful here to go through old PG docs and identify each feature and also installed older versions to check, guess that wasn't enough.

    can you try this please and confirm that it resolves:

    diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index cea9d67..1a0f67d 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2204,7 +2204,7 @@ class PGDialect(default.DefaultDialect):
               t.relname,
               i.relname
         """ % (
-                "::varchar" if self.server_version_info >= (8, 1) else "",
+                "::varchar" if self.server_version_info >= (8, 3) else "",
                 self._pg_index_any("a.attnum", "ix.indkey")
             )
  2. Mike Bayer repo owner

    though that was post SQLA 0.8.4. There were no changes to the PG dialect in SQLA 0.8.4. assuming the issue is introduced in 0.8.5.

  3. Cristian Codorean reporter

    So far, so good. The fix seems to be working for me, both for the small code snippet I've used to reproduce the issue, and throughout the whole application I'm developing, that uses sqlalchemy.

  4. Mike Bayer repo owner
    • Fixed regression caused by release 0.8.5 / 0.9.3's compatibility enhancements where index reflection on Postgresql versions specific to only the 8.1, 8.2 series again broke, surrounding the ever problematic int2vector type. While int2vector supports array operations as of 8.1, apparently it only supports CAST to a varchar as of 8.3. fix #3000

    → <<cset 1a35188b6903>>

  5. Mike Bayer repo owner
    • Fixed regression caused by release 0.8.5 / 0.9.3's compatibility enhancements where index reflection on Postgresql versions specific to only the 8.1, 8.2 series again broke, surrounding the ever problematic int2vector type. While int2vector supports array operations as of 8.1, apparently it only supports CAST to a varchar as of 8.3. fix #3000

    → <<cset f6614aed6604>>

  6. Mike Bayer repo owner

    well the good news is i have just a regular "build every PG version" thing going on on a VM now, none of these int2vector quirks are documented. I grepped through the git log, release docs, nothing about casting arrays or int2vectors to varchars - seems like 8.3 made some big changes to arrays overall.

  7. Log in to comment
Assignee
–
Type
bug
Priority
major
Status
resolved
Component
postgres
Milestone
0.9.4
Votes
0
Watchers
2
Jira: the preferred issue tracker for Bitbucket. Join the team!