Thread
-
DEFERRABLE NOT NULL constraint
Andreas Joseph Krogh <andreak@officenet.no> — 2013-02-04T18:48:09Z
<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>
-
Re: DEFERRABLE NOT NULL constraint
Albe Laurenz <laurenz.albe@wien.gv.at> — 2013-02-05T08:59:54Z
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
-
Re: DEFERRABLE NOT NULL constraint
Andreas Joseph Krogh <andreak@officenet.no> — 2013-02-05T09:22:18Z
<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>
-
Re: DEFERRABLE NOT NULL constraint
Darren Duncan <darren@darrenduncan.net> — 2013-02-05T09:39:43Z
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
-
Re: DEFERRABLE NOT NULL constraint
Andreas Joseph Krogh <andreak@officenet.no> — 2013-02-05T09:57:34Z
<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>
-
Re: DEFERRABLE NOT NULL constraint
Thomas Kellerer <spam_eater@gmx.net> — 2013-02-05T10:04:27Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Victor Yegorov <vyegorov@gmail.com> — 2013-02-05T10:11:10Z
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
-
Re: DEFERRABLE NOT NULL constraint
Andreas Joseph Krogh <andreak@officenet.no> — 2013-02-05T10:15:55Z
<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>
-
Re: DEFERRABLE NOT NULL constraint
Alban Hertroys <haramrae@gmail.com> — 2013-02-05T10:26:20Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Thomas Kellerer <spam_eater@gmx.net> — 2013-02-05T10:31:49Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Andreas Joseph Krogh <andreak@officenet.no> — 2013-02-05T11:41:21Z
<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>
-
Re: DEFERRABLE NOT NULL constraint
Alban Hertroys <haramrae@gmail.com> — 2013-02-05T12:32:15Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Andreas Joseph Krogh <andreak@officenet.no> — 2013-02-05T12:52:39Z
<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>
-
Re: DEFERRABLE NOT NULL constraint
Bèrto ëd Sèra <berto.d.sera@gmail.com> — 2013-02-05T13:12:31Z
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
-
Re: DEFERRABLE NOT NULL constraint
Chris Angelico <rosuav@gmail.com> — 2013-02-05T13:13:20Z
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
-
Re: DEFERRABLE NOT NULL constraint
Bèrto ëd Sèra <berto.d.sera@gmail.com> — 2013-02-05T13:20:35Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Chris Angelico <rosuav@gmail.com> — 2013-02-05T13:26:40Z
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
-
Re: DEFERRABLE NOT NULL constraint
Bèrto ëd Sèra <berto.d.sera@gmail.com> — 2013-02-05T13:31:39Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Albe Laurenz <laurenz.albe@wien.gv.at> — 2013-02-05T13:47:11Z
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
-
Re: DEFERRABLE NOT NULL constraint
Bèrto ëd Sèra <berto.d.sera@gmail.com> — 2013-02-05T13:51:09Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Andreas Joseph Krogh <andreak@officenet.no> — 2013-02-05T14:24:36Z
<div>På tirsdag 05. februar 2013 kl. 14:13:20, skrev Chris Angelico <<a href="mailto:rosuav@gmail.com" target="_blank">rosuav@gmail.com</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;">On Tue, Feb 5, 2013 at 11:32 PM, Alban Hertroys <haramrae@gmail.com> wrote:<br> > On 5 February 2013 12:41, Andreas Joseph Krogh <andreak@officenet.no> wrote:<br> >><br> >> There are lots of things you can do, but when it's the ORM which does it<br> >> you have limited control, and that's the way it should to be (me as<br> >> application-developer having to worry less about such details).<br> ><br> > In that case it's your ORM that needs fixing, not the database.<br> <br> Agreed. One of the differences between MySQL and PostgreSQL is that<br> the latter gives you a database with rules, while the former is a<br> place for an application to store data. This last couple of weeks I've<br> been working with a really sloppily-built application (and a very<br> popular one too, so I won't name names), and it fits MySQL<br> perfectly... What I'd much rather do is build real rules that may not<br> EVER be violated. While I can see the value in deferring foreign key<br> constraints (circular references - never used 'em though), I don't see<br> any reason to create a record with a NULL and then replace that NULL<br> before committing. Sort out program logic first; then look to the<br> database.<br> <br> Making people's lives easier in the short term is NOT the greatest<br> goal of a database. Consistent data will make the admins' lives far<br> easier in the long term. I do not ever want to have to deal with<br> BTrieve file corruption in my life.</div> </blockquote> <div> </div> <div>I've been using PG since v-6.5 and I'm very aware of its strengths, and also its weaknesses. There really isn't an argument for not having NOT NULL deferrable constraints, other than -hackers not prioritizing it, which I understand perfectly well as they have lots of other interesting stuff on their plate.</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> </div>
-
Re: DEFERRABLE NOT NULL constraint
Alban Hertroys <haramrae@gmail.com> — 2013-02-05T14:25:51Z
On 5 February 2013 14:20, 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 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? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
-
Re: DEFERRABLE NOT NULL constraint
Jasen Betts <jasen@xnet.co.nz> — 2013-02-06T05:05:36Z
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
-
Re: DEFERRABLE NOT NULL constraint
Bèrto ëd Sèra <berto.d.sera@gmail.com> — 2013-02-06T07:37:47Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Jasen Betts <jasen@xnet.co.nz> — 2013-02-06T09:01:06Z
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
-
Re: DEFERRABLE NOT NULL constraint
Chris Angelico <rosuav@gmail.com> — 2013-02-06T09:22:03Z
On Wed, Feb 6, 2013 at 8:01 PM, Jasen Betts <jasen@xnet.co.nz> wrote: > 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? 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
-
Re: DEFERRABLE NOT NULL constraint
Bèrto ëd Sèra <berto.d.sera@gmail.com> — 2013-02-06T11:36:03Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Chris Angelico <rosuav@gmail.com> — 2013-02-06T11:56:31Z
On Wed, Feb 6, 2013 at 10:36 PM, Bèrto ëd Sèra <berto.d.sera@gmail.com> 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
-
Re: DEFERRABLE NOT NULL constraint
Alban Hertroys <haramrae@gmail.com> — 2013-02-06T12:31:25Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Gavan Schneider <pg-gts@snkmail.com> — 2013-02-06T19:38:21Z
On Wednesday, February 6, 2013 at 23:31, 00jkxma2vt@sneakemail.com (Alban Hertroys haramrae-at-gmail.com |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 this makes it DEFFERABLE. Taking a different tangent ... 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. Regards Gavan Schneider
-
Re: DEFERRABLE NOT NULL constraint
Albe Laurenz <laurenz.albe@wien.gv.at> — 2013-02-07T07:45:58Z
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. Yours, Laurenz Albe -
Re: DEFERRABLE NOT NULL constraint
Chris Travers <chris.travers@gmail.com> — 2013-02-07T08:11:24Z
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> >
-
Re: DEFERRABLE NOT NULL constraint
Chris Travers <chris.travers@gmail.com> — 2013-02-07T08:23:08Z
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
-
Re: DEFERRABLE NOT NULL constraint
Dean Rasheed <dean.a.rasheed@gmail.com> — 2013-02-07T08:50:52Z
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 -
Re: DEFERRABLE NOT NULL constraint
Bèrto ëd Sèra <berto.d.sera@gmail.com> — 2013-02-07T09:02:12Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Dean Rasheed <dean.a.rasheed@gmail.com> — 2013-02-07T09:09:20Z
On 7 February 2013 09:02, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote: > 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
-
Re: DEFERRABLE NOT NULL constraint
Bèrto ëd Sèra <berto.d.sera@gmail.com> — 2013-02-07T09:14:16Z
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.
-
Re: DEFERRABLE NOT NULL constraint
Dean Rasheed <dean.a.rasheed@gmail.com> — 2013-02-07T09:33:37Z
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
-
Re: DEFERRABLE NOT NULL constraint
Albe Laurenz <laurenz.albe@wien.gv.at> — 2013-02-07T10:51:35Z
Dean Rasheed wrote: >> ISO/IEC 9075-2:2003 says: >> >> 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. 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
-
Re: DEFERRABLE NOT NULL constraint
Jasen Betts <jasen@xnet.co.nz> — 2013-02-07T11:05:46Z
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 -
Re: DEFERRABLE NOT NULL constraint
Jasen Betts <jasen@xnet.co.nz> — 2013-02-07T11:17:07Z
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
-
Re: DEFERRABLE NOT NULL constraint
Albe Laurenz <laurenz.albe@wien.gv.at> — 2013-02-07T14:42:38Z
Jasen Betts wrote: > Well, the standard syntax allows them to be requested, check constraints too. > > what does the standard say about it behaviourally? What you'd expect: The checking of a constraint depends on its constraint mode within the current SQL-transaction. If the constraint mode is immediate, then the constraint is effectively checked at the end of each SQL-statement. If the constraint mode is deferred, then the constraint is effectively checked when the constraint mode is changed to immediate either explicitly by execution of a <set constraints mode statement>, or implicitly at the end of the current SQL-transaction. When a constraint is checked other than at the end of an SQL-transaction, if it is not satisfied, then an exception condition is raised and the SQL-statement that caused the constraint to be checked has no effect other than entering the exception information into the first diagnostics area. When a <commit statement> is executed, all constraints are effectively checked and, if any constraint is not satisfied, then an exception condition is raised and the SQL-transaction is terminated by an implicit <rollback statement>. > what do other major SQL databases do? Seems to work in Oracle: CREATE TABLE con_test( ID NUMBER(5) CONSTRAINT con_test_pk PRIMARY KEY DEFERRABLE INITIALLY DEFERRED, val VARCHAR2(20 CHAR) CONSTRAINT con_test_val_null NOT NULL DEFERRABLE INITIALLY DEFERRED ); Table created. INSERT INTO con_test VALUES (1, NULL); 1 row created. UPDATE con_test SET val = 'one' WHERE id = 1; 1 row updated. COMMIT; Commit complete. INSERT INTO con_test VALUES (1, 'two'); 1 row created. UPDATE con_test SET id = 2 WHERE val = 'two'; 1 row updated. COMMIT; Commit complete. INSERT INTO con_test VALUES (1, 'three'); 1 row created. COMMIT; * ERROR at line 1: ORA-02091: transaction rolled back ORA-00001: unique constraint (LAURENZ.CON_TEST_PK) violated -
Re: DEFERRABLE NOT NULL constraint
Gavan Schneider <pg-gts@snkmail.com> — 2013-02-07T21:25:12Z
Getting back to the OP (Andreas): On Tuesday, February 5, 2013 at 20:22, Andreas Joseph Krogh wrote: >På 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 >$xxx" mechanism? 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 and several suggested 'work arounds' needed to compensate for this implied SQL compliance failure, there seems to be no good reason why this 'entry in the TODO list' couldn't be sponsored 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 | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | 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 operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] CHECK constraints, NOT NULL constraints and FOREIGN KEY constraints all look very deferrable in this definition. If that's the case, why are we having this discussion if the requested functionality/compliance is already present? (As I have said already) I really must have missed something so am standing by for the 'gotcha'... please supply :) Regards Gavan Schneider -
Re: DEFERRABLE NOT NULL constraint
Albe Laurenz <laurenz.albe@wien.gv.at> — 2013-02-08T08:34:54Z
Gavan Schneider wrote: > But I feel I have missed something here. > > Referring to: > <http://www.postgresql.org/docs/current/static/sql-createtable.html> > CHECK constraints, NOT NULL constraints and FOREIGN KEY > constraints all look very deferrable in this definition. If > that's the case, why are we having this discussion if the > requested functionality/compliance is already present? (As I > have said already) I really must have missed something so am > standing by for the 'gotcha'... please supply :) Further down on the page you quote, it says: DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. Yours, Laurenz Albe
-
Re: DEFERRABLE NOT NULL constraint
Gavan Schneider <pg-gts@snkmail.com> — 2013-02-08T12:46:35Z
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 documentation gives a synopsis which is not correct for the current implementation but relies on a negation much further down the page to properly describe the actual behaviour? Mostly the manual follows the pattern of a correct synopsis (where correct means what this version will actually do) followed by a section setting out the differences from the standard and/or other implementations. While this chapter of the current documentation is not in error overall it's a bit misleading. Of course if anything is going to change my preference would be to leave the synopsis in its SQL conformant state and bring the implementation up to standard in this area, meaning we can drop the contradiction/'correcting' paragraph. And, no, I'm not holding my breath on this just now. Regards Gavan Schneider
-
Re: DEFERRABLE NOT NULL constraint
Albe Laurenz <laurenz.albe@wien.gv.at> — 2013-02-08T13:25:58Z
Gavan Schneider wrote: > And this leads to a thought. Why is it that in this chapter the > documentation gives a synopsis which is not correct for the > current implementation but relies on a negation much further > down the page to properly describe the actual behaviour? The synopsis gives the syntax diagram, that is, what you must type to avoid a syntax error. Not every syntactically correct statement is also correct. Some examples: test=> CREATE TABLE test (id integer PRIMARY KEY, val text NOT NULL USING DEFERRABLE); ERROR: syntax error at or near "USING" LINE 1: ...E test (id integer PRIMARY KEY, val text NOT NULL USING DEFE... ^ A syntax error. test=> CREATE TABLE test (id integer PRIMARY KEY, val text NOT NULL DEFERRABLE); ERROR: misplaced DEFERRABLE clause LINE 1: ...E test (id integer PRIMARY KEY, val text NOT NULL DEFERRABLE... ^ A syntactically correct statement that is nontheless incorrect. test=> CREATE TABLE test (id integer PRIMARY KEY, val integer DEFAULT 'y'); ERROR: invalid input syntax for integer: "y" The same. Yours, Laurenz Albe