On 5 February 2013 11:15, Andreas Joseph Krogh <andr...@officenet.no> wrote:
> På tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer < > spam_ea...@gmx.net>: > > Andreas Joseph Krogh, 05.02.2013 10:57: > > The value of having NOT NULL deferrable is, well, to not check for > > NULL until the tx commits. When working with ORMs this often is the > > case, especially with circular FKs. > > With circular FKs it's enough to define the FK constraint as deferred. > > > I meant; circular FKs which are also NOT NULL > If you would use that, every pair of circular inserts would require 2 inserts and an update (=insert & delete in MVCC): 1; insert node 1 with FK null, 2; insert node 2 referencing node1, 3; update node 1 with FK to node 2. OTOH, when you decide the FK from node 1 to node 2 before inserting node 1 and have the FK constraint(s) deferrable, then you only need to insert both records: 1; decide FK key from node 1 to node 2, 2; insert node 1 referencing node 2, 3; insert node 2 referencing node 1 This case typically only occurs when you're using surrogate keys, but even in that case you can select nextval(...). The deferred FK approach has the benefit that you don't create 3 copies of the record for node 1, so table and index bloat will be less. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
