Thread: NOT DEFERRABLE as default, why and how to manage it.
I just learnt that NOT DEFERRABLE is default. I vaguely understand that generally stricter policies protect distracted programmers from making mistakes... but missing an "alter constraint" it makes refactoring a PITA. Is it mandated by SQL standard? Any other rational reason to make NOT DEFERRABLE default? Is there any shortcut if I've to change to deferrable most of my constraints? Other than pgfoundry is there any other recipe repository where to look for refactoring tools for postgresql? -- Ivan Sergio Borgonovo http://www.webthatworks.it
Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: > I just learnt that NOT DEFERRABLE is default. > Is it mandated by SQL standard? Yes. > Is there any shortcut if I've to change to deferrable most of my > constraints? Probably not, short of writing a little script. > Other than pgfoundry is there any other recipe repository where to > look for refactoring tools for postgresql? The wiki, I'd say.
On Tue, 19 Aug 2008 11:20:08 +0300 Peter Eisentraut <peter_e@gmx.net> wrote: > Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: > > I just learnt that NOT DEFERRABLE is default. > > > Is it mandated by SQL standard? > > Yes. Is there any reason they put it that way in the standard other than the mantra "stricter is better"? > > Is there any shortcut if I've to change to deferrable most of my > > constraints? > > Probably not, short of writing a little script. Reading the wiki an alter constraint is in the TODO. What about a: update pg_constraint set deeferrable=true where contype='f' and confupdtype<>'r' and confdeltype<>'r' ... BTW looking at pg_constraint and http://www.alberton.info/postgresql_meta_info.html was inspirational. What are the general rules about modifying the system tables? Where can I find what can be done and when and what can't be done? > > Other than pgfoundry is there any other recipe repository where > > to look for refactoring tools for postgresql? > The wiki, I'd say. Thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Tue, 19 Aug 2008 10:49:11 +0200 Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: > On Tue, 19 Aug 2008 11:20:08 +0300 > Peter Eisentraut <peter_e@gmx.net> wrote: > > Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: > > > I just learnt that NOT DEFERRABLE is default. > > > Is it mandated by SQL standard? > > Yes. > Is there any reason they put it that way in the standard other than > the mantra "stricter is better"? After reflecting a bit I think it is a matter of "failing earlier". But it doesn't make things more transparent. Since there is no simple standard way to see which constraints are deferrable and no simple way to alter them. If you expect a constraint to be deferrable and it is not there are higher chances you'll have some warning. If you expect a constraint to be not deferrable but it is... the chances that something you're not expecting will silently happen are higher. But you can still get surprises in both cases. It would be nice to know some way which constraint are checked during a transaction so it would be easier to see wich ones you really need to defer and which one were declared as not deferrable. anyway are there guidelines on how/when changing directly the system tables? -- Ivan Sergio Borgonovo http://www.webthatworks.it
Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: > > Is there any reason they put it that way in the standard other than > > the mantra "stricter is better"? > > After reflecting a bit I think it is a matter of "failing earlier". Deferrable constraints are an optional feature of SQL, and the reason this default is chosen is that systems with and without the feature behave the same. > But it doesn't make things more transparent. > Since there is no simple standard way to see which constraints are > deferrable and no simple way to alter them. Query information_schema.table_constraints to find out about existing constraints and their parameters.