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

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