Re: Lock strategies! - Mailing list pgsql-general

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