On Sun, Oct 09, 2005 at 09:32:55PM -0700, Miles Keaton wrote:
> Solved!
>
> CREATE OR REPLACE FUNCTION non_duplicated_isbn() RETURNS trigger AS $function$
> DECLARE
> rez RECORD;
> BEGIN
> SELECT INTO rez * FROM books WHERE isbn=NEW.isbn AND name != NEW.name;
> IF FOUND THEN
> RAISE EXCEPTION 'isbn % already used for different book name: %',
> NEW.isbn, rez.name;
> END IF;
> RETURN NEW;
> END;
> $function$ LANGUAGE plpgsql;
>
> CREATE TRIGGER ndi BEFORE INSERT OR UPDATE ON books FOR EACH ROW
> EXECUTE PROCEDURE non_duplicated_isbn();
This solution doesn't allow for concurrency. Suppose no records
for ISBN 1234 exist in the table, then two concurrent transactions
try to insert (1234,Red Roses) and (1234,Green Glasses), respectively.
Both will find no conflicting records, so both inserts will succeed.
In other words, you have a race condition because transaction B
will fail only if transaction A happens to commit its change before
transaction B makes its check. To prevent this problem you'll need
a locking mechanism, which can hurt performance if it locks the
entire table.
Using a foreign key reference seems like a better solution. You
could probably implement this without changing the structure of the
existing table aside from adding the foreign key constraint and
perhaps a trigger to automatically add records to the other table,
so you shouldn't need any application changes either. Would a
change like that still be out of the question?
--
Michael Fuhr