Re: Optimising Foreign Key checks

On Sat, Jun 08, 2013 at 08:20:42PM -0400, Robert Haas wrote:
> On Sat, Jun 8, 2013 at 5:41 PM, Noah Misch <noah@leadboat.com> wrote:
> > 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.
> 
> ...this seems like it might have some promise; but what if the action
> we're performing isn't idempotent?  And how do we know?

The action discussed so far is RI_FKey_check_ins().  It acquires a KEY SHARE
lock (idempotent by nature) on a row that it finds using B-tree equality
(presumed IMMUTABLE, thus idempotent).  RI_FKey_check_upd() is nearly the same
action, so the same argument holds.  Before treating any other operation in
the same way, one would need to conduct similar analysis.

Thanks,
nm

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