On 8 June 2013 15:30, Noah Misch 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
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

Search Discussions

Discussion Posts


Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 11 of 21 | next ›
Discussion Overview
grouppgsql-hackers @
postedJun 1, '13 at 8:41a
activeJun 11, '13 at 4:09a



site design / logo © 2021 Grokbase