Thread: pgsql constraints and temporal tables
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
Enrico Sirola <enrico.sirola@gmail.com> writes: > 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. Use CREATE CONSTRAINT TRIGGER: http://developer.postgresql.org/pgdocs/postgres/sql-createconstraint.html (This statement has been there all along, but was marked as deprecated in pre-8.3 releases, for now-forgotten reasons.) regards, tom lane
Hi Tom! Tom Lane ha scritto: > Enrico Sirola <enrico.sirola@gmail.com> writes: >> 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. > Use CREATE CONSTRAINT TRIGGER: > http://developer.postgresql.org/pgdocs/postgres/sql-createconstraint.html > > (This statement has been there all along, but was marked as deprecated > in pre-8.3 releases, for now-forgotten reasons.) > > regards, tom lane ah! My fault! I created the triggers with "create constraint trigger", but was misleaded by the documentation on constraints, that says triggers are not deferrables. Thanks for the help, e.