Re: Lock strategies! - Mailing list pgsql-general

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