Re: Getting sequence value after inserting many rows at a time

On Sat, Jan 11, 2003 at 12:25:47 -0600,
Maurício Sessue Otta <mauricio(at)cristorei(dot)com(dot)br> wrote:
> Hi,
>
> I have a PHP script that do something like
> this:
>
> INSERT INTO table_with_sequence (field1,
> field2...., fieldn)
> SELECT field1, field2, ..., fieldn FROM table,table
>
> WHERE condition, condition
>
> My doubt:
> Will the rows just inserted in
> "table_with_sequence" always be sequencial?
> (won't it happen to have X rows from this
> INSERT, 1 rows from another INSERT
> in the middle and the rest rows of the first
> INSERT?)
>
> How can I safely get the first value the
> INSERT "generated" for
> the sequence?

I wanted to expound on my previous answer some more.

In general sequences are designed to provide an efficient way to generate
unique ids. If you are trying to get any more than uniqness out of them
you need to be careful. And in being careful, you may need to do things
that will make them less efficient.

In particular there can be gaps in sequences and they only have a loose
correlation with time.

I suspect that what you are trying to do is label a group in your example.
This is based on the insert not have a guarenteed order and your concern
about other transactions using sequence numbers between the lowest and
highest numbers used in a specif transaction.

My suggestion is to instead use one sequence number for the whole group.
A sketched example follows:

INSERT INTO newtable (groupid, field1, field2...., fieldn)
SELECT a.groupid, b.field1, b.field2, ..., b.fieldn
FROM (SELECT nextval('groupid_seq') as groupid) a,
(SELECT * FROM oldtable WHERE condition, condition) b

The groupid will be the same for each row inserted by a single instance
of this insert statement. Following sql statements can use
currval('groupid_seq') to get the value used for this insert statement.

Doing things this way will probably make other parts of what you are doing
easier. In particular getting records from a specific group will be much
simpler if they all have the same groupid as opposed to all groupids within
a specific range (that will somehow need to be tracked).