Re: Foreign keys for non-default datatypes, redux

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Mon, 12 Feb 2007, Tom Lane wrote:
>> It turns out this isn't sufficient: ri_Check_Pk_Match() wants to
>> generate PK = PK checks, and the PK = FK operator isn't the right one
>> for that.

> Ugh, right, for modifications of the pk side with no action to make sure
> there isn't a new row with that key.

When I looked closer I found out that ri_AttributesEqual() is applied to
pairs of FK values as well as pairs of PK values. The old coding was
looking up the default btree operator for the types, which is close but
not close enough, if we want to allow for the possibility of unique
indexes built on non-default operator classes. So I ended up with three
new columns in pg_constraint --- PK=FK, PK=PK, FK=FK operator OIDs.
Anyway it's all done and committed.

It strikes me BTW that the RI mechanism was vulnerable to the same sort
of search_path-based shenanigans that Peter is worried about: any RI
checks triggered within a SECURITY DEFINER function could have been
subverted by substituting a different "=" operator.

regards, tom lane