[postmodern-devel] Inserting into related tables
I don't quite understand how to do inserts into related table. For example, we have table 'items' with field 'producer_id' and table 'producer' with 'producer_id' and 'producer_name' fields: ,---- | (deftable item () | ((producer-id :type (or integer db-null)") | ... ; Other fields | ) | (:auto-id id)) | | (postmodern:deftable producers () | ((producer-name :type (string 50))) | (:auto-id producer-id) | (:indices producer)) | | ;; Add unique index manually | (postmodern:execute | (:create-unique-index 'producers-name-uniq-idx | :on 'producers | :fields 'producer-name)) `---- When we add new item to the database, producer may exist or may be new. We have to check producer's existense and add it to table if required. The only safe way to do it seems to be ,---- | ;; We ignore error if name exists. But what about other errors? | (ignore-errors | (let ((id (postmodern:next-id 'producers))) | (postmodern:execute | (:insert-into table :set | 'producer-id id | 'producer-name name)))) | | ;; Now get ID to insert into items.producer-id. | (postmodern:query | (:select 'producer-id :from table :where (:= 'producer-name name)) | :single) `---- However, on each insertion we increment producers-producer-id-seq even if producer name already exists in the table. This is somewhat clumsy. And faulty query wrapped with ignore-errors doesn't work with transactions. Is there any other postmodern-way? -- Ivan Boldyrev XML -- new language of ML family.
Hi Ivan, Why don't you just start by looking for the record, and create a new record only if it is not found? Something like (untested): (defun get-producer-id (name) (or (query (:select 'id :from 'producer :where (:= 'name name))) On Jan 4, 2008 12:40 PM, Ivan Boldyrev <lispnik@gmail.com> wrote:
I don't quite understand how to do inserts into related table.
For example, we have table 'items' with field 'producer_id' and table 'producer' with 'producer_id' and 'producer_name' fields:
,---- | (deftable item () | ((producer-id :type (or integer db-null)") | ... ; Other fields | ) | (:auto-id id)) | | (postmodern:deftable producers () | ((producer-name :type (string 50))) | (:auto-id producer-id) | (:indices producer)) | | ;; Add unique index manually | (postmodern:execute | (:create-unique-index 'producers-name-uniq-idx | :on 'producers | :fields 'producer-name)) `----
When we add new item to the database, producer may exist or may be new. We have to check producer's existense and add it to table if required. The only safe way to do it seems to be
,---- | ;; We ignore error if name exists. But what about other errors? | (ignore-errors | (let ((id (postmodern:next-id 'producers))) | (postmodern:execute | (:insert-into table :set | 'producer-id id | 'producer-name name)))) | | ;; Now get ID to insert into items.producer-id. | (postmodern:query | (:select 'producer-id :from table :where (:= 'producer-name name)) | :single) `----
However, on each insertion we increment producers-producer-id-seq even if producer name already exists in the table. This is somewhat clumsy. And faulty query wrapped with ignore-errors doesn't work with transactions. Is there any other postmodern-way?
-- Ivan Boldyrev
XML -- new language of ML family. _______________________________________________ postmodern-devel mailing list postmodern-devel@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
Ugh, previous message got sent before I finished typing. Here we go again... (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). Cheers, Marijn
(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))))
(Actually, now that I think about it, this would probably not be thread-safe. I'll see if I can figure out a better approach.) Cheers, Marijn
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.
There is a race condition:
You are entirely right, of course. I asked about this on #postgresql on IRC (freenode), and they didn't give me a better answer than your first try. Wasting IDs is rather ugly -- though I'm not sure this would be a concrete problem. Using ignore-errors is probably bad. Each postmodern exception has a database-error-code accessor, which can be used to identify a specific type of errors, and let all others through. Hope that helps. If you come up with a better way, let me know. Cheers, Marijn
On 10074 day of my life Marijn Haverbeke wrote:
Hope that helps. If you come up with a better way, let me know.
I have googled for "postgres insert ignore" and found solution using SAVEPOINT and ROLLBACK TO: http://robbat2.livejournal.com/214267.html (The example is from PosgreSQL UPDATE documentation). On 10074 day of my life Marijn Haverbeke wrote:
Oh (and I'm really sending too many separate mails today) -- have you tried just using the producer names as their IDs?
It only solves wasted IDs problem, but no other, doesn't it? And joins on strings are slower than on ints (though I cannot say my application is so much performance-constrained). -- Ivan Boldyrev "Assembly of Japanese bicycle require great peace of mind."
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
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.
Oh (and I'm really sending too many separate mails today) -- have you tried just using the producer names as their IDs? This has worked quite well for me in the past (of course, if you have to add more information to producers you will still need the producer table anyway, but you do get rid of the useless ID numbers). Marijn
participants (2)
-
Ivan Boldyrev -
Marijn Haverbeke