Thread

1. thoughts about constraint trigger

   Adrian von Bidder <avbidder@fortytwo.ch> — 2010-06-14T18:33:00Z

   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
   

2. Re: thoughts about constraint trigger

   Craig Ringer <craig@postnewspapers.com.au> — 2010-06-15T06:03:49Z

   On 15/06/10 02:33, Adrian von Bidder wrote:
   > 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.
   
   AFAIK, at this point only FOREIGN KEY constraints may be deferred.
   
   http://www.postgresql.org/docs/current/static/sql-set-constraints.html
   http://www.postgresql.org/docs/current/static/sql-createtable.html
   
   "DEFERRABLE
   NOT DEFERRABLE
   
       This controls whether the constraint can be deferred. A constraint
   that is not deferrable will be checked immediately after every command.
   Checking of constraints that are deferrable can be postponed until the
   end of the transaction (using the SET CONSTRAINTS command). NOT
   DEFERRABLE is the default. Only foreign key constraints currently accept
   this clause. All other constraint types are not deferrable."
   
   -- 
   Craig Ringer
   
   Tech-related writing: http://soapyfrogs.blogspot.com/
   
   

3. Re: thoughts about constraint trigger

   Adrian von Bidder <avbidder@fortytwo.ch> — 2010-06-15T11:46:43Z

   On Tuesday 15 June 2010 08.03:49 Craig Ringer wrote:
   > AFAIK, at this point only FOREIGN KEY constraints may be deferred.
   
   I think you didn't understand what I wrote.   9.0 allows to defer UNIQUE as 
   well, but not NOT NULL, which is why I wrote a derred constraint trigger to 
   implement it, which behaved slightly different from what I expected, which 
   lead me to wonder if my expectation was so far off ...
   
   cheers
   -- vbi
   
   -- 
   "Valentine's Day is the one holiday when everyone is expected to do
   something romantic for their spouse or lover -- and if someone has both,
   it's a serious problem. ...  planning a 'business trip' that falls over
   Valentine's Day is a typical mistake cheaters make." ... So now I'm
   wondering why the RSA Conference is being held over Valentine's Day.
           -- Bruce Schneier quoting the Wall Street Journal