Re: INSERT/SELECT and excessive foreign key checks

Andrew Dunstan wrote:
> Could we achieve the same thing in a more general way by having a
> per-FK tiny (say 10?) LRU cache of values checked. Then it wouldn't
> only be restricted to constant expressions. Of course, then the
> trigger would need to keep state, so it might well be too complex
> (e.g. what if there are are concurrent inserts?)
>
I was wondering whether one could try to identify what might be termed
'enum tables' that exist to provide lookups.

There are perhaps three main types of table that is the target of a
foreign key lookup:

1) tables that map to program language enumerations: typically small
(less than a hundred rows) and changing very infrequently.

2) tables that hold quasi-static reference data where rows are 'never'
deleted (the may be amended, perhaps to identify that they are logically
inactivated, but still needed for reference lookup from existing rows
elsewhere) - typically customer definitions, product definitions, site
definitions and that sort of thing that is often regarded as 'static
data' by a user application session but which may change.

3) master records in master/detail relationships such as order/orderline.

If you can have mechanisms that reflect the likelihood of an update and
optimise accordingly, then hopefully performance in real-world
applications can be improved.

In the case of 1) for example, we might reasonably have a single logical
read/write lock that controls access to ALL such tables in a schema, and
a single 'update generation count'. The lock would effectively provide
repeatable read stability across all of the tables (a multi-table table
lock) while in place, and the generation count (which can be a tran id)
idicates to caching processes when the cache is stale. This means that
unlike normal MVCC the readers will block a writer, but in this case we
expect the write to happen only during application release.

In the case of 2), we can't use the cross-table lock, and the tables may
be large, so the suggested LRU cache per table (with a table-level
read/write lock again) may be most effective, but we may elect to regard
a read lock as allowing any operation that doesn't invalidate the
primary key..

And in the case of 3) we don't do anything special at all.

I certainly think that anything which can materially reduce lookups in
case 1) and hopefully 2) will encourage good database design and
declarative referential integrity - in some clases of high performance
application the cost is too high to be done inline with an update, which
is a shame.

James