Re: Foreign keys for non-default datatypes

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Fri, 3 Mar 2006, Tom Lane wrote:
>> BTW, I had another thought about this: if we go this way, then the plans
>> associated with RI check queries would essentially always be trivial
>> index lookups (for everything except RI_Initial_Check).

> Would we have to do anything odd if we want to be testing only some of the
> index columns and possibly not in the index order (like match partial
> where some of the fk side is null)? I don't honestly see us doing match
> partial any time soon, but I'd like to have an idea of what'd be involved.

Match partial would be an index lookup with a subset of the keys, which
btree at least is fine with. You could argue that a "sufficiently
partial" match would be better done as a seqscan, but I think we could
just bull ahead and do it as indexscans always ...

>> If we did this then RI checks would no longer be subvertible by rules or
>> user triggers.

> I don't think that it'd really help because it's the actions that are
> generally subvertible not the checks and since those are looking at the
> potentially not indexed fk side, I don't think the above would apply.

Oh, right, we'd probably still need to do planning in that case. Unless
we wanted to insist on having an FK-side index too for every FK, which
is something I'm not for.

Do you think it's worth redoing the implementation of just the PK checks?

regards, tom lane