ProgrammingError when autoload from PostgreSQL (Amazon Redshift)

[Former user]

I'm trying to use sqlalchemy with Amazon Redshift. It uses Postgres drivers so I'm using psycopg2. Here's what I got when trying to autload table structure:

>>> from sqlalchemy import *
>>> engine = create_engine('postgresql+psycopg2://<connection string>')
>>> meta = MetaData(engine)
>>> t = Table('table_name', meta, autoload=True)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/schema.py", line 332, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/schema.py", line 396, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/schema.py", line 424, in _autoload
    self, include_columns, exclude_columns
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1595, in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1118, in run_callable
    return callable_(self, *args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 262, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 443, in reflecttable
    pk_cons = self.get_pk_constraint(table_name, schema, **tblkw)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 290, in get_pk_constraint
    **kw)
  File "<string>", line 1, in <lambda>
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 49, in cache
    ret = fn(self, con, *args, **kw)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1823, in get_pk_constraint
    c = connection.execute(t, table_oid=table_oid)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 163, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) op ANY/ALL (array) requires array on right side
 "\n                SELECT a.attname\n                FROM\n                    pg_class t\n                    join pg_index ix on t.oid = ix.indrelid\n                    join pg_attribute a\n                        on t.oid=a.attrelid and a.attnum=ANY(ix.indkey)\n                 WHERE\n                  t.oid = %(table_oid)s and ix.indisprimary = 't'\n                ORDER BY a.attnum\n            " {'table_oid': 165790}

I'm using sqlalchemy 0.8.1, Python 2.7.4

[Mike Bayer]

can you tell me if this patch helps please:

diff -r f574bcf98de0d30f0a28bf82aae84098157de0f4 lib/sqlalchemy/dialects/postgresql/base.py
--- a/lib/sqlalchemy/dialects/postgresql/base.py    Mon May 13 16:13:15 2013 -0400
+++ b/lib/sqlalchemy/dialects/postgresql/base.py    Wed May 15 10:52:03 2013 -0400
@@ -1802,7 +1802,7 @@
                     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 a.attnum=ANY(ix.indkey::smallint[])
                  WHERE
                   t.oid = :table_oid and ix.indisprimary = 't'
                 ORDER BY a.attnum

[Former user]

Nope. But error changed:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) cannot cast type int2vector to smallint["\n                SELECT a.attname\n                FROM\n                    pg_class t\n                    join pg_index ix on t.oid = ix.indrelid\n                    join pg_attribute a\n                        on t.oid=a.attrelid and a.attnum=ANY(ix.indkey::smallint[](]
))\n                 WHERE\n                  t.oid = %(table_oid)s and ix.indisprimary = 't'\n                ORDER BY a.attnum\n            " {'table_oid': 165790}

[Mike Bayer]

what is the postgresql version they run with redshift? if its like pre 8.0, I'm not sure that I have an immediate solution here.

[Former user]

RedShift is not postgres, they forked it some time ago and re-worked it a lot.

I just found info that RedShift does not support arrays and array functions so it looks like that is not sqlalchemy issue, but limitation of RedShift. http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-datatypes.html. Sorry to bother you for nothing.

[Mike Bayer]

The query there would have to be reworked to work on redshift. It's certainly possible but would take some time to dive into how redshift has altered the information schemas.

[Former user]

Well, \d+ in psql shows that ix.indkey is int2vector and I can select indkey0, indkey1. But ANY function does not work.

[Mike Bayer]

yeah, so indkey can be theoretically any length. how do I tell on redshift if an int2vector contains the number "12" ?

[Former user]

I've asked this on AWS forum. None of the ways I know for Postgres works on RedShift. Will update as soon as I will receive answer from Amazon.

[Former user]

RedShift is based on PostgreSQL 8.0.2 and int2vector could be treated as array only since 8.1. In amazon they suggested to use pg_get_indexdef(), but I don't think it could help much in our case.

[Former user]

Number of elements in indkey is stored in pg_index.indnatts so potentialy all of them could be checked in some kind of loop.

[Mike Bayer]

I need a single SQL statement that does the job here.

[Former user]

(original author: mattg) As you mentioned on twitter, i took the easy way with the dialect i have here: https://github.com/binarydud/redshift_sqlalchemy.

The reason I went down this route to begin with is that according to this documentation: http://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html, constraints on informational only, they are not enforced.

My longer term goal is to try support the constraints, but because of the postgres 8.0 api this has proved a little difficult so far.

[Mike Bayer]

note potential pullreq at https://bitbucket.org/zzzeek/sqlalchemy/pull-request/6/sqlalchemy-to-support-postgresql-80/diff

[Mike Bayer]

the issue is pushed out to the redshift-sqlalchemy dialect, the docs have been updated to link to it in ee1f4d21037690ad996c. the dialect for now skips index reflection.