Date: Mon, 11 Feb 2008 11:55:22 -0500
From: Jean-David Beyer <jeandavid8@verizon.net>
Subject: Re: Check before INSERT INTO
In-reply-to: <024301c86cca$591c9c60$3102a8c0@dream>
To: pgsql-sql@postgresql.org
Message-id: <47B07DFA.8030003@verizon.net>
Organization: Institute for Regimented Whimsey
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit
References: <010901c86ca6$3b66e870$3102a8c0@dream>
	<873arzscnu.fsf@oxford.xeocode.com>
	<024301c86cca$591c9c60$3102a8c0@dream>
User-Agent: Thunderbird 1.5.0.9 (X11/20070317)

Shavonne Marietta Wijesinghe wrote:
> Thanks for the reply Grogory. I am trying to do a INSERT INTO.
> 
> Here is my table. n_gen, n_sheet, tot_n_sheet are defined as Primary Key
> (Serial not null)

That is sure confusing. What could a DDL saying

   INTEGER n_gen SERIAL NOT NULL;
   INTEGER n_sheet SERIAL NOT NULL;
   INTEGER tot_n_sheet SERIAL NOT NULL;
   PRIMARY KEY (n_gen, n_sheet, tot_n_sheet)

mean? Is this what your DDL says? Do you enter rows of this table specifying
the id and expecting the three serial generators to pick non-null sequential
numbers for the other three fields? I think you are very unclear about what
is going on here. Are you perhaps saying the table has three (distinct)
primary keys? Because if you assign them values, why would they be SERIAL?

>   id     | n_gen     | n_sheet   | tot_n_sheet
> ----------+-----------+-----------+-------------
>     a    |      1    |     1     |      1
>     b    |      2    |     1     |      2
>     x    |      2    |     2     |      2
>     u    |      3    |     1     |      1
>     r    |      4    |     1     |      3
>     a    |      4    |     2     |      3
>     s    |      4    |     3     |      3
> 
> 
> So there are 2 users inserting in to the db. In my ASP page i have a
> field that shows the value of n_gen +1. So when the 2 users both login
> at the same time, with different sessions, they both see "7" in the
> n_gen field. But when they click on the sumbit button only one record is
> inserted and the other is lost.

Whatever you are doing that I do not understand with your keys, if you have
two users doing inserts on the same table, would you not have to run this
with Serializable Isolation Level (12.2.2. in the manual)? Would this not
fix your problem especially if you have a SERIAL as primary key?
> 
> I though it was possible to change the SQL string before it does the
> update.. But i can't seem to find a solution for it.. Any idea ??
> 


-- 
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 11:35:01 up 18 days, 1:21, 1 user, load average: 4.22, 4.28, 4.27