Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAFM3lm50972 for ; Thu, 15 Nov 2001 17:03:47 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: from localhost (localhost [127.0.0.1]) by ece.rice.edu (Postfix) with ESMTP id A803D68A5E for ; Thu, 15 Nov 2001 16:03:43 -0600 (CST) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id 318B268A5B for ; Thu, 15 Nov 2001 16:03:42 -0600 (CST) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.22 #1 (Debian)) id 164Ubm-0001DX-00 for ; Thu, 15 Nov 2001 16:03:42 -0600 Date: Thu, 15 Nov 2001 16:03:38 -0600 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Subject: Re: INSERT question Message-ID: <20011115160338.D4187@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , pgsql-sql@postgresql.org References: <1f5f2b44.0111130130.17e8e57a@posting.google.com> <3bf19679$0$189$e4fe514c@newszilla.xs4all.nl> <878zd7vpzn.fsf@npa01zz001.simplot.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.3.18i X-Virus-Scanned: by AMaViS snapshot-20010714 X-Archive-Number: 200111/229 X-Sequence-Number: 5611 On Thu, Nov 15, 2001 at 03:03:00PM -0500, Roland Roberts wrote: > >>>>> "Jason" == Jason Earl 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