Re: INSERT/SELECT and excessive foreign key checks

Lodewijk Voege <lvoege(at)gmail(dot)com> writes:
> I hacked up a patch that handles these two cases:

"Hack" is the right word. People keep proposing variants of the idea
that the executor should optimize updates on the basis of examining
the query tree to see whether columns changed or not, and they're always
wrong. You don't know what else might have been done to the row by
BEFORE triggers.

An additional problem with your proposal is that it fails to consider
other changes that might be happening concurrently -- eg, what if some
other backend deletes a source row after you copy it, and commits before
you do? There would be an interval with no committed row having that FK
value, and no one holding a row lock on the referenced PK row, so some
third transaction could delete the PK row.

There are some implementation problems in this patch too, but it's not
necessary to delve into such details when the basic premise is
unworkable. Sorry...

regards, tom lane