Re: DEFERRABLE NOT NULL constraint

On 5 February 2013 11:15, Andreas Joseph Krogh <andreak(at)officenet(dot)no> wrote:

> På tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer <
> spam_eater(at)gmx(dot)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.