4 ms·

That seems like an overly complex way to do the standard: create table foo_history; create view foo as select * from foo_history order by date_added de

That seems like an overly complex way to do the standard: create table foo_history; create view foo as select * from foo_history order by date_added desc limit 1;

moron4hire 13y ago
In the user profile example, you'd only get one profile entry for one user that way. And I don't see what is so complex about it. It's meaning is explicit and clear. The difference from UPSERT is in the requirements. Any notion of an UPSERT would fail to meet my requirements, requirements I have far more often than I don't.
masklinn 13y ago
That's not quite what his scheme does, his handles pre-filled future events (and historical changes thereof), not just appending new events. Also I may be wrong but I believe your view will only ever be filled with a single row. You'd need something along the lines of: create view foo as select distinct on (id) * from foo_history order by id, date_added desc;
- moron4hire 13y ago
  Yes, exactly. It is basically a time machine. A close reading of some federal compliance laws basically requires either this sort of history tracking or caching of every report ever ran, as the auditing requirements often stipulate needing to be able to "see how the report appeared at the time it was generated".
  - platz 13y ago
    Was the perpetrator of the crime caught and punished?
    - moron4hire 13y ago
      Yes! It was one of the most satisfying moments of my career. The person had been falsifying documents to say they were distributing samples of drugs to doctors, but we're actually selling them on the black market. It taught me a valuable lesson about how seemingly innocuous software can have an impact in the world and that every project deserves competent engineers.
- nousernamesleft 13y ago
  Yeah I was just referencing the standard pattern for doing this, I didn't intend it to be complete code. Using a table that stores a new copy of the item for each "update" and having a convenience view that shows the "current" one is pretty standard, and I don't get how it doesn't accomplish his goal.

That seems like an overly complex way to do the standard: create table foo_history; create view foo as select * from foo_history order by date_added desc limit 1;

moron4hire 13y ago

In the user profile example, you'd only get one profile entry for one user that way. And I don't see what is so complex about it. It's meaning is explicit and clear. The difference from UPSERT is in the requirements. Any notion of an UPSERT would fail to meet my requirements, requirements I have far more often than I don't.

masklinn 13y ago

That's not quite what his scheme does, his handles pre-filled future events (and historical changes thereof), not just appending new events. Also I may be wrong but I believe your view will only ever be filled with a single row. You'd need something along the lines of: create view foo as select distinct on (id) * from foo_history order by id, date_added desc;

moron4hire 13y ago
Yes, exactly. It is basically a time machine. A close reading of some federal compliance laws basically requires either this sort of history tracking or caching of every report ever ran, as the auditing requirements often stipulate needing to be able to "see how the report appeared at the time it was generated".
- platz 13y ago
  Was the perpetrator of the crime caught and punished?
  - moron4hire 13y ago
    Yes! It was one of the most satisfying moments of my career. The person had been falsifying documents to say they were distributing samples of drugs to doctors, but we're actually selling them on the black market. It taught me a valuable lesson about how seemingly innocuous software can have an impact in the world and that every project deserves competent engineers.
nousernamesleft 13y ago
Yeah I was just referencing the standard pattern for doing this, I didn't intend it to be complete code. Using a table that stores a new copy of the item for each "update" and having a convenience view that shows the "current" one is pretty standard, and I don't get how it doesn't accomplish his goal.