mutating trade IDs?

[npomfret]

I downloaded some transaction data via the coinfloor REST API a while ago and recorded it to disk. When I recently downloaded the same data I noticed that I had what appeared to be a bunch of over filled orders. On closer inspection I noticed that I have two almost identical trades for a single order, differing only by their id.

The trade as it looks today is this:

  {
    "datetime": "2017-08-05 03:42:40",
    "id": 1501904560496851,
    "type": 2,
    "xbt": "0.1000",
    "xbt_usd": "2678.00",
    "usd": "-267.80",
    "fee": "0.00",
    "order_id": 85721093
  }

But in the past i had this:

  {
    "datetime": "2017-08-05 03:42:40",
    "id": 1501904560496850,
    "type": 2,
    "xbt": "0.1000",
    "xbt_usd": "2678.00",
    "usd": "-267.80",
    "fee": "0.00",
    "order_id": 85721093
  }

(Identical values apart from the id)

But this older version of the trade (1501904560496850) is no longer returned but the API. It's as if the trade id isn't constant. Does it change? Could someone explain what's happened please?

[whitslack]

Very thorough of you to notice. What happened is that the API had been returning rounded (to the "tens" place) trade IDs due to an unintended implicit cast in an SQL query in the back end. The bug in the back end has been fixed, but an unfortunate side effect is that trade IDs that were previously reported incorrectly are now being reported correctly, thus causing them to appear to have changed.

You will notice that, in every case, the corrected trade ID, when rounded to the "tens" place, matches a previously reported, incorrect trade ID. You could use this fact to correlate the corrected records with your existing records. Apologies for the inconvenience.

[whitslack]

Incidentally, the "trade IDs" reported by the BIST API are really just the microsecond timestamps at which the trades occurred.

[npomfret]

Hey @whitslack - thanks for the quick reply! Understood.

What what it's worth, in my experience it doesn't pay to model database IDs in software as numbers. Just because they look like numbers doesn't make them numeric. You can't add them or multiply them, one ID isn't greater than another. If you need to sort trades by time then it's best and clearest to use an explicit time field.

But that's just my opinion! And the great thing about opinion is that it's easy to ignore ;)

Using a timestamp to generate an ID leaves you open to have 2 identical IDs though. As unlikely as it is, it will happen at some point won't it? Or do you have a way to guard against that happening? I guess you can work out the exact likelihood from your data. What would go wrong if this did happen?

Did you notify your customers of the change to the trade IDs? It's taken me a while to figure this out and it played havoc with my accounting, I probably won't be the only one.

[whitslack]

Using a timestamp to generate an ID leaves you open to have 2 identical IDs though. As unlikely as it is, it will happen at some point won't it? Or do you have a way to guard against that happening?

It won't be possible until computer hardware gets significantly faster. Back-to-back trades resulting from one limit order placement, despite being processed in native code in as tight a loop as we can manage, are still separated by hundreds of microseconds. No chance of duplicate trade timestamps for the foreseeable future.

Did you notify your customers of the change to the trade IDs? It's taken me a while to figure this out and it played havoc with my accounting, I probably won't be the only one.

I discovered the rounding issue on 14 July and immediately produced a fix, upon which I commented:

This fix corrects the trade transaction IDs reported by the BIST /user_transactions/ method so that they match the values actually recorded in the database, but this will appear to end users as though most (90%) of their historical trades are having their identifiers changed. If this is not acceptable, then we could augment the code so that it conditionally rounds trade timestamps older than the deployment date of this fix so that they match the previously (and erroneously) reported values. Alternatively, Coinfloor could send out an email blast to all users who have ever called the BIST /user_transactions/ method, informing them that the transaction IDs of their historical trades will be gaining an additional digit of precision thanks to a bug fix.

I followed up on 24 July:

Have we reached a decision about whether to explicitly round timestamps prior to the fix or to tell customers that trade IDs previously returned from /user_transactions/ were inaccurate?

The answer I received back was that the three biggest customers had reported that they would have no problems with the historical trade IDs changing and to go ahead with the fix. The fix was deployed to Coinfloor's internal test platform on 12 September and then to production on 20 September. Evidently no notification was ever sent out to customers. I do apologize for that. As you can see, I tried.

[npomfret]

Where were these announcement made, via email? I didn't get it.

You say it won't be possible to get duplicate IDs - is that because you are processing trades on a single server?

[whitslack]

Where were these announcement made, via email? I didn't get it.

What I was trying to say is that I urged them to announce the change to customers by email, but I don't think they did it.

You say it won't be possible to get duplicate IDs - is that because you are processing trades on a single server?

There are multiple servers that handle the APIs (WebSocket and BIST), but order matching (i.e., trading) occurs on a single server.