On Mon, Jun 10, 2013 at 09:05:40AM +0100, Simon Riggs wrote:
> Your earlier comments argue that it is OK to make an early check. The
> above seems to argue the opposite, not sure.
I'll attempt to summarize. If we execute a traditional error-throwing FK
check any earlier than we execute it today, applications with certain triggers
will notice a behavior change (probably not OK). However, we *can* safely
execute an optimistic FK check as early as just after ExecInsertIndexTuples().
If the optimistic check is successful, later activity cannot invalidate its
success as concerns that particular inserted tuple.
> IIUYC we can do this:
>
> * search hash table for a value, if found, skip check and continue
> * if entry in hash not found make an immediate FK check
> * if the check passes, store value in hash table, if it fits
> * if check does not pass or value doesn't fit, queue up an after
> trigger queue entry
Why shall doesn't-fit prompt an after-statement recheck?
You do need a mechanism to invalidate table entries or the entire table. As a
first cut at that, perhaps have parent table RI triggers empty any local hash
tables of the same FK relationship. Note that invalidating table entries does
not invalidate skips already done on the strength of those entries.
> except we want to batch things a little, so same algo just with a
> little batching.
>
> * search hash table for a value, if found, skip check and continue
> * if entry in hash not found add to next batch of checks and continue
> * when batch full make immediate FK checks for whole batch in one SQL stmt
> * if a check passes, store value in hash table, if it fits
> * if check does not pass or value doesn't fit, queue up an after
> trigger queue entry
> * when executing queue, use batches to reduce number of SQL stmts
I think this all can be made to work, too.
--
Noah Misch
EnterpriseDB http://www.enterprisedb.com
