On 02/25/2016 01:58 AM, Karsten Hilbert wrote:
> On Wed, Feb 24, 2016 at 04:12:22PM -0800, Adrian Klaver wrote:
>
>>> 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.
>
> Absolutely, it's just that the dump/restore made me realize
> the folly of my ways.
>
>> 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.
>
> Which expects to rely on the fact that triggers are only
> activated after the data has been restored, be it by SQL
> INSERTs or by COPY. However the COPY docs say (under Notes):
>
> COPY FROM will invoke any triggers and check constraints
> on the destination table. However, it will not invoke
> rules.
>
> So there :-)
Well not entirely true ;) -
http://www.postgresql.org/docs/9.4/interactive/app-pgrestore.html
--disable-triggers
This option is relevant only when performing a data-only restore.
It instructs pg_restore to execute commands to temporarily disable
triggers on the target tables while the data is reloaded. Use this if
you have referential integrity checks or other triggers on the tables
that you do not want to invoke during data reload.
Presently, the commands emitted for --disable-triggers must be done
as superuser. So you should also specify a superuser name with -S or,
preferably, run pg_restore as a PostgreSQL superuser.
>
> I'll have to install a BEFORE INSERT/UPDATE trigger which
> forces .is_ongoing to FALSE when
>
> ((.ts_end is not NULL) and .ts_end < now())
>
> That should do the right thing.
>
> Thanks for the input. Helpful as usual.
>
> Karsten
>
--
Adrian Klaver
adrian.klaver@aklaver.com