pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Yes, pg does triggers first before asserting check constraints! Was Re: why vacuum 2+ messages / 2 participants [nested] [flat]
* Yes, pg does triggers first before asserting check constraints! Was Re: why vacuum @ 2005-10-27 00:42 Bath, David <dave.bath@unix.net> 2005-10-27 00:55 ` Re: Yes, pg does triggers first before asserting check constraints! Was Re: why vacuum Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Bath, David @ 2005-10-27 00:42 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql 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)=? ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Yes, pg does triggers first before asserting check constraints! Was Re: why vacuum 2005-10-27 00:42 Yes, pg does triggers first before asserting check constraints! Was Re: why vacuum Bath, David <dave.bath@unix.net> @ 2005-10-27 00:55 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2005-10-27 00:55 UTC (permalink / raw) To: dave.bath@unix.net; +Cc: pgsql-sql "Bath, David" <dave.bath@unix.net> writes: > you wrote (2005-10-26 17:00) >>> 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! >> Er, doesn't PG do it that way too? > Well, it works for me! You're right of course. I was confusing this with a related problem that people occasionally complain about, which is that datatype-related errors are thrown before the trigger can do anything about them. For instance, you can't stuff "abc" into a int4 field and hope that the trigger will have a chance to replace it with something valid. (Yes, people have asked for that :-() A less silly example is that domain constraints on a field of a domain type get checked before the trigger can run. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-10-27 00:55 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-10-27 00:42 Yes, pg does triggers first before asserting check constraints! Was Re: why vacuum Bath, David <dave.bath@unix.net> 2005-10-27 00:55 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox