Re: check constraint problem during COPY while pg_upgrade-ing

On Wed, Feb 24, 2016 at 04:12:22PM -0800, Adrian Klaver wrote:

> >This works fine, the application (GNUmed) ensures INSERTS and
> >UPDATES do the right thing with .is_ongoing and .ts_end.
> >
> >Now the following sequence happens:
> >
> >- insert row with .is_ongoing=true and .ts_end=tomorrow()
> >- wait a week
> >- dump
> >- restore
>
> Seems to be you are caught in a logical bind even with out the dump/restore.

Absolutely, it's just that the dump/restore made me realize
the folly of my ways.

> At some point past tomorrow(), absent a change in is_ongoing, you will have
> a row where is_ongoing is 't' but ts_end says the therapy is over. To my way
> of thinking this means having ts_end be NULL until the therapy is completed
> or have a periodic job that marks is_ongoing = 'f' when ts_end goes into the
> past and is_ongoing = 't'. Otherwise resort to the trigger method you
> suggest below.

Which expects to rely on the fact that triggers are only
activated after the data has been restored, be it by SQL
INSERTs or by COPY. However the COPY docs say (under Notes):

    COPY FROM will invoke any triggers and check constraints
    on the destination table. However, it will not invoke
    rules.

So there :-)

I'll have to install a BEFORE INSERT/UPDATE trigger which
forces .is_ongoing to FALSE when

    ((.ts_end is not NULL) and .ts_end < now())

That should do the right thing.

Thanks for the input. Helpful as usual.

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