Re: Foreign keys for non-default datatypes, redux

On Mon, 12 Feb 2007, Tom Lane wrote:

> I wrote:
> >> * Add an oid[] column to pg_constraint that stores the equality operator
> >> OIDs for a foreign-key constraint, in the same column order as conkey[]
> >> and confkey[].
>
> 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.

> The information I suggested adding to pg_constraint isn't enough to let
> it find out which operator is the right one.
>
> We could handle this in a couple of ways:
>
> 1. Add yet another column with PK=PK operator OIDs to pg_constraint.
>
> 2. Add a column with the underlying PK index's OID to pg_constraint, and
> expect ri_Check_Pk_Match to dredge the info from that. This is probably
> possible, but not exactly trivial because of which-column-is-which
> considerations.
>
> 3. Leave pg_constraint alone and expect ri_Check_Pk_Match to look in
> pg_depend to find out the underlying PK index, then proceed as in #2.
>
> >From an efficiency standpoint #1 seems the best, and yet it seems a bit
> ugly. Not that the others aren't. Comments, other ideas?

I think #1, while ugly, is probably less ugly than the others, although I
guess it means even more work if the underlying type of the column is
changed.

Is there any reason to think that in the future we might need more such
things for some constraints?