Multiple retired users with same name prevent import/export of db.

Hi all:

This is an update to my original email. The original may not be
delivered it seems to be MIA 12 hours after I sent it but...

The darcs folks are trying to upgrade to 2.0.0 and change their
backend database from postgres to sqlite.

Trying to import an export they get:

   IntegrityError: UNIQUE constraint failed: _user.__retired__, _user._use=
rname

They have multiple retired users with the same username.

I suggested to the darcs folks that munging the username of the
retired users is probably the easiest thing to do.

However they did have luck by sorting the input file so the
active user comes after any retired entries for the same user.

I sugested in my original email that there was info lost on
export cycle. I was wrong.  The export uses true/false for
retired status while the retired status in the database is
considered true if filled with the id of the entry.

  select id, _username, __retired__ from _user order by id;
  1|admin|0
  ...
  22|duplicate|22
  23|duplicate|23
  24|duplicate|0

22 and 23 are retired. The export looks like:

  activity:actor:address:...:id:...:username:is retired

  (2021, 6, 5, 22, 17, 28.615, 0, 0, 0):'1':'[email protected]':...'22':...=
:'duplicate':True

  (2021, 6, 5, 22, 20, 5.85, 0, 0, 0):'1':'[email protected]':...:'24':...:=
'duplicate':False

so you only get True/False for retired, not the integer number in
the original database. It turns out this isn't a problem.

The import has two steps in rdbms based systems (this issue
doesn't happen in anydbm). The key is username.

  1. create a new node that sets the unique composite index (key,
     __retired__) where __retired__ has the default value of 0.
  2. retire it by updating the unique composite index (key
     __retired__) setting __retired__ to the id.

If I have an export file ordered like:

  (2021, 6, 5, 22, 17, 28.615, 0, 0, 0):'1':'[email protected]':...'22':...=
:'duplicate':True

  (2021, 6, 5, 22, 20, 5.85, 0, 0, 0):'1':'[email protected]':...:'24':...:=
'duplicate':False

it will import correctly. As the unique index will see:

   duplicate, 0  (id 22)
   duplicate, 22 (id 22)
   duplicate, 0  (id 24)

However if the active entry is imported first:

  (2021, 6, 5, 22, 20, 5.85, 0, 0, 0):'1':'[email protected]':...:'24':...:=
'duplicate':False

  (2021, 6, 5, 22, 17, 28.615, 0, 0, 0):'1':'[email protected]':...'22':...=
:'duplicate':True

the unique index sees:

  duplicate,0   (id 24)
  duplicate,0   (id 22)  # conflict
   
and we get the error. But how do we fix thing for the future? I
think reusing a username is an edge case (and confusing), but we
should handle this better.

I can change the export to sort by (id, retired). Sorting by id
on the assumption that the active entry is the newest entry seems
a dangerous assumption. That should fix it for the future.

But this doesn't allow importing an unsorted/missorted export.
Assuming my theory that export/import was supposed to work only
with the same versions of roundup is incorrect, we need to
support handling a misordered export.

1. Import could read an entire csv and sort properly (taking
possibly a large amount of memory). Not a great idea IMO.

2. Could it be handled when the exception is triggered?  When the
exception is trigered, changing the non-retired index entry from
(key1, 0) to (key1, -1). Then retry the failing insert.

When the retry succeeds, update the index for key1 back to 0. If
-1 doesn't work for some reason use 10000 or some other sentinal
number (that we hope is not a valid value for a retired user).

Or we could leave the -1 (sentry) value until all entries are
fully imported and do one update of the index changing -1 to
0. That is probably performs better.

3. The code could be rewritten to set the __retired__ property on
initial node creation, but that looks to be pretty invasive.

Anybody have some insight here? I am leaning toward #2.

Thanks.

--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.