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
Дата:
FAQ