[SQL] PRIMARY KEY
Hello
I have created a table
CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY, FORMSTORE text,
COD_NOTAIO text, PA_COGNOME text);
And i insert the rows via a form in ASP. When the form loads i have a functin
that goes and gets the value of the field N_GEN adds 1 to it and shows it to
the user.
The problem is when i have 2 users working at the same time.
For example the last value in my field N_GEN is 2
When both the users A and B loads the form (ASP page) it sees N_GEN = 3 :)
So they fill in the form and user A clicks on the button OK and the record has
been inserted with N_GEN = 3. But when the user B clicks on the button the
record is not inserted because it has the same key "3"
INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO, PA_COGNOME) VALUES
('192168217200737122012', '3', '', '00128', 'DE MARTINIS')
Is there any way i can do this automatically? i mean maybe i have to use
someother property instead of "Primary Key" ??
Thanks
Shavonne Wijesinghe
Re: [SQL] PRIMARY KEY
Shavonne Marietta Wijesinghe wrote: Hello I have created a table CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY, FORMSTORE text, COD_NOTAIO text, PA_COGNOME text); And i insert the rows via a form in ASP. When the form loads i have a functin that goes and gets the value of the field N_GEN adds 1 to it and shows it to the user. The problem is when i have 2 users working at the same time. Check the manuals for "sequences" and "serial type". -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] PRIMARY KEY
Hello,
Is it possible to redesign your table as follows:
create table Mod48_00_2007 (
IDtext,
N_GEN serial not null,
FORMSTORE text,
COD_NOTATIO text,
PA_COGNOMEtext,
constraint pk_Mod48_00_2007 primary key (N_GEN)
);
Your insert simply becomes:
INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO, PA_COGNOME) VALUES
('192168217200737122012', '', '00128', 'DE MARTINIS')
Do note that you do not refer to the N_GEN column, it will use the next
value, please refer to
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
for more information.
Mario
Shavonne Marietta Wijesinghe wrote:
Hello
I have created a table
CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY, FORMSTORE
text, COD_NOTAIO text, PA_COGNOME text);
And i insert the rows via a form in ASP. When the form loads i have a
functin that goes and gets the value of the field N_GEN adds 1 to it
and shows it to the user.
The problem is when i have 2 users working at the same time.
For example the last value in my field N_GEN is 2
When both the users A and B loads the form (ASP page) it sees N_GEN = 3 :)
So they fill in the form and user A clicks on the button OK and the
record has been inserted with N_GEN = 3. But when the user B clicks on
the button the record is not inserted because it has the same key "3"
INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO,
PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE
MARTINIS')
Is there any way i can do this automatically? i mean maybe i have to
use someother property instead of "Primary Key" ??
Thanks
Shavonne Wijesinghe
Re: [SQL] PRIMARY KEY
Phillip Smith wrote:
If you actually need to know the value of N_GEN in your ASP
application, you will need to query the database first and select the
NEXTVAL from the sequence that the "serial" data type will create,
then use that returned value in your insert - ie, DON'T exclude it
from the insert, otherwise it will default to NEXTVAL again and return
a different value.
The only catch with this is that you most likely won't end up with
contiguous values in this column - ie, if a user cancels after you
seect nextval, but before you insert - the value of the sequence has
already increased, and will be increased again before returning a
value when you next select nextval
That's precisely why I would not use the nextval call, you might end up
with gaps. Moreover you are making 2 database calls instead of one.
But then again, if your application needs to know the value you can't
really avoid using nextval (good thing it's concurrency proof)
Mario
Phillip Smith wrote:
If you actually need to know the value of N_GEN in your ASP
application, you will need to query the database first and select the
NEXTVAL from the sequence that the "serial" data type will create,
then use that returned value in your insert - ie, DON'T exclude it
from the insert, otherwise it will default to NEXTVAL again and return
a different value.
The only catch with this is that you most likely won't end up with
contiguous values in this column - ie, if a user cancels after you
seect nextval, but before you insert - the value of the sequence has
already increased, and will be increased again before returning a
value when you next select nextval
Cheers,
~p
On Wed, 2007-03-07 at 12:57 +0100, M.P.Dankoor wrote:
Hello,
Is it possible to redesign your table as follows:
create table Mod48_00_2007 (
IDtext,
N_GEN serial not null,
FORMSTORE text,
COD_NOTATIO text,
PA_COGNOMEtext,
constraint pk_Mod48_00_2007 primary key (N_GEN)
);
Your insert simply becomes:
INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO, PA_COGNOME)
VALUES ('192168217200737122012', '', '00128', 'DE MARTINIS')
Do note that you do not refer to the N_GEN column, it will use the
next value, please refer to
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
for more information.
Mario
Shavonne Marietta Wijesinghe wrote:
Hello
I have created a table
CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY,
FORMSTORE text, COD_NOTAIO text, PA_COGNOME text);
And i insert the rows via a form in ASP. When the form loads i have
a functin that goes and gets the value of the field N_GEN adds 1 to
it and shows it to the user.
The problem is when i have 2 users working at the same time.
For example the last value in my field N_GEN is 2
When both the users A and B loads the form (ASP page) it sees N_GEN
= 3 :)
So they fill in the form and user A clicks on the button OK and the
record has been inserted with N_GEN = 3. But when the user B clicks
on the button the record is not inserted because it has the same key
"3"
INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO,
PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE
MARTINIS')
Is there any way i can do this automatically? i mean maybe i have to
use someother property instead of "Primary Key" ??
Thanks
Shavonne Wijesinghe
Confidentiality and Privilege
Notice
The material contained in this message is privileged and confidential
to the addressee. If you are not the addressee indicated in this
message or responsible for delivery of the message to such person, you
may not copy or deliver this message to anyone, and you should destroy
it and kindly notify the sender by reply email.
Information in this message that does not relate to the official
business of Weatherbeeta must be treated as neither given nor endorsed
by Weatherbeeta. Weatherbeeta, its employees, contractors or
associates shall not be liable for direct, indirect or consequential
loss arising from transmission of this message or any attachments
Re: [SQL] PRIMARY KEY
Thanks alot Mario. It did exactly what i wanted. I shall also take alook at the
link you gave for more details..
Shavonne Wijesinghe
- Original Message -
From: M.P.Dankoor
To: Shavonne Marietta Wijesinghe
Cc: pgsql-sql@postgresql.org
Sent: Wednesday, March 07, 2007 12:57 PM
Subject: Re: [SQL] PRIMARY KEY
Hello,
Is it possible to redesign your table as follows:
create table Mod48_00_2007 (
IDtext,
N_GEN serial not null,
FORMSTORE text,
COD_NOTATIO text,
PA_COGNOMEtext,
constraint pk_Mod48_00_2007 primary key (N_GEN)
);
Your insert simply becomes:
INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO, PA_COGNOME) VALUES
('192168217200737122012', '', '00128', 'DE MARTINIS')
Do note that you do not refer to the N_GEN column, it will use the next
value, please refer to
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
for more information.
Mario
Shavonne Marietta Wijesinghe wrote:
Hello
I have created a table
CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY, FORMSTORE
text, COD_NOTAIO text, PA_COGNOME text);
And i insert the rows via a form in ASP. When the form loads i have a
functin that goes and gets the value of the field N_GEN adds 1 to it and shows
it to the user.
The problem is when i have 2 users working at the same time.
For example the last value in my field N_GEN is 2
When both the users A and B loads the form (ASP page) it sees N_GEN = 3 :)
So they fill in the form and user A clicks on the button OK and the record
has been inserted with N_GEN = 3. But when the user B clicks on the button the
record is not inserted because it has the same key "3"
INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO, PA_COGNOME)
VALUES ('192168217200737122012', '3', '', '00128', 'DE MARTINIS')
Is there any way i can do this automatically? i mean maybe i have to use
someother property instead of "Primary Key" ??
Thanks
Shavonne Wijesinghe
[SQL] tesearch2 question
Hi All, I'm trying to udpate a table containing 13149741 records. And its taking forever to complete this process. The update query i'm trying to run is for full text indexing similiar to UPDATE tblMessages SET idxFTI=to_tsvector(strMessage); Below are some of the stats which might be helpful for analyzing this $top PID USERNAME LWP PRI NICE SIZE RES STATETIMECPU COMMAND 3091 postgres 1 430 46M 38M cpu/1 200:06 3.20% postgres 5052 postgres 1 600 149M 134M sleep0:17 3.12% postgres <<
Re: [SQL] best index for ~ ordering?
On Wed, Mar 07, 2007 at 12:28:57AM -0300, [EMAIL PROTECTED] wrote: > Well, im wondering if is possible using LIKE '%blah%', even better would be > upper/lower(string) like '%blah%', Your better bet then is either to index lower(string) or better, on insert convert everything to lower(). A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] tesearch2 question
On Wed, 7 Mar 2007, Sumeet wrote: Hi All, I'm trying to udpate a table containing 13149741 records. And its taking forever to complete this process. The update query i'm trying to run is for full text indexing similiar to UPDATE tblMessages SET idxFTI=to_tsvector(strMessage); How big are your strMessage ? and what's your tsearch2 configuration ? Can you estimate how long takes updating, for example, 1000 rows ? It looks like your system is IO bound. What's your hardware ? Below are some of the stats which might be helpful for analyzing this $top PID USERNAME LWP PRI NICE SIZE RES STATETIMECPU COMMAND 3091 postgres 1 430 46M 38M cpu/1 200:06 3.20% postgres 5052 postgres 1 600 149M 134M sleep0:17 3.12% postgres << Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] SHA-1 vs MD5
Hi list, I know that there is a md5 internal function on postgresql, but I noticed that it isn't the more secure today. I would like to know if there is a SHA-1 function implemented yet of, if not, if the team has plan to introduce it on PostgreSQL. Regards ... -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/
Re: [SQL] SHA-1 vs MD5
On Wed, Mar 07, 2007 at 05:04:18PM -0300, Ezequias Rodrigues da Rocha wrote: > Hi list, > > I know that there is a md5 internal function on postgresql, but I noticed > that it isn't the more secure today. I would like to know if there is a > SHA-1 function implemented yet of, if not, if the team has plan to introduce > it on PostgreSQL. What is the problem you're trying to solve? Md5 is probably good enough for many cases, but for long-term use, you're right that sha-1 is what you need. Actually, you need sha-256, quite frankly. a -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SHA-1 vs MD5
On 3/8/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote: What is the problem you're trying to solve? Md5 is probably good enough for many cases, but for long-term use, you're right that sha-1 is what you need. Actually, you need sha-256, quite frankly. Looking at his last mail he's after a password hash. To the OP: Currently there's no support in pg for sha algorithms, but you could always implement those in your application and store the hash in pg that way. Cheers, Andrej ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] tesearch2 question
Thanks Oleg,
My String message are Abstracts of papers, I did a
$ select avg(len) from (select length(abstract) as len from master_table
limit 500) E;
avg
---
1355.5907859078590786
(1 row)
so length is approx 1400.
I couldn't find any appropriate way to analyze the time for update queries,
but what i did was a explain analyze
$ explain analyze select to_tsvector(article_title) from master_table limit
1000;
The total runtime was approx 500ms.
The server is Sun OS 5.10, with around 8gigs of RAM and 6cpus.
Thanks,
Sumeet.
On 3/7/07, Oleg Bartunov wrote:
On Wed, 7 Mar 2007, Sumeet wrote:
> Hi All,
>
> I'm trying to udpate a table containing 13149741 records. And its
taking
> forever to complete this process.
>
> The update query i'm trying to run is for full text indexing similiar to
>
> UPDATE tblMessages SET idxFTI=to_tsvector(strMessage);
>
How big are your strMessage ? and what's your tsearch2 configuration ?
Can you estimate how long takes updating, for example, 1000 rows ?
It looks like your system is IO bound. What's your hardware ?
>
> Below are some of the stats which might be helpful for analyzing this
>
> $top
>
> PID USERNAME LWP PRI NICE SIZE RES STATETIMECPU COMMAND
> 3091 postgres 1 430 46M 38M cpu/1 200:06 3.20% postgres
> 5052 postgres 1 600 149M 134M sleep0:17 3.12% postgres
>
> << running almost for a day and a half and it is still running,
>
> This table which i'm trying to update has 10 indexes
>
> =
> "a_article_pk" PRIMARY KEY, btree (id)
> "a_article_uk_pmid" UNIQUE, btree (pmid)
> "a_article_idx_abstract" btree ("substring"(abstract::text, 0, 255))
> "a_article_idx_date_cr_year" btree (date_cr_year)
> "a_article_idx_ml_journal_info_medline_ta" btree
(ml_journal_info_a_ta)
> "a_article_idx_owner" btree ("owner")
> "a_article_idx_pmid" btree (pmid)
> "a_article_idx_status" btree (status)
> "a_article_idx_title" btree (article_title)
> "a_master_t_idx_year_published" btree (published_year)
>
> But no indexes on the field i'm trying to update. The field i'm trying
to
> add is a new field.
> Can anyone help me out to figure out why is it taking so much time to
update
> the table.
>
> Also as u see in the above indexes, I have some indexes on some varchar
> column which i feel are totally useless unless u so a exact string
match.
> But does that help in any sense for improving the speed of retreiving
the
> string just normally without any search on it?
>
> Thanks,
> Sumeet.
>
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Thanks,
Sumeet Ambre
Master of Information Science Candidate,
Indiana University.
Re: [SQL] tesearch2 question
On Wed, 7 Mar 2007, Sumeet wrote:
Thanks Oleg,
My String message are Abstracts of papers, I did a
$ select avg(len) from (select length(abstract) as len from master_table
limit 500) E;
avg
---
1355.5907859078590786
(1 row)
so length is approx 1400.
that'is about 18 Gb of text ! What's your hardware ?
I couldn't find any appropriate way to analyze the time for update queries,
but what i did was a explain analyze
$ explain analyze select to_tsvector(article_title) from master_table limit
1000;
The total runtime was approx 500ms.
just issue \timing in psql before executing update command.
Then you could estimate total time.
The server is Sun OS 5.10, with around 8gigs of RAM and 6cpus.
Thanks,
Sumeet.
On 3/7/07, Oleg Bartunov wrote:
On Wed, 7 Mar 2007, Sumeet wrote:
> Hi All,
>
> I'm trying to udpate a table containing 13149741 records. And its
taking
> forever to complete this process.
>
> The update query i'm trying to run is for full text indexing similiar to
>
> UPDATE tblMessages SET idxFTI=to_tsvector(strMessage);
>
How big are your strMessage ? and what's your tsearch2 configuration ?
Can you estimate how long takes updating, for example, 1000 rows ?
It looks like your system is IO bound. What's your hardware ?
>
> Below are some of the stats which might be helpful for analyzing this
>
> $top
>
> PID USERNAME LWP PRI NICE SIZE RES STATETIMECPU COMMAND
> 3091 postgres 1 430 46M 38M cpu/1 200:06 3.20% postgres
> 5052 postgres 1 600 149M 134M sleep0:17 3.12% postgres
>
> << running almost for a day and a half and it is still running,
>
> This table which i'm trying to update has 10 indexes
>
> =
> "a_article_pk" PRIMARY KEY, btree (id)
> "a_article_uk_pmid" UNIQUE, btree (pmid)
> "a_article_idx_abstract" btree ("substring"(abstract::text, 0, 255))
> "a_article_idx_date_cr_year" btree (date_cr_year)
> "a_article_idx_ml_journal_info_medline_ta" btree
(ml_journal_info_a_ta)
> "a_article_idx_owner" btree ("owner")
> "a_article_idx_pmid" btree (pmid)
> "a_article_idx_status" btree (status)
> "a_article_idx_title" btree (article_title)
> "a_master_t_idx_year_published" btree (published_year)
>
> But no indexes on the field i'm trying to update. The field i'm trying
to
> add is a new field.
> Can anyone help me out to figure out why is it taking so much time to
update
> the table.
>
> Also as u see in the above indexes, I have some indexes on some varchar
> column which i feel are totally useless unless u so a exact string
match.
> But does that help in any sense for improving the speed of retreiving
the
> string just normally without any search on it?
>
> Thanks,
> Sumeet.
>
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] SHA-1 vs MD5
On 3/7/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
I know that there is a md5 internal function on postgresql, but I noticed
that it isn't the more secure today. I would like to know if there is a
SHA-1 function implemented yet of, if not, if the team has plan to introduce
it on PostgreSQL.
You are probably looking for the pgcrypto contribution, look in the contrib
directory for it.
test=# select encode(digest('blahblah', 'sha256'), 'hex');
encode
--
40b1bbb5445fc021a312315379f4633284851e14d1db83fb0730f58872d6033b
(1 row)
--
Chad
http://www.postgresqlforums.com/
