Re: DEFERRABLE NOT NULL constraint

Forgot to cc general

On Tue, Feb 5, 2013 at 1:39 AM, Darren Duncan <darren(at)darrenduncan(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>