From: "Bath, David" <dave.bath@unix.net>
Reply-To: dave.bath@unix.net
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Yes,
	pg does triggers first before asserting check constraints! Was Re:
	why vacuum
Date: Thu, 27 Oct 2005 10:42:10 +1000
User-Agent: KMail/1.8
References: <200510261015.17623.lawgon@thenilgiris.com>
	<200510261622.40874.dave.bath@unix.net>
	<2827.1130310024@sss.pgh.pa.us>
In-Reply-To: <2827.1130310024@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Message-Id: <200510271042.11487.dave.bath@unix.net>

Tom,

After I wrote
> > Sybase/MS-SQL's check constraint model asserts the constraint
> > BEFORE the trigger, which discourages you from attempting to>
> > check and handle meaning of data! 
you wrote (2005-10-26 17:00)
> Er, doesn't PG do it that way too?

Well, it works for me!  In this case (with examples and caveats
below), postgresql (and Oracle) have got it right, which is a BIG
reason why you never get an Oracle guy to define Sybase/MS-SQL
systems - the other big reason is when you look at the sybase/mssql
nestlevel internal parameter - got the scars to prove it!

Quite frankly, if pg did not allow you to tidy things during
pre-insert and pre-update triggers, I wouldn't be so keen on it,
and stick to Oracle.

My general approach is:
1) Pre-* triggers are for tidying up the data to cover for
   what is obviously a typo by user and their intent is clear
2) Post-* triggers are for propagating required data changes,
   i.e. implications such as updating the "current balance"
   attribute in a "customer account" record whenever the
   dollar value in a transaction detail record changes.

Let me give a simple example:
1) Define attribute x as a varchar(whatever).
2) Ensure x has no leading/trailing whites
   ... CHECK ((NOT (X ~ '^[ \t\n\r]')) AND (NOT (X ~ '[ \t\n\r]$')))
3) During "BEFORE INSERT" and "BEFORE UPDATE" (rowlevel) triggers, include
   NEW.x := btrim(NEW.x, ' \t\n\r');
4) INSERT INTO y (x, ...) VALUES ('\tblah blahdy blah\n', ...)
5) SELECT x FROM y WHERE ...
   Get 'blah blahdy blah' back.
Any processing overhead is trivial compared to the time wasted by
users, by a dba when the user's complain, or undesired application
behaviour when developers make invalid assumptions about the data.

Another useful example, based on my opinion/experience that
any change of arbitrary primary keys is imnsho wrong-headed
and recoverable, I usually do the following in a pre-update
row-level trigger, especially when pk is set from a sequence:
  NEW.pk := OLD.pk ;
or are least
  NEW.pk := coalesce(NEW.pk, OLD.pk)

Caveat:
This approach DOES NOT WORK if we
1) define a domain (z) as a varchar(whatever),
2) put the constraint on z
3) use domain z as the datatype for x
4) attempt to change x during pre* rowlevel triggers as above
   as pg barfs as soon as you assign a value to a domain that
   breaks its constraint.
This caveat prompted my recent question on this list about the
possibility of a "pre-assert trigger" on a domain.  (Version 9?
Pretty please with sugar on top?).

Wow! pg is even smarter than even YOU thought Tom!  For this to
be the case, you guys must be excellent designers and coders,
and I tips my lid to you.  Bugs are common, serendipitous useful
features are almost as rare as neonates with teeth.

-- 
dave.bath@unix.net
Question for the day: delta(dummies_guide, executive_summary)=?