Thread: Lock strategies!
Hi guys,
I have a simple table:
teste=# \d forn
Table "public.forn"
Column | Type | Modifiers
---------+---------+------------------------------------------------------
id | integer | not null default
nextval('public.forn_id_seq'::text)
forn_id | integer |
descrip | text |
Ok! The forn_id is supposed to be sequencial and
without holes (if someone perform a DELETE or UPDATE,
so there will be a hole... no problem if the hole
happens in this case!).
Well, to know the next value of the forn_id column, it
was planned to be done like this:
teste=# INSERT INTO forn (forn_id,descrip) VALUES
((SELECT max(forn_id) FROM forn),'descrip1');
It will cause a huge delay in case this table became
huge, because the forn_id isn't an indexed column (but
I would index it! The problem I am talking about is
ONLY about the sequence of numbers).
As a way to be sure it will not another other client
getting the exact value as the max(forn_id), there was
a dirty thing:
teste=# BEGIN;
teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
teste=# INSERT INTO ...
teste=# COMMIT;
Well, I really think it is not the best way to do that
and I am asking you for advices!
1) Is it (... max(forn_id)... ) the best way to get
the next value to be inserted in the table?
2) Is there a automatic way to do that?
Thanks in advance and
Best Regards,
Marcelo
______________________________________________________________________
Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora:
http://mail.yahoo.com.br
I think that defining forn_id as "serial" is what you are looking for.
This will handle the assignment of unique numbers to the id for you (it creates
a sequence table).
The locking stategy is fraught with danger... and unnecessary.
Marc A. Leith
redboxdata inc.
E-mail:mleith@redboxdata.com
Quoting MaRcElO PeReIrA <gandalf_mp@yahoo.com.br>:
> Hi guys,
>
> I have a simple table:
>
> teste=# \d forn
> Table "public.forn"
> Column | Type | Modifiers
> ---------+---------+------------------------------------------------------
> id | integer | not null default
> nextval('public.forn_id_seq'::text)
> forn_id | integer |
> descrip | text |
>
> Ok! The forn_id is supposed to be sequencial and
> without holes (if someone perform a DELETE or UPDATE,
> so there will be a hole... no problem if the hole
> happens in this case!).
>
> Well, to know the next value of the forn_id column, it
> was planned to be done like this:
>
> teste=# INSERT INTO forn (forn_id,descrip) VALUES
> ((SELECT max(forn_id) FROM forn),'descrip1');
>
> It will cause a huge delay in case this table became
> huge, because the forn_id isn't an indexed column (but
> I would index it! The problem I am talking about is
> ONLY about the sequence of numbers).
>
> As a way to be sure it will not another other client
> getting the exact value as the max(forn_id), there was
> a dirty thing:
>
> teste=# BEGIN;
> teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> teste=# INSERT INTO ...
> teste=# COMMIT;
>
> Well, I really think it is not the best way to do that
> and I am asking you for advices!
>
> 1) Is it (... max(forn_id)... ) the best way to get
> the next value to be inserted in the table?
>
> 2) Is there a automatic way to do that?
>
> Thanks in advance and
> Best Regards,
>
> Marcelo
>
> ______________________________________________________________________
>
> Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora:
> http://mail.yahoo.com.br
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Dave,
I actually use just the sequence, as you wrote!
The biggest problem it that I *can't* have holes in
that column, so it was because I used id (serial) and
forn_id (integer).
All other tables use only the sequence by itself, but
this one, especially, CAN'T have holes! It is the
problem!!! ;-)
So, if I rollback or whatever, the ID will be
populated with the sequence values, but the forn_id
must increase in a controled way, ie, without holes!
Advices??????
Regards!
Marcelo
--- Dave Cramer <pg@fastcrypt.com> escreveu: >
Marceio
>
>
>
> The sequence logic takes care of it. try it yourself
>
> open two connections with psql
>
> on one do a
> begin;
> insert into table
> select curval('forn_id_seq');
>
> on the other
>
> do a
> begin
> insert into table
> select curval('forn_id_seq');
>
>
> You will see that they both increment the sequence
> number
>
> you will also see how to get the current value as
> well.
>
> Note, no locking is actually required, you can do
> this without the
> transaction stuff, it is there just so you can see
> it in two sessions at
> the same time.
>
> Also note that a rollback will NOT roll back the
> sequence number, this
> will end up with holes but sequences are not
> guaranteed to not have
> holes.
>
> Why do you have two columns, id, and forn_id, you
> only need one.
>
> and then do an
>
> insert into forn (descrip) values ( 'some
> description' );
> then select curval('forn_id_seq');
>
> forn_id will be populated for you with the value
> from curval.
>
>
> Dave
>
> On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> > Hi guys,
> >
> > I have a simple table:
> >
> > teste=# \d forn
> > Table "public.forn"
> > Column | Type |
> Modifiers
> >
>
---------+---------+------------------------------------------------------
> > id | integer | not null default
> > nextval('public.forn_id_seq'::text)
> > forn_id | integer |
> > descrip | text |
> >
> > Ok! The forn_id is supposed to be sequencial and
> > without holes (if someone perform a DELETE or
> UPDATE,
> > so there will be a hole... no problem if the hole
> > happens in this case!).
> >
> > Well, to know the next value of the forn_id
> column, it
> > was planned to be done like this:
> >
> > teste=# INSERT INTO forn (forn_id,descrip) VALUES
> > ((SELECT max(forn_id) FROM forn),'descrip1');
> >
> > It will cause a huge delay in case this table
> became
> > huge, because the forn_id isn't an indexed column
> (but
> > I would index it! The problem I am talking about
> is
> > ONLY about the sequence of numbers).
> >
> > As a way to be sure it will not another other
> client
> > getting the exact value as the max(forn_id), there
> was
> > a dirty thing:
> >
> > teste=# BEGIN;
> > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> > teste=# INSERT INTO ...
> > teste=# COMMIT;
> >
> > Well, I really think it is not the best way to do
> that
> > and I am asking you for advices!
> >
> > 1) Is it (... max(forn_id)... ) the best way to
> get
> > the next value to be inserted in the table?
> >
> > 2) Is there a automatic way to do that?
> >
> > Thanks in advance and
> > Best Regards,
> >
> > Marcelo
> >
> >
>
______________________________________________________________________
> >
> > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito!
> Crie sua conta agora:
> > http://mail.yahoo.com.br
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> >
>
______________________________________________________________________
Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora:
http://mail.yahoo.com.br
On Mon, 24 Nov 2003 10:20:07 -0300 (ART)
MaRcElO PeReIrA <gandalf_mp@yahoo.com.br> wrote:
> Hi guys,
>
> I have a simple table:
>
> teste=# \d forn
> Table "public.forn"
> Column | Type | Modifiers
> ---------+---------+-------------------------------------------------
> -----
> id | integer | not null default
> nextval('public.forn_id_seq'::text)
> forn_id | integer |
> descrip | text |
>
Why not make forn_id a sequence as well?
then you simply call nextval('forn_id_seq')
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
Marceio
The sequence logic takes care of it. try it yourself
open two connections with psql
on one do a
begin;
insert into table
select curval('forn_id_seq');
on the other
do a
begin
insert into table
select curval('forn_id_seq');
You will see that they both increment the sequence number
you will also see how to get the current value as well.
Note, no locking is actually required, you can do this without the
transaction stuff, it is there just so you can see it in two sessions at
the same time.
Also note that a rollback will NOT roll back the sequence number, this
will end up with holes but sequences are not guaranteed to not have
holes.
Why do you have two columns, id, and forn_id, you only need one.
and then do an
insert into forn (descrip) values ( 'some description' );
then select curval('forn_id_seq');
forn_id will be populated for you with the value from curval.
Dave
On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> Hi guys,
>
> I have a simple table:
>
> teste=# \d forn
> Table "public.forn"
> Column | Type | Modifiers
> ---------+---------+------------------------------------------------------
> id | integer | not null default
> nextval('public.forn_id_seq'::text)
> forn_id | integer |
> descrip | text |
>
> Ok! The forn_id is supposed to be sequencial and
> without holes (if someone perform a DELETE or UPDATE,
> so there will be a hole... no problem if the hole
> happens in this case!).
>
> Well, to know the next value of the forn_id column, it
> was planned to be done like this:
>
> teste=# INSERT INTO forn (forn_id,descrip) VALUES
> ((SELECT max(forn_id) FROM forn),'descrip1');
>
> It will cause a huge delay in case this table became
> huge, because the forn_id isn't an indexed column (but
> I would index it! The problem I am talking about is
> ONLY about the sequence of numbers).
>
> As a way to be sure it will not another other client
> getting the exact value as the max(forn_id), there was
> a dirty thing:
>
> teste=# BEGIN;
> teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> teste=# INSERT INTO ...
> teste=# COMMIT;
>
> Well, I really think it is not the best way to do that
> and I am asking you for advices!
>
> 1) Is it (... max(forn_id)... ) the best way to get
> the next value to be inserted in the table?
>
> 2) Is there a automatic way to do that?
>
> Thanks in advance and
> Best Regards,
>
> Marcelo
>
> ______________________________________________________________________
>
> Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora:
> http://mail.yahoo.com.br
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
Marcelo,
You are asking for the impossible.
In order for sequences to work reliably they have to exist outside of a
transaction, and be atomic. If two transactions asked for a sequence
simultaneously, what number would you give them? If the first one gets
1, and the second gets 2 how do you roll back the first one and then
give the second one 1?
And it gets worse, what happens if 10 connections ask for one
simultaneously and then connection 3 7 rollback?
I don't know how to say this gently, but usually this requirement
suggests that more thinking is required on the application end.
Dave
On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> Dave,
>
> I actually use just the sequence, as you wrote!
>
> The biggest problem it that I *can't* have holes in
> that column, so it was because I used id (serial) and
> forn_id (integer).
>
> All other tables use only the sequence by itself, but
> this one, especially, CAN'T have holes! It is the
> problem!!! ;-)
>
> So, if I rollback or whatever, the ID will be
> populated with the sequence values, but the forn_id
> must increase in a controled way, ie, without holes!
>
> Advices??????
>
> Regards!
>
> Marcelo
>
> --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> Marceio
> >
> >
> >
> > The sequence logic takes care of it. try it yourself
> >
> > open two connections with psql
> >
> > on one do a
> > begin;
> > insert into table
> > select curval('forn_id_seq');
> >
> > on the other
> >
> > do a
> > begin
> > insert into table
> > select curval('forn_id_seq');
> >
> >
> > You will see that they both increment the sequence
> > number
> >
> > you will also see how to get the current value as
> > well.
> >
> > Note, no locking is actually required, you can do
> > this without the
> > transaction stuff, it is there just so you can see
> > it in two sessions at
> > the same time.
> >
> > Also note that a rollback will NOT roll back the
> > sequence number, this
> > will end up with holes but sequences are not
> > guaranteed to not have
> > holes.
> >
> > Why do you have two columns, id, and forn_id, you
> > only need one.
> >
> > and then do an
> >
> > insert into forn (descrip) values ( 'some
> > description' );
> > then select curval('forn_id_seq');
> >
> > forn_id will be populated for you with the value
> > from curval.
> >
> >
> > Dave
> >
> > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> > > Hi guys,
> > >
> > > I have a simple table:
> > >
> > > teste=# \d forn
> > > Table "public.forn"
> > > Column | Type |
> > Modifiers
> > >
> >
> ---------+---------+------------------------------------------------------
> > > id | integer | not null default
> > > nextval('public.forn_id_seq'::text)
> > > forn_id | integer |
> > > descrip | text |
> > >
> > > Ok! The forn_id is supposed to be sequencial and
> > > without holes (if someone perform a DELETE or
> > UPDATE,
> > > so there will be a hole... no problem if the hole
> > > happens in this case!).
> > >
> > > Well, to know the next value of the forn_id
> > column, it
> > > was planned to be done like this:
> > >
> > > teste=# INSERT INTO forn (forn_id,descrip) VALUES
> > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > >
> > > It will cause a huge delay in case this table
> > became
> > > huge, because the forn_id isn't an indexed column
> > (but
> > > I would index it! The problem I am talking about
> > is
> > > ONLY about the sequence of numbers).
> > >
> > > As a way to be sure it will not another other
> > client
> > > getting the exact value as the max(forn_id), there
> > was
> > > a dirty thing:
> > >
> > > teste=# BEGIN;
> > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> > > teste=# INSERT INTO ...
> > > teste=# COMMIT;
> > >
> > > Well, I really think it is not the best way to do
> > that
> > > and I am asking you for advices!
> > >
> > > 1) Is it (... max(forn_id)... ) the best way to
> > get
> > > the next value to be inserted in the table?
> > >
> > > 2) Is there a automatic way to do that?
> > >
> > > Thanks in advance and
> > > Best Regards,
> > >
> > > Marcelo
> > >
> > >
> >
> ______________________________________________________________________
> > >
> > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito!
> > Crie sua conta agora:
> > > http://mail.yahoo.com.br
> > >
> > > ---------------------------(end of
> > broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> > >
> >
>
> ______________________________________________________________________
>
> Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora:
> http://mail.yahoo.com.br
>
>
Hi Dave, Marc and all others,
I know it is really weird!
But, how can I explain to the user, who use the
sequence numbers, that he will have to handle with
those holes?
Ok! I will try to handle the holes! (fight against the
users)
Thanks!
Marcelo
--- Dave Cramer <pg@fastcrypt.com> escreveu: >
Marcelo,
>
> You are asking for the impossible.
>
> In order for sequences to work reliably they have to
> exist outside of a
> transaction, and be atomic. If two transactions
> asked for a sequence
> simultaneously, what number would you give them? If
> the first one gets
> 1, and the second gets 2 how do you roll back the
> first one and then
> give the second one 1?
>
> And it gets worse, what happens if 10 connections
> ask for one
> simultaneously and then connection 3 7 rollback?
>
> I don't know how to say this gently, but usually
> this requirement
> suggests that more thinking is required on the
> application end.
>
> Dave
>
>
>
> On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> > Dave,
> >
> > I actually use just the sequence, as you wrote!
> >
> > The biggest problem it that I *can't* have holes
> in
> > that column, so it was because I used id (serial)
> and
> > forn_id (integer).
> >
> > All other tables use only the sequence by itself,
> but
> > this one, especially, CAN'T have holes! It is the
> > problem!!! ;-)
> >
> > So, if I rollback or whatever, the ID will be
> > populated with the sequence values, but the
> forn_id
> > must increase in a controled way, ie, without
> holes!
> >
> > Advices??????
> >
> > Regards!
> >
> > Marcelo
> >
> > --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> > Marceio
> > >
> > >
> > >
> > > The sequence logic takes care of it. try it
> yourself
> > >
> > > open two connections with psql
> > >
> > > on one do a
> > > begin;
> > > insert into table
> > > select curval('forn_id_seq');
> > >
> > > on the other
> > >
> > > do a
> > > begin
> > > insert into table
> > > select curval('forn_id_seq');
> > >
> > >
> > > You will see that they both increment the
> sequence
> > > number
> > >
> > > you will also see how to get the current value
> as
> > > well.
> > >
> > > Note, no locking is actually required, you can
> do
> > > this without the
> > > transaction stuff, it is there just so you can
> see
> > > it in two sessions at
> > > the same time.
> > >
> > > Also note that a rollback will NOT roll back the
> > > sequence number, this
> > > will end up with holes but sequences are not
> > > guaranteed to not have
> > > holes.
> > >
> > > Why do you have two columns, id, and forn_id,
> you
> > > only need one.
> > >
> > > and then do an
> > >
> > > insert into forn (descrip) values ( 'some
> > > description' );
> > > then select curval('forn_id_seq');
> > >
> > > forn_id will be populated for you with the value
> > > from curval.
> > >
> > >
> > > Dave
> > >
> > > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA
> wrote:
> > > > Hi guys,
> > > >
> > > > I have a simple table:
> > > >
> > > > teste=# \d forn
> > > > Table "public.forn"
> > > > Column | Type |
> > > Modifiers
> > > >
> > >
> >
>
---------+---------+------------------------------------------------------
> > > > id | integer | not null default
> > > > nextval('public.forn_id_seq'::text)
> > > > forn_id | integer |
> > > > descrip | text |
> > > >
> > > > Ok! The forn_id is supposed to be sequencial
> and
> > > > without holes (if someone perform a DELETE or
> > > UPDATE,
> > > > so there will be a hole... no problem if the
> hole
> > > > happens in this case!).
> > > >
> > > > Well, to know the next value of the forn_id
> > > column, it
> > > > was planned to be done like this:
> > > >
> > > > teste=# INSERT INTO forn (forn_id,descrip)
> VALUES
> > > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > > >
> > > > It will cause a huge delay in case this table
> > > became
> > > > huge, because the forn_id isn't an indexed
> column
> > > (but
> > > > I would index it! The problem I am talking
> about
> > > is
> > > > ONLY about the sequence of numbers).
> > > >
> > > > As a way to be sure it will not another other
> > > client
> > > > getting the exact value as the max(forn_id),
> there
> > > was
> > > > a dirty thing:
> > > >
> > > > teste=# BEGIN;
> > > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE
> MODE;
> > > > teste=# INSERT INTO ...
> > > > teste=# COMMIT;
> > > >
> > > > Well, I really think it is not the best way to
> do
> > > that
> > > > and I am asking you for advices!
> > > >
> > > > 1) Is it (... max(forn_id)... ) the best way
> to
> > > get
> > > > the next value to be inserted in the table?
> > > >
> > > > 2) Is there a automatic way to do that?
> > > >
> > > > Thanks in advance and
> > > > Best Regards,
> > > >
> > > > Marcelo
> > > >
> > > >
> > >
> >
>
______________________________________________________________________
> > > >
> > > > Yahoo! Mail: 6MB, anti-spam e antivírus
> gratuito!
> > > Crie sua conta agora:
> > > > http://mail.yahoo.com.br
> > > >
>
=== message truncated ===
Yahoo! Mail - 6MB, anti-spam e antivírus gratuito. Crie sua conta agora:
http://mail.yahoo.com.br
On Mon, 24 Nov 2003 12:48:26 -0300 (ART) MaRcElO PeReIrA <gandalf_mp@yahoo.com.br> wrote: > The biggest problem it that I *can't* have holes in > that column, so it was because I used id (serial) and > forn_id (integer). > Well, if you cannot use a sequence you will have no choice but to use locking. don't use max - it isn't fast on PG use select forn_id from thetable order by fornid desc limit 1. You'll need an index on forn_id or performance will suffer. -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
Perhaps the primary key should be a sequence/serial, but also have a
secondary key which is assigned after commit.
You could have a process that continually ran something like:
select max(skey) from the_table;
select pkey from the_table where skey is null;
Then loop through the answers and assign sequenctial values.
As long as this is the only process that is allowed to update skey, it
should work.
Jon
On 24 Nov 2003, Dave Cramer wrote:
> Marcelo,
>
> You are asking for the impossible.
>
> In order for sequences to work reliably they have to exist outside of a
> transaction, and be atomic. If two transactions asked for a sequence
> simultaneously, what number would you give them? If the first one gets
> 1, and the second gets 2 how do you roll back the first one and then
> give the second one 1?
>
> And it gets worse, what happens if 10 connections ask for one
> simultaneously and then connection 3 7 rollback?
>
> I don't know how to say this gently, but usually this requirement
> suggests that more thinking is required on the application end.
>
> Dave
>
>
>
> On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> > Dave,
> >
> > I actually use just the sequence, as you wrote!
> >
> > The biggest problem it that I *can't* have holes in
> > that column, so it was because I used id (serial) and
> > forn_id (integer).
> >
> > All other tables use only the sequence by itself, but
> > this one, especially, CAN'T have holes! It is the
> > problem!!! ;-)
> >
> > So, if I rollback or whatever, the ID will be
> > populated with the sequence values, but the forn_id
> > must increase in a controled way, ie, without holes!
> >
> > Advices??????
> >
> > Regards!
> >
> > Marcelo
> >
> > --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> > Marceio
> > >
> > >
> > >
> > > The sequence logic takes care of it. try it yourself
> > >
> > > open two connections with psql
> > >
> > > on one do a
> > > begin;
> > > insert into table
> > > select curval('forn_id_seq');
> > >
> > > on the other
> > >
> > > do a
> > > begin
> > > insert into table
> > > select curval('forn_id_seq');
> > >
> > >
> > > You will see that they both increment the sequence
> > > number
> > >
> > > you will also see how to get the current value as
> > > well.
> > >
> > > Note, no locking is actually required, you can do
> > > this without the
> > > transaction stuff, it is there just so you can see
> > > it in two sessions at
> > > the same time.
> > >
> > > Also note that a rollback will NOT roll back the
> > > sequence number, this
> > > will end up with holes but sequences are not
> > > guaranteed to not have
> > > holes.
> > >
> > > Why do you have two columns, id, and forn_id, you
> > > only need one.
> > >
> > > and then do an
> > >
> > > insert into forn (descrip) values ( 'some
> > > description' );
> > > then select curval('forn_id_seq');
> > >
> > > forn_id will be populated for you with the value
> > > from curval.
> > >
> > >
> > > Dave
> > >
> > > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> > > > Hi guys,
> > > >
> > > > I have a simple table:
> > > >
> > > > teste=# \d forn
> > > > Table "public.forn"
> > > > Column | Type |
> > > Modifiers
> > > >
> > >
> > ---------+---------+------------------------------------------------------
> > > > id | integer | not null default
> > > > nextval('public.forn_id_seq'::text)
> > > > forn_id | integer |
> > > > descrip | text |
> > > >
> > > > Ok! The forn_id is supposed to be sequencial and
> > > > without holes (if someone perform a DELETE or
> > > UPDATE,
> > > > so there will be a hole... no problem if the hole
> > > > happens in this case!).
> > > >
> > > > Well, to know the next value of the forn_id
> > > column, it
> > > > was planned to be done like this:
> > > >
> > > > teste=# INSERT INTO forn (forn_id,descrip) VALUES
> > > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > > >
> > > > It will cause a huge delay in case this table
> > > became
> > > > huge, because the forn_id isn't an indexed column
> > > (but
> > > > I would index it! The problem I am talking about
> > > is
> > > > ONLY about the sequence of numbers).
> > > >
> > > > As a way to be sure it will not another other
> > > client
> > > > getting the exact value as the max(forn_id), there
> > > was
> > > > a dirty thing:
> > > >
> > > > teste=# BEGIN;
> > > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> > > > teste=# INSERT INTO ...
> > > > teste=# COMMIT;
> > > >
> > > > Well, I really think it is not the best way to do
> > > that
> > > > and I am asking you for advices!
> > > >
> > > > 1) Is it (... max(forn_id)... ) the best way to
> > > get
> > > > the next value to be inserted in the table?
> > > >
> > > > 2) Is there a automatic way to do that?
> > > >
> > > > Thanks in advance and
> > > > Best Regards,
> > > >
> > > > Marcelo
> > > >
> > > >
> > >
> > ______________________________________________________________________
> > > >
> > > > Yahoo! Mail: 6MB, anti-spam e antiv�rus gratuito!
> > > Crie sua conta agora:
> > > > http://mail.yahoo.com.br
> > > >
> > > > ---------------------------(end of
> > > broadcast)---------------------------
> > > > TIP 4: Don't 'kill -9' the postmaster
> > > >
> > > >
> > >
> >
> > ______________________________________________________________________
> >
> > Yahoo! Mail: 6MB, anti-spam e antiv�rus gratuito! Crie sua conta agora:
> > http://mail.yahoo.com.br
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
> But, how can I explain to the user, who use the
> sequence numbers, that he will have to handle with
> those holes?
If it's just hte user, you might try to make sure that there are ALWAYS
holes, so he doesn't get confused.
Jon
>
> Ok! I will try to handle the holes! (fight against the
> users)
>
> Thanks!
>
> Marcelo
>
>
> --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> Marcelo,
> >
> > You are asking for the impossible.
> >
> > In order for sequences to work reliably they have to
> > exist outside of a
> > transaction, and be atomic. If two transactions
> > asked for a sequence
> > simultaneously, what number would you give them? If
> > the first one gets
> > 1, and the second gets 2 how do you roll back the
> > first one and then
> > give the second one 1?
> >
> > And it gets worse, what happens if 10 connections
> > ask for one
> > simultaneously and then connection 3 7 rollback?
> >
> > I don't know how to say this gently, but usually
> > this requirement
> > suggests that more thinking is required on the
> > application end.
> >
> > Dave
> >
> >
> >
> > On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> > > Dave,
> > >
> > > I actually use just the sequence, as you wrote!
> > >
> > > The biggest problem it that I *can't* have holes
> > in
> > > that column, so it was because I used id (serial)
> > and
> > > forn_id (integer).
> > >
> > > All other tables use only the sequence by itself,
> > but
> > > this one, especially, CAN'T have holes! It is the
> > > problem!!! ;-)
> > >
> > > So, if I rollback or whatever, the ID will be
> > > populated with the sequence values, but the
> > forn_id
> > > must increase in a controled way, ie, without
> > holes!
> > >
> > > Advices??????
> > >
> > > Regards!
> > >
> > > Marcelo
> > >
> > > --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> > > Marceio
> > > >
> > > >
> > > >
> > > > The sequence logic takes care of it. try it
> > yourself
> > > >
> > > > open two connections with psql
> > > >
> > > > on one do a
> > > > begin;
> > > > insert into table
> > > > select curval('forn_id_seq');
> > > >
> > > > on the other
> > > >
> > > > do a
> > > > begin
> > > > insert into table
> > > > select curval('forn_id_seq');
> > > >
> > > >
> > > > You will see that they both increment the
> > sequence
> > > > number
> > > >
> > > > you will also see how to get the current value
> > as
> > > > well.
> > > >
> > > > Note, no locking is actually required, you can
> > do
> > > > this without the
> > > > transaction stuff, it is there just so you can
> > see
> > > > it in two sessions at
> > > > the same time.
> > > >
> > > > Also note that a rollback will NOT roll back the
> > > > sequence number, this
> > > > will end up with holes but sequences are not
> > > > guaranteed to not have
> > > > holes.
> > > >
> > > > Why do you have two columns, id, and forn_id,
> > you
> > > > only need one.
> > > >
> > > > and then do an
> > > >
> > > > insert into forn (descrip) values ( 'some
> > > > description' );
> > > > then select curval('forn_id_seq');
> > > >
> > > > forn_id will be populated for you with the value
> > > > from curval.
> > > >
> > > >
> > > > Dave
> > > >
> > > > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA
> > wrote:
> > > > > Hi guys,
> > > > >
> > > > > I have a simple table:
> > > > >
> > > > > teste=# \d forn
> > > > > Table "public.forn"
> > > > > Column | Type |
> > > > Modifiers
> > > > >
> > > >
> > >
> >
> ---------+---------+------------------------------------------------------
> > > > > id | integer | not null default
> > > > > nextval('public.forn_id_seq'::text)
> > > > > forn_id | integer |
> > > > > descrip | text |
> > > > >
> > > > > Ok! The forn_id is supposed to be sequencial
> > and
> > > > > without holes (if someone perform a DELETE or
> > > > UPDATE,
> > > > > so there will be a hole... no problem if the
> > hole
> > > > > happens in this case!).
> > > > >
> > > > > Well, to know the next value of the forn_id
> > > > column, it
> > > > > was planned to be done like this:
> > > > >
> > > > > teste=# INSERT INTO forn (forn_id,descrip)
> > VALUES
> > > > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > > > >
> > > > > It will cause a huge delay in case this table
> > > > became
> > > > > huge, because the forn_id isn't an indexed
> > column
> > > > (but
> > > > > I would index it! The problem I am talking
> > about
> > > > is
> > > > > ONLY about the sequence of numbers).
> > > > >
> > > > > As a way to be sure it will not another other
> > > > client
> > > > > getting the exact value as the max(forn_id),
> > there
> > > > was
> > > > > a dirty thing:
> > > > >
> > > > > teste=# BEGIN;
> > > > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE
> > MODE;
> > > > > teste=# INSERT INTO ...
> > > > > teste=# COMMIT;
> > > > >
> > > > > Well, I really think it is not the best way to
> > do
> > > > that
> > > > > and I am asking you for advices!
> > > > >
> > > > > 1) Is it (... max(forn_id)... ) the best way
> > to
> > > > get
> > > > > the next value to be inserted in the table?
> > > > >
> > > > > 2) Is there a automatic way to do that?
> > > > >
> > > > > Thanks in advance and
> > > > > Best Regards,
> > > > >
> > > > > Marcelo
> > > > >
> > > > >
> > > >
> > >
> >
> ______________________________________________________________________
> > > > >
> > > > > Yahoo! Mail: 6MB, anti-spam e antiv�rus
> > gratuito!
> > > > Crie sua conta agora:
> > > > > http://mail.yahoo.com.br
> > > > >
> >
> === message truncated ===
>
> Yahoo! Mail - 6MB, anti-spam e antiv�rus gratuito. Crie sua conta agora:
> http://mail.yahoo.com.br
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
MaRcElO PeReIrA wrote:
> Dave,
>
> I actually use just the sequence, as you wrote!
>
> The biggest problem it that I *can't* have holes in
> that column, so it was because I used id (serial) and
> forn_id (integer).
You could maintain some sort of systemnumber table yourself
create table sysnum (
first int not null,
next int not null,
last int not null
latest_updater text not null,
the_time timestamp? not null);
and get your serial number from the next column.
However, this strategy demands the same logic from
all programs using the table:
pseudo Ada code
loop
begin transaction
select * from sysnum into some Adarecord;
update sysnum
set next=next+1
latest_updater = The_pid_or_name_of_your_process_or_thread
the_time=now (with good enough acurracy)
where
latest_updater = Adarecord.latest_updater and
The_time = Adarecord.The_time;
if Rows_Affected = 0 then
Rollback transaction;
else
commit transaction:
exit
end if;
(perhaps a small delay, say 0.05 sec?)
end loop;
you can get Rows_affected from PQ_Cmd_Tuples
if Rows_affected is 0 then you have a transaction conflict,
and must start all over again, to get a unique value.
What this does to performance, I don't know, but I do know it works,
IF AND ONLY IF all processes follow the same rule.
There should proberly be some code to handle when
you fall over the edge, ie next > last => next = first
/Björn
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Monday 24 November 2003 08:01 am, Dave Cramer wrote:
> Marcelo,
>
> You are asking for the impossible.
>
> In order for sequences to work reliably they have to exist outside of a
> transaction, and be atomic. If two transactions asked for a sequence
> simultaneously, what number would you give them? If the first one gets
> 1, and the second gets 2 how do you roll back the first one and then
> give the second one 1?
>
> And it gets worse, what happens if 10 connections ask for one
> simultaneously and then connection 3 7 rollback?
>
> I don't know how to say this gently, but usually this requirement
> suggests that more thinking is required on the application end.
Well, there are cases where you have to have the numbers without holes - no
matter what. It's not even a matter of the application. Go check your
insurance policy: the policy numbers are sequential without holes. Actually
you can make that work via stored procedures. But you'd have to lock the
table exclusive to avoid duplicates. This still might produce numbering gaps,
but you can have the application compensate for that, i.e. if you have a
rollback remember the number someplace else and reuse it for the next record.
>
> Dave
>
> On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> > Dave,
> >
> > I actually use just the sequence, as you wrote!
> >
> > The biggest problem it that I *can't* have holes in
> > that column, so it was because I used id (serial) and
> > forn_id (integer).
> >
> > All other tables use only the sequence by itself, but
> > this one, especially, CAN'T have holes! It is the
> > problem!!! ;-)
> >
> > So, if I rollback or whatever, the ID will be
> > populated with the sequence values, but the forn_id
> > must increase in a controled way, ie, without holes!
> >
> > Advices??????
> >
> > Regards!
> >
> > Marcelo
> >
> > --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> > Marceio
> >
> > > The sequence logic takes care of it. try it yourself
> > >
> > > open two connections with psql
> > >
> > > on one do a
> > > begin;
> > > insert into table
> > > select curval('forn_id_seq');
> > >
> > > on the other
> > >
> > > do a
> > > begin
> > > insert into table
> > > select curval('forn_id_seq');
> > >
> > >
> > > You will see that they both increment the sequence
> > > number
> > >
> > > you will also see how to get the current value as
> > > well.
> > >
> > > Note, no locking is actually required, you can do
> > > this without the
> > > transaction stuff, it is there just so you can see
> > > it in two sessions at
> > > the same time.
> > >
> > > Also note that a rollback will NOT roll back the
> > > sequence number, this
> > > will end up with holes but sequences are not
> > > guaranteed to not have
> > > holes.
> > >
> > > Why do you have two columns, id, and forn_id, you
> > > only need one.
> > >
> > > and then do an
> > >
> > > insert into forn (descrip) values ( 'some
> > > description' );
> > > then select curval('forn_id_seq');
> > >
> > > forn_id will be populated for you with the value
> > > from curval.
> > >
> > >
> > > Dave
> > >
> > > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> > > > Hi guys,
> > > >
> > > > I have a simple table:
> > > >
> > > > teste=# \d forn
> > > > Table "public.forn"
> > > > Column | Type |
> > >
> > > Modifiers
> >
> > ---------+---------+-----------------------------------------------------
> >-
> >
> > > > id | integer | not null default
> > > > nextval('public.forn_id_seq'::text)
> > > > forn_id | integer |
> > > > descrip | text |
> > > >
> > > > Ok! The forn_id is supposed to be sequencial and
> > > > without holes (if someone perform a DELETE or
> > >
> > > UPDATE,
> > >
> > > > so there will be a hole... no problem if the hole
> > > > happens in this case!).
> > > >
> > > > Well, to know the next value of the forn_id
> > >
> > > column, it
> > >
> > > > was planned to be done like this:
> > > >
> > > > teste=# INSERT INTO forn (forn_id,descrip) VALUES
> > > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > > >
> > > > It will cause a huge delay in case this table
> > >
> > > became
> > >
> > > > huge, because the forn_id isn't an indexed column
> > >
> > > (but
> > >
> > > > I would index it! The problem I am talking about
> > >
> > > is
> > >
> > > > ONLY about the sequence of numbers).
> > > >
> > > > As a way to be sure it will not another other
> > >
> > > client
> > >
> > > > getting the exact value as the max(forn_id), there
> > >
> > > was
> > >
> > > > a dirty thing:
> > > >
> > > > teste=# BEGIN;
> > > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> > > > teste=# INSERT INTO ...
> > > > teste=# COMMIT;
> > > >
> > > > Well, I really think it is not the best way to do
> > >
> > > that
> > >
> > > > and I am asking you for advices!
> > > >
> > > > 1) Is it (... max(forn_id)... ) the best way to
> > >
> > > get
> > >
> > > > the next value to be inserted in the table?
> > > >
> > > > 2) Is there a automatic way to do that?
> > > >
> > > > Thanks in advance and
> > > > Best Regards,
> > > >
> > > > Marcelo
> >
> > ______________________________________________________________________
> >
> > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito!
> > >
> > > Crie sua conta agora:
> > > > http://mail.yahoo.com.br
> > > >
> > > > ---------------------------(end of
> > >
> > > broadcast)---------------------------
> > >
> > > > TIP 4: Don't 'kill -9' the postmaster
> >
> > ______________________________________________________________________
> >
> > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora:
> > http://mail.yahoo.com.br
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
- --
UC
- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/w0/bjqGXBvRToM4RAvZJAJ4980r/Cp+jWSTrHpq7kBRiPpUTIwCfcTUF
It3bBNKywCxc3FzOzr7FSyA=
=TSWf
-----END PGP SIGNATURE-----
Actually, in practice Policy & Certificate 'numbers' only need to be unique.
Insurance companies (at least those we deal with) have no restriction that
there can be no holes. In fact, one of our clients has a huge gap in the
sequence.
Likewise - they aren't usually strictly numeric, consisting of ALPHA and
NUMERIC components. Ie. AA000001 AA000002 ... AA999999 AB000001.
A better example - is Invoice Numbers. Accountants hate the gaps, since they
leave room for fraud and make collection difficult.
That said - our implementation for unique ids is either use sequences or to
encapsulate the logic in a Stored Proc. and ensure that these tranasactions are
fully isolated.
Marc A. Leith
redboxdata inc.
E-mail:mleith@redboxdata.com
Quoting "Uwe C. Schroeder" <uwe@oss4u.com>:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Monday 24 November 2003 08:01 am, Dave Cramer wrote:
> > Marcelo,
> >
> > You are asking for the impossible.
> >
> > In order for sequences to work reliably they have to exist outside of a
> > transaction, and be atomic. If two transactions asked for a sequence
> > simultaneously, what number would you give them? If the first one gets
> > 1, and the second gets 2 how do you roll back the first one and then
> > give the second one 1?
> >
> > And it gets worse, what happens if 10 connections ask for one
> > simultaneously and then connection 3 7 rollback?
> >
> > I don't know how to say this gently, but usually this requirement
> > suggests that more thinking is required on the application end.
>
> Well, there are cases where you have to have the numbers without holes - no
> matter what. It's not even a matter of the application. Go check your
> insurance policy: the policy numbers are sequential without holes. Actually
> you can make that work via stored procedures. But you'd have to lock the
> table exclusive to avoid duplicates. This still might produce numbering gaps,
>
> but you can have the application compensate for that, i.e. if you have a
> rollback remember the number someplace else and reuse it for the next
> record.
>
> >
> > Dave
> >
> > On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> > > Dave,
> > >
> > > I actually use just the sequence, as you wrote!
> > >
> > > The biggest problem it that I *can't* have holes in
> > > that column, so it was because I used id (serial) and
> > > forn_id (integer).
> > >
> > > All other tables use only the sequence by itself, but
> > > this one, especially, CAN'T have holes! It is the
> > > problem!!! ;-)
> > >
> > > So, if I rollback or whatever, the ID will be
> > > populated with the sequence values, but the forn_id
> > > must increase in a controled way, ie, without holes!
> > >
> > > Advices??????
> > >
> > > Regards!
> > >
> > > Marcelo
> > >
> > > --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> > > Marceio
> > >
> > > > The sequence logic takes care of it. try it yourself
> > > >
> > > > open two connections with psql
> > > >
> > > > on one do a
> > > > begin;
> > > > insert into table
> > > > select curval('forn_id_seq');
> > > >
> > > > on the other
> > > >
> > > > do a
> > > > begin
> > > > insert into table
> > > > select curval('forn_id_seq');
> > > >
> > > >
> > > > You will see that they both increment the sequence
> > > > number
> > > >
> > > > you will also see how to get the current value as
> > > > well.
> > > >
> > > > Note, no locking is actually required, you can do
> > > > this without the
> > > > transaction stuff, it is there just so you can see
> > > > it in two sessions at
> > > > the same time.
> > > >
> > > > Also note that a rollback will NOT roll back the
> > > > sequence number, this
> > > > will end up with holes but sequences are not
> > > > guaranteed to not have
> > > > holes.
> > > >
> > > > Why do you have two columns, id, and forn_id, you
> > > > only need one.
> > > >
> > > > and then do an
> > > >
> > > > insert into forn (descrip) values ( 'some
> > > > description' );
> > > > then select curval('forn_id_seq');
> > > >
> > > > forn_id will be populated for you with the value
> > > > from curval.
> > > >
> > > >
> > > > Dave
> > > >
> > > > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> > > > > Hi guys,
> > > > >
> > > > > I have a simple table:
> > > > >
> > > > > teste=# \d forn
> > > > > Table "public.forn"
> > > > > Column | Type |
> > > >
> > > > Modifiers
> > >
> > >
> ---------+---------+-----------------------------------------------------
> > >-
> > >
> > > > > id | integer | not null default
> > > > > nextval('public.forn_id_seq'::text)
> > > > > forn_id | integer |
> > > > > descrip | text |
> > > > >
> > > > > Ok! The forn_id is supposed to be sequencial and
> > > > > without holes (if someone perform a DELETE or
> > > >
> > > > UPDATE,
> > > >
> > > > > so there will be a hole... no problem if the hole
> > > > > happens in this case!).
> > > > >
> > > > > Well, to know the next value of the forn_id
> > > >
> > > > column, it
> > > >
> > > > > was planned to be done like this:
> > > > >
> > > > > teste=# INSERT INTO forn (forn_id,descrip) VALUES
> > > > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > > > >
> > > > > It will cause a huge delay in case this table
> > > >
> > > > became
> > > >
> > > > > huge, because the forn_id isn't an indexed column
> > > >
> > > > (but
> > > >
> > > > > I would index it! The problem I am talking about
> > > >
> > > > is
> > > >
> > > > > ONLY about the sequence of numbers).
> > > > >
> > > > > As a way to be sure it will not another other
> > > >
> > > > client
> > > >
> > > > > getting the exact value as the max(forn_id), there
> > > >
> > > > was
> > > >
> > > > > a dirty thing:
> > > > >
> > > > > teste=# BEGIN;
> > > > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> > > > > teste=# INSERT INTO ...
> > > > > teste=# COMMIT;
> > > > >
> > > > > Well, I really think it is not the best way to do
> > > >
> > > > that
> > > >
> > > > > and I am asking you for advices!
> > > > >
> > > > > 1) Is it (... max(forn_id)... ) the best way to
> > > >
> > > > get
> > > >
> > > > > the next value to be inserted in the table?
> > > > >
> > > > > 2) Is there a automatic way to do that?
> > > > >
> > > > > Thanks in advance and
> > > > > Best Regards,
> > > > >
> > > > > Marcelo
> > >
> > > ______________________________________________________________________
> > >
> > > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito!
> > > >
> > > > Crie sua conta agora:
> > > > > http://mail.yahoo.com.br
> > > > >
> > > > > ---------------------------(end of
> > > >
> > > > broadcast)---------------------------
> > > >
> > > > > TIP 4: Don't 'kill -9' the postmaster
> > >
> > > ______________________________________________________________________
> > >
> > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora:
> > > http://mail.yahoo.com.br
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> - --
> UC
>
> - --
> Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> Phone: +1 650 872 2425 San Bruno, CA 94066
> Cell: +1 650 302 2405 United States
> Fax: +1 650 872 2417
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (GNU/Linux)
>
> iD8DBQE/w0/bjqGXBvRToM4RAvZJAJ4980r/Cp+jWSTrHpq7kBRiPpUTIwCfcTUF
> It3bBNKywCxc3FzOzr7FSyA=
> =TSWf
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Obviously depends on the carrier. Lloyds for example doesn't allow numbering
gaps. But as said: doing it in a fully isolated stored proc usually works.
The stp I use also assembles the alpha part, so I end up with something like
AA-0001234 in a fixed width format.
On Tuesday 25 November 2003 08:40 am, Marc A. Leith wrote:
> Actually, in practice Policy & Certificate 'numbers' only need to be
> unique. Insurance companies (at least those we deal with) have no
> restriction that there can be no holes. In fact, one of our clients has a
> huge gap in the sequence.
>
> Likewise - they aren't usually strictly numeric, consisting of ALPHA and
> NUMERIC components. Ie. AA000001 AA000002 ... AA999999 AB000001.
>
> A better example - is Invoice Numbers. Accountants hate the gaps, since
> they leave room for fraud and make collection difficult.
>
> That said - our implementation for unique ids is either use sequences or to
> encapsulate the logic in a Stored Proc. and ensure that these tranasactions
> are fully isolated.
>
> Marc A. Leith
> redboxdata inc.
>
> E-mail:mleith@redboxdata.com
>
> Quoting "Uwe C. Schroeder" <uwe@oss4u.com>:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > On Monday 24 November 2003 08:01 am, Dave Cramer wrote:
> > > Marcelo,
> > >
> > > You are asking for the impossible.
> > >
> > > In order for sequences to work reliably they have to exist outside of a
> > > transaction, and be atomic. If two transactions asked for a sequence
> > > simultaneously, what number would you give them? If the first one gets
> > > 1, and the second gets 2 how do you roll back the first one and then
> > > give the second one 1?
> > >
> > > And it gets worse, what happens if 10 connections ask for one
> > > simultaneously and then connection 3 7 rollback?
> > >
> > > I don't know how to say this gently, but usually this requirement
> > > suggests that more thinking is required on the application end.
> >
> > Well, there are cases where you have to have the numbers without holes -
> > no matter what. It's not even a matter of the application. Go check your
> > insurance policy: the policy numbers are sequential without holes.
> > Actually you can make that work via stored procedures. But you'd have to
> > lock the table exclusive to avoid duplicates. This still might produce
> > numbering gaps,
> >
> > but you can have the application compensate for that, i.e. if you have a
> > rollback remember the number someplace else and reuse it for the next
> > record.
> >
> > > Dave
> > >
> > > On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> > > > Dave,
> > > >
> > > > I actually use just the sequence, as you wrote!
> > > >
> > > > The biggest problem it that I *can't* have holes in
> > > > that column, so it was because I used id (serial) and
> > > > forn_id (integer).
> > > >
> > > > All other tables use only the sequence by itself, but
> > > > this one, especially, CAN'T have holes! It is the
> > > > problem!!! ;-)
> > > >
> > > > So, if I rollback or whatever, the ID will be
> > > > populated with the sequence values, but the forn_id
> > > > must increase in a controled way, ie, without holes!
> > > >
> > > > Advices??????
> > > >
> > > > Regards!
> > > >
> > > > Marcelo
> > > >
> > > > --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> > > > Marceio
> > > >
> > > > > The sequence logic takes care of it. try it yourself
> > > > >
> > > > > open two connections with psql
> > > > >
> > > > > on one do a
> > > > > begin;
> > > > > insert into table
> > > > > select curval('forn_id_seq');
> > > > >
> > > > > on the other
> > > > >
> > > > > do a
> > > > > begin
> > > > > insert into table
> > > > > select curval('forn_id_seq');
> > > > >
> > > > >
> > > > > You will see that they both increment the sequence
> > > > > number
> > > > >
> > > > > you will also see how to get the current value as
> > > > > well.
> > > > >
> > > > > Note, no locking is actually required, you can do
> > > > > this without the
> > > > > transaction stuff, it is there just so you can see
> > > > > it in two sessions at
> > > > > the same time.
> > > > >
> > > > > Also note that a rollback will NOT roll back the
> > > > > sequence number, this
> > > > > will end up with holes but sequences are not
> > > > > guaranteed to not have
> > > > > holes.
> > > > >
> > > > > Why do you have two columns, id, and forn_id, you
> > > > > only need one.
> > > > >
> > > > > and then do an
> > > > >
> > > > > insert into forn (descrip) values ( 'some
> > > > > description' );
> > > > > then select curval('forn_id_seq');
> > > > >
> > > > > forn_id will be populated for you with the value
> > > > > from curval.
> > > > >
> > > > >
> > > > > Dave
> > > > >
> > > > > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> > > > > > Hi guys,
> > > > > >
> > > > > > I have a simple table:
> > > > > >
> > > > > > teste=# \d forn
> > > > > > Table "public.forn"
> > > > > > Column | Type |
> > > > >
> > > > > Modifiers
> >
> > ---------+---------+-----------------------------------------------------
> >
> > > >-
> > > >
> > > > > > id | integer | not null default
> > > > > > nextval('public.forn_id_seq'::text)
> > > > > > forn_id | integer |
> > > > > > descrip | text |
> > > > > >
> > > > > > Ok! The forn_id is supposed to be sequencial and
> > > > > > without holes (if someone perform a DELETE or
> > > > >
> > > > > UPDATE,
> > > > >
> > > > > > so there will be a hole... no problem if the hole
> > > > > > happens in this case!).
> > > > > >
> > > > > > Well, to know the next value of the forn_id
> > > > >
> > > > > column, it
> > > > >
> > > > > > was planned to be done like this:
> > > > > >
> > > > > > teste=# INSERT INTO forn (forn_id,descrip) VALUES
> > > > > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > > > > >
> > > > > > It will cause a huge delay in case this table
> > > > >
> > > > > became
> > > > >
> > > > > > huge, because the forn_id isn't an indexed column
> > > > >
> > > > > (but
> > > > >
> > > > > > I would index it! The problem I am talking about
> > > > >
> > > > > is
> > > > >
> > > > > > ONLY about the sequence of numbers).
> > > > > >
> > > > > > As a way to be sure it will not another other
> > > > >
> > > > > client
> > > > >
> > > > > > getting the exact value as the max(forn_id), there
> > > > >
> > > > > was
> > > > >
> > > > > > a dirty thing:
> > > > > >
> > > > > > teste=# BEGIN;
> > > > > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> > > > > > teste=# INSERT INTO ...
> > > > > > teste=# COMMIT;
> > > > > >
> > > > > > Well, I really think it is not the best way to do
> > > > >
> > > > > that
> > > > >
> > > > > > and I am asking you for advices!
> > > > > >
> > > > > > 1) Is it (... max(forn_id)... ) the best way to
> > > > >
> > > > > get
> > > > >
> > > > > > the next value to be inserted in the table?
> > > > > >
> > > > > > 2) Is there a automatic way to do that?
> > > > > >
> > > > > > Thanks in advance and
> > > > > > Best Regards,
> > > > > >
> > > > > > Marcelo
> > > >
> > > > _____________________________________________________________________
> > > >_
> > > >
> > > > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito!
> > > > >
> > > > > Crie sua conta agora:
> > > > > > http://mail.yahoo.com.br
> > > > > >
> > > > > > ---------------------------(end of
> > > > >
> > > > > broadcast)---------------------------
> > > > >
> > > > > > TIP 4: Don't 'kill -9' the postmaster
> > > >
> > > > _____________________________________________________________________
> > > >_
> > > >
> > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta
> > > > agora: http://mail.yahoo.com.br
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 4: Don't 'kill -9' the
> > > postmaster
> >
> > - --
> > UC
> >
> > - --
> > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> > Phone: +1 650 872 2425 San Bruno, CA 94066
> > Cell: +1 650 302 2405 United States
> > Fax: +1 650 872 2417
> > -----BEGIN PGP SIGNATURE-----
> > Version: GnuPG v1.2.1 (GNU/Linux)
> >
> > iD8DBQE/w0/bjqGXBvRToM4RAvZJAJ4980r/Cp+jWSTrHpq7kBRiPpUTIwCfcTUF
> > It3bBNKywCxc3FzOzr7FSyA=
> > =TSWf
> > -----END PGP SIGNATURE-----
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
- --
UC
- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/w+7EjqGXBvRToM4RAvyFAJ4m5WghKaTVdoUDBm4S56HhcgYlJACfVBiu
V/hGezsXsywrsaNdWvrzp1g=
=2S5x
-----END PGP SIGNATURE-----
How can you avoid holes?
Unless you void policies that people cancel halfway through the process
? How is that different than rollback?
Lets say that the customer goes through the motions and after signing
the papers, and then during the cooling off period (mandatory in Canada)
decides he really doesn't want the policy (rollback). A policy number
must have been assigned. So now we have a hole ?
Dave
On Tue, 2003-11-25 at 19:07, Uwe C. Schroeder wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Obviously depends on the carrier. Lloyds for example doesn't allow numbering
> gaps. But as said: doing it in a fully isolated stored proc usually works.
> The stp I use also assembles the alpha part, so I end up with something like
> AA-0001234 in a fixed width format.
>
>
> On Tuesday 25 November 2003 08:40 am, Marc A. Leith wrote:
> > Actually, in practice Policy & Certificate 'numbers' only need to be
> > unique. Insurance companies (at least those we deal with) have no
> > restriction that there can be no holes. In fact, one of our clients has a
> > huge gap in the sequence.
> >
> > Likewise - they aren't usually strictly numeric, consisting of ALPHA and
> > NUMERIC components. Ie. AA000001 AA000002 ... AA999999 AB000001.
> >
> > A better example - is Invoice Numbers. Accountants hate the gaps, since
> > they leave room for fraud and make collection difficult.
> >
> > That said - our implementation for unique ids is either use sequences or to
> > encapsulate the logic in a Stored Proc. and ensure that these tranasactions
> > are fully isolated.
> >
> > Marc A. Leith
> > redboxdata inc.
> >
> > E-mail:mleith@redboxdata.com
> >
> > Quoting "Uwe C. Schroeder" <uwe@oss4u.com>:
> > > -----BEGIN PGP SIGNED MESSAGE-----
> > > Hash: SHA1
> > >
> > > On Monday 24 November 2003 08:01 am, Dave Cramer wrote:
> > > > Marcelo,
> > > >
> > > > You are asking for the impossible.
> > > >
> > > > In order for sequences to work reliably they have to exist outside of a
> > > > transaction, and be atomic. If two transactions asked for a sequence
> > > > simultaneously, what number would you give them? If the first one gets
> > > > 1, and the second gets 2 how do you roll back the first one and then
> > > > give the second one 1?
> > > >
> > > > And it gets worse, what happens if 10 connections ask for one
> > > > simultaneously and then connection 3 7 rollback?
> > > >
> > > > I don't know how to say this gently, but usually this requirement
> > > > suggests that more thinking is required on the application end.
> > >
> > > Well, there are cases where you have to have the numbers without holes -
> > > no matter what. It's not even a matter of the application. Go check your
> > > insurance policy: the policy numbers are sequential without holes.
> > > Actually you can make that work via stored procedures. But you'd have to
> > > lock the table exclusive to avoid duplicates. This still might produce
> > > numbering gaps,
> > >
> > > but you can have the application compensate for that, i.e. if you have a
> > > rollback remember the number someplace else and reuse it for the next
> > > record.
> > >
> > > > Dave
> > > >
> > > > On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> > > > > Dave,
> > > > >
> > > > > I actually use just the sequence, as you wrote!
> > > > >
> > > > > The biggest problem it that I *can't* have holes in
> > > > > that column, so it was because I used id (serial) and
> > > > > forn_id (integer).
> > > > >
> > > > > All other tables use only the sequence by itself, but
> > > > > this one, especially, CAN'T have holes! It is the
> > > > > problem!!! ;-)
> > > > >
> > > > > So, if I rollback or whatever, the ID will be
> > > > > populated with the sequence values, but the forn_id
> > > > > must increase in a controled way, ie, without holes!
> > > > >
> > > > > Advices??????
> > > > >
> > > > > Regards!
> > > > >
> > > > > Marcelo
> > > > >
> > > > > --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> > > > > Marceio
> > > > >
> > > > > > The sequence logic takes care of it. try it yourself
> > > > > >
> > > > > > open two connections with psql
> > > > > >
> > > > > > on one do a
> > > > > > begin;
> > > > > > insert into table
> > > > > > select curval('forn_id_seq');
> > > > > >
> > > > > > on the other
> > > > > >
> > > > > > do a
> > > > > > begin
> > > > > > insert into table
> > > > > > select curval('forn_id_seq');
> > > > > >
> > > > > >
> > > > > > You will see that they both increment the sequence
> > > > > > number
> > > > > >
> > > > > > you will also see how to get the current value as
> > > > > > well.
> > > > > >
> > > > > > Note, no locking is actually required, you can do
> > > > > > this without the
> > > > > > transaction stuff, it is there just so you can see
> > > > > > it in two sessions at
> > > > > > the same time.
> > > > > >
> > > > > > Also note that a rollback will NOT roll back the
> > > > > > sequence number, this
> > > > > > will end up with holes but sequences are not
> > > > > > guaranteed to not have
> > > > > > holes.
> > > > > >
> > > > > > Why do you have two columns, id, and forn_id, you
> > > > > > only need one.
> > > > > >
> > > > > > and then do an
> > > > > >
> > > > > > insert into forn (descrip) values ( 'some
> > > > > > description' );
> > > > > > then select curval('forn_id_seq');
> > > > > >
> > > > > > forn_id will be populated for you with the value
> > > > > > from curval.
> > > > > >
> > > > > >
> > > > > > Dave
> > > > > >
> > > > > > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> > > > > > > Hi guys,
> > > > > > >
> > > > > > > I have a simple table:
> > > > > > >
> > > > > > > teste=# \d forn
> > > > > > > Table "public.forn"
> > > > > > > Column | Type |
> > > > > >
> > > > > > Modifiers
> > >
> > > ---------+---------+-----------------------------------------------------
> > >
> > > > >-
> > > > >
> > > > > > > id | integer | not null default
> > > > > > > nextval('public.forn_id_seq'::text)
> > > > > > > forn_id | integer |
> > > > > > > descrip | text |
> > > > > > >
> > > > > > > Ok! The forn_id is supposed to be sequencial and
> > > > > > > without holes (if someone perform a DELETE or
> > > > > >
> > > > > > UPDATE,
> > > > > >
> > > > > > > so there will be a hole... no problem if the hole
> > > > > > > happens in this case!).
> > > > > > >
> > > > > > > Well, to know the next value of the forn_id
> > > > > >
> > > > > > column, it
> > > > > >
> > > > > > > was planned to be done like this:
> > > > > > >
> > > > > > > teste=# INSERT INTO forn (forn_id,descrip) VALUES
> > > > > > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > > > > > >
> > > > > > > It will cause a huge delay in case this table
> > > > > >
> > > > > > became
> > > > > >
> > > > > > > huge, because the forn_id isn't an indexed column
> > > > > >
> > > > > > (but
> > > > > >
> > > > > > > I would index it! The problem I am talking about
> > > > > >
> > > > > > is
> > > > > >
> > > > > > > ONLY about the sequence of numbers).
> > > > > > >
> > > > > > > As a way to be sure it will not another other
> > > > > >
> > > > > > client
> > > > > >
> > > > > > > getting the exact value as the max(forn_id), there
> > > > > >
> > > > > > was
> > > > > >
> > > > > > > a dirty thing:
> > > > > > >
> > > > > > > teste=# BEGIN;
> > > > > > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> > > > > > > teste=# INSERT INTO ...
> > > > > > > teste=# COMMIT;
> > > > > > >
> > > > > > > Well, I really think it is not the best way to do
> > > > > >
> > > > > > that
> > > > > >
> > > > > > > and I am asking you for advices!
> > > > > > >
> > > > > > > 1) Is it (... max(forn_id)... ) the best way to
> > > > > >
> > > > > > get
> > > > > >
> > > > > > > the next value to be inserted in the table?
> > > > > > >
> > > > > > > 2) Is there a automatic way to do that?
> > > > > > >
> > > > > > > Thanks in advance and
> > > > > > > Best Regards,
> > > > > > >
> > > > > > > Marcelo
> > > > >
> > > > > _____________________________________________________________________
> > > > >_
> > > > >
> > > > > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito!
> > > > > >
> > > > > > Crie sua conta agora:
> > > > > > > http://mail.yahoo.com.br
> > > > > > >
> > > > > > > ---------------------------(end of
> > > > > >
> > > > > > broadcast)---------------------------
> > > > > >
> > > > > > > TIP 4: Don't 'kill -9' the postmaster
> > > > >
> > > > > _____________________________________________________________________
> > > > >_
> > > > >
> > > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta
> > > > > agora: http://mail.yahoo.com.br
> > > >
> > > > ---------------------------(end of
> > > > broadcast)--------------------------- TIP 4: Don't 'kill -9' the
> > > > postmaster
> > >
> > > - --
> > > UC
> > >
> > > - --
> > > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> > > Phone: +1 650 872 2425 San Bruno, CA 94066
> > > Cell: +1 650 302 2405 United States
> > > Fax: +1 650 872 2417
> > > -----BEGIN PGP SIGNATURE-----
> > > Version: GnuPG v1.2.1 (GNU/Linux)
> > >
> > > iD8DBQE/w0/bjqGXBvRToM4RAvZJAJ4980r/Cp+jWSTrHpq7kBRiPpUTIwCfcTUF
> > > It3bBNKywCxc3FzOzr7FSyA=
> > > =TSWf
> > > -----END PGP SIGNATURE-----
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> - --
> UC
>
> - --
> Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> Phone: +1 650 872 2425 San Bruno, CA 94066
> Cell: +1 650 302 2405 United States
> Fax: +1 650 872 2417
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (GNU/Linux)
>
> iD8DBQE/w+7EjqGXBvRToM4RAvyFAJ4m5WghKaTVdoUDBm4S56HhcgYlJACfVBiu
> V/hGezsXsywrsaNdWvrzp1g=
> =2S5x
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
It seems to me there is a confusion about identifiers. There is the primary
key of the table which should be a sequence and may have holes. Seperate
from that is the CustomerFriendlyID which is an ID you can assign and
reassign at your leasure. For a bank, the statement numbers all start from one
for each customer so they're not useful as a global identifier anyway.
In your case below, once you've signed the paper-work, the policy is a legal
document and would need to be kept even if it never was activated.
IMHO, most people looking for no-hole-sequences are using the primary keys
for Bad Things (tm).
Hope this helps,
On Tue, Nov 25, 2003 at 10:19:20PM -0500, Dave Cramer wrote:
> How can you avoid holes?
>
> Unless you void policies that people cancel halfway through the process
> ? How is that different than rollback?
>
> Lets say that the customer goes through the motions and after signing
> the papers, and then during the cooling off period (mandatory in Canada)
> decides he really doesn't want the policy (rollback). A policy number
> must have been assigned. So now we have a hole ?
>
> Dave
>
> On Tue, 2003-11-25 at 19:07, Uwe C. Schroeder wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> >
> > Obviously depends on the carrier. Lloyds for example doesn't allow numbering
> > gaps. But as said: doing it in a fully isolated stored proc usually works.
> > The stp I use also assembles the alpha part, so I end up with something like
> > AA-0001234 in a fixed width format.
> >
> >
> > On Tuesday 25 November 2003 08:40 am, Marc A. Leith wrote:
> > > Actually, in practice Policy & Certificate 'numbers' only need to be
> > > unique. Insurance companies (at least those we deal with) have no
> > > restriction that there can be no holes. In fact, one of our clients has a
> > > huge gap in the sequence.
> > >
> > > Likewise - they aren't usually strictly numeric, consisting of ALPHA and
> > > NUMERIC components. Ie. AA000001 AA000002 ... AA999999 AB000001.
> > >
> > > A better example - is Invoice Numbers. Accountants hate the gaps, since
> > > they leave room for fraud and make collection difficult.
> > >
> > > That said - our implementation for unique ids is either use sequences or to
> > > encapsulate the logic in a Stored Proc. and ensure that these tranasactions
> > > are fully isolated.
> > >
> > > Marc A. Leith
> > > redboxdata inc.
> > >
> > > E-mail:mleith@redboxdata.com
> > >
> > > Quoting "Uwe C. Schroeder" <uwe@oss4u.com>:
> > > > -----BEGIN PGP SIGNED MESSAGE-----
> > > > Hash: SHA1
> > > >
> > > > On Monday 24 November 2003 08:01 am, Dave Cramer wrote:
> > > > > Marcelo,
> > > > >
> > > > > You are asking for the impossible.
> > > > >
> > > > > In order for sequences to work reliably they have to exist outside of a
> > > > > transaction, and be atomic. If two transactions asked for a sequence
> > > > > simultaneously, what number would you give them? If the first one gets
> > > > > 1, and the second gets 2 how do you roll back the first one and then
> > > > > give the second one 1?
> > > > >
> > > > > And it gets worse, what happens if 10 connections ask for one
> > > > > simultaneously and then connection 3 7 rollback?
> > > > >
> > > > > I don't know how to say this gently, but usually this requirement
> > > > > suggests that more thinking is required on the application end.
> > > >
> > > > Well, there are cases where you have to have the numbers without holes -
> > > > no matter what. It's not even a matter of the application. Go check your
> > > > insurance policy: the policy numbers are sequential without holes.
> > > > Actually you can make that work via stored procedures. But you'd have to
> > > > lock the table exclusive to avoid duplicates. This still might produce
> > > > numbering gaps,
> > > >
> > > > but you can have the application compensate for that, i.e. if you have a
> > > > rollback remember the number someplace else and reuse it for the next
> > > > record.
> > > >
> > > > > Dave
> > > > >
> > > > > On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> > > > > > Dave,
> > > > > >
> > > > > > I actually use just the sequence, as you wrote!
> > > > > >
> > > > > > The biggest problem it that I *can't* have holes in
> > > > > > that column, so it was because I used id (serial) and
> > > > > > forn_id (integer).
> > > > > >
> > > > > > All other tables use only the sequence by itself, but
> > > > > > this one, especially, CAN'T have holes! It is the
> > > > > > problem!!! ;-)
> > > > > >
> > > > > > So, if I rollback or whatever, the ID will be
> > > > > > populated with the sequence values, but the forn_id
> > > > > > must increase in a controled way, ie, without holes!
> > > > > >
> > > > > > Advices??????
> > > > > >
> > > > > > Regards!
> > > > > >
> > > > > > Marcelo
> > > > > >
> > > > > > --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> > > > > > Marceio
> > > > > >
> > > > > > > The sequence logic takes care of it. try it yourself
> > > > > > >
> > > > > > > open two connections with psql
> > > > > > >
> > > > > > > on one do a
> > > > > > > begin;
> > > > > > > insert into table
> > > > > > > select curval('forn_id_seq');
> > > > > > >
> > > > > > > on the other
> > > > > > >
> > > > > > > do a
> > > > > > > begin
> > > > > > > insert into table
> > > > > > > select curval('forn_id_seq');
> > > > > > >
> > > > > > >
> > > > > > > You will see that they both increment the sequence
> > > > > > > number
> > > > > > >
> > > > > > > you will also see how to get the current value as
> > > > > > > well.
> > > > > > >
> > > > > > > Note, no locking is actually required, you can do
> > > > > > > this without the
> > > > > > > transaction stuff, it is there just so you can see
> > > > > > > it in two sessions at
> > > > > > > the same time.
> > > > > > >
> > > > > > > Also note that a rollback will NOT roll back the
> > > > > > > sequence number, this
> > > > > > > will end up with holes but sequences are not
> > > > > > > guaranteed to not have
> > > > > > > holes.
> > > > > > >
> > > > > > > Why do you have two columns, id, and forn_id, you
> > > > > > > only need one.
> > > > > > >
> > > > > > > and then do an
> > > > > > >
> > > > > > > insert into forn (descrip) values ( 'some
> > > > > > > description' );
> > > > > > > then select curval('forn_id_seq');
> > > > > > >
> > > > > > > forn_id will be populated for you with the value
> > > > > > > from curval.
> > > > > > >
> > > > > > >
> > > > > > > Dave
> > > > > > >
> > > > > > > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> > > > > > > > Hi guys,
> > > > > > > >
> > > > > > > > I have a simple table:
> > > > > > > >
> > > > > > > > teste=# \d forn
> > > > > > > > Table "public.forn"
> > > > > > > > Column | Type |
> > > > > > >
> > > > > > > Modifiers
> > > >
> > > > ---------+---------+-----------------------------------------------------
> > > >
> > > > > >-
> > > > > >
> > > > > > > > id | integer | not null default
> > > > > > > > nextval('public.forn_id_seq'::text)
> > > > > > > > forn_id | integer |
> > > > > > > > descrip | text |
> > > > > > > >
> > > > > > > > Ok! The forn_id is supposed to be sequencial and
> > > > > > > > without holes (if someone perform a DELETE or
> > > > > > >
> > > > > > > UPDATE,
> > > > > > >
> > > > > > > > so there will be a hole... no problem if the hole
> > > > > > > > happens in this case!).
> > > > > > > >
> > > > > > > > Well, to know the next value of the forn_id
> > > > > > >
> > > > > > > column, it
> > > > > > >
> > > > > > > > was planned to be done like this:
> > > > > > > >
> > > > > > > > teste=# INSERT INTO forn (forn_id,descrip) VALUES
> > > > > > > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > > > > > > >
> > > > > > > > It will cause a huge delay in case this table
> > > > > > >
> > > > > > > became
> > > > > > >
> > > > > > > > huge, because the forn_id isn't an indexed column
> > > > > > >
> > > > > > > (but
> > > > > > >
> > > > > > > > I would index it! The problem I am talking about
> > > > > > >
> > > > > > > is
> > > > > > >
> > > > > > > > ONLY about the sequence of numbers).
> > > > > > > >
> > > > > > > > As a way to be sure it will not another other
> > > > > > >
> > > > > > > client
> > > > > > >
> > > > > > > > getting the exact value as the max(forn_id), there
> > > > > > >
> > > > > > > was
> > > > > > >
> > > > > > > > a dirty thing:
> > > > > > > >
> > > > > > > > teste=# BEGIN;
> > > > > > > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> > > > > > > > teste=# INSERT INTO ...
> > > > > > > > teste=# COMMIT;
> > > > > > > >
> > > > > > > > Well, I really think it is not the best way to do
> > > > > > >
> > > > > > > that
> > > > > > >
> > > > > > > > and I am asking you for advices!
> > > > > > > >
> > > > > > > > 1) Is it (... max(forn_id)... ) the best way to
> > > > > > >
> > > > > > > get
> > > > > > >
> > > > > > > > the next value to be inserted in the table?
> > > > > > > >
> > > > > > > > 2) Is there a automatic way to do that?
> > > > > > > >
> > > > > > > > Thanks in advance and
> > > > > > > > Best Regards,
> > > > > > > >
> > > > > > > > Marcelo
> > > > > >
> > > > > > _____________________________________________________________________
> > > > > >_
> > > > > >
> > > > > > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito!
> > > > > > >
> > > > > > > Crie sua conta agora:
> > > > > > > > http://mail.yahoo.com.br
> > > > > > > >
> > > > > > > > ---------------------------(end of
> > > > > > >
> > > > > > > broadcast)---------------------------
> > > > > > >
> > > > > > > > TIP 4: Don't 'kill -9' the postmaster
> > > > > >
> > > > > > _____________________________________________________________________
> > > > > >_
> > > > > >
> > > > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta
> > > > > > agora: http://mail.yahoo.com.br
> > > > >
> > > > > ---------------------------(end of
> > > > > broadcast)--------------------------- TIP 4: Don't 'kill -9' the
> > > > > postmaster
> > > >
> > > > - --
> > > > UC
> > > >
> > > > - --
> > > > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> > > > Phone: +1 650 872 2425 San Bruno, CA 94066
> > > > Cell: +1 650 302 2405 United States
> > > > Fax: +1 650 872 2417
> > > > -----BEGIN PGP SIGNATURE-----
> > > > Version: GnuPG v1.2.1 (GNU/Linux)
> > > >
> > > > iD8DBQE/w0/bjqGXBvRToM4RAvZJAJ4980r/Cp+jWSTrHpq7kBRiPpUTIwCfcTUF
> > > > It3bBNKywCxc3FzOzr7FSyA=
> > > > =TSWf
> > > > -----END PGP SIGNATURE-----
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 2: you can get off all lists at once with the unregister command
> > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> > - --
> > UC
> >
> > - --
> > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> > Phone: +1 650 872 2425 San Bruno, CA 94066
> > Cell: +1 650 302 2405 United States
> > Fax: +1 650 872 2417
> > -----BEGIN PGP SIGNATURE-----
> > Version: GnuPG v1.2.1 (GNU/Linux)
> >
> > iD8DBQE/w+7EjqGXBvRToM4RAvyFAJ4m5WghKaTVdoUDBm4S56HhcgYlJACfVBiu
> > V/hGezsXsywrsaNdWvrzp1g=
> > =2S5x
> > -----END PGP SIGNATURE-----
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato
Attachment
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Actually in this case you don't have a hole.
Yes you created the next policy (in this case, may be any similar situation).
But the customer already signed the contract. This means even if he opts out
of it, a record has to be kept. In some areas this is even a legal
requirement. So you don't rollback, you just mark the record as "inactive"
"cancelled" or whatever the situation stipulates. In case of a commercial
insurance policy in California most likely the customer would have to pay a
penalty - usually a percentage of the premium - so you have to keep the
record. You also may want to keep the record to deny further requests from
this customer, or to simply warn the customer rep that this customer is a
"drop out" type.
So it's not a gap in the numbering. As Martijn already pointed out: don't
confuse this with an internal record sequence, which you should never ever
give to the "user" as an id or something. The requirement for record
sequences (usually the primary key or part of it) is uniqueness.
On Tuesday 25 November 2003 07:19 pm, Dave Cramer wrote:
> How can you avoid holes?
>
> Unless you void policies that people cancel halfway through the process
> ? How is that different than rollback?
>
> Lets say that the customer goes through the motions and after signing
> the papers, and then during the cooling off period (mandatory in Canada)
> decides he really doesn't want the policy (rollback). A policy number
> must have been assigned. So now we have a hole ?
>
> Dave
>
> On Tue, 2003-11-25 at 19:07, Uwe C. Schroeder wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> >
> > Obviously depends on the carrier. Lloyds for example doesn't allow
> > numbering gaps. But as said: doing it in a fully isolated stored proc
> > usually works. The stp I use also assembles the alpha part, so I end up
> > with something like AA-0001234 in a fixed width format.
> >
> > On Tuesday 25 November 2003 08:40 am, Marc A. Leith wrote:
> > > Actually, in practice Policy & Certificate 'numbers' only need to be
> > > unique. Insurance companies (at least those we deal with) have no
> > > restriction that there can be no holes. In fact, one of our clients has
> > > a huge gap in the sequence.
> > >
> > > Likewise - they aren't usually strictly numeric, consisting of ALPHA
> > > and NUMERIC components. Ie. AA000001 AA000002 ... AA999999 AB000001.
> > >
> > > A better example - is Invoice Numbers. Accountants hate the gaps, since
> > > they leave room for fraud and make collection difficult.
> > >
> > > That said - our implementation for unique ids is either use sequences
> > > or to encapsulate the logic in a Stored Proc. and ensure that these
> > > tranasactions are fully isolated.
> > >
> > > Marc A. Leith
> > > redboxdata inc.
> > >
> > > E-mail:mleith@redboxdata.com
> > >
> > > Quoting "Uwe C. Schroeder" <uwe@oss4u.com>:
> > > > -----BEGIN PGP SIGNED MESSAGE-----
> > > > Hash: SHA1
> > > >
> > > > On Monday 24 November 2003 08:01 am, Dave Cramer wrote:
> > > > > Marcelo,
> > > > >
> > > > > You are asking for the impossible.
> > > > >
> > > > > In order for sequences to work reliably they have to exist outside
> > > > > of a transaction, and be atomic. If two transactions asked for a
> > > > > sequence simultaneously, what number would you give them? If the
> > > > > first one gets 1, and the second gets 2 how do you roll back the
> > > > > first one and then give the second one 1?
> > > > >
> > > > > And it gets worse, what happens if 10 connections ask for one
> > > > > simultaneously and then connection 3 7 rollback?
> > > > >
> > > > > I don't know how to say this gently, but usually this requirement
> > > > > suggests that more thinking is required on the application end.
> > > >
> > > > Well, there are cases where you have to have the numbers without
> > > > holes - no matter what. It's not even a matter of the application. Go
> > > > check your insurance policy: the policy numbers are sequential
> > > > without holes. Actually you can make that work via stored procedures.
> > > > But you'd have to lock the table exclusive to avoid duplicates. This
> > > > still might produce numbering gaps,
> > > >
> > > > but you can have the application compensate for that, i.e. if you
> > > > have a rollback remember the number someplace else and reuse it for
> > > > the next record.
> > > >
> > > > > Dave
> > > > >
> > > > > On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> > > > > > Dave,
> > > > > >
> > > > > > I actually use just the sequence, as you wrote!
> > > > > >
> > > > > > The biggest problem it that I *can't* have holes in
> > > > > > that column, so it was because I used id (serial) and
> > > > > > forn_id (integer).
> > > > > >
> > > > > > All other tables use only the sequence by itself, but
> > > > > > this one, especially, CAN'T have holes! It is the
> > > > > > problem!!! ;-)
> > > > > >
> > > > > > So, if I rollback or whatever, the ID will be
> > > > > > populated with the sequence values, but the forn_id
> > > > > > must increase in a controled way, ie, without holes!
> > > > > >
> > > > > > Advices??????
> > > > > >
> > > > > > Regards!
> > > > > >
> > > > > > Marcelo
> > > > > >
> > > > > > --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> > > > > > Marceio
> > > > > >
> > > > > > > The sequence logic takes care of it. try it yourself
> > > > > > >
> > > > > > > open two connections with psql
> > > > > > >
> > > > > > > on one do a
> > > > > > > begin;
> > > > > > > insert into table
> > > > > > > select curval('forn_id_seq');
> > > > > > >
> > > > > > > on the other
> > > > > > >
> > > > > > > do a
> > > > > > > begin
> > > > > > > insert into table
> > > > > > > select curval('forn_id_seq');
> > > > > > >
> > > > > > >
> > > > > > > You will see that they both increment the sequence
> > > > > > > number
> > > > > > >
> > > > > > > you will also see how to get the current value as
> > > > > > > well.
> > > > > > >
> > > > > > > Note, no locking is actually required, you can do
> > > > > > > this without the
> > > > > > > transaction stuff, it is there just so you can see
> > > > > > > it in two sessions at
> > > > > > > the same time.
> > > > > > >
> > > > > > > Also note that a rollback will NOT roll back the
> > > > > > > sequence number, this
> > > > > > > will end up with holes but sequences are not
> > > > > > > guaranteed to not have
> > > > > > > holes.
> > > > > > >
> > > > > > > Why do you have two columns, id, and forn_id, you
> > > > > > > only need one.
> > > > > > >
> > > > > > > and then do an
> > > > > > >
> > > > > > > insert into forn (descrip) values ( 'some
> > > > > > > description' );
> > > > > > > then select curval('forn_id_seq');
> > > > > > >
> > > > > > > forn_id will be populated for you with the value
> > > > > > > from curval.
> > > > > > >
> > > > > > >
> > > > > > > Dave
> > > > > > >
> > > > > > > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> > > > > > > > Hi guys,
> > > > > > > >
> > > > > > > > I have a simple table:
> > > > > > > >
> > > > > > > > teste=# \d forn
> > > > > > > > Table "public.forn"
> > > > > > > > Column | Type |
> > > > > > >
> > > > > > > Modifiers
> > > >
> > > > ---------+---------+-------------------------------------------------
> > > >----
> > > >
> > > > > >-
> > > > > >
> > > > > > > > id | integer | not null default
> > > > > > > > nextval('public.forn_id_seq'::text)
> > > > > > > > forn_id | integer |
> > > > > > > > descrip | text |
> > > > > > > >
> > > > > > > > Ok! The forn_id is supposed to be sequencial and
> > > > > > > > without holes (if someone perform a DELETE or
> > > > > > >
> > > > > > > UPDATE,
> > > > > > >
> > > > > > > > so there will be a hole... no problem if the hole
> > > > > > > > happens in this case!).
> > > > > > > >
> > > > > > > > Well, to know the next value of the forn_id
> > > > > > >
> > > > > > > column, it
> > > > > > >
> > > > > > > > was planned to be done like this:
> > > > > > > >
> > > > > > > > teste=# INSERT INTO forn (forn_id,descrip) VALUES
> > > > > > > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > > > > > > >
> > > > > > > > It will cause a huge delay in case this table
> > > > > > >
> > > > > > > became
> > > > > > >
> > > > > > > > huge, because the forn_id isn't an indexed column
> > > > > > >
> > > > > > > (but
> > > > > > >
> > > > > > > > I would index it! The problem I am talking about
> > > > > > >
> > > > > > > is
> > > > > > >
> > > > > > > > ONLY about the sequence of numbers).
> > > > > > > >
> > > > > > > > As a way to be sure it will not another other
> > > > > > >
> > > > > > > client
> > > > > > >
> > > > > > > > getting the exact value as the max(forn_id), there
> > > > > > >
> > > > > > > was
> > > > > > >
> > > > > > > > a dirty thing:
> > > > > > > >
> > > > > > > > teste=# BEGIN;
> > > > > > > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> > > > > > > > teste=# INSERT INTO ...
> > > > > > > > teste=# COMMIT;
> > > > > > > >
> > > > > > > > Well, I really think it is not the best way to do
> > > > > > >
> > > > > > > that
> > > > > > >
> > > > > > > > and I am asking you for advices!
> > > > > > > >
> > > > > > > > 1) Is it (... max(forn_id)... ) the best way to
> > > > > > >
> > > > > > > get
> > > > > > >
> > > > > > > > the next value to be inserted in the table?
> > > > > > > >
> > > > > > > > 2) Is there a automatic way to do that?
> > > > > > > >
> > > > > > > > Thanks in advance and
> > > > > > > > Best Regards,
> > > > > > > >
> > > > > > > > Marcelo
> > > > > >
> > > > > > _________________________________________________________________
> > > > > >____ _
> > > > > >
> > > > > > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito!
> > > > > > >
> > > > > > > Crie sua conta agora:
> > > > > > > > http://mail.yahoo.com.br
> > > > > > > >
> > > > > > > > ---------------------------(end of
> > > > > > >
> > > > > > > broadcast)---------------------------
> > > > > > >
> > > > > > > > TIP 4: Don't 'kill -9' the postmaster
> > > > > >
> > > > > > _________________________________________________________________
> > > > > >____ _
> > > > > >
> > > > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta
> > > > > > agora: http://mail.yahoo.com.br
> > > > >
> > > > > ---------------------------(end of
> > > > > broadcast)--------------------------- TIP 4: Don't 'kill -9' the
> > > > > postmaster
> > > >
> > > > - --
> > > > UC
> > > >
> > > > - --
> > > > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> > > > Phone: +1 650 872 2425 San Bruno, CA 94066
> > > > Cell: +1 650 302 2405 United States
> > > > Fax: +1 650 872 2417
> > > > -----BEGIN PGP SIGNATURE-----
> > > > Version: GnuPG v1.2.1 (GNU/Linux)
> > > >
> > > > iD8DBQE/w0/bjqGXBvRToM4RAvZJAJ4980r/Cp+jWSTrHpq7kBRiPpUTIwCfcTUF
> > > > It3bBNKywCxc3FzOzr7FSyA=
> > > > =TSWf
> > > > -----END PGP SIGNATURE-----
> > > >
> > > >
> > > > ---------------------------(end of
> > > > broadcast)--------------------------- TIP 2: you can get off all
> > > > lists at once with the unregister command (send "unregister
> > > > YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 1: subscribe and unsubscribe
> > > commands go to majordomo@postgresql.org
> >
> > - --
> > UC
> >
> > - --
> > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> > Phone: +1 650 872 2425 San Bruno, CA 94066
> > Cell: +1 650 302 2405 United States
> > Fax: +1 650 872 2417
> > -----BEGIN PGP SIGNATURE-----
> > Version: GnuPG v1.2.1 (GNU/Linux)
> >
> > iD8DBQE/w+7EjqGXBvRToM4RAvyFAJ4m5WghKaTVdoUDBm4S56HhcgYlJACfVBiu
> > V/hGezsXsywrsaNdWvrzp1g=
> > =2S5x
> > -----END PGP SIGNATURE-----
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
- --
UC
- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/xCOfjqGXBvRToM4RAmjxAKCkxrJgj1M02lcnC+9+yogKf/CZrgCgv3Zv
8oW4A4b6Xe6QFGaZSxx0HgE=
=Mx7j
-----END PGP SIGNATURE-----