From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
---|---|
To: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Tweaking Foreign Keys for larger tables |
Date: | 2014-11-06 08:49:56 |
Message-ID: | CA+U5nMKKzVOQkeHUL9nr_i5ocnYQF856_ZtM6Z09ukhX6fqFQQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2014-11-06 08:58:37 | Re: Tweaking Foreign Keys for larger tables |
Previous Message | David G Johnston | 2014-11-06 08:11:22 | Re: Tweaking Foreign Keys for larger tables |