I have noticed another problem during pg_upgrade on a 9.1
cluster with 9.4 as the target.
Consider this sort of table
create table therapy (
pk serial primary key,
description text,
is_ongoing boolean not null,
ts_end timestamp with time zone
);
Now, business rules say that a particular therapy is either
ongoing or not. The end of therapy can be known or not.
...
However, if the therapy is ongoing the ts_end must be either
NULL or "in the future" at row INSERT/UPDATE time.
Then you must record the "INSERT/UPDATE time" into the table, as a constant, and refer to that value instead of having "now()" which happens to be correct at the time of the insert/update but is not correct at any future point.
So:
ts_last_change DEFAULT now()
and
CHECK (...
(is_ongoing is true) AND (ts_end > ts_last_change
))
In this way a restoration will restore both the historical ts_last_change and the value of is_ongoing that matched.
David J.
