Re: Check before INSERT INTO - Mailing list pgsql-sql

From Jean-David Beyer
Subject Re: Check before INSERT INTO
Date
Msg-id 47B07DFA.8030003@verizon.net
Whole thread Raw
In response to Re: Check before INSERT INTO  ("Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>)
List pgsql-sql
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
 


pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Usage of UUID with 8.3 (Windows)
Next
From: Alvaro Herrera
Date:
Subject: Re: Usage of UUID with 8.3 (Windows)