Обсуждение: insert locking issue for PG 9.0
Our application has a table that looks like: create table jobs ( id int, first boolean ); What we need is for the app to be able to shove data into jobs with an assigned id, and guarantee that first is only truefor one id. In other words, we could easily enforce what we want by creating a unique index on jobs (id) where first=true. The problem comes in how we deal with exceptions (and there will be many). We'd like to just have the database say, "oh,hey, there's already a row with this id; I guess I'll make first=false". If we were to wrap inserts to this table intoa stored proc it seems like that would be easy enough to handle those exceptions and try to re-insert with first=false,except that this is Rails and calling a stored proc instead of doing inserts will be difficult in this case.So that's pretty much out, if we can avoid it. We could use rules to call that procedure INSTEAD OF inserts. That seems like it should work, but.... rules. Also, it requiresus to keep an index that we don't need for anything else. It would be nice if there was a way to have a before trigger function on jobs that would twiddle first as needed, but we'vebeen unable to find a way that doesn't involve a race condition or lock escalation deadlocks. Advisory locks are releasedbefore the new row is visible, and "normal" locks stronger than what INSERT acquires leads to lock escalation deadlocks. We've considered using rules to acquire the strict lock, then a before trigger to do the twiddling, but then we're back tousing rules. Does anybody have any elegant ideas to make this happen?
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ben Chobot Sent: Wednesday, November 16, 2011 5:48 PM To: pgsql-general General Subject: [GENERAL] insert locking issue for PG 9.0 Our application has a table that looks like: create table jobs ( id int, first boolean ); What we need is for the app to be able to shove data into jobs with an assigned id, and guarantee that first is only true for one id. In other words, we could easily enforce what we want by creating a unique index on jobs (id) where first=true. ----------------------------------------- Thinking outside the box... Do records ever get inserted with "first = FALSE" directly or is the only way for a record to have a false "first" is because another record already exists? Can your process re-execute the record insertion if a UNIQUE INDEX failure occurs? My suggestion is to forget dealing with "first" and instead create a unique index on (id, job_time). The earliest record for a given id will always be "first". This is likely to be fast enough but if not you could always setup the "first" field and update it later - and then use it as part of a partial index. The first question needs to be "No" so that you do not have a situation where the first inserted record is one that should not be "TRUE"; though by using three-valued logic you could work around this requirement. The second question needs to be "Yes" in the rare circumstance that two IDs are inserted at exactly the same milli/nano-second. So CREATE TABLE jobs (id int, job_time timestamptz); CREATE UNIQUE INDEX (id, job_time [need explicit timezone to avoid mutability; or convert to some kind of epoch]); CREATE VIEW first_job AS SELECT j1.id FROM jobs j1 WHERE j1.record_creation_ts = (SELECT job_time FROM jobs j2 WHERE j2.id = j1.id ORDER BY job_time ASC LIMIT 1) ; --syntax not checked but this is the idea... The main thing you need to decide with this approach is how you calculate the timestamp and, if two timestamps are equal, how to resolve the difference. Simply adding a few time units and re-inserting should resolve the problem in simplistic cases. David J.