Thread: BUG #2971: 8.1.7/8.2.2 break constraint checking for 'update'

The following bug has been logged online:

Bug reference:      2971
Logged by:          Martin Pitt
Email address:      martin@piware.de
PostgreSQL version: 8.2.2
Operating system:   Linux
Description:        8.1.7/8.2.2 break constraint checking for 'update'
Details:

Originally reported on https://launchpad.net/bugs/83505:

db> create table foo (bar VARCHAR(20) NOT NULL check (bar in
('FOO','BAR')));
CREATE TABLE

db> insert into foo (bar) values ('FOO');
INSERT 0 1

db> update foo set bar = 'BAR';
ERROR: attribute 1 has wrong type
DETAIL: Table has type character varying, but query expects character
varying.

This worked in the previous versions (8.2.1/8.1.6). I checked that it does
not affect 7.4.16.

Thank you!

"Martin Pitt" <martin@piware.de> writes:
> db> create table foo (bar VARCHAR(20) NOT NULL check (bar in
> ('FOO','BAR')));
> CREATE TABLE

> db> insert into foo (bar) values ('FOO');
> INSERT 0 1

> db> update foo set bar = 'BAR';
> ERROR: attribute 1 has wrong type
> DETAIL: Table has type character varying, but query expects character varying.

Sigh.  The trouble with security patches is that by nature they can't
get very wide testing :-(.  I think we shall have to do something like
the attached.  Arguably this problem is exposing bugs elsewhere in the
system, but for now ExecEvalVar() is going to have to be less
aggressive.

            regards, tom lane

Index: execQual.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/executor/execQual.c,v
retrieving revision 1.212
diff -c -r1.212 execQual.c
*** execQual.c    3 Feb 2007 14:06:53 -0000    1.212
--- execQual.c    6 Feb 2007 16:14:08 -0000
***************
*** 488,495 ****
           * Note: we allow a reference to a dropped attribute.  slot_getattr
           * will force a NULL result in such cases.
           *
!          * Note: we check typmod, but allow the case that the Var has
!          * unspecified typmod while the column has a specific typmod.
           */
          if (attnum > 0)
          {
--- 488,498 ----
           * Note: we allow a reference to a dropped attribute.  slot_getattr
           * will force a NULL result in such cases.
           *
!          * Note: ideally we'd check typmod as well as typid, but that seems
!          * impractical at the moment: in many cases the tupdesc will have
!          * been generated by ExecTypeFromTL(), and that can't guarantee to
!          * generate an accurate typmod in all cases, because some expression
!          * node types don't carry typmod.
           */
          if (attnum > 0)
          {
***************
*** 505,513 ****
              /* can't check type if dropped, since atttypid is probably 0 */
              if (!attr->attisdropped)
              {
!                 if (variable->vartype != attr->atttypid ||
!                     (variable->vartypmod != attr->atttypmod &&
!                      variable->vartypmod != -1))
                      ereport(ERROR,
                              (errmsg("attribute %d has wrong type", attnum),
                               errdetail("Table has type %s, but query expects %s.",
--- 508,514 ----
              /* can't check type if dropped, since atttypid is probably 0 */
              if (!attr->attisdropped)
              {
!                 if (variable->vartype != attr->atttypid)
                      ereport(ERROR,
                              (errmsg("attribute %d has wrong type", attnum),
                               errdetail("Table has type %s, but query expects %s.",

Hi Tom,

Tom Lane [2007-02-06 11:25 -0500]:
> "Martin Pitt" <martin@piware.de> writes:
> > db> create table foo (bar VARCHAR(20) NOT NULL check (bar in
> > ('FOO','BAR')));
> > CREATE TABLE
>=20
> > db> insert into foo (bar) values ('FOO');
> > INSERT 0 1
>=20
> > db> update foo set bar =3D 'BAR';
> > ERROR: attribute 1 has wrong type
> > DETAIL: Table has type character varying, but query expects character v=
arying.
>=20
> Sigh.  The trouble with security patches is that by nature they can't
> get very wide testing :-(.  I think we shall have to do something like
> the attached.  Arguably this problem is exposing bugs elsewhere in the
> system, but for now ExecEvalVar() is going to have to be less
> aggressive.

I confirm that this patch fixes the problem.

Thanks for the fast reaction!

Martin
--=20
Martin Pitt        http://www.piware.de
Ubuntu Developer   http://www.ubuntu.com
Debian Developer   http://www.debian.org