From: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
To: Jean-David Beyer <jeandavid8@verizon.net>
To: pgsql-sql@postgresql.org
Subject: Re: Check before INSERT INTO
Date: Tue, 12 Feb 2008 11:00:12 +0100
Message-ID: <00ae01c86d5e$0f2b2ba0$3102a8c0@dream> (raw)
References: <010901c86ca6$3b66e870$3102a8c0@dream>
<873arzscnu.fsf@oxford.xeocode.com>
<024301c86cca$591c9c60$3102a8c0@dream>
<47B07DFA.8030003@verizon.net>
Thanks for the replies.. But my problem still continues even after setting
the isolation level.
Set oRs = oConn.Execute("SELECT N_GEN FROM MY_TABLE ORDER BY N_GEN::INT
DESC")
If err <> 0 then 'If table not found
GetFieldValue = "1"
WriteToFile logfilepath, date & " " & time & " -- no table Numero
progressivo: 1" & vbcrlf , True
else
BEGIN
SET TRANSACTION = "ISOLATION LEVEL SERIALIZABLE"
if tipo_scheda = "SINGOLA" then
GetFieldValue = oRs("N_GEN") + 1
else
GetFieldValue = oRs("N_GEN")
end if
end if
COMMIT
Debugging my code(with logs) i see that still when 2 users login at the same
time the N_GEN value is the same. (they enter the first if)
In "My_Table" the last record has the value "5" so the next user that logs
in shoul get the value "6". Sadly both the current users get the value "6".
Have i set the isolation level correctly??
Thanks
Shavonne
----- Original Message -----
From: "Jean-David Beyer" <jeandavid8@verizon.net>
To: <pgsql-sql@postgresql.org>
Sent: Monday, February 11, 2008 5:55 PM
Subject: Re: [SQL] Check before INSERT INTO
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: shavonne.marietta@studioform.it, jeandavid8@verizon.net
Subject: Re: Check before INSERT INTO
In-Reply-To: <00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox