Re: Subqueries in Check() -- Still Intentionally Omitted?

On Tue, 2008-09-02 at 18:47 -0400, Alvaro Herrera wrote:
> The problem is that you have to rerun the query to verify that the CHECK
> condition still holds, whenever the table that the CHECK clause is
> checking changes. This is rather problematic, because we'd need to make
> the system aware of such reverse dependencies.

Even if you re-ran the query, how do you avoid the race condition?

For example:
CREATE TABLE foo(
...
CHECK ((SELECT COUNT(*) FROM foo) < 10)
);

If another transaction commits between the time you re-run the query and
the time you commit, the CHECK will be violated.

>From an arbitrary subquery in a CHECK, it's hard to determine what kind
of locking semantics might be necessary for inserting transactions.

I think this is precisely what triggers are for. You define the error
condition and the locking semantics in one procedure.

Regards,
Jeff Davis