Re: Fixing handling of constraint triggers

2010/1/18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Dean Rasheed <dean(dot)a(dot)rasheed(at)googlemail(dot)com> writes:
>> Agreed. That's much neater. However, it does introduce a change in
>> behaviour - if you have 2 constraints with the same name in different
>> schemas, one deferrable, and one not, and the non-deferrable one is
>> first on the search path, then you'll get an error if you try to defer
>> the other one, referring to it with an unqualified name. This used to
>> work, when it was searching for the trigger first.
>
>> So perhaps the code should continue searching after finding a
>> non-deferrable constraint, remembering the fact that it found one, and
>> only report the error at the end if it didn't also find a deferrable
>> one.
>
> I went around on that a few times before committing.  It's not really
> true that it used to work, at least not for cases where the
> nondeferrable constraint was also an FK constraint; that threw an error
> just as now.  What we've done is opened up the search so that it will
> find nondeferrable index constraints, which is more or less the whole
> point of Hubert's request.
>

Ah OK. It was unique constraints that I was thinking of when I looked
at it, and in the scenario described above it used to work by ignoring
the non-deferrable one and just applying to the deferrable one. But
since that's new functionality, no one's likely to notice the change.
I think the new behaviour makes more sense and it's now consistent
with FK constraints, in throwing an error if the first matching
constraint on the search path is not deferrable.

> The part that I actually think is weird is that the search stops after
> the first schema in which it finds any matches.  If we removed that then
> we could just document the behavior as "sets the constraint mode for all
> matching constraint names", full stop.  Which definitely seems easier
> to understand than what we have now.  (The SQL spec is no big help here
> btw, it just says "if a <constraint name> is specified, then it shall
> identify a deferrable constraint".  No clarity about what "identify"
> means.)
>

Yeah I could go either way on that, so I guess for compatibility it's
best to stick with the existing behaviour. It's not that surprising to
have it apply only to the first match on the search path. Users can
always use schema qualification if needed.

I only really raised this because I thought the behaviour was
changing, but that's not really the case for users who never had
deferrable uniqueness constraints before, so FWIW I'm happy to stick
with the existing code.

Cheers,
Dean