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
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
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
Forgot to cc general
On Tue, Feb 5, 2013 at 1:39 AM, Darren Duncan dar...@darrenduncan.netwrote:
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
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
On 7 February 2013 07:45, Albe Laurenz laurenz.a...@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
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
On 7 February 2013 09:02, Bèrto ëd Sèra berto.d.s...@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,
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
On 7 February 2013 08:50, Dean Rasheed dean.a.rash...@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 ---
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
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)
);
On 2013-02-07, Albe Laurenz laurenz.a...@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
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
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
On 2013-02-06, Bèrto ëd Sèra berto.d.s...@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
On Wed, Feb 6, 2013 at 8:01 PM, Jasen Betts ja...@xnet.co.nz wrote:
On 2013-02-06, Bèrto ëd Sèra berto.d.s...@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
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
On Wed, Feb 6, 2013 at 10:36 PM, Bèrto ëd Sèra berto.d.s...@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
On 6 February 2013 12:56, Chris Angelico ros...@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
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 ros...@gmail.com wrote:
If you get into a taxi and ask
to be driven to New Zealand within the hour, no amount of begging
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
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
På tirsdag 05. februar 2013 kl. 09:59:54, skrev Albe Laurenz laurenz.a...@wien.gv.at:
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
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
På tirsdag 05. februar 2013 kl. 10:39:43, skrev Darren Duncan dar...@darrenduncan.net:
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
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
2013/2/5 Darren Duncan dar...@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
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 working with ORMs this often is the
case, especially with circular
On 5 February 2013 11:15, Andreas Joseph Krogh andr...@officenet.no 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
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
På tirsdag 05. februar 2013 kl. 11:26:20, skrev Alban Hertroys haram...@gmail.com:
On 5 February 2013 11:15, Andreas Joseph Krogh andr...@officenet.no 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
On 5 February 2013 12:41, Andreas Joseph Krogh andr...@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
På tirsdag 05. februar 2013 kl. 13:32:15, skrev Alban Hertroys haram...@gmail.com:
On 5 February 2013 12:41, Andreas Joseph Krogh andr...@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
Hi,
The value of having NOT NULL deferrable is, well, to not check for NULL
until the tx commits. When working with ORMs this often is the case,
especially with circular FKs.
+1000 here.
Cheers
Bèrto
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to
On Tue, Feb 5, 2013 at 11:32 PM, Alban Hertroys haram...@gmail.com wrote:
On 5 February 2013 12:41, Andreas Joseph Krogh andr...@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
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
On Wed, Feb 6, 2013 at 12:20 AM, Bèrto ëd Sèra berto.d.s...@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
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
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
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
På tirsdag 05. februar 2013 kl. 14:13:20, skrev Chris Angelico ros...@gmail.com:
On Tue, Feb 5, 2013 at 11:32 PM, Alban Hertroys haram...@gmail.com wrote:
On 5 February 2013 12:41, Andreas Joseph Krogh andr...@officenet.no wrote:
There are lots of things you can do, but when it's the ORM
On 5 February 2013 14:20, Bèrto ëd Sèra berto.d.s...@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
On 2013-02-05, Bèrto ëd Sèra berto.d.s...@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
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
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:
46 matches
Mail list logo