From: | Noah Misch <noah(at)leadboat(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Optimising Foreign Key checks |
Date: | 2013-06-08 21:41:33 |
Message-ID: | 20130608214133.GA445736@tornado.leadboat.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Sat, Jun 08, 2013 at 09:39:14PM +0100, Simon Riggs wrote:
> On 8 June 2013 15:30, Noah Misch <noah(at)leadboat(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2013-06-08 21:45:27 | Re: Cost limited statements RFC |
Previous Message | Tom Lane | 2013-06-08 21:27:16 | Re: Proposed patch: remove hard-coded limit MAX_ALLOCATED_DESCS |