DEFERRABLE NOT NULL constraint
<div>It's currently (9.2) not possible to define DEFERRABLE NOT NULL constraints. Meaning the following is not valid:</div>
<div> </div>
<div>CREATE TABLE my_table(</div>
<div>id varchar PRIMARY KEY,</div>
<div>stuff_id BIGINT NOT NULL <u>DEFERRABLE INITIALLY DEFERRED</u></div>
<div>);</div>
<div> </div>
<div>While it's possible to define a trigger to enforce this, like this:</div>
<div> </div>
<div>
<div>CREATE CONSTRAINT TRIGGER my_table_t AFTER INSERT OR UPDATE ON onp_crm_relation <u>DEFERRABLE INITIALLY DEFERRED</u></div>
<div>FOR EACH ROW EXECUTE PROCEDURE my_table_check_stuff_id_nn_tf();</div>
<div> </div>
</div>
<div>And have the <u>my_table_check_stuff_id_nn_tf()</u> raise an exception if "stuff_id" is null.</div>
<div> </div>
<div>Having deferrable constraints on FKs and UKs is really nice and when working with ORMs it's almost impossible to not use this feature.</div>
<div> </div>
<div>Are there any plans to make NOT NULL constraints deferrable so one can avoid the trigger "boilerplate"?</div>
<div> </div>
<div class="origo-email-signature">--<br>
Andreas Joseph Krogh <andreak@officenet.no> mob: +47 909 56 963<br>
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br>
Public key: http://home.officenet.no/~andreak/public_key.asc</div>
Andreas Joseph Krogh wrote:
It's currently (9.2) not possible to define DEFERRABLE NOT NULL constraints. 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 "stuff_id" is null.
Having deferrable constraints on FKs and UKs is really nice and when working 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...
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<div>På tirsdag 05. februar 2013 kl. 09:59:54, skrev Albe Laurenz <<a href="mailto:laurenz.albe@wien.gv.at" target="_blank">laurenz.albe@wien.gv.at</a>>:</div>
<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style="display:inline; font-family: monospace; font-size: 12px;">Andreas Joseph Krogh wrote:<br>
> It's currently (9.2) not possible to define DEFERRABLE NOT NULL constraints. Meaning the following is<br>
> not valid:<br>
><br>
> CREATE TABLE my_table(<br>
> id varchar PRIMARY KEY,<br>
> stuff_id BIGINT NOT NULL DEFERRABLE INITIALLY DEFERRED<br>
> );<br>
><br>
> While it's possible to define a trigger to enforce this, like this:<br>
><br>
> CREATE CONSTRAINT TRIGGER my_table_t AFTER INSERT OR UPDATE ON onp_crm_relation DEFERRABLE INITIALLY<br>
> DEFERRED<br>
> FOR EACH ROW EXECUTE PROCEDURE my_table_check_stuff_id_nn_tf();<br>
><br>
> And have the my_table_check_stuff_id_nn_tf() raise an exception if "stuff_id" is null.<br>
><br>
> Having deferrable constraints on FKs and UKs is really nice and when working with ORMs it's almost<br>
> impossible to not use this feature.<br>
><br>
> Are there any plans to make NOT NULL constraints deferrable so one can avoid the trigger<br>
> "boilerplate"?<br>
<br>
Not that I know of.<br>
<br>
There's an entry in the TODO list that recognizes that it would<br>
be desirable to make NOT NULL a regular constraint (you can do<br>
that today by using CHECK (col IS NOT NULL) instead).<br>
<br>
But CHECK constraints are also not deferrable...</div>
</blockquote>
<div> </div>
<div>+100 for having NOT NULL and CHECK-constraints deferrable:-)</div>
<div> </div>
<div>Is there any "I want to sponsor development of <feature-X> with $xxx" mechanism?</div>
<div> </div>
<div class="origo-email-signature">--<br>
Andreas Joseph Krogh <andreak@officenet.no> mob: +47 909 56 963<br>
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br>
Public key: http://home.officenet.no/~andreak/public_key.asc</div>
<div> </div>
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<div>På tirsdag 05. februar 2013 kl. 10:39:43, skrev Darren Duncan <<a href="mailto:darren@darrenduncan.net" target="_blank">darren@darrenduncan.net</a>>:</div>
<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style="display:inline; font-family: monospace; font-size: 12px;">Deferrable foreign key and unique key constraints I can understand, but ...<br>
<br>
On 2013.02.05 1:22 AM, Andreas Joseph Krogh wrote:<br>
> +100 for having NOT NULL and CHECK-constraints deferrable:-)<br>
> Is there any "I want to sponsor development of <feature-X> with $xxx" mechanism?<br>
<br>
I'd like to know what value there is in making NOT NULL and CHECK deferrable.<br>
<br>
While we're at it, do we want to make the column data type check constraints<br>
deferrable too, so you can initially assign any value at all without regard for<br>
the declared type of the column? Then we only at constraints-immediate time<br>
say, sorry, you can't put a string in a number column, or, sorry, that number is<br>
too large, or that string is too long, or whatever.<br>
<br>
NOT NULL and CHECK constraints are effectively just part of a data type<br>
definition after all. Postgres' current behavior is fairly consistent; if we<br>
make these deferrable, then why stop there?</div>
</blockquote>
<div> </div>
<div>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.</div>
<div> </div>
<div class="origo-email-signature">--<br>
Andreas Joseph Krogh <andreak@officenet.no> mob: +47 909 56 963<br>
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br>
Public key: http://home.officenet.no/~andreak/public_key.asc</div>
<div> </div>
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<div>På tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer <<a href="mailto:spam_eater@gmx.net" target="_blank">spam_eater@gmx.net</a>>:</div>
<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style="display:inline; font-family: monospace; font-size: 12px;">Andreas Joseph Krogh, 05.02.2013 10:57:<br>
> The value of having NOT NULL deferrable is, well, to not check for<br>
> NULL until the tx commits. When working with ORMs this often is the<br>
> case, especially with circular FKs.<br>
<br>
With circular FKs it's enough to define the FK constraint as deferred.</div>
</blockquote>
<div> </div>
<div>I meant; circular FKs which are also NOT NULL</div>
<div> </div>
<div class="origo-email-signature">--<br>
Andreas Joseph Krogh <andreak@officenet.no> mob: +47 909 56 963<br>
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br>
Public key: http://home.officenet.no/~andreak/public_key.asc</div>
<div> </div>
On 5 February 2013 11:15, Andreas Joseph Krogh <andreak@officenet.no> wrote:
På 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 (=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.
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<div>På tirsdag 05. februar 2013 kl. 11:26:20, skrev Alban Hertroys <<a href="mailto:haramrae@gmail.com" target="_blank">haramrae@gmail.com</a>>:</div>
<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">On 5 February 2013 11:15, Andreas Joseph Krogh <span dir="ltr"><<a href="mailto:andreak@officenet.no" target="_blank">andreak@officenet.no</a>></span> wrote:
<div class="gmail_quote">
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>På tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer <<a href="mailto:spam_eater@gmx.net" target="_blank">spam_eater@gmx.net</a>>:</div>
<div class="im">
<blockquote style="border-left:1px solid rgb(204,204,204);margin:0pt 0pt 0pt 0.8ex;padding-left:1ex">
<div style="display:inline;font-family:monospace;font-size:12px">Andreas Joseph Krogh, 05.02.2013 10:57:<br>
> The value of having NOT NULL deferrable is, well, to not check for<br>
> NULL until the tx commits. When working with ORMs this often is the<br>
> case, especially with circular FKs.<br>
<br>
With circular FKs it's enough to define the FK constraint as deferred.</div>
</blockquote>
<div> </div>
</div>
<div>I meant; circular FKs which are also NOT NULL </div>
</blockquote>
<div> </div>
<div> If you would use that, every pair of circular inserts would require 2 inserts and an update (=insert & delete in MVCC):</div>
<div> </div>
<div>1; insert node 1 with FK null,</div>
<div>2; insert node 2 referencing node1,</div>
<div>3; update node 1 with FK to node 2.</div>
</div>
<div>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:</div>
<div> </div>
<div>1; decide FK key from node 1 to node 2,</div>
<div>2; insert node 1 referencing node 2,</div>
<div>3; insert node 2 referencing node 1</div>
<div> </div>
<div>This case typically only occurs when you're using surrogate keys, but even in that case you can select nextval(...).</div>
<div> </div>
<div>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.</div>
--<br>
If you can't see the forest for the trees,<br>
Cut the trees and you'll see there is no forest.</blockquote>
<div> </div>
<div>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).</div>
<div> </div>
<div class="origo-email-signature">--<br>
Andreas Joseph Krogh <andreak@officenet.no> mob: +47 909 56 963<br>
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br>
Public key: http://home.officenet.no/~andreak/public_key.asc</div>
<div> </div>
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.
<div>På tirsdag 05. februar 2013 kl. 13:32:15, skrev Alban Hertroys <<a href="mailto:haramrae@gmail.com" target="_blank">haramrae@gmail.com</a>>:</div>
<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">On 5 February 2013 12:41, Andreas Joseph Krogh <span dir="ltr"><<a href="mailto:andreak@officenet.no" target="_blank">andreak@officenet.no</a>></span> wrote:
<div class="gmail_quote">
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="HOEnZb">
<div class="h5">
<div>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).</div>
</div>
</div>
</blockquote>
<div> </div>
<div>In that case it's your ORM that needs fixing, not the database.</div>
</div>
</blockquote>
<div> </div>
<div>"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.</div>
<div> </div>
<div>My question was if having deferrable NOT NULLs was on PGs road-map, not whether or not someone finds it usefull or is able to work around it.</div>
<div> </div>
<div class="origo-email-signature">--<br>
Andreas Joseph Krogh <andreak@officenet.no> mob: +47 909 56 963<br>
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br>
Public key: http://home.officenet.no/~andreak/public_key.asc</div>
<div> </div>
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Feb 6, 2013 at 12:20 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com> 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
==============================
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Bèrto ëd Sèra wrote:
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.
That sounds a bit contrived, but you could create a view
and hide the processing in an INSTEAD OF INSERT trigger.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
