id	summary	reporter	owner	description	type	status	priority	milestone	component	resolution	keywords	cc	parents
920	Broken migration for #894	Elrond		"tl;dr the index-add on User.username might be broken. If it is, this is a blocker.

== Intro ==
We used to have `unique=True` on `User.username`. In #894 (commit 892eed590fb30131ea2e8612da5ba22fa24f690c) we decided to add an extra `index=True` (discussion, on whether this was is needed, is not the scope here).

On most (if not all) databases, a UniqueConstraint will create an internal index, so that checking the constraint is reasonably performant. This is different from an actual index (with or without uniqueness attribute) in a concptual way. Whether this is a real difference on a database, is another story.

According to the [http://docs.sqlalchemy.org/en/rel_0_8/core/constraints.html#indexes sqlalchemy docs] `unique=True, index=True` column creates only an Index with a uniqueness attribute. This is actually exactly what we would like after all.

== Situations ==

(These are the situations I expect after reading our code and the docs.)

Old schema::
  * `unique=True`
  * One Constraint on the table.
Old schema + migration
  * The migration adds an Index (without uniqueness).
  * One Constraint on the table (from before)
  * One Index (from the migration)
New schema, when creating database anew::
  * `unique=True, index=True`
  * One Index(with uniqueness attribute) on the table

As you can see, the situation with migration or building from scratch are different.

== How to verify ==

1. Create a fresh database before the migration.
2. Run the migration
3. Investigate/safe variant A
  * `pg_dump MEDIAGOBLINDB >variant_A.sql`
  * `psql MEDIAGOBLINDB`, then `\d core__users`, look at the Indexes-list
4. Create a fresh database after the migration
5. Investigate/safe variant B
  * `pg_dump MEDIAGOBLINDB >variant_B.sql`
  * `psql MEDIAGOBLINDB`, then `\d core__users`, look at the Indexes-list
6. `diff -u variant_A.sql variant_B.sql`

If the indexes / pg_dump differ, then something is wrong and this bug is a valid. Otherwise, I'd love to know, what's wrong with my assumptions.

"	defect	closed	blocker	0.7.0	programming	fixed	sql