I'd expect negative integer ids in an API to break even more integrations than unexpectedly large integers.
Though I guess that likelyhood is influenced by the choice of protocol. For example when using protobuf the client code generated from the specification file will use a 32-bit integer, if that's how it was defined. While in JSON I'd generally assume it's a positive integer smaller than 2^53.
What kind of API specifies that your number is int, uint, or bigint? According to a quick search, the formats for APIs are: JSON ~80%, XML ~15%, ~5% other.
Anyone storing them in a DB, or using them in internal fields will likely have a surprise on their hands. Unless they store them as opaque strings anyway, which is the saner thing to do in these situations anyway.
Hard to believe that all their customers had written their code to work with signed IDs though.
Honestly I would expect that to break more users code (and in weirder ways) than just changing the type. It's unclear from the story how the type was exposed though.
Yeah, this was my immediate thought as well, but if the spec for the API says signed int, then at least you're defensible: you haven't broken the letter of the spec, even if you're pounding on the spirit of the spec pretty hard. You have a fairly reasonable likelihood that most/all of your customers have implemented to your spec, and therefore any negative consequences are down to secondary effects of how they handle the negative values, not directly because of failure to be able to store them.
That said, to your point, there was almost certainly someone comparing IDs to determine recency, and during the transition from large-positive to large-negative, that would absolutely cause havoc.
I'd be curious if their API spec actually said anywhere that the IDs increased consistently.
Came here to say exactly this. Programming languages usually default to signed, but if you're storing these things in databases it's common to explicitly choose unsigned, since ID's are virtually always unsigned and it gives you twice the space until you run out.
Like, instead of using negative primary keys, they could have also just have converted to an unsigned int32. I would assume both of those would break a bunch of customer implementations though.
Postgres doesn’t have unsigned column types out of the box. There’s an extension that enables it, but you’d have to know about that (which you should, if you’re managing a DB, but I digress).
MySQL does have unsigned ints out of the box, FWIW.
I'd believe it. Not sure when this is, but if it's a few years old and business software, they could probably asume everyone uses java, which doesn't even have unsigned integers.
True, but it does seem like the best alternative here. If it's a SOAP API in 2005 for business customers, for example, then it sounds like the least bad option of the four (tell consumers to update, hold up the whole company's deployment, push negative ints, or push longs). I'm just saying that to me, it isn't hard to believe this was the best option here.
You generally can't rely on strict monotonicity of primary keys, since the order in which transactions commit isn't necessarily the order in which the ids were generated. But I have relied on primary keys being "monotonic enough" to sort output by creation time for display purposes.
In the days when you used custom printed forms that had a number printed on them by the printer - when you loaded a new box of paper into your printer you had to input the first form number into the system so they'd match.
If you opened boxes in "whatever" order you'd have invoice numbers that would run contiguous for 150 or so counts (the number of forms in the box), then skip to the next multiple of 150 to correspond to when the next (or previous!) box had been used.
On-prem, single company who issued invoices to customers.
When there was an audit the government could ask to see invoices in a certain range. If some of them were missing, what does that mean? Paid under the table?
My wife worked at a place where they did manual PDFs, but there they had a tool to change properties of a PDF to change the creation time / last editing time, for when 'modifications' were needed.
And this reminds me of the other post here where some people assume cash means shady. Definitely the case there.
Well, I'd imagine that before returning the value through their API they could just check that if the number is negative, then add 2^32 to it, which would make it look like an unsigned 32 bit integer.
In most languages that support differently sized integer types and/or unsigned integer types, you wouldn’t have to check, but can just apply the appropriate modulo or bit operation on all values.
Is that really true? I did keep reading the entire piece. I think they're often interesting and can contain nuggets of wisdom or insight. Or sometimes they're just funny. When I meet someone who worked on something interesting, I often start trying to pry stories like this post out of them.
Half the time I read the stories they're just a thinly disguised ad for some flavor the day SaaS, so at least in this instance the hook was somewhat useful. Now if everyone uses this to shill their SaaS, then maybe not.
I wonder how many API users needed the attribute to be an integer (instead of just treating it as an opaque handle string), but didn't mind the integer turning negative
I think the point is that the API doesn't specify that the returned integers are positive, or are monotonically increasing, then it's fine for the service to return any unique integer.
If a client application makes an assumption about this, then their engineers will accept this as being their bad and will fix it.
I'd defend this as being pragmatic - minimising disruption to clients instead of the more 'correct' solution of changing the API. I'm hoping that they managed to roll out the new API update alongside the old one and avoid a 'big bang' API change with this. Sometimes this isn't possible, but it's great when that works out.
I'm far more likely to assume that an integer-id I get from an API is non-negative or even positive than to assume that they're always smaller than 2^31. And I'd be far more likely to blame the API provider for violating the former assumption.
Probably none needed it to be an integer. At the same time, if the API contract says {id: integer, name: string}, then you are likely to have developers, especially in statically-typed languages, that will create a class with an int32 field, and tell the JSON parsing library to create instances of that class when deserializing the API response.
I don't understand, what was the issue with changing the column type from `int` to `bigint`? What does exposing the IDs have to do with how large those ints can be? This seems like a backend issue, if we're talking about HTTP/REST APIs. Now, if we're talking compiled C style APIs, then yes, obviously widening the types will cause issues. This is very important context that is missing from this article.
The issue was probably database migration time. I was once at a startup that had close over 1 billion+ rows in MySQL. We were approaching the `int` limit in another year or so. Many tables would need to be migrated due to foreign key constraints. Migrating one of the tables required significant downtime (6 to 8 hours, IIRC) due to slow spinning disks. Some servers didn't have enough space to rebuild the tables, so we'd want to add disks just in case. There were several servers.
A few "alter table" commands cascades to an operational PITA.
I guess if in the API documentation you are saying the pkey is an int, then someone consuming that data and storing it in their own table would also likely make that the column type. So when it crosses that threshold, your customers’ tables will break.
I think he did a pretty bad job of explaining it if that’s the case though.
Assuming the API was properly documented as returning signed int, that’s not my problem. Abuse of the API or
misunderstanding of the API doesn’t trump running out of space.
Exactly. I mean, if the end solution is to convert to a big int, who’s to say that some customer didn’t assume it would always be 32 bits and blow up then, too.
This does highlight the fact that 32 bit is just a small number these days. Personally, I prefer UUIDs instead of incrementing integers for primary keys since they also scale out without having to have global coordination, but at least choose a 64-bit number.
Yes. It's just so much easier to create a UUID client-side, use that to identify data in temporary UI state and commit without having to worry about getting the incremented identifier.
I find this significantly reduces decision fatigue. Deciding which hack to use for temporary identifiers is not much fun.
The file format is obsolete (it assumes a fixed number of terminal lines per system) and has unfixable locking issues, so it has to be replaced anyway.
Negative integers seem like a nightmare if somewhere downstream someone has a route like /widgets/(\d+), or if someone is scanning text outputs for IDs with a similar regex. The BigInt expansion seems far less risky IMO.
This is engineering at its finest. Working within tight constraints to find solutions that minimize impact. An equally important part of the “solution” is communication - to the leadership, departments and customers. Start early, communicate often and you will almost always come out ahead, even if mistakes are made.
If the existing code was using int32, a switch to anything larger would cause integer overflows or JSON parsing errors in languages with strongly-typed fixed-width integer types.
Any call from a typed langage distinguishing between 32b and 64b integers (that being most popular typed languages I reckon) would break if it had assumed / used the smaller of the two.
TBF using the negative range could also break callers distinguishing between signed and unsigned if they’d used the latter on their side depending how the API was documented.
As my last job was winding down (much to the disbelief and utter denial of the CEO) we'd ran out of money for Unity licenses and ran out of staff to use Unity. CEO decided that we absolutely must have a Unity demo that worked with the slightly newer generation of hardware I was wrapping up. Being the only programmer left, it was of course my problem to figure out. Oh and also this has to be ready for a show next week, so chop-chop.
I ended up decompiling some android APKs our last Unity dev had built like eight months prior. I figured out how to extract our device driver library, then painstakingly rewrote the entire library to support new hardware while also maintaining a compatible ABI and stuffed it all back into the APK. I think I also had to forge some keys or something? It was a fucking mess. Anyway, that was the last work I ever did for him because he didn't pay me for about two months after that, and I quit the moment he gave me the wages he owed me.
He's only got one employee and zero customers, but hey his stupid demo worked for all that mattered.
I would say there are times that doing math with a primary key is a useful property (say, getting the Nth primary key (or so)) but if you are exposing it in an API I would say you would never even want a primary key projected in the first place.
A primary key is almost an implementation detail - a key that an API knows about something is one of many things that might point to this thing, might need to change, and generally might need a different representation (so don't make it your primary key.)
I also tell people to just use the bottom of any primary key space (when choosing monotonic stuff) but so many engineers just complain that they dont like the numbers (and yet many of them have had to deal with the migration a few years later so ... enjoy that I guess.)
Though I guess that likelyhood is influenced by the choice of protocol. For example when using protobuf the client code generated from the specification file will use a 32-bit integer, if that's how it was defined. While in JSON I'd generally assume it's a positive integer smaller than 2^53.