SQLite Forum

Shortcut to change a non changing update or upsert in a noop
Login
> I am sure you would agree that changes() should report actual inserts and rewrite of rows on update, regardless of whether the latter changed any bits.

I don't agree, or disagree. "Changes()", like any other function, should report what its documentation claims for it to report. Whether or not this involves actual "bits on disk" changing or not I cannot seem to find reference to in its documentation, so have no basis to argue one or way or the other.

What now transpires, regardless of what "changes()" report, is the question of "Do actual bits change on disk?", or more succinctly, does a physical file write happen if no bits have changed, or not?

If the answer is NO, then the act of trying to prevent writes by adding a lot of extra time-consuming conditions is a fool's errand.  If the answer is YES, that process may well be worth the effort.

You will find that MSSQL and MySQL for instance internally avoid writes that are not needed but then also report so in their respective "row_count()" or similar functions, though options exist to change this behaviour to show all rows matching the search rather than those actually changed, if needed.

I do not really care how SQLite reports it, as long as it is documented so, and understanding how it functions to know whether or not it is worth adding code to avoid unnecessary writes or not.


I would love a definitive answer on that.


PS:
Upon re-reading my post I think some confusion comes from this sentence:

> Whatever the reason, you may have good reason to actually avoid it...

Which can be interpreted as me warning off the habit of checking to avoid writes, but in fact, with that unfortunate phrase, by "avoid it" I really meant "avoiding the updates yourself" - as in I precisely encouraged what the OP did and is in full agreement with your statement.

I realise now also that a lot of deliberation water flowed under the bridge, yet the original question remains unanswered. I'm not sure there is a better way to do it (i.e. avoid the updates) than what the OP described, should SQLite not avoid it internally.