Tom Lane wrote:
>
> 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.
Correct. As far as I can see there is no partial unique constraints
defined within the standard, and there are check constraints, so...
Although, especially after this:
> 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.
Thank you for the clarification, it's easy to understand now why using
check constraints is a bad idea for the purpose I wanted to use them.
It's also easy to see why 'proper' way is using partial unique indexes.
Still, one has to wonder why there are no partial unique constraints
defined in SQL standard :)
Mario
