Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-07 Thread Andreas Karlsson
On 11/07/2014 08:15 AM, Simon Riggs wrote: 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] I like the idea and thinks it solves the

Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-06 Thread David G Johnston
Peter Eisentraut-2 wrote On 10/31/14 6:19 AM, Simon Riggs wrote: Various ways of tweaking Foreign Keys are suggested that are helpful for larger databases. *INITIALLY NOT ENFORCED FK created, but is not enforced during DML. Will be/Must be marked NOT VALID when first created. We can

Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-06 Thread Simon Riggs
On 5 November 2014 21:15, Peter Eisentraut pete...@gmx.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

Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-06 Thread Simon Riggs
On 5 November 2014 21:15, Peter Eisentraut pete...@gmx.net wrote: On 10/31/14 6:19 AM, Simon Riggs wrote: Various ways of tweaking Foreign Keys are suggested that are helpful for larger databases. *INITIALLY NOT ENFORCED FK created, but is not enforced during DML. Will be/Must be marked

Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-06 Thread Jim Nasby
On 11/6/14, 2:58 AM, Simon Riggs wrote: On 5 November 2014 21:15, Peter Eisentraut pete...@gmx.net wrote: On 10/31/14 6:19 AM, Simon Riggs wrote: Various ways of tweaking Foreign Keys are suggested that are helpful for larger databases. *INITIALLY NOT ENFORCED FK created, but is not

Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-06 Thread David G Johnston
On Thu, Nov 6, 2014 at 10:29 AM, Jim Nasby-5 [via PostgreSQL] ml-node+s1045698n582596...@n5.nabble.com wrote: On 11/6/14, 2:58 AM, Simon Riggs wrote: On 5 November 2014 21:15, Peter Eisentraut [hidden email] http://user/SendEmail.jtp?type=nodenode=5825967i=0 wrote: On 10/31/14 6:19 AM,

Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-06 Thread Alvaro Herrera
Simon Riggs wrote: On 5 November 2014 21:15, Peter Eisentraut pete...@gmx.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

Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-06 Thread Kevin Grittner
Alvaro Herrera alvhe...@2ndquadrant.com wrote: Simon Riggs wrote: On 5 November 2014 21:15, Peter Eisentraut pete...@gmx.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

Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-06 Thread Jim Nasby
On 11/6/14, 11:49 AM, David G Johnston wrote: Constraint would add a statement-level after trigger for insert, update, delete and trigger, which issues a relcache invalidation if the state was marked valid. Marked as deferrable initially deferred. I don't think you'd need to

Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-06 Thread Simon Riggs
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

Re: [HACKERS] Tweaking Foreign Keys for larger tables

2014-11-05 Thread Peter Eisentraut
On 10/31/14 6:19 AM, Simon Riggs wrote: Various ways of tweaking Foreign Keys are suggested that are helpful for larger databases. *INITIALLY NOT ENFORCED FK created, but is not enforced during DML. Will be/Must be marked NOT VALID when first created. We can run a VALIDATE on the

[HACKERS] Tweaking Foreign Keys for larger tables

2014-10-31 Thread Simon Riggs
Various ways of tweaking Foreign Keys are suggested that are helpful for larger databases. * Deferrable Enforcement Timing Clause * NOT DEFERRABLE - immediate execution * DEFERRABLE *INITIALLY IMMEDIATE - existing *INITIALLY DEFERRED - existing *INITIALLY NOT ENFORCED FK created, but