Thread: thoughts about constraint trigger

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

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/

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