Re: Lock strategies! - Mailing list pgsql-general

> 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
>