Re: Checking number of entries - Mailing list pgsql-general

On Fri, 29 Sep 2000, Philip Warner wrote:

> At 01:53 29/09/00 -0400, Tom Lane wrote:
> >
> >The canonical example is a check like
> >    CHECK (mycol > (SELECT max(othertab.othercol) FROM othertab))
> >declared as a constraint on mytab.
>
> I would argue that a CHECK constraint only applies to the table on which it
> is defined - basically a contract to ensure that certain conditions apply
> to changes on that table.

The problem is that the spec seems to say that constraints
are checked either at end of transaction or statement
and doesn't seem to say anything about limiting which statements
(in fact, it says, "each SQL statement" for the immediate case),
and that if a constraint is not satisified an exception is raised.
It basically appears to be implying that all immediate constraints
must be satisfied at end of statement and all deferred ones at
end of transaction, regardless of what the statement or transaction
was.  I could be misreading the "Checking constraints" section
as well, though:
     4.10.1  Checking of constraints

      Every constraint is either deferrable or non-deferrable. Within
      a transaction, every constraint has a constraint mode; if a con-
      straint is non-deferrable, then its constraint mode is always im-
      mediate, otherwise it is either or immediate or deferred. Every
      constraint has an initial constraint mode that specifies the
      constraint mode for that constraint at the start of each SQL-
      transaction and immediately after definition of that constraint.
      If a constraint is deferrable, then its constraint mode may be
      changed (from immediate to deferred, or from deferred to immediate)
      by execution of a <set constraints mode statement>.

      The checking of a constraint depends on its constraint mode within
      the current SQL-transaction. If the constraint mode is immedi-
      ate, then the constraint is effectively checked at the end of
      each SQL-statement. If the constraint mode is deferred, then the
      constraint is effectively checked when the constraint mode is
      changed to immediate either explicitly by execution of a <set con-
      straints mode statement>, or implicitly at the end of the current
      SQL-transaction.

      When a constraint is checked other than at the end of an SQL-
      transaction, if it is not satisfied, then an exception condition
      is raised and the SQL-statement that caused the constraint to be
      checked has no effect other than entering the exception information
      into the diagnostics area. When a <commit statement> is executed,
      all constraints are effectively checked and, if any constraint
      is not satisfied, then an exception condition is raised and the
      transaction is terminated by an implicit <rollback statement>.


> Once you allow functions in constraints, you give up all hope of
> cross-compatibility, eg.

> Create Table tab1 (f1 integer);
> Create Function tot_of_tab1() returns integer
>      as 'select cast(sum(f1) as int4) from tab1' language 'sql';
> Alter Table tab1 add check(tot_of_tab1() > 0);
>
> zzz=# insert into tab1 values(1);
> INSERT 142380 1
> zzz=# insert into tab1 values(-10);
> INSERT 142381 1
> zzz=# select tot_of_tab1();
>  tot_of_tab1
> -------------
>           -9
> (1 row)
>
> zzz=# insert into tab1 values(-12);
> ERROR:  ExecAppend: rejected due to CHECK constraint $1
>
> This demonstrates the problem, and, I think, highlights a bug in the
> constraint checking code: the constraint should have failed on the second
> insert. Maybe the constraint is evaluate before the insert?

Yeah, my guess is that it checks the new value of the row before the
insert and so the select doesn't see the new row.  And yes, as far as I
can tell, this would be incorrect behavior.

And functions are a problem because they shouldn't give different
behavior.  One solution would be to take check constraints calling
functions that may check sql data (i.e. any functions without a certain
attribute) and treat them as a check after each statement, but that sounds
like it would suck.

The other thing is that if you really want a insert/update check only, you
can do that by using a trigger.