Re: check constraint problem during COPY while pg_upgrade-ing

On Wed, Feb 24, 2016 at 05:24:44PM -0700, David G. Johnston wrote:

> ​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.​

Thanks for this suggestion. In fact, my table data audit
system already provides for such a column, namely
.modified_when.

However, I still like to have the trigger to set .is_ongoing
to FALSE when .ts_end goes into the past (as of the time of
an UPDATE to the row), regardless of the fact that the
constraint between .ts_end and $TS_LAST_CHANGE (here:
.modified_when) is formally fulfilled. The reason being that
that does not really represent the business intent of the
constraint, which is

WHENEVER
therapy.is_ongoing is true,
THEN (at the time of WHENEVER)
therapy.ts_end must be unknown OR in
the (real world) future

:-)

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346