On Wed, Sep 27, 2006 at 02:17:23PM -0800, Matthew Schumacher wrote: > Jim C. Nasby wrote: > > > > It can cause a race if another process could be performing those same > > inserts or updates at the same time. > > There are inserts and updates running all of the time, but never the > same data. I'm not sure how I can get around this since the queries are > coming from my radius system which is not able to queue this stuff up > because it waits for a successful query before returning an OK packet > back to the client. > > > > > I know the UPDATE case can certainly cause a race. 2 connections try to > > update, both hit NOT FOUND, both try to insert... only one will get to > > commit. > > Why is that? Doesn't the first update lock the row causing the second > one to wait, then the second one stomps on the row allowing both to > commit? I must be confused.... What if there's no row to update? Process A Process B UPDATE .. NOT FOUND UPDATE .. NOT FOUND INSERT INSERT blocks COMMIT UNIQUE_VIOLATION That's assuming that there's a unique index. If there isn't one, you'd get duplicate records. > > I think that the UNIQUE_VIOLATION case should be safe, since a second > > inserter should block if there's another insert that's waiting to > > commit. > > Are you saying that inserts inside of an EXCEPTION block, but normal > inserts don't? No... if there's a unique index, a second INSERT attempting to create a duplicate record will block until the first INSERT etiher commits or rollsback. > > DELETEs are something else to think about for both cases. > > I only do one delete and that is every night when I move the data to the > primary table and remove that days worth of data from the tmp table. > This is done at non-peak times with a vacuum, so I think I'm good here. Except that you might still have someone fire off that function while the delete's running, or vice-versa. So there could be a race condition (I haven't thought enough about what race conditions that could cause). > > If you're certain that only one process will be performing DML on those > > tables at any given time, then what you have is safe. But if that's the > > case, I'm thinking you should be able to group things into chunks, which > > should be more efficient. > > Yea, I wish I could, but I really need to do one at a time because of > how radius waits for a successful query before telling the access server > all is well. If the query fails, the access server won't get the 'OK' > packet and will send the data to the secondary radius system where it > gets queued. In that case, the key is to do the absolute smallest amount of work possible as part of that transaction. Ideally, you would only insert a record into a queue table somewhere, and then periodically process records out of that table in batches. > Do you know of a way to see what is going on with the locking system > other than "select * from pg_locks"? I can't ever seem to catch the > system when queries start to lag. No. Your best bet is to open two psql sessions and step through things in different combinations (make sure and do this in transactions). -- Jim Nasby jim@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)