RE: [SQL] Getting primary key from insert statement - Mailing list pgsql-sql

Have you ever actually looked at what Access97 does as far as SQL is
concerned?
If you ever do then you will realize that arguing that "Access97 like's it"
is no argument at all.
I have implemented your proposed method for insertion before as well.  And A) if you do not want to waste sequences in
yourprimary keyandB) do not want dead lock conditions showing up at random or aborted inserts
 
then it's best if you use transactions.
Any counter systems I ever implement in a database I've implemented in a
stored procedure and then called the stored procedure in a transaction to
store the value in a variable for use in an insert within the same
transaction.  There is no way around these problems in a multi-user system.


Scenarios:1) Grab the sequence number and prefill the number for the insert.
User fills out rest of form and submits.  Insert data and increment counter.problems -     a) another user start to
insertwithin the time that the
 
first insert starts and ends
answer    2) Start Transaction. Lock the sequence table/row. Proceed with
Scenario 1. (already needs transaction if you don't implement you own locking
system).problems -     a) the user does nothing for a few hours/days and your
system is useless until you can track down the offending insert and finish
it (cancel)    b) the user's application dies weather through malicious
intent, an honest systems failure, or an honest user mistake; this could be
fine because your transaction might abort for you (I wouldn't count on it).

answer    3) Start Transaction. Lock the sequence table/row. Grab the next
number. Increment the sequence. End Transaction. prefill form. Users enters
data and commits.  Insert data.problems -    a) your data will have holes in it if the user aborts an
insert.
answer ... This could go on forever, but no matter how you approach it
you'll need a Transaction to accomplish what you want in standard SQL.
-DEJ
> -----Original Message-----
> From:    Michael J Davis [SMTP:michael.j.davis@tvguide.com]
> Sent:    Monday, June 07, 1999 3:10 PM
> To:    'Herouth Maoz'; emils@mail.usis.bkc.lv; pgsql-sql@postgreSQL.org
> Subject:    RE: [SQL] Getting primary key from insert statement
> 
> 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
> > 
> >