Hi,
Maybe there is a simple solution for PostgreSQL behaviour that is
annoying me...
I've got users making updates to a master table and a number of detail
tables. All changes to the master record and related detail records are
encapsulated in a transaction so everything can be rolled back if
necessary and also to lock those master and related records for the user
making the changes.
When they do something that violates a constraint (for example adding a
duplicate detail record where that is not allowed), PostgreSQL aborts
the transaction. What I would much rather have is that PostgreSQL
returns an error but does not cancel the transaction as it's perfectly
OK (from a user's point of view) to try to do something that violates a
constraint.
What annoys me is that I don't think that a constraint violation made by
a user should result in an aborted transaction. There is probably a very
good reason to do that however the logic escapes me...
Of course I can start testing existing values in the database before
accepting them in the user interface but that's putting the horse behind
the cart. I much rather use the constraints at the database level to
tell me a particular update can't be done and do that without loosing
everything else I happened to have done in that transaction until that
point.
Any suggestions?
Jan