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.