ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Fri, 15 Aug 2003, Christopher Kings-Lynne wrote:
>> I can also attest to the horrendously long time it takes to restore the ADD
>> FOREIGN KEY section...

> That really needs to be rewritten to do a single check over the table
> rather than running the constraint for every row. I keep meaning to get
> around to it and never actually do. :( I'm not sure that in practice
> you'll get a better plan at restore time depending on what the default
> statistics give you.

In simple cases I think that the creation of indexes would be enough to
get you a passable plan --- CREATE INDEX does update pg_class.reltuples,
so the planner will know how big the tables are, and for single-column
primary keys the existence of a unique index is enough to cue the
planner that the column is unique, even without any ANALYZE stats.
Those are the biggest levers on the plan choice.

This assumes that pg_dump always dumps CREATE INDEX before ADD FOREIGN
KEY; I'm not certain if there's anything to enforce that at the
moment...

I assume what you have in mind is to replace
validateForeignKeyConstraint() with something that does a join of the
two tables via an SPI command. But supposing that we want to keep the
present ability to report (one of) the failing key values, it seems
like the query has to look like
SELECT keycolumns FROM referencing_table WHERE
keycolumns NOT IN (SELECT refcols FROM referenced_table);
which is only gonna do the right thing for one of the MATCH styles
(not sure which, offhand ... actually it may not do the right thing
for any match style if there are nulls in referenced_table ...).
How would you make it work for all the MATCH styles? And will it
really be all that efficient? (NOT IN is a lot more circumscribed
than IN.)

regards, tom lane