4 ms·

This description isn't too far from bitemporal tables, which are one of my favorite obscure technologies. I think though that one distinction is that immutabil

This description isn't too far from bitemporal tables, which are one of my favorite obscure technologies. I think though that one distinction is that immutability in this case requires cooperation from the client, a commitment not to modify existing records. As compare to the database enforcing it.

Mister_Snuggles 2y ago
This could be enforced in the schema via triggers and/or security permissions. Cooperation from the client is not required. EDIT: Oracle has append-only tables, and can also use "blockchain" to verify integrity. See the IMMUTABLE option on CREATE TABLE[0]. PostgreSQL doesn't appear to have append-only tables, so using security and/or triggers seems to be the only option there. [0] https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-TABLE.html https://docs.oracle.com/en/database/oracle/oracle-database/2...
- jacques_chester 2y ago
  You're correct, I overlooked triggers. Though that may be a bridge too far for some folks, triggers are only really comfortable for folks who are deep on RDBMSes. For lots of app developers the ORM is the limit of the world.
  - Mister_Snuggles 2y ago
    ORMs could offer a unique advantage by allowing the user to describe an append-only table and generating the required triggers (or the appropriate CREATE TABLE options). They'd also be able to include helpers to make working with the table easier - like defaulting to selecting current rows only, or an easy way to specify that you want rows as-of a certain point in time. I'm not sure if any ORMs actually support this though.
    - refset 2y ago
      I suspect there have been a great many examples and attempts at the ORM level - this one with "bi-temporal chaining" springs to mind: https://github.com/goldmansachs/reladomo https://github.com/goldmansachs/reladomo And without the ORM layer there's this extension for Postgres: https://github.com/hettie-d/pg_bitemporal https://github.com/hettie-d/pg_bitemporal
    - codr7 2y ago
      My "ORM" supports event sourcing, or at least easily plugging it in at the model level. https://github.com/codr7/hostr/blob/532295b40dcad6c54082e1d3c9d992321208496f/src/Hostr/Domain/Model.cs#L26 https://github.com/codr7/hostr/blob/532295b40dcad6c54082e1d3...
- mike_hearn 2y ago
  More to the point Oracle has flashback queries: SELECT ... AS OF <timestamp or logical clock time> So you can query the database as of any point in the past, making manual work to implement the same feature with custom columns redundant. Oracle can also show you a record of transactions made and what SQL to run to undo them, including dependency tracking between transactions.
  - fulafel 2y ago
    This sounds like proprietary syntax for SQL 2011 temporal queries. Apparently supported by eg MS SQL server and MariaDB.
clusterhacks 2y ago
I didn't mention it, but in this particular service, no client actually had SQL access to the database. There were create/read/update/delete service functions that clients used instead. The timestamp for a tuple was returned by reads, so when a client wanted to update/delete a tuple, the service functions all required the client to provide the timestamp from the client read. If that timestamp argument wasn't the most recent, the client had to deal with that. But the actual database insert would fail. Importantly, this particular service had only a few internal clients who almost 100% "owned" the tuples the client worked with. So there wasn't a lot of contention for any specific tuple by multiple clients.
- jacques_chester 2y ago
  > There were create/read/update/delete service functions that clients used instead. Oh! I haven't seen this pattern in a long time - not since I worked on Oracle Application Express apps. In a cousin comment I noted that the bulk of app developers don't think about how to use the database to their advantage.

Mister_Snuggles 2y ago

This could be enforced in the schema via triggers and/or security permissions. Cooperation from the client is not required. EDIT: Oracle has append-only tables, and can also use "blockchain" to verify integrity. See the IMMUTABLE option on CREATE TABLE[0]. PostgreSQL doesn't appear to have append-only tables, so using security and/or triggers seems to be the only option there. [0] https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-TABLE.html https://docs.oracle.com/en/database/oracle/oracle-database/2...

jacques_chester 2y ago
You're correct, I overlooked triggers. Though that may be a bridge too far for some folks, triggers are only really comfortable for folks who are deep on RDBMSes. For lots of app developers the ORM is the limit of the world.
- Mister_Snuggles 2y ago
  ORMs could offer a unique advantage by allowing the user to describe an append-only table and generating the required triggers (or the appropriate CREATE TABLE options). They'd also be able to include helpers to make working with the table easier - like defaulting to selecting current rows only, or an easy way to specify that you want rows as-of a certain point in time. I'm not sure if any ORMs actually support this though.
  - refset 2y ago
    I suspect there have been a great many examples and attempts at the ORM level - this one with "bi-temporal chaining" springs to mind: https://github.com/goldmansachs/reladomo https://github.com/goldmansachs/reladomo And without the ORM layer there's this extension for Postgres: https://github.com/hettie-d/pg_bitemporal https://github.com/hettie-d/pg_bitemporal
  - codr7 2y ago
    My "ORM" supports event sourcing, or at least easily plugging it in at the model level. https://github.com/codr7/hostr/blob/532295b40dcad6c54082e1d3c9d992321208496f/src/Hostr/Domain/Model.cs#L26 https://github.com/codr7/hostr/blob/532295b40dcad6c54082e1d3...
mike_hearn 2y ago
More to the point Oracle has flashback queries: SELECT ... AS OF <timestamp or logical clock time> So you can query the database as of any point in the past, making manual work to implement the same feature with custom columns redundant. Oracle can also show you a record of transactions made and what SQL to run to undo them, including dependency tracking between transactions.
- fulafel 2y ago
  This sounds like proprietary syntax for SQL 2011 temporal queries. Apparently supported by eg MS SQL server and MariaDB.

clusterhacks 2y ago

I didn't mention it, but in this particular service, no client actually had SQL access to the database. There were create/read/update/delete service functions that clients used instead. The timestamp for a tuple was returned by reads, so when a client wanted to update/delete a tuple, the service functions all required the client to provide the timestamp from the client read. If that timestamp argument wasn't the most recent, the client had to deal with that. But the actual database insert would fail. Importantly, this particular service had only a few internal clients who almost 100% "owned" the tuples the client worked with. So there wasn't a lot of contention for any specific tuple by multiple clients.

jacques_chester 2y ago
> There were create/read/update/delete service functions that clients used instead. Oh! I haven't seen this pattern in a long time - not since I worked on Oracle Application Express apps. In a cousin comment I noted that the bulk of app developers don't think about how to use the database to their advantage.