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...
SERIALIZABLE
isolation level. – Craig Ringer Oct 16 '12 at 2:28BEGIN ISOLATION LEVEL SERIALIZABLE
inside a 2 psql shells). – ocharles Oct 16 '12 at 10:00