modifying preferred email address fails

[Steve McInerney]

had a request to modify a users preferred email address from <email address hidden> to <email address hidden>
Both addresses existed. A@ is preferred. B@ is validated.

Via the admin interface this failed, regardless of which options we togged A@ to have (New, Old, Validated etc).
Was able to duplicate with the same account on login.staging.

Account #5410187

We also have an apparent earlier version of this account @ #3390115

[Steve McInerney (spm)]

https://pastebin.canonical.com/49794/

for a bit of detail from the staging failure.

[Steve McInerney (spm)]

To complicate matters:
we deleted the existing preferred address in staging as a possible workaround (figuring trialling in staging is preferable).
This gets rid of the A@ address. But leaves the B@ address as VALIDATED.
Attempted to change that to PREFERRED, fail.

https://pastebin.canonical.com/49795/

[David Owen (dsowen)]

This is one of the constraints that isn't re-created in a development environment yet:

CREATE UNIQUE INDEX emailaddress__person__key ON emailaddress (person) WHERE ((status = 4) AND (person IS NOT NULL));

For the set of rows where status=4 (preferred address) and person is not null, person must be unique; in other words, each person has at most one preferred address.

So, there must be another row for that person that is set as the preferred. However, the link between person and account is kind of flimsy, so it might be worth checking how rows in the person table line up with the account.