4 ms·

He acts like sequence key is odd, but that’s quite normal in database world. https://www.postgresql.org/docs/current/sql-createsequence.html https://www.postgr

He acts like sequence key is odd, but that’s quite normal in database world. https://www.postgresql.org/docs/current/sql-createsequence.html https://www.postgresql.org/docs/current/sql-createsequence.h...

williamdclt 2y ago
yeah, I've seen tables with a single row and single columns a few times, for alright reasons. Sometimes you do just want to store a single global value!
MarceColl 2y ago
Yes, but both these have very different properties. He said (I don't know if its the case) that the db didnt have an autoincremental type. Postgres uses these sequence objects to implement autoincremental ids as he was referring to, they are implemented in-engine and are very fast and have already solved data races. In the article, what he complains about is not about what a sequence is, but about implementing it manually with a table that is read, incremented and then saved. This us more expensive, and depending on how it was implemented you need to take care of the whole data flow so you are unable to allocate the same id twice. That's what he considers odd
- pbronez 2y ago
  The scary thing to me about that setup is how the global value is updated. Every individual script must successfully increment the value to avoid duplicate keys. Really hope they had a “get key” stored procedure to handle that.
  - SoftTalker 2y ago
    It's possible they had some requirement for gapless ID numbers. You can't do that with a sequence.
_elf 2y ago
Databases have built-in features for this now. What the author is talking about is a regular table. In reality, that wasn't too unusual to see because frameworks would use that technique because it's a lowest common denominator across RDMS.
- ssdspoimdsjvv 2y ago
  Does SQLite have sequences yet?
  - immibis 2y ago
    Sqlite only has automatically assigned row ID. You can type a column as "integer primary key" to make it an alias for the row ID, or use ROWID directly (not recommended for compatibility).
mnahkies 2y ago
I think the intriguing part was purposefully using the same sequence value for rows in multiple tables. I've worked with globally unique (to our application) integer keys, and per table integer sequences (which obviously aren't globally unique), but I don't recall seeing anyone use a global sequence but purposefully reuse elements of the sequence before.
- collinvandyck76 2y ago
  I've seen this fairly recently and I was surprised to also see it in this article because I think that makes it twice that I've seen it in ~25 years.
  - airstrike 2y ago
    They may even be the same codebase!
- linux2647 2y ago
  It’s kind of like an idempotency key assigned either by the request or generated at the beginning of processing that request
  - mnahkies 2y ago
    If the caller/consumer isn't choosing the idempotence key then I don't think it would act as a particularly good one. In this scenario the database is choosing the id during a transaction and so you'll only find out the value if the transaction successfully commits and the response makes it back to you. Generally you'd want the client to provide the idempotency key such that whatever failure occurs you know the value and can provide it on a retry such that the server can use it to prevent double entry / etc
masklinn 2y ago
I think you missed the crucial part: every related record across all tables would have the same sequence item (the same id). That's really not normal in the database world. It sounds a lot like ECS though.

williamdclt 2y ago

yeah, I've seen tables with a single row and single columns a few times, for alright reasons. Sometimes you do just want to store a single global value!

MarceColl 2y ago

Yes, but both these have very different properties. He said (I don't know if its the case) that the db didnt have an autoincremental type. Postgres uses these sequence objects to implement autoincremental ids as he was referring to, they are implemented in-engine and are very fast and have already solved data races. In the article, what he complains about is not about what a sequence is, but about implementing it manually with a table that is read, incremented and then saved. This us more expensive, and depending on how it was implemented you need to take care of the whole data flow so you are unable to allocate the same id twice. That's what he considers odd

pbronez 2y ago
The scary thing to me about that setup is how the global value is updated. Every individual script must successfully increment the value to avoid duplicate keys. Really hope they had a “get key” stored procedure to handle that.
- SoftTalker 2y ago
  It's possible they had some requirement for gapless ID numbers. You can't do that with a sequence.

_elf 2y ago

Databases have built-in features for this now. What the author is talking about is a regular table. In reality, that wasn't too unusual to see because frameworks would use that technique because it's a lowest common denominator across RDMS.

ssdspoimdsjvv 2y ago
Does SQLite have sequences yet?
- immibis 2y ago
  Sqlite only has automatically assigned row ID. You can type a column as "integer primary key" to make it an alias for the row ID, or use ROWID directly (not recommended for compatibility).

mnahkies 2y ago

I think the intriguing part was purposefully using the same sequence value for rows in multiple tables. I've worked with globally unique (to our application) integer keys, and per table integer sequences (which obviously aren't globally unique), but I don't recall seeing anyone use a global sequence but purposefully reuse elements of the sequence before.

collinvandyck76 2y ago
I've seen this fairly recently and I was surprised to also see it in this article because I think that makes it twice that I've seen it in ~25 years.
- airstrike 2y ago
  They may even be the same codebase!
linux2647 2y ago
It’s kind of like an idempotency key assigned either by the request or generated at the beginning of processing that request
- mnahkies 2y ago
  If the caller/consumer isn't choosing the idempotence key then I don't think it would act as a particularly good one. In this scenario the database is choosing the id during a transaction and so you'll only find out the value if the transaction successfully commits and the response makes it back to you. Generally you'd want the client to provide the idempotency key such that whatever failure occurs you know the value and can provide it on a retry such that the server can use it to prevent double entry / etc

masklinn 2y ago

I think you missed the crucial part: every related record across all tables would have the same sequence item (the same id). That's really not normal in the database world. It sounds a lot like ECS though.