[postmodern-devel] Inserting into related tables

[Ivan Boldyrev]

On 10074 day of my life Marijn Haverbeke wrote:
>> SAVEPOINT and ROLLBACK TO:
>> http://robbat2.livejournal.com/214267.html
> Does that actually solve the wasted id problem?

No, "a nextval operation is never rolled back".

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

It was original design (created by some student).  However, I need to do
queries like "get all producers like 'abc%'" and "get all producers" for
autocompletion and input validation.  SELECT DISTINCT works, but look at
the timings:

CL-USER> (time (dotimes (i 100) (postmodern:query (:select (:distinct 'producer) :from 'catalog))))
Evaluation took:
  3.353 seconds of real time
  0.056004 seconds of user run time
  0.016001 seconds of system run time
  0 calls to %EVAL
  0 page faults and
  417,472 bytes consed.
NIL
CL-USER> (time (dotimes (i 100) (postmodern:query (:select 'producer :from 'producers))))
Evaluation took:
  0.142 seconds of real time
  0.032002 seconds of user run time
  0.008001 seconds of system run time
  0 calls to %EVAL
  0 page faults and
  273,816 bytes consed.
NIL

(Indicies are present).  Well, for my application such difference is not
really important, but what if it will in next project?

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

Thanks, I will note.  But so far SAVEPOINT solution looks best for me.

-- 
Ivan Boldyrev

                                   Violets are red
                                   Roses are blue
                                   It's amazing what DNA splicing can do.