pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCheck before INSERT INTO
10+ messages / 4 participants
[nested] [flat]
* Check before INSERT INTO
@ 2008-02-11 12:04 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 10+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-11 12:04 UTC (permalink / raw)
To: pgsql-sql
My table structure
CREATE TABLE my_shevi
(
id text,
n_gen serial NOT NULL,
n_sheet serial NOT NULL,
tot_sheet serial NOT NULL,
CONSTRAINT my_shevi_pkey PRIMARY KEY (n_gen, n_sheet, tot_sheet)
)
WITH OIDS;
ALTER TABLE my_shevi OWNER TO postgres;
The user updates the DB via ASP. When 2 users click on the submit button at the same time, only 1 record is inserted. (ERROR: duplicate key violates unique constraint "my_shevi_pkey")
For example they both send a string like below.
strSQL = INSERT INTO my_shevi VALUES ('a', 4, 1, 1);
I thought of adding a test before executing the insert into.
Set SQLN_GEN = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET), upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN='" & n_gen & "' AND N_SHEET='" & n_sheet & "' AND TOT_N_SHEET='" & tot_n_sheet & "'")
if (not SQLN_GEN.eof) then
***** set n_gen + 1
else
***** leave n_gen the way it is
end if
conn.Execute strSQL
But how can i ask it to change the n_gen value??? (The part with the *****)
Thanks
Shavonne Wijesinghe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Check before INSERT INTO
@ 2008-02-11 13:13 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 0 replies; 10+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-11 13:13 UTC (permalink / raw)
To: Kopljan Michael <kopljan@beotel.net>; +Cc: pgsql-sql
ehmm and where is it said that N_GEN should add 1 before inserting the record?? sorry i don't understand the help.
Shavonne
----- Original Message -----
From: Kopljan Michael
To: 'Shavonne Marietta Wijesinghe'
Sent: Monday, February 11, 2008 1:45 PM
Subject: RE: [SQL] Check before INSERT INTO
Try fierst to add Transaction issolation level to Connection object :
Conn.IsolationLevel = 1048576 && adXactIsolated
Then when user submit form try something like this :
strSQL = 'SELECT * FROM my_shevi ORDER BY n_gen DESC, n_sheet DESC, tot_sheet DESC LIMIT 1'
Set SQLN_GEN = oConn.Execute(strSQL)
SQLN_GEN.AddNew
SQLN_GEN('id').Value = 'a'
Conn.BeginTrans()
SQLN_GEN.Update
SQLN_GEN.Requery()
Conn.CommitTrans()
Bye Michael
------------------------------------------------------------------------------
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Shavonne Marietta Wijesinghe
Sent: Monday, February 11, 2008 1:04 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Check before INSERT INTO
My table structure
CREATE TABLE my_shevi
(
id text,
n_gen serial NOT NULL,
n_sheet serial NOT NULL,
tot_sheet serial NOT NULL,
CONSTRAINT my_shevi_pkey PRIMARY KEY (n_gen, n_sheet, tot_sheet)
)
WITH OIDS;
ALTER TABLE my_shevi OWNER TO postgres;
The user updates the DB via ASP. When 2 users click on the submit button at the same time, only 1 record is inserted. (ERROR: duplicate key violates unique constraint "my_shevi_pkey")
For example they both send a string like below.
strSQL = INSERT INTO my_shevi VALUES ('a', 4, 1, 1);
I thought of adding a test before executing the insert into.
Set SQLN_GEN = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET), upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN='" & n_gen & "' AND N_SHEET='" & n_sheet & "' AND TOT_N_SHEET='" & tot_n_sheet & "'")
if (not SQLN_GEN.eof) then
***** set n_gen + 1
else
***** leave n_gen the way it is
end if
conn.Execute strSQL
But how can i ask it to change the n_gen value??? (The part with the *****)
Thanks
Shavonne Wijesinghe
__________ Information from ESET Smart Security, version of virus signature database 2863 (20080211) __________
The message was checked by ESET Smart Security.
http://www.eset.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Check before INSERT INTO
@ 2008-02-11 16:03 Gregory Stark <stark@enterprisedb.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 10+ messages in thread
From: Gregory Stark @ 2008-02-11 16:03 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
"Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> writes:
> The user updates the DB via ASP. When 2 users click on the submit button at the
> same time, only 1 record is inserted. (ERROR: duplicate key violates unique
> constraint "my_shevi_pkey")
>
> For example they both send a string like below.
> strSQL = INSERT INTO my_shevi VALUES ('a', 4, 1, 1);
>
> I thought of adding a test before executing the insert into.
It's not clear to me what you're trying to do. If you're trying to update an
existing record then you might want something like example 37-1 on this page:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html
If you want to pick the first available n_gen then you're going to have to
repeatedly try inserting until you don't get that error. That will perform
quite poorly when you get to large values. You could do a "SELECT max(n_gen)
WHERE..." first but even that will be quite a lot of work for your database.
Perhaps you should rethink n_gen and use a serial column to generate your
primary key instead.
> Set SQLN_GEN = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET),
> upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN='" & n_gen & "' AND
> N_SHEET='" & n_sheet & "' AND TOT_N_SHEET='" & tot_n_sheet & "'")
For what it's worth your script is a security hole. Look into using query
parameters which in ASP will probably be represented by "?". The method above
will allow hackers to get direct access to your database and do nasty things.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Check before INSERT INTO
@ 2008-02-11 16:22 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 2 replies; 10+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-11 16:22 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: pgsql-sql
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)
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.
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 ??
Thanks,
Shavonne
----- Original Message -----
From: "Gregory Stark" <stark@enterprisedb.com>
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, February 11, 2008 5:03 PM
Subject: Re: Check before INSERT INTO
> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> writes:
>
>> The user updates the DB via ASP. When 2 users click on the submit button
>> at the
>> same time, only 1 record is inserted. (ERROR: duplicate key violates
>> unique
>> constraint "my_shevi_pkey")
>>
>> For example they both send a string like below.
>> strSQL = INSERT INTO my_shevi VALUES ('a', 4, 1, 1);
>>
>> I thought of adding a test before executing the insert into.
>
> It's not clear to me what you're trying to do. If you're trying to update
> an
> existing record then you might want something like example 37-1 on this
> page:
>
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html
>
> If you want to pick the first available n_gen then you're going to have to
> repeatedly try inserting until you don't get that error. That will perform
> quite poorly when you get to large values. You could do a "SELECT
> max(n_gen)
> WHERE..." first but even that will be quite a lot of work for your
> database.
>
> Perhaps you should rethink n_gen and use a serial column to generate your
> primary key instead.
>
>> Set SQLN_GEN = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET),
>> upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN='" & n_gen & "' AND
>> N_SHEET='" & n_sheet & "' AND TOT_N_SHEET='" & tot_n_sheet & "'")
>
> For what it's worth your script is a security hole. Look into using query
> parameters which in ASP will probably be represented by "?". The method
> above
> will allow hackers to get direct access to your database and do nasty
> things.
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's RemoteDBA services!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Check before INSERT INTO
@ 2008-02-11 16:55 Jean-David Beyer <jeandavid8@verizon.net>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
1 sibling, 1 reply; 10+ messages in thread
From: Jean-David Beyer @ 2008-02-11 16:55 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Check before INSERT INTO
@ 2008-02-11 16:56 Gregory Stark <stark@enterprisedb.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
1 sibling, 0 replies; 10+ messages in thread
From: Gregory Stark @ 2008-02-11 16:56 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
"Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> writes:
> 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)
> 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.
>
> 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 ??
You'll have to explain what you want n_gen to contain.
Then you'll have to decide whether you want to do this in ASP where you can
certainly change the SQL all you like, or on the server where you can have
triggers which change the values being stored or executing additional queries.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Check before INSERT INTO
@ 2008-02-12 10:00 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 1 reply; 10+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-12 10:00 UTC (permalink / raw)
To: Jean-David Beyer <jeandavid8@verizon.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Check before INSERT INTO
@ 2008-02-12 10:35 Richard Huxton <dev@archonet.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2008-02-12 10:35 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: Jean-David Beyer <jeandavid8@verizon.net>; pgsql-sql
Shavonne Marietta Wijesinghe wrote:
> 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)
Of course it is.
> 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".
Why sadly? What do you think should happen?
> Have i set the isolation level correctly??
I think you are having problems with thinking through the concurrency of
this problem.
Scenario 1 - will work
==========
User1: Read value 5
User1: new value = 5 + 1
User1: Commit changes
User2: Read value 6
User2: new value = 6 + 1
User2: Commit changes
Scenario 2 - will not work
==========
User1: Read value 5
User1: new value = 5 + 1
User2: Read value 5 (there is no "6" yet, it's not been committed)
User2: new value = 5 + 1
User1: Commit changes
User2: Commit changes - ERROR
There are only two alternatives in scenario #2 - block user 2 from
reading a value until user1 commits/rolls back or give them a value that
might be out of date. That's what the isolation level controls.
From your original email you have n_gen defined as a serial. That's
basically an integer column with a default value from a
sequence-generator. I'd just let the default value be accepted when you
want a new number, that guarantees you a different value each time
(although you can't guarantee you'll get 1,2,3,4,5...)
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Check before INSERT INTO
@ 2008-02-12 11:06 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-12 11:06 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Jean-David Beyer <jeandavid8@verizon.net>; pgsql-sql
Even though n_gen is defined as a serial I can't let it handle the
progressive key by its self since there is the need that some records should
have the same value. That's why i use 3 primary keys.
A | B | C
---+---+---
1 | 1 | 1
2 | 1 | 3
2 | 2 | 3
2 | 3 | 3
3 | 1 | 2
3 | 2 | 2
4 | 1 | 1
The 3 keys A, B, C are defined as Serial and Primay Keys
Anyway the other suggestion, Blocking the second user from reading the db.
So for the second user I could give A temp key something like 0 and then do
a select before the submit and change the value.
But how is it possible to know if some other user is reading the db??
Thanks
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: "Jean-David Beyer" <jeandavid8@verizon.net>; <pgsql-sql@postgresql.org>
Sent: Tuesday, February 12, 2008 11:35 AM
Subject: Re: [SQL] Check before INSERT INTO
> Shavonne Marietta Wijesinghe wrote:
>> 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)
>
> Of course it is.
>
>> 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".
>
> Why sadly? What do you think should happen?
>
>> Have i set the isolation level correctly??
>
> I think you are having problems with thinking through the concurrency of
> this problem.
>
> Scenario 1 - will work
> ==========
> User1: Read value 5
> User1: new value = 5 + 1
> User1: Commit changes
> User2: Read value 6
> User2: new value = 6 + 1
> User2: Commit changes
>
> Scenario 2 - will not work
> ==========
> User1: Read value 5
> User1: new value = 5 + 1
> User2: Read value 5 (there is no "6" yet, it's not been committed)
> User2: new value = 5 + 1
> User1: Commit changes
> User2: Commit changes - ERROR
>
> There are only two alternatives in scenario #2 - block user 2 from reading
> a value until user1 commits/rolls back or give them a value that might be
> out of date. That's what the isolation level controls.
>
> From your original email you have n_gen defined as a serial. That's
> basically an integer column with a default value from a
> sequence-generator. I'd just let the default value be accepted when you
> want a new number, that guarantees you a different value each time
> (although you can't guarantee you'll get 1,2,3,4,5...)
>
> --
> Richard Huxton
> Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Check before INSERT INTO
@ 2008-02-12 12:01 Richard Huxton <dev@archonet.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2008-02-12 12:01 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: Jean-David Beyer <jeandavid8@verizon.net>; pgsql-sql
Shavonne Marietta Wijesinghe wrote:
> Even though n_gen is defined as a serial I can't let it handle the
> progressive key by its self since there is the need that some records
> should have the same value.
Of course you can - the default is only provided if you don't provide
your own.
> That's why i use 3 primary keys.
No, you're not. By definition you can only have one primary key. You are
using a 3-column primary key.
> A | B | C
> ---+---+---
> 1 | 1 | 1
> 2 | 1 | 3
> 2 | 2 | 3
> 2 | 3 | 3
> 3 | 1 | 2
> 3 | 2 | 2
> 4 | 1 | 1
>
> The 3 keys A, B, C are defined as Serial and Primay Keys
You don't have 3 keys, you have 3 columns and one primary key (A,B,C).
I'm not clear why B and C are serial - I don't see what that gets you.
> Anyway the other suggestion, Blocking the second user from reading the
> db. So for the second user I could give A temp key something like 0 and
> then do a select before the submit and change the value.
>
> But how is it possible to know if some other user is reading the db??
Don't try. If you want to insert some rows with a new value for A do
something like:
INSERT INTO my_shevi (a,b,c) VALUES (DEFAULT, 1, 1);
or
INSERT INTO my_shevi (a,b,c) VALUES (nextval(<sequence-name-here>, 1, 1);
INSERT INTO my_shevi (a,b,c) VALUES (currval(<sequence-name-here>), 1, 2);
INSERT INTO my_shevi (a,b,c) VALUES (currval(<sequence-name-here>), 1, 3);
etc.
Relevant parts of the manual (in 8.3 anyway):
9.15. Sequence Manipulation Functions
9.22. System Information Functions
The second chapter is for pg_get_serial_sequence() which might be useful
if you need to do this a lot.
Oh - and please try trimming unwanted parts of the message when you
reply. There was 100 unnecessary lines below here.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2008-02-12 12:01 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-11 12:04 Check before INSERT INTO Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-11 16:03 ` Gregory Stark <stark@enterprisedb.com>
2008-02-11 16:22 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-11 16:55 ` Jean-David Beyer <jeandavid8@verizon.net>
2008-02-12 10:00 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-12 10:35 ` Richard Huxton <dev@archonet.com>
2008-02-12 11:06 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-12 12:01 ` Richard Huxton <dev@archonet.com>
2008-02-11 16:56 ` Gregory Stark <stark@enterprisedb.com>
2008-02-11 13:13 Re: Check before INSERT INTO Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
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