Re: Tweaking Foreign Keys for larger tables

On 5 November 2014 21:15, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:

>> ON DELETE IGNORE
>> ON UPDATE IGNORE
>> If we allow this specification then the FK is "one way" - we check the
>> existence of a row in the referenced table, but there is no need for a
>> trigger on the referenced table to enforce an action on delete or
>> update, so no need to lock the referenced table when adding FKs.
>
> Are you worried about locking the table at all, or about having to lock
> many rows?

This is useful for smaller, highly referenced tables that don't change
much, if ever.

In that case the need for correctness thru locking is minimal. If we
do lock it will cause very high multixact traffic, so that is worth
avoiding alone.

The main issue is referencing a table many times. Getting a full table
lock can halt all FK checks, so skipping adding the trigger altogether
avoids freezing up everything just for a trigger that doesn't actually
do much.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services