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.
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
into a 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 requires us 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've been unable to find a way that
doesn't involve a race condition or lock escalation deadlocks. Advisory locks
are released before 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 to using rules.
Does anybody have any elegant ideas to make this happen?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
