Alembic: Creating second migration throws error (UniqueConstraint?)

Created on 7 Nov 2019 · 11Comments · Source: sqlalchemy/alembic

@zzzeek
I have an initial postgres database and migration created with alembic.

When I try to make a second migration with alembic revision --autogenerate -m "Message" after modifying my models, I receive an error:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.ddl.postgresql] Detected sequence named 'variant_annotation_id_seq' as owned by integer column 'variant_annotation(id)', assuming SERIAL and omitting
Traceback (most recent call last):
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/config.py", line 573, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/config.py", line 567, in main
    self.run_cmd(cfg, options)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/config.py", line 547, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/command.py", line 214, in revision
    script_directory.run_env()
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/util/compat.py", line 173, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 85, in <module>
    run_migrations_online()
  File "alembic/env.py", line 79, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/runtime/migration.py", line 507, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/command.py", line 190, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/api.py", line 433, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/api.py", line 473, in _run_environment
    autogen_context, migration_script
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/compare.py", line 25, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/compare.py", line 51, in _produce_net_changes
    autogen_context, upgrade_ops, schemas
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/util/langhelpers.py", line 303, in go
    fn(*arg, **kw)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/compare.py", line 83, in _autogen_for_tables
    autogen_context,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/compare.py", line 195, in _compare_tables
    inspector.reflecttable(t, None)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 667, in reflecttable
    reflection_options,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 846, in _reflect_fk
    **reflection_options
  File "<string>", line 2, in __new__
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/util/deprecations.py", line 128, in warned
    return fn(*args, **kwargs)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 496, in __new__
    metadata._remove_table(name, schema)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 491, in __new__
    table._init(name, metadata, *args, **kw)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 585, in _init
    resolve_fks=resolve_fks,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 609, in _autoload
    _extend_on=_extend_on,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1604, in run_callable
    return callable_(self, *args, **kwargs)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 431, in reflecttable
    table, include_columns, exclude_columns, resolve_fks, **opts
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 687, in reflecttable
    reflection_options,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 942, in _reflect_unique_constraints
    constraints = self.get_unique_constraints(table_name, schema)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 520, in get_unique_constraints
    self.bind, table_name, schema, info_cache=self.info_cache, **kw
  File "<string>", line 2, in get_unique_constraints
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 56, in cache
    ret = fn(self, con, *args, **kw)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3401, in get_unique_constraints
    for name, uc in uniques.items()
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3401, in <listcomp>
    for name, uc in uniques.items()
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3400, in <listcomp>
    {"name": name, "column_names": [uc["cols"][i] for i in uc["key"]]}
KeyError: 'longValues'

Here are my models and my initial db creation migration:

class FactoryMixin:
    @classmethod
    def map_to_model(cls, mapping: dict) -> 'FactoryMixin':
        mappings = inspect(cls).columns.keys()
        mappings.remove('id')
        mapping = {k: mapping[k] for k in mappings}
        return cls(**mapping)


class Variant(Base, FactoryMixin):
    __tablename__: str = 'variant'
    id = Column(Integer, primary_key=True)
    chrom = Column(String(25))
    pos = Column(Integer)
    rs_id = Column(String(100))
    ref = Column(String(75))
    alt = Column(String(75))
    qual = Column(Float)
    __table_args__ = (UniqueConstraint('chrom', 'pos', 'ref', 'alt'),)


class VariantAnnotation(Base, FactoryMixin):
    __tablename__ = 'variant_annotation'
    id = Column(Integer, primary_key=True)
    variant_id = Column(Integer, ForeignKey('variant.id'))
    variant = relationship(Variant, backref='annotations')
    allele = Column(String(75))
    annotation = Column(String(200))
    annotation_impact = Column(String(20))
    gene_name = Column(String(50))
    gene_id = Column(String(50))
    hgvs_c = Column(String(200))
    hgvs_p = Column(String(200))
    reference = Column(String(200))
    specimen_id = Column(String(50))

from alembic import op
import sqlalchemy as sa

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('variant',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('chrom', sa.String(length=25), nullable=True),
    sa.Column('pos', sa.Integer(), nullable=True),
    sa.Column('rs_id', sa.String(length=100), nullable=True),
    sa.Column('ref', sa.String(length=75), nullable=True),
    sa.Column('alt', sa.String(length=75), nullable=True),
    sa.Column('qual', sa.Float(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('chrom', 'pos', 'ref', 'alt')
    )
    op.create_table('variant_annotation',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('variant_id', sa.Integer(), nullable=True),
    sa.Column('allele', sa.String(length=75), nullable=True),
    sa.Column('annotation', sa.String(length=200), nullable=True),
    sa.Column('annotation_impact', sa.String(length=20), nullable=True),
    sa.Column('gene_name', sa.String(length=50), nullable=True),
    sa.Column('gene_id', sa.String(length=50), nullable=True),
    sa.Column('hgvs_c', sa.String(length=200), nullable=True),
    sa.Column('hgvs_p', sa.String(length=200), nullable=True),
    sa.Column('reference', sa.String(length=200), nullable=True),
    sa.Column('specimen_id', sa.String(length=50), nullable=True),
    sa.ForeignKeyConstraint(['variant_id'], ['variant.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('variant_id', 'specimen_id', 'allele', 'annotation', 'gene_name', 'hgvs_c', 'hgvs_p', 'reference')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('variant_annotation')
    op.drop_table('variant')
    # ### end Alembic commands ###

Is this something wonky due to my unique constraint?

postgresql question

Source

Rubyj

Most helpful comment

I was going to ask if this was Amazon RDS but I saw the server version info at 'PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit',)' . but yes I see the offending "longValue" at https://github.com/koxudaxi/py-data-api/blob/master/pydataapi/pydataapi.py#L105 so that's what that is :)

zzzeek on 7 Nov 2019

🎉1 👍1

All 11 comments

this is an issue with SQLAlchemy, not alembic.

what PostgreSQL version is this?
run this query please and post results:

select * from pg_catalog.pg_constraint where cast(conkey as varchar) like '%longValues%';

zzzeek on 7 Nov 2019

corrected:

select * from pg_catalog.pg_constraint where cast(conkey as varchar) like '%longValues%';

zzzeek on 7 Nov 2019

Thank you for your response! Interesting. Should I file there?

This is Postgres 10.7 I have running in the cloud. There are no results from that query.

Rubyj on 7 Nov 2019

no results, OK.

let's try this: put your URL in the create_engine and run this script. it should fail in the same way. I'm assuming you are not using include_schemas=True in your env.py, let me know if you are. the failure should show logging output as to what the query is getting where this "longValues" word is coming from.

from sqlalchemy import create_engine, MetaData

e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
m = MetaData()

m.reflect(e)

zzzeek on 7 Nov 2019

You are correct. I am not using include_schemas=True.

Here are the results of the above steps:

>>> m.reflect(e)
2019-11-07 12:24:57,231 INFO sqlalchemy.engine.base.Engine select version()
2019-11-07 12:24:57,232 INFO sqlalchemy.engine.base.Engine {}
2019-11-07 12:24:57,302 DEBUG sqlalchemy.engine.base.Engine Col ('version',)
2019-11-07 12:24:57,304 DEBUG sqlalchemy.engine.base.Engine Row ('PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit',)
2019-11-07 12:24:57,305 INFO sqlalchemy.engine.base.Engine select current_schema()
2019-11-07 12:24:57,305 INFO sqlalchemy.engine.base.Engine {}
2019-11-07 12:24:57,343 DEBUG sqlalchemy.engine.base.Engine Col ('current_schema',)
2019-11-07 12:24:57,344 DEBUG sqlalchemy.engine.base.Engine Row ('public',)
2019-11-07 12:24:57,380 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2019-11-07 12:24:57,381 INFO sqlalchemy.engine.base.Engine {}
2019-11-07 12:24:57,428 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2019-11-07 12:24:57,430 INFO sqlalchemy.engine.base.Engine {}
2019-11-07 12:24:57,467 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2019-11-07 12:24:57,468 INFO sqlalchemy.engine.base.Engine {}
2019-11-07 12:24:57,505 DEBUG sqlalchemy.engine.base.Engine Col ('standard_conforming_strings',)
2019-11-07 12:24:57,506 DEBUG sqlalchemy.engine.base.Engine Row ('on',)
2019-11-07 12:24:57,577 INFO sqlalchemy.engine.base.Engine SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = :schema AND c.relkind in ('r', 
'p')
2019-11-07 12:24:57,579 INFO sqlalchemy.engine.base.Engine {'schema': 'public'}
2019-11-07 12:24:57,725 DEBUG sqlalchemy.engine.base.Engine Col ('relname',)
2019-11-07 12:24:57,726 DEBUG sqlalchemy.engine.base.Engine Row ('alembic_version',)
2019-11-07 12:24:57,726 DEBUG sqlalchemy.engine.base.Engine Row ('variant',)
2019-11-07 12:24:57,727 DEBUG sqlalchemy.engine.base.Engine Row ('variant_annotation',)
2019-11-07 12:24:57,728 INFO sqlalchemy.engine.base.Engine
            SELECT c.oid
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE (pg_catalog.pg_table_is_visible(c.oid))
            AND c.relname = :table_name AND c.relkind in
            ('r', 'v', 'm', 'f', 'p')

2019-11-07 12:24:57,731 INFO sqlalchemy.engine.base.Engine {'table_name': 'alembic_version'}
2019-11-07 12:24:57,771 DEBUG sqlalchemy.engine.base.Engine Col ('oid',)
2019-11-07 12:24:57,772 DEBUG sqlalchemy.engine.base.Engine Row (41066,)
2019-11-07 12:24:57,772 INFO sqlalchemy.engine.base.Engine
            SELECT a.attname,
              pg_catalog.format_type(a.atttypid, a.atttypmod),
              (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                FROM pg_catalog.pg_attrdef d
               WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
               AND a.atthasdef)
              AS DEFAULT,
              a.attnotnull, a.attnum, a.attrelid as table_oid,
              pgd.description as comment
            FROM pg_catalog.pg_attribute a
            LEFT JOIN pg_catalog.pg_description pgd ON (
                pgd.objoid = a.attrelid AND pgd.objsubid = a.attnum)
            WHERE a.attrelid = :table_oid
            AND a.attnum > 0 AND NOT a.attisdropped
            ORDER BY a.attnum

2019-11-07 12:24:57,780 INFO sqlalchemy.engine.base.Engine {'table_oid': 41066}
2019-11-07 12:24:57,835 DEBUG sqlalchemy.engine.base.Engine Col ('attname', 'format_type', 'default', 'attnotnull', 'attnum', 'table_oid', 'comment')
2019-11-07 12:24:57,836 DEBUG sqlalchemy.engine.base.Engine Row ('version_num', 'character varying(32)', None, True, 1, 41066, None)
2019-11-07 12:24:57,837 INFO sqlalchemy.engine.base.Engine
            SELECT t.typname as "name",
               pg_catalog.format_type(t.typbasetype, t.typtypmod) as "attype",
               not t.typnotnull as "nullable",
               t.typdefault as "default",
               pg_catalog.pg_type_is_visible(t.oid) as "visible",
               n.nspname as "schema"
            FROM pg_catalog.pg_type t
               LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
            WHERE t.typtype = 'd'

2019-11-07 12:24:57,841 INFO sqlalchemy.engine.base.Engine {}
2019-11-07 12:24:57,878 DEBUG sqlalchemy.engine.base.Engine Col ('name', 'attype', 'nullable', 'default', 'visible', 'schema')
2019-11-07 12:24:57,879 DEBUG sqlalchemy.engine.base.Engine Row ('cardinal_number', 'integer', True, None, False, 'information_schema')
2019-11-07 12:24:57,880 DEBUG sqlalchemy.engine.base.Engine Row ('character_data', 'character varying', True, None, False, 'information_schema')
2019-11-07 12:24:57,881 DEBUG sqlalchemy.engine.base.Engine Row ('sql_identifier', 'character varying', True, None, False, 'information_schema')
2019-11-07 12:24:57,881 DEBUG sqlalchemy.engine.base.Engine Row ('time_stamp', 'timestamp(2) with time zone', True, 'CURRENT_TIMESTAMP(2)', False, 'information_schema')
2019-11-07 12:24:57,882 DEBUG sqlalchemy.engine.base.Engine Row ('yes_or_no', 'character varying(3)', True, None, False, 'information_schema')
2019-11-07 12:24:57,883 INFO sqlalchemy.engine.base.Engine
            SELECT t.typname as "name",
               -- no enum defaults in 8.4 at least
               -- t.typdefault as "default",
               pg_catalog.pg_type_is_visible(t.oid) as "visible",
               n.nspname as "schema",
               e.enumlabel as "label"
            FROM pg_catalog.pg_type t
                 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
                 LEFT JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
            WHERE t.typtype = 'e'
        ORDER BY "schema", "name", e.oid
2019-11-07 12:24:57,888 INFO sqlalchemy.engine.base.Engine {}
2019-11-07 12:24:57,930 DEBUG sqlalchemy.engine.base.Engine Col ('name', 'visible', 'schema', 'label')
2019-11-07 12:24:57,931 INFO sqlalchemy.engine.base.Engine
                SELECT a.attname
                FROM pg_attribute a JOIN (
                    SELECT unnest(ix.indkey) attnum,
                           generate_subscripts(ix.indkey, 1) ord
                    FROM pg_index ix
                    WHERE ix.indrelid = :table_oid AND ix.indisprimary
                    ) k ON a.attnum=k.attnum
                WHERE a.attrelid = :table_oid
                ORDER BY k.ord

2019-11-07 12:24:57,936 INFO sqlalchemy.engine.base.Engine {'table_oid': 41066}
2019-11-07 12:24:57,982 DEBUG sqlalchemy.engine.base.Engine Col ('attname',)
2019-11-07 12:24:57,984 DEBUG sqlalchemy.engine.base.Engine Row ('version_num',)
2019-11-07 12:24:57,986 INFO sqlalchemy.engine.base.Engine
        SELECT conname
           FROM  pg_catalog.pg_constraint r
           WHERE r.conrelid = :table_oid AND r.contype = 'p'
           ORDER BY 1

2019-11-07 12:24:57,990 INFO sqlalchemy.engine.base.Engine {'table_oid': 41066}
2019-11-07 12:24:58,036 DEBUG sqlalchemy.engine.base.Engine Col ('conname',)
2019-11-07 12:24:58,037 DEBUG sqlalchemy.engine.base.Engine Row ('alembic_version_pkc',)
2019-11-07 12:24:58,038 INFO sqlalchemy.engine.base.Engine
          SELECT r.conname,
                pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
                n.nspname as conschema
          FROM  pg_catalog.pg_constraint r,
                pg_namespace n,
                pg_class c

          WHERE r.conrelid = :table AND
                r.contype = 'f' AND
                c.oid = confrelid AND
                n.oid = c.relnamespace
          ORDER BY 1

2019-11-07 12:24:58,045 INFO sqlalchemy.engine.base.Engine {'table': 41066}
2019-11-07 12:24:58,078 DEBUG sqlalchemy.engine.base.Engine Col ('conname', 'condef', 'conschema')
2019-11-07 12:24:58,079 INFO sqlalchemy.engine.base.Engine
              SELECT
                  i.relname as relname,
                  ix.indisunique, ix.indexprs, ix.indpred,
                  a.attname, a.attnum, c.conrelid, ix.indkey::varchar,
                  ix.indoption::varchar, i.reloptions, am.amname
              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)
                        left outer join
                            pg_constraint c
                            on (ix.indrelid = c.conrelid and
                                ix.indexrelid = c.conindid and
                                c.contype in ('p', 'u', 'x'))
                        left outer join
                            pg_am am
                            on i.relam = am.oid
              WHERE
                  t.relkind IN ('r', 'v', 'f', 'm', 'p')
                  and t.oid = :table_oid
                  and ix.indisprimary = 'f'
              ORDER BY
                  t.relname,
                  i.relname

2019-11-07 12:24:58,089 INFO sqlalchemy.engine.base.Engine {'table_oid': 41066}
2019-11-07 12:24:58,143 DEBUG sqlalchemy.engine.base.Engine Col ('relname', 'indisunique', 'indexprs', 'indpred', 'attname', 'attnum', 'conrelid', 'indkey', 'indoption', 'reloptions', 'amname')
2019-11-07 12:24:58,145 INFO sqlalchemy.engine.base.Engine
            SELECT
                cons.conname as name,
                cons.conkey as key,
                a.attnum as col_num,
                a.attname as col_name
            FROM
                pg_catalog.pg_constraint cons
                join pg_attribute a
                  on cons.conrelid = a.attrelid AND
                    a.attnum = ANY(cons.conkey)
            WHERE
                cons.conrelid = :table_oid AND
                cons.contype = 'u'

2019-11-07 12:24:58,151 INFO sqlalchemy.engine.base.Engine {'table_oid': 41066}
2019-11-07 12:24:58,185 DEBUG sqlalchemy.engine.base.Engine Col ('name', 'key', 'col_num', 'col_name')
2019-11-07 12:24:58,186 INFO sqlalchemy.engine.base.Engine
            SELECT
                cons.conname as name,
                pg_get_constraintdef(cons.oid) as src
            FROM
                pg_catalog.pg_constraint cons
            WHERE
                cons.conrelid = :table_oid AND
                cons.contype = 'c'

2019-11-07 12:24:58,190 INFO sqlalchemy.engine.base.Engine {'table_oid': 41066}
2019-11-07 12:24:58,221 DEBUG sqlalchemy.engine.base.Engine Col ('name', 'src')
2019-11-07 12:24:58,222 INFO sqlalchemy.engine.base.Engine
            SELECT
                pgd.description as table_comment
            FROM
                pg_catalog.pg_description pgd
            WHERE
                pgd.objsubid = 0 AND
                pgd.objoid = :table_oid

2019-11-07 12:24:58,227 INFO sqlalchemy.engine.base.Engine {'table_oid': 41066}
2019-11-07 12:24:58,258 DEBUG sqlalchemy.engine.base.Engine Col ('table_comment',)
2019-11-07 12:24:58,260 INFO sqlalchemy.engine.base.Engine 
            SELECT c.oid
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE (pg_catalog.pg_table_is_visible(c.oid))
            AND c.relname = :table_name AND c.relkind in
            ('r', 'v', 'm', 'f', 'p')

2019-11-07 12:24:58,266 INFO sqlalchemy.engine.base.Engine {'table_name': 'variant'}
2019-11-07 12:24:58,299 DEBUG sqlalchemy.engine.base.Engine Col ('oid',)
2019-11-07 12:24:58,303 DEBUG sqlalchemy.engine.base.Engine Row (41074,)
2019-11-07 12:24:58,306 INFO sqlalchemy.engine.base.Engine
            SELECT a.attname,
              pg_catalog.format_type(a.atttypid, a.atttypmod),
              (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                FROM pg_catalog.pg_attrdef d
               WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
               AND a.atthasdef)
              AS DEFAULT,
              a.attnotnull, a.attnum, a.attrelid as table_oid,
              pgd.description as comment
            FROM pg_catalog.pg_attribute a
            LEFT JOIN pg_catalog.pg_description pgd ON (
                pgd.objoid = a.attrelid AND pgd.objsubid = a.attnum)
            WHERE a.attrelid = :table_oid
            AND a.attnum > 0 AND NOT a.attisdropped
            ORDER BY a.attnum

2019-11-07 12:24:58,319 INFO sqlalchemy.engine.base.Engine {'table_oid': 41074}
2019-11-07 12:24:58,365 DEBUG sqlalchemy.engine.base.Engine Col ('attname', 'format_type', 'default', 'attnotnull', 'attnum', 'table_oid', 'comment')
2019-11-07 12:24:58,368 DEBUG sqlalchemy.engine.base.Engine Row ('id', 'integer', "nextval('variant_id_seq'::regclass)", True, 1, 41074, None)
2019-11-07 12:24:58,371 DEBUG sqlalchemy.engine.base.Engine Row ('chrom', 'character varying(25)', None, False, 2, 41074, None)
2019-11-07 12:24:58,372 DEBUG sqlalchemy.engine.base.Engine Row ('pos', 'integer', None, False, 3, 41074, None)
2019-11-07 12:24:58,376 DEBUG sqlalchemy.engine.base.Engine Row ('rs_id', 'character varying(100)', None, False, 4, 41074, None)
2019-11-07 12:24:58,377 DEBUG sqlalchemy.engine.base.Engine Row ('ref', 'character varying(75)', None, False, 5, 41074, None)
2019-11-07 12:24:58,378 DEBUG sqlalchemy.engine.base.Engine Row ('alt', 'character varying(75)', None, False, 6, 41074, None)
2019-11-07 12:24:58,378 DEBUG sqlalchemy.engine.base.Engine Row ('qual', 'double precision', None, False, 7, 41074, None)
2019-11-07 12:24:58,379 DEBUG sqlalchemy.engine.base.Engine Row ('tool_name', 'character varying(100)', None, False, 8, 41074, None)
2019-11-07 12:24:58,380 INFO sqlalchemy.engine.base.Engine
            SELECT t.typname as "name",
               pg_catalog.format_type(t.typbasetype, t.typtypmod) as "attype",
               not t.typnotnull as "nullable",
               t.typdefault as "default",
               pg_catalog.pg_type_is_visible(t.oid) as "visible",
               n.nspname as "schema"
            FROM pg_catalog.pg_type t
               LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
            WHERE t.typtype = 'd'

2019-11-07 12:24:58,391 INFO sqlalchemy.engine.base.Engine {}
2019-11-07 12:24:58,423 DEBUG sqlalchemy.engine.base.Engine Col ('name', 'attype', 'nullable', 'default', 'visible', 'schema')
2019-11-07 12:24:58,425 DEBUG sqlalchemy.engine.base.Engine Row ('cardinal_number', 'integer', True, None, False, 'information_schema')
2019-11-07 12:24:58,425 DEBUG sqlalchemy.engine.base.Engine Row ('character_data', 'character varying', True, None, False, 'information_schema')
2019-11-07 12:24:58,426 DEBUG sqlalchemy.engine.base.Engine Row ('sql_identifier', 'character varying', True, None, False, 'information_schema')
2019-11-07 12:24:58,427 DEBUG sqlalchemy.engine.base.Engine Row ('time_stamp', 'timestamp(2) with time zone', True, 'CURRENT_TIMESTAMP(2)', False, 'information_schema')
2019-11-07 12:24:58,427 DEBUG sqlalchemy.engine.base.Engine Row ('yes_or_no', 'character varying(3)', True, None, False, 'information_schema')
2019-11-07 12:24:58,428 INFO sqlalchemy.engine.base.Engine
            SELECT t.typname as "name",
               -- no enum defaults in 8.4 at least
               -- t.typdefault as "default",
               pg_catalog.pg_type_is_visible(t.oid) as "visible",
               n.nspname as "schema",
               e.enumlabel as "label"
            FROM pg_catalog.pg_type t
                 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
                 LEFT JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
            WHERE t.typtype = 'e'
        ORDER BY "schema", "name", e.oid
2019-11-07 12:24:58,437 INFO sqlalchemy.engine.base.Engine {}
2019-11-07 12:24:58,470 DEBUG sqlalchemy.engine.base.Engine Col ('name', 'visible', 'schema', 'label')
2019-11-07 12:24:58,472 INFO sqlalchemy.engine.base.Engine
                SELECT a.attname
                FROM pg_attribute a JOIN (
                    SELECT unnest(ix.indkey) attnum,
                           generate_subscripts(ix.indkey, 1) ord
                    FROM pg_index ix
                    WHERE ix.indrelid = :table_oid AND ix.indisprimary
                    ) k ON a.attnum=k.attnum
                WHERE a.attrelid = :table_oid
                ORDER BY k.ord

2019-11-07 12:24:58,476 INFO sqlalchemy.engine.base.Engine {'table_oid': 41074}
2019-11-07 12:24:58,507 DEBUG sqlalchemy.engine.base.Engine Col ('attname',)
2019-11-07 12:24:58,508 DEBUG sqlalchemy.engine.base.Engine Row ('id',)
2019-11-07 12:24:58,509 INFO sqlalchemy.engine.base.Engine
        SELECT conname
           FROM  pg_catalog.pg_constraint r
           WHERE r.conrelid = :table_oid AND r.contype = 'p'
           ORDER BY 1

2019-11-07 12:24:58,512 INFO sqlalchemy.engine.base.Engine {'table_oid': 41074}
2019-11-07 12:24:58,547 DEBUG sqlalchemy.engine.base.Engine Col ('conname',)
2019-11-07 12:24:58,549 DEBUG sqlalchemy.engine.base.Engine Row ('variant_pkey',)
2019-11-07 12:24:58,550 INFO sqlalchemy.engine.base.Engine
          SELECT r.conname,
                pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
                n.nspname as conschema
          FROM  pg_catalog.pg_constraint r,
                pg_namespace n,
                pg_class c

          WHERE r.conrelid = :table AND
                r.contype = 'f' AND
                c.oid = confrelid AND
                n.oid = c.relnamespace
          ORDER BY 1

2019-11-07 12:24:58,556 INFO sqlalchemy.engine.base.Engine {'table': 41074}
2019-11-07 12:24:58,587 DEBUG sqlalchemy.engine.base.Engine Col ('conname', 'condef', 'conschema')
2019-11-07 12:24:58,588 INFO sqlalchemy.engine.base.Engine
              SELECT
                  i.relname as relname,
                  ix.indisunique, ix.indexprs, ix.indpred,
                  a.attname, a.attnum, c.conrelid, ix.indkey::varchar,
                  ix.indoption::varchar, i.reloptions, am.amname
              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)
                        left outer join
                            pg_constraint c
                            on (ix.indrelid = c.conrelid and
                                ix.indexrelid = c.conindid and
                                c.contype in ('p', 'u', 'x'))
                        left outer join
                            pg_am am
                            on i.relam = am.oid
              WHERE
                  t.relkind IN ('r', 'v', 'f', 'm', 'p')
                  and t.oid = :table_oid
                  and ix.indisprimary = 'f'
              ORDER BY
                  t.relname,
                  i.relname

2019-11-07 12:24:58,597 INFO sqlalchemy.engine.base.Engine {'table_oid': 41074}
2019-11-07 12:24:58,643 DEBUG sqlalchemy.engine.base.Engine Col ('relname', 'indisunique', 'indexprs', 'indpred', 'attname', 'attnum', 'conrelid', 'indkey', 'indoption', 'reloptions', 'amname')
2019-11-07 12:24:58,645 DEBUG sqlalchemy.engine.base.Engine Row ('variant_chrom_pos_ref_alt_key', True, None, None, 'chrom', 2, 41074, '2 3 5 6', '0 0 0 0', None, 'btree')
2019-11-07 12:24:58,645 DEBUG sqlalchemy.engine.base.Engine Row ('variant_chrom_pos_ref_alt_key', True, None, None, 'pos', 3, 41074, '2 3 5 6', '0 0 0 0', None, 'btree')
2019-11-07 12:24:58,646 DEBUG sqlalchemy.engine.base.Engine Row ('variant_chrom_pos_ref_alt_key', True, None, None, 'ref', 5, 41074, '2 3 5 6', '0 0 0 0', None, 'btree')
2019-11-07 12:24:58,646 DEBUG sqlalchemy.engine.base.Engine Row ('variant_chrom_pos_ref_alt_key', True, None, None, 'alt', 6, 41074, '2 3 5 6', '0 0 0 0', None, 'btree')
2019-11-07 12:24:58,647 INFO sqlalchemy.engine.base.Engine
            SELECT
                cons.conname as name,
                cons.conkey as key,
                a.attnum as col_num,
                a.attname as col_name
            FROM
                pg_catalog.pg_constraint cons
                join pg_attribute a
                  on cons.conrelid = a.attrelid AND
                    a.attnum = ANY(cons.conkey)
            WHERE
                cons.conrelid = :table_oid AND
                cons.contype = 'u'

2019-11-07 12:24:58,654 INFO sqlalchemy.engine.base.Engine {'table_oid': 41074}
2019-11-07 12:24:58,701 DEBUG sqlalchemy.engine.base.Engine Col ('name', 'key', 'col_num', 'col_name')
2019-11-07 12:24:58,702 DEBUG sqlalchemy.engine.base.Engine Row ('variant_chrom_pos_ref_alt_key', {'longValues': [2, 3, 5, 6]}, 2, 'chrom')
2019-11-07 12:24:58,703 DEBUG sqlalchemy.engine.base.Engine Row ('variant_chrom_pos_ref_alt_key', {'longValues': [2, 3, 5, 6]}, 3, 'pos')
2019-11-07 12:24:58,704 DEBUG sqlalchemy.engine.base.Engine Row ('variant_chrom_pos_ref_alt_key', {'longValues': [2, 3, 5, 6]}, 5, 'ref')
2019-11-07 12:24:58,704 DEBUG sqlalchemy.engine.base.Engine Row ('variant_chrom_pos_ref_alt_key', {'longValues': [2, 3, 5, 6]}, 6, 'alt')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 4249, in reflect
    Table(name, self, **reflect_opts)
  File "<string>", line 2, in __new__
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/util/deprecations.py", line 128, in warned
    return fn(*args, **kwargs)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 496, in __new__
    metadata._remove_table(name, schema)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 491, in __new__
    table._init(name, metadata, *args, **kw)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 585, in _init
    resolve_fks=resolve_fks,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 609, in _autoload
    _extend_on=_extend_on,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1604, in run_callable
    return callable_(self, *args, **kwargs)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 431, in reflecttable
    table, include_columns, exclude_columns, resolve_fks, **opts
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 687, in reflecttable
    reflection_options,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 942, in _reflect_unique_constraints
    constraints = self.get_unique_constraints(table_name, schema)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 520, in get_unique_constraints
    self.bind, table_name, schema, info_cache=self.info_cache, **kw
  File "<string>", line 2, in get_unique_constraints
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 56, in cache
    ret = fn(self, con, *args, **kw)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3401, in get_unique_constraints
    for name, uc in uniques.items()
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3401, in <listcomp>
    for name, uc in uniques.items()
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3400, in <listcomp>
    {"name": name, "column_names": [uc["cols"][i] for i in uc["key"]]}
KeyError: 'longValues'

Rubyj on 7 Nov 2019

looks like you've found something we've never seen before. i will try to transfer this issue to the SQLAlchemy tracker.

zzzeek on 7 Nov 2019

@zzzeek Ok thank you. Do you think this has to do with the way my database is set up? Or with the way unique constrains are being handled?

This is an amazon rds serverless postgres database, so I wonder if there are some oddities in it or the way I am accessing it (driver I am using).

Rubyj on 7 Nov 2019

@zzzeek I believe this is a result of the custom driver I am using. I filed the issue there and will follow up.

Rubyj on 7 Nov 2019

zzzeek on 7 Nov 2019

🎉1 👍1

it appears to be some exotic PG type that psycopg2 nonetheless returns as a string of the form '{x, y, z}'.

zzzeek on 7 Nov 2019

Yes! Thank you! I am working with package maintainer to get it sorted :D It just needs to be returned as an array or string basically.

Rubyj on 7 Nov 2019

Was this page helpful?

0 / 5 - 0 ratings

Related issues

SQLite reflection: parentheses wrongly stripped from expression in default clause

zmwangx · 7Comments

Getting `ValueError: not enough values to unpack (expected 2, got 1)` when running db migrate - Need to understand how to get more info on the root cause.

MoShitrit · 3Comments

Redundant Foreign Key migrations (again)

rjaduthie · 9Comments

server_default not working with python datetime

Ahtii · 8Comments

How can I do a rollback if the migration fails?

LostInDarkMath · 6Comments