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.
Attachment:
signature.asc
Description: OpenPGP digital signature
