cannot cast type int2vector to character varying, post sqlalchemy v0.8.3, for PostgreSQL 8.2.4
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)
-
repo owner -
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.
-
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.
-
repo owner - changed status to resolved
- 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>>
-
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>>
- 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
-
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.
- Log in to comment
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: