Re: Tweaking Foreign Keys for larger tables

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> 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.

On its face, that sounds more promising to me.

> 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.

It could also allow a (potentially large) optimization to
serializable transactions -- there is no need to take any predicate
locks on a table or its indexes if it is read only. To safely
transition a table from read only to read write you would need at
least two flags (similar in some ways to indisvalid and indisready)
-- one to say whether any of these read only optimizations are
allowed, and another flag that would only be set after all
transactions which might have seen the read only state have
completed which actually allows writes. Or that could be done with
a "char" column with three states. So on transition to read only
you would flag it as non-writable, and after all transactions which
might have seen it in a writable state complete you flag it as
allowing read only optimizations. To transition to read write you
disable the optimizations first and wait before actually flagging
it as read write.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company