Re: abnormal behaviour of a CHECK CONSTRAINT with a regular

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

Daniel Struck <struck(dot)d(at)retrovirology(dot)lu> writes:
> I think I have found a bug in postgresql:

I don't see any bug here; it's just that CHECK constraints are applied
after any BEFORE triggers are run. Since the triggers could change the
data to be inserted, the reverse order would be insecure.

regards, tom lane

> I don't see any bug here; it's just that CHECK constraints are applied
> after any BEFORE triggers are run. Since the triggers could change the
> data to be inserted, the reverse order would be insecure.

Ups, it did make some false assumptions.

Thank you for the clarification.

If I understood now correctly, first the type, then the trigger and last the check constraint is checked.

This makes it also impossible to change the type of a value by a trigger?

Say I would want to change the input of a user to a date, this wouldn't be possible, because in first instance the type is not a date.

Here is an example I tried out:

CREATE TABLE test(datum DATE);

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

new.datum := (''15.06.'' || new.datum)::date;

RETURN new;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER trigger_test
BEFORE INSERT OR UPDATE
ON test FOR EACH ROW
EXECUTE PROCEDURE function_test();

INSERT INTO test VALUES('2003');

Best regards,
Daniel

--
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

On Tue, 10 Feb 2004, Daniel Struck wrote:

> 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.

Wrong order. Before triggers happen before the table check constraint is
checked (since the triggers can change the data). IIRC, you can currently
have a constraint that is checked before that currently by making the
column type be a domain that has the constraint since the value gets
coerced to the column type (and checked) when making the row value for the
trigger.

Daniel Struck <struck(dot)d(at)retrovirology(dot)lu> writes:
> This makes it also impossible to change the type of a value by a trigger?

Correct. It is annoying in some cases, but if the input to the trigger
isn't of the column datatype, then what type is it? It's hard to see
how that could work in general.

If you want, say, a varchar length constraint to be checked only after
the trigger runs, I'd counsel declaring the column as plain text and
writing the length test as a CHECK constraint.

regards, tom lane

> Correct. It is annoying in some cases, but if the input to the trigger
> isn't of the column datatype, then what type is it? It's hard to see
> how that could work in general.
>
> If you want, say, a varchar length constraint to be checked only after
> the trigger runs, I'd counsel declaring the column as plain text and
> writing the length test as a CHECK constraint.

I had just an example, where it would have been convenient, if one could change the type.

Let's say you want to give the users the possibility to enter incomplete dates (year, year&month or the complete date). Indeed you could not insert this information as it is. But later on for stats you would like to be able to treat the column as a date.

I have implemented it by storing the user information in a varchar column and inserting the whole date in another column.
(if the user only provides the year, the date is inserted as 16.05.year,...).

Kind 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