Issue: pwiz throwing  peewee.ProgrammingError: op ANY/ALL (array) requires array on right side
Author: idvorkin
State: closed
Created: 2017-12-24 17:09:45

Howdy, I'm trying to get pwiz to run against redshift.  I'm starting by reflecting my database and have the following errors: 

Run pwiz over my redshift database:
```
idvor@IGORBASEMENTHP C:\Users\idvor
> python -m pwiz <snip> 
Password:
Traceback (most recent call last):
  File "C:\Users\idvor\Anaconda3\lib\site-packages\peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
psycopg2.ProgrammingError: op ANY/ALL (array) requires array on right side


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\idvor\Anaconda3\lib\runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "C:\Users\idvor\Anaconda3\lib\runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "C:\Users\idvor\Anaconda3\lib\site-packages\pwiz.py", line 201, in <module>
    print_models(introspector, tables, preserve_order=options.preserve_order)
  File "C:\Users\idvor\Anaconda3\lib\site-packages\pwiz.py", line 47, in print_models
    database = introspector.introspect(table_names=tables)
  File "C:\Users\idvor\Anaconda3\lib\site-packages\playhouse\reflection.py", line 461, in introspect
    table_indexes = self.metadata.get_indexes(table, self.schema)
  File "C:\Users\idvor\Anaconda3\lib\site-packages\playhouse\reflection.py", line 256, in get_indexes
    return super(PostgresqlMetadata, self).get_indexes(table, schema)
  File "C:\Users\idvor\Anaconda3\lib\site-packages\playhouse\reflection.py", line 180, in get_indexes
    return self.database.get_indexes(table, schema)
  File "C:\Users\idvor\Anaconda3\lib\site-packages\peewee.py", line 4216, in get_indexes
    cursor = self.execute_sql(query, (table, 'r', schema))
  File "C:\Users\idvor\Anaconda3\lib\site-packages\peewee.py", line 3837, in execute_sql
    self.commit()
  File "C:\Users\idvor\Anaconda3\lib\site-packages\peewee.py", line 3656, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "C:\Users\idvor\Anaconda3\lib\site-packages\peewee.py", line 135, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\idvor\Anaconda3\lib\site-packages\peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
peewee.ProgrammingError: op ANY/ALL (array) requires array on right side

```

My psql content is: 
```

idvorkin1=# \d
          List of relations
 schema |  name   | type  |  owner
--------+---------+-------+----------
 public | account | table | idvorkin
(1 row)


idvorkin1=# \d account
                Table "public.account"
   Column   |            Type             | Modifiers
------------+-----------------------------+-----------
 username   | character varying(50)       | not null
 password   | character varying(50)       | not null
 email      | character varying(355)      | not null
 created_on | timestamp without time zone | not null
 last_login | timestamp without time zone |
Indexes:
    "account_email_key" UNIQUE, btree (email)
    "account_username_key" UNIQUE, btree (username)

```

```psycopg2.ProgrammingError: op ANY/ALL (array) requires array on right side```

Can you turn on SQL logging and print out the SQL that causes the problem?

```python
import logging
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)
```

Oh wait, no need to, I found the SQL:

```
            SELECT
                i.relname, idxs.indexdef, idx.indisunique,
                array_to_string(array_agg(cols.attname), ',')
            FROM pg_catalog.pg_class AS t
            INNER JOIN pg_catalog.pg_index AS idx ON t.oid = idx.indrelid
            INNER JOIN pg_catalog.pg_class AS i ON idx.indexrelid = i.oid
            INNER JOIN pg_catalog.pg_indexes AS idxs ON
                (idxs.tablename = t.relname AND idxs.indexname = i.relname)
            LEFT OUTER JOIN pg_catalog.pg_attribute AS cols ON
                (cols.attrelid = t.oid AND cols.attnum = ANY(idx.indkey))
            WHERE t.relname = %s AND t.relkind = %s AND idxs.schemaname = %s
            GROUP BY i.relname, idxs.indexdef, idx.indisunique
            ORDER BY idx.indisunique DESC, i.relname;
```

This comes down to some difference in how redshift implements the SQL engine, I guess?

I'd suggest subclassing `PostgresqlDatabase` and overriding the `get_indexes()` method with a working version of the above query. If you find a version that works on both regular postgres *and* redshift please let me know, I'd be happy to update the code.

@coleifer attempting to do something similar as OP. 

I don't think Redshift really has the concept of indexes in the way that Postgres does

https://www.stitchdata.com/blog/how-redshift-differs-from-postgresql/

>Redshift doesn't support indexes. Instead, each table has a sort key, which determines how rows are ordered when the data is loaded. When you insert, update, or copy data in a Redshift table, new rows get added to an unsorted region, and are sorted only when the table is vacuumed or deep copied.

Would it be advantageous to just no-op for `get_indexes()` during redshift? 

You'll probably want to subclass-and-extend, as redshift is not supported as a first-class database by peewee. It's just happens to speak the postgres protocol and *mostly* works.