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

[ continuing a discussion from mid-August ]

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
>> 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.

> It'd probably be:
> MATCH unspecified:
> SELECT keycolumns FROM referencing_table WHERE
> (keycolumns) NOT IN (SELECT refcols FROM referenced_table
> WHERE refcol1 IS NOT NULL AND ... )
> AND keycolumn1 IS NOT NULL AND ...;

> MATCH FULL: (something like, I haven't tried it)
> SELECT keycolumns FROM referencing_table WHERE
> ((keycolumns) NOT IN (SELECT refcols FROM referenced_table
> WHERE refcol1 IS NOT NULL AND ...)
> AND
> (keycolumn1 IS NOT NULL AND ...)
> )
> OR ((keycolumn1 IS NOT NULL)!=(keycolumn2 IS NOT NULL) OR ...)

I thought of what seems to be a better design for the check query: use
a LEFT JOIN and check for NULL in the righthand joined column. For
example, I think a MATCH UNSPECIFIED on two columns could be tested like
this:

select f1,f2
from fk left join pk on (fk.f1=pk.f1 and fk.f2=pk.f2)
where pk.f1 is null and (fk.f1 is not null and fk.f2 is not null);

and MATCH FULL is the same except

where pk.f1 is null and (fk.f1 is not null OR fk.f2 is not null);

MATCH PARTIAL would be harder; I think you'd need to generate a separate
query for each subset of the columns, in which you would probe for
unmatched rows having exactly that subset non-null. But it could be
done.

Do you see any logical error here?

In some preliminary tests, the planner seems to be able to choose
reasonable plans for this type of query even without pg_statistic data,
as long as it knows the table sizes (which it would do after CREATE INDEX).
So it would work reasonably well during a pg_dump script, I think.

regards, tom lane