Обсуждение: 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==