Re: check constraint problem during COPY while pg_upgrade-ing

On 02/24/2016 02:12 PM, Karsten Hilbert 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.
>
> Consider this check constraint
>
>     CHECK (
>         (is_ongoing is false)
>             OR
>         (
>             ((is_ongoing is true) AND (ts_end is null))
>                 OR
>             ((is_ongoing is true) AND (ts_end > now()))
>         )
>     )
>
> (I know this can logically be reduced. I wrote it this way to
> be explicit about the intent.)
>
> 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.

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.

>
> The restore will fail because the inserted row contains
> .is_ongoing=true and .ts_end<now() ...
>
> Of course, dump/restore can't be expected to know about my
> business rules so I wonder what the _suggested_ approach to
> this requirement is ?
>
> (Technically one could use a BEFORE INSERT/UPDATE trigger to
>   check .ts_end and .is_ongoing.)
>
> Thanks for any input,
> Karsten
>


--
Adrian Klaver
adrian.klaver@aklaver.com