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
Attachment:
signature.asc
Description: This is a digitally signed message part.
