Re: INSERT question

Let me say this again, since my previous post is probably unclear:

The code below is safe, even without a transaction, _if it runs in one,
unshared connection to the DB_. So the danger is much less than you
might think. Adding the transaction is good (though nextval() _does not_
rollback, BTW: you get holes in the sequence) because it protects your
data integrity (no orphan parent records if child fails for other reasons)
and lets any layers in between know that these go together, not because
it's needed to cover the multiuser case.

Ross

On Thu, Nov 15, 2001 at 02:28:26PM -0700, Jason Earl wrote:
>
> Yes that is entirely correct. As you pointed out my example is *very*
> dangerous if not wrapped in a transaction. My query clearly should
> have looked like this:
>
> BEGIN;
> INSERT INTO parent_table (data) values ('some data');
> INSERT INTO child_table (parent, more_data)
> (currval('parent_table_p_key_seq'),
> 'more data');
> COMMIT;
>
> Thanks for pointing that out.
>
> Roland Roberts <roland(at)astrofoto(dot)org> writes:
>
> > >>>>> "Jason" == Jason Earl <jason(dot)earl(at)simplot(dot)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.
> >
> > roland
> > --
> > PGP Key ID: 66 BC 3B CD
> > Roland B. Roberts, PhD RL Enterprises
> > roland(at)rlenter(dot)com 76-15 113th Street, Apt 3B
> > roland(at)astrofoto(dot)org Forest Hills, NY 11375
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org