[postmodern-devel] Inserting into related tables

[Ivan Boldyrev]

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...

-- 
Ivan Boldyrev

                       Perl is a language where 2 x 2 is not equal to 4.