From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Marcelo Pereira <gandalf(at)sum(dot)desktop(dot)com(dot)br> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Auto Increase |
Date: | 2002-01-24 11:59:22 |
Message-ID: | 3C4FF71A.48BCEBA5@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Marcelo Pereira wrote:
>
> Hello All,
>
> I am building a database and I am in troubles to get a field
> autonumbering itself.
...
> How can I create a field that avoid this action? I would like the field
> local_cod had the sequence:
>
> 1,2,3,4,5,6,7,8,9,10,...,N,...
>
> ...whatever happens while inserting tupples.
You can't. When transactions are rolled back, sequence values are not
reset. Imagine the following scenario:
Transaction #1
BEGIN;
SELECT nextval('seq'); <= 1
Transaction #2
BEGIN;
SELECT nextval('seq'); <= 2
Transaction #1
ABORT;
BEGIN;
SELECT nextval('seq'); ???
Just because Transaction #1 aborted doesn't mean the sequence value can
be reset. Sequences only guarantee you'll get a number bigger than
before, not that there won't be holes. I am unaware of a database where
this is not true (at least its not true of Oracle). The only way to what
you want is use a 1-row table and a SELECT FOR UPDATE, but that single
row will become a serious bottleneck for you as the number of
simultaneous sessions grows.
Hope that helps,
Mike Mascari
mascarm(at)mascari(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Mascari | 2002-01-24 12:03:14 | Re: Problem with btree index on 7.1.3 |
Previous Message | Marcelo Pereira | 2002-01-24 10:38:03 | Auto Increase |