* Don't manually modify database without a well-tested procedure and another pair of eyes
* Don't leave persistent problems (e.g. memory problems) uninvestigated so that you miss new problems with similar symptoms
* Don't push new code to production while operational problem is ongoing (unless it addresses the operational problem)
I'm pretty sure I've repeated this exact same sequence before with similar results...
Even if you think that the code that creates and modifies your data will not put it in some undesired state, the code that uses this data should assume that the data may be in all undesired states you can dream up and should do its best not to do something seriously bad when that happens (like landing in endless loop/recursion or executing possibly user provided strings).
In our production database, I used CHECK constraints religiously. Worked great.
Then one day, I was no longer able to commit ANY transactions to a particular table, even completely innocuous ones.
The problem? The database itself had violated its own CHECK constraint on a previous commit, but was enforcing it on all subsequent commits, causing them to fail. Brilliant.
Moral: not even CHECK constraints will save you.
----
P.s. This was a proprietary database, and when I reported the problem to the vendor (eventually, I figured out how to reproduce it), the vendor actually refunded our (expensive) support contract rather than fix the bug -- they couldn't figure out how to fix it despite having a small bug report that reproduced the problem.
In the end, I actually had to remove the CHECK constraint altogether. :(
I've never heard of that, and unless you're using a really buggy, broken database, it should not be possible.
> P.s. This was a proprietary database, and when I reported the problem to the vendor
well there you go. I think in practice, a simple CHECK constraint like the one we'd do here (literally, comment_id > parent_comment_id) is pretty easy to put one's faith into.