Stephan Szabo wrote:
> 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.
LOL, I hadn't even considered that. In my proposed solution, every
select against a table containing a foreign key (which selects at least
part of the foreign key) would require an implicit join to retrieve the
actual foreign key values. Additionally, selects with explicit joins
would need to be converted, which would require the DBMS to execute
several additional queries (to retrieve the internal surrogate key for
the given natural key values) before executing the original join query.
Oh well, can't kill a guy for trying.
Thanks for pointing out what I was too blind to see. :o)
-Robert
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org