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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
