questions about query design

From: Ottavio Campana <ottavio(at)campana(dot)vi(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: questions about query design
Date: 2007-03-22 06:08:15
Message-ID: 46021D4F.1030700@campana.vi.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Devrim GÜNDÜZ 2007-03-22 07:04:42 Re: Configuring phpPgAdmin and pg_ctl reload
Previous Message lmth 2007-03-22 06:02:58 A request for your input.