Skip site navigation (1) Skip section navigation (2)

Re: BUG #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Matt Nourse <matthew(at)nplus1(dot)com(dot)au>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery
Date: 2010-05-29 19:06:30
Message-ID: 201005291906.o4TJ6Up01271@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Applied.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Tom Lane wrote:
> > "Matt Nourse" <matthew(at)nplus1(dot)com(dot)au> writes:
> > > CREATE DOMAIN test_id_domain INT NOT NULL; 
> > > CREATE TABLE test_state(id test_id_domain PRIMARY KEY, display_value
> > > varchar(20) NOT NULL);
> > > CREATE TABLE test_city(state_id test_id_domain REFERENCES test_state(id));
> > 
> > > This produces an error as expected:
> > 
> > > INSERT INTO test_city(state_id) VALUES (NULL);
> > 
> > > This successfully inserts a NULL value into the state_id field:
> > 
> > > INSERT INTO test_city(state_id) VALUES ((SELECT id FROM test_state WHERE
> > > display_value = 'Nonexistent state'));
> > 
> > There are any number of ways you can get a similar result, for example
> > a LEFT JOIN.  To my mind, this demonstrates why not-null constraints
> > associated with datatypes are a fundamentally flawed concept.  If the
> > SELECT or LEFT JOIN can produce a null value, as it clearly can, then
> > it's nonsensical to think that the output column should be considered
> > to be of a NOT NULL domain type.  But what else should it be?  If we
> > smash domains to their base types when assigning result types of
> > queries, that will make many people unhappy.
> > 
> > Moral: NOT NULL constraints at the domain level suck.  Don't use 'em.
> 
> I have written up the following documentation patch to document this
> behavior.  It doesn't seem like something we want to fix, so I am not
> making it a TODO item.
> 
> -- 
>   Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
>   EnterpriseDB                             http://enterprisedb.com

[ text/x-diff is unsupported, treating like TEXT/PLAIN ]

> Index: doc/src/sgml/ref/create_domain.sgml
> ===================================================================
> RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_domain.sgml,v
> retrieving revision 1.34
> diff -c -c -r1.34 create_domain.sgml
> *** doc/src/sgml/ref/create_domain.sgml	3 Apr 2010 07:22:58 -0000	1.34
> --- doc/src/sgml/ref/create_domain.sgml	28 May 2010 17:19:35 -0000
> ***************
> *** 121,127 ****
>         <term><literal>NOT NULL</></term>
>         <listitem>
>          <para>
> !         Values of this domain are not allowed to be null.
>          </para>
>         </listitem>
>        </varlistentry>
> --- 121,132 ----
>         <term><literal>NOT NULL</></term>
>         <listitem>
>          <para>
> !         Values of this domain are normally prevented from being null.
> !         It is still possible for a domain with this constraint
> !         to take a null value if it is assigned a matching domain type
> !         that has become null, e.g. via a LEFT OUTER JOIN, or
> !         <command>INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM
> !         tab WHERE false))</command>.
>          </para>
>         </listitem>
>        </varlistentry>

> 
> -- 
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

In response to

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2010-05-29 21:00:55
Subject: Re: BUG #5469: regexp_matches() has poor behaviour and more poor documentation
Previous:From: Kevin GrittnerDate: 2010-05-29 14:17:51
Subject: Re: BUG #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group