On 10074 day of my life Marijn Haverbeke wrote:
(defun get-producer-id (name) (or (query (:select 'id :from 'producer :where (:= 'name name))) (let ((new-producer (make-instance 'producer :name name)) (insert-dao new-producer) (get-id new-producer))))
Does that work (it is probably full of typos and mistakes, but you get the idea).
There is a race condition:
Session A Session B
(:select ... "AA")) => nil (:select ... "AA")) => nil
(sequence-next ...) => 10 (sequence-next ...) => 11
(:insert 'producer :set 'id 10 (:insert producer :set id 11 'name "AA") name "AA")
So what is ID of "AA": 10 or 11? And if UNIQUE index is created, one of sessions will fail. If no UNIQUE index is created, we have two lines of "AA" in 'producers'.
Perhaps, I misunderstand something, I'm quite new to SQL, transactons and so on.
MySQL has REPLACE and INSERT IGNORE extensions. Perhaps, the only sane way of doing it in PostgreSQL is using locks, but they are another can of worms...