Re: Optimising Foreign Key checks

On Tue, Jun 04, 2013 at 02:45:17PM +0100, Simon Riggs wrote:
> > On Sun, Jun 02, 2013 at 10:45:21AM +0100, Simon Riggs wrote:
> >> For clarity the 4 problems are
> >> 1. SQL execution overhead
> >> 2. Memory usage
> >> 3. Memory scrolling
> >> 4. Locking overhead, specifically FPWs and WAL records from FK checks
> >> probably in that order or thereabouts.

> Lets rethink things to put a few options on the table and see what we get...

> 2. Don't store FK events in the after trigger queue at all, but apply
> them as we go. That solves problems2 and 3. That could be considered
> to be in violation of the SQL standard, which requires us to apply the
> checks at the end of the statement. We already violate the standard
> with regard to uniqueness checks, so doing it here doesn't seem
> unacceptable.

I wouldn't like to see that compliance bug propagate to other constraint
types.  What clauses in the standard demand end-of-statement timing, anyway?

What if we followed the example of deferred UNIQUE: attempt FK checks as we go
and enqueue an after-trigger recheck when such an initial test fails?

> Implementation: Given we know that COPY uses a ring buffer, and given
> your earlier thoughts on use of a batched SQL, I have a new
> suggestion. Every time the ring buffer fills, we go through the last
> buffers accessed, pulling out all the PKs and then issue them as a
> single SQL statement (as above). We can do that manually, or using the
> block scan mentioned previously. This uses batched SQL to solve
> problem1. It doesn't build up a large data structure in memory,
> problem2, and it also solves problem3 by accessing data blocks before
> they fall out of the ring buffer. If there are no duplicates in the
> referenced table, then this behavious will do as much as possible to
> make accesses to the referenced table also use a small working set.
> (We may even wish to consider making the batched SQL use a separate
> ring buffer for RI accesses). That approach doesn't make any
> assumptions about duplicates.

If this can be made standard-compliant, it sounds most fruitful.

> Perhaps another way would be to avoid very large COPY statements
> altogether, breaking down loads into smaller pieces.

True.  It would be nice for the system to not need such hand-holding, but
that's a largely-adequate tool for coping in the field.

Thanks,
nm

-- 
Noah Misch
EnterpriseDB                                 http://www.enterprisedb.com