On Sun, 1 Oct 2006, rlee0001 wrote:
> I know, for example, that by default PostgreSQL assigns every record a
> small unique identifier called an OID. It seems reasonable then, that
> when the DBA creates a cascading foreign key to a record, that the DBMS
> could, instead of storing the record's entire natural key, store only a
> reference to the OID and abstract/hide this behavior from the
> environment just as PostgreSQL does with its OID feature now. Of
> course, this would require that the OID be guaranteed unique, which I
> don't beleave is the case in the current versions.
>
> This would completely eliminate concerns related to the performance of
> cascading updates because no actual cascade would take place, but
> rather the update would affect all referencing records implicitly via
> the abstraction.
Well, that alone isn't enough I think.
MATCH SIMPLE allows you to pass the constraint for a row if any of the
columns in a multi-column foreign key are NULL, so there isn't always a
matching row, but there's also meaningful information in the column
values. MATCH PARTIAL (which we admittedly don't support yet) allows you
to have a valid key if the non-NULL portions of the multi-column foreign
key match to one or more rows in the referenced table, so there may be
more than one matching row. The all NULL case is pretty easy to handle in
general.
In addition, AFAICT for cascades you would potentially be trading the cost
at cascade time with the cost at select time, so that would itself not
always be a win. Also, I don't see how you get away with not needing two
indexes on the referenced table to do this well unless you're storing
something like a ctid which has its own problems with updates.
I think there may be some better options than what we've got, but there's
tradeoffs as well.