Re: Optimising Foreign Key checks

On Sat, Jun 08, 2013 at 09:39:14PM +0100, Simon Riggs wrote:
> 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:

> >> 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. "

This does appear to specify FK timing semantics like PostgreSQL gives today.
Namely, it does not permit a FK-induced error when later actions of the query
that prompted the check could possibly remedy the violation.

> I can't see anything there that stops me applying locks as we go, but

Likewise; I don't see why we couldn't perform an optimistic check ASAP and
schedule a final after-statement check when an early check fails.  That
changes performance characteristics without changing semantics.

> I feel like someone will object...
> 
> This point seems crucial to the particular approach we take, so I need
> wider input.

Agreed.

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