Postgres Pro
Downloads
Home   >   mailing lists

Re: check constraint problem during COPY while pg_upgrade-ing - Mailing list pgsql-general

From David G. Johnston
Subject Re: check constraint problem during COPY while pg_upgrade-ing
Date
Msg-id CAKFQuwaypLi5vbAEBhDNJCiU+j2A5qLvz60qKYNJGepLNyP3jg@mail.gmail.com
Whole thread Raw
In response to check constraint problem during COPY while pg_upgrade-ing  (Karsten Hilbert <Karsten.Hilbert@gmx.net>)
Responses Re: check constraint problem during COPY while pg_upgrade-ing
List pgsql-general
Tree view
On Wed, Feb 24, 2016 at 3:12 PM, Karsten Hilbert <Karsten.Hilbert@gmx.net> 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.

​Then you must record the "INSERT/UPDATE time" into the table, as a constant, and refer to that value instead of having "now()" which happens to be correct at the time of the insert/update but is not correct at any future point.

So:

ts_last_change DEFAULT now()
and
CHECK (...
(is_ongoing is true) AND (ts_end >
​ts_last_change
)
​)​

​In this way a restoration will restore both the historical ts_last_change and the value of is_ongoing that matched.​

David J.

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: check constraint problem during COPY while pg_upgrade-ing
Next
From: Maxim Boguk
Date:
Subject: 2x Time difference between first and subsequent run of the same query on fresh established connection (on linux, with perf data included, all query data in the shared buffers) on postgresql 9.3.10. Any explanation?