On 6 November 2014 20:47, Alvaro Herrera <alvhe...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers