Re: INSERT/SELECT and excessive foreign key checks

On 19-aug-2007, at 12:38, Tom Lane wrote:

> "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.

but that's something it can check for. if there are BEFORE triggers
on the table, don't do it.

> 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.

so if it checks those FKs being carried over also only once, that
would close that hole, right?

it would just be nice to not have to disable triggers altogether in
this case. there is a person twiddling his/her thumbs while all this
checking and re-checking is going on.

Lodewijk