Re: Tweaking Foreign Keys for larger tables

On 6 November 2014 20:47, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> Simon Riggs wrote:
...
>> 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.

How about we set lock level on each Foreign Key like this

[USING LOCK [lock level]]

level is one of
KEY - [FOR KEY SHARE] - default
ROW - [FOR SHARE]
TABLE SHARE - [ ]
TABLE EXCLUSIVE - [FOR TABLE EXCLUSIVE]

which introduces these new level descriptions
TABLE SHARE - is default behavior of SELECT
TABLE EXCLUSIVE - we lock the referenced table against all writes -
this allows the table to be fully cached for use in speeding up checks
[FOR TABLE EXCLUSIVE] - uses ShareRowExclusiveLock

The last level is like "Read Only tables" apart from the fact that
they can be written to when needed, but we optimize things on the
assumption that such writes are very rare.

We could also add Read Only tables as well, but I don't see as much
use for them. Sounds like you'd spend a lot of time with ALTER TABLE
as you turn it on and off. I'd like to be able to do that
automatically as needed.

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