abnormal behaviour of a CHECK CONSTRAINT with a regular expression when a trigger is added - Mailing list pgsql-bugs

Hello,


I think I have found a bug in postgresql:

When I construct a table with a check constraint with a regular expressions=
, it works as expected.
But when I add a trigger to the table. The check does not work anymore in s=
ome circumstances. It seems like the same check is influenced/ changed by t=
he trigger.


First my environment:
SELECT version();
                                                         version
-----------------------------------------------------------------------
 PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 200=
31218 (Gentoo Linux 3.3.2-r5, propolice-3.3-7)
(1 row)


The only difference with the standard installation:

datestyle =3D 'german, dmy' (/var/lib/postgresql/data/postgresql.conf)


The table to test this bug?:

CREATE TABLE test_reg1(
date_user                VARCHAR(10)  CHECK (date_user ~ '^([0-9]{1,2}\\.)?([0=
-9]{1,2}\\.)?[0-9]{4}$|^unkown$'),
date                        DATE,
date_quality             INT CHECK (date_quality IN (1,2,3,4))
);


Some values I insert in the table, which should pass the CHECK constraint:

retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('unkown');
INSERT 205697 1
retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('1999');
INSERT 205698 1
retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('11.1999');
INSERT 205699 1
retrovirology=3D#


Some values I insert in the table, which should not pass the CHECK constrai=
nt:

retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('unk');
ERROR:  new row for relation "test_reg1" violates check constraint "test_re=
g1_date_user"
retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('19999');
ERROR:  new row for relation "test_reg1" violates check constraint "test_re=
g1_date_user"
retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('111.1999');
ERROR:  new row for relation "test_reg1" violates check constraint "test_re=
g1_date_user"
retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('11:1999');
ERROR:  new row for relation "test_reg1" violates check constraint "test_re=
g1_date_user"
retrovirology=3D#


This part does work as expected.

Now I will add a trigger to this table.

(The background of the trigger is to allow the user to insert incomplete da=
tes. The user can add the year, the year and the month or the complete date=
. A quality of the date is inserted in the "date_quality" column and a comp=
lete date is inserted in the "date" column.  The "date" column will later b=
e used to calculate stats, ...)


CREATE OR REPLACE FUNCTION function_test_reg1 ()
RETURNS trigger
AS 'BEGIN

IF new.date_user IS NOT NULL THEN
    IF new.date_user =3D ''unkown'' THEN
    new.date_quality :=3D 4;
    END IF;
    IF new.date_user ~ ''^([0-9]{1,2}\\.){1}([0-9]{1,2}\\.){1}[0-9]{4}$'' T=
HEN
    new.date :=3D new.date_user;
    new.date_quality :=3D 1;
    END IF;
    IF new.date_user ~ ''^([0-9]{1,2}\\.){1}[0-9]{4}$'' THEN
    new.date :=3D ''15.'' || new.date_user;
    new.date_quality :=3D 2;
    END IF;
    IF new.date_user ~ ''^[0-9]{4}$'' THEN
    new.date :=3D ''15.06.'' || new.date_user;
    new.date_quality :=3D 3;
    END IF;
END IF;

RETURN new;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER trigger_test_reg1
                          BEFORE INSERT OR UPDATE
                          ON test_reg1 FOR EACH ROW
                          EXECUTE PROCEDURE function_test_reg1();


Now I will insert the same values as before, the first part should be inser=
ted without a problem.

retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('unkown');
INSERT 206770 1
retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('1999');
INSERT 206771 1
retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('11.1999');
INSERT 206772 1


retrovirology=3D# select * from test_reg1;
 date_user |    date    | date_quality
-----------+------------+--------------
 unkown    |            |            4
 1999      | 15.06.1999 |            3
 11.1999   | 15.11.1999 |            2
(3 rows)


You can see, that the trigger works as expected.


But now, I insert the values, which shouldn't pass the CHECK CONSTRAINT:

retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('unk');
ERROR:  new row for relation "test_reg1" violates check constraint "test_re=
g1_date_user"
retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('19999');
ERROR:  new row for relation "test_reg1" violates check constraint "test_re=
g1_date_user"

This seems right, the values are rejected by the CHECK CONSTRAINT. Now I in=
sert the other "wrong values":

retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('111.1999');
ERROR:  invalid input syntax for type date: "111.1999"
CONTEXT:  PL/pgSQL function "function_test_reg1" line 8 at assignment
retrovirology=3D# INSERT INTO test_reg1 (date_user) VALUES ('11:1999');
ERROR:  invalid input syntax for type date: "15.11:1999"
CONTEXT:  PL/pgSQL function "function_test_reg1" line 12 at assignment
retrovirology=3D#

This values do pass NOW the CHECK CONSTRAINT ???? and are injected in the t=
rigger function. Normally the trigger shouldn't m have an influence an the =
CHECK CONSTRAINT.
Very strange?

Of course the trigger function now rejects the values, because they don't c=
onstitute a valid date.


Best regards,
Daniel Struck


--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu