complex DELETE
I confess I don't quite understand the motivation behind that SQL statement. It seems to be about finding set difference of kept_ranges and then un-nesting?
I predict that when you recruit additional engineers
to this project, they will be reluctant to maintain that statement,
as it looks "dangerous", looks like it would be easy
to accidentally discard important rows in the course
of testing it.
Consider adding an is_old column to the base relation,
or perhaps to a VIEW that ties a two-column table to the base relation.
We update it to show which rows are "old",
INSERT corresponding rows, and DELETE the original rows.
Notice that an interactive tester can perform just
the first step and verify that it seems to be going as expected.
automated tests
The OP would be more easily understood if it were accompanied by a small test suite. Tests have documentation value.
Including the output of SHOW CREATE TABLE ... would also make it easier to understand the OP code.
diagnostic error message
This is terrible:
raise Exception
Maybe it "never" happens? We need a better description of what went south, so a maintenance engineer has some hope of diagnosing root cause and fixing the bug.
informative identifier names
for n_serial_range in ... :
I think that means for new_serial_range?
Saving two ew characters hardly seems worth it.
If you want a shorter identifier, elide the word _serial.
simplicity
I don't know how big your ranges are, nor what memory constraints you work within. But consider finding set difference with something like
range2 = range(lo, hi)
range3 = set(range1) - set(range2)
It's horribly inefficient, but there's no special cases. Possibly you'll want a helper which inspects the set(), verifies "no gaps!", and turns it back into a range().
Or write a proper SerialRange class that uses arithmetic to accomplish the same thing. I think your code avoids MultiRanges?