Hacker News Reader

Comment by nousernamesleft | Hacker News Reader

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;

View on HN

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;

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".

Was the perpetrator of the crime caught and punished?

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.

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.

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.

Reply on news.ycombinator.com