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(at)leadboat(dot)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