I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following.
We have a table called
artist with 2 columns,
(name, id) where
name is the unique and
id is a serial primary key. It's a contrived example, but it illustrates my problem:
SESSION A SESSION B 1. SELECT id FROM artist WHERE name = 'Bob'; 2. INSERT INTO artist (name) VALUES ('Bob') 3. INSERT INTO artist (name) VALUES ('Bob') 4. code that users 'Bob' (e.g., a FK to Bob's ID) 5. ??? Bob already exists, but we can't find it 4. COMMIT
Session B begins by trying to find an
artist called Bob, which fails. However, Session A then creates Bob. Session B tries to insert an artist called Bob, which fails as it violates the primary key. But here's the bit I don't get -- if I change operation 3 to be a select on
artist the table is still empty! This is because I'm using the serializable isolation level, but how can I handle this case?
It seems the only option I have is to abort the entire transaction and try again. If this is the case, should I throw my own 'could not serialize' exception, indicating the application should retry? I already wanted this 'find-or-insert' in a plpgsql function, where I would
INSERT, and if that failed
SELECT but it seems impossible to find the conflicting row...