From: Ross J. Reedstrom <reedstrm@rice.edu>
To: pgsql-sql@postgresql.org
Subject: Re: INSERT question
Date: Thu, 15 Nov 2001 16:09:02 -0600
Message-ID: <20011115160902.E4187@rice.edu> (raw)
In-Reply-To: <87y9l7u3k5.fsf@npa01zz001.simplot.com>
References: <1f5f2b44.0111130130.17e8e57a@posting.google.com>
<3bf19679$0$189$e4fe514c@newszilla.xs4all.nl>
<878zd7vpzn.fsf@npa01zz001.simplot.com>
<m21yizhkej.fsf@tycho.rlent.pnet>
<87y9l7u3k5.fsf@npa01zz001.simplot.com>
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@astrofoto.org> writes:
>
> > >>>>> "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.
> >
> > roland
> > --
> > PGP Key ID: 66 BC 3B CD
> > Roland B. Roberts, PhD RL Enterprises
> > roland@rlenter.com 76-15 113th Street, Apt 3B
> > roland@astrofoto.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@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu
Subject: Re: INSERT question
In-Reply-To: <20011115160902.E4187@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox