On 8 June 2013 15:30, Noah Misch <n...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
