Re: Lock strategies! - Mailing list pgsql-general

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