On 9/22/15 1:17 PM, Sergi Pons Freixes wrote:
2015-09-21 18:31 GMT-07:00 Mike Bayer <[email protected]
<mailto:[email protected]>>:
OK great, I can now show something similar happening. Your
model has tables t1 and t2 in *both* the public and notifications
schema and I'm assuming the same foreign key setup.
In many of your examples I've observed the explicit use of "public":
__table_args__ = {'schema': 'public'}
That has to be removed entirely. When I have all four tables
and I use "public" explicitly, the reflection system cannot get
enough information to make a decision, based on the information in
the section I originally referred to at
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path
- see the yellow sidebar box at the bottom of the section for a
quick summary.
When I remove the redundant "public" schema from my table defs,
the redundant FK defs go away.
Mmmm, but:
- Before each test, I was dropping all the tables on the 'public' and
'notifications' schemas, and I made sure they were empty. So there
should never be both tables on both schemas.
do you *want* both tables in both schemas? that's what came up with
you ran the inspector lines I showed you. If your alembic config
created the tables twice in both schemas then there is a lot more going
on than just a foreign key issue.
I agree that the first snippets that I copy-pasted were confusing, so
I decided to limit the tests just to the env.py I attached previously,
dropping the tables for a clean state. In this code I am only
referencing to 'notifications' with "__table_args__ = {'schema':
'notifications'}", and 'public' is never used. With it, t1, t2 and
alembic_versions are created all on 'notifications', an nothing in
'public'.
Alembic never adds a schema qualifier to the alembic_version table
unless you specify one. So it's not possible that the table is created
in "notifications", *unless* your postgresql configuration is such that
your *current* schema (defined as, the first schema in search_path) when
you connect is "notifications". Which we've established that it's not.
So one more time, with all detail possible; attached is an env.py script
and a full log of all SQL emitted and commands; we have alembic_version
is created in "public", the two tables created only in "notifications",
no redundant FK commands in the next run. Please start with a brand new,
definitely completely empty PG database, no dependencies, and run this
env.py as is without any of your application being imported, then do a
line-by-line on your logs vs. the logs (use logging config in attached
alembic.ini) here to see where they diverge. thanks!
Could you reproduce that?
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
from __future__ import with_statement
from alembic import context
from sqlalchemy import engine_from_config, pool
from logging.config import fileConfig
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class t1(Base):
__tablename__='t1'
__table_args__ = {'schema': 'notifications'}
id = Column(Integer, primary_key=True)
def __init__(self):
pass
class t2(Base):
__tablename__='t2'
__table_args__ = {'schema': 'notifications'}
id = Column(Integer, primary_key=True)
t1id = Column('t1id', Integer, ForeignKey(t1.id))
def __init__(self, t1id=None):
self.t1id = t1id
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = Base.metadata
# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
def run_migrations_offline():
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url, target_metadata=target_metadata, literal_binds=True)
with context.begin_transaction():
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix='sqlalchemy.',
poolclass=pool.NullPool)
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
include_schemas=True
)
with context.begin_transaction():
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
classics-MacBook-Pro:tmp classic$ python -m alembic.config revision -m "rev1"
--autogenerate
INFO [sqlalchemy.engine.base.Engine] select version()
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] select current_schema()
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT CAST('test plain returns' AS
VARCHAR(60)) AS anon_1
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT CAST('test unicode returns' AS
VARCHAR(60)) AS anon_1
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] show standard_conforming_strings
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit)
INFO [sqlalchemy.engine.base.Engine] select relname from pg_class c join
pg_namespace n on n.oid=c.relnamespace where
pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO [sqlalchemy.engine.base.Engine] select relname from pg_class c join
pg_namespace n on n.oid=c.relnamespace where
pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE alembic_version (
version_num VARCHAR(32) NOT NULL
)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
SELECT nspname
FROM pg_namespace
ORDER BY nspname
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT relname FROM pg_class c WHERE
relkind = 'r' AND 'test_schema_2' = (select nspname from pg_namespace n where
n.oid = c.relnamespace)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT relname FROM pg_class c WHERE
relkind = 'r' AND 'notifications' = (select nspname from pg_namespace n where
n.oid = c.relnamespace)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT relname FROM pg_class c WHERE
relkind = 'r' AND 'public' = (select nspname from pg_namespace n where n.oid =
c.relnamespace)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT relname FROM pg_class c WHERE
relkind = 'r' AND 'test_schema' = (select nspname from pg_namespace n where
n.oid = c.relnamespace)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.autogenerate.compare] Detected added table u'notifications.t1'
INFO [alembic.autogenerate.compare] Detected added table u'notifications.t2'
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)s AND c.relkind in ('r', 'v', 'm', 'f')
INFO [sqlalchemy.engine.base.Engine] {'table_name': u't'}
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
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = %(table_oid)s
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5539397}
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'
INFO [sqlalchemy.engine.base.Engine] {}
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
INFO [sqlalchemy.engine.base.Engine] {}
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)s AND ix.indisprimary
) k ON a.attnum=k.attnum
WHERE a.attrelid = %(table_oid)s
ORDER BY k.ord
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5539397}
INFO [sqlalchemy.engine.base.Engine]
SELECT conname
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = %(table_oid)s AND r.contype = 'p'
ORDER BY 1
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5539397}
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)s AND
r.contype = 'f' AND
c.oid = confrelid AND
n.oid = c.relnamespace
ORDER BY 1
INFO [sqlalchemy.engine.base.Engine] {'table': 5539397}
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,
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')
and t.oid = %(table_oid)s
and ix.indisprimary = 'f'
ORDER BY
t.relname,
i.relname
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5539397}
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)s AND
cons.contype = 'u'
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5539397}
INFO [alembic.autogenerate.compare] Detected removed table u't'
INFO [sqlalchemy.engine.base.Engine] COMMIT
Generating /Users/classic/Desktop/tmp/foo/versions/3814354bbd88_rev1.py ...
done
classics-MacBook-Pro:tmp classic$ python -m alembic.config upgrade head
INFO [sqlalchemy.engine.base.Engine] select version()
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] select current_schema()
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT CAST('test plain returns' AS
VARCHAR(60)) AS anon_1
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT CAST('test unicode returns' AS
VARCHAR(60)) AS anon_1
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] show standard_conforming_strings
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit)
INFO [sqlalchemy.engine.base.Engine] select relname from pg_class c join
pg_namespace n on n.oid=c.relnamespace where
pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
FROM alembic_version
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] select relname from pg_class c join
pg_namespace n on n.oid=c.relnamespace where
pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO [alembic.runtime.migration] Running upgrade -> 3814354bbd88, rev1
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE notifications.t1 (
id SERIAL NOT NULL,
PRIMARY KEY (id)
)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE notifications.t2 (
id SERIAL NOT NULL,
t1id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(t1id) REFERENCES notifications.t1 (id)
)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
DROP TABLE t
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] INSERT INTO alembic_version (version_num)
VALUES ('3814354bbd88')
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
classics-MacBook-Pro:tmp classic$ python -m alembic.config revision -m "rev2"
--autogenerate
INFO [sqlalchemy.engine.base.Engine] select version()
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] select current_schema()
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT CAST('test plain returns' AS
VARCHAR(60)) AS anon_1
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT CAST('test unicode returns' AS
VARCHAR(60)) AS anon_1
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] show standard_conforming_strings
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit)
INFO [sqlalchemy.engine.base.Engine] select relname from pg_class c join
pg_namespace n on n.oid=c.relnamespace where
pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
FROM alembic_version
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
SELECT nspname
FROM pg_namespace
ORDER BY nspname
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT relname FROM pg_class c WHERE
relkind = 'r' AND 'test_schema' = (select nspname from pg_namespace n where
n.oid = c.relnamespace)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT relname FROM pg_class c WHERE
relkind = 'r' AND 'notifications' = (select nspname from pg_namespace n where
n.oid = c.relnamespace)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT relname FROM pg_class c WHERE
relkind = 'r' AND 'test_schema_2' = (select nspname from pg_namespace n where
n.oid = c.relnamespace)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] SELECT relname FROM pg_class c WHERE
relkind = 'r' AND 'public' = (select nspname from pg_namespace n where n.oid =
c.relnamespace)
INFO [sqlalchemy.engine.base.Engine] {}
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 (n.nspname = %(schema)s)
AND c.relname = %(table_name)s AND c.relkind in ('r', 'v', 'm', 'f')
INFO [sqlalchemy.engine.base.Engine] {'table_name': u't2', 'schema':
u'notifications'}
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
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = %(table_oid)s
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542947}
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'
INFO [sqlalchemy.engine.base.Engine] {}
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
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] select c.relname, a.attname from pg_class
as c join pg_depend d on d.objid=c.oid and d.classid='pg_class'::regclass and
d.refclassid='pg_class'::regclass join pg_class t on t.oid=d.refobjid join
pg_attribute a on a.attrelid=t.oid and a.attnum=d.refobjsubid where
c.relkind='S' and c.relname=%(seqname)s
INFO [sqlalchemy.engine.base.Engine] {'seqname': u'notifications.t2_id_seq'}
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)s AND ix.indisprimary
) k ON a.attnum=k.attnum
WHERE a.attrelid = %(table_oid)s
ORDER BY k.ord
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542947}
INFO [sqlalchemy.engine.base.Engine]
SELECT conname
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = %(table_oid)s AND r.contype = 'p'
ORDER BY 1
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542947}
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)s AND
r.contype = 'f' AND
c.oid = confrelid AND
n.oid = c.relnamespace
ORDER BY 1
INFO [sqlalchemy.engine.base.Engine] {'table': 5542947}
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 (n.nspname = %(schema)s)
AND c.relname = %(table_name)s AND c.relkind in ('r', 'v', 'm', 'f')
INFO [sqlalchemy.engine.base.Engine] {'table_name': u't1', 'schema':
u'notifications'}
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
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = %(table_oid)s
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542939}
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'
INFO [sqlalchemy.engine.base.Engine] {}
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
INFO [sqlalchemy.engine.base.Engine] {}
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)s AND ix.indisprimary
) k ON a.attnum=k.attnum
WHERE a.attrelid = %(table_oid)s
ORDER BY k.ord
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542939}
INFO [sqlalchemy.engine.base.Engine]
SELECT conname
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = %(table_oid)s AND r.contype = 'p'
ORDER BY 1
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542939}
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)s AND
r.contype = 'f' AND
c.oid = confrelid AND
n.oid = c.relnamespace
ORDER BY 1
INFO [sqlalchemy.engine.base.Engine] {'table': 5542939}
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,
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')
and t.oid = %(table_oid)s
and ix.indisprimary = 'f'
ORDER BY
t.relname,
i.relname
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542939}
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)s AND
cons.contype = 'u'
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542939}
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,
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')
and t.oid = %(table_oid)s
and ix.indisprimary = 'f'
ORDER BY
t.relname,
i.relname
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542947}
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)s AND
cons.contype = 'u'
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542947}
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 (n.nspname = %(schema)s)
AND c.relname = %(table_name)s AND c.relkind in ('r', 'v', 'm', 'f')
INFO [sqlalchemy.engine.base.Engine] {'table_name': u't1', 'schema':
u'notifications'}
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)s AND
cons.contype = 'u'
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542939}
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,
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')
and t.oid = %(table_oid)s
and ix.indisprimary = 'f'
ORDER BY
t.relname,
i.relname
INFO [sqlalchemy.engine.base.Engine] {'table_oid': 5542939}
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)s AND
r.contype = 'f' AND
c.oid = confrelid AND
n.oid = c.relnamespace
ORDER BY 1
INFO [sqlalchemy.engine.base.Engine] {'table': 5542939}
INFO [sqlalchemy.engine.base.Engine] COMMIT
Generating /Users/classic/Desktop/tmp/foo/versions/4f9c144e833c_rev2.py ...
done# A generic, single database configuration.
[alembic]
# path to migration scripts
script_location = foo
# template used to generate migration files
# file_template = %%(rev)s_%%(slug)s
# max length of characters to apply to the
# "slug" field
#truncate_slug_length = 40
# set to 'true' to run the environment during
# the 'revision' command, regardless of autogenerate
# revision_environment = false
# set to 'true' to allow .pyc and .pyo files without
# a source .py file to be detected as revisions in the
# versions/ directory
# sourceless = false
# version location specification; this defaults
# to foo/versions. When using multiple version
# directories, initial revisions must be specified with --version-path
# version_locations = %(here)s/bar %(here)s/bat foo/versions
# the output encoding used when revision files
# are written from script.py.mako
# output_encoding = utf-8
sqlalchemy.url = postgresql://scott:tiger@localhost/test
# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic
[handlers]
keys = console
[formatters]
keys = generic
[logger_root]
level = WARN
handlers = console
qualname =
[logger_sqlalchemy]
level = INFO
handlers =
qualname = sqlalchemy.engine
[logger_alembic]
level = INFO
handlers =
qualname = alembic
[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic
[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S
