5 ms·

Upsert, oh well. CRUD becomes CRUDUM: Create, Read, Update, Delete, Uperst, Merge. Instead of 4 orthogonal concepts we now have 6 overlapping. Because the majo

Upsert, oh well. CRUD becomes CRUDUM: Create, Read, Update, Delete, Uperst, Merge. Instead of 4 orthogonal concepts we now have 6 overlapping. Because the majority voted for it. That's progress!

FooBarWidget 11y ago
I'll take actual usefulness in practice any day over theoretical elegantness that has problems in practice. And really, upserts aren't that hard to understand.
pilif 11y ago
To do upserts correctly in a case of concurrent write access to the database is a real pain in the ass to get right and in the end always boils down to locking or retrying in loops with random sleep times interspersed in order to not conflict over and over again. Having the ability to tell the database the data to insert together with a conflict resolution rule and then having the guarantee that either the record will be created or the conflict resolution will be applied is very handy. No more looping, no more deadlocks, no more retrying the same insert multiple times. Yes, you can do it manually, but it's painful. See also http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...
andrewl-hn 11y ago
Well, philosophically, CRUD is a lie. You only ever need two operations: READ and UPSERT. Create with upsert and Delete by upserting "deleted = true" flag.
- bmh100 11y ago
  That "deleted" flag is extremely useful in data warehousing and OLAP applications. I wish every table had a "deleted" column and an "updated" column.
  - Todd 11y ago
    They do in my schemas :) One extra tip, which I have found useful, is to make the deleted column a time data type (just like created and updated), but nullable. That way, your Boolean check just needs to change to an IS NULL check, but you get the additional 'when' information without using an extra column.
    - pjungwir 11y ago
      That is the normal pattern in Rails apps using the `acts_as_paranoid` or `permanent_records` gems (`deleted_at` to match `created_at` and `updated_at`). But I often also have `deleted_by_id` to capture Who, and I wonder if I shouldn't just have a separate `deletions` table with the who/when and other context, and then `deletion_id` on the record. And then I wonder if I should track updates too. There are auditing solutions to record all that, but the ones I know are (rightly) not really designed for building application logic on top of. The idea of a relational schema having some kind of temporal dimension letting you get at changes is something that's been on my mind a lot lately.
      - mason55 11y ago
        > There are auditing solutions to record all that, but the ones I know are (rightly) not really designed for building application logic on top of. Yes, a big problem with table-level audits is that you lose all kinds of information about the other entities in the system. Sure, now you have an audit log of when a row was changed, but you don't really know anything about the state of all the other pieces of the database at that time, so you can't really usefully reconstruct what the entity looked like at the time it was modified. In theory you could parse through the whole audit log to reconstruct the state of the DB but in practice it gets very complicated.
        bmh100 11y ago
        A better solution is to perform row-level snapshots with a compressed storage format, such as a column store. I maintain a database which takes monthly snapshots of data and supports an application that allows period vs. period comparisons of aggregates or even individual rows. In my case, I use snapshots, but more space efficient (at the cost of computation) would be to only store changed records, then dynamically determine which data to show based on the desired periods and sorted the row changes.
deleted 11y ago
[deleted]
bsg75 11y ago
So with the original 4 CRUD operations how would you instead suggest handling the "upsert" pattern in a way that maintains data integrity and performance without a specific operator?

Upsert, oh well. CRUD becomes CRUDUM: Create, Read, Update, Delete, Uperst, Merge. Instead of 4 orthogonal concepts we now have 6 overlapping. Because the majority voted for it. That's progress!

FooBarWidget 11y ago

I'll take actual usefulness in practice any day over theoretical elegantness that has problems in practice. And really, upserts aren't that hard to understand.

pilif 11y ago

To do upserts correctly in a case of concurrent write access to the database is a real pain in the ass to get right and in the end always boils down to locking or retrying in loops with random sleep times interspersed in order to not conflict over and over again. Having the ability to tell the database the data to insert together with a conflict resolution rule and then having the guarantee that either the record will be created or the conflict resolution will be applied is very handy. No more looping, no more deadlocks, no more retrying the same insert multiple times. Yes, you can do it manually, but it's painful. See also http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...

andrewl-hn 11y ago

Well, philosophically, CRUD is a lie. You only ever need two operations: READ and UPSERT. Create with upsert and Delete by upserting "deleted = true" flag.

bmh100 11y ago
That "deleted" flag is extremely useful in data warehousing and OLAP applications. I wish every table had a "deleted" column and an "updated" column.
- Todd 11y ago
  They do in my schemas :) One extra tip, which I have found useful, is to make the deleted column a time data type (just like created and updated), but nullable. That way, your Boolean check just needs to change to an IS NULL check, but you get the additional 'when' information without using an extra column.
  - pjungwir 11y ago
    That is the normal pattern in Rails apps using the `acts_as_paranoid` or `permanent_records` gems (`deleted_at` to match `created_at` and `updated_at`). But I often also have `deleted_by_id` to capture Who, and I wonder if I shouldn't just have a separate `deletions` table with the who/when and other context, and then `deletion_id` on the record. And then I wonder if I should track updates too. There are auditing solutions to record all that, but the ones I know are (rightly) not really designed for building application logic on top of. The idea of a relational schema having some kind of temporal dimension letting you get at changes is something that's been on my mind a lot lately.
    - mason55 11y ago
      > There are auditing solutions to record all that, but the ones I know are (rightly) not really designed for building application logic on top of. Yes, a big problem with table-level audits is that you lose all kinds of information about the other entities in the system. Sure, now you have an audit log of when a row was changed, but you don't really know anything about the state of all the other pieces of the database at that time, so you can't really usefully reconstruct what the entity looked like at the time it was modified. In theory you could parse through the whole audit log to reconstruct the state of the DB but in practice it gets very complicated.
      - bmh100 11y ago
        A better solution is to perform row-level snapshots with a compressed storage format, such as a column store. I maintain a database which takes monthly snapshots of data and supports an application that allows period vs. period comparisons of aggregates or even individual rows. In my case, I use snapshots, but more space efficient (at the cost of computation) would be to only store changed records, then dynamically determine which data to show based on the desired periods and sorted the row changes.

deleted 11y ago

[deleted]

bsg75 11y ago

So with the original 4 CRUD operations how would you instead suggest handling the "upsert" pattern in a way that maintains data integrity and performance without a specific operator?