Thread: BUG #2144: Domain NOT NULL constraints ignored in rules

The following bug has been logged online:

Bug reference:      2144
Logged by:          John Supplee
Email address:      john@supplee.com
PostgreSQL version: 8.1.1
Operating system:   Fedore Core 4
Description:        Domain NOT NULL constraints ignored in rules
Details:

I have a database with some views which have rules for insertion.  One of
the views (view_a) inserts data into another view (view_b) with an insert
rule.  The data inserted into view_b by view_a insert rule does not have
domain NOT NULL constraints enforced.  That is, it is possible to insert a
NULL into a column whose domain forbids NULLs.  NOT NULL constraints
attached directly to columns continue to forbid NULL data in the second view
(b).

If this requires more explanation let me know.

I have observed this behavior on versions 8.0.5 and 8.1.1

"John Supplee" <john@supplee.com> writes:
> Description:        Domain NOT NULL constraints ignored in rules

Works for me:

regression=# create domain dint as int not null;
CREATE DOMAIN
regression=# create table t1 (f1 dint);
CREATE TABLE
regression=# create view v1 as select * from t1;
CREATE VIEW
regression=# create rule r1 as on insert to v1 do instead
regression-# insert into t1 values(new.f1);
CREATE RULE
regression=# insert into v1 values(1);
INSERT 0 1
regression=# insert into v1 values(null);
ERROR:  domain dint does not allow null values
regression=#

How about a test case?

            regards, tom lane

Tom Lane wrote:

> Works for me:
>
> regression=# create domain dint as int not null;
> CREATE DOMAIN
> regression=# create table t1 (f1 dint);
> CREATE TABLE
> regression=# create view v1 as select * from t1;
> CREATE VIEW
> regression=# create rule r1 as on insert to v1 do instead
> regression-# insert into t1 values(new.f1);
> CREATE RULE
> regression=# insert into v1 values(1);
> INSERT 0 1
> regression=# insert into v1 values(null);
> ERROR:  domain dint does not allow null values
> regression=#
>
> How about a test case?
>
>             regards, tom lane
>
>

You need to modify your test case slightly.

test=# create domain dint as int not null;
CREATE DOMAIN
test=# create table t1 (f1 dint, f2 dint);
CREATE TABLE
test=# create view v1 as select * from t1;
CREATE VIEW
test=# create rule r1 as on insert to v1 do instead
test-# insert into t1 values (new.f1, new.f2);
CREATE RULE
test=# insert into v1 values( 1 );
INSERT 0 1
test=# select * from v1;
 f1 | f2
----+----
  1 |
(1 row)

Notice that f2 has a null value even though the domain constraint should
forbid it.


Now try this:

test=# delete from t1;
DELETE 1
test=# alter table t1 alter column f2 set not null;
ALTER TABLE
test=# insert into v1 values( 1 );
ERROR:  null value in column "f2" violates not-null constraint

Having the constraint on the column correctly forbids the NULL value.  For
now I have tagged all columns with the NOT NULL constraint individually, but
I think this should be fixed.


John Supplee

"John Supplee" <john@supplee.com> writes:
> Tom Lane wrote:
>> Works for me:

> You need to modify your test case slightly.

OK, got it.  Patch for 8.1 is attached if you need it.  Thanks for the
test case.

            regards, tom lane


Index: rewriteManip.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/rewrite/rewriteManip.c,v
retrieving revision 1.92.2.2
diff -c -r1.92.2.2 rewriteManip.c
*** rewriteManip.c    23 Nov 2005 17:21:22 -0000    1.92.2.2
--- rewriteManip.c    6 Jan 2006 19:41:30 -0000
***************
*** 18,23 ****
--- 18,24 ----
  #include "optimizer/clauses.h"
  #include "optimizer/tlist.h"
  #include "parser/parsetree.h"
+ #include "parser/parse_coerce.h"
  #include "parser/parse_relation.h"
  #include "rewrite/rewriteManip.h"
  #include "utils/lsyscache.h"
***************
*** 838,844 ****
          else
          {
              /* Otherwise replace unmatched var with a null */
!             return (Node *) makeNullConst(var->vartype);
          }
      }
      else
--- 839,851 ----
          else
          {
              /* Otherwise replace unmatched var with a null */
!             /* need coerce_to_domain in case of NOT NULL domain constraint */
!             return coerce_to_domain((Node *) makeNullConst(var->vartype),
!                                     InvalidOid,
!                                     var->vartype,
!                                     COERCE_IMPLICIT_CAST,
!                                     false,
!                                     false);
          }
      }
      else

Tom Lane wrote:

> OK, got it.  Patch for 8.1 is attached if you need it.
> Thanks for the test case.

Wow, thanks for the quick work.  But since I can solve the problem with NOT
NULL constraints directly on the column I will wait for the next release to
test it (I don't have the source on my machine).

BTW, I also observed the same behavior in 8.0.5 as well.


John Supplee

On 1/7/06, John Supplee <john@supplee.com> wrote:
> Tom Lane wrote:
>
> > OK, got it.  Patch for 8.1 is attached if you need it.
> > Thanks for the test case.
>
> Wow, thanks for the quick work.  But since I can solve the problem with N=
OT
> NULL constraints directly on the column I will wait for the next release =
to
> test it (I don't have the source on my machine).
>
> BTW, I also observed the same behavior in 8.0.5 as well.
>
>
> John Supplee
>

of course. Tom backpatch all branches until 7.3...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)