Ok, so this is Hacker News, it's in the name, and most of us are aware that HN is also a research/hobby project. It's not made to be an rock-stable enterprise system doing bank transactions or what not, so I think what pg did was prefectly excusable. People make mistakes. Nobody will die without HN for a day or two, and it won't affect the site's popularity one bit.

You are right though, making mistakes is human as they say, and nobody dies because of this. In fact, less popularity might be good for the site's content quality. I'm just surprised by how much they care about thousands of hourly users, that what I would dream of having.

Focus on providing people what they want/need, and don't worry so much about having flawless technology until you can employ a horde of PHDs.

So, how many customers do you think he lost because of this? The answer is two, and one of them signed back up because they were impressed by the great job he did in handling the fiasco.

Moral of the story: As long as you really are making your best effort, you might be surprised how willing people are to deal with human error. Yes, they might be be mad, but a mistake is (usually) not the end of the world.

For what it's worth, I upvoted this thread specifically because we've all done something this stupid (or worse) :)

But that apart, even if there were an admin button to change the parent id of a thread, he would still have made the same mistake.

Unless the code in question was checking for loops. In that case, repl would have worked the same.

This is the kind of mistake one would make even if you were writing proper migrations. He was doing things live isn't an issue; neither is an incorrect id. The issue is the code doesn't check for loops.

One, if the data were held in a database, should a change like this be captured in the database logs? I am seeing more and more situations where I want these, I notice that they are by default turned off for mysql and wonder if this reflects a de facto judgment that logging slows performance more than is usually worthwhile.

Two, if the data were kept in a database, wouldn't something like this be prevented by a constraint preventing a comment from making itself an ancestor? But I suppose there is a slight performance hit in checking such constraints, and the case arises so rarely that this hit isn't generally worthwhile.

I think it's more like your application is doing the logging already(probably; most of the frameworks do). If you really need it, turn it on yourself.

> Two, if the data were kept in a database, wouldn't something like this be prevented by a constraint preventing a comment from making itself an ancestor?

Copy pasting the table from another comment.

    create table post (id int primary_key, parent_id int references post(id), child_id int references post(id), created_at timestamp)

This isn't really a big problem to solve. pg simply overlooked this problem. Had he not, he would have checked child.created_at > parent.created_at in his mutator method. So, when you do a post.parent = some_post(assuming mutator is parent=; replace it with post.setParent or (send post set-parent some-post) or whatever), it checks if post.created_at > some_post.created_at, and then assigns post.parent_id = some_post.id

More generally:

I'm not a big SQL wonk anymore, but I find a lot of people have the intuition that relational databases are ill-suited for trees.

An intuition that is much closer to the truth is that almost all databases can handle trees pretty well, because there's still an unambiguous concept of ordering and containment, and you can usually arrange things so as to do range/ancestor/inclusion queries efficiently.

It's graphs with loops/without unambiguous concept of ordering/containment that are really hard.

One way involves accumulating an array of nodes already visited as the tree gets walked, checking each node as-visited for membership in the array-to-date.

The other method, a bit more of a hack, is just adding a LIMIT clause.

I think the 'WITH' clause is a great addition to the SQL standard, very much worth the learning the weirdness of its syntax and its optional 'RECURSIVE' term (which, as the Postgres documentation points out, isn't really recursion, it's iteration).

But in this case, writing a before insert/update trigger which ensure some_post.created_at < parent.created_at before setting parent.parent_id = some_post will do the trick.

Oh manually modifying production database on the fly ain't unheard of.

However it's still not "very Chuck Norris" on a scale of Chuck Norrisness compared to the modification of a running app directly in the REPL. I mean: it doesn't matter if you manually modify the DB itself or not when you directly modify the app from the REPL itself (the app being anyway "in charge" of the DB).

Sure, modifying manually the production DB might be an issue to some. But I can guarantee you that it's the last of your worries when you're actually modifying production code directly from the REPL ; )