Thread: questions about query design

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.

Ottavio Campana wrote:
> 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).

Assuming active can't be NULL, what's wrong with a UNIQUE constraint on
(description, active)?

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Alban Hertroys <alban@magproductions.nl> writes:
> Ottavio Campana wrote:
>> 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).

> Assuming active can't be NULL, what's wrong with a UNIQUE constraint on
> (description, active)?

I think he does not want descriptions to be unique among non-active
entries.  If so, the right thing is a partial unique index:

CREATE UNIQUE INDEX ... on (description) WHERE active;

            regards, tom lane