claimed users still have locked accounts elsewhere

[chadwhitacre]

select platform, claimed_time is null AS claimed, count(claimed_time is null) as nlocked 
from elsewhere 
join participants on username = participant 
where is_locked 
group by platform, claimed_time is null 
order by platform;

 platform | claimed | nlocked 
----------+---------+---------
 github   | t       |      17
 github   | f       |       5
 twitter  | t       |       4
 twitter  | f       |       4
(4 rows)

I would expect claimed accounts to not be marked is_locked in elsewhere.

Here's a query to see the deets:

select platform, user_info -> 'screen_name' AS screen_name, user_info->'login' AS login, claimed_time 
from elsewhere 
join participants on username = participant
where is_locked 
order by platform, claimed_time, screen_name, login;

[zbynekwinkler]

I've updated the issue description to the current schema.

[zbynekwinkler]

I am wondering - what is the relation of claimed_time and is_locked? First is on participants, second on elsewhere but their meaning reminds me of is_suspicious where we have 3 values - yes, no and null (for not yet decided). Using similar logic claimed_time IS NOT null means 'yes', is_locked means 'no' and claimed_time IS null means 'not yet decided'.

Does it make sense to have is_locked in elsewhere and not in participants? The only situation when this would be needed is when there are 2 elsewheres attached to 1 participant and one elsewhere should be locked and the other should not. But that does not make sense - I mean how would we even interpret such a situation?

Let's move is_locked to participants or maybe even merge it with claimed_time? It would help in simplifying the homepage queries where we have 4-way joins, one being just to get is_locked.

Ref. #1549

[chadwhitacre]

Dropping from Infrastructure per IRC.

[zbynekwinkler]

Should check for this in #1705. There is most likely some bug lurking there.

[zbynekwinkler]

The correct way to get the list of affected users is:

select platform, user_info -> 'screen_name' AS screen_name, user_info->'login' AS login, claimed_time 
from elsewhere 
join participants on username = participant
where is_locked and claimed_time is not null
order by platform, claimed_time, screen_name, login;

Which returns 7 users (4 github, 3 twitter).

[zbynekwinkler]

All affected users have claimed_time of less than 2012-10-05 and afaics set_is_locked has been added by
db5bb15, which is 2012-12-14. I have no idea how it was handled before but I am inclined to say this bug is no longer present in the current code. I suggest to set is_locked to false for these users and close the issue. Ah, adding a check to #1705 is never a bad idea.

@whit537 What do you think?

[chadwhitacre]

Can we start by adding the checks and repair the db once we've found the bug for sure?

[zbynekwinkler]

What I am saying is that I think there is no bug to be found. See gittip/elsewhere/init.py#L128. The code is there since db5bb15.

[zbynekwinkler]

No new problem has been created since that time.

[chadwhitacre]

I suggest to set is_locked to false for these users and close the issue.

+1

Ah, adding a check to #1705 is never a bad idea.

+1