Received: from corppo1.uvsg.com (corppo1.uvsg.com [192.40.65.90]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA10953 for ; Mon, 7 Jun 1999 16:09:38 -0400 (EDT) (envelope-from michael.j.davis@tvguide.com) Received: from prevuepo1.uvsg.com ([192.40.65.44]) by corppo1.uvsg.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2448.0) id L4BC424G; Mon, 7 Jun 1999 15:09:42 -0500 Received: by prevuepo1.uvsg.com with Internet Mail Service (5.5.2448.0) id ; Mon, 7 Jun 1999 15:12:39 -0500 Message-ID: <93C04F1F5173D211A27900105AA8FCFC14557B@lambic.prevuenet.com> From: Michael J Davis To: "'Herouth Maoz'" , emils@mail.usis.bkc.lv, pgsql-sql@postgreSQL.org Subject: RE: [SQL] Getting primary key from insert statement Date: Mon, 7 Jun 1999 15:10:26 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain I have been using this technique for years in Oracle (select next value from the primary key sequence and insert using this primary key). I like this approach. I don't think a transaction is needed. Access97 definitely likes this approach better because it wants to reselect the newly inserted record. Without the primary key in the insert statement, the re-select can be very slow and potentially fail because the re-select is only using values provided in the insert statement. Retrieving the primary key after the insert does not help Access97. > -----Original Message----- > From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il] > Sent: Monday, June 07, 1999 1:29 AM > To: emils@mail.usis.bkc.lv; pgsql-sql@postgreSQL.org > Subject: Re: [SQL] Getting primary key from insert statement > > At 12:51 +0300 on 07/06/1999, Emils Klotins wrote: > > > > > > Wouldn't it be simpler just to SELECT the next value from the sequence > >BEFORE the insert and > > use it in the INSERT statement directly? > > Then you have to have a transaction around the two operations. And by the > time you do the select, someone may lock the table where you insert. It's > a > potential for deadlock. > > Herouth > > -- > Herouth Maoz, Internet developer. > Open University of Israel - Telem project > http://telem.openu.ac.il/~herutma > >