Re: Lock strategies!

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
>