Jasen Betts <ja...@xnet.co.nz> writes: > On 2010-04-19, Mario Splivalo <mario.spliv...@megafon.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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql