Heyho!
I was trying to implement a deferred NOT NULL constraint using a deferred
constraint trigger (on update and insert of this row) because some values
would be filled in later during the transaction, after the initial part of
the record has been filled.
I asked myself if a type of deferred trigger that would
* trigger only once per affected row
* with the NEW value set to what is about to be committed (OLD should
probably be OLD from when the trigger was first fired the first time)
might not be useful (compared with the current model -- trigger it once for
each operation, with OLD/NEW being the same as for non-deferred trigger)
At least, I was expecting this behaviour (undoubtedly because I only skimmed
the docs and did not really read them thoroughly ;-) and was surprised when
I got my error for a non-null value (IF .. IS NULL THEN RAISE ...), when I
did set the value in an UPDATE during the same transaction... [0]
I'm neither an SQL guru nor familiar with PostgreSQL internals. I was only
starting from the viewpoint of deferred triggers as an implementation for
deferred NOT NULL (or other CHECK) constraints. There may as well be other
usecases where the current behaviour is appropriate.
(ironically it turned out that I didn't think about my DB schema carefully
enough and this particular column did not need the NOT NULL constraint, so I
scrapped the trigger.)
cheers
-- vbi
[0] The implementation I ended with was PERFORM ... WHERE id = NEW.id AND
mycol IS NULL and then RAISing if FOUND; the id will not change. But the
fact that this may end up being executed several times at commit seems less
than ideal.
--
featured link: http://www.pool.ntp.org