Re: Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?

Jasen Betts <jasen(at)xnet(dot)co(dot)nz> writes:
> On 2010-04-19, Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr> wrote:
>> The 'proper' way to do this (as suggested by earlier posts on this
>> mailing list) is to use partial UNIQUE indexes, but I have problem with
>> that too: indexes are not part of DDL (no matter that primary key
>> constraints and/or unique constraints use indexes to employ those
>> constraints), and as far as I know there is no 'partial unique
>> constraint' in SQL?

> huh?

I think what Mario is actually complaining about is that partial unique
indexes are not part of the SQL standard, and he wants a solution that
at least gives the illusion that it might be portable to some other
RDBMS in the future.

Unfortunately, an illusion is all it would be. Even presuming that the
other DBMS lets you run plpgsql-equivalent functions in CHECK
constraints, the whole approach is broken by concurrency considerations.
If you have two transactions simultaneously inserting rows that would be
valid given the prior state of the table, but it's *not* valid for them
both to be present, then a CHECK or trigger-based constraint is going to
fail, because neither transaction will see the other's uncommitted row.
At least that's how it works in Postgres. In some other DBMS it might
work differently, but you're right back up against the fact that your
solution is not portable.

Unique constraints (partial or otherwise) deal with the race-condition
problem by doing low-level things that aren't exposed at the SQL level.
So there's simply no way to get the equivalent behavior in pure standard
SQL.

>> And, wouldn't it be better to have CHECK constraints check the data
>> AFTER data-modification?

> no.

Indeed. The race condition is still there. CHECK is meant to handle
constraints on a row's value *in isolation*. If you try to use it to
enforce cross-row conditions, the project will certainly end badly.

regards, tom lane