Re: DEFERRABLE NOT NULL constraint

[Andreas Joseph Krogh <andreak@xxxxxxxxxxxx>]

På tirsdag 05. februar 2013 kl. 11:26:20, skrev Alban Hertroys <haramrae@xxxxxxxxx>:

On 5 February 2013 11:15, Andreas Joseph Krogh <andreak@xxxxxxxxxxxx> wrote:

På tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer <spam_eater@xxxxxxx>:

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.

 

There are lots of things you can do, but when it's the ORM which does it you have limited control, and that's the way it should to be (me as application-developer having to worry less about such details).

 

--
Andreas Joseph Krogh <andreak@xxxxxxxxxxxx>      mob: +47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc