Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 sy

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On Friday, February 8, 2013 at 19:34, Albe Laurenz wrote: Gavan Schneider wrote: Referring to: I really must have missed something so am standing by for the 'gotcha'... please supply :) Further down on the page you quote, i

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Gavan Schneider wrote: > But I feel I have missed something here. > > Referring to: > > CHECK constraints, NOT NULL constraints and FOREIGN KEY > constraints all look very deferrable in this definition. If > that's the case, why

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 "boilerpla

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 i

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 2013-02-07, Albe Laurenz 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 databa

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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) ); t

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Dean Rasheed wrote: >> ISO/IEC 9075-2:2003 says: >> >> Chapter 10.8 ( and ): >> >> ::= >> [ [ NOT ] DEFERRABLE ] >> | [ NOT ] DEFERRABLE [ ] >> >> ::= >> INITIALLY DEFERRED >> | INITIALLY IMMEDIATE >> >> >> So yes, the standard caters for deferrable NOT NULL constraints. >> >> Moreo

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 7 February 2013 08:50, Dean Rasheed 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 eff

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 7 February 2013 09:02, Bèrto ëd Sèra 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 >

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 Pa

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 7 February 2013 07:45, Albe Laurenz 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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Forgot to cc general On Tue, Feb 5, 2013 at 1:39 AM, Darren Duncan 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 w

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On Wednesday, February 6, 2013 at 23:31, 00jkxma...@sneakemail.com (Alban Hertroys haramrae-at-gmail.com |pg-gts/Basic|) wrote: On 6 February 2013 12:56, Chris Angelico 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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 6 February 2013 12:56, Chris Angelico 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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On Wed, Feb 6, 2013 at 10:36 PM, Bèrto ëd Sèra 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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 ve

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On Wed, Feb 6, 2013 at 8:01 PM, Jasen Betts wrote: > On 2013-02-06, Bèrto ëd Sèra 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 >

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 2013-02-06, Bèrto ëd Sèra 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. >

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 2013-02-05, Bèrto ëd Sèra 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 scen

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 5 February 2013 14:20, Bèrto ëd Sèra 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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

På tirsdag 05. februar 2013 kl. 14:13:20, skrev Chris Angelico : On Tue, Feb 5, 2013 at 11:32 PM, Alban Hertroys wrote: > On 5 February 2013 12:41, Andreas Joseph Krogh wrote: >> >> There are lots of things you can do, but when it's the ORM which does it >> you have limited co

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 def

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 s

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 mea

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On Wed, Feb 6, 2013 at 12:20 AM, Bèrto ëd Sèra 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 rule

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 o

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On Tue, Feb 5, 2013 at 11:32 PM, Alban Hertroys wrote: > On 5 February 2013 12:41, Andreas Joseph Krogh 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 w

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi, > The value of having NOT NULL deferrable is, well, to not check for NULL > until the tx commits. When working with ORMs this often is the case, > especially with circular FKs. +1000 here. Cheers Bèrto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes t

Re: [GENERAL] DEFERRABLE NOT NULL constraint

På tirsdag 05. februar 2013 kl. 13:32:15, skrev Alban Hertroys : On 5 February 2013 12:41, Andreas Joseph Krogh 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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 5 February 2013 12:41, Andreas Joseph Krogh 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 th

Re: [GENERAL] DEFERRABLE NOT NULL constraint

På tirsdag 05. februar 2013 kl. 11:26:20, skrev Alban Hertroys : On 5 February 2013 11:15, Andreas Joseph Krogh wrote: På tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer : Andreas Joseph Krogh, 05.02.2013 10:57: > Th

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 FK

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 5 February 2013 11:15, Andreas Joseph Krogh wrote: > På tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer < > spam_ea...@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 w

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

2013/2/5 Darren Duncan : > 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_ite

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Andreas Joseph Krogh, 05.02.2013 10:57: The value of having NOT NULL deferrable is, well, to not check for NULL until the tx commits. When working with ORMs this often is the case, especially with circular FKs. With circular FKs it's enough to define the FK constraint as deferred. -- Sent v

Re: [GENERAL] DEFERRABLE NOT NULL constraint

På tirsdag 05. februar 2013 kl. 10:39:43, skrev Darren Duncan : 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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 with $xxx" mechanism? I'd like to know what value there is

Re: [GENERAL] DEFERRABLE NOT NULL constraint

På tirsdag 05. februar 2013 kl. 09:59:54, skrev Albe Laurenz : 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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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