Re: Optimising Foreign Key checks

On 8 June 2013 15:30, Noah Misch <noah@leadboat.com> wrote:
> 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?

The copy I have access to (2008 draft), 4.17.2 Checking of constraints

"The checking of a constraint depends on its constraint mode within
the current SQL-transaction. Whenever an SQL-statement is executed,
every constraint whose mode is immediate is checked, at a certain
point after any changes to SQL-data and schemas resulting from that
execution have been effected, to see if it is satisfied. A constraint
is satisfied if and only if the applicable <search condition> included
in its descriptor evaluates to True or Unknown. If any constraint is
not satisfied, then any changes to SQL-data or schemas resulting from
executing that statement are canceled. (See the General Rules of
Subclause 13.5, “<SQL procedure statement>”.

NOTE 31 — This includes SQL-statements that are executed as a direct
result or an indirect result of executing a different SQL-statement.
It also includes statements whose effects explicitly or implicitly
include setting the constraint mode to immediate. "


I can't see anything there that stops me applying locks as we go, but
I feel like someone will object...

This point seems crucial to the particular approach we take, so I need
wider input.

--Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services