Hi,
I'm trying to implement some stored procedures but I'm having some
doubts, and I'd like to ask you if I'm doing well or not.
Here's an example of what I'm doing: I have a table like
create table (
id serial,
description text not null,
active boolean default true);
What I want to do is a function inserting a new item into the table
ensuring that there is only one record in the table having a particular
description and at the same time the active field set to true (it might
seem stupid, but the application requires it).
My first solution was a function executing a select on the table
checking for a record with the passed description and the active field
set to true. If a record is found, then the function fails.
This function works, but I don't think it's thread safe, since two
functions could be executed at the same time, so that they pass the test
and insert twice the record. To solve the problem, I tried to put a
constraint on the table, but I didn't figure how to do it. How can I add
the constraint "description is unique among all the record having active
set to true"?
I think that having this constraint would assure me that one of the two
function will fail, so I'll be able to handle the exception. Am I right?
I also have a second small question. In faq 4.11.3 they say that
currval() doesn't lead to race conditions. How does it work? I can't
really understand the meaning of "currval() returns the current value
assigned by your session, not by all sessions".
Thank you.
--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.
