Re: pre-commit triggers

On 11/19/2013 08:42 AM, Andres Freund wrote:
> Could you explain a bit what the use case of this is and why it's not
> sufficient to allow constraint triggers to work on a statement level?
> "Just" that there would be multiple ones fired?

The main reason is to enforce arbitrary assertions which need
enforcement at the end of a transaction and not before. For example:

"each person record needs at least one record in the phone_numbers table"

or:

"no person may have more than one work and one home address which are
currently active"

You can't enforce this at the statement level because the
update/insert/deletes can happen in any order on the various tables.
The proposed patch is certainly an inefficient way to implement them
(since your checks get run regardless of which tables were touched), but
any other method would require a large and complex accounting
infrastructure to track which tables were modified and how.

This is the sort of thing the SQL committee covered in ASSERTIONS, but
of course they never specified any method for implementation.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com