Re: INSERT/SELECT and excessive foreign key checks

"Andrew Dunstan" <andrew(at)dunslane(dot)net> writes:

> Could we achieve the same thing in a more general way by having a per-FK tiny
> (say 10?) LRU cache of values checked. Then it wouldn't only be restricted to
> constant expressions. Of course, then the trigger would need to keep state, so
> it might well be too complex (e.g. what if there are are concurrent inserts?)

The danger here is actually that the same transaction (or command in the case
of non-deferred constraints) later deletes the same record. eg, something
like:

INSERT INTO tab VALUES (1) -> queues check for FK value 1
DELETE FROM tab where fk = 1
DELETE FROM othertab where id = 1
INSERT INTO tab VALUES (1) -> skips queing check because 1 is in cache

Now when the triggers fire that check is skipped because the record that fired
it is no longer valid. And the second record

You could do it when it comes time to do the check though. Keep a cache of
values you've already actually performed the check for. But I'm not sure how
much that will save.

I was also thinking of having a cache which simply avoided the SPI query which
would keep the tid and xmin found previously. Then we could look up the actual
record directly instead of going through the executor. As long as the record
is still there with the same xmin then we now the value we cached for it is
still valid. This would help the OLTP case where you're performing many small
transactions which refer to the same records over and over again.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com