Re: Tweaking Foreign Keys for larger tables

Simon Riggs wrote:
> 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.

This seems like a can of worms to me. How about the ability to mark a
table READ ONLY, so that insert/update/delete operations on it raise an
error? For such tables, you can just assume that tuples never go away,
which can help optimize some ri_triggers.c queries by doing plain
SELECT, not SELECT FOR KEY SHARE.

If you later need to add rows to the table, you set it READ WRITE, and
then ri_triggers.c automatically start using FOR KEY SHARE; add/modify
to your liking, then set READ ONLY again. So you incur the cost of
tuple locking only while you have the table open for writes.

This way we don't get into the mess of reasoning about foreign keys that
might be violated some of the time.

There's a side effect of tables being READ ONLY which is that tuple
freezing can be optimized as well. I vaguely recall we have discussed
this. It's something like SET READ ONLY, then freeze it, which sets its
relfrozenxid to 0 or maybe FrozenXid; vacuum knows it can ignore the
table for freezing purposes. When SET READ WRITE, relfrozenxid jumps to
RecentXmin.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services