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

Access97 only knows about the fields used in the insert.  Once the insert is
completed, Access97 re-selects the record based on the values provided in
the insert statement.  It does not know what the primary key of the new
record is unless the primary key is a part of the insert statement.  If the
information provided in the insert does not hit a unique record, then the
wrong record could be returned.  The re-select is important to make sure the
newly inserted record reflects any updates that may have occurred as a
result of an insert trigger.

Thanks, Michael


> -----Original Message-----
> From:    Jackson, DeJuan [SMTP:djackson@cpsgroup.com]
> Sent:    Monday, June 07, 1999 5:31 PM
> To:    Michael J Davis; pgsql-sql@postgreSQL.org
> Subject:    RE: [SQL] Getting primary key from insert statement
> 
> Sorry, I interpreted your statement as saying that you were implementing
> your own counter system, which your response makes clear is not the case.
> But I still say that there is a transaction (or it's equivalent) there,
> weather or not you are the one initiating it.  I don't know who
> implemented
> sequences in the backend but sequence increment-read must be an autonomous
> unit to not run into duplicate sequence number problems.  
> If any experts out there want to prove me wrong, I'm all ears.
> My question to you, since you've watched what Access97 generates in SQL,
> is
> why can't it find the row in question after it has been committed to the
> database without the primary key?  The only possibility that I can think
> of
> is that all of the other data is a duplicate of a previous entry in the
> table.
>     -DEJ
> 
> > -----Original Message-----
> > From:    Michael J Davis [SMTP:michael.j.davis@tvguide.com]
> > Sent:    Monday, June 07, 1999 6:14 PM
> > To:    'Jackson, DeJuan'; pgsql-sql@postgreSQL.org
> > Subject:    RE: [SQL] Getting primary key from insert statement
> > 
> > As I am sure you are aware, getting the next value in a sequence
> > automatically increments the sequence.  Two users that get the next
> value
> > from the same sequence at the same time should get different sequence
> > numbers regardless of whether transactions are used or not.  Neither
> user
> > should lock the other user out of the sequence.  If one user does manage
> > to
> > lock a sequence then other users should have to wait or will time out.
> > Even
> > if you get the next value from a sequence within a transaction, I'll bet
> > the
> > sequence is incremented even if the transaction is rolled back or
> aborted.
> > The only problem with this solution is it creates potential holes in the
> > primary key, i.e. you loose sequences.  
> > 
> > In reference to whether "Access97 likes it", I have monitored the
> process
> > in
> > the psql log files of what Access97 does after an insert and can verify
> > that
> > it does what I suggest.  This process (get next sequence and use this as
> > the
> > primary key in an insert statement) improved the time it takes to do
> > inserts
> > by minutes (i.e. it shaved several minutes off the time it took Access
> to
> > complete one insert).  This process also eliminated several problems I
> was
> > having with doing an insert followed by Access97 showing a blank record
> > (this was very disturbing).  If refreshed my form, I could see the newly
> > inserted record (sometimes).  
> > 
> > In summary, I don't agree with your assessment of the locking problems
> > associated with getting the next value of the sequence before the
> insert.
> > I
> > also don't agree that a transaction is needed.  I do agree that you can
> > potentially loose sequence numbers.  I have been willing to accept this
> > loss
> > when compared to the problems this fixed in Access97.
> > 
> > Thanks, Michael
> > 
> > 
> > > -----Original Message-----
> > > From:    Jackson, DeJuan [SMTP:djackson@cpsgroup.com]
> > > Sent:    Monday, June 07, 1999 4:35 PM
> > > To:    Michael J Davis; 'Herouth Maoz'; emils@mail.usis.bkc.lv;
> > > pgsql-sql@postgreSQL.org
> > > Subject:    RE: [SQL] Getting primary key from insert statement
> > > 
> > > 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 your primary key
> > >  and
> > >  B) 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 insert within 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
> > > > > 
> > > > >