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);
}
}
elseTom 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 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 ;)