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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
