From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | "Matt A(dot)" <survivedsushi(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Serials jumping |
Date: | 2005-08-26 09:21:04 |
Message-ID: | 430EDF00.8070901@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Matt A. wrote:
> I have a serial column on a test box DB. I'm
> using "select nextval('some_seq') as id" to insert a
> id in sequence. It doesn't return the next value but
> at least 4 or so (random) ahead of the current
> value. Is this to be expected?
Quite possibly. The sequence generator doesn't skip around randomly, but
various connections can use/not-use a fetched value.
> Is this the most efficient way to retreive an id value
> for insert a row then insert the resulting row into
> the related tables?
Probably, yes. You can also do something like:
BEGIN;
INSERT INTO master_table (auto_id, a,b,c) VALUES (DEFAULT, 1,2,3);
INSERT INTO detail_table (another_auto_id, master_ref, x)
VALUES (DEFAULT, currval('master_table_auto_id_seq'), 4);
...
COMMIT;
> Is there a more efficient/proper way? I would like all
> my records to increment by 1 if possible. I'd be very
> grateful to read your opinion.
You can either have:
1. A quick, easy way to get a unique number (sequences/serial types)
2. A set of IDs incrementing by 1 each time.
Make your choice. Option 2 involves an explicit counter and locking.
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2005-08-26 10:16:27 | Re: [ANNOUNCE] Its Beta Time Again ... 8.1 Beta 1 Now Available |
Previous Message | Alban Hertroys | 2005-08-26 09:19:07 | Re: Serials jumping |