abnormal behaviour of a CHECK CONSTRAINT with a regular expression when a trigger is added

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 some circumstances. It seems like the same check is influenced/ changed by the trigger.

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

The only difference with the standard installation:

datestyle = '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=# INSERT INTO test_reg1 (date_user) VALUES ('unkown');
INSERT 205697 1
retrovirology=# INSERT INTO test_reg1 (date_user) VALUES ('1999');
INSERT 205698 1
retrovirology=# INSERT INTO test_reg1 (date_user) VALUES ('11.1999');
INSERT 205699 1
retrovirology=#

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

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

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 dates. 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 complete date is inserted in the "date" column. The "date" column will later be 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 = ''unkown'' THEN
new.date_quality := 4;
END IF;
IF new.date_user ~ ''^([0-9]{1,2}\\.){1}([0-9]{1,2}\\.){1}[0-9]{4}$'' THEN
new.date := new.date_user;
new.date_quality := 1;
END IF;
IF new.date_user ~ ''^([0-9]{1,2}\\.){1}[0-9]{4}$'' THEN
new.date := ''15.'' || new.date_user;
new.date_quality := 2;
END IF;
IF new.date_user ~ ''^[0-9]{4}$'' THEN
new.date := ''15.06.'' || new.date_user;
new.date_quality := 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 inserted without a problem.

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

retrovirology=# 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=# INSERT INTO test_reg1 (date_user) VALUES ('unk');
ERROR: new row for relation "test_reg1" violates check constraint "test_reg1_date_user"
retrovirology=# INSERT INTO test_reg1 (date_user) VALUES ('19999');
ERROR: new row for relation "test_reg1" violates check constraint "test_reg1_date_user"

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

retrovirology=# 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=# 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=#

This values do pass NOW the CHECK CONSTRAINT ???? and are injected in the trigger 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 constitute a valid date.

Best regards,
Daniel Struck

--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax: +352-44116113
web: http://www.retrovirology.lu
e-mail: struck(dot)d(at)retrovirology(dot)lu