Re: Constraint enforcement

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thanks, Tom.

This doesn't quite answer the case in which, say, an UPDATE is performed
and the underlying table has a .

OK, a piece of the puzzle is in place- all CHECKs are performed before
all TRIGGERs. But what is the actual order of constraint enforcement,
considering PKs, FKs, UNIQUEs, CHECKs, COLUMN TYPES/DOMAINs, NOT NULLs,
and TRIGGERs? Are all constraints always enforced, or does postgres
always find the *exact* set of constraints it needs to check for the
specific command? Take the very simplistic example below:

UPDATE foo SET name='foo', tag='bar' WHERE name='joe';

and

UPDATE foo SET name='foo' WHERE name='joe';

Assuming some constraints on column "tag", and PK "name", will both
commands trigger the same sets of constraint enforcements, or will the
second command trigger only the *necessary* checks, given that the "tag"
column is not modified? In what order are PK, TYPE, NOT NULLs, etc
checked on "name"? Are the constraints on "tag" ever checked on the
second command?

Thank you,

Demian

Tom Lane wrote:
> Demian Lessa <demian(at)lessa(dot)org> writes:
>> What I'm trying to figure out is how postgre identifies the *exact* set
>> of constraints to enforce for each of the different commands above, and
>> the order in which these are performed (since some checks are way more
>> expensive than others).
>
> Triggers on the same event are fired in alphabetical order by "tgname".
> I don't believe any particular ordering is enforced among CHECK
> constraints --- but they all happen before any triggers fire.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFF1JfNWs7G5iIp9akRAgEQAJ9IWPgaxUNzwymucc8pqIdAEPM/GgCfYqSv
1PCKTVtXlwCW33M4532nYm0=
=1cFT
-----END PGP SIGNATURE-----