We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
claimed users still have locked accounts elsewhere
``` sql
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:

``` sql
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;
```


--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `gittip/models/__init__.py`
Content:
```
1 """
2 
3 The most important object in the Gittip object model is Participant, and the
4 second most important one is Ccommunity. There are a few others, but those are
5 the most important two. Participant, in particular, is at the center of
6 everything on Gittip.
7 
8 """
9 from postgres import Postgres
10 
11 class GittipDB(Postgres):
12 
13     def self_check(self):
14         """
15         Runs all available self checks on the database.
16         """
17         self._check_balances()
18         self._check_tips()
19         self._check_orphans()
20         self._check_orphans_no_tips()
21         self._check_paydays_volumes()
22 
23     def _check_tips(self):
24         """
25         Checks that there are no rows in tips with duplicate (tipper, tippee, mtime).
26 
27         https://github.com/gittip/www.gittip.com/issues/1704
28         """
29         conflicting_tips = self.one("""
30             SELECT count(*)
31               FROM
32                  (
33                     SELECT * FROM tips
34                     EXCEPT
35                     SELECT DISTINCT ON(tipper, tippee, mtime) *
36                       FROM tips
37                   ORDER BY tipper, tippee, mtime
38                   ) AS foo
39         """)
40         assert conflicting_tips == 0
41 
42     def _check_balances(self):
43         """
44         Recalculates balances for all participants from transfers and exchanges.
45 
46         https://github.com/gittip/www.gittip.com/issues/1118
47         """
48         with self.get_cursor() as cursor:
49             if cursor.one("select exists (select * from paydays where ts_end < ts_start) as running"):
50                 # payday is running and the query bellow does not account for pending
51                 return
52             b = cursor.one("""
53                 select count(*)
54                   from (
55                     select username, sum(a) as balance
56                       from (
57                               select participant as username, sum(amount) as a
58                                 from exchanges
59                                where amount > 0
60                             group by participant
61 
62                                union
63 
64                               select participant as username, sum(amount-fee) as a
65                                 from exchanges
66                                where amount < 0
67                             group by participant
68 
69                                union
70 
71                               select tipper as username, sum(-amount) as a
72                                 from transfers
73                             group by tipper
74 
75                                union
76 
77                               select tippee as username, sum(amount) as a
78                                 from transfers
79                             group by tippee
80                             ) as foo
81                     group by username
82 
83                     except
84 
85                     select username, balance
86                     from participants
87                   ) as foo2
88             """)
89         assert b == 0, "conflicting balances: {}".format(b)
90 
91     def _check_orphans(self):
92         """
93         Finds participants that
94             * does not have corresponding elsewhere account
95             * have not been absorbed by other participant
96 
97         These are broken because new participants arise from elsewhere
98         and elsewhere is detached only by take over which makes a note
99         in absorptions if it removes the last elsewhere account.
100 
101         Especially bad case is when also claimed_time is set because
102         there must have been elsewhere account attached and used to sign in.
103 
104         https://github.com/gittip/www.gittip.com/issues/617
105         """
106         orphans = self.all("""
107             select username
108                from participants
109               where not exists (select * from elsewhere where elsewhere.participant=username)
110                 and not exists (select * from absorptions where archived_as=username)
111         """)
112         assert len(orphans) == 0, "missing elsewheres: {}".format(list(orphans))
113 
114     def _check_orphans_no_tips(self):
115         """
116         Finds participants
117             * without elsewhere account attached
118             * having non zero outstanding tip
119 
120         This should not happen because when we remove the last elsewhere account
121         in take_over we also zero out all tips.
122         """
123         tips_with_orphans = self.all("""
124             WITH orphans AS (
125                 SELECT username FROM participants
126                 WHERE NOT EXISTS (SELECT 1 FROM elsewhere WHERE participant=username)
127             ), valid_tips AS (
128                   SELECT * FROM (
129                             SELECT DISTINCT ON (tipper, tippee) *
130                               FROM tips
131                           ORDER BY tipper, tippee, mtime DESC
132                       ) AS foo
133                   WHERE amount > 0
134             )
135             SELECT id FROM valid_tips
136             WHERE tipper IN (SELECT * FROM orphans)
137             OR tippee IN (SELECT * FROM orphans)
138         """)
139         known = set([25206, 46266]) # '4c074000c7bc', 'naderman', '3.00'
140         real = set(tips_with_orphans) - known
141         assert len(real) == 0, real
142 
143     def _check_paydays_volumes(self):
144         """
145         Recalculate *_volume fields in paydays table using exchanges table.
146         """
147         with self.get_cursor() as cursor:
148             if cursor.one("select exists (select * from paydays where ts_end < ts_start) as running"):
149                 # payday is running
150                 return
151             charge_volume = cursor.all("""
152                 select * from (
153                     select id, ts_start, charge_volume, (
154                             select coalesce(sum(amount+fee), 0)
155                             from exchanges
156                             where timestamp > ts_start
157                             and timestamp < ts_end
158                             and amount > 0
159                         ) as ref
160                     from paydays
161                     order by id
162                 ) as foo
163                 where charge_volume != ref
164             """)
165             assert len(charge_volume) == 0
166 
167             charge_fees_volume = cursor.all("""
168                 select * from (
169                     select id, ts_start, charge_fees_volume, (
170                             select coalesce(sum(fee), 0)
171                             from exchanges
172                             where timestamp > ts_start
173                             and timestamp < ts_end
174                             and amount > 0
175                         ) as ref
176                     from paydays
177                     order by id
178                 ) as foo
179                 where charge_fees_volume != ref
180             """)
181             assert len(charge_fees_volume) == 0
182 
183             ach_volume = cursor.all("""
184                 select * from (
185                     select id, ts_start, ach_volume, (
186                             select coalesce(sum(amount), 0)
187                             from exchanges
188                             where timestamp > ts_start
189                             and timestamp < ts_end
190                             and amount < 0
191                         ) as ref
192                     from paydays
193                     order by id
194                 ) as foo
195                 where ach_volume != ref
196             """)
197             assert len(ach_volume) == 0
198 
199             ach_fees_volume = cursor.all("""
200                 select * from (
201                     select id, ts_start, ach_fees_volume, (
202                             select coalesce(sum(fee), 0)
203                             from exchanges
204                             where timestamp > ts_start
205                             and timestamp < ts_end
206                             and amount < 0
207                         ) as ref
208                     from paydays
209                     order by id
210                 ) as foo
211                 where ach_fees_volume != ref
212             """)
213             assert len(ach_fees_volume) == 0
214 #
215
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/gittip/models/__init__.py b/gittip/models/__init__.py
--- a/gittip/models/__init__.py
+++ b/gittip/models/__init__.py
@@ -19,6 +19,7 @@
         self._check_orphans()
         self._check_orphans_no_tips()
         self._check_paydays_volumes()
+        self._check_claimed_not_locked()
 
     def _check_tips(self):
         """
@@ -211,4 +212,18 @@
                 where ach_fees_volume != ref
             """)
             assert len(ach_fees_volume) == 0
+
+    def _check_claimed_not_locked(self):
+        locked = self.all("""
+            SELECT participant
+            FROM elsewhere
+            WHERE EXISTS (
+                SELECT *
+                FROM participants
+                WHERE username=participant
+                AND claimed_time IS NOT NULL
+            ) AND is_locked
+        """)
+        assert len(locked) == 0
+
 #

{"golden_diff": "diff --git a/gittip/models/__init__.py b/gittip/models/__init__.py\n--- a/gittip/models/__init__.py\n+++ b/gittip/models/__init__.py\n@@ -19,6 +19,7 @@\n         self._check_orphans()\n         self._check_orphans_no_tips()\n         self._check_paydays_volumes()\n+        self._check_claimed_not_locked()\n \n     def _check_tips(self):\n         \"\"\"\n@@ -211,4 +212,18 @@\n                 where ach_fees_volume != ref\n             \"\"\")\n             assert len(ach_fees_volume) == 0\n+\n+    def _check_claimed_not_locked(self):\n+        locked = self.all(\"\"\"\n+            SELECT participant\n+            FROM elsewhere\n+            WHERE EXISTS (\n+                SELECT *\n+                FROM participants\n+                WHERE username=participant\n+                AND claimed_time IS NOT NULL\n+            ) AND is_locked\n+        \"\"\")\n+        assert len(locked) == 0\n+\n #\n", "issue": "claimed users still have locked accounts elsewhere\n``` sql\nselect platform, claimed_time is null AS claimed, count(claimed_time is null) as nlocked \nfrom elsewhere \njoin participants on username = participant \nwhere is_locked \ngroup by platform, claimed_time is null \norder by platform;\n```\n\n```\n platform | claimed | nlocked \n----------+---------+---------\n github   | t       |      17\n github   | f       |       5\n twitter  | t       |       4\n twitter  | f       |       4\n(4 rows)\n```\n\nI would expect claimed accounts to not be marked is_locked in elsewhere.\n\nHere's a query to see the deets:\n\n``` sql\nselect platform, user_info -> 'screen_name' AS screen_name, user_info->'login' AS login, claimed_time \nfrom elsewhere \njoin participants on username = participant\nwhere is_locked \norder by platform, claimed_time, screen_name, login;\n```\n\n", "before_files": [{"content": "\"\"\"\n\nThe most important object in the Gittip object model is Participant, and the\nsecond most important one is Ccommunity. There are a few others, but those are\nthe most important two. Participant, in particular, is at the center of\neverything on Gittip.\n\n\"\"\"\nfrom postgres import Postgres\n\nclass GittipDB(Postgres):\n\n    def self_check(self):\n        \"\"\"\n        Runs all available self checks on the database.\n        \"\"\"\n        self._check_balances()\n        self._check_tips()\n        self._check_orphans()\n        self._check_orphans_no_tips()\n        self._check_paydays_volumes()\n\n    def _check_tips(self):\n        \"\"\"\n        Checks that there are no rows in tips with duplicate (tipper, tippee, mtime).\n\n        https://github.com/gittip/www.gittip.com/issues/1704\n        \"\"\"\n        conflicting_tips = self.one(\"\"\"\n            SELECT count(*)\n              FROM\n                 (\n                    SELECT * FROM tips\n                    EXCEPT\n                    SELECT DISTINCT ON(tipper, tippee, mtime) *\n                      FROM tips\n                  ORDER BY tipper, tippee, mtime\n                  ) AS foo\n        \"\"\")\n        assert conflicting_tips == 0\n\n    def _check_balances(self):\n        \"\"\"\n        Recalculates balances for all participants from transfers and exchanges.\n\n        https://github.com/gittip/www.gittip.com/issues/1118\n        \"\"\"\n        with self.get_cursor() as cursor:\n            if cursor.one(\"select exists (select * from paydays where ts_end < ts_start) as running\"):\n                # payday is running and the query bellow does not account for pending\n                return\n            b = cursor.one(\"\"\"\n                select count(*)\n                  from (\n                    select username, sum(a) as balance\n                      from (\n                              select participant as username, sum(amount) as a\n                                from exchanges\n                               where amount > 0\n                            group by participant\n\n                               union\n\n                              select participant as username, sum(amount-fee) as a\n                                from exchanges\n                               where amount < 0\n                            group by participant\n\n                               union\n\n                              select tipper as username, sum(-amount) as a\n                                from transfers\n                            group by tipper\n\n                               union\n\n                              select tippee as username, sum(amount) as a\n                                from transfers\n                            group by tippee\n                            ) as foo\n                    group by username\n\n                    except\n\n                    select username, balance\n                    from participants\n                  ) as foo2\n            \"\"\")\n        assert b == 0, \"conflicting balances: {}\".format(b)\n\n    def _check_orphans(self):\n        \"\"\"\n        Finds participants that\n            * does not have corresponding elsewhere account\n            * have not been absorbed by other participant\n\n        These are broken because new participants arise from elsewhere\n        and elsewhere is detached only by take over which makes a note\n        in absorptions if it removes the last elsewhere account.\n\n        Especially bad case is when also claimed_time is set because\n        there must have been elsewhere account attached and used to sign in.\n\n        https://github.com/gittip/www.gittip.com/issues/617\n        \"\"\"\n        orphans = self.all(\"\"\"\n            select username\n               from participants\n              where not exists (select * from elsewhere where elsewhere.participant=username)\n                and not exists (select * from absorptions where archived_as=username)\n        \"\"\")\n        assert len(orphans) == 0, \"missing elsewheres: {}\".format(list(orphans))\n\n    def _check_orphans_no_tips(self):\n        \"\"\"\n        Finds participants\n            * without elsewhere account attached\n            * having non zero outstanding tip\n\n        This should not happen because when we remove the last elsewhere account\n        in take_over we also zero out all tips.\n        \"\"\"\n        tips_with_orphans = self.all(\"\"\"\n            WITH orphans AS (\n                SELECT username FROM participants\n                WHERE NOT EXISTS (SELECT 1 FROM elsewhere WHERE participant=username)\n            ), valid_tips AS (\n                  SELECT * FROM (\n                            SELECT DISTINCT ON (tipper, tippee) *\n                              FROM tips\n                          ORDER BY tipper, tippee, mtime DESC\n                      ) AS foo\n                  WHERE amount > 0\n            )\n            SELECT id FROM valid_tips\n            WHERE tipper IN (SELECT * FROM orphans)\n            OR tippee IN (SELECT * FROM orphans)\n        \"\"\")\n        known = set([25206, 46266]) # '4c074000c7bc', 'naderman', '3.00'\n        real = set(tips_with_orphans) - known\n        assert len(real) == 0, real\n\n    def _check_paydays_volumes(self):\n        \"\"\"\n        Recalculate *_volume fields in paydays table using exchanges table.\n        \"\"\"\n        with self.get_cursor() as cursor:\n            if cursor.one(\"select exists (select * from paydays where ts_end < ts_start) as running\"):\n                # payday is running\n                return\n            charge_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, charge_volume, (\n                            select coalesce(sum(amount+fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount > 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where charge_volume != ref\n            \"\"\")\n            assert len(charge_volume) == 0\n\n            charge_fees_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, charge_fees_volume, (\n                            select coalesce(sum(fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount > 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where charge_fees_volume != ref\n            \"\"\")\n            assert len(charge_fees_volume) == 0\n\n            ach_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, ach_volume, (\n                            select coalesce(sum(amount), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount < 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where ach_volume != ref\n            \"\"\")\n            assert len(ach_volume) == 0\n\n            ach_fees_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, ach_fees_volume, (\n                            select coalesce(sum(fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount < 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where ach_fees_volume != ref\n            \"\"\")\n            assert len(ach_fees_volume) == 0\n#\n", "path": "gittip/models/__init__.py"}], "after_files": [{"content": "\"\"\"\n\nThe most important object in the Gittip object model is Participant, and the\nsecond most important one is Ccommunity. There are a few others, but those are\nthe most important two. Participant, in particular, is at the center of\neverything on Gittip.\n\n\"\"\"\nfrom postgres import Postgres\n\nclass GittipDB(Postgres):\n\n    def self_check(self):\n        \"\"\"\n        Runs all available self checks on the database.\n        \"\"\"\n        self._check_balances()\n        self._check_tips()\n        self._check_orphans()\n        self._check_orphans_no_tips()\n        self._check_paydays_volumes()\n        self._check_claimed_not_locked()\n\n    def _check_tips(self):\n        \"\"\"\n        Checks that there are no rows in tips with duplicate (tipper, tippee, mtime).\n\n        https://github.com/gittip/www.gittip.com/issues/1704\n        \"\"\"\n        conflicting_tips = self.one(\"\"\"\n            SELECT count(*)\n              FROM\n                 (\n                    SELECT * FROM tips\n                    EXCEPT\n                    SELECT DISTINCT ON(tipper, tippee, mtime) *\n                      FROM tips\n                  ORDER BY tipper, tippee, mtime\n                  ) AS foo\n        \"\"\")\n        assert conflicting_tips == 0\n\n    def _check_balances(self):\n        \"\"\"\n        Recalculates balances for all participants from transfers and exchanges.\n\n        https://github.com/gittip/www.gittip.com/issues/1118\n        \"\"\"\n        with self.get_cursor() as cursor:\n            if cursor.one(\"select exists (select * from paydays where ts_end < ts_start) as running\"):\n                # payday is running and the query bellow does not account for pending\n                return\n            b = cursor.one(\"\"\"\n                select count(*)\n                  from (\n                    select username, sum(a) as balance\n                      from (\n                              select participant as username, sum(amount) as a\n                                from exchanges\n                               where amount > 0\n                            group by participant\n\n                               union\n\n                              select participant as username, sum(amount-fee) as a\n                                from exchanges\n                               where amount < 0\n                            group by participant\n\n                               union\n\n                              select tipper as username, sum(-amount) as a\n                                from transfers\n                            group by tipper\n\n                               union\n\n                              select tippee as username, sum(amount) as a\n                                from transfers\n                            group by tippee\n                            ) as foo\n                    group by username\n\n                    except\n\n                    select username, balance\n                    from participants\n                  ) as foo2\n            \"\"\")\n        assert b == 0, \"conflicting balances: {}\".format(b)\n\n    def _check_orphans(self):\n        \"\"\"\n        Finds participants that\n            * does not have corresponding elsewhere account\n            * have not been absorbed by other participant\n\n        These are broken because new participants arise from elsewhere\n        and elsewhere is detached only by take over which makes a note\n        in absorptions if it removes the last elsewhere account.\n\n        Especially bad case is when also claimed_time is set because\n        there must have been elsewhere account attached and used to sign in.\n\n        https://github.com/gittip/www.gittip.com/issues/617\n        \"\"\"\n        orphans = self.all(\"\"\"\n            select username\n               from participants\n              where not exists (select * from elsewhere where elsewhere.participant=username)\n                and not exists (select * from absorptions where archived_as=username)\n        \"\"\")\n        assert len(orphans) == 0, \"missing elsewheres: {}\".format(list(orphans))\n\n    def _check_orphans_no_tips(self):\n        \"\"\"\n        Finds participants\n            * without elsewhere account attached\n            * having non zero outstanding tip\n\n        This should not happen because when we remove the last elsewhere account\n        in take_over we also zero out all tips.\n        \"\"\"\n        tips_with_orphans = self.all(\"\"\"\n            WITH orphans AS (\n                SELECT username FROM participants\n                WHERE NOT EXISTS (SELECT 1 FROM elsewhere WHERE participant=username)\n            ), valid_tips AS (\n                  SELECT * FROM (\n                            SELECT DISTINCT ON (tipper, tippee) *\n                              FROM tips\n                          ORDER BY tipper, tippee, mtime DESC\n                      ) AS foo\n                  WHERE amount > 0\n            )\n            SELECT id FROM valid_tips\n            WHERE tipper IN (SELECT * FROM orphans)\n            OR tippee IN (SELECT * FROM orphans)\n        \"\"\")\n        known = set([25206]) # '4c074000c7bc', 'naderman', '3.00'\n        real = set(tips_with_orphans) - known\n        assert len(real) == 0, real\n\n    def _check_paydays_volumes(self):\n        \"\"\"\n        Recalculate *_volume fields in paydays table using exchanges table.\n        \"\"\"\n        with self.get_cursor() as cursor:\n            if cursor.one(\"select exists (select * from paydays where ts_end < ts_start) as running\"):\n                # payday is running\n                return\n            charge_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, charge_volume, (\n                            select coalesce(sum(amount+fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount > 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where charge_volume != ref\n            \"\"\")\n            assert len(charge_volume) == 0\n\n            charge_fees_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, charge_fees_volume, (\n                            select coalesce(sum(fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount > 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where charge_fees_volume != ref\n            \"\"\")\n            assert len(charge_fees_volume) == 0\n\n            ach_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, ach_volume, (\n                            select coalesce(sum(amount), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount < 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where ach_volume != ref\n            \"\"\")\n            assert len(ach_volume) == 0\n\n            ach_fees_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, ach_fees_volume, (\n                            select coalesce(sum(fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount < 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where ach_fees_volume != ref\n            \"\"\")\n            assert len(ach_fees_volume) == 0\n\n    def _check_claimed_not_locked(self):\n        locked = self.all(\"\"\"\n            SELECT participant\n            FROM elsewhere\n            WHERE EXISTS (\n                SELECT *\n                FROM participants\n                WHERE username=participant\n                AND claimed_time IS NOT NULL\n            ) AND is_locked\n        \"\"\")\n        assert len(locked) == 0\n\n#\n", "path": "gittip/models/__init__.py"}]}