On Wed, Feb 24, 2016 at 3:12 PM, Karsten Hilbert <karsten.hilb...@gmx.net> wrote:
> 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.
