"Simon Riggs" <[EMAIL PROTECTED]> writes: > AFAICS we must aggregate the trigger checks. We would need a special > property of triggers that allowed them to be aggregated when two similar > checks arrived. We can then use hash aggregation to accumulate them. We > might conceivably need to spill to disk also, since the aggregation may > not always be effective. But in most cases the tables against which FK > checks are made are significantly smaller than the tables being loaded. > Once we have hash aggregated them, that is then the first part of a hash > join to the target table.
Well we can't aggregate them as they're created because later modifications could delete or update the original records. The SQL spec requires that FK checks be effective at the end of the command. I admit off the top of my head I can't actually come up with any situations which would be covered by the spec. All the instances I can think of involve either Postgres's UPDATE FROM or plpgsql functions or some other postgres specific functionality. But I do seem to recall there were some situations where it mattered. But we could aggregate them when it comes time to actually check them. Or we could hash the FK keys and scan the event list. Or we could sort the two and merge join them.... > We certainly need a TODO item for "improve RI checks during bulk > operations". I have a feeling it's already there. Hm. There's a whole section on RI triggers but the closest I see is this, neither of the links appear to refer to bulk operations: Optimize referential integrity checks http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php http://archives.postgresql.org/pgsql-hackers/2007-04/msg00744.php -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
