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