Thread: DEFERRABLE NOT NULL constraint

It's currently (9.2) not possible to define DEFERRABLE NOT NULL constr=
aints. Meaning the following is not valid:

=C2=A0

CREATE TABLE my_table(

id varchar PRIMARY KEY,

stuff_id BIGINT NOT NULL DEFERRABLE INITIALLY DEFERRED

);

=C2=A0

While it's possible to define a trigger to enforce this, like this:</d=
iv>

=C2=A0


CREATE CONSTRAINT TRIGGER my_table_t AFTER INSERT OR UPDATE ON onp_crm=
_relation DEFERRABLE INITIALLY DEFERRED

FOR EACH ROW EXECUTE PROCEDURE my_table_check_stuff_id_nn_tf();

=C2=A0


And have the my_table_check_stuff_id_nn_tf() raise an exception=
 if "stuff_id" is null.

=C2=A0

Having deferrable constraints on FKs and UKs is really nice and when w=
orking with ORMs it's almost impossible to not use this feature.

=C2=A0

Are there any plans to make NOT NULL constraints deferrable so one can=
 avoid the trigger "boilerplate"?

=C2=A0

--
Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 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=

QW5kcmVhcyBKb3NlcGggS3JvZ2ggd3JvdGU6DQo+IEl0J3MgY3VycmVudGx5ICg5LjIpIG5vdCBw
b3NzaWJsZSB0byBkZWZpbmUgREVGRVJSQUJMRSBOT1QgTlVMTCBjb25zdHJhaW50cy4gTWVhbmlu
ZyB0aGUgZm9sbG93aW5nIGlzDQo+IG5vdCB2YWxpZDoNCj4gDQo+IENSRUFURSBUQUJMRSBteV90
YWJsZSgNCj4gaWQgdmFyY2hhciBQUklNQVJZIEtFWSwNCj4gc3R1ZmZfaWQgQklHSU5UIE5PVCBO
VUxMIERFRkVSUkFCTEUgSU5JVElBTExZIERFRkVSUkVEDQo+ICk7DQo+IA0KPiBXaGlsZSBpdCdz
IHBvc3NpYmxlIHRvIGRlZmluZSBhIHRyaWdnZXIgdG8gZW5mb3JjZSB0aGlzLCBsaWtlIHRoaXM6
DQo+IA0KPiBDUkVBVEUgQ09OU1RSQUlOVCBUUklHR0VSIG15X3RhYmxlX3QgQUZURVIgSU5TRVJU
IE9SIFVQREFURSBPTiBvbnBfY3JtX3JlbGF0aW9uIERFRkVSUkFCTEUgSU5JVElBTExZDQo+IERF
RkVSUkVEDQo+IEZPUiBFQUNIIFJPVyBFWEVDVVRFIFBST0NFRFVSRSBteV90YWJsZV9jaGVja19z
dHVmZl9pZF9ubl90ZigpOw0KPiANCj4gQW5kIGhhdmUgdGhlIG15X3RhYmxlX2NoZWNrX3N0dWZm
X2lkX25uX3RmKCkgcmFpc2UgYW4gZXhjZXB0aW9uIGlmICJzdHVmZl9pZCIgaXMgbnVsbC4NCj4g
DQo+IEhhdmluZyBkZWZlcnJhYmxlIGNvbnN0cmFpbnRzIG9uIEZLcyBhbmQgVUtzIGlzIHJlYWxs
eSBuaWNlIGFuZCB3aGVuIHdvcmtpbmcgd2l0aCBPUk1zIGl0J3MgYWxtb3N0DQo+IGltcG9zc2li
bGUgdG8gbm90IHVzZSB0aGlzIGZlYXR1cmUuDQo+IA0KPiBBcmUgdGhlcmUgYW55IHBsYW5zIHRv
IG1ha2UgTk9UIE5VTEwgY29uc3RyYWludHMgZGVmZXJyYWJsZSBzbyBvbmUgY2FuIGF2b2lkIHRo
ZSB0cmlnZ2VyDQo+ICJib2lsZXJwbGF0ZSI/DQoNCk5vdCB0aGF0IEkga25vdyBvZi4NCg0KVGhl
cmUncyBhbiBlbnRyeSBpbiB0aGUgVE9ETyBsaXN0IHRoYXQgcmVjb2duaXplcyB0aGF0IGl0IHdv
dWxkDQpiZSBkZXNpcmFibGUgdG8gbWFrZSBOT1QgTlVMTCBhIHJlZ3VsYXIgY29uc3RyYWludCAo
eW91IGNhbiBkbw0KdGhhdCB0b2RheSBieSB1c2luZyBDSEVDSyAoY29sIElTIE5PVCBOVUxMKSBp
bnN0ZWFkKS4NCg0KQnV0IENIRUNLIGNvbnN0cmFpbnRzIGFyZSBhbHNvIG5vdCBkZWZlcnJhYmxl
Li4uDQoNCllvdXJzLA0KTGF1cmVueiBBbGJlDQo=

P=C3=A5 tirsdag 05. februar 2013 kl. 09:59:54, skrev Albe Laurenz <=
laurenz.albe@w=
ien.gv.at>:

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">
And=
reas Joseph Krogh wrote:
> It's currently (9.2) not possible to define DEFERRABLE NOT NULL constr=
aints. Meaning the following is
> not valid:
>
> CREATE TABLE my_table(
> id varchar PRIMARY KEY,
> stuff_id BIGINT NOT NULL DEFERRABLE INITIALLY DEFERRED
> );
>
> While it's possible to define a trigger to enforce this, like this:
>
> CREATE CONSTRAINT TRIGGER my_table_t AFTER INSERT OR UPDATE ON onp_crm=
_relation DEFERRABLE INITIALLY
> DEFERRED
> FOR EACH ROW EXECUTE PROCEDURE my_table_check_stuff_id_nn_tf();
>
> And have the my_table_check_stuff_id_nn_tf() raise an exception if &qu=
ot;stuff_id" is null.
>
> Having deferrable constraints on FKs and UKs is really nice and when w=
orking with ORMs it's almost
> impossible to not use this feature.
>
> Are there any plans to make NOT NULL constraints deferrable so one can=
 avoid the trigger
> "boilerplate"?

Not that I know of.

There's an entry in the TODO list that recognizes that it would
be desirable to make NOT NULL a regular constraint (you can do
that today by using CHECK (col IS NOT NULL) instead).

But CHECK constraints are also not deferrable...


=C2=A0

+100 for having NOT NULL and CHECK-constraints deferrable:-)

=C2=A0

Is there any "I want to sponsor development of <feature-X> =
with $xxx" mechanism?

=C2=A0

--
Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 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

=C2=A0=

Deferrable foreign key and unique key constraints I can understand, but ...

On 2013.02.05 1:22 AM, Andreas Joseph Krogh wrote:
> +100 for having NOT NULL and CHECK-constraints deferrable:-)
> Is there any "I want to sponsor development of <feature-X> with $xxx" mechanism?

I'd like to know what value there is in making NOT NULL and CHECK deferrable.

While we're at it, do we want to make the column data type check constraints
deferrable too, so you can initially assign any value at all without regard for
the declared type of the column?  Then we only at constraints-immediate time
say, sorry, you can't put a string in a number column, or, sorry, that number is
too large, or that string is too long, or whatever.

NOT NULL and CHECK constraints are effectively just part of a data type
definition after all.  Postgres' current behavior is fairly consistent; if we
make these deferrable, then why stop there?

-- Darren Duncan

P=C3=A5 tirsdag 05. februar 2013 kl. 10:39:43, skrev Darren Duncan <=
;darren@darren=
duncan.net>:

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">
Def=
errable foreign key and unique key constraints I can understand, but ...

On 2013.02.05 1:22 AM, Andreas Joseph Krogh wrote:
> +100 for having NOT NULL and CHECK-constraints deferrable:-)
> Is there any "I want to sponsor development of <feature-X> =
with $xxx" mechanism?

I'd like to know what value there is in making NOT NULL and CHECK deferrabl=
e.

While we're at it, do we want to make the column data type check constraint=
s
deferrable too, so you can initially assign any value at all without regard=
 for
the declared type of the column?=C2=A0 Then we only at constraints-immediat=
e time
say, sorry, you can't put a string in a number column, or, sorry, that numb=
er is
too large, or that string is too long, or whatever.

NOT NULL and CHECK constraints are effectively just part of a data type
definition after all.=C2=A0 Postgres' current behavior is fairly consistent=
; if we
make these deferrable, then why stop there?


=C2=A0

The value of having NOT NULL deferrable is, well, to not check for NUL=
L until the tx commits. When working with ORMs this often is the case, espe=
cially with circular FKs.

=C2=A0

--
Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 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

=C2=A0=

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.

2013/2/5 Darren Duncan <darren@darrenduncan.net>:
> I'd like to know what value there is in making NOT NULL and CHECK
> deferrable.

Consider such schema sample:
- you have tables “groups” and “group_items”
- each group must have at least one item
- each group must have a “master” item, that is denoted in
groups.master_item_id column
- groups.group_id, groups.master_item_id, group_items.item_id and
group_items.group_id should be NOT NULL
- you use “serial” type for the KEY columns

Now, when you're creating a new group:
- you cannot insert a row into the groups, as master_item_id is not
yet known and NULL is not allowed;
- you cannot insert a row into the group_items, as you need to know
group_id, FK can be deferred, but NULL is not allowed.


All this works pretty good if one don't use “serial” type for the keys and
explicitly calls nextval() on the corresponding sequences first.


--
Victor Y. Yegorov

P=C3=A5 tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer &=
lt;spam_eater@gmx.n=
et>:

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">
And=
reas 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.


=C2=A0

I meant; circular FKs which are also NOT NULL

=C2=A0

--
Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 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

=C2=A0=

On 5 February 2013 11:15, Andreas Joseph Krogh <andreak@officenet.no> wrote=
:

> P=E5 tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer <
> spam_eater@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 (=3Dinsert & 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.
--=20
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Andreas Joseph Krogh, 05.02.2013 11:15:
>     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

A deferrable FK is still enough for that scenario as you can insert FK values that do not yet exist.

See Alban's answer for an example.

P=C3=A5 tirsdag 05. februar 2013 kl. 11:26:20, skrev Alban Hertroys &l=
t;haramrae@gmail.co=
m>:

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">On 5 February 2013 11:15, Andreas Josep=
h Krogh <<a href=3D"mailto:andreak@officenet.no" targe=
t=3D"_blank">andreak@officenet.no> wrote:


<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
P=C3=A5 tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer &=
lt;spam_eater@gmx.n=
et>:


<blockquote style=3D"border-left:1px solid rgb(204,204,204);margin:0pt 0pt =
0pt 0.8ex;padding-left:1ex">
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.


=C2=A0


I meant; circular FKs which are also NOT NULL=C2=A0


=C2=A0

=C2=A0If you would use that, every pair of circular inserts would requ=
ire 2 inserts and an update (=3Dinsert & delete in MVCC):

=C2=A0

1; insert node 1 with FK null,

2; insert node 2 referencing node1,

3; update node 1 with FK to node 2.

=C2=A0

OTOH, when you decide the FK from node 1 to node 2 before inserting no=
de 1 and have the FK constraint(s) deferrable, then you only need to insert=
 both records:

=C2=A0

1; decide FK key from node 1 to node 2,

2; insert node 1 referencing node 2,

3; insert node 2 referencing node 1

=C2=A0

This case typically only occurs when you're using surrogate keys, but =
even in that case you can select nextval(...).

=C2=A0

The deferred FK approach has the benefit that you don't create 3 copie=
s 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.

=C2=A0

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 appl=
ication-developer having to worry less about such details).

=C2=A0

--
Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 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

=C2=A0=

On 5 February 2013 12:41, Andreas Joseph Krogh <andreak@officenet.no> wrote:

> 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).
>

In that case it's your ORM that needs fixing, not the database.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

P=C3=A5 tirsdag 05. februar 2013 kl. 13:32:15, skrev Alban Hertroys &l=
t;haramrae@gmail.co=
m>:

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">On 5 February 2013 12:41, Andreas Josep=
h Krogh <<a href=3D"mailto:andreak@officenet.no" targe=
t=3D"_blank">andreak@officenet.no> wrote:


<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">


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 appl=
ication-developer having to worry less about such details).




=C2=A0

In that case it's your ORM that needs fixing, not the database.



=C2=A0

"Fix your tool" is not helping here... Having deferrable NOT=
 NULLs in the RDBMS will help making peoples lives easier and (some) other =
RDBMS have this.

=C2=A0

My question was if having deferrable NOT NULLs was on PGs road-map, no=
t whether or not someone finds it usefull or is able to work around it.</di=
v>

=C2=A0

--
Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 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

=C2=A0=

Hi,

> 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.

+1000 here.

Cheers
Bèrto

On Tue, Feb 5, 2013 at 11:32 PM, Alban Hertroys <haramrae@gmail.com> wrote:
> On 5 February 2013 12:41, Andreas Joseph Krogh <andreak@officenet.no> wrote:
>>
>> 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).
>
> In that case it's your ORM that needs fixing, not the database.

Agreed. One of the differences between MySQL and PostgreSQL is that
the latter gives you a database with rules, while the former is a
place for an application to store data. This last couple of weeks I've
been working with a really sloppily-built application (and a very
popular one too, so I won't name names), and it fits MySQL
perfectly... What I'd much rather do is build real rules that may not
EVER be violated. While I can see the value in deferring foreign key
constraints (circular references - never used 'em though), I don't see
any reason to create a record with a NULL and then replace that NULL
before committing. Sort out program logic first; then look to the
database.

Making people's lives easier in the short term is NOT the greatest
goal of a database. Consistent data will make the admins' lives far
easier in the long term. I do not ever want to have to deal with
BTrieve file corruption in my life.

ChrisA

Hi Chris,

> I don't see
> any reason to create a record with a NULL and then replace that NULL
> before committing. Sort out program logic first; then look to the
> database.

I beg to differ here. Say you have a set of business rules that
rigidly defines how that field must be made AND the data on which it
is based is not visible to the user who does the insert. At this point
you need "something" to generate that value on the fly for the user
(calling a procedure from a before insert trigger). You still need
your field to be NOT NULL, though. Because it happens to be... the PK
:)

Cheers
Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

On Wed, Feb 6, 2013 at 12:20 AM, B=E8rto =EBd S=E8ra <berto.d.sera@gmail.co=
m> wrote:
> Hi Chris,
>
>> I don't see
>> any reason to create a record with a NULL and then replace that NULL
>> before committing. Sort out program logic first; then look to the
>> database.
>
> I beg to differ here. Say you have a set of business rules that
> rigidly defines how that field must be made AND the data on which it
> is based is not visible to the user who does the insert. At this point
> you need "something" to generate that value on the fly for the user
> (calling a procedure from a before insert trigger). You still need
> your field to be NOT NULL, though. Because it happens to be... the PK
> :)

Why do that as a trigger, then? Why not simply call a procedure that
generates the value and inserts it?

ChrisA

Hi Chris,

> Why do that as a trigger, then? Why not simply call a procedure that
> generates the value and inserts it?

Because this must be iso-8859-1 to whoever makes the call and I'm not
supposed to expose any detail of what's going on behind the scenes.
Outsourcing part of sensitive apps also means that you do not want all
of the outside devs to know all that company X is doing, and how it is
doing it, sometimes.

Cheers
Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

QsOocnRvIMOrZCBTw6hyYSB3cm90ZToNCj4gPiBXaHkgZG8gdGhhdCBhcyBhIHRyaWdnZXIsIHRo
ZW4/IFdoeSBub3Qgc2ltcGx5IGNhbGwgYSBwcm9jZWR1cmUgdGhhdA0KPiA+IGdlbmVyYXRlcyB0
aGUgdmFsdWUgYW5kIGluc2VydHMgaXQ/DQo+IA0KPiBCZWNhdXNlIHRoaXMgbXVzdCBiZSB1bmtu
b3duIHRvIHdob2V2ZXIgbWFrZXMgdGhlIGNhbGwgYW5kIEknbSBub3QNCj4gc3VwcG9zZWQgdG8g
ZXhwb3NlIGFueSBkZXRhaWwgb2Ygd2hhdCdzIGdvaW5nIG9uIGJlaGluZCB0aGUgc2NlbmVzLg0K
PiBPdXRzb3VyY2luZyBwYXJ0IG9mIHNlbnNpdGl2ZSBhcHBzIGFsc28gbWVhbnMgdGhhdCB5b3Ug
ZG8gbm90IHdhbnQgYWxsDQo+IG9mIHRoZSBvdXRzaWRlIGRldnMgdG8ga25vdyBhbGwgdGhhdCBj
b21wYW55IFggaXMgZG9pbmcsIGFuZCBob3cgaXQgaXMNCj4gZG9pbmcgaXQsIHNvbWV0aW1lcy4N
Cg0KVGhhdCBzb3VuZHMgYSBiaXQgY29udHJpdmVkLCBidXQgeW91IGNvdWxkIGNyZWF0ZSBhIHZp
ZXcNCmFuZCBoaWRlIHRoZSBwcm9jZXNzaW5nIGluIGFuIElOU1RFQUQgT0YgSU5TRVJUIHRyaWdn
ZXIuDQoNCllvdXJzLA0KTGF1cmVueiBBbGJlDQo=

Hi,

> That sounds a bit contrived, but you could create a view
> and hide the processing in an INSTEAD OF INSERT trigger.

Yes, there are ways to hack it anyway. The thing is about keeping it
simple and having it come out clear of a \d, when you ask info about
the table from within psql. It is definitely possible "as is", in a
number of ways.

Cheers
Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

P=C3=A5 tirsdag 05. februar 2013 kl. 14:13:20, skrev Chris Angelico &l=
t;rosuav@gmail.com</a=
>>:

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">
On =
Tue, Feb 5, 2013 at 11:32 PM, Alban Hertroys <haramrae@gmail.com> wro=
te:
> On 5 February 2013 12:41, Andreas Joseph Krogh <andreak@officenet.n=
o> wrote:
>>
>> There are lots of things you can do, but when it's the ORM which d=
oes it
>> you have limited control, and that's the way it should to be (me a=
s
>> application-developer having to worry less about such details).
>
> In that case it's your ORM that needs fixing, not the database.

Agreed. One of the differences between MySQL and PostgreSQL is that
the latter gives you a database with rules, while the former is a
place for an application to store data. This last couple of weeks I've
been working with a really sloppily-built application (and a very
popular one too, so I won't name names), and it fits MySQL
perfectly... What I'd much rather do is build real rules that may not
EVER be violated. While I can see the value in deferring foreign key
constraints (circular references - never used 'em though), I don't see
any reason to create a record with a NULL and then replace that NULL
before committing. Sort out program logic first; then look to the
database.

Making people's lives easier in the short term is NOT the greatest
goal of a database. Consistent data will make the admins' lives far
easier in the long term. I do not ever want to have to deal with
BTrieve file corruption in my life.


=C2=A0

I've been using PG since v-6.5 and I'm very aware of its strengths, an=
d also its weaknesses. There really isn't an argument for not having NOT NU=
LL deferrable constraints, other than -hackers not prioritizing it, which I=
 understand perfectly well as they have lots of other interesting stuff on =
their plate.

=C2=A0
--
Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 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

=C2=A0
=

On 5 February 2013 14:20, B=E8rto =EBd S=E8ra <berto.d.sera@gmail.com> wrot=
e:

> Hi Chris,
>
> > I don't see
> > any reason to create a record with a NULL and then replace that NULL
> > before committing. Sort out program logic first; then look to the
> > database.
>
> I beg to differ here. Say you have a set of business rules that
> rigidly defines how that field must be made AND the data on which it
> is based is not visible to the user who does the insert. At this point
> you need "something" to generate that value on the fly for the user
> (calling a procedure from a before insert trigger).


You need that "something" either way, whether you supply it before the
record is inserted or after. Stuff like that is best done as database
logic, by means of a trigger or rule.

I have to admit that I don't know off the top of my head whether a NOT NULL
constraint fires before ON INSERT triggers or after and I don't have access
to PG from here to check that.
If that's the problem, then you might need to put a RULE in place instead
of an ON BEFORE INSERT trigger. Is that what your complaint boils down to?

--=20
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On 2013-02-05, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:
> Hi Chris,
>
>> Why do that as a trigger, then? Why not simply call a procedure that
>> generates the value and inserts it?
>
> Because this must be unknown to whoever makes the call and I'm not
> supposed to expose any detail of what's going on behind the scenes.
> Outsourcing part of sensitive apps also means that you do not want all
> of the outside devs to know all that company X is doing, and how it is
> doing it, sometimes.
>
> Cheers
> Bèrto

You've hidden nothing from INSERT-RETURNING.



--
⚂⚃ 100% natural

Hi

> You've hidden nothing from INSERT-RETURNING.

?? Or from a select, if the final value is what you mean. What we hide
is the way values are made, clearly not the final value. That bit is
accessible to anyone who can select the table, obviously.

Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

On 2013-02-06, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:
> Hi
>
>> You've hidden nothing from INSERT-RETURNING.
>
> ?? Or from a select, if the final value is what you mean. What we hide
> is the way values are made, clearly not the final value. That bit is
> accessible to anyone who can select the table, obviously.
>

so the trigger function is opaque, written in C or some other language
where they can't access the source easily?




--
⚂⚃ 100% natural

On Wed, Feb 6, 2013 at 8:01 PM, Jasen Betts <jasen@xnet.co.nz> wrote:
> On 2013-02-06, B=E8rto =EBd S=E8ra <berto.d.sera@gmail.com> wrote:
>> Hi
>>
>>> You've hidden nothing from INSERT-RETURNING.
>>
>> ?? Or from a select, if the final value is what you mean. What we hide
>> is the way values are made, clearly not the final value. That bit is
>> accessible to anyone who can select the table, obviously.
>>
>
> so the trigger function is opaque, written in C or some other language
> where they can't access the source easily?

I still don't see how that's any better than a stored procedure that
directly does the INSERT. You can conceal the code every bit as
easily.

ChrisA

Hi

> I still don't see how that's any better than a stored procedure that
> directly does the INSERT. You can conceal the code every bit as
> easily.

Guys I DO NOT write the customers' security guidelines. I get asked to
produce a design in which "party X will make plain INSERTs and ignore
the very existence of business rules". Can I do it in PG, No. Can I
rewrite the guidelines? No. Hence, PG is not used. Full stop.

Whether these customers are clever or stupid is not an issue. They are
paying customers, so they are right by design. And yes, sometimes I
manage to sell them something else, as I said earlier. Some other
times I end up having to use a db that is not PG. Easy as that.

Cheers
Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

On Wed, Feb 6, 2013 at 10:36 PM, B=E8rto =EBd S=E8ra <berto.d.sera@gmail.co=
m> wrote:
> Hi
>
>> I still don't see how that's any better than a stored procedure that
>> directly does the INSERT. You can conceal the code every bit as
>> easily.
>
> Guys I DO NOT write the customers' security guidelines. I get asked to
> produce a design in which "party X will make plain INSERTs and ignore
> the very existence of business rules". Can I do it in PG, No. Can I
> rewrite the guidelines? No. Hence, PG is not used. Full stop.

Sometimes you just have to tell the customer that his/her requirements
are impossible to plausibly implement. If you get into a taxi and ask
to be driven to New Zealand within the hour, no amount of begging will
get you what you want.

ChrisA

On 6 February 2013 12:56, Chris Angelico <rosuav@gmail.com> wrote:

> If you get into a taxi and ask
> to be driven to New Zealand within the hour, no amount of begging will
> get you what you want.
>

...Unless you get into a taxi in New Zealand.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On Wednesday, February 6, 2013 at 23:31,=20
00jkxma2vt@sneakemail.com (Alban Hertroys haramrae-at-gmail.com=20
|pg-gts/Basic|) wrote:

>On 6 February 2013 12:56, Chris Angelico <rosuav@gmail.com> wrote:
>
>>If you get into a taxi and ask
>>to be driven to New Zealand within the hour, no amount of begging will
>>get you what you want.
>>
>
>....Unless you get into a taxi in New Zealand.
>

   ....Which makes the request effectively NULL, planning to do=20
this makes it DEFFERABLE.


Taking a different tangent ...

Is there anything in the SQL standards about NOT NULL=20
constraints being deferrable?

To my mind we should not consider implementing non-standard=20
behaviour, but if something is in the standard I can't see why=20
it shouldn't be implemented, esp. when there is no compulsion=20
for it to be used.

Regards
Gavan Schneider

R2F2YW4gU2NobmVpZGVyIHdyb3RlOg0KPiBUYWtpbmcgYSBkaWZmZXJlbnQgdGFuZ2VudCAuLi4N
Cg0KR29vZCBpZGVhLg0KDQo+IElzIHRoZXJlIGFueXRoaW5nIGluIHRoZSBTUUwgc3RhbmRhcmRz
IGFib3V0IE5PVCBOVUxMDQo+IGNvbnN0cmFpbnRzIGJlaW5nIGRlZmVycmFibGU/DQo+IA0KPiBU
byBteSBtaW5kIHdlIHNob3VsZCBub3QgY29uc2lkZXIgaW1wbGVtZW50aW5nIG5vbi1zdGFuZGFy
ZA0KPiBiZWhhdmlvdXIsIGJ1dCBpZiBzb21ldGhpbmcgaXMgaW4gdGhlIHN0YW5kYXJkIEkgY2Fu
J3Qgc2VlIHdoeQ0KPiBpdCBzaG91bGRuJ3QgYmUgaW1wbGVtZW50ZWQsIGVzcC4gd2hlbiB0aGVy
ZSBpcyBubyBjb21wdWxzaW9uDQo+IGZvciBpdCB0byBiZSB1c2VkLg0KDQpJU08vSUVDIDkwNzUt
MjoyMDAzIHNheXM6DQoNCkNoYXB0ZXIgMTEuNCAoPGNvbHVtbiBkZWZpbml0aW9uPik6DQoNCjxj
b2x1bW4gY29uc3RyYWludCBkZWZpbml0aW9uPiA6Oj0NCiAgWyA8Y29uc3RyYWludCBuYW1lIGRl
ZmluaXRpb24+IF0gPGNvbHVtbiBjb25zdHJhaW50PiBbIDxjb25zdHJhaW50IGNoYXJhY3Rlcmlz
dGljcz4gXQ0KDQo8Y29sdW1uIGNvbnN0cmFpbnQ+IDo6PQ0KICAgIE5PVCBOVUxMDQogIHwgPHVu
aXF1ZSBzcGVjaWZpY2F0aW9uPg0KICB8IDxyZWZlcmVuY2VzIHNwZWNpZmljYXRpb24+DQogIHwg
PGNoZWNrIGNvbnN0cmFpbnQgZGVmaW5pdGlvbj4NCg0KQ2hhcHRlciAxMC44ICg8Y29uc3RyYWlu
dCBuYW1lIGRlZmluaXRpb24+IGFuZCA8Y29uc3RyYWludCBjaGFyYWN0ZXJpc3RpY3M+KToNCg0K
PGNvbnN0cmFpbnQgY2hhcmFjdGVyaXN0aWNzPiA6Oj0NCiAgICA8Y29uc3RyYWludCBjaGVjayB0
aW1lPiBbIFsgTk9UIF0gREVGRVJSQUJMRSBdDQogIHwgWyBOT1QgXSBERUZFUlJBQkxFIFsgPGNv
bnN0cmFpbnQgY2hlY2sgdGltZT4gXQ0KDQo8Y29uc3RyYWludCBjaGVjayB0aW1lPiA6Oj0NCiAg
ICBJTklUSUFMTFkgREVGRVJSRUQNCiAgfCBJTklUSUFMTFkgSU1NRURJQVRFDQoNCg0KU28geWVz
LCB0aGUgc3RhbmRhcmQgY2F0ZXJzIGZvciBkZWZlcnJhYmxlIE5PVCBOVUxMIGNvbnN0cmFpbnRz
Lg0KDQpNb3Jlb3ZlcjoNCg0KQ2hhcHRlciAxMC44LCBHZW5lcmFsIFJ1bGVzDQoxKSBBIDxjb25z
dHJhaW50IG5hbWU+IGlkZW50aWZpZXMgYSBjb25zdHJhaW50LiBMZXQgdGhlIGlkZW50aWZpZWQg
Y29uc3RyYWludCBiZSBDLg0KMikgSWYgTk9UIERFRkVSUkFCTEUgaXMgc3BlY2lmaWVkLCB0aGVu
IEMgaXMgbm90IGRlZmVycmFibGU7IG90aGVyd2lzZSBpdCBpcyBkZWZlcnJhYmxlLg0KDQpTbyBk
ZWZlcnJhYmxlIHNob3VsZCBiZSB0aGUgZGVmYXVsdC4NCg0KWW91cnMsDQpMYXVyZW56IEFsYmUN
Cg==

Forgot to cc general



On Tue, Feb 5, 2013 at 1:39 AM, Darren Duncan <darren@darrenduncan.net>wrote:

> Deferrable foreign key and unique key constraints I can understand, but ...
>
>
> On 2013.02.05 1:22 AM, Andreas Joseph Krogh wrote:
>
>> +100 for having NOT NULL and CHECK-constraints deferrable:-)
>> Is there any "I want to sponsor development of <feature-X> with $xxx"
>> mechanism?
>>
>
> I'd like to know what value there is in making NOT NULL and CHECK
> deferrable.
>

I think this is likely to come up when an incomplete record is stored and
then expected to be later updated before commit time.  There are a number
of reasons why this is a bad idea as a matter of general practice (extra
dead tuples, etc), but I could imagine cases in thick clients where such
behavior might be desirable and where transactions might be kept open for a
little bit.  They do seem few and far between.


>
> While we're at it, do we want to make the column data type check
> constraints deferrable too, so you can initially assign any value at all
> without regard for the declared type of the column?  Then we only at
> constraints-immediate time say, sorry, you can't put a string in a number
> column, or, sorry, that number is too large, or that string is too long, or
> whatever.
>

If you had deferrable check constraints you could do that just storing
whatever type cast to text anyway....


>
> NOT NULL and CHECK constraints are effectively just part of a data type
> definition after all.  Postgres' current behavior is fairly consistent; if
> we make these deferrable, then why stop there?


However NOT NULL and CHECK constraints operate very differently on
attributes than they do on domains in at least some cases.   If you use NOT
NULL domains in a complex type, those constraints will be honored when you
use the complex type as a column, but they will not be if you try to do the
same by using a table definition as a complex type with not nulls attached
there.

Best Wishes,
Chris Travers

>
>
> -- Darren Duncan
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>

Hi all;

I have some thoughts on this and I think deferrable not null constraints
make some sense (and I think once one gets there deferrable check
constraints make some sense too).  My view of the use cases though are a
bit different and assume thick clients where some data may be looked up and
we may want to insert partial information in real time, requiring that the
information is complete before the data transaction completes.   Unlike
with a middleware layer or a web client, we might not want to assume that
transactions are short-lived, or they might have to do with short-lived
transactions and order of data coming in but we may not know the value yet
and may require an insert/update routine in the acquisition of the data.
 These might not be cases where we are expecting to insert a new row.  They
might be cases where we might expect to reference an existing row.

Now, we could put in bogus data into the fkey fields, or use magic numbers
like 0 to mean unassigned.  But this gets into what I see as relative
anti-patterns, namely using magic values when existing value of null would
be semanticaly clearer.

The other option of course is to say "don't put it into the db until all
variables are known!" but then I think that goes against PostgreSQL's great
strength which is the programmability and the ability to take on certain
middleware roles.

Best Wishes,
Chris Travers

On 7 February 2013 07:45, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Gavan Schneider wrote:
>> Taking a different tangent ...
>
> Good idea.
>
>> Is there anything in the SQL standards about NOT NULL
>> constraints being deferrable?
>>
>> To my mind we should not consider implementing non-standard
>> behaviour, but if something is in the standard I can't see why
>> it shouldn't be implemented, esp. when there is no compulsion
>> for it to be used.
>
> ISO/IEC 9075-2:2003 says:
>
> Chapter 11.4 (<column definition>):
>
> <column constraint definition> ::=
>   [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]
>
> <column constraint> ::=
>     NOT NULL
>   | <unique specification>
>   | <references specification>
>   | <check constraint definition>
>
> Chapter 10.8 (<constraint name definition> and <constraint characteristics>):
>
> <constraint characteristics> ::=
>     <constraint check time> [ [ NOT ] DEFERRABLE ]
>   | [ NOT ] DEFERRABLE [ <constraint check time> ]
>
> <constraint check time> ::=
>     INITIALLY DEFERRED
>   | INITIALLY IMMEDIATE
>
>
> So yes, the standard caters for deferrable NOT NULL constraints.
>
> Moreover:
>
> Chapter 10.8, General Rules
> 1) A <constraint name> identifies a constraint. Let the identified constraint be C.
> 2) If NOT DEFERRABLE is specified, then C is not deferrable; otherwise it is deferrable.
>
> So deferrable should be the default.
>

No. If you look at the Syntax Rules section just above that, it says:

1) If <constraint check time> is not specified, then INITIALLY
IMMEDIATE is implicit.
2) Case:
    a) If INITIALLY DEFERRED is specified, then:
        i) NOT DEFERRABLE shall not be specified.
        ii) If DEFERRABLE is not specified, then DEFERRABLE is implicit.
    b) If INITIALLY IMMEDIATE is specified or implicit and neither
DEFERRABLE nor NOT
DEFERRABLE is specified, then NOT DEFERRABLE is implicit.

So NOT DEFERRABLE is the default, if nothing else is specified.

That's actually a sensible default, because there are consequences to
making a constraint deferrable --- it can hurt performance if a large
number of rows need to be queued up for later checking, and also a
deferrable primary key/unique constraint can't be used as the target
for a foreign key.

Regards,
Dean

Hi

> also a
> deferrable primary key/unique constraint can't be used as the target
> for a foreign key.

ehr, why? I mean, I'm positive it cannot be used before an actual
value is in the record, but what would be the problem, apart from
that?

Cheers
Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

On 7 February 2013 09:02, B=E8rto =EBd S=E8ra <berto.d.sera@gmail.com> wrot=
e:
> Hi
>
>> also a
>> deferrable primary key/unique constraint can't be used as the target
>> for a foreign key.
>
> ehr, why? I mean, I'm positive it cannot be used before an actual
> value is in the record, but what would be the problem, apart from
> that?
>

This restriction is specified in the SQL standard.

I think most of the problems occur with CASCADE actions. E.g., if the
row you refer to isn't currently unique, and then it is updated, how
should those updates cascade to the referencing rows?

There might be something that could be done (perhaps if only RESTRICT
or NO ACTION is specified), but at the moment PostgreSQL doesn't
support it.

Regards,
Dean

Hi

> This restriction is specified in the SQL standard.

Thanks! This is the kind of thing one CAN sell to customers :) "Your
thing is out of standards, Sir" sounds much better than "But I really
hate that, Sir".

Which has, however, a terrible impact on the ORM that use circular
FKs. Will have to think this over very well.

Cheers
Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

On 7 February 2013 08:50, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> That's actually a sensible default, because there are consequences to
> making a constraint deferrable --- it can hurt performance if a large
> number of rows need to be queued up for later checking...

Just to clarify --- PostgreSQL goes to some effort to avoid queuing up
re-checks of deferred constraints if they are unnecessary. So, for
example, in the case of primary key/unique constraints, the
performance in the deferrable and non-deferrable cases are about the
same provided that none of the inserted/updated rows violate the
uniqueness check at insert/update time. The real performance hit comes
in if the constraint is deferrable, and a large number of new rows
violate the constraint temporarily, and so need to be re-checked
later.

Regards,
Dean

Dean Rasheed wrote:
>> ISO/IEC 9075-2:2003 says:
>>
>> Chapter 10.8 (<constraint name definition> and <constraint characteristi=
cs>):
>>
>> <constraint characteristics> ::=3D
>>     <constraint check time> [ [ NOT ] DEFERRABLE ]
>>   | [ NOT ] DEFERRABLE [ <constraint check time> ]
>>
>> <constraint check time> ::=3D
>>     INITIALLY DEFERRED
>>   | INITIALLY IMMEDIATE
>>
>>
>> So yes, the standard caters for deferrable NOT NULL constraints.
>>
>> Moreover:
>>
>> Chapter 10.8, General Rules
>> 1) A <constraint name> identifies a constraint. Let the identified const=
raint be C.
>> 2) If NOT DEFERRABLE is specified, then C is not deferrable; otherwise i=
t is deferrable.
>>
>> So deferrable should be the default.

> No. If you look at the Syntax Rules section just above that, it says:
>=20
> 1) If <constraint check time> is not specified, then INITIALLY
> IMMEDIATE is implicit.
> 2) Case:
>     a) If INITIALLY DEFERRED is specified, then:
>         i) NOT DEFERRABLE shall not be specified.
>         ii) If DEFERRABLE is not specified, then DEFERRABLE is implicit.
>     b) If INITIALLY IMMEDIATE is specified or implicit and neither
> DEFERRABLE nor NOT
> DEFERRABLE is specified, then NOT DEFERRABLE is implicit.
>=20
> So NOT DEFERRABLE is the default, if nothing else is specified.

The SQL standard is usually as confusing as is still
compatible with correctness, but after rereading the whole chapter
I think that here it is self-contradictory.

The syntax rules support what you say:
- If I specify nothing at all, INITIALLY IMMEDIATE is implicit.
- Since INITIALLY IMMEDIATE is implicit and neither DEFERRABLE
  nor NOT DEFERRABLE are specified, NOT DEFERRABLE is implicit.

But how does that go together with General Rule 2?
It does not say "if NOT DEFERRABLE is specified or implicit",
it says "if NOT DEFERRABLE is specified".

Anyway, that's a sideline; at any rate the standard requires
deferrable NOT NULL constraints.

Yours,
Laurenz Albe

here's a relatively clean way to do circular references:

 given the circular reference:

  table a (
    i serial primary key ,
    j integer references b(j) deferrable initially deferred
          );

  table b (
    j serial primary key ,
    i integer references a(i)
           );

to make inserts easier put the default value of the column  b.i
onto column a.j also (so both columns have the same sequence as their
default value)

then you ans do an INSERT INTO a [...] RETURNING i,j and have the
primary and foreign keys values needed for the new b row, without
needing to explictly reference the sequence in the query or
beforehand.

getting an ORM to follow that process may not be so easy, but is
probably the right thing to do.




--
⚂⚃ 100% natural

On 2013-02-07, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

> Anyway, that's a sideline; at any rate the standard requires
> deferrable NOT NULL constraints.

Well, the standard syntax allows them to be requested, check constraints too.

what does the standard say about it behaviourally?

what do other major SQL databases do?


--
⚂⚃ 100% natural

SmFzZW4gQmV0dHMgd3JvdGU6DQo+IFdlbGwsIHRoZSBzdGFuZGFyZCBzeW50YXggYWxsb3dzIHRo
ZW0gdG8gYmUgcmVxdWVzdGVkLCBjaGVjayBjb25zdHJhaW50cyB0b28uDQo+IA0KPiB3aGF0IGRv
ZXMgdGhlIHN0YW5kYXJkIHNheSBhYm91dCBpdCBiZWhhdmlvdXJhbGx5Pw0KDQpXaGF0IHlvdSdk
IGV4cGVjdDoNCg0KVGhlIGNoZWNraW5nIG9mIGEgY29uc3RyYWludCBkZXBlbmRzIG9uIGl0cyBj
b25zdHJhaW50IG1vZGUNCndpdGhpbiB0aGUgY3VycmVudCBTUUwtdHJhbnNhY3Rpb24uIElmIHRo
ZSBjb25zdHJhaW50DQptb2RlIGlzIGltbWVkaWF0ZSwgdGhlbiB0aGUgY29uc3RyYWludCBpcyBl
ZmZlY3RpdmVseQ0KY2hlY2tlZCBhdCB0aGUgZW5kIG9mIGVhY2ggU1FMLXN0YXRlbWVudC4NCg0K
SWYgdGhlIGNvbnN0cmFpbnQgbW9kZSBpcyBkZWZlcnJlZCwgdGhlbiB0aGUgY29uc3RyYWludA0K
aXMgZWZmZWN0aXZlbHkgY2hlY2tlZCB3aGVuIHRoZSBjb25zdHJhaW50IG1vZGUgaXMgY2hhbmdl
ZA0KdG8gaW1tZWRpYXRlIGVpdGhlciBleHBsaWNpdGx5IGJ5IGV4ZWN1dGlvbiBvZiBhDQo8c2V0
IGNvbnN0cmFpbnRzIG1vZGUgc3RhdGVtZW50Piwgb3IgaW1wbGljaXRseSBhdCB0aGUgZW5kIG9m
DQp0aGUgY3VycmVudCBTUUwtdHJhbnNhY3Rpb24uDQoNCldoZW4gYSBjb25zdHJhaW50IGlzIGNo
ZWNrZWQgb3RoZXIgdGhhbiBhdCB0aGUgZW5kIG9mIGFuDQpTUUwtdHJhbnNhY3Rpb24sIGlmIGl0
IGlzIG5vdCBzYXRpc2ZpZWQsIHRoZW4gYW4gZXhjZXB0aW9uDQpjb25kaXRpb24gaXMgcmFpc2Vk
IGFuZCB0aGUgU1FMLXN0YXRlbWVudCB0aGF0IGNhdXNlZCB0aGUNCmNvbnN0cmFpbnQgdG8gYmUg
Y2hlY2tlZCBoYXMgbm8gZWZmZWN0IG90aGVyIHRoYW4NCmVudGVyaW5nIHRoZSBleGNlcHRpb24g
aW5mb3JtYXRpb24gaW50byB0aGUgZmlyc3QgZGlhZ25vc3RpY3MNCmFyZWEuIFdoZW4gYSA8Y29t
bWl0IHN0YXRlbWVudD4gaXMgZXhlY3V0ZWQsIGFsbA0KY29uc3RyYWludHMgYXJlIGVmZmVjdGl2
ZWx5IGNoZWNrZWQgYW5kLCBpZiBhbnkgY29uc3RyYWludA0KaXMgbm90IHNhdGlzZmllZCwgdGhl
biBhbiBleGNlcHRpb24gY29uZGl0aW9uIGlzIHJhaXNlZA0KYW5kIHRoZSBTUUwtdHJhbnNhY3Rp
b24gaXMgdGVybWluYXRlZCBieSBhbiBpbXBsaWNpdA0KPHJvbGxiYWNrIHN0YXRlbWVudD4uDQoN
Cj4gd2hhdCBkbyBvdGhlciBtYWpvciBTUUwgZGF0YWJhc2VzIGRvPw0KDQpTZWVtcyB0byB3b3Jr
IGluIE9yYWNsZToNCg0KQ1JFQVRFIFRBQkxFIGNvbl90ZXN0KA0KICAgSUQgTlVNQkVSKDUpDQog
ICAgICBDT05TVFJBSU5UIGNvbl90ZXN0X3BrIFBSSU1BUlkgS0VZIERFRkVSUkFCTEUgSU5JVElB
TExZIERFRkVSUkVELA0KICAgdmFsIFZBUkNIQVIyKDIwIENIQVIpDQogICAgICBDT05TVFJBSU5U
IGNvbl90ZXN0X3ZhbF9udWxsIE5PVCBOVUxMIERFRkVSUkFCTEUgSU5JVElBTExZIERFRkVSUkVE
DQopOw0KVGFibGUgY3JlYXRlZC4NCg0KSU5TRVJUIElOVE8gY29uX3Rlc3QgVkFMVUVTICgxLCBO
VUxMKTsNCjEgcm93IGNyZWF0ZWQuDQoNClVQREFURSBjb25fdGVzdCBTRVQgdmFsID0gJ29uZScg
V0hFUkUgaWQgPSAxOw0KMSByb3cgdXBkYXRlZC4NCg0KQ09NTUlUOw0KQ29tbWl0IGNvbXBsZXRl
Lg0KDQpJTlNFUlQgSU5UTyBjb25fdGVzdCBWQUxVRVMgKDEsICd0d28nKTsNCjEgcm93IGNyZWF0
ZWQuDQoNClVQREFURSBjb25fdGVzdCBTRVQgaWQgPSAyIFdIRVJFIHZhbCA9ICd0d28nOw0KMSBy
b3cgdXBkYXRlZC4NCg0KQ09NTUlUOw0KQ29tbWl0IGNvbXBsZXRlLg0KDQpJTlNFUlQgSU5UTyBj
b25fdGVzdCBWQUxVRVMgKDEsICd0aHJlZScpOw0KMSByb3cgY3JlYXRlZC4NCg0KQ09NTUlUOw0K
Kg0KRVJST1IgYXQgbGluZSAxOg0KT1JBLTAyMDkxOiB0cmFuc2FjdGlvbiByb2xsZWQgYmFjaw0K
T1JBLTAwMDAxOiB1bmlxdWUgY29uc3RyYWludCAoTEFVUkVOWi5DT05fVEVTVF9QSykgdmlvbGF0
ZWQNCg==

Getting back to the OP (Andreas):

On Tuesday, February 5, 2013 at 20:22, Andreas Joseph Krogh wrote:

>P=C3=A5 tirsdag 05. februar 2013 kl. 09:59:54, skrev Albe Laurenz:
>Andreas Joseph Krogh wrote: ...
>>Are there any plans to make NOT NULL constraints deferrable so
>>one can avoid the trigger "boilerplate"?
>
>Not that I know of.
>
>There's an entry in the TODO list that recognizes that it would
>be desirable to make NOT NULL a regular constraint (you can do
>that today by using CHECK (col IS NOT NULL) instead).
>
>But CHECK constraints are also not deferrable...
>
....
>Is there any "I want to sponsor development of <feature-X> with=20
>$xxx" mechanism? =C2=A0

On Thursday, February 7, 2013 at 18:45, Albe Laurenz wrote:

>... the standard caters for deferrable NOT NULL constraints.
>
So, notwithstanding the many expressions of personal preference=20
and several suggested 'work arounds' needed to compensate for=20
this implied SQL compliance failure, there seems to be no good=20
reason why this 'entry in the TODO list' couldn't be sponsored=20
for development.

But I feel I have missed something here.

Referring to:
<http://www.postgresql.org/docs/current/static/sql-createtable.html>

     where column_constraint is:

     [ CONSTRAINT constraint_name ]
     { NOT NULL |
       NULL |
       CHECK ( expression ) [ NO INHERIT ] |
       DEFAULT default_expr |
       UNIQUE index_parameters |
       PRIMARY KEY index_parameters |
       REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL |=20
MATCH PARTIAL | MATCH SIMPLE ]
         [ ON DELETE action ] [ ON UPDATE action ] }
     [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED |=20
INITIALLY IMMEDIATE ]

     and table_constraint is:

     [ CONSTRAINT constraint_name ]
     { CHECK ( expression ) [ NO INHERIT ] |
       UNIQUE ( column_name [, ... ] ) index_parameters |
       PRIMARY KEY ( column_name [, ... ] ) index_parameters |
       EXCLUDE [ USING index_method ] ( exclude_element WITH=20
operator [, ... ] )
       index_parameters [ WHERE ( predicate ) ] |
       FOREIGN KEY ( column_name [, ... ] )
         REFERENCES reftable [ ( refcolumn [, ... ] ) ]
         [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON=20
DELETE action ]
         [ ON UPDATE action ] }
     [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED |=20
INITIALLY IMMEDIATE ]

CHECK constraints, NOT NULL constraints and FOREIGN KEY=20
constraints all look very deferrable in this definition. If=20
that's the case, why are we having this discussion if the=20
requested functionality/compliance is already present? (As I=20
have said already) I really must have missed something so am=20
standing by for the 'gotcha'... please supply :)

Regards
Gavan Schneider

R2F2YW4gU2NobmVpZGVyIHdyb3RlOg0KPiBCdXQgSSBmZWVsIEkgaGF2ZSBtaXNzZWQgc29tZXRo
aW5nIGhlcmUuDQo+IA0KPiBSZWZlcnJpbmcgdG86DQo+IDxodHRwOi8vd3d3LnBvc3RncmVzcWwu
b3JnL2RvY3MvY3VycmVudC9zdGF0aWMvc3FsLWNyZWF0ZXRhYmxlLmh0bWw+DQoNCj4gQ0hFQ0sg
Y29uc3RyYWludHMsIE5PVCBOVUxMIGNvbnN0cmFpbnRzIGFuZCBGT1JFSUdOIEtFWQ0KPiBjb25z
dHJhaW50cyBhbGwgbG9vayB2ZXJ5IGRlZmVycmFibGUgaW4gdGhpcyBkZWZpbml0aW9uLiBJZg0K
PiB0aGF0J3MgdGhlIGNhc2UsIHdoeSBhcmUgd2UgaGF2aW5nIHRoaXMgZGlzY3Vzc2lvbiBpZiB0
aGUNCj4gcmVxdWVzdGVkIGZ1bmN0aW9uYWxpdHkvY29tcGxpYW5jZSBpcyBhbHJlYWR5IHByZXNl
bnQ/IChBcyBJDQo+IGhhdmUgc2FpZCBhbHJlYWR5KSBJIHJlYWxseSBtdXN0IGhhdmUgbWlzc2Vk
IHNvbWV0aGluZyBzbyBhbQ0KPiBzdGFuZGluZyBieSBmb3IgdGhlICdnb3RjaGEnLi4uIHBsZWFz
ZSBzdXBwbHkgOikNCg0KRnVydGhlciBkb3duIG9uIHRoZSBwYWdlIHlvdSBxdW90ZSwgaXQgc2F5
czoNCg0KICBERUZFUlJBQkxFDQogIE5PVCBERUZFUlJBQkxFDQoNCiAgVGhpcyBjb250cm9scyB3
aGV0aGVyIHRoZSBjb25zdHJhaW50IGNhbiBiZSBkZWZlcnJlZC4NCiAgQSBjb25zdHJhaW50IHRo
YXQgaXMgbm90IGRlZmVycmFibGUgd2lsbCBiZSBjaGVja2VkDQogIGltbWVkaWF0ZWx5IGFmdGVy
IGV2ZXJ5IGNvbW1hbmQuIENoZWNraW5nIG9mIGNvbnN0cmFpbnRzDQogIHRoYXQgYXJlIGRlZmVy
cmFibGUgY2FuIGJlIHBvc3Rwb25lZCB1bnRpbCB0aGUgZW5kIG9mDQogIHRoZSB0cmFuc2FjdGlv
biAodXNpbmcgdGhlIFNFVCBDT05TVFJBSU5UUyBjb21tYW5kKS4NCiAgTk9UIERFRkVSUkFCTEUg
aXMgdGhlIGRlZmF1bHQuIEN1cnJlbnRseSwgb25seSBVTklRVUUsDQogIFBSSU1BUlkgS0VZLCBF
WENMVURFLCBhbmQgUkVGRVJFTkNFUyAoZm9yZWlnbiBrZXkpDQogIGNvbnN0cmFpbnRzIGFjY2Vw
dCB0aGlzIGNsYXVzZS4NCiAgTk9UIE5VTEwgYW5kIENIRUNLIGNvbnN0cmFpbnRzIGFyZSBub3Qg
ZGVmZXJyYWJsZS4NCg0KWW91cnMsDQpMYXVyZW56IEFsYmUNCg==

On Friday, February 8, 2013 at 19:34, Albe Laurenz wrote:

Gavan Schneider wrote:
>>Referring to:
>><http://www.postgresql.org/docs/current/static/sql-createtable.html>
>>
>>I really must have missed something so am
>>standing by for the 'gotcha'... please supply :)

>Further down on the page you quote, it says: ...

Thank you, it had to be somewhere. :)


And this leads to a thought. Why is it that in this chapter the=20
documentation gives a synopsis which is not correct for the=20
current implementation but relies on a negation much further=20
down the page to properly describe the actual behaviour?

Mostly the manual follows the pattern of a correct synopsis=20
(where correct means what this version will actually do)=20
followed by a section setting out the differences from the=20
standard and/or other implementations.

While this chapter of the current documentation is not in error=20
overall it's a bit misleading.

Of course if anything is going to change my preference would be=20
to leave the synopsis in its SQL conformant state and bring the=20
implementation up to standard in this area, meaning we can drop=20
the contradiction/'correcting' paragraph. And, no, I'm not=20
holding my breath on this just now.

Regards
Gavan Schneider

R2F2YW4gU2NobmVpZGVyIHdyb3RlOg0KPiBBbmQgdGhpcyBsZWFkcyB0byBhIHRob3VnaHQuIFdo
eSBpcyBpdCB0aGF0IGluIHRoaXMgY2hhcHRlciB0aGUNCj4gZG9jdW1lbnRhdGlvbiBnaXZlcyBh
IHN5bm9wc2lzIHdoaWNoIGlzIG5vdCBjb3JyZWN0IGZvciB0aGUNCj4gY3VycmVudCBpbXBsZW1l
bnRhdGlvbiBidXQgcmVsaWVzIG9uIGEgbmVnYXRpb24gbXVjaCBmdXJ0aGVyDQo+IGRvd24gdGhl
IHBhZ2UgdG8gcHJvcGVybHkgZGVzY3JpYmUgdGhlIGFjdHVhbCBiZWhhdmlvdXI/DQoNClRoZSBz
eW5vcHNpcyBnaXZlcyB0aGUgc3ludGF4IGRpYWdyYW0sIHRoYXQgaXMsIHdoYXQgeW91IG11c3QN
CnR5cGUgdG8gYXZvaWQgYSBzeW50YXggZXJyb3IuDQoNCk5vdCBldmVyeSBzeW50YWN0aWNhbGx5
IGNvcnJlY3Qgc3RhdGVtZW50IGlzIGFsc28gY29ycmVjdC4NCg0KU29tZSBleGFtcGxlczoNCg0K
dGVzdD0+IENSRUFURSBUQUJMRSB0ZXN0IChpZCBpbnRlZ2VyIFBSSU1BUlkgS0VZLCB2YWwgdGV4
dCBOT1QgTlVMTCBVU0lORyBERUZFUlJBQkxFKTsNCkVSUk9SOiAgc3ludGF4IGVycm9yIGF0IG9y
IG5lYXIgIlVTSU5HIg0KTElORSAxOiAuLi5FIHRlc3QgKGlkIGludGVnZXIgUFJJTUFSWSBLRVks
IHZhbCB0ZXh0IE5PVCBOVUxMIFVTSU5HIERFRkUuLi4NCiAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBeDQpBIHN5bnRheCBlcnJvci4N
Cg0KdGVzdD0+IENSRUFURSBUQUJMRSB0ZXN0IChpZCBpbnRlZ2VyIFBSSU1BUlkgS0VZLCB2YWwg
dGV4dCBOT1QgTlVMTCBERUZFUlJBQkxFKTsNCkVSUk9SOiAgbWlzcGxhY2VkIERFRkVSUkFCTEUg
Y2xhdXNlDQpMSU5FIDE6IC4uLkUgdGVzdCAoaWQgaW50ZWdlciBQUklNQVJZIEtFWSwgdmFsIHRl
eHQgTk9UIE5VTEwgREVGRVJSQUJMRS4uLg0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIF4NCkEgc3ludGFjdGljYWxseSBjb3JyZWN0
IHN0YXRlbWVudCB0aGF0IGlzIG5vbnRoZWxlc3MgaW5jb3JyZWN0Lg0KDQp0ZXN0PT4gQ1JFQVRF
IFRBQkxFIHRlc3QgKGlkIGludGVnZXIgUFJJTUFSWSBLRVksIHZhbCBpbnRlZ2VyIERFRkFVTFQg
J3knKTsNCkVSUk9SOiAgaW52YWxpZCBpbnB1dCBzeW50YXggZm9yIGludGVnZXI6ICJ5Ig0KDQpU
aGUgc2FtZS4NCg0KWW91cnMsDQpMYXVyZW56IEFsYmUNCg==