check constraint problem during COPY while pg_upgrade-ing
От
Karsten Hilbert
Тема
check constraint problem during COPY while pg_upgrade-ing
Дата
Msg-id
20160224221209.GE4526@hermes.hilbert.loc
Список
Дерево обсуждения
check constraint problem during COPY while pg_upgrade-ing Karsten Hilbert <Karsten.Hilbert@gmx.net>
Re: check constraint problem during COPY while
pg_upgrade-ing Adrian Klaver <adrian.klaver@aklaver.com>
Re: check constraint problem during COPY while
pg_upgrade-ing Karsten Hilbert <Karsten.Hilbert@gmx.net>
Re: check constraint problem during COPY while
pg_upgrade-ing Adrian Klaver <adrian.klaver@aklaver.com>
Re: check constraint problem during COPY while pg_upgrade-ing "David G. Johnston" <david.g.johnston@gmail.com>
Re: check constraint problem during COPY while
pg_upgrade-ing Karsten Hilbert <Karsten.Hilbert@gmx.net>
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 The restore will fail because the inserted row contains .is_ongoing=true and .ts_end
В списке pgsql-general по дате отправления
От: Stephen Frost
Дата:
От: Adrian Klaver
Дата: