On Thu, Nov 15, 2001 at 03:03:00PM -0500, Roland Roberts wrote:
> >>>>> "Jason" == Jason Earl <jason.earl@simplot.com> writes:
>
> Jason> Even better, however, is to use the functions nextval() and
> Jason> currval() like so:
>
> Jason> INSERT INTO parent_table (data) values ('some data');
> Jason> INSERT INTO child_table (parent, more_data)
> Jason> (currval('parent_table_p_key_seq'),
> Jason> 'more data');
>
> The above is probably the best for a pure-SQL way with the caveat
> that it should be wrapped in a transaction or currval() may not be
> what you expect; i.e., another client may add a row and you get a
> value different from what you inserted.
This is one of the only FMAQ I've ever seen: (Frequently Mis-Answered
Question)
Read the docs for currval(): it returns the value _last sent to
this connection_, so is multiuser safe. The _only_ caveat is that in
the presence of connection pooling, your problem as described might
occur. However, since the two inserts above _should_ be wrapped in a
transaction, I'd say any connection pool that shares a connection with
an open transaction on it is broken, anyway.
Ross