[postmodern-devel] Inserting into related tables

[Marijn Haverbeke]

> I have googled for "postgres insert ignore" and found solution using
> SAVEPOINT and ROLLBACK TO:
> http://robbat2.livejournal.com/214267.html

Does that actually solve the wasted id problem?

> > have you tried just using the producer names as their IDs?
>
> It only solves wasted IDs problem, but no other, doesn't it?

The idea was that you wouldn't need the table that maps names to ids
at all, so this whole problem goes away. I'm not sure that joining on
strings is really that much slowing in Postgres, but I haven't got any
benchmarks to really find out.

Anyway, I also came up with this monstrosity, it doesn't solve the
wasted id problem, but take a look:

(let ((name "dummy") (id (generate-next-id)))
  (execute (:insert-into 'producer (:except (:select name id) (:select
'* :from 'producer :where (:= 'name name))))))

It uses EXCEPT to remove the new row from the set of inserted rows if
it already exists. If you want to use it, you'll have to pull in a
patch I just added to Postmodern because my :insert-into operator was
a bit too picky about the input it allowed.

Cheers,
Marijn