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