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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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 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
>

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

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend