Hello,
I'm reading "Developing Time-Oriented Database Applications in SQL" by
Richard Snodgrass, and trying to reimplement some of the examples using
postgresql. The book is about temporal tables and applications involving
time-varying data; the one of the main difficulties with such problems
is that SQL does not support "temporal" referential integrity, so you
have to code it with triggers or checks (well, in the pgsql case
triggers, because check constraints doesn't support subqueries which are
useful in these cases).
However, I'm facing a problem I'm not able to solve with postgresql:
usually updating or deleting time-varying data means to temporary
violate a constraint in a transaction but ensuring that at the end of
the transaction the table(s) is(are) in a consistent state, but this
seems impossible to do because trigger constraints are not deferrable.
So here is the question: how do you use trigger constraints to ensure
complex referential integrity and at the same time are still able to
update the table data? I'm sure this question is maybe a bit too
generic, I hope someone here in the list readed the book (which is very
interesting and electronic copies are available for free) and is willing
to help. If interested, I can post an example here...
Thanks,
Enrico