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

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
> > > > 
> > > >