Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9829247636E for ; Tue, 17 Sep 2002 11:19:36 -0400 (EDT) Received: from squire.barchord.com (squire.barchord.com [216.194.67.18]) by postgresql.org (Postfix) with ESMTP id A4D3147621A for ; Tue, 17 Sep 2002 11:19:35 -0400 (EDT) Received: from [10.0.2.12] (nat.inquent.com [216.6.14.45]) by squire.barchord.com (Postfix) with ESMTP id B15B9424; Tue, 17 Sep 2002 11:19:29 -0400 (EDT) Subject: Re: Bug: COPY IN doesn't test domain constraints From: Rod Taylor To: Tom Lane Cc: PostgreSQL Patches In-Reply-To: <2438.1032213240@sss.pgh.pa.us> References: <2438.1032213240@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="=-Krh9/Bfx6cYwawledm9g" X-Mailer: Ximian Evolution 1.0.8 Date: 17 Sep 2002 11:19:32 -0400 Message-Id: <1032275973.32170.9.camel@jester> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/218 X-Sequence-Number: 5420 --=-Krh9/Bfx6cYwawledm9g Content-Type: text/plain Content-Transfer-Encoding: 7bit Fixed this problem and added regression tests in domain.sql. Also: - Changed header file order (alphabetical) - Changed to m = attnum - 1 in binary copy code for consistency On Mon, 2002-09-16 at 17:54, Tom Lane wrote: > In CVS tip: > > regression=# create domain nnint int not null; > CREATE DOMAIN > regression=# create table foo (f1 nnint); > CREATE TABLE > regression=# insert into foo values(null); > ERROR: Domain nnint does not allow NULL values -- okay > regression=# \copy foo from stdin > 123 > \N > \. > regression=# select * from foo; > f1 > ----- > 123 > -- not okay > (2 rows) > > regression=# create domain vc4 varchar(4); > CREATE DOMAIN > regression=# create table foot (f1 vc4); > CREATE TABLE > regression=# \copy foot from stdin > 1234567890 > \. > regression=# select * from foot; > f1 > ------------ > 1234567890 -- not okay > (1 row) > > > regards, tom lane > -- Rod Taylor --=-Krh9/Bfx6cYwawledm9g Content-Disposition: attachment; filename=domain_copy.patch Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; name=domain_copy.patch; charset=ISO-8859-1 ? src/test/regress/sql/.domain.sql.swp Index: src/backend/commands/copy.c =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/copy.c,v retrieving revision 1.171 diff -c -r1.171 copy.c *** src/backend/commands/copy.c 2002/09/04 20:31:14 1.171 --- src/backend/commands/copy.c 2002/09/17 15:17:07 *************** *** 28,37 **** #include "commands/copy.h" #include "commands/trigger.h" #include "executor/executor.h" - #include "rewrite/rewriteHandler.h" #include "libpq/libpq.h" #include "miscadmin.h" #include "parser/parse_relation.h" #include "tcop/pquery.h" #include "tcop/tcopprot.h" #include "utils/acl.h" --- 28,40 ---- #include "commands/copy.h" #include "commands/trigger.h" #include "executor/executor.h" #include "libpq/libpq.h" + #include "mb/pg_wchar.h" #include "miscadmin.h" + #include "nodes/makefuncs.h" + #include "parser/parse_coerce.h" #include "parser/parse_relation.h" + #include "rewrite/rewriteHandler.h" #include "tcop/pquery.h" #include "tcop/tcopprot.h" #include "utils/acl.h" *************** *** 39,45 **** #include "utils/relcache.h" #include "utils/lsyscache.h" #include "utils/syscache.h" - #include "mb/pg_wchar.h" =20=20 #define ISOCTAL(c) (((c) >=3D '0') && ((c) <=3D '7')) #define OCTVALUE(c) ((c) - '0') --- 42,47 ---- *************** *** 744,749 **** --- 746,753 ---- num_defaults; FmgrInfo *in_functions; Oid *elements; + bool *isDomain; + bool hasDomain =3D false; int i; List *cur; Oid in_func_oid; *************** *** 796,801 **** --- 800,806 ---- defexprs =3D (Node **) palloc(sizeof(Node *) * num_phys_attrs); in_functions =3D (FmgrInfo *) palloc(num_phys_attrs * sizeof(FmgrInfo)); elements =3D (Oid *) palloc(num_phys_attrs * sizeof(Oid)); + isDomain =3D (bool *) palloc(num_phys_attrs * sizeof(bool)); =20=20 for (i =3D 0; i < num_phys_attrs; i++) { *************** *** 803,808 **** --- 808,823 ---- if (attr[i]->attisdropped) continue; =20=20 + /* Test for the base type */ + if (getBaseType(attr[i]->atttypid) !=3D attr[i]->atttypid) + { + hasDomain =3D true; + isDomain[i] =3D true; + } + else + isDomain[i] =3D false; +=20 + /* Fetch the input function */ in_func_oid =3D (Oid) GetInputFunction(attr[i]->atttypid); fmgr_info(in_func_oid, &in_functions[i]); elements[i] =3D GetTypeElement(attr[i]->atttypid); *************** *** 1011,1016 **** --- 1026,1032 ---- foreach(cur, attnumlist) { int attnum =3D lfirsti(cur); + int m =3D attnum - 1; =20=20 i++; =20=20 *************** *** 1019,1027 **** elog(ERROR, "COPY BINARY: unexpected EOF"); if (fld_size =3D=3D 0) continue; /* it's NULL; nulls[attnum-1] already set */ ! if (fld_size !=3D attr[attnum - 1]->attlen) elog(ERROR, "COPY BINARY: sizeof(field %d) is %d, expected %d", ! i, (int) fld_size, (int) attr[attnum - 1]->attlen); if (fld_size =3D=3D -1) { /* varlena field */ --- 1035,1043 ---- elog(ERROR, "COPY BINARY: unexpected EOF"); if (fld_size =3D=3D 0) continue; /* it's NULL; nulls[attnum-1] already set */ ! if (fld_size !=3D attr[m]->attlen) elog(ERROR, "COPY BINARY: sizeof(field %d) is %d, expected %d", ! i, (int) fld_size, (int) attr[m]->attlen); if (fld_size =3D=3D -1) { /* varlena field */ *************** *** 1040,1048 **** fp); if (CopyGetEof(fp)) elog(ERROR, "COPY BINARY: unexpected EOF"); ! values[attnum - 1] =3D PointerGetDatum(varlena_ptr); } ! else if (!attr[attnum - 1]->attbyval) { /* fixed-length pass-by-reference */ Pointer refval_ptr; --- 1056,1064 ---- fp); if (CopyGetEof(fp)) elog(ERROR, "COPY BINARY: unexpected EOF"); ! values[m] =3D PointerGetDatum(varlena_ptr); } ! else if (!attr[m]->attbyval) { /* fixed-length pass-by-reference */ Pointer refval_ptr; *************** *** 1052,1058 **** CopyGetData(refval_ptr, fld_size, fp); if (CopyGetEof(fp)) elog(ERROR, "COPY BINARY: unexpected EOF"); ! values[attnum - 1] =3D PointerGetDatum(refval_ptr); } else { --- 1068,1074 ---- CopyGetData(refval_ptr, fld_size, fp); if (CopyGetEof(fp)) elog(ERROR, "COPY BINARY: unexpected EOF"); ! values[m] =3D PointerGetDatum(refval_ptr); } else { *************** *** 1067,1076 **** CopyGetData(&datumBuf, fld_size, fp); if (CopyGetEof(fp)) elog(ERROR, "COPY BINARY: unexpected EOF"); ! values[attnum - 1] =3D fetch_att(&datumBuf, true, fld_size); } =20=20 ! nulls[attnum - 1] =3D ' '; } } =20=20 --- 1083,1138 ---- CopyGetData(&datumBuf, fld_size, fp); if (CopyGetEof(fp)) elog(ERROR, "COPY BINARY: unexpected EOF"); ! values[m] =3D fetch_att(&datumBuf, true, fld_size); } +=20 + nulls[m] =3D ' '; + } + } +=20 + /* Deal with domains */ + if (hasDomain) + { + ParseState *pstate; + pstate =3D make_parsestate(NULL); +=20 + foreach(cur, attnumlist) + { + int attnum =3D lfirsti(cur); + int m =3D attnum - 1; +=20 + Const *con; + Node *node; + bool isNull =3D (nulls[m] =3D=3D 'n'); +=20 + /* This is not a domain, so lets skip it */ + if (!isDomain[m]) + continue; +=20 + /* + * This is a domain. As such, we must process it's input + * function and coerce_type_constraints. The simplest way + * of doing that is to allow coerce_type to accomplish its + * job from an unknown constant + */ +=20 + /* Create a constant */ + con =3D makeConst(attr[m]->atttypid, + attr[m]->attlen, + values[m], + isNull, + attr[m]->attbyval, + false, /* not a set */ + false); /* not coerced */ +=20 + /* Process constraints */ + node =3D coerce_type_constraints(pstate, (Node *) con, + attr[m]->atttypid, true); +=20 + values[m] =3D ExecEvalExpr(node, econtext, + &isNull, NULL); =20=20 ! nulls[m] =3D isNull ? 'n' : ' '; } } =20=20 Index: src/test/regress/expected/domain.out =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/domain.o= ut,v retrieving revision 1.9 diff -c -r1.9 domain.out *** src/test/regress/expected/domain.out 2002/08/31 22:10:48 1.9 --- src/test/regress/expected/domain.out 2002/09/17 15:17:08 *************** *** 33,44 **** INSERT INTO basictest values ('88', 'haha', 'short text', '123.12'); -- B= ad varchar ERROR: value too long for type character varying(5) INSERT INTO basictest values ('88', 'haha', 'short', '123.1212'); -- T= runcate numeric select * from basictest; testint4 | testtext | testvarchar | testnumeric=20 ----------+----------+-------------+------------- 88 | haha | short | 123.12 88 | haha | short | 123.12 ! (2 rows) =20=20 -- check that domains inherit operations from base types select testtext || testvarchar as concat, testnumeric + 42 as sum --- 33,50 ---- INSERT INTO basictest values ('88', 'haha', 'short text', '123.12'); -- B= ad varchar ERROR: value too long for type character varying(5) INSERT INTO basictest values ('88', 'haha', 'short', '123.1212'); -- T= runcate numeric + -- Test copy + COPY basictest (testvarchar) FROM stdin; -- fail + ERROR: copy: line 1, value too long for type character varying(5) + lost synchronization with server, resetting connection + COPY basictest (testvarchar) FROM stdin; select * from basictest; testint4 | testtext | testvarchar | testnumeric=20 ----------+----------+-------------+------------- 88 | haha | short | 123.12 88 | haha | short | 123.12 ! | | short |=20 ! (3 rows) =20=20 -- check that domains inherit operations from base types select testtext || testvarchar as concat, testnumeric + 42 as sum *************** *** 47,53 **** -----------+-------- hahashort | 165.12 hahashort | 165.12 ! (2 rows) =20=20 drop table basictest; drop domain domainvarchar restrict; --- 53,60 ---- -----------+-------- hahashort | 165.12 hahashort | 165.12 ! |=20=20=20=20=20=20=20 ! (3 rows) =20=20 drop table basictest; drop domain domainvarchar restrict; *************** *** 107,118 **** INSERT INTO nulltest values ('a', 'b', NULL, 'd'); ERROR: ExecInsert: Fail to add null value in not null attribute col3 INSERT INTO nulltest values ('a', 'b', 'c', NULL); -- Good select * from nulltest; col1 | col2 | col3 | col4=20 ------+------+------+------ a | b | c | d a | b | c |=20 ! (2 rows) =20=20 -- Test out coerced (casted) constraints SELECT cast('1' as dnotnull); --- 114,131 ---- INSERT INTO nulltest values ('a', 'b', NULL, 'd'); ERROR: ExecInsert: Fail to add null value in not null attribute col3 INSERT INTO nulltest values ('a', 'b', 'c', NULL); -- Good + -- Test copy + COPY nulltest FROM stdin; --fail + ERROR: copy: line 1, CopyFrom: Fail to add null value in not null attrib= ute col3 + lost synchronization with server, resetting connection + COPY nulltest FROM stdin; select * from nulltest; col1 | col2 | col3 | col4=20 ------+------+------+------ a | b | c | d + a | b | c |=20 a | b | c |=20 ! (3 rows) =20=20 -- Test out coerced (casted) constraints SELECT cast('1' as dnotnull); *************** *** 152,164 **** insert into defaulttest default values; insert into defaulttest default values; insert into defaulttest default values; select * from defaulttest; col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8=20=20 ------+------+------+------+------+------+------+------- 3 | 12 | 5 | 1 | 3 | 88 | 8000 | 12.12 3 | 12 | 5 | 2 | 3 | 88 | 8000 | 12.12 3 | 12 | 5 | 3 | 3 | 88 | 8000 | 12.12 ! (3 rows) =20=20 drop sequence ddef4_seq; drop table defaulttest; --- 165,180 ---- insert into defaulttest default values; insert into defaulttest default values; insert into defaulttest default values; + -- Test defaults with copy + COPY defaulttest(col5) FROM stdin; select * from defaulttest; col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8=20=20 ------+------+------+------+------+------+------+------- 3 | 12 | 5 | 1 | 3 | 88 | 8000 | 12.12 3 | 12 | 5 | 2 | 3 | 88 | 8000 | 12.12 3 | 12 | 5 | 3 | 3 | 88 | 8000 | 12.12 ! 3 | 12 | 5 | 4 | 42 | 88 | 8000 | 12.12 ! (4 rows) =20=20 drop sequence ddef4_seq; drop table defaulttest; Index: src/test/regress/sql/domain.sql =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/domain.sql,v retrieving revision 1.6 diff -c -r1.6 domain.sql *** src/test/regress/sql/domain.sql 2002/08/31 22:10:48 1.6 --- src/test/regress/sql/domain.sql 2002/09/17 15:17:08 *************** *** 35,40 **** --- 35,50 ---- INSERT INTO basictest values ('88', 'haha', 'short', '123.12'); -- G= ood INSERT INTO basictest values ('88', 'haha', 'short text', '123.12'); -- B= ad varchar INSERT INTO basictest values ('88', 'haha', 'short', '123.1212'); -- T= runcate numeric +=20 + -- Test copy + COPY basictest (testvarchar) FROM stdin; -- fail + notsoshorttext + \. +=20 + COPY basictest (testvarchar) FROM stdin; + short + \. +=20 select * from basictest; =20=20 -- check that domains inherit operations from base types *************** *** 84,89 **** --- 94,109 ---- INSERT INTO nulltest values ('a', NULL, 'c', 'd'); INSERT INTO nulltest values ('a', 'b', NULL, 'd'); INSERT INTO nulltest values ('a', 'b', 'c', NULL); -- Good +=20 + -- Test copy + COPY nulltest FROM stdin; --fail + a b \N d + \. +=20 + COPY nulltest FROM stdin; + a b c \N + \. +=20 select * from nulltest; =20=20 -- Test out coerced (casted) constraints *************** *** 119,124 **** --- 139,150 ---- insert into defaulttest default values; insert into defaulttest default values; insert into defaulttest default values; +=20 + -- Test defaults with copy + COPY defaulttest(col5) FROM stdin; + 42 + \. +=20 select * from defaulttest; =20=20 drop sequence ddef4_seq; --=-Krh9/Bfx6cYwawledm9g--