Thread: Conditional Adding to a Table
Greetings, I need to check a table to see if an item already exist in the table. It if does not, then I want to add it. Unfortunately, multiple processes (Solaris system) can be running at the same time. Sometimes, one process checks to see if an item exist and it does not. At the same time another process checks to see if the same item exist and it does not. Then both, add the item to the table. This is, of course, not what I want. I cannot seem to figure out how to do a conditional test to do something like this. If I could, then the whole operation could fit in one transaction with locking. Any ideas? Thanks, Glenn
just add a unique constraint on the things you want to be unique... On Wed, 10 Oct 2001 14:37:09 -0600, Glenn Sullivan <glenn.sullivan@varianinc.com> wrote: > Greetings, > > I need to check a table to see if an item already exist in the table. > It if does not, then I want to add it. Unfortunately, multiple processes > (Solaris system) can be running at the same time. Sometimes, one process > checks to see if an item exist and it does not. At the same time another > process checks to see if the same item exist and it does not. Then both, > add the item to the table. This is, of course, not what I want. > > I cannot seem to figure out how to do a conditional test to do something > like this. If I could, then the whole operation could fit in one transaction > with locking. > > Any ideas? > > Thanks, > Glenn > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
You could use a LOCK TABLE statement to gain an exclusive lock, but a better approach is probably to put a unique constrainton whatever your check is and then just attempt the INSERT. Hope this helps a little.... Allan. Glenn Sullivan wrote: > Greetings, > > I need to check a table to see if an item already exist in the table. > It if does not, then I want to add it. Unfortunately, multiple processes > (Solaris system) can be running at the same time. Sometimes, one process > checks to see if an item exist and it does not. At the same time another > process checks to see if the same item exist and it does not. Then both, > add the item to the table. This is, of course, not what I want. > > I cannot seem to figure out how to do a conditional test to do something > like this. If I could, then the whole operation could fit in one transaction > with locking. > > Any ideas? > > Thanks, > Glenn > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)