"Andrew Dunstan" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings