In a recent discussion on IRC, some anomalies concerning CHECK
constraints were brought to light, in that in some cases they do not
guarantee that the data within the table satisfies them. For example
(against 7.4.1),

test=# create table foo (
test(# foo_stamp timestamptz not null,
test(# foo_int integer not null,
test(# check (foo_stamp > current_timestamp)
test(# );
CREATE TABLE
test=#
test=# insert into foo values (now() + '20 seconds'::interval, 3);
INSERT 647207 1
test=# \d foo;
Table "public.foo"
Column | Type | Modifiers
-----------+--------------------------+-----------
foo_stamp | timestamp with time zone | not null
foo_int | integer | not null
Check constraints:
"$1" CHECK (foo_stamp > 'now'::text::timestamp(6) with time zone)

test=# select foo_stamp, foo_int, now() as now_stamp from foo;
foo_stamp | foo_int | now_stamp
------------------------------+---------+------------------------------
2004-03-01 21:38:35.54846+09 | 3 | 2004-03-01 21:39:02.91338+09
(1 row)

test=# update foo set foo_int = 4;
ERROR: new row for relation "foo" violates check constraint "$1"
test=# insert into foo values (now() - '10 seconds'::interval,3);
ERROR: new row for relation "foo" violates check constraint "$1"

The CHECK constraint ( foo_stamp > current_timestamp ) is only checked
on INSERT and UPDATE (and fails appropriately in such cases). In the
case of the SELECT statement, it's clear that the data within the table
no longer satisfies the CHECK constraint.

Another example, using an admittedly strange CHECK constraint:

test=# create table f (a float, check (a < random()));
CREATE TABLE
test=# insert into f values (0.02);
INSERT 647211 1
test=# insert into f values (0.03);
INSERT 647212 1
test=# insert into f values (0.04);
INSERT 647213 1
test=# insert into f values (0.99);
ERROR: new row for relation "f" violates check constraint "$1"
test=# select * from f;
a
------
0.02
0.03
0.04
(3 rows)

While it may make sense under certain conditions to test against a
random number at a specific time, what does it mean for the data to
always be less than random(), as the CHECK constraint implies?

In both cases, the CHECK constraint uses a function that is stable or
volatile. It was suggested that functions used in CHECK constraints be
restricted to immutable, as are functions used in indexes on
expressions, at least until PostgreSQL can guarantee that the CHECK
constraints will hold at times beyond INSERT and UPDATE.

Similar functionality can be attained using ON INSERT and ON UPDATE
trigger functions, which in the case of stable or volatile functions is
a more accurate description of what actually is protected.

If functions such as CURRENT_TIMESTAMP are allowed in CHECK constraints
and they are evaluated on SELECT as well as on INSERT or UPDATE,
another thing to consider is what the proper behavior would be when
rows are found to be in violation of the constraint. Should the
offending rows be deleted?

Michael Glaesemann
grzm myrealbox com

Search Discussions

  • Tom Lane at Mar 2, 2004 at 1:28 am

    Michael Glaesemann writes:
    In both cases, the CHECK constraint uses a function that is stable or
    volatile. It was suggested that functions used in CHECK constraints be
    restricted to immutable,
    This seems reasonable to me. I'm a bit surprised we do not have such a
    check already.

    Of course, a user could easily get into the sort of situation you
    describe anyway, just by lying about the volatility labeling of a
    user-defined function. But at least we could say it was his fault
    then ;-)

    regards, tom lane
  • Bruno Wolff III at Mar 2, 2004 at 1:41 am

    On Mon, Mar 01, 2004 at 20:28:02 -0500, Tom Lane wrote:
    Michael Glaesemann <grzm@myrealbox.com> writes:
    In both cases, the CHECK constraint uses a function that is stable or
    volatile. It was suggested that functions used in CHECK constraints be
    restricted to immutable,
    This seems reasonable to me. I'm a bit surprised we do not have such a
    check already.
    There may be times you want to do this. For example you may want a timestamp
    to be in the past. In this case as long as it was in the past when the
    data was entered it will continue to be in the past (barring someone
    resetting the system time). This is something someone might actually
    check unlike comparing to random numbers.

    I think just noting that check constraints are only checked on inserts
    and updates and that this means that check constraints using volatile
    or stable functions need to be well thought out.
  • Rod Taylor at Mar 2, 2004 at 2:24 am

    On Mon, 2004-03-01 at 20:43, Bruno Wolff III wrote:
    On Mon, Mar 01, 2004 at 20:28:02 -0500,
    Tom Lane wrote:
    Michael Glaesemann <grzm@myrealbox.com> writes:
    In both cases, the CHECK constraint uses a function that is stable or
    volatile. It was suggested that functions used in CHECK constraints be
    restricted to immutable,
    This seems reasonable to me. I'm a bit surprised we do not have such a
    check already.
    There may be times you want to do this. For example you may want a timestamp
    to be in the past. In this case as long as it was in the past when the
    Agreed that this is useful behaviour, but a trigger is usually a better
    mechanism for confirming such data as you really only want to check it
    when the value is changed.
  • Tom Lane at Mar 2, 2004 at 3:36 am

    Rod Taylor writes:
    On Mon, 2004-03-01 at 20:43, Bruno Wolff III wrote:
    Tom Lane wrote:
    Michael Glaesemann <grzm@myrealbox.com> writes:
    In both cases, the CHECK constraint uses a function that is stable or
    volatile. It was suggested that functions used in CHECK constraints be
    restricted to immutable,
    This seems reasonable to me. I'm a bit surprised we do not have such a
    check already.
    There may be times you want to do this. For example you may want a timestamp
    to be in the past. In this case as long as it was in the past when the
    Agreed that this is useful behaviour, but a trigger is usually a better
    mechanism for confirming such data as you really only want to check it
    when the value is changed.
    Yes. I was just arguing in a different thread that triggers are the
    right way to express one-time checks. A constraint notionally expresses
    an always-true condition. (The SQL spec defines this more formally as a
    condition that must hold at the completion of every statement or every
    transaction, depending on the "deferrability" property of the
    constraint.) We presently support only constraints that are of a form
    that need only be checked at row insert or update time. It would be
    inconsistent with the spec to consider that part of the fundamental
    semantics of check constraints, though --- it's just an implementation
    restriction. Someday we might want to remove that restriction.

    Requiring CHECK functions to be immutable is consistent with the
    existing implementation restriction. Misusing them in the way Bruno
    suggests is a cute trick, but I think we have to consider it to be
    gaming the implementation, not a supported way to do things.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 2, '04 at 12:53a
activeMar 2, '04 at 3:36a
posts5
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase